Question Re: Running a script file with dbisql (command-line) in Technology Q&A

Running a script file with dbisql (command-line)

Former Member — Mon, 24 Sep 2012 05:45:39 GMT

I'm trying to connect to a database and run a script using dbisql. I've checked the docs and tried many combinations but I guess I'm missing something.

For example:

dbisql -nogui -c "uid=dba;pwd=sql"

results in:

((not connected)

whereas if I write:

dbisql -c "uid=dba;pwd=sql"

an Interactive SQL window opens as connected.

What I'd really like to is call dbisql -nogui -c "uid=dba;pwd=sql" myscript.sql but I'm stuck on the first step.

Notes:

I have both SA9 and SA12 installed on this machine.
I'm working on SA12 Bin32 directory.
-nogui -c "uid=dba;pwd=sql" line works with SA9 dbisql command.
v12.0.1.3769

Update:

According to the docs, the following command should work against the default database although Links property is missing:

dbisql -c "UID=DBA;PWD=sql" -onerror exit mycom.sql

However, I get the message "You are not connected to a database". On the other hand, I can connect to the same database smoothly via Interactive SQL window (choosing 'connect to a running database on this computer'). dbping -d -c "UID=DBA;PWD=sql" succeeds with the same connection string stating it connected to the database. The target database is started with -x none switch.

Is there anything else I'm missing?

Screenshots:

Dbsrv12:

Dbping & Dbisql:

Dbisql window (works ok):

Re: Running a script file with dbisql (command-line)

thomas_duemesnil — Mon, 24 Sep 2012 05:53:04 GMT

-c Should be a complete connections string. Probably you should add links=shmem to.

In my batch files i don't use the -nogui paramater. And I'm not getting a normal window when I run the batchfile

Re: Running a script file with dbisql (command-line)

Former Member — Mon, 24 Sep 2012 06:57:20 GMT

Thank you, you're correct in both cases. Most of the time I used the Server property but forgot about Links. Also no need for nogui parameters. I suppose I accidentally used SA9 exes in some tests. Anyway, please post your comment as answer so I can accept it.

Re: Running a script file with dbisql (command-line)

Former Member — Mon, 24 Sep 2012 07:57:36 GMT

Hm, it might not be over yet, updating the question.

Re: Running a script file with dbisql (command-line)

reimer_pods — Mon, 24 Sep 2012 08:08:59 GMT

Changed Thomas's comment to answer so henginy may accept it.

Re: Running a script file with dbisql (command-line)

Former Member — Mon, 24 Sep 2012 08:11:04 GMT

Thank you for the answer. First time I tried, it worked with links attribute added. But later, I don't have a single idea how, it didn't work. I keep getting 'You are not connected to a database' message even if I add links (and also server attribute). Please see the update.

Re: Running a script file with dbisql (command-line)

VolkerBarth — Mon, 24 Sep 2012 08:25:37 GMT

I would certainly add the path to the DBSIQL call in order to prevent to call the wrong version. The environment variables are handy here, such as

"%SQLANY12%\\bin32\\dbisql" -c "..."

Are you having a SQLCONNECT environment variable that might "secretly" add stuff to your connection string?

FWIW I still usually use the dbisqlc tool for batch stuff...

Re: Running a script file with dbisql (command-line)

Former Member — Mon, 24 Sep 2012 08:34:06 GMT

@Volker Barth: Yes, I'm also setting another variable to shorten the script: SET sa12bin=%SQLANY12%\\bin32. Checked the environment variables, but no SQLConnect variable (didn't know about them before, thanks).

I like dbisqlc too, but am afraid to use it in case it is discontinued..(don't know if there's really such a risk, but..)

Re: Running a script file with dbisql (command-line)

Dan_Cummins — Mon, 24 Sep 2012 09:32:30 GMT

This is a long shot, but from your screen snap it looks as though you may have endashes (long dashes) on the command line instead of ASCII dashes. I can see how you could get the non-ASCII dash if you cut-and-paste the command line from the docs on Windows. You might want to try typing it in with regular dashes and see if you still have a problem?

