Bad SQL Generated?

Aaron de Bruyn

unread,

Aug 1, 2015, 4:49:25 PM8/1/15

to SQL Workbench/J - DBMS independent SQL tool

I use sqlworkbench to sync data from a Microsoft SQL server to a PostgreSQL server.

I have a large collection of Linux tools that generate statistics and automate processes based on the data--but it's a huge pain to have them connect to MSSQL, hence the sync.

Every five minutes, I fire off a cron job to run this through sqlwbconsole:

wbcopy -syncDelete=true -sourceProfile=CW -targetProfile=CWDUMP -showProgress=true -sourceSchema=dbo -targetSchema=public -createTarget=false -continueOnError=true -sourceTable=Company_Address -mode=update,insert -useSavepoint=true -keyColumns=Company_RecID

The output is an error:

0 row(s) inserted

0 row(s) updated

ERROR: syntax error at or near "FROM"

Position: 9 [SQL State=42601]

SQL execution time: 2.32s

1 statement executed.

Execution time: 2.32s

I get the same thing when I run it from the sqlworkbench GUI

Turning on PostgresSQL query logging shows this:

2015-08-01 13:40:20 PDT ERROR: syntax error at or near "FROM" at character 9

2015-08-01 13:40:20 PDT STATEMENT: SELECT FROM company_address

Notice 'SELECT FROM'. I'm guessing it's supposed to be 'SELECT * FROM' or maybe a list of columns.

This affects about half of the tables I am trying to sync.

Attached are screenshots of the two profiles 'CW' and 'CWDUMP'.

Any ideas what I might be doing wrong?

Thanks,

-A

Screenshot from 2015-08-01 13:47:17.png

Screenshot from 2015-08-01 13:47:20.png

Aaron de Bruyn

unread,

Aug 1, 2015, 4:50:28 PM8/1/15

to SQL Workbench/J - DBMS independent SQL tool

I should probably also mention that I'm running build 117 (2015-01-04 21:00).

Aaron de Bruyn

unread,

Aug 1, 2015, 5:52:51 PM8/1/15

to SQL Workbench/J - DBMS independent SQL tool

After messing around for a bit, I have found that removing the -syncDelete=true option fixes the issue.

Unfortunately, deleted rows are left around now.

Any pointers on how I can solve this?

Thanks,

-A

Thomas Kellerer

unread,

Aug 2, 2015, 3:28:57 AM8/2/15

to sql-wo...@googlegroups.com

Please send me the Workbench logfile. Without that it's hard to tell what's going on.

The CREATE TABLE statements for both tables (SQL Server and Postgres) would be helpful as well.

Thomas

Aaron de Bruyn

unread,

Aug 5, 2015, 8:20:46 PM8/5/15

to SQL Workbench/J - DBMS independent SQL tool

On Sunday, August 2, 2015 at 12:28:57 AM UTC-7, Thomas Kellerer wrote:

Please send me the Workbench logfile. Without that it's hard to tell what's going on.

The CREATE TABLE statements for both tables (SQL Server and Postgres) would be helpful as well.

Thomas

Here's an example with the Company_Address table:

wbcopy -syncDelete=true -sourceProfile=CW -targetProfile=CWDUMP -showProgress=true -sourceSchema=dbo -targetSchema=public -createTarget=true -continueOnError=true -sourceTable=Company_Address -mode=update,insert -useSavepoint=true -keyColumns=Company_Address_RecID;

Log snippet:

2015-08-05 17:05 INFO Creating table using sql: CREATE TABLE company_address

(

Owner_ID integer,

Company_Address_RecID integer NOT NULL,

Company_RecID integer NOT NULL,

Company_Name varchar,

Address_Line1 varchar,

Address_Line2 varchar,

City varchar,

State_ID varchar,

Zip varchar,

Country varchar,

PhoneNbr varchar,

PhoneNbr_Fax varchar,

Default_Flag boolean NOT NULL,

Default_Mail_Flag boolean NOT NULL,

Default_Ship_Flag boolean NOT NULL,

Default_Bill_Flag boolean NOT NULL,

Last_Update timestamp,

Updated_By varchar,

Description varchar,

tax_code_recid integer,

EX_Reimb DECIMAL,

Inactive_Flag boolean,

Country_RecID integer

)

2015-08-05 17:05 INFO Statement for insert: INSERT INTO public.company_address (inactive_flag,tax_code_recid,zip,default_bill_flag,company_recid,country_recid,phonenbr_fax,country,city,default_ship_flag,state_id,last_update,default_mail_flag,address_line1,address_line2,phonenbr,company_name,description,owner_id,updated_by,ex_reimb,default_flag,company_address_recid) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

2015-08-05 17:05 INFO Statement for update: UPDATE public.company_address SET inactive_flag = ?, tax_code_recid = ?, zip = ?, default_bill_flag = ?, company_recid = ?, country_recid = ?, phonenbr_fax = ?, country = ?, city = ?, default_ship_flag = ?, state_id = ?, last_update = ?, default_mail_flag = ?, address_line1 = ?, address_line2 = ?, phonenbr = ?, company_name = ?, description = ?, owner_id = ?, updated_by = ?, ex_reimb = ?, default_flag = ? WHERE company_address_recid = ?

2015-08-05 17:05 INFO Starting import for table public.company_address

2015-08-05 17:05 INFO company_address: 394 row(s) inserted. 0 row(s) updated. Committing changes.

