Executing SQL files without semicolon as end-of-statement

[monofox]

Environment:

• DB Engine: DB2 on IBM System i
• Driver: ODBC

Situation:

DB2 on IBM System i can only run single SQL statements. A batch mode executing several statements at once is not supported.

Executing SQL statements like

 ~/D/usql (master)> ./usql -q -C -o puser.csv -c 'SELECT * FROM PUSER' 'odbc+IBM+i+Access+ODBC+Driver://?SYSTEM=pub1hostname&NAM=1&UID=user&PWD=my-secret-password&DBQ=LIB1,LIB2'

works flawlessly and is given the right thing as output.

Imagine the SQL statement is too big for e.g. PowerShell in order to pass it in the "-c" parameter. So we will put the SQL statement in the file passed in "--file" parameter. The SQL statement must be terminated with a semicolon ";" by today. Otherwise, they will not be executed.

So putting the above SQL statement

SELECT * FROM PUSER;

into a puser.sql statement and executing the same again - just as file:

 ~/D/usql (master)> ./usql -q -C -o puser.csv --file=puser.sql 'odbc+IBM+i+Access+ODBC+Driver://?SYSTEM=pub1hostname&NAM=1&UID=user&PWD=my-secret-password&DBQ=LIB1,LIB2'
error: odbc: SQLPrepare: {42000} [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token ; was not valid. Valid tokens: <END-OF-STATEMENT>.

This is producing an error message. IBM System i does not support having statements ending with a ";"

I could not find a solution for this through the options and CLI documentation. I would like to propose to a have a -c parameter just for files ( = single statement files).

For my tests i've made a modification to support this within a local fork

Would this be an option to have it upstream here?

[kenshaw]

As with psql, you can use \g or any of the other Query Execute commands to execute whatever is in the query buffer:

Query Execute
  \g [(OPTIONS)] [FILE] or ;           execute query (and send results to file or |pipe)
  \crosstabview [(OPTIONS)] [COLUMNS]  execute query and display results in crosstab
  \G [(OPTIONS)] [FILE]                as \g, but forces vertical output mode
  \gexec                               execute query and execute each value of the result
  \gset [PREFIX]                       execute query and store results in usql variables
  \gx [(OPTIONS)] [FILE]               as \g, but forces expanded output mode
  \watch [(OPTIONS)] [DURATION]        execute query every specified interval

[kenshaw]

There are other databases that can't have the semi-colon sent. I had already solved this for other databases, and the same logic could be copied to this driver as well.

[monofox]

Thank you for your feedback.

As with psql, you can use \g or any of the other Query Execute commands to execute whatever is in the query buffer:

Actually it is not loading the sql file in the query buffer if the ";" is missing in the file. The buffer is empty after executing

\g puser.sql

So it seems not to be forceable to execute it.

There are other databases that can't have the semi-colon sent. I had already solved this for other databases, and the same logic could be copied to this driver as well.

Can you name examples so it can be evaluated if it might be able to put it to generic odbc driver?

[kenshaw]

You would use \g in place of semicolons in your script:

pg=> select 1 \g

[kenshaw]

I'm pushing a commit right now, after it passes my local tests, that will put a special usql option on the ODBC driver to remove these at execution.

Are you using this on Windows? If you need a release, you will need to wait some time, as I'm in the middle of overhauling the code base, and a release won't be ready for another week at minimum.

[monofox]

You would use \g in place of semicolons in your script:

Oh this didn't came in my mind. Indeed it is working flawlessly. I can totally work with this.

Are you using this on Windows?

Yes, but is totally fine to me like it is working now as i understand it.

So for me, the ticket would then be closed as "user mishandling" ;)

Thank you for this incredible fast support.

[kenshaw]

Sure, glad to see the tool is getting use on Windows. I assumed it was windows as I couldn't find a *nix ODBC driver for the database you were listing. I have a different change I'm pushing shortly that will allow you to write your scripts normally, and not use \g as the terminator.

[kenshaw]

@monofox I just pushed a changed that will allow you to enable trimming of ending semicolons for ODBC. Please build from source (with the odbc tag of course:

go install -tags 'most odbc' github.com/xo/usql@master

And then connect as usual, but add the usql_trim=1 parameter:

usql 'odbc+IBM+i+Access+ODBC+Driver://?SYSTEM=pub1hostname&NAM=1&UID=user&PWD=my-secret-password&DBQ=LIB1,LIB2&usql_trim=1'

Try doing a few queries and let me know if this works as expected.

[kenshaw]

Sorry -- if you had already built it, I realized after I had pushed the code that Windows has a different code path that Unix for ODBC. I've pushed a change that adds this to the ADODB driver, which is what's used for ODBC on Windows.