Lists: | pgsql-jdbc |
---|
From: | Scott Lamb <slamb(at)slamb(dot)org> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | setUseServerPrepare & typecasts |
Date: | 2002-11-13 10:03:02 |
Message-ID: | 3DD22356.2060208@slamb.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
I've got a bunch of queries that deal with functions in the custom
functions I've made. So I might have a function foo(integer) returning
text. A simple case:
ps = conn.prepare("select foo(?)");
and then calling
ps.setObject(1, new Integer(42), Types.INTEGER);
Without setUseServerPrepare, this works fine. With setUseServerPrepare,
this complains that foo(text) does not exist and suggests explicit
typecasts. With
select foo(?::integer)
it works again.
Should this be necessary? I am telling it the type of that parameter
before I execute the statement. Does it need to know that at prepare
time? Is the explicit cast in the SQL the only way to do that?
I am using a layer of my own design that creates the placeholders and
binds the parameters for me from named, typed parameters. So I could
easily have it automatically insert "{fn convert(?, <TYPENAME>)}"
instead of "?" into the SQL everywhere I use parameters. Would that be
the best way to fix this problem?
Thanks,
Scott
From: | Barry Lind <blind(at)xythos(dot)com> |
---|---|
To: | Scott Lamb <slamb(at)slamb(dot)org> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: setUseServerPrepare & typecasts |
Date: | 2002-11-13 17:23:40 |
Message-ID: | 3DD28A9C.7040208@xythos.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
Scott,
If you use ps.setInt(1,42) does it work? I want to isolate the problem.
In just looking at the code, it seems that the setInt() and
setObject() methods do the same thing.
And I know that the setInt() methods should work since the regression
test uses them.
thanks,
--Barry
Scott Lamb wrote:
> I've got a bunch of queries that deal with functions in the custom
> functions I've made. So I might have a function foo(integer) returning
> text. A simple case:
>
> ps = conn.prepare("select foo(?)");
>
> and then calling
>
> ps.setObject(1, new Integer(42), Types.INTEGER);
>
> Without setUseServerPrepare, this works fine. With setUseServerPrepare,
> this complains that foo(text) does not exist and suggests explicit
> typecasts. With
>
> select foo(?::integer)
>
> it works again.
>
> Should this be necessary? I am telling it the type of that parameter
> before I execute the statement. Does it need to know that at prepare
> time? Is the explicit cast in the SQL the only way to do that?
>
> I am using a layer of my own design that creates the placeholders and
> binds the parameters for me from named, typed parameters. So I could
> easily have it automatically insert "{fn convert(?, <TYPENAME>)}"
> instead of "?" into the SQL everywhere I use parameters. Would that be
> the best way to fix this problem?
>
> Thanks,
> Scott
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
From: | Scott Lamb <slamb(at)slamb(dot)org> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: setUseServerPrepare & typecasts |
Date: | 2002-11-13 22:52:28 |
Message-ID: | 3DD2D7AC.6000703@slamb.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
Barry Lind wrote:
> Scott,
>
> If you use ps.setInt(1,42) does it work? I want to isolate the problem.
> In just looking at the code, it seems that the setInt() and setObject()
> methods do the same thing.
Ahh, I'm sorry. setObject(1, new Object(42), Types.INTEGER) doesn't do
it after all. I translated inappropriately when I was writing the email.
setObject(1, null, Types.INTEGER) is the problem. Test function and Java
program attached to reproduce.
Scott
Attachment | Content-Type | Size |
---|---|---|
PreparedCastTest.java | text/plain | 1.1 KB |
preparedCastTest.sql | text/plain | 149 bytes |
From: | Barry Lind <blind(at)xythos(dot)com> |
---|---|
To: | Scott Lamb <slamb(at)slamb(dot)org> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: setUseServerPrepare & typecasts |
Date: | 2002-11-13 23:45:01 |
Message-ID: | 3DD2E3FD.4020607@xythos.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
Scott,
That makes sense because null takes a different code path than a real
value. Thanks for the test case. I will look into this.
--Barry
Scott Lamb wrote:
> Barry Lind wrote:
>
>> Scott,
>>
>> If you use ps.setInt(1,42) does it work? I want to isolate the
>> problem. In just looking at the code, it seems that the setInt() and
>> setObject() methods do the same thing.
>
>
> Ahh, I'm sorry. setObject(1, new Object(42), Types.INTEGER) doesn't do
> it after all. I translated inappropriately when I was writing the email.
> setObject(1, null, Types.INTEGER) is the problem. Test function and Java
> program attached to reproduce.
>
> Scott
>
>
> ------------------------------------------------------------------------
>
> import java.sql.Connection;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.DriverManager;
> import java.sql.Types;
> import java.sql.SQLException;
> import org.postgresql.PGStatement;
>
> public class PreparedCastTest {
> public static void main(String[] args) {
> Connection c = null;
> PreparedStatement ps = null;
> ResultSet rs = null;
> try {
> c = DriverManager.getConnection(args[0], args[1], args[2]);
> ps = c.prepareStatement("select foo(?)");
> ( (PGStatement) ps ).setUseServerPrepare(true);
> //ps.setInt(1, 42);
> //ps.setObject(1, new Integer(42), Types.INTEGER);
> ps.setObject(1, null, Types.INTEGER);
> rs = ps.executeQuery();
> } catch (Exception e) {
> e.printStackTrace();
> System.exit(1);
> } finally {
> if (rs != null) { try { rs.close(); } catch (Throwable t) {} }
> if (ps != null) { try { ps.close(); } catch (Throwable t) {} }
> if (c != null) { try { c .close(); } catch (Throwable t) {} }
> }
> }
> }
>
>
>
> ------------------------------------------------------------------------
>
> create or replace function foo(integer) returns text as '
> declare
> theint alias for $1;
> begin
> return theint::text;
> end;' language 'plpgsql';
>
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
From: | Aaron Mulder <ammulder(at)alumni(dot)princeton(dot)edu> |
---|---|
To: | PostgreSQL JDBC <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: setUseServerPrepare & typecasts |
Date: | 2002-11-13 23:49:12 |
Message-ID: | Pine.LNX.4.44.0211131848020.26423-100000@www.princetongames.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
On Wed, 13 Nov 2002, Barry Lind wrote:
> Scott,
>
> That makes sense because null takes a different code path than a real
> value. Thanks for the test case. I will look into this.
But I don't think you're supposed to use setObject for a null
value. Instead, try
setNull(1, Types.INTEGER)
Aaron
From: | Scott Lamb <slamb(at)slamb(dot)org> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: setUseServerPrepare & typecasts |
Date: | 2002-11-14 01:16:22 |
Message-ID: | 3DD2F966.7080000@slamb.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
Aaron Mulder wrote:
> On Wed, 13 Nov 2002, Barry Lind wrote:
>
>>Scott,
>>
>>That makes sense because null takes a different code path than a real
>>value. Thanks for the test case. I will look into this.
>
>
> But I don't think you're supposed to use setObject for a null
> value. Instead, try
>
> setNull(1, Types.INTEGER)
Hmm. Yeah, I guess the API docs don't really describe what setObject is
supposed to do on null. But setNull(1, Types.INTEGER) seems to have the
same problem.
Thanks,
Scott
From: | Scott Lamb <slamb(at)slamb(dot)org> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: setUseServerPrepare & typecasts |
Date: | 2002-11-14 01:20:33 |
Message-ID: | 3DD2FA61.2070502@slamb.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
Scott Lamb wrote:
> Aaron Mulder wrote:
>> But I don't think you're supposed to use setObject for a null
>> value. Instead, try
>>
>> setNull(1, Types.INTEGER)
>
> Hmm. Yeah, I guess the API docs don't really describe what setObject is
> supposed to do on null. But setNull(1, Types.INTEGER) seems to have the
> same problem.
Ahh, but the JDBC 3.0 specification does. Section 13.2.2.3 says "if a
Java null is passed to any of the setter methods that take a Java
object, the parameter will be set to JDBC NULL". So it should work.
Thanks,
Scott
From: | Aaron Mulder <ammulder(at)alumni(dot)princeton(dot)edu> |
---|---|
To: | Scott Lamb <slamb(at)slamb(dot)org> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: setUseServerPrepare & typecasts |
Date: | 2002-11-14 01:57:27 |
Message-ID: | Pine.LNX.4.44.0211132056030.26627-100000@www.princetongames.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
On Wed, 13 Nov 2002, Scott Lamb wrote:
> Ahh, but the JDBC 3.0 specification does. Section 13.2.2.3 says "if a
> Java null is passed to any of the setter methods that take a Java
> object, the parameter will be set to JDBC NULL". So it should work.
Oops, you're right. I had it in my head that you _had_ to use
setNull, but I see that's not really required.
Aaron
From: | Felipe Schnack <felipes(at)ritterdosreis(dot)br> |
---|---|
To: | pgsql-jdbc <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: setUseServerPrepare & typecasts |
Date: | 2002-11-14 09:59:43 |
Message-ID: | 1037267984.1520.0.camel@desenv1.ritterdosreis.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
I never understood why I have to specify a data type when setting a
column to NULL in jdbc's PreparedStatement.setNull() method. Someone can
explain to me?
On Wed, 2002-11-13 at 23:57, Aaron Mulder wrote:
> On Wed, 13 Nov 2002, Scott Lamb wrote:
> > Ahh, but the JDBC 3.0 specification does. Section 13.2.2.3 says "if a
> > Java null is passed to any of the setter methods that take a Java
> > object, the parameter will be set to JDBC NULL". So it should work.
>
> Oops, you're right. I had it in my head that you _had_ to use
> setNull, but I see that's not really required.
>
> Aaron
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
--
Felipe Schnack
Analista de Sistemas
felipes(at)ritterdosreis(dot)br
Cel.: (51)91287530
Linux Counter #281893
Faculdade Ritter dos Reis
www.ritterdosreis.br
felipes(at)ritterdosreis(dot)br
Fone/Fax.: (51)32303328
From: | Dave Cramer <Dave(at)micro-automation(dot)net> |
---|---|
To: | Felipe Schnack <felipes(at)ritterdosreis(dot)br> |
Cc: | pgsql-jdbc <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: setUseServerPrepare & typecasts |
Date: | 2002-11-14 10:37:34 |
Message-ID: | 1037270254.5146.66.camel@inspiron.cramers |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
Well, in postgres it certainly isn't necessary, however I would suspect
that one of the vendors that was involved in creating the spec required
it.
Dave
On Thu, 2002-11-14 at 04:59, Felipe Schnack wrote:
> I never understood why I have to specify a data type when setting a
> column to NULL in jdbc's PreparedStatement.setNull() method. Someone can
> explain to me?
>
> On Wed, 2002-11-13 at 23:57, Aaron Mulder wrote:
> > On Wed, 13 Nov 2002, Scott Lamb wrote:
> > > Ahh, but the JDBC 3.0 specification does. Section 13.2.2.3 says "if a
> > > Java null is passed to any of the setter methods that take a Java
> > > object, the parameter will be set to JDBC NULL". So it should work.
> >
> > Oops, you're right. I had it in my head that you _had_ to use
> > setNull, but I see that's not really required.
> >
> > Aaron
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
--
Dave Cramer <Dave(at)micro-automation(dot)net>
From: | Barry Lind <blind(at)xythos(dot)com> |
---|---|
To: | Scott Lamb <slamb(at)slamb(dot)org> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: setUseServerPrepare & typecasts |
Date: | 2002-11-14 10:51:03 |
Message-ID: | 3DD38017.1030703@xythos.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
Scott,
I have checked in a fix for this problem.
--Barry
Scott Lamb wrote:
> Barry Lind wrote:
>
>> Scott,
>>
>> If you use ps.setInt(1,42) does it work? I want to isolate the
>> problem. In just looking at the code, it seems that the setInt() and
>> setObject() methods do the same thing.
>
>
> Ahh, I'm sorry. setObject(1, new Object(42), Types.INTEGER) doesn't do
> it after all. I translated inappropriately when I was writing the email.
> setObject(1, null, Types.INTEGER) is the problem. Test function and Java
> program attached to reproduce.
>
> Scott
>
>
> ------------------------------------------------------------------------
>
> import java.sql.Connection;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.DriverManager;
> import java.sql.Types;
> import java.sql.SQLException;
> import org.postgresql.PGStatement;
>
> public class PreparedCastTest {
> public static void main(String[] args) {
> Connection c = null;
> PreparedStatement ps = null;
> ResultSet rs = null;
> try {
> c = DriverManager.getConnection(args[0], args[1], args[2]);
> ps = c.prepareStatement("select foo(?)");
> ( (PGStatement) ps ).setUseServerPrepare(true);
> //ps.setInt(1, 42);
> //ps.setObject(1, new Integer(42), Types.INTEGER);
> ps.setObject(1, null, Types.INTEGER);
> rs = ps.executeQuery();
> } catch (Exception e) {
> e.printStackTrace();
> System.exit(1);
> } finally {
> if (rs != null) { try { rs.close(); } catch (Throwable t) {} }
> if (ps != null) { try { ps.close(); } catch (Throwable t) {} }
> if (c != null) { try { c .close(); } catch (Throwable t) {} }
> }
> }
> }
>
>
>
> ------------------------------------------------------------------------
>
> create or replace function foo(integer) returns text as '
> declare
> theint alias for $1;
> begin
> return theint::text;
> end;' language 'plpgsql';
>
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
From: | Scott Lamb <slamb(at)slamb(dot)org> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: setUseServerPrepare & typecasts |
Date: | 2002-11-14 12:41:24 |
Message-ID: | 3DD399F4.1000008@slamb.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
Barry Lind wrote:
> Scott,
>
> I have checked in a fix for this problem.
>
> --Barry
...and it works for me. Thanks!
Scott