Thread: 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 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 erhaltenhaben, informieren Sie bitte sofort den Absender und vernichten Sie diese E-Mail. Das unerlaubte Kopieren sowie dieunbefugte 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 inerror) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distributionof 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
Re: PreparedStatement.toString() creates valid SQL; was: Q: use setObject also for int or string
From
Michael Enke
Date:
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
erhaltenhaben, informieren Sie bitte sofort den Absender und vernichten Sie diese E-Mail. Das unerlaubte Kopieren sowie
dieunbefugte 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
inerror) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or
distributionof the material in this e-mail is strictly forbidden.
*** SimpleQuery.java 2008-09-21 21:15:55.000000000 +0200
--- /home/me/JDBC/postgresql-jdbc-8.3-603.src/org/postgresql/core/v3/SimpleQuery.java.orig 2008-09-21
18:51:55.000000000+0200
*************** class SimpleQuery implements V3Query {
*** 35,66 ****
public String toString(ParameterList parameters) {
StringBuffer sbuf = new StringBuffer(fragments[0]);
- int[] typeOIDs = (parameters == null) ? null : parameters.getTypeOIDs();
for (int i = 1; i < fragments.length; ++i)
{
if (parameters == null)
sbuf.append('?');
! else {
! String s = parameters.toString(i);
! if("NULL".equals(s) ||
! typeOIDs[i-1] == Oid.INT2 || typeOIDs[i-1] == Oid.INT4 || typeOIDs[i-1] == Oid.INT8 ||
! typeOIDs[i-1] == Oid.NUMERIC || typeOIDs[i-1] == Oid.FLOAT4 || typeOIDs[i-1] == Oid.FLOAT8 ||
! typeOIDs[i-1] == Oid.OID) {
! sbuf.append(s);
! } else {
! StringBuffer p = null;
! try {
! // last boolean false is: standardConformingStrings
! // if true: one backslash is output as one backslash
! // if false: one backslash is output as two backslashes
! p = Utils.appendEscapedLiteral(null, s, false);
! if(p.indexOf("\\") != -1) sbuf.append(" E");
! } catch(java.sql.SQLException sqle) {
! p = new StringBuffer(sqle.toString());
! }
! sbuf.append("'" + p + "'");
! }
! }
sbuf.append(fragments[i]);
}
return sbuf.toString();
--- 35,46 ----
public String toString(ParameterList parameters) {
StringBuffer sbuf = new StringBuffer(fragments[0]);
for (int i = 1; i < fragments.length; ++i)
{
if (parameters == null)
sbuf.append('?');
! else
! sbuf.append(parameters.toString(i));
sbuf.append(fragments[i]);
}
return sbuf.toString();
*** V2Query.java 2008-09-21 21:17:34.000000000 +0200
--- /home/me/JDBC/postgresql-jdbc-8.3-603.src/org/postgresql/core/v2/V2Query.java.orig 2008-09-21 18:52:19.000000000
+0200
*************** class V2Query implements Query {
*** 86,117 ****
public String toString(ParameterList parameters) {
StringBuffer sbuf = new StringBuffer(fragments[0]);
- int[] typeOIDs = (parameters == null) ? null : parameters.getTypeOIDs();
for (int i = 1; i < fragments.length; ++i)
{
if (parameters == null)
sbuf.append("?");
! else {
! String s = parameters.toString(i);
! if("NULL".equals(s) ||
! typeOIDs[i-1] == Oid.INT2 || typeOIDs[i-1] == Oid.INT4 || typeOIDs[i-1] == Oid.INT8 ||
! typeOIDs[i-1] == Oid.NUMERIC || typeOIDs[i-1] == Oid.FLOAT4 || typeOIDs[i-1] == Oid.FLOAT8 ||
! typeOIDs[i-1] == Oid.OID) {
! sbuf.append(s);
! } else {
! StringBuffer p = null;
! try {
! // last boolean false is: standardConformingStrings
! // if true: one backslash is output as one backslash
! // if false: one backslash is output as two backslashes
! p = Utils.appendEscapedLiteral(null, s, false);
! if(p.indexOf("\\") != -1) sbuf.append(" E");
! } catch(java.sql.SQLException sqle) {
! p = new StringBuffer(sqle.toString());
! }
! sbuf.append("'" + p + "'");
! }
! }
sbuf.append(fragments[i]);
}
return sbuf.toString();
--- 86,97 ----
public String toString(ParameterList parameters) {
StringBuffer sbuf = new StringBuffer(fragments[0]);
for (int i = 1; i < fragments.length; ++i)
{
if (parameters == null)
sbuf.append("?");
! else
! sbuf.append(parameters.toString(i));
sbuf.append(fragments[i]);
}
return sbuf.toString();
Re: PreparedStatement.toString() creates valid SQL; was: Q: use setObject also for int or string
From
Kris Jurka
Date:
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
Attachment
Re: PreparedStatement.toString() creates valid SQL; was: Q: use setObject also for int or string
From
Michael Enke
Date:
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 erhaltenhaben, informieren Sie bitte sofort den Absender und vernichten Sie diese E-Mail. Das unerlaubte Kopieren sowie dieunbefugte 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 inerror) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distributionof the material in this e-mail is strictly forbidden.
Attachment
Re: PreparedStatement.toString() creates valid SQL; was: Q: use setObject also for int or string
From
Kris Jurka
Date:
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
Re: PreparedStatement.toString() creates valid SQL; was: Q: use setObject also for int or string
From
Michael Enke
Date:
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 erhaltenhaben, informieren Sie bitte sofort den Absender und vernichten Sie diese E-Mail. Das unerlaubte Kopieren sowie dieunbefugte 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 inerror) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distributionof the material in this e-mail is strictly forbidden.
Re: PreparedStatement.toString() creates valid SQL; was: Q: use setObject also for int or string
From
Kris Jurka
Date:
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