SymmetricDS / Discussion / Help: dbexport SQL format and NULL characters

Rick Otten - 2012-11-08

It looks like the SQL format output from dbexport isn't processing NULL values very well. The insert statements will put the next column value in there, and then the last few column values are replaced with "?" question marks.

ie, instead of:

insert into mytable (col1, col2, col3, col4) values (null, 'test', 'value3', 'value4');

it will generate

insert into mytable (col1, col2, col3, col4) values ('test', 'value3', 'value4', ?);

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Chris Henson - 2012-11-08

What is the column type that is having issues?

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Rick Otten - 2012-11-08

Using the test described in this thread:
https://sourceforge.net/p/symmetricds/discussion/739236/thread/81b81271/

exporting from Oracle, using release 3.1.9 ...

The insert statements look like this:

insert into "ROTTEN"."DBEXPORT_TEST" ("ID", "NUMBER1", "STRING1", "TIME1", "STRING2", "NUMBER2", "STRING3", "TIME2", "NUMBER3", "TIME3", "TIME4", "STRING4", "NUMBER4", "NUMBER5") values (1001,1001,'First row of test data.',TO_TIMESTAMP_TZ('row one. string 2.', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),365,null,12500,TO_TIMESTAMP_TZ(null, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),'more stuff',TO_TIMES
TAMP_TZ(2, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),TO_TIMESTAMP_TZ(1, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),?,?,?);

insert into "ROTTEN"."DBEXPORT_TEST" ("ID", "NUMBER1", "STRING1", "TIME1", "STRING2", "NUMBER2", "STRING3", "TIME2", "NUMBER3", "TIME3", "TIME4", "STRING4", "NUMBER4", "NUMBER5") values (3912,11434,'Second row of test data.',TO_TIMESTAMP_TZ('row two, string 2', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),31,null,24900,TO_TIMESTAMP_TZ(null, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),null,TO_TIMESTAMP_TZ(
'good data', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),TO_TIMESTAMP_TZ(2, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),null,?,?);

insert into "ROTTEN"."DBEXPORT_TEST" ("ID", "NUMBER1", "STRING1", "TIME1", "STRING2", "NUMBER2", "STRING3", "TIME2", "NUMBER3", "TIME3", "TIME4", "STRING4", "NUMBER4", "NUMBER5") values (11434,11434,'Third row of test data.',TO_TIMESTAMP_TZ('row three, string 2', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),31,null,9900,TO_TIMESTAMP_TZ(null, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),null,TO_TIMESTAMP_TZ
('test-test', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),TO_TIMESTAMP_TZ(2, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),null,?,?);

So it looks like the problem is with the timestamp columns...

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Chris Henson - 2012-11-08

Try this version:

http://snapshots.repository.codehaus.org/org/jumpmind/symmetric/symmetric-assemble/3.1.10-SNAPSHOT/symmetric-assemble-3.1.10-20121108.210825-10-server.zip

I entered a bug:
http://www.symmetricds.org/issues/view.php?id=903

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Rick Otten - 2012-11-08

The snapshot works better.
The same command line export from the same table as above yields:

insert into "ROTTEN"."DBEXPORT_TEST" ("ID", "NUMBER1", "STRING1", "TIME1", "STRING2", "NUMBER2", "STRING3", "TIME2", "NUMBER3", "TIME3", "TIME4", "STRING4", "NUMBER4", "NUMBER5") values (1001,1001,'First row of test data.',TO_TIMESTAMP_TZ(2010-01-26 16:14:13.000000 -04:00, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),'row one. string 2.',365,null,TO_TIMESTAMP_TZ(2010-01-26 16:14:13.000000 -04:00, 'YYYY-MM-DD HH24:MI:SS.FF TZH
:TZM'),12500,TO_TIMESTAMP_TZ(2011-02-04 17:06:23.000000 -04:00, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),TO_TIMESTAMP_TZ(null, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),'more stuff',2,1);

insert into "ROTTEN"."DBEXPORT_TEST" ("ID", "NUMBER1", "STRING1", "TIME1", "STRING2", "NUMBER2", "STRING3", "TIME2", "NUMBER3", "TIME3", "TIME4", "STRING4", "NUMBER4", "NUMBER5") values (3912,11434,'Second row of test data.',TO_TIMESTAMP_TZ(2010-06-04 18:17:26.000000 -04:00, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),'row two, string 2',31,null,TO_TIMESTAMP_TZ(2010-06-04 18:17:26.000000 -04:00, 'YYYY-MM-DD HH24:MI:SS.FF TZH
:TZM'),24900,TO_TIMESTAMP_TZ(null, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),TO_TIMESTAMP_TZ(null, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),'good data',2,null);

insert into "ROTTEN"."DBEXPORT_TEST" ("ID", "NUMBER1", "STRING1", "TIME1", "STRING2", "NUMBER2", "STRING3", "TIME2", "NUMBER3", "TIME3", "TIME4", "STRING4", "NUMBER4", "NUMBER5") values (11434,11434,'Third row of test data.',TO_TIMESTAMP_TZ(2010-09-29 21:52:06.000000 -04:00, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),'row three, string 2',31,null,TO_TIMESTAMP_TZ(2010-09-29 21:52:06.000000 -04:00, 'YYYY-MM-DD HH24:MI:SS.FF T
ZH:TZM'),9900,TO_TIMESTAMP_TZ(null, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),TO_TIMESTAMP_TZ(null, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),'test-test',2,null);

Should there be single quotes around the timestamp values for completeness?

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Chris Henson - 2012-11-08

http://snapshots.repository.codehaus.org/org/jumpmind/symmetric/symmetric-assemble/3.1.10-SNAPSHOT/symmetric-assemble-3.1.10-20121108.215454-11-server.zip

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Rick Otten - 2012-11-09

Much better! Thanks for the amazingly quick turn around!

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Rick Otten - 2012-11-09

Ok, well, sort of better (solved the first problem I posted)...

I get the same result for the insert statement whether I export with --compatible postgresql, oracle, or mysql.

Unfortunately TO_TIMESTAMP_TZ() is unique to Oracle.

PostgreSQL has TO_TIMESTAMP(), and it appears MySQL doesn't even have that.

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Chris Henson - 2012-11-09

I see the issue. I'll work on a fix.

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Chris Henson - 2012-11-09

New version: http://snapshots.repository.codehaus.org/org/jumpmind/symmetric/symmetric-assemble/3.1.10-SNAPSHOT/symmetric-assemble-3.1.10-20121109.202045-13-server.zip

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Rick Otten - 2012-11-15

Finally got a chance to test that build... (The flu took me out of commission for a few days, sorry about that.) Anyway:
--compatible oracle looks ok.
--compatible postgresql throws a missing parenthesis exception
--compatible mysql throws an invalid character exception

--compatible postgresql:

java.sql.SQLSyntaxErrorException: ORA-00906: missing left parenthesis

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1281) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3628) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1493) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at org.jumpmind.db.sql.JdbcSqlReadCursor.<init>(JdbcSqlReadCursor.java:58)

[wrapped] org.jumpmind.db.sql.SqlException: select "ID", "NUMBER1", "STRING1", case when extract(timezone_hour from "TIME1") < -9 then to_char("TIME1", 'YYYY-MM-DD HH24:MI:SS.US ')|| lpad(cast(extract(timezone_hour from "TIME1") as varchar),3,'0')||':'|| lpad(cast(extract(timezone_minute from "TIME1") as varchar), 2, '0') when extract(timezone_hour from "TIME1") >= 0 then to_char("TIME1", 'YYYY-MM-DD HH24:MI:SS.US ')||'+'|| lpad(cast(extract(timezone_hour from "TIME1") as varchar),2,'0')||':'|| lpad(cast(extract(timezone_minute from "TIME1") as varchar), 2, '0') else to_char("TIME1", 'YYYY-MM-DD HH24:MI:SS.US ')|| lpad(cast(extract(timezone_hour from "TIME1") as varchar),2,'0')||':'|| lpad(cast(extract(timezone_minute from "TIME1") as varchar), 2, '0') end as TIME1, "STRING2", "NUMBER2", "STRING3", case when extract(timezone_hour from "TIME2") < -9 then to_char("TIME2", 'YYYY-MM-DD HH24:MI:SS.US ')|| lpad(cast(extract(timezone_hour from "TIME2") as varchar),3,'0')||':'|| lpad(cast(extract(timezone_minute from "TIME2") as varchar), 2, '0') when extract(timezone_hour from "TIME2") >= 0 then to_char("TIME2", 'YYYY-MM-DD HH24:MI:SS.US ')||'+'|| lpad(cast(extract(timezone_hour from "TIME2") as varchar),2,'0')||':'|| lpad(cast(extract(timezone_minute from "TIME2") as varchar), 2, '0') else to_char("TIME2", 'YYYY-MM-DD HH24:MI:SS.US ')|| lpad(cast(extract(timezone_hour from "TIME2") as varchar),2,'0')||':'|| lpad(cast(extract(timezone_minute from "TIME2") as varchar), 2, '0') end as TIME2, "NUMBER3", case when extract(timezone_hour from "TIME3") < -9 then to_char("TIME3", 'YYYY-MM-DD HH24:MI:SS.US ')|| lpad(cast(extract(timezone_hour from "TIME3") as varchar),3,'0')||':'|| lpad(cast(extract(timezone_minute from "TIME3") as varchar), 2, '0') when extract(timezone_hour from "TIME3") >= 0 then to_char("TIME3", 'YYYY-MM-DD HH24:MI:SS.US ')||'+'|| lpad(cast(extract(timezone_hour from "TIME3") as varchar),2,'0')||':'|| lpad(cast(extract(timezone_minute from "TIME3") as varchar), 2, '0') else to_char("TIME3", 'YYYY-MM-DD HH24:MI:SS.US ')|| lpad(cast(extract(timezone_hour from "TIME3") as varchar),2,'0')||':'|| lpad(cast(extract(timezone_minute from "TIME3") as varchar), 2, '0') end as TIME3, case when extract(timezone_hour from "TIME4") < -9 then to_char("TIME4", 'YYYY-MM-DD HH24:MI:SS.US ')|| lpad(cast(extract(timezone_hour from "TIME4") as varchar),3,'0')||':'|| lpad(cast(extract(timezone_minute from "TIME4") as varchar), 2, '0') when extract(timezone_hour from "TIME4") >= 0 then to_char("TIME4", 'YYYY-MM-DD HH24:MI:SS.US ')||'+'|| lpad(cast(extract(timezone_hour from "TIME4") as varchar),2,'0')||':'|| lpad(cast(extract(timezone_minute from "TIME4") as varchar), 2, '0') else to_char("TIME4", 'YYYY-MM-DD HH24:MI:SS.US ')|| lpad(cast(extract(timezone_hour from "TIME4") as varchar),2,'0')||':'|| lpad(cast(extract(timezone_minute from "TIME4") as varchar), 2, '0') end as TIME4, "STRING4", "NUMBER4", "NUMBER5" from "ROTTEN"."DBEXPORT_TEST"
at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:260)
at org.jumpmind.db.sql.JdbcSqlReadCursor.<init>(JdbcSqlReadCursor.java:70)
at org.jumpmind.db.sql.JdbcSqlTemplate.queryForCursor(JdbcSqlTemplate.java:92)
at org.jumpmind.db.sql.AbstractSqlTemplate.query(AbstractSqlTemplate.java:159)
at org.jumpmind.db.sql.AbstractSqlTemplate.query(AbstractSqlTemplate.java:154)
at org.jumpmind.db.sql.AbstractSqlTemplate.query(AbstractSqlTemplate.java:103)
at org.jumpmind.symmetric.DbExport.writeTable(DbExport.java:219)
at org.jumpmind.symmetric.DbExport.exportTables(DbExport.java:183)
at org.jumpmind.symmetric.DbExport.exportTables(DbExport.java:162)
at org.jumpmind.symmetric.DbExport.exportTables(DbExport.java:152)
at org.jumpmind.symmetric.DbExportCommand.executeWithOptions(DbExportCommand.java:170)
at org.jumpmind.symmetric.AbstractCommandLauncher.execute(AbstractCommandLauncher.java:130)
at org.jumpmind.symmetric.DbExportCommand.main(DbExportCommand.java:69)

--compatible mysql:

CREATE TABLE DBEXPORT_TEST
(
ID DECIMAL(10,0) NOT NULL,
NUMBER1 DECIMAL(10,0),
STRING1 VARCHAR(200) NOT NULL,
TIME1 TIMESTAMPTZ NOT NULL,
STRING2 VARCHAR(50) NOT NULL,
NUMBER2 DECIMAL(10,0) NOT NULL,
STRING3 VARCHAR(25) NULL,
TIME2 TIMESTAMPTZ,
NUMBER3 DECIMAL(10,0) NOT NULL,
TIME3 TIMESTAMPTZ,
TIME4 TIMESTAMPTZ,
STRING4 VARCHAR(200) NOT NULL,
NUMBER4 DECIMAL(10,0) NOT NULL,
NUMBER5 INTEGER,
PRIMARY KEY (ID)
);
java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1281) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3628) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1493) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at org.jumpmind.db.sql.JdbcSqlReadCursor.<init>(JdbcSqlReadCursor.java:58)