Re: Running a script file with dbisql (command-line)

Former Member — Mon, 24 Sep 2012 09:42:57 GMT

I actually typed in the commands every time, no copy-paste. The dashes on the screen must the same as this: '-' (next to Backspace on my keyboard:). Thank you.

Re: Running a script file with dbisql (command-line)

VolkerBarth — Mon, 24 Sep 2012 09:51:49 GMT

Have you tried if using dbisqlc raises the same problem?

Re: Running a script file with dbisql (command-line)

Former Member — Mon, 24 Sep 2012 09:55:36 GMT

I've tried it now and it works OK, no problem connecting to database. Only a few trivial warnings about spatial data options. It does the job and I might stick to it all right, but I wonder why dbisql cannot connect.

Re: Running a script file with dbisql (command-line)

VolkerBarth — Mon, 24 Sep 2012 10:00:08 GMT

I'd try to use the LOG connection parameter within the connection string to get more debug output on the connection attempts...

Re: Running a script file with dbisql (command-line)

Former Member — Mon, 24 Sep 2012 10:17:27 GMT

Good pointer. The command dbisql -c "UID=dba;PWD=sql;LINKS=SharedMemory;Log=C:\\salog" opens an isql window, connected, and the log says Connected to database successfully. When I add file name at the end (dbisql -c "UID=dba;PWD=sql;LINKS=SharedMemory;Log=C:\\salog" test.sql), it says 'You are not connected to a database' and no log file is created. (Test.sql exists, and I'm running the command in %SQLANY12%\\Bin32 directory.)

Re: Running a script file with dbisql (command-line)

Former Member — Mon, 24 Sep 2012 10:22:59 GMT

Btw, test.sql contains only SELECT * FROM SYSVIEW and GO lines.

Re: Running a script file with dbisql (command-line)

VolkerBarth — Mon, 24 Sep 2012 11:08:56 GMT

Does the server console contain any info that a connect attempt is tried (dbeng12 -z ...)?

Re: Running a script file with dbisql (command-line)

Former Member — Mon, 24 Sep 2012 11:22:56 GMT

Sorry, reformatted the output - (done now):

Command: C:\\Program Files\\SQL Anywhere 12\\Bin32>dbisql -c "uid=dba;pwd=sql;LINKS=SharedMemory;Log=C:\\salog.txt"

Server console: Connection ID 1: "dba" connected to database "asusys" from SQL Anywhere 12.0.1 (3769) client over SharedMemory Connection ID 1: AppInfo is IP=x.x.x.x;HOST=xxx;OSUSER=xxx;OS='Windows XP Build 2600 Service Pack 3';EXE='C:\\Program Files\\SQL Anywhere 12\\Bin32\\dbisql.exe';PID=0xdb0;THREAD=0xed8;VERSION=12.0.1.3769;API=iAnywhereJDBC;TIMEZONEADJUS Connection ID 1: Server character set conversion is enabled, but not required using character set "windows-1254"

Command: C:\\Program Files\\SQL Anywhere 12\\Bin32>dbisql -c "uid=dba;pwd=sql;LINKS=SharedMemory;Log=C:\\salog.txt" test.sql

You are not connected to a database.

Server console: => No new lines appended to console.

Re: Running a script file with dbisql (command-line)

Former Member — Mon, 24 Sep 2012 11:28:52 GMT

Other info: Workgroup Edition, Turkish OS.

Re: Running a script file with dbisql (command-line)

VolkerBarth — Mon, 24 Sep 2012 11:49:50 GMT

Still strange. May this be an encoding problem (I-dot etc.)? - If so, I'm no expert anyway:(

Re: Running a script file with dbisql (command-line)

Dan_Cummins — Mon, 24 Sep 2012 11:54:49 GMT

If you want to test if the behaviour is caused by an encoding problem, you can switch the encoding of your command prompt using the chcp command. Example:

chcp 850

chcp 1252