Re: PreparedStatement.toString() creates valid SQL; was: Q: use setObject also for int or string

Hello all,
in my application, queries with placeholder are created in a generic way without
knowing if the column is string or integer.
It uses setString(1, "1") even on int columns.
This was working for the pg74*.jar

Now I want to upgrade (postgresql-8*.jar I measured 20% speedup in selects)
but with version 8 I get Exception:
ERROR: operator does not exist: bigint = character varying

In the thread "macaddr data type and prepared statements"
I found that I can use setObject(1, "1", Types.OTHER);
and this works for me too.

My question is if this is the "correct" way to continue
(I can exchange all the setString with setObject but I can not
find out the datatype of the columns in an easy way)
or if this has drawbacks I do not see at the moment.

Thank you and regards,
Michael

--
WINCOR NIXDORF International GmbH
Sitz der Gesellschaft: Paderborn
Registergericht Paderborn HRB 3507
Geschftsfhrer: Eckard Heidloff (Vorsitzender), Stefan Auerbach, Dr. Jrgen Wunram
Vorsitzender des Aufsichtsrats: Karl-Heinz Stiller
Steuernummer: 339/5884/0020 - Ust-ID Nr.: DE812927716 - WEEE-Reg.-Nr. DE44477193

Diese E-Mail enthlt vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtmlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese E-Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser E-Mail ist nicht gestattet.

This e-mail may contain confidential information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.

On Thu, 18 Sep 2008, Michael Enke wrote:

> in my application, queries with placeholder are created in a generic way
> without knowing if the column is string or integer. It uses setString(1,
> "1") even on int columns.
>
> In the thread "macaddr data type and prepared statements" I found that I
> can use setObject(1, "1", Types.OTHER); and this works for me too.
>
> My question is if this is the "correct" way to continue (I can exchange
> all the setString with setObject but I can not find out the datatype of
> the columns in an easy way) or if this has drawbacks I do not see at the
> moment.

If you never have any type information, you can avoid changing all your
setString calls by setting the connection URL property
stringtype=unspecified [1]. setObject(..., Types.OTHER) is more useful
when you have some type information.

[1] http://jdbc.postgresql.org/documentation/83/connect.html#connection-parameters

Kris Jurka

Michael Enke wrote:
> The PreparedStatement.toString() returns the query,
> replaced with available parameter, but string constants are not enclosed
> in apostrophes:
>
> 74: select * from a where a='a'
> 83: select * from a where a=a
>
> The 74 version put this into apostrophes if setString was used.
> If setInt was used, no apostrophes were output.
>

We've never claimed that the output of PreparedStatement.toString would
produce valid SQL. There are certainly other problems with it than just
missing apostrophes. It currently doesn't escape values, so it breaks
if your data contains embedded ' or \. It doesn't understand whether
the server has standard_conforming_strings enabled or not. What would
it to do with a parameter that is an InputStream? If the toString code
reads it, the driver cannot re-read it to send it to the server when
executed.

The InputStream is perhaps an unusual case, so I wouldn't be opposed if
someone wanted to make the simple cases work better, but it's not
something I'm particularly excited about.

Kris Jurka

Hi Kris,

in my Java code I need to retrieve the SQL
from a PreparedStatement to create from this another statement.

I did this and it would be great if you could have a look
into the attached patch files and make comments.

It has to set standard_conforming_strings to false
in any case to create valid sql:
If I use setString(1, "a\\b");
the real stored characters are 'a','\','b'.
If I would use standard_conforming_strings=true
the output would be "a\b"
but with set to false the output is "a\\b" which is the correct one.

I did not understand what you mean with InputStream.

Michael

Kris Jurka schrieb:
> Michael Enke wrote:
>> The PreparedStatement.toString() returns the query,
>> replaced with available parameter, but string constants are not
>> enclosed in apostrophes:
>>
>> 74: select * from a where a='a'
>> 83: select * from a where a=a
>>
>> The 74 version put this into apostrophes if setString was used.
>> If setInt was used, no apostrophes were output.
>>
>
> We've never claimed that the output of PreparedStatement.toString would
> produce valid SQL. There are certainly other problems with it than just
> missing apostrophes. It currently doesn't escape values, so it breaks
> if your data contains embedded ' or \. It doesn't understand whether
> the server has standard_conforming_strings enabled or not. What would
> it to do with a parameter that is an InputStream? If the toString code
> reads it, the driver cannot re-read it to send it to the server when
> executed.
>
> The InputStream is perhaps an unusual case, so I wouldn't be opposed if
> someone wanted to make the simple cases work better, but it's not
> something I'm particularly excited about.
>
> Kris Jurka

