TFieldType Enumeration

[theLizard]

I am using this statement to get the datatype for a column in an sql table using ODBC

SQLBindCol(q.hstmt, 5, SQL_C_SSHORT, &DataType, 0, &cbDataType)

DataType returns -9 for VARCHAR Just trying to workout where -9 fits into the TFieldType Enumeration.

is SQL_C_SSHORT the right choice? I have tried SQL_C_USHORT same result, should I be using something else?

I am not having any problems with this so it's no big issue as I test for -9 rather than an ft??? type specified in TFieldType Enumeration.

Just curious...

Google's no good, searched but no answers...

Cheers

[rlebeau]

I am using this statement to get the datatype for a column in an sql table using ODBC

SQLBindCol(q.hstmt, 5, SQL_C_SSHORT, &DataType, 0, &cbDataType)

If your goal is to determine the data type used for record data in a specific column, SQLBindCol() is the wrong way to do it (I guess you did not read the Binding Columns documentation). Use SQLDescribeCol() or SQLColAttribute() instead. A value stored in a record's column is a totally different thing than the data type of the column itself.

SQLBindCol() is used to receive data for a specific column when fetching records, or to provide data for a specific column when committing records.

If you read the documentation for SQLBindCol(), it clearly states the following for the 4th parameter:

TargetValuePtr
[Deferred Input/Output] Pointer to the data buffer to bind to the column. SQLFetch and SQLFetchScroll return data in this buffer. SQLBulkOperations returns data in this buffer when Operation is SQL_FETCH_BY_BOOKMARK; it retrieves data from this buffer when Operation is SQL_ADD or SQL_UPDATE_BY_BOOKMARK. SQLSetPos returns data in this buffer when Operation is SQL_REFRESH; it retrieves data from this buffer when Operation is SQL_UPDATE.

The 3rd parameter tells ODBC the type of data that is stored in that buffer:

TargetType
[Input] The identifier of the C data type of the *TargetValuePtr buffer. When it is retrieving data from the data source with SQLFetch, SQLFetchScroll, SQLBulkOperations, or SQLSetPos, the driver converts the data to this type; when it sends data to the data source with SQLBulkOperations or SQLSetPos, the driver converts the data from this type.

So, in this case, you are telling ODBC that the data in your &DataType buffer is a "signed short int". Meaning, when ODBC is fetching record data to return to you, it will write a "signed short int" value into the buffer, and when ODBC is requesting record data from you, it will read a "signed short int" value from the buffer.

The 6th parameter is an output parameter used to report things like string lengths, NULL data flag, etc.

StrLen_or_IndPtr
[Deferred Input/Output] Pointer to the length/indicator buffer to bind to the column. SQLFetch and SQLFetchScroll return a value in this buffer. SQLBulkOperations retrieves a value from this buffer when Operation is SQL_ADD, SQL_UPDATE_BY_BOOKMARK, or SQL_DELETE_BY_BOOKMARK. SQLBulkOperations returns a value in this buffer when Operation is SQL_FETCH_BY_BOOKMARK. SQLSetPos returns a value in this buffer when Operation is SQL_REFRESH; it retrieves a value from this buffer when Operation is SQL_UPDATE.

SQLFetch, SQLFetchScroll, SQLBulkOperations, and SQLSetPos can return the following values in the length/indicator buffer:

• The length of the data available to return
• SQL_NO_TOTAL
• SQL_NULL_DATA

The application can put the following values in the length/indicator buffer for use with SQLBulkOperations or SQLSetPos:

• The length of the data being sent
• SQL_NTS
• SQL_NULL_DATA
• SQL_DATA_AT_EXEC
• The result of the SQL_LEN_DATA_AT_EXEC macro
• SQL_COLUMN_IGNORE

For instance, when fetching a record, or when committing a record, that contains a NULL value in the column, you use the 6th parameter to determine/specify that.

DataType returns -9 for VARCHAR Just trying to workout where -9 fits into the TFieldType Enumeration.

It doesn't fit anywhere.

For one thing, TFieldType only contains positive values, no negatives. The TFieldType value for "signed short int" is ftSmallint (2).

For another thing, your DataType variable (which you are using as an output buffer when fetching a record) will receive a value of -9 only if you fetch a record whose value in column 5 is convertible to a "signed short int".

is SQL_C_SSHORT the right choice?

You tell us. What is the actual SQL data type of column 5 in the table/recordset that is represented by q.hstmt?

should I be using something else?

What are you trying to accomplish in the first place?

[theLizard]

If your goal is to determine the data type used for record data in a specific column

Yes, it is but for a table's definition of the columns not the data stored in the column

Code: Select all

SQLColumnsW(.hstmt, NULL, 0, NULL, 0, table.c_str(), SQL_NTS, NULL, 0);

SQLBindCol() is used to receive data for a specific column when fetching records

Yes, that is what I understood and wanted, follows the example given by Microsoft, specifically for DataType

Code: Select all

SQLBindCol(hstmt, 5, SQL_C_SSHORT, &DataType, 0, &cbDataType);


refer https://msdn.microsoft.com/en-us/library/ms711683%28v=vs.85%29.aspx

Documentation says DataType is SQLSMALLINT, which is what I have declared, the return value when checking the type for an VARCHAR column in a database comes back as -9, this is what I do not get because I have found no reference yet that explains what -9 means

Does it mean, SQL_VARCHAR, SQL_LONGVARCHAR, SQL_WVARCHAR or SQL_WLONGVARCHAR, that is the question.

