org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"

[bragustin]

I am confounded in having the following PSQLException error everytime I SUCCESSFULLY (note: i.e., successfully) attempt to programmatically insert set of values onto a table using PostgreSQL's JDBC. In my attempt to look for the root cause, I arrived at the rather too simple an SQL INSERT statement as below which still fails to point on the underlying problem:

INSERT INTO "PUBLIC"."tblJOB_PROFILE" ( "ccJOBPROFILE_NO" ) VALUES ( '05-0038' );

org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"

I even actually tried to run that straight in PGAdmin III's Query tool to test it out. It works perfectly fine there.

Where do I go wrong programmatically? =D Here's a snippet of my code:

sIns = "INSERT INTO \"PUBLIC\".\"tblJOB_PROFILE\" " +
" ( \"ccJOBPROFILE_NO\" )" +
" VALUES ( '" + lblJobProfileCode.getText() + "' );";

int iRowCount = pStm.executeUpdate(sIns);

rs.insertRow();


- - - - -
I am using PostgreSQL ver8.0 and pg74.215.jdbc3.jar


Many many thanks in advance if you can enlighten me on this.

 

[stoolpigeon]

I don't use JDBC so I can't test it-- but have you tried running it without the double quotes around the table and field names?<BR><BR>Can the driver write to a log file so you can see exactly what it passes to the database?

 

[bragustin]

Hi,

I am not sure if JDBC has that log feature; I'll try to check it out though.

Yup, I did try--it doesn't work though.

Thank you for attending my concern.

 

[stoolpigeon]

What data type is the column ccJOBPROFILE_NO?

 

[bragustin]

Hi,

It's "ccJOBPROFILE_NO" char(7) NOT NULL

 

[bragustin]

I found the problem. I have referenced what is actually an already closed resultset object by invoking rs.insertRow();. A Statement object's executeUpdate and executeQuery methods effectively closes its currently open derivative ResultSet objects.

I am not exactly sure, but I think the generated exception error, org.postgresql.util.PSQLException: ERROR: syntax error at or near ")" , is not suggestive enough of what's really causing the problem.

StoolPigeon, thank you so much for responding.

 

[stoolpigeon]

I sort of wondered about that in your code (really) but like I said, I'm not familiar with JDBC and using it. You are right that the error is quite misleading.

Postgres is a great product-- but sometimes the stuff built around it isn't quite as high in quality or as far along. I don't fault the developers. It's great that the stuff works at all, but there are idosyncracies to learn. It's the same way with ODBC.