Exporting to External System using JDBC

James T Sidebotham

Waiting to get an external system set up to allow updates via JDBC - in the meantime, if I select mode = UPDATE in the export step, does a full SQL statement get generated (to allow our DBAs to verify and QA the process).

Thanks

Henry Simms

There are a couple of ways to inspect the SQL that is generated by the JDBC driver.

In the example below I have a workflow that updates a single record into a MS SQL Server table "CustomerEmails", which just contains two columns:

| CustID | email |

Where the CustID field contains the primary key used for the UPDATE.

1. Use the Debug Connections setting on the External System

This will add additional logging to the application log (datastudio.log) showing the prepared statement that's being generated:

2024-11-13 09:33:15	237 INFO c.e.d.w.e.o.i.JDBCExportIOIterator [jobControllerSystem-job-blocking-dispatcher-35] JDBC_DEBUG: SQL statement = UPDATE "dbo"."CustomerEmails" SET "email" = ? WHERE "custID" = ?
2024-11-13 09:33:15	300 INFO c.e.d.w.e.o.i.JDBCExportIOIterator [workpool-server-lowmem-executor-closer-4] EXPORT COMPLETED IN 63ms
2024-11-13 09:33:15	302 INFO c.e.d.w.e.o.i.JDBCExportIOIterator [workpool-server-lowmem-executor-closer-4] BATCH SIZE: 1000
2024-11-13 09:33:15	302 INFO c.e.d.w.e.o.i.JDBCExportIOIterator [workpool-server-lowmem-executor-closer-4] INSERT: 0
2024-11-13 09:33:15	302 INFO c.e.d.w.e.o.i.JDBCExportIOIterator [workpool-server-lowmem-executor-closer-4] UPDATE: 1
2024-11-13 09:33:15	302 INFO c.e.d.w.e.o.i.JDBCExportIOIterator [workpool-server-lowmem-executor-closer-4] DELETE: 0

2. Configure the Spy Log

This option applies to all native "Progress" drivers are bundled with Data Studio. The spy log will give more detailed information about the queries issued by the driver. To set this up:

1. Edit the existing connection, and add a new User connection Property:
   1. Key: SpyAttributes
   2. Value: (log=(file)Spy.log;timestamp=yes)
2. This will create a Spy.log file in the installation root directory when the connection is next used (restart not needed)

The resulting log will contain a lot more information about the query being generated. In this case we can see the prepared statement being generated and the values being used for the parameters.

spy(2024-11-13T09:43:04.351)>> Connection[1].prepareStatement(String sql)
spy(2024-11-13T09:43:04.351)>> sql = UPDATE "dbo"."CustomerEmails" SET "email" = ? WHERE "custID" = ?
spy(2024-11-13T09:43:04.356)>> OK (PreparedStatement[1])

spy(2024-11-13T09:43:04.358)>> PreparedStatement[1].setString(int parameterIndex, String x)
spy(2024-11-13T09:43:04.358)>> parameterIndex = 1
spy(2024-11-13T09:43:04.358)>> x = MyUpdatedEmail@gmail.com
spy(2024-11-13T09:43:04.358)>> OK

spy(2024-11-13T09:43:04.359)>> PreparedStatement[1].setLong(int parameterIndex, long x)
spy(2024-11-13T09:43:04.359)>> parameterIndex = 2
spy(2024-11-13T09:43:04.359)>> x = 2
spy(2024-11-13T09:43:04.359)>> OK

spy(2024-11-13T09:43:04.359)>> PreparedStatement[1].addBatch()
spy(2024-11-13T09:43:04.359)>> OK

spy(2024-11-13T09:43:04.360)>> PreparedStatement[1].executeBatch()
spy(2024-11-13T09:43:04.363)>> OK ({1})

spy(2024-11-13T09:43:04.363)>> Connection[1].getAutoCommit()
spy(2024-11-13T09:43:04.363)>> OK (false)

spy(2024-11-13T09:43:04.363)>> Connection[1].commit()
spy(2024-11-13T09:43:04.364)>> OK

Note that because this log can contain sensitive information, we do not recommend using the Spy setting in production workflows.

James T Sidebotham

This is perfect, thanks so much.

James T Sidebotham

Hi @Henry Simms - do you know if its possible if the update export step can produce an equivalent of "UPDATE TABLE1 SET COL1 ='MY NAME IS HENRY' WHERE COL1='MY NAME IS JAMES'?

I have a table that does not have a PK (!), and I can use a combo of columns, but they are not unique - only way is to use the updating column as part of the key column - but cant see how that is possible.

Thanks