Set Schema not workling on postgresql DB

[figolino]

Hi I’m using jBatch on glassfish 4.1 release and I get a problem to use jBatch with postgresql DB.

The fix is simple replace prepared statement with normal SQL statement in the “JDBCPersistenceManagerImpl”

I guess this will fix also problems on MySQL Oracle …… since there are different bug reports around… on Glassfish JIRA to….

Old code PreparedStatement ps = null; ps = connection.prepareStatement("SET SCHEMA ?"); ps.setString(1, schema); ps.executeUpdate();

    ps.close();

New code

/**

• Set the default schema JBATCH or the schema defined in batch-config on the connection object.
• @param connection

• @throws SQLException */ private void setSchemaOnConnection(Connection connection) throws SQLException { logger.finest("Entering " + CLASSNAME +".setSchemaOnConnection()");

  final Statement stmt = connection.createStatement(); stmt.execute("SET SCHEMA '"+schema+"'"); stmt.close();

  logger.finest("Exiting " + CLASSNAME +".setSchemaOnConnection()"); }

Then I startet to test the new version of the jBatch FW 1.0.1 and I found some more problems on postgresql.

Only valid for latest master version!

Old code private long createRuntimeJobExecutionEntry(JobInstance jobInstance, Properties jobParameters, BatchStatus batchStatus, Timestamp timestamp) { …… …

        statement = conn.prepareStatement("INSERT INTO executioninstancedata (jobinstanceid, createtime, updatetime, batchstatus, parameters) VALUES(?, ?, ?, ?, ?)", new String[] { "JOBEXECID" });

new code note jobexecid is lover case this is only a quick fix real fix wood by to not use prepared statement here since with prepared statement in the result query the JOBEXECID is quoted “JOBEXECID” statement = conn.prepareStatement("INSERT INTO executioninstancedata (jobinstanceid, createtime, updatetime, batchstatus, parameters) VALUES(?, ?, ?, ?, ?)", new String[] { "jobexecid" });

same for to: statement = conn.prepareStatement(query, new String[] { "STEPEXECID" });

and statement = conn.prepareStatement("INSERT INTO jobinstancedata (name, apptag) VALUES(?, ?)", new String[] { "JOBINSTANCEID" } );

[figolino]

some more infos the result query on postgresql:

Not working: INSERT INTO jobinstancedata (name, apptag) VALUES('xxxxxx', 'server-config:xxxxxx-4.0-SNAPSHOT') RETURNING "JOBINSTANCEID"

Working: INSERT INTO jobinstancedata (name, apptag) VALUES('xxxxxx', 'server-config:xxxxxx-4.0-SNAPSHOT') RETURNING JOBINSTANCEID

Working to: INSERT INTO jobinstancedata (name, apptag) VALUES('xxxxxx', 'server-config:xxxxxx-4.0-SNAPSHOT') RETURNING "jobinstanceid"

[scottkurz]

Thanks for the report, @figolino.

Some related issues were also reported here: https://java.net/bugzilla/show_bug.cgi?id=5315 (a bit confusing that we have two issue trackers, I know)

Also, overlaps discussion in issue #11, where @smillidge has started down the path of subclassing the persistence service instead of having if-oracle, if-mysql, etc. logic.

At this point, I don't test with postgresql, so I'm not going to be looking into this myself. But if someone wants to send me a pull request and vouch for it working (or even go further and add test config), I'd be happy to consider adding more special-case logic, like we did for Oracle in 52a51e0fbb2b8a739e88d95d17f6ce1fb51abbac. (Please note the CLA required for contributions).

[figolino]

Ok send you the CLA

[smillidge]

@figolino you can try Payara ( a supported GlassFish) http://www.payara.co/upstream_builds the pre-release builds should have working Postgres JBatch support although it's still a work in progress.