Sergey A. Sokolov schrieb:
> Hello all,
>
> I would appreciate if JDBC development team consider my message as a feature
> request for Postgres JDBC 8.xx drivers.
>
> My issue is the following: in our application we use java.lang.String for
> holding Identifier values of the database objects (portability,
> extensibility, etc...), while PostgreSQL schema implementation have ID
> fields as BIGINT's (or BIGSERIAL's) for most of the objects. We use some
> stored procedures/functions in order to access and modify the data and,
> certainly, have to pass ID values there from Java.
>
> We use "PreparedStatement.setString(index, id);"-like statements in Java for
> this kind of fields and everything works fine with JDBC drivers version 7.4
> -- I mean autocast works and stored procedure/function is being found and
> called correctly.
> When we moved to Postgres JDBC version 8.0, or 8.1dev -- we've ran into the
> problem of getting exceptions like
>
> org.postgresql.util.PSQLException: ERROR: function proc_test(character
> varying) does not exist
>

As I followed this mailinglist, this is a feature of version 8.x
and you must use the setXXX-Method that fits the type of the parameter.

In your case setLong(index, id).

This feature was introduced to use real Prepared-Statements with the
protocol v3 for client-server-communication of the database.

Regards,
Roland.


--
Roland Walter
MOSAIC SOFTWARE AG
Telefon: 02225/882-411 Fax: 02225/882-201
http://www.mosaic-ag.com
------- L E G A L D I S C L A I M E R ---------

Die Informationen in dieser Nachricht sind vertraulich
und ausschliesslich fuer den Adressaten bestimmt.
Kenntnisnahme durch Dritte ist unzulaessig. Die
Erstellung von Kopien oder das Weiterleiten an weitere,
nicht originaere und benannte Adressaten ist nicht
vorgesehen und kann ungesetzlich sein. Die Meinungen
in dieser Nachricht stellen lediglich die Meinungen
des Senders dar. Falls Sie vermuten, dass diese
Nachricht veraendert wurde, setzen Sie sich mit dem
Absender in Verbindung. Der Absender uebernimmt ohne
weitere Ueberpruefung keine Verantwortung fuer die
Richtigkeit und Vollstaendigkeit des Inhalts. Unbefugte
Empfaenger werden gebeten, die Vertraulichkeit der
Nachricht zu wahren und den Absender sofort ueber
einen Uebertragungsfehler zu informieren.
------------------------------------------------------


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Hello Roland,

Thank you for your prompt reply. I was sure this topic was discussed here
many times.
I agree that setLong will solve my problem, as well as doing call to
"test_proc ( CAST(? AS bigint))".



My point here is that in such an intergation layer as JDBC is, strengthening
the type-checking will not lead to any good results for end-users of this
layer (software developers). It will force them to create an another
database abstraction sublayer.

Imagine tomorrow Postgres team would decide to expand the size of BIGINT
even more (ok, ok, create another HUGEINT/HUGESERIAL). What class in Java
should it be mapped to? java.math.BigDecimal? Is there any reason of using
BigDecimal in your application if you are NOT going to add, subtract,
multiply or take square root from it?

I think there must be support for String as input and output parameters for
the most of Postgres datatypes (numeric, date,..). Just my humble opinion.

Sergey Sokolov

> -----Original Message-----
> From: Roland Walter [mailto:rwa@mosaic-ag.com]
> Sent: Wednesday, May 25, 2005 8:54 PM
> To: Sergey A. Sokolov
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] BIGINT <-> java.lang.String auto cast
>
> Sergey A. Sokolov schrieb:
> > Hello all,
> >
> > I would appreciate if JDBC development team consider my
> message as a
> > feature request for Postgres JDBC 8.xx drivers.
> >
> > My issue is the following: in our application we use
> java.lang.String
> > for holding Identifier values of the database objects (portability,
> > extensibility, etc...), while PostgreSQL schema
> implementation have ID
> > fields as BIGINT's (or BIGSERIAL's) for most of the objects. We use
> > some stored procedures/functions in order to access and modify the
> > data and, certainly, have to pass ID values there from Java.
> >
> > We use "PreparedStatement.setString(index, id);"-like statements in
> > Java for this kind of fields and everything works fine with JDBC
> > drivers version 7.4
> > -- I mean autocast works and stored procedure/function is
> being found
> > and called correctly.
> > When we moved to Postgres JDBC version 8.0, or 8.1dev -- we've ran
> > into the problem of getting exceptions like
> >
> > org.postgresql.util.PSQLException: ERROR: function
> proc_test(character
> > varying) does not exist
> >
>
> As I followed this mailinglist, this is a feature of version
> 8.x and you must use the setXXX-Method that fits the type of
> the parameter.
>
> In your case setLong(index, id).
>
> This feature was introduced to use real Prepared-Statements
> with the protocol v3 for client-server-communication of the database.
>
> Regards,
> Roland.


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

I have a patch that I haven't applied yet, but can that will allow you
to use setObject(n, obj,sqltype) and it will convert obj to the sqltype
requested as per the spec.

I have been holding back applying this patch but could apply it if this
will help.

Dave

Sergey A. Sokolov wrote:

>Hello Roland,
>
>Thank you for your prompt reply. I was sure this topic was discussed here
>many times.
>I agree that setLong will solve my problem, as well as doing call to
>"test_proc ( CAST(? AS bigint))".
>
>
>
>My point here is that in such an intergation layer as JDBC is, strengthening
>the type-checking will not lead to any good results for end-users of this
>layer (software developers). It will force them to create an another
>database abstraction sublayer.
>
>Imagine tomorrow Postgres team would decide to expand the size of BIGINT
>even more (ok, ok, create another HUGEINT/HUGESERIAL). What class in Java
>should it be mapped to? java.math.BigDecimal? Is there any reason of using
>BigDecimal in your application if you are NOT going to add, subtract,
>multiply or take square root from it?
>
>I think there must be support for String as input and output parameters for
>the most of Postgres datatypes (numeric, date,..). Just my humble opinion.
>
>Sergey Sokolov
>
>
>
>>-----Original Message-----
>>From: Roland Walter [mailto:rwa@mosaic-ag.com]
>>Sent: Wednesday, May 25, 2005 8:54 PM
>>To: Sergey A. Sokolov
>>Cc: pgsql-jdbc@postgresql.org
>>Subject: Re: [JDBC] BIGINT <-> java.lang.String auto cast
>>
>>Sergey A. Sokolov schrieb:
>>
>>
>>>Hello all,
>>>
>>>I would appreciate if JDBC development team consider my
>>>
>>>
>>message as a
>>
>>
>>>feature request for Postgres JDBC 8.xx drivers.
>>>
>>>My issue is the following: in our application we use
>>>
>>>
>>java.lang.String
>>
>>
>>>for holding Identifier values of the database objects (portability,
>>>extensibility, etc...), while PostgreSQL schema
>>>
>>>
>>implementation have ID
>>
>>
>>>fields as BIGINT's (or BIGSERIAL's) for most of the objects. We use
>>>some stored procedures/functions in order to access and modify the
>>>data and, certainly, have to pass ID values there from Java.
>>>
>>>We use "PreparedStatement.setString(index, id);"-like statements in
>>>Java for this kind of fields and everything works fine with JDBC
>>>drivers version 7.4
>>>-- I mean autocast works and stored procedure/function is
>>>
>>>
>>being found
>>
>>
>>>and called correctly.
>>>When we moved to Postgres JDBC version 8.0, or 8.1dev -- we've ran
>>>into the problem of getting exceptions like
>>>
>>>org.postgresql.util.PSQLException: ERROR: function
>>>
>>>
>>proc_test(character
>>
>>
>>>varying) does not exist
>>>
>>>
>>>
>>As I followed this mailinglist, this is a feature of version
>>8.x and you must use the setXXX-Method that fits the type of
>>the parameter.
>>
>>In your case setLong(index, id).
>>
>>This feature was introduced to use real Prepared-Statements
>>with the protocol v3 for client-server-communication of the database.
>>
>>Regards,
>>Roland.
>>
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>
>
>

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Dave Cramer wrote:
> I have a patch that I haven't applied yet, but can that will allow you
> to use setObject(n, obj,sqltype) and it will convert obj to the sqltype
> requested as per the spec.

Err the current driver already did this, I thought?

-O

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Sergey A. Sokolov wrote:

> I think there must be support for String as input and output parameters for
> the most of Postgres datatypes (numeric, date,..). Just my humble opinion.

You need to talk to the backend developers; this is just the way that
the backend's default implicit casts are set up. The driver change is
that we are actually passing the type information provided at the JDBC
level (e.g. "This parameter is a Java long because I called setLong()")
down to the backend, rather than going via a stringizing step which
loses that information. Why would you want to throw this type
information away?

BTW, my reading of the JDBC spec says nothing about implicit type
conversion on setString being required.

-O

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Oliver,

As far as I know it doesn't handle things like setObject( n, Date,
Types.int )

I think most of the conversions are being handled by the server.

Dave

Oliver Jowett wrote:

>Dave Cramer wrote:
>
>
>>I have a patch that I haven't applied yet, but can that will allow you
>>to use setObject(n, obj,sqltype) and it will convert obj to the sqltype
>>requested as per the spec.
>>
>>
>
>Err the current driver already did this, I thought?
>
>-O
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Dave Cramer wrote:

> As far as I know it doesn't handle things like setObject( n, Date,
> Types.int )

Err, what the heck is the driver meant to do with that anyway? There's a
matrix of necessary-to-support conversions for setObject() in one of the
JDBC spec's appendicies.. from memory, date -> integer isn't one of them.

-O

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Well, this is defined

setObject( n, Double, Types.boolean) and the backend doesn't handle it

Dave

Oliver Jowett wrote:

> Dave Cramer wrote:
>
>> As far as I know it doesn't handle things like setObject( n, Date,
>> Types.int )
>
>
> Err, what the heck is the driver meant to do with that anyway? There's
> a matrix of necessary-to-support conversions for setObject() in one of
> the JDBC spec's appendicies.. from memory, date -> integer isn't one
> of them.

> -O
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Dave Cramer wrote:
> Well, this is defined
>
> setObject( n, Double, Types.boolean) and the backend doesn't handle it

Ah, ok, guess we missed that one. AFAIK the driver should handle most of
the required conversions, though -- I don't see that adding support for
(for example) double->boolean requires a complete rewrite of setObject()
as you originally suggested, we already have a structure in place for
conversions?

What other conversions is the driver missing?

-O

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Where do you see the driver doing such conversions.

To answer your question

all numeric types to boolean, int, long
boolean types to numeric

Dave

Oliver Jowett wrote:

> Dave Cramer wrote:
>
>> Well, this is defined
>>
>> setObject( n, Double, Types.boolean) and the backend doesn't handle it
>
>
> Ah, ok, guess we missed that one. AFAIK the driver should handle most
> of the required conversions, though -- I don't see that adding support
> for (for example) double->boolean requires a complete rewrite of
> setObject() as you originally suggested, we already have a structure
> in place for conversions?
>
> What other conversions is the driver missing?
>
> -O
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match