Data Insertion Error with table created under DB23 AI database and Quartz 2.4.0 - QRTZ92_JOB_DETAILS

[mailforlalit]

Hi Team,

Problem Statement :

Error Message = ORA-12899: value too large for column "<<SCHEMA_NAME>>"."QRTZ92_JOB_DETAILS"."IS_DURABLE" (actual: 4, maximum: 1)

Description :

I am using Quartz2.4.0 version and while we have bootstrap process running which seed data in the QRTZ92_JOB_DETAILS table with a schema in DB23 AI database. It fail to insert data for the column who values set from setBoolean() and fail with ORA-12899: value too large for column XXXXX.

 at org.quartz.impl.jdbcjobstore.StdJDBCDelegate.insertJobDetail(StdJDBCDelegate.java:625)
       at org.quartz.impl.jdbcjobstore.JobStoreSupport.storeJob(JobStoreSupport.java:1117)

Caused by: Error : 12899, Position : 220, SQL = INSERT INTO QRTZ92_JOB_DETAILS (SCHED_NAME, JOB_NAME, JOB_GROUP, DESCRIPTION, JOB_CLASS_NAME, IS_DURABLE, IS_NONCONCURRENT, IS_UPDATE_DATA, REQUESTS_RECOVERY, JOB_DATA) VALUES('OIMQuartzScheduler', :1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 ), Original SQL = INSERT INTO QRTZ92_JOB_DETAILS (SCHED_NAME, JOB_NAME, JOB_GROUP, DESCRIPTION, JOB_CLASS_NAME, IS_DURABLE, IS_NONCONCURRENT, IS_UPDATE_DATA, REQUESTS_RECOVERY, JOB_DATA) VALUES('OIMQuartzScheduler', ?, ?, ?, ?, ?, ?, ?, ?, ?), Error Message = ORA-12899: value too large for column "<<SCHEMA_NAME>>"."QRTZ92_JOB_DETAILS"."IS_DURABLE" (actual: 4, maximum: 1)

Existing code in Quartz2.4.0 for insertingJobDetails :

public int insertJobDetail(Connection conn, JobDetail job) throws IOException, SQLException {
    ByteArrayOutputStream baos = serializeJobData(job.getJobDataMap());
    PreparedStatement ps = null;
    int insertResult = 0;
    try {
      ps = conn.prepareStatement(rtp("INSERT INTO {0}JOB_DETAILS (SCHED_NAME, JOB_NAME, JOB_GROUP, DESCRIPTION, JOB_CLASS_NAME, IS_DURABLE, IS_NONCONCURRENT, IS_UPDATE_DATA, REQUESTS_RECOVERY, JOB_DATA)  VALUES({1}, ?, ?, ?, ?, ?, ?, ?, ?, ?)"));
      ps.setString(1, job.getKey().getName());
      ps.setString(2, job.getKey().getGroup());
      ps.setString(3, job.getDescription());
      ps.setString(4, job.getJobClass().getName());
      setBoolean(ps, 5, job.isDurable());
      setBoolean(ps, 6, job.isConcurrentExectionDisallowed());
      setBoolean(ps, 7, job.isPersistJobDataAfterExecution());
      setBoolean(ps, 8, job.requestsRecovery());
      setBytes(ps, 9, baos);
      insertResult = ps.executeUpdate();
    } finally {
      closeStatement(ps);
    } 
    return insertResult;
  }

Similar use-case worked with Quartz2.4.0 and with schema created under DB19c.

Could you please advise solution (or other api to populate data in QRTZ92_JOB_DETAILS ) to overcome above sited usecase ? ( how this Quartz2.4.0 can be used with schema that's created with DB23 AI or any alternative solution).

Thanks,
Lalit Gupta

[mailforlalit]

Got solution for above issue.

We can use a DB23 API parameter "oracle.jdbc.sendBooleanAsNativeBoolean" set as false to support backward feature.

This property can be set with connection as below.

 Properties props = new Properties();
  	props.setProperty("user", "DBUSER");
  	props.setProperty("password", "DBPWS");
  	props.setProperty("oracle.jdbc.sendBooleanAsNativeBoolean", "false");		
  	Class.forName("oracle.jdbc.OracleDriver");
  	Connection con = DriverManager.getConnection("jdbc:oracle:thin:@DBHOST:DBPORT/DBSERVICE", props);

This connection can be used and there will not be any error.