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.

asked 16 Apr, 14:09

dharrel's gravatar image

dharrel
1314515
accept rate: 0%


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

permanent link

answered 16 Apr, 15:22

Ian%20McHardy's gravatar image

Ian McHardy
3.4k23557
accept rate: 40%

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.

(19 Apr, 12:05) dharrel
Replies hidden
1

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

(19 Apr, 14:08) Volker Barth
2

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

Kind regards, Ian

(19 Apr, 16:26) Ian McHardy

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.

(19 Apr, 22:40) Chris Keating

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.

(20 Apr, 16:01) dharrel
Replies hidden

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...

(20 Apr, 17:14) Volker Barth

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.

(22 Apr, 11:19) dharrel
Replies hidden
1

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...:)

(22 Apr, 12:35) Volker Barth

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!

(23 Apr, 22:49) dharrel
showing 5 of 9 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×154
×139

question asked: 16 Apr, 14:09

question was seen: 128 times

last updated: 23 Apr, 22:49