Thread: BIGINT <-> java.lang.String auto cast

BIGINT <-> java.lang.String auto cast

From
"Sergey A. Sokolov"
Date:
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

Attached is the test case for this issue. We use PostgreSQL database server
v 8.0.2.

I don't think this new behaviour is the correct one as BIGINT can be
serialised and deserialised back to String nicely and sometimes it is the
only way of passing data in heterogeneous systems.


Thank you for your time,

Sergey A. Sokolov
xTech Ltd.

--
| E-mail: abac@xtech.ru;  URL: http://www.xtech.ru
| P.O. Box 567, Novosibirsk-117, 630117, Russia
| Tel/Fax:   +7 383-2 329-658
| Our local time zone is GMT+7 (Summer time).


Attachment

Re: BIGINT <-> java.lang.String auto cast

From
Roland Walter
Date:
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.
------------------------------------------------------


Re: BIGINT <-> java.lang.String auto cast

From
"Sergey A. Sokolov"
Date:
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.


Re: BIGINT <-> java.lang.String auto cast

From
Dave Cramer
Date:
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
>
>
>
>

Re: BIGINT <-> java.lang.String auto cast

From
Oliver Jowett
Date:
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

Re: BIGINT <-> java.lang.String auto cast

From
Oliver Jowett
Date:
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

Re: BIGINT <-> java.lang.String auto cast

From
Dave Cramer
Date:
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


Re: BIGINT <-> java.lang.String auto cast

From
Oliver Jowett
Date:
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

Re: BIGINT <-> java.lang.String auto cast

From
Dave Cramer
Date:
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


Re: BIGINT <-> java.lang.String auto cast

From
Oliver Jowett
Date:
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

Re: BIGINT <-> java.lang.String auto cast

From
Dave Cramer
Date:
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


Re: BIGINT <-> java.lang.String auto cast

From
Oliver Jowett
Date:
Dave Cramer wrote:
> Where do you see the driver doing such conversions.

In AbstractJdbc2Statement.setObject(int,Object,int,int), e.g.:

>     // Helper method that extracts numeric values from an arbitary Object.
>     private String numericValueOf(Object x)
>     {
>         if (x instanceof Boolean)
>             return ((Boolean)x).booleanValue() ? "1" : "0";
>         else if (x instanceof Integer || x instanceof Long ||
>                  x instanceof Double || x instanceof Short ||
>                  x instanceof Number || x instanceof Float)
>             return x.toString();
>         else
>             //ensure the value is a valid numeric value to avoid
>             //sql injection attacks
>             return new BigDecimal(x.toString()).toString();
>     }

[...]

>         switch (targetSqlType)
>         {
>         case Types.INTEGER:
>             bindLiteral(parameterIndex, numericValueOf(x), Oid.INT4);
>             break;

[...]

-O

Re: BIGINT <-> java.lang.String auto cast

From
Dave Cramer
Date:
Ok,

I recall it now, this isn't sufficient for the plethora of conversions
which have to be made.

Basically what I did was create an internal type for every SQL type in
org.postgresql.types

and I do one set of instanceof as below to determine which type to
create, and then
do a convert based on the sqltype passed in.

I'll commit the patch tomorrow

Dave

Oliver Jowett wrote:

> Dave Cramer wrote:
>
>> Where do you see the driver doing such conversions.
>
>
> In AbstractJdbc2Statement.setObject(int,Object,int,int), e.g.:
>
>>     // Helper method that extracts numeric values from an arbitary
>> Object.
>>     private String numericValueOf(Object x)
>>     {
>>         if (x instanceof Boolean)
>>             return ((Boolean)x).booleanValue() ? "1" : "0";
>>         else if (x instanceof Integer || x instanceof Long ||
>>                  x instanceof Double || x instanceof Short ||
>>                  x instanceof Number || x instanceof Float)
>>             return x.toString();
>>         else
>>             //ensure the value is a valid numeric value to avoid
>>             //sql injection attacks
>>             return new BigDecimal(x.toString()).toString();
>>     }
>
>
> [...]
>
>>         switch (targetSqlType)
>>         {
>>         case Types.INTEGER:
>>             bindLiteral(parameterIndex, numericValueOf(x), Oid.INT4);
>>             break;
>
>
> [...]
>
> -O
>
>

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


Re: BIGINT <-> java.lang.String auto cast

From
Oliver Jowett
Date:
Dave Cramer wrote:

> I recall it now, this isn't sufficient for the plethora of conversions
> which have to be made.
>
> Basically what I did was create an internal type for every SQL type in
> org.postgresql.types
>
> and I do one set of instanceof as below to determine which type to
> create, and then
> do a convert based on the sqltype passed in.

Yeah, I was considering something similar, but the current scheme was
sufficiently simple that it didn't seem worth it.

> I'll commit the patch tomorrow

What conversions does your patch add? (can you post the patch?)

-O

Dynamically Out put a image in Jsp

From
"Dilan"
Date:
Hello,

I'm using psql 8.0.3-RH,
And using hibernate version 2.0.3 mapping,

I'm using hibernate to connect postgresql via java, and I'm trying to save a
image as a binary file,


When I upload, use this part of the class:




UploadTransferForm uForm = (UploadTransferForm) form; int
size=uForm.getFileUpload().getFileSize();
byte[] byteArr = new byte[size];

ActionErrors errors = new ActionErrors();

try {
    //Create an input stream to read the uploaded file.
    ByteArrayInputStream bytein = new
ByteArrayInputStream(uForm.getFileUpload().getFileData());
    // Load the input stream into the byte Array.
    bytein.read(byteArr);
    // Close the input stream.
    bytein.close();
    // Load the byte[] into the content field.
    uForm.setContent(byteArr);
} catch (Exception e) {
        logger.error("Error adding image to the database", e);
}

Session sess = null;

AuthorityApprovalForms af = new AuthorityApprovalForms();

try {
        sess = Waterfind.getSessionFactory().openSession();
} catch (net.sf.hibernate.HibernateException e) {
        logger.error("Error opening hibernate session", e); }
   try {
        af.setImg(byteArr);
        sess.save(af);
        sess.flush();

} catch (Exception e) {




--

when i diplaying it





try {

    AuthorityApprovalForms af = new AuthorityApprovalForms();
    Long formId = new Long(request.getParameter("formId"));
    af = AuthorityApprovalForms.getFormById(formId);

    byte pic[] = af.getImg();

    OutputStream out = response.getOutputStream();
    out.write( pic );

     } catch (IOException e) {
             logger.error("Error opening file region", e);
     }
}



I have save the data into the database via hibernate-mapping as binary





<hibernate-mapping>

    <class name="com.admin.AuthorityApprovalForms"
table="AUTHORITY_APPROVAL_FORMS">
        <id name="id" type="long">
            <generator class="native"/>
        </id>
    <many-to-one name="OfferId" class="com.admin.core.Offer">
            <column name="offer_id"/>
        </many-to-one>
    <property name="name" type="string"/>
    <property name="type" type="string"/>
    <property name="img" type="binary"/>

    </class>
</hibernate-mapping>



in JSP


<img src="trasnferImage.html?formId=<%= String.valueOf(formId) %>"
align="absmiddle" border="0">



However, when jpg is displaying, it appear to be corrupted, with green and
black back ground (similar to missing layers).

I Think this is something to do with setting up the content type? can some
one help me with how to set it up?

I even check byte compression between before and after saving the data, and
no different between that either, can some one please help regards that or
give me some comments regards the problem.

I even upgrade the database version,

All I want to do is, upload the jpg files to the database then later display
it.

Regards