Error in console mode

[Darshak]

Hi,

While using SQLWorkbench in console mode, get errors. it is highlight in below text/log.
Same cmd was working in Ver 116.x

Env Details :

SQLWorkbench Build # 117
OS : Windows 7
Java version=1.7.0_71

cmd used for console mode:

"C:\Program Files (x86)\Java\jre7\bin\java" -Dworkbench.log.log4j=log4j_cmdline.xml -jar C:\wbench\sqlworkbench.jar -url=jdbc:db2://machineName:50000/dbName -username=db2admin -password=PASSWORD -driver=com.ibm.db2.jcc.DB2 Driver -driverJar=C:\db2driver\db2_97\db2jcc.jar;C:\db2driver\db2_97\db2jcc_license_cu.jar -autocommit=Off -abortOnError=true -cleanupSuccess=commit.sql -cleanupError=rollback.sql -script=c:\temp\db2_run.sql -displayResult=true -feedback=true -ignoreDropErrors=true -removeComments=true

LOG :

[2015-01-30 14:57:18,742] [INFO ] =================== Log started ===================
[2015-01-30 14:57:18,743] [INFO ] Log4J initialized from: file:/C:/wbench/log4j_cmdline.xml
[2015-01-30 14:57:18,744] [INFO ] Using configdir: C:\wbench
[2015-01-30 14:57:18,746] [DEBUG] Last modification time of loaded config file: 1422609807688
[2015-01-30 14:57:18,784] [INFO ] Setting default locale to: en
[2015-01-30 14:57:18,785] [INFO ] Starting SQL Workbench/J, Build 117 (2015-01-04 21:00)
[2015-01-30 14:57:18,785] [INFO ] Java version=1.7.0_71, java.home=C:\Program Files (x86)\Java\jre7, vendor=Oracle Corpo
ration, name=Java HotSpot(TM) Client VM
[2015-01-30 14:57:18,787] [INFO ] Operating System=Windows 7, version=6.1, platform=x86
[2015-01-30 14:57:18,789] [INFO ] Available memory: 247MB
[2015-01-30 14:57:18,923] [INFO ] POI version: 3.9 available.
[2015-01-30 14:57:19,237] [INFO ] Creating new connection for [{Default group}/$Wb$CommandLineProfile] for driver=com.ib
m.db2.jcc.DB2Driver and URL=[jdbc:db2://machineName:50000/dbName]
[2015-01-30 14:57:19,265] [INFO ] Adding ClassLoader URL=file:/C:/wbench/db2driver/db2_97/db2jcc.jar
[2015-01-30 14:57:19,266] [INFO ] Adding ClassLoader URL=file:/C:/wbench/db2driver/db2_97/db2jcc_license_cu.jar
[2015-01-30 14:57:23,797] [DEBUG] Schema term: schema
[2015-01-30 14:57:23,798] [DEBUG] Catalog term: null
[2015-01-30 14:57:23,800] [INFO ] Using DBID=db2
[2015-01-30 14:57:23,820] [DEBUG] Re-Use statement: false, cache current schema: false, SQL: values(current_schema)
[2015-01-30 14:57:23,821] [DEBUG] Identifier quote character obtained from driver: "
[2015-01-30 14:57:23,822] [INFO ] Using identifier quote character: "
[2015-01-30 14:57:23,822] [INFO ] Using search string escape character: \
[2015-01-30 14:57:29,348] [DEBUG] Using table types returned by the JDBC driver: [ALIAS, HIERARCHY TABLE, MATERIALIZED Q
UERY TABLE, NICKNAME, SYSTEM TABLE, TABLE, TYPED TABLE]
[2015-01-30 14:57:29,349] [INFO ] Using catalog separator: .
[2015-01-30 14:57:29,350] [INFO ] Connected to: [DB2/NT], Database version: [SQL09071], Driver version: [3.57.82], JDBC
Version: [3.0], ID: [BatchRunner]
[2015-01-30 14:57:29,356] [INFO ] Connection to "User=db2admin, URL=jdbc:db2://machineName:50000/dbName" successful
Connection to "User=db2admin, URL=jdbc:db2://machineName:50000/dbName" successful
[2015-01-30 14:57:29,358] [INFO ] Processing file: C:\Temp\db2_run.sql
[2015-01-30 14:57:29,415] [DEBUG] Executing statement: SET SCHEMA MYSCHEMA;
@D:\script_DB2.sql
[2015-01-30 14:57:29,469] [ERROR] DB2 SQL Error: SQLCODE=-7, SQLSTATE=42601, SQLERRMC=\;SET SCHEMA MYSCHEMA;
@D:, DRIVER=3.57.82
DB2 SQL Error: SQLCODE=-7, SQLSTATE=42601, SQLERRMC=\;SET SCHEMA MYSCHEMA;
@D:, DRIVER=3.57.82
In file: C:\Temp\db2_run.sql
SQL execution time: 0.05s

C:\Temp\db2_run.sql: 1 statement executed.
Execution time: 0.09s
[2015-01-30 14:57:29,502] [INFO ] Executing error script file: C:\wbench\rollback.sql
[2015-01-30 14:57:29,506] [DEBUG] Executing statement: rollback;
ROLLBACK executed successfully
SQL execution time: 0s

C:\wbench\rollback.sql: 1 statement executed.
Execution time: 0s
[2015-01-30 14:57:29,512] [INFO ] Disconnecting: [$Wb$CommandLineProfile], ID=BatchRunner
[2015-01-30 14:57:29,513] [DEBUG] Connection BatchRunner closed.
[2015-01-30 14:57:29,514] [DEBUG] Closing all open windows
[2015-01-30 14:57:29,514] [DEBUG] Settings not saved. writeSettings=true, runMode=Batch
[2015-01-30 14:57:29,514] [INFO ] Stopping SQL Workbench/J, Build 117
[2015-01-30 14:57:29,515] [INFO ] =================== Log stopped ===================

What could be issue ? cmds are wrong ?.

Thanks,
 Darshak

 

[Thomas Kellerer]

Can you show me the complete content from "db2_run.sql"?

Thomas

Darshak schrieb am 30.01.2015 um 11:09:
> Hi,
>
> While using SQLWorkbench in console mode, get errors. it is highlight in below text/log.
> Same cmd was working in Ver 116.x
>

> *Env Details :*

[Darshak]

Hi,

Content of db_run.sql

SET SCHEMA MYSCHEMA;
@D:\script_DB2.sql

Regards,
 Darshak

[Thomas Kellerer]

Does it work when you terminate the @ line with a semicolon?

SET SCHEMA MYSCHEMA;
@D:\script_DB2.sql;

Regards
Thomas

[Darshak]

No, It does not work even after adding semicolon at end of 2nd line.

[Darshak]

Hi,

  What could be issue for below problem ?

Regards,
 Darshak

[Thomas Kellerer]

It's a regression of the new script parser I implemented (which essentially enables mixed usage of / and ; for Oracle).

I added support for the @ command only for Oracle - somehow I thought that only Oracle user would use that SQL*Plus compatibility.

This should be fixed now with 117.1

Sorry for the trouble.
Thomas

[Darshak]

Hi,

I tried with Build 117.1 (2015-02-01 19:10) but still same issue.

[2015-02-04 11:08:30,028] [INFO ] Processing file: C:\Temp\db2_run.sql
[2015-02-04 11:08:30,049] [DEBUG] Executing statement: SET SCHEMA MYSCHEMA;
@D:\script_DB2.sql
[2015-02-04 11:08:30,061] [ERROR] DB2 SQL Error: SQLCODE=-7, SQLSTATE=42601, SQLERRMC=\;SET SCHEMA MYSCHEMA;

@D:, DRIVER=3.57.82
DB2 SQL Error: SQLCODE=-7, SQLSTATE=42601, SQLERRMC=\;SET SCHEMA MYSCHEMA;
@D:, DRIVER=3.57.82
In file: C:\Temp\db2_run.sql

Thanks,
 Darshak

[Thomas Kellerer]

Hmm,

this works for me:

-- db2_run.sql
set schema stuff;
@script_db2.sql

-- script_db2.sql
wbexport -file=temp.txt -sourceTable=foo;


Then I run:

java -jar c:\Projects\sqlworkbench\dist\sqlworkbench.jar ^
-configDir=c:\Projects\sqlworkbench\conf ^
-profile="DB2 Express" ^
-script=db2_run.sql

Logfile:

2015-02-04 08:58 BatchRunner.connect() Connection to "User=db2inst1, URL=jdbc:db2://192.168.56.2:50001/SAMPLE" successful
2015-02-04 08:58 BatchRunner.execute() Processing file: C:\Projects\sqlworkbench\testscripts\db2_run.sql
2015-02-04 08:58 BatchRunner.executeScript() Executing statement: set schema stuff
2015-02-04 08:58 BatchRunner.executeScript() Executing statement: @script_db2.sql
2015-02-04 08:58 BatchRunner.execute() Processing file: C:\Projects\sqlworkbench\testscripts\script_db2.sql
2015-02-04 08:58 BatchRunner.executeScript() Executing statement: wbexport -file=temp.txt -sourceTable=foo

Can you please send me the complete logfile?

Regards
Thomas


Darshak schrieb am 04.02.2015 um 06:46:
> Hi,
>
> I tried with *Build 117.1 *(2015-02-01 19:10) but still same issue.

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbenc...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[Darshak]

Hi,

Please find required details along with attached log file.

-- db2_run.sql
SET SCHEMA db2admin;
@c:\temp\db2_script.sql


-- db2_script.sql
select * from sysibm.sysdummy1;


-- cmd used
"C:\Program Files (x86)\Java\jre7\bin\java" -Dworkbench.log.log4j=log4j_cmdline.xml -jar C:\utils\wbench\sqlworkbench.jar -url=jdbc:db2://machineName:50000/dbName -username=db2admin -password=PASSWORD -driver=com.ibm.db2.jcc.DB2Driver -driverJar=C:\Utils\wbench\db2driver\db2_97\db2jcc.jar;C:\Utils\wbench\db2driver\db2_97\db2jcc_license_cu.jar -autocommit=Off -abortOnError=true -cleanupSuccess=commit.sql -cleanupError=rollback.sql -script=c:\temp\db2_run.sql -displayResult=true -feedback=true -ignoreDropErrors=true -removeComments=true


-- logFile
Attached log4j_cmdline.log in current reply


Thanks,
 Darshak

> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbench+unsub...@googlegroups.com>.

[Thomas Kellerer]

I'm completely stumped - I have no idea what is going on here.

If I create exactly the same scripts as you have (except for the Log4J stuff and some of the paths), this works for me.

I can see only one difference: you are using DB2 9.7 on Windows I use DB2 10.1 on Linux
But I cannot imagine any reason why that would influence the parsing of the script.

For the time being I can only suggest to change the script to use WbInclude:

SET SCHEMA db2admin;
WbInclude -file=c:\temp\db2_script.sql;

Regards
Thomas

Darshak schrieb am 04.02.2015 um 09:58:
> Hi,
>
> Please find required details along with attached log file.
>

> *-- db2_run.sql*

> SET SCHEMA db2admin;
> @c:\temp\db2_script.sql
>
>

> *-- db2_script.sql*
> select * from sysibm.sysdummy1;
>
>
> *-- cmd used*

> "C:\Program Files (x86)\Java\jre7\bin\java" -Dworkbench.log.log4j=log4j_cmdline.xml -jar C:\utils\wbench\sqlworkbench.jar -url=jdbc:db2://machineName:50000/dbName -username=db2admin -password=PASSWORD -driver=com.ibm.db2.jcc.DB2Driver -driverJar=C:\Utils\wbench\db2driver\db2_97\db2jcc.jar;C:\Utils\wbench\db2driver\db2_97\db2jcc_license_cu.jar -autocommit=Off -abortOnError=true -cleanupSuccess=commit.sql -cleanupError=rollback.sql -script=c:\temp\db2_run.sql -displayResult=true -feedback=true -ignoreDropErrors=true -removeComments=true
>
>

> *-- logFile*

[Darshak]

Hi,

  Thanks for your support.

I tried WbInclude -file= and it worked for given sample.

Actually, wrapper scripts - db2_run.sql is being generated using .bat and likewise ora_run.sql and as4_run.sql also generated.
and then another .bat connects to each RDBMS and run <RDBMS>_run.sql as mentioned in given console based cmd in previous mail.

I will try to modify .bat (to generate _run.sql) else need to use Ver 116 for a while.

Thanks,
 Darshak

[Thomas Kellerer]

WbInclude will work on any DBMS so there is no need to generate different scripts for different DBMS.

Thomas

Darshak schrieb am 04.02.2015 um 11:06:
> Hi,
>
> Thanks for your support.
>

> I tried *WbInclude -file= *and it worked for given sample.
>
> Actually, wrapper scripts - db2_run.sql is being generated using *.bat* and likewise ora_run.sql and as4_run.sql also generated.

> and then another .bat connects to each RDBMS and run <RDBMS>_run.sql as mentioned in given console based cmd in previous mail.
>

> I will try to modify *.bat* (to generate _run.sql) else need to use Ver 116 for a while.

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.

> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbenc...@googlegroups.com>.

[Darshak]

I agree, but main script (e.g. db2_script.sql) mentioned as FILE parameter in WbInclude is RDBMS specific (syntax).
and that is the reason I need to generate <RDBMS>_run.sql (e.g. db2_run.sql) for each RDBMS.

Regards,
 Darshak

> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbench+unsub...@googlegroups.com>.

[Thomas Kellerer]

I meant, you can use WbInclude for the generated Oracle and AS400 Scripts as well.

There is no need to create a "@foo.sql" for Oracle and "WbInclude -file=foo.sql" for DB2

[Darshak]

Hi,

Yes, Agree.

And I have modified .bat to use WbInclude for all RDBMS.

But seems there is still issue.

Sample was given for dummy test in my previous threads. But when used it for actual work, it failed with same error.

I will give more details soon after investigation.

Thanks,
 Darshak

[Darshak]

Still No Luck. :(

Even I am not using @ or wbInclude in db2_run.sql, then also I get errors.

Now i have added just plain 2 SELECTs in db2_run.sql

also, in highlighted SQL in below log, 2nd semilcolon is missing !!

-- db2_run.sql
select * from sysibm.sysdummy1;
select * from sysibm.sysdummy1;

-- Log
[2015-02-04 17:46:18,439] [INFO ] Processing file: C:\Temp\db2_run.sql
[2015-02-04 17:46:18,455] [DEBUG] Executing statement: select * from sysibm.sysdummy1;
select * from sysibm.sysdummy1;
[2015-02-04 17:46:18,471] [ERROR] Error executing:
select * from sysibm.sysdummy1;
select * from sysibm.sysdummy1

com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=;;rom sysibm.sysdummy1;INTERSECT, DRIVER=3.
57.82
        at com.ibm.db2.jcc.am.bd.a(bd.java:676)
        at com.ibm.db2.jcc.am.bd.a(bd.java:60)
        at com.ibm.db2.jcc.am.bd.a(bd.java:127)
        at com.ibm.db2.jcc.am.km.c(km.java:2506)
....



Regards,
 Darshak

[Thomas Kellerer]

What happens if you run

select * from sysibm.sysdummy1;
select * from sysibm.sysdummy1;

manually in the UI using "Execute All"

Can you run

WbInclude -file=C:\Temp\db2_run.sql;

from the UI directly?

Do you have any alternate delimiter defined globally? If yes which one?

Could you also send me your settings file? (c:\Utils\wbench\workbench.settings)

You can send me that directly to the support email if you want to.

What line ending does the file use? Windows (CR/LF) or Linux (just LF)?
Which encoding does the file use?

Regards
Thomas

Darshak schrieb am 04.02.2015 um 13:20:
>
>
> Still No Luck. :(
>
> Even I am not using @ or wbInclude in db2_run.sql, then also I get errors.
>
> Now i have added just plain 2 SELECTs in db2_run.sql
>
> also, in highlighted SQL in below log, 2nd semilcolon is missing !!
>

> *-- db2_run.sql*

> select * from sysibm.sysdummy1;
> select * from sysibm.sysdummy1;
>

> *-- Log*

> [2015-02-04 17:46:18,439] [INFO ] Processing file: C:\Temp\db2_run.sql
> [2015-02-04 17:46:18,455] [DEBUG] Executing statement: select * from sysibm.sysdummy1;
> select * from sysibm.sysdummy1;

> *[2015-02-04 17:46:18,471] [ERROR] Error executing:
> select * from sysibm.sysdummy1;
> select * from sysibm.sysdummy1*

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.

> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbenc...@googlegroups.com>.