Changing -createTarget to false:

wbcopy -syncDelete=true -sourceProfile=CW -targetProfile=CWDUMP -showProgress=true -sourceSchema=dbo -targetSchema=public -createTarget=false -continueOnError=true -sourceTable=Company_Address -mode=update,insert -useSavepoint=true -keyColumns=Company_RecID

Full log:

2015-08-05 17:16 INFO =================== Log started ===================

2015-08-05 17:16 INFO Using configdir: /root/.sqlworkbench

2015-08-05 17:16 INFO Setting default locale to: en

2015-08-05 17:16 INFO Starting SQL Workbench/J, Build 117 (2015-01-04 21:00)

2015-08-05 17:16 INFO Java version=1.7.0_79, java.home=/usr/lib/jvm/java-7-openjdk-amd64/jre, vendor=Oracle Corporation, name=OpenJDK 64-Bit Server VM

2015-08-05 17:16 INFO Operating System=Linux, version=3.13.0-57-generic, platform=amd64

2015-08-05 17:16 INFO Available memory: 482MB

2015-08-05 17:16 INFO Starting DeadlockMonitor

2015-08-05 17:20 INFO Creating new connection for [{Default group}/CWDUMP] for driver=org.postgresql.Driver and URL=[jdbc:postgresql://tetrad.utilizeit.com/cwdump?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory]

2015-08-05 17:20 INFO Adding ClassLoader URL=file:/root/sqlworkbench/postgresql-9.3-1102.jdbc41.jar

2015-08-05 17:20 INFO Using DBID=postgresql

2015-08-05 17:20 INFO Using identifier quote character: "

2015-08-05 17:20 INFO Using search string escape character: \

2015-08-05 17:20 INFO Using configured table types: [TABLE, TEMPORARY TABLE, FOREIGN TABLE]

2015-08-05 17:20 INFO Using catalog separator: .

2015-08-05 17:20 INFO Connected to: [PostgreSQL], Database version: [9.3.9], Driver version: [PostgreSQL 9.3 JDBC4.1 (build 1102)], JDBC Version: [4.0], ID: [Wb1-2]

2015-08-05 17:20 INFO Creating new connection for [{Default group}/CW] for driver=com.microsoft.sqlserver.jdbc.SQLServerDriver and URL=[jdbc:sqlserver://connect.utilizeit.com]

2015-08-05 17:20 INFO Adding ClassLoader URL=file:/root/sqlworkbench/sqljdbc4.jar

2015-08-05 17:20 INFO Using DBID=microsoft_sql_server

2015-08-05 17:20 INFO Using identifier quote character: "

2015-08-05 17:20 INFO Using search string escape character: \

2015-08-05 17:20 INFO Using catalog separator: .

2015-08-05 17:20 INFO Connected to: [Microsoft SQL Server], Database version: [9.00.5069], Driver version: [4.0.2206.100], JDBC Version: [4.0], ID: [$Wb-Copy$-Source-1$]

2015-08-05 17:20 INFO Using savepoints for DML: true

2015-08-05 17:20 INFO Statement for insert: INSERT INTO public.company_address (inactive_flag,tax_code_recid,zip,default_bill_flag,company_recid,country_recid,phonenbr_fax,country,city,default_ship_flag,state_id,last_update,default_mail_flag,address_line1,address_line2,phonenbr,company_name,description,owner_id,updated_by,ex_reimb,default_flag,company_address_recid) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

2015-08-05 17:20 INFO Statement for update: UPDATE public.company_address SET inactive_flag = ?, tax_code_recid = ?, zip = ?, default_bill_flag = ?, company_recid = ?, country_recid = ?, phonenbr_fax = ?, country = ?, city = ?, default_ship_flag = ?, state_id = ?, last_update = ?, default_mail_flag = ?, address_line1 = ?, address_line2 = ?, phonenbr = ?, company_name = ?, description = ?, owner_id = ?, updated_by = ?, ex_reimb = ?, default_flag = ? WHERE company_address_recid = ?

2015-08-05 17:20 INFO Starting import for table public.company_address

2015-08-05 17:20 INFO company_address: 0 row(s) inserted. 394 row(s) updated. Committing changes.

2015-08-05 17:20 WARN No primary key found to delete rows from target table company_address

2015-08-05 17:20 ERROR Error when copying data ERROR: syntax error at or near "FROM"

Position: 9 [SQL State=42601]

org.postgresql.util.PSQLException: ERROR: syntax error at or near "FROM"

Position: 9

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:405)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:285)

at workbench.db.compare.TableDeleteSync.doSync(TableDeleteSync.java:285)

at workbench.db.datacopy.DataCopier.startCopy(DataCopier.java:586)

at workbench.sql.wbcommands.TableCopy.copyData(TableCopy.java:64)

at workbench.sql.wbcommands.WbCopy.execute(WbCopy.java:278)

at workbench.sql.StatementRunner.runStatement(StatementRunner.java:543)

at workbench.gui.sql.SqlPanel.displayResult(SqlPanel.java:3247)

at workbench.gui.sql.SqlPanel.runStatement(SqlPanel.java:2101)

at workbench.gui.sql.SqlPanel$14.run(SqlPanel.java:2052)

2015-08-05 17:20 INFO Rollback changes

2015-08-05 17:20 ERROR SQL Error when copying data ERROR: syntax error at or near "FROM"