Postgres Pro
Downloads
Home   >   mailing lists

Thread: JDBC Update question (quoted strings)

JDBC Update question (quoted strings)

From
Gerry Gattis
Date:
I had to make an unexpected adjustment in a query string in order to insert a record into a table.  I was would like to know if anyone has a remedy for this.  Here is a short description.

String strInsert = "insert into myTable (lastName) values (?)";
PreparedStatement stm = conn.prepareCall(strInsert);
stm.setString(1, "Smith");
// OK so far
stm.execute();
//fails with an error message attribute Smith not found

If I use a Statement and manually construct the query string so it contains the single quotes as follows:
String strInsert = "insert into myTable (lastName) values ('Smith')";
Statement stm = conn.getStatement();
stm.execute(strInsert);

It works like a charm. 

It looks like there is a difference in the way the PostGres driver handles Strings. I have been through several of the documents on the web site, but have not seen this addressed in any of the configuration articles.
The app I am working on needs to work with different RDBMS products. I'm sure I am not the first person to want to do this. Is there a trick to it with PostGres?

Best regards,
Gerry


Re: JDBC Update question (quoted strings)

From
"David Wall"
Date:

Why are you using conn.prepareCall() instead of conn.prepareStatement()?  If you did, it should certainly work as you originally did it.  CallableStatements are different (I don't use them much, but they are for stored procedures, not flat SQL statements).

David
----- Original Message -----
Sent: Tuesday, August 31, 2004 7:59 PM
Subject: [JDBC] JDBC Update question (quoted strings)

I had to make an unexpected adjustment in a query string in order to insert a record into a table.  I was would like to know if anyone has a remedy for this.  Here is a short description.

String strInsert = "insert into myTable (lastName) values (?)";
PreparedStatement stm = conn.prepareCall(strInsert);
stm.setString(1, "Smith");
// OK so far
stm.execute();
//fails with an error message attribute Smith not found

If I use a Statement and manually construct the query string so it contains the single quotes as follows:
String strInsert = "insert into myTable (lastName) values ('Smith')";
Statement stm = conn.getStatement();
stm.execute(strInsert);

It works like a charm. 

It looks like there is a difference in the way the PostGres driver handles Strings. I have been through several of the documents on the web site, but have not seen this addressed in any of the configuration articles.
The app I am working on needs to work with different RDBMS products. I'm sure I am not the first person to want to do this. Is there a trick to it with PostGres?

Best regards,
Gerry


Re: JDBC Update question (quoted strings)

From
Oliver Jowett
Date:
Gerry Gattis wrote:
> I had to make an unexpected adjustment in a query string in order to
> insert a record into a table.  I was would like to know if anyone has a
> remedy for this.  Here is a short description.
>
> String strInsert = "insert into myTable (lastName) values (?)";
> PreparedStatement stm = conn.prepareCall(strInsert);
> stm.setString(1, "Smith");
> // OK so far
> stm.execute();
> //fails with an error message attribute Smith not found

That sounds like a bug. What driver version are you using?

-O

Re: JDBC Update question (quoted strings)

From
Oliver Jowett
Date:
Gerry Gattis wrote:
> Hi Oliver,
>
> Thanks for the response.  David pointed out that I was using the wrong
> call to create the statement.  After I corrected that, the driver works
> fine.  I think that it must be treating strings differently if it thinks
> that it is talking to a stored procedure.  That would make sense.
>
> Methinks the bug is me!

Hmm.. but prepareCall should behave the same as prepareStatement for
simple queries, in theory. It should only behave differently if it sees
a {call} escape sequence..

Odd.

-O

Re: JDBC Update question (quoted strings)

From
Paul Thomas
Date:
On 01/09/2004 04:46 Oliver Jowett wrote:
> Gerry Gattis wrote:
>> Hi Oliver,
>>
>> Thanks for the response.  David pointed out that I was using the wrong
>> call to create the statement.  After I corrected that, the driver works
>> fine.  I think that it must be treating strings differently if it thinks
>> that it is talking to a stored procedure.  That would make sense.
>>
>> Methinks the bug is me!
>
> Hmm.. but prepareCall should behave the same as prepareStatement for
> simple queries, in theory. It should only behave differently if it sees
> a {call} escape sequence..
>
> Odd.


Could it be anything to do with him using stmt.execute() instead of the
more usual stmt.executeUpdate()?

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for
Business             |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+