Thread: Batch Inserts: Bug in 9.4.1208? Wrong binary data format

Batch Inserts: Bug in 9.4.1208? Wrong binary data format

From

"Merder, Markus"

Date:

16 June 2016, 14:06:18

Hello,

we are making use of batch inserts in ordert o fill our database at deployment time. When upgrading to version 9.4.1208 (from 9.3-1104-jdbc41) we encountered an error message at some lines: “org.postgresql.util.PSQLException: FEHLER: falsches Binärdatenformat in Binden-Parameter 23”

Removing the affected lines in the source file didn’t solve the problem and after some investigation I found out, that a workaround is to use PreparedStatment. setObject(int parameterIndex, Object x, int sqlType) instead of setObject(int parameterIndex, Object x). I know this method is the recommended one anyway, but I still believe it is an unknown bug in 9.4.1208. because it was working in 9.3.

Please see my attached maven testcase to reproduce this error. If you change the driver-version in pom.xml, it is going to work.

My PG-server: PostgreSQL 9.4.0, compiled by Visual C++ build 1800, 64-bit

Stacktrace:

java.sql.BatchUpdateException: Batch-Eintrag 0 INSERT INTO "sos_lab_pord" (matrikel_nr, labnr, alter, geschlecht, ca12_staat, hzbart, ch30_fach, ch39_vertief, ch35_ang_abschluss, pversion, schwerpunkt, kz_fach, stort, tid_stg, sem_der_pruefung, d_abg_pruefung, note, abschnitt, fach_sem_zahl, pstatus, prueck, pvermerk, pnr, zeitabschnitt, pform, pordnr, part, schriftlich, ppflicht, modulart, bonus_pord, summe, pktxt, pversuch, pseudonym, studiengang_nr, fach_nr, geburtsjahr, hmkfzkz, hmkfz, semkfzkz, semkfz, hzbkfzkz, hzbkfz, hrst, kz_rueck_beur_ein, klinsem, hssem, hzbart_int, ch27_grund_beurl, ch62_grund_exmatr, hzbnote, d_exmatr, stufrm, ch35_ang_abschluss_stg, ch30_fach_stg, ch39_vertief_stg, schwerpunkt_stg, kz_fach_stg, pversion_stg, sem_einschreibung, sem_1fs, pdum, sem_wechsel, ch35_ang_abschluss_einschr, ch30_fach_einschr, ch35_ang_abschluss_1fs, ch30_fach_1fs, fachsem_beurl_einschr, fachsem_beurl_1fs, sourcesystem) VALUES (1.1208286E7, 2378.0, 19.0, 2.0, 0.0, 1.0, 'IEM', 'DEFAULT', 'MS', 5.0, 'DEFAULT', 'H', 'DEFAULT', 50.0, 20152.0, '2015-10-1 +2:0:0', 3.1, 2.0, 1.0, 'BE', 0.0, NULL, 1.0, NULL, NULL, 2969.0, NULL, NULL, NULL, 'K', NULL, 1.0, NULL, NULL, NULL, 1.0, 1.0, 1995.0, 0.0, 3252.0, 0.0, -9999.0, 0.0, 5334.0, 'H', 1.0, NULL, 1.0, '03', NULL, NULL, NULL, NULL, '7', 'MS', 'IEM', 'DEFAULT', 'DEFAULT', 'H', 5.0, 20152.0, 20152.0, NULL, NULL, 'MS', 'IEM', 'MS', 'IEM', 0.0, 0.0, 6.0) wurde abgebrochen. Rufen Sie 'getNextException' auf, um die Ursache zu erfahren.

at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:136)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2004)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:360)

at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:1019)

at de.adesso.his.pgbug.TestDataLoaderPlain.executeBatchInsert(TestDataLoaderPlain.java:257)

at de.adesso.his.pgbug.TestDataLoaderPlain.loadTableDataFromFile(TestDataLoaderPlain.java:154)

at de.adesso.his.pgbug.TestDataLoaderPlain.testLoad(TestDataLoaderPlain.java:97)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at junit.framework.TestCase.runTest(TestCase.java:176)

at junit.framework.TestCase.runBare(TestCase.java:141)

at junit.framework.TestResult$1.protect(TestResult.java:122)

at junit.framework.TestResult.runProtected(TestResult.java:142)

at junit.framework.TestResult.run(TestResult.java:125)

at junit.framework.TestCase.run(TestCase.java:129)

at junit.framework.TestSuite.runTest(TestSuite.java:255)

at junit.framework.TestSuite.run(TestSuite.java:250)

at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:131)

at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)

2016-06-06 14:55:51,103 ERROR - org.postgresql.util.PSQLException: FEHLER: falsches Binärdatenformat in Binden-Parameter 23

org.postgresql.util.PSQLException: FEHLER: falsches Binärdatenformat in Binden-Parameter 23

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2284)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2003)