--
WINCOR NIXDORF International GmbH
Sitz der Gesellschaft: Paderborn
Registergericht Paderborn HRB 3507
Geschäftsführer: Eckard Heidloff (Vorsitzender), Stefan Auerbach, Dr. Jürgen Wunram
Vorsitzender des Aufsichtsrats: Karl-Heinz Stiller
Steuernummer: 339/5884/0020 - Ust-ID Nr.: DE812927716 - WEEE-Reg.-Nr. DE44477193

Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese E-Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser E-Mail ist nicht gestattet.

This e-mail may contain confidential information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.

On Sun, 21 Sep 2008, Michael Enke wrote:

> I did this and it would be great if you could have a look into the
> attached patch files and make comments.
>
> It has to set standard_conforming_strings to false
> in any case to create valid sql:
> If I use setString(1, "a\\b");
> the real stored characters are 'a','\','b'.
> If I would use standard_conforming_strings=true
> the output would be "a\b"
> but with set to false the output is "a\\b" which is the correct one.

One of the backslashes is used for Java's escaping, so the real data does
only have one backslash which is the correct behavior for
standard_conforming_strings. Also note that E'' syntax is only supported
on 8.1 and later servers, so it can't be used unconditionally.

> I did not understand what you mean with InputStream.

See the attached test case.

Also note that the String "NULL" should be 'NULL', not plain NULL.

Kris Jurka

Attached file contains all changed java sources to make your testcase working.
I added to ParameterList interface a method toSQLString(int).

I did not implement the InputStream because
1) I do not know the encoding of data
2) it could be an InputStream which can not be reset

Michael

> See the attached test case.

--
WINCOR NIXDORF International GmbH
Sitz der Gesellschaft: Paderborn
Registergericht Paderborn HRB 3507
Geschäftsführer: Eckard Heidloff (Vorsitzender), Stefan Auerbach, Dr. Jürgen Wunram
Vorsitzender des Aufsichtsrats: Karl-Heinz Stiller
Steuernummer: 339/5884/0020 - Ust-ID Nr.: DE812927716 - WEEE-Reg.-Nr. DE44477193

Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese E-Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser E-Mail ist nicht gestattet.

This e-mail may contain confidential information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.

On Mon, 22 Sep 2008, Michael Enke wrote:

> Attached file contains all changed java sources to make your testcase
> working. I added to ParameterList interface a method toSQLString(int).

What is the reason for this instead of using the existing toString(int)
method? If toSQLString is required, how can CompositeParameterList's
toSQLString just call toString?

> I did not implement the InputStream because
> 1) I do not know the encoding of data
> 2) it could be an InputStream which can not be reset
>

That's fair. I was just pointing out that you can't always produce a text
representation for the prepared statement. Your changes are certainly an
improvement.

Also, context diffs are appreciated instead of the whole file.

Kris Jurka

Kris Jurka schrieb:
>
>> Attached file contains all changed java sources to make your testcase
>> working. I added to ParameterList interface a method toSQLString(int).
>
> What is the reason for this instead of using the existing toString(int)
> method? If toSQLString is required, how can CompositeParameterList's
> toSQLString just call toString?

I used separate method cause I do not know of any side effects,
maybe toString(int) has to output exactly what it did before?
If not, toSQLString() can replace toString().
Then also the CompositeParameterList would be ok immediately.

I did not look into CompositeParameterList how it's toString works.
Now I've seen that it simply calls the SimpleParameterList.toString().
So if toString can be the new output, all is ok.

What's about the v2 Parameter? Should also output new style?

> Also, context diffs are appreciated instead of the whole file.

Noted.

Michael

--
WINCOR NIXDORF International GmbH
Sitz der Gesellschaft: Paderborn
Registergericht Paderborn HRB 3507
Geschäftsführer: Eckard Heidloff (Vorsitzender), Stefan Auerbach, Dr. Jürgen Wunram
Vorsitzender des Aufsichtsrats: Karl-Heinz Stiller
Steuernummer: 339/5884/0020 - Ust-ID Nr.: DE812927716 - WEEE-Reg.-Nr. DE44477193

Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese E-Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser E-Mail ist nicht gestattet.

This e-mail may contain confidential information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.

On Mon, 22 Sep 2008, Michael Enke wrote:

> Attached file contains all changed java sources to make your testcase
> working. I added to ParameterList interface a method toSQLString(int).
>

I've applied a modified version of this to CVS.

Kris Jurka