[wrapped] org.jumpmind.db.sql.SqlException: select ID, NUMBER1, STRING1, TIME1, STRING2, NUMBER2, STRING3, TIME2, NUMBER3, TIME3, TIME4, STRING4, NUMBER4, NUMBER5 from ROTTEN.DBEXPORT_TEST
at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:260)
at org.jumpmind.db.sql.JdbcSqlReadCursor.<init>(JdbcSqlReadCursor.java:70)
at org.jumpmind.db.sql.JdbcSqlTemplate.queryForCursor(JdbcSqlTemplate.java:92)
at org.jumpmind.db.sql.AbstractSqlTemplate.query(AbstractSqlTemplate.java:159)
at org.jumpmind.db.sql.AbstractSqlTemplate.query(AbstractSqlTemplate.java:154)
at org.jumpmind.db.sql.AbstractSqlTemplate.query(AbstractSqlTemplate.java:103)
at org.jumpmind.symmetric.DbExport.writeTable(DbExport.java:219)
at org.jumpmind.symmetric.DbExport.exportTables(DbExport.java:183)
at org.jumpmind.symmetric.DbExport.exportTables(DbExport.java:162)
at org.jumpmind.symmetric.DbExport.exportTables(DbExport.java:152)
at org.jumpmind.symmetric.DbExportCommand.executeWithOptions(DbExportCommand.java:170)
at org.jumpmind.symmetric.AbstractCommandLauncher.execute(AbstractCommandLauncher.java:130)
at org.jumpmind.symmetric.DbExportCommand.main(DbExportCommand.java:69)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Chris Henson - 2012-11-21

I've been working on dbimport and dbexport. The latest snapshot from here should work (the double backslash problem with the csv export has been fixed as well):

http://snapshots.repository.codehaus.org/org/jumpmind/symmetric/symmetric-assemble/3.1.10-SNAPSHOT/

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

dbexport SQL format and NULL characters

Database Replication, Filtered Synchronization, and Transformation

Forums

Help

dbexport SQL format and NULL characters

dbexport SQL format and NULL characters

Database Replication, Filtered Synchronization, and Transformation

Forums

Help

dbexport SQL format and NULL characters document.SUBSCRIPTION_OPTIONS = { "thing": "topic", "subscribed": false, "url": "subscribe", "icon": { "css": "fa fa-envelope-o" } };

dbexport SQL format and NULL characters