How to use ODBC SQLSetConnectAttr from C# ?

Hi,

I'm having problems dealing with some large datatypes in a C# application using odbc.

Basically certain datatypes, such as db2 CLOB, are not recognized as 'valid' by the odbc driver.

It seems the fix is to use the SQLSetConnectAttr function using the appropriate key/value pair to 'map' those to datatypes known to ODBC but I'm not sure how to use it in C# code. The template seems to be more an unmanaged C type API with handles being passed etc...

SQLRETURN SQLSetConnectAttr (
SQLHDBC ConnectionHandle, /* hdbc */
SQLINTEGER Attribute, /* fOption */
SQLPOINTER ValuePtr, /* pvParam */
SQLINTEGER StringLength); /* fStrLen */

I tried creating an OdbcCommand with that function as the command text but without success.

Could somebody drop a line with a C# example of how to do that ?

Thank you,

MM

I don't think you can get to ODBC connection attributes if you're using the ODBC managed provider.

Have you considered using the LongDataCompat keyword in the db2cli.ini config file? You should be able to achieve this via the drivers setup dialog via the ODBC control panel when you configur the DSN rather than editing the file directly. It maps to SQL_ATTR_LONGDATA_COMPAT which is the attribute you're probably trying to set programatically.

|||

Yes,

I did try that, actually followed the instructions of the ibm db2 boulder site and edited the file directly. Unfortunately it didn't work.

Changing the parameter in the DSN does work, but :

1- You have to do it for each DSN (and I'm dealing with numerous DBs)

2- The piece of code I wanted to use it for is to be deployed on machines I do not control and it would be better to let the app handle the data type rather than having the user fiddle with their DSN config.

Any suggestion as to how to do that ?

Thanks,

MM

|||

The next thing to try would be setting the attribute via a connection string keyword. The ODBC managed provider will pass this though to the driver, so this is a fairly safe and clean approach where the driver provides an appropriate keyword.

Failing this - and only if you're absolutely desperate - you might try reflection with the ODBC managed provider to try to gain access to the underlying ODBC connection handle. This would be completely unsupported (if it worked at all) and there is no guarantee that it wouldn't change in the future.

|||

Chris,

I have tried passing the info as an extra connection attribute at the end of the connection string, to no avail.

But I was really poking around for a solution, so I'm not sure I used a correct syntax. Also I'm not sure if the placement matters (ie after the password...). I just stuck it at the end.

Would you know what the correct syntax is ?

Thanks,

MM

|||It should be 'LongDataCompat=1' and can appear anywhere in the connection string, so the end would be fine. Connection string items are seperated by semi-colons. Is this what you tried?|||

Chris,

It worked, you're a genius!

I had tried adding ';SQL_ATTR_LONGDATA_COMPAT=SQL_LD_COMPAT_YES'

I thought LONGDATACOMPAT=1 was to be used in the db2cli.ini file and SQL_ATTR... = SQL_LD... was the key/value pair to be used in the connection string based on the info off the IBM DB2 website.

I still don't understand why it's not but at least it's working!

Thank you,

MM

|||

Hi MM,

My reading was that you can use ini file entries in connection strings. If there's a conflict the connection string wins.

I'm pleased to hear you app is working.