InitString ODBC connection parameter

[dhkom]

Is there a way to specify a value for ODBC connection parameter "InitString" within a SQL Anywhere ODBC DataSource, or even better, within a SQL Anywhere Connection string. The following would be ideal, but does not work:

DSN=mydsn;InitString=''set temporary option bell='off';''

InitString is described here.

Thanks.

[ian_mchardy]

The quotes and semicolons are likely throwing off the connection string parsing. Try

"dsn=mydsn;InitString=set temporary option prefetch='off'"

Something similar worked for me with a 17.0.10 ODBC driver. I could see the set option going to the server and was executed successfully.

Kind regards, Ian

[dhkom]

Thanks Ian. This helped me out. I wanted to use the "InitString" capability to authenticate an application from a C# Web API to an OEM SQL Anywhere database via property "connection_authentication".

For the record, the C# required quote escaping as follows:

string connectionString = "...;InitString=\\"SET TEMPORARY OPTION CONNECTION_AUTHENTICATION='...'\\"";

Note that I was not successful at providing a value for InitString in a shortcut to run ISQL such as:

"%sqlany17%\\Bin64\\dbisqlc.exe" -c "dsn=testdsn;InitString=set temporary option prefetch='off'"

I tried several ways to include "set ... 'off'" within various types of escaped quotes with no success. It's not a big deal, because I could do what I really needed to, but I am interested to know why I could not specify InitString in the shortcut.

[VolkerBarth]

As to the C# sytax, this is also confirmed here...

[ian_mchardy]

initString doesn't work with dbisqlc because dbisqlc uses DBLib, and DBLib doesn't support initString.

Kind regards, Ian

[chris_keating]

Is there a reason need to use InitString for this case? If you are making a connection, perhaps make the first operation on that connection a SET TEMPORARY OPTION to handle the authentication.

[dhkom]

I think there is a need for InitString in this case because when I open a connection within my C# Web API, I don't know whether the connection came from the Pool or was newly created. I need to SET TEMPORARY OPTION in the latter case but not the former. In my legacy windows client application, I have for years done the SET TEMPORARY OPTION after connecting, just as you (Chris) suggest.

[VolkerBarth]

Note, you can also use a login procedure to automatically set options for fresh connections, and you can use appropriate logic to do so. That might be easier than using InitString.

That being said, InitString should work with DBISQL as this is not based on DBLib...

[dhkom]

Thanks, Volker. Please bear in mind that the InitString SQL command in question authenticates the client application to the authenticated database server. Encoding this authentication in a login procedure would make a database authenticated on its own, and thus usable from any application. This circumvents the purpose of authentication.

[VolkerBarth]

Well, you had also used the sample with "set temporary option prefetch", and for options like that, a login procedure might be very usable. But even for authentication, you could add logic to the login procedure so only the according app (say, identified via AppInfo connection information) would supply that value - but I certainly agree this would be quite easy to lever out...:)

[dhkom]

I was not aware of the AppInfo connection parameter, but after reading the documentation, I am now. "set temporary option prefetch" was just an example. Anyway - I am good on my requirement, and as I mentioned, have learned even more about the SQL engine that I have used for 25+ years!