Trouble with the V12 connection dialog and omitted PWDs in connection strings

[VolkerBarth]

As announced in this (pseudo-)answer, I have some trouble when connecting to SA 12 datases when not all necessary connection info is given.

I'm using 12.0.0.2601 on Windows XP on both according boxes.

I commonly use dbisqlc/dbisql with shortcuts and connection strings. When connecting as DBA, I generally omit the PWD in the string - for obvious reasons. Say, I use something like

"%SQLANY12%\\bin32\\dbisqlc.exe" -c "HOST=MyOtherServer;DBN=MyDb;UID=DBA"

to connect to a running database on another box.

With V11 and all older versions I'm aware of, the connection dialog issues an according error (SQLCODE -103, i.e. "You supplied an invalid user ID or an incorrect password"). You then enter the PWD, the other connection parameters are still there, and all is fine. (Yes, I would then use ENG= and LINKS=TCPIP instead of HOST=, obviously.)

With V12 dbisqlc, in this case the same error message comes up, but unfortunately the according connection dialog is set to the "connect with a running database on the same machine" action (poorly translated...). That's a bad choice as the HOST parameter obviously pointed to a different box. Conesequently, when entering the PWD, the HOST= connection info is lost, and therefore the connection won't succeed, leading to a SQLCODE -100 "Database server not found" error.

So, I have to change the correct action type and re-enter the HOST or SERVER name, and only then the connection will succeed.

With V12 DBISQL, the same connection string does not trigger an immediate error message but displays the "connect with UltraLite database" dialog (though I have never used UltraLite at all!). That's even worse, as this won't lead to any connection success. And when changing the dialog to a SQL Anywhere connect, all connection attributes are emptied, and I have to start from scratch.

And it doesn't seem to matter whether I add "Server=MyServer" to the connection string or replace "HOST=MyOtherServer" with "Links=TCPIP" - dbisqlc still assumes a local connection, and DBISQL still assumes an UltraLite connect. Only by adding "Links=TCPIP(HOST=MyOtherServer)" dbisqlc is assured to try a remote connect (but forgets about the given server name). And DBISQL still likes to connect to UltraLite...

With V12 dbisqlc, if I do add the PWD in the connection string, all is fine.

For DBISQL, it still tries to use an UltraLite connect, for whatever reasons. - Is there any connection parameter / DBISQL option to stop that nonsense?

Resume:

I guess the behaviour for V12 should be optimized in a way similar to the previous versions's behaviour.

[graeme_perrow · Accepted solution]

The problem with dbisqlc is likely that it does not recognize the HOST connection parameter, since it was added with v12 and no new features have been added to dbisqlc in several major releases now. The dblib library that dbisqlc uses does recognize it so it works, but if dbisqlc itself has to do any manipulation of the connection string, or display the connection dialog, the HOST parameter will likely get removed.

If the PWD is in the connection string, dbisqlc can just pass it on to dblib and the connection will work. But if there is no PWD, dbisqlc will attempt to prompt for one and then recreate the connection string, As described above, you end up losing the HOST parameter.

I've never seen the dbisql behaviour you mention (using UltraLite by default) so I can't help there.

Update: The dbisqlc problem has been fixed and the fix will appear in 12.0.0.2626. It also affected other ODBC applications that used the connection dialog.

---

Addition: CR number (according to Karim): 652752. Description:

When starting dbisqlc, or an ODBC application with a connection string containing LINKS=TCPIP, if there is an error connecting the connection dialog is displayed. It was incorrectly defaulting to "connect to a running database on another computer", losing the LINKS=TCPIP setting. If TCP parameters were specified, such as LINKS=TCPIP(PORT=2638), then it worked correctly. This has now been fixed.

That being said, Volker is not sure if this fixes the whole problem...

[VolkerBarth · Accepted solution]

But that's not the whole story: Even if dbisqlc12 doesn't recognize HOST=, it should treat the combination of ENG= (or Server=) and LINKS=TCPIP as in previous versions. But as stated above, even then the "connect to local engine" action appears - unless I include the host protocol option... Besides that, I guess dbisqlc should handle HOST=, too.

[graeme_perrow · Accepted solution]

Sorry, I misunderstood. I just tried it and got the same behaviour. I will investigate.

[VolkerBarth · Accepted solution]

@Graeme: Thanks for the fix - could you add the CR number, please?

[Former Member · Accepted solution]

@Volker: I believe the CR number is 652752.

[VolkerBarth · Accepted solution]

@Graeme: Just to add: If the CR number is correct, then the description is partly wrong: The incorrect default action is to connect to a running database "on the same computer"...

[Former Member]

I will leave it up to others to provide an answer regarding dbisqlc, but for dbisql, I believe you might be able to resolve the problem by doing the following:

1. try your dbisql connection as above
2. when the dbisql connection dialog pops up, click the "Change database type" located below the Connect to an UltraLite Database heading
3. Choose SQL Anywhere from the pop down and click connect to force a failed connection
4. Click cancel once the failed connect attempt is completed

Once you do the above, DBISQL should remember that you want to connect to SQL Anywhere rather than UltraLite.

HTH Karim

------- EDIT ------

Volker,

I have head back from the DBISQL group. The bad behaviour that you are seeing with DBISQL has been identified in-house and will be fixed in a future release.

Karim

[VolkerBarth]

Nope, I had tried that several times before - both with successful and failing connections. I even assured that DBISQL was really closed down between different attempts (i.e. stopped the fast launcher). Still the dialog defaults to the UltraLite connect type and lists the given parameters in the "Advanced options" tab.

[Former Member]

Odd, it seems to work every time for me. I will see if I can glean any additional information from the DBISQL folks.

[Former Member]

Volker,

I have heard back from the DBISQL group. See my edit above.