Re: Support of SQL_WVARCHAR/nvarchar

Hello James,

I am not an ODBC expert and I can speak from my experience only. May be
other members of this community know a better way to handle this case.

The MSDN mentions 4 possible return codes for GetSQLTypeInfo(). As I
see it: the successful call of GetSQLTypeInfo() simply means that the
function did not fail and the caller may expect to get some data back.
In my particular case the call of GetSQLTypeInfo() only makes sense
when it is called in pair with SQLFetch(). Therefore, every time when
I want to get a list of data types, supported by a particular data
source, I call that pair of the functions and check the return code of
SQLFetch(). I am interested to see if the "successful" return code is
different from SQL_NO_DATA, because SQL_NO_DATA (in my view) means -
your check did not succeed, the data type you requested is not
supported, substitution of data types should be used. But this is not
an error and execution should not be stopped here, substituted data
types will be used instead of non-existed ones.

Usually I want to know about 6 character data types:
SQL_CHAR,SQL_VARCHAR,SQL_LONGVARCHAR,SQL_WCHAR,SQL_WVARCHAR,SQL_WLONGVARCHAR.

How does it work on Windows in case of the native MS SQL, MS Access,
MS dBase ODBC drivers (the table is built with the calls of
GetSQLTypeInfo()/SQLFetch())?

 MS SQL ver.2000.85.1117.00?
 SQL_WCHAR  : [nchar[4000]]
 SQL_WVARCHAR : [nvarchar[4000]]
 SQL_WLONGVARCHAR : [ntext[1073741823]]
 SQL_CHAR  : [char[8000]]
 SQL_VARCHAR : [varchar[8000]]
 SQL_WLONGVARCHAR: [text[2147483647]]

 MS Access ODBC dr. ver. 4.00.6304.00
 SQL_WCHAR : [CHAR[255]] (not supported, CHAR was used as substitution)
 SQL_WVARCHAR  : [CHAR[255]] (not supported, CHAR was used as substitution)  
 SQL_WLONGVARCHAR : [CHAR[255]] (not supported, CHAR was used as substitution) 
 SQL_CHAR : [CHAR[255]] 
 SQL_VARCHAR : [VARCHAR[255]]
 SQL_LONGVARCHAR : [LONGCHAR[1073741823]] 

 MS dBase ODBC dr. ver. 4.00.6304.00
 SQL_WCHAR : [Char[254]] (not supported, Char was used as substitution)
 SQL_WVARCHAR: [Memo[1073741824]] (not supported, Memo was used as substitution)
 SQL_WLONGVARCHAR : [Memo[1073741824]] (not supported, Memo was used as substitution)
 SQL_CHAR : [Char[254]]
 SQL_VARCHAR : [Memo[1073741824]] (not supported, Memo was used as substitution)
 SQL_WLONGVARCHAR : [Memo[1073741824]]

Having this list of available data types I am ready to choose
the appropriate ones and begin to work with the database/server.

Therefore, I think the right way to inform the caller what the
freetfds ODBC driver does not support SQL_WVARCHAR is to return
SQL_NO_DATA from SQLFetch() (if it is possible of course). In turn,
the caller if he really cares about this particular data type has to
look for an appropriate substitution.

JKL> and your input definitely *is* a valid datatype.
This might be important. The pair of calls
(GetSQLTypeInfo()/SQLFetch()) does not answer the _generic question
whether or not this data type is valid in broad terms. It answers the
question if this SQL data type can be mapped into a particular data
type which is specific to a particular data source.

How does it work now? The call of GetSQLTypeInfo()/SQLFetch()
indicates that the caller can use SQL_WVARCHAR and the next call of
SQLBindParameter() fails with the message that the used data type is
not supported.

James, what would you say? Does my point of view sound reasonable?
-- 
Thanks,
 Marko


JKL> marko68 GMX wrote:
>> In my view, it is a bit strange that GetSQLTypeInfo() indicates that
>> SQL_WVARCHAR is supported 

JKL> Does a successful return from GetSQLTypeInfo() indicate the type is
JKL> supported?  Or does it just indicate that type information was returned?

>> and the corresponding MS SQL UNICODE data
>> type is "nvarchar", because any next attempt to use it immediately
>> fails. Would not it make more sense for GetSQLTypeInfo() to fail when
>> the info for SQL_WVARCHAR is requested

JKL> http://msdn.microsoft.com/en-us/library/ms714632(VS.85).aspx

JKL> Fail how?  

JKL> The only error code I found that might apply is HY004 "Invalid SQL data
JKL> type", which doesn't seem to fit.  Its description is:

JKL>         "The value specified for the argument DataType was neither a valid ODBC
JKL> SQL data type identifier nor a driver-specific data type identifier
JKL> supported by the driver."

JKL> and your input definitely *is* a valid datatype.  
 
JKL> --jkl