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 52a51e0. (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.