It doesn't fit anywhere.

If it does not fit anywhere, why is it that I get the -9 value, I have seen no reference to it anywhere yet this is what is returned.

You tell us. What is the actual SQL data type of column 5 in the table/recordset that is represented by q.hstmt?

According to MS it should be SQL_C_SSHORT, so will accept that this is correct.

Apologies if I asked the wrong question or was not specific enough, just trying to improve my understanding.

Cheers

[rlebeau]

If your goal is to determine the data type used for record data in a specific column

Yes, it is but for a table's definition of the columns not the data stored in the column

Code: Select all

SQLColumnsW(.hstmt, NULL, 0, NULL, 0, table.c_str(), SQL_NTS, NULL, 0);

SQLColumns() retrieves a recordset of columns in the specified table. That recordset contains some metadata about the columns, including their data types. The data type of each table column is in column 5 of the recordset, which is indeed a smallint, so you can use SQLBindCol(SQL_C_SSHORT) to read that value, eg:

Code: Select all

SQLRETURN ret = SQLColumnsW(.hstmt, NULL, 0, NULL, 0, table.c_str(), SQL_NTS, NULL, 0);
if (ret failed) ...

SQLSMALLINT DataType;
SQLLEN DataIndicator;

ret = SQLBindCol(.hstmt, 5, SQL_C_SSHORT, &DataType, 0, &DataIndicator);
if (ret failed) ...

do
{
    ret = SQLFetch(.hstmt);
    if (ret failed) break;

    // use DataType and DataIndicator as needed...
    switch (DataType)
    {
        case SQL_CHAR: ...
        case SQL_VARCHAR: ...
        case SQL_WCHAR: ...
        ...
    }
}
while (true);


Yes, that is what I understood and wanted, follows the example given by Microsoft, specifically for DataType

Code: Select all

SQLBindCol(hstmt, 5, SQL_C_SSHORT, &DataType, 0, &cbDataType);

refer https://msdn.microsoft.com/en-us/library/ms711683%28v=vs.85%29.aspx

Documentation says DataType is SQLSMALLINT, which is what I have declared, the return value when checking the type for an VARCHAR column in a database comes back as -9, this is what I do not get because I have found no reference yet that explains what -9 means

In that case, -9 is SQL_WVARCHAR: "SQL Unicode type ... has a variable length with a declared maximum", aka NVARCHAR(SomeLength).

Does it mean, SQL_VARCHAR, SQL_LONGVARCHAR, SQL_WVARCHAR or SQL_WLONGVARCHAR, that is the question.

A simple Google search would have answered that. Or you can have searched the ODBC header files on your machine. ODBC's SQL Unicode data types, including SQL_WVARCHAR, are declared in sqlucode.h (which also declares ODBC's Unicode functions, including SQLColumnsW()):

Code: Select all

#define SQL_WCHAR           (-8)
#define SQL_WVARCHAR        (-9)
#define SQL_WLONGVARCHAR    (-10)
#define SQL_C_WCHAR         SQL_WCHAR

#ifdef UNICODE
#define SQL_C_TCHAR         SQL_C_WCHAR
#else
#define SQL_C_TCHAR         SQL_C_CHAR
#endif


Other SQL data types are declared in sql.h and sqlext.h.

[theLizard]

SQLBindCol(SQL_C_SSHORT) to read that value, eg:

That is pretty much what I am doing.

A simple Google search would have

Nothing is simple in a Google search when you are confronted with useless information that is not in any way related to question asked.

However, I have since found the answer I was looking for at easysoft.com

Code: Select all


//#define SQL_UNKNOWN_TYPE        0
//#define SQL_CHAR                1
//#define SQL_NUMERIC             2
//#define SQL_DECIMAL             3
//#define SQL_INTEGER             4
//#define SQL_SMALLINT            5
//#define SQL_FLOAT               6
//#define SQL_REAL                7
//#define SQL_DOUBLE              8
//#define SQL_DATETIME            9      ODBCVER >= 0x0300
//#define SQL_DATE                9
//#define SQL_INTERVAL            10     ODBCVER >= 0x0300
//#define SQL_TIME                10
//#define SQL_TIMESTAMP           11
//#define SQL_VARCHAR             12

//#define SQL_TYPE_DATE           91     ODBCVER >= 0x0300
//#define SQL_TYPE_TIME           92     ODBCVER >= 0x0300
//#define SQL_TYPE_TIMESTAMP      93     ODBCVER >= 0x0300

//#define SQL_LONGVARCHAR         (-1)
//#define SQL_BINARY              (-2)
//#define SQL_VARBINARY           (-3)
//#define SQL_LONGVARBINARY       (-4)
//#define SQL_BIGINT              (-5)
//#define SQL_TINYINT             (-6)
//#define SQL_BIT                 (-7)
//#define SQL_WCHAR               (-8)
//#define SQL_WVARCHAR            (-9)
//#define SQL_WLONGVARCHAR        (-10)
//#define SQL_GUID        (-11)  ODBCVER >= 0x0350
//#define SQL_SS_VARIANT          (-150) SQL Server 2008
//#define SQL_SS_UDT              (-151) SQL Server 2008
//#define SQL_SS_XML              (-152) SQL Server 2008
//#define SQL_SS_TABLE            (-153) SQL Server 2008
//#define SQL_SS_TIME2            (-154) SQL Server 2008
//#define SQL_SS_TIMESTAMPOFFSET  (-155) SQL Server 2008


are declared in sqlucode.h

Now I know..

Cheers