Thread: preprocessing of sql in the jdbc driver

preprocessing of sql in the jdbc driver

From
"Andrea Nasato"
Date:
Hi to all,

I'm developing a standard java web application using postgres as dbms.
I'm using postgres 8.2.4 on RHEL and the 8.2-504 jdbc driver

I use an mda tool which generates both java and sql code. The problem
is that the sql code generated is not so compliant with postgres
syntax.

Specifically, the SELECT ... FOR UPDATE statement is generated with this syntax:

SELECT T1.column_1, ..., T1.column_N
FROM table_name AS T1
WHERE ...
FOR UPDATE OF T1.column_1, ..., T1.column_n

instead of

SELECT T1.column_1, ..., T1.column_N
FROM table_name AS T1
WHERE ...
FOR UPDATE

So the generator qualifies columns in the FOR UPDATE clause. When I
submit this SQL to the dbms I receive a syntax error:

ERROR:  syntax error at or near "." at character 119
LINE 5: FOR UPDATE OF T1.TABLENAME, T1.SURROGATE

We can't patch the generator (we don't have the code!) but we can
modify the jdbc driver to insert a sort of preprocessing of the sql in
order to adjust the syntax.

So my question is, which is the right place to do such operation? I
analyzed the call stack and I've found that the method called before
the execution of "for update" queries is
org.postgresql.jdbc3.Jdbc3Connection.prepareStatement(String , int ,
int , int ).

Maybe I can pre process the sql string in this method, before the
construction of the PreparedStatement. Is this correct?

Thank you for yor attention and thanks in advance

Andrea