Обсуждение: BIGINT <-> java.lang.String auto cast
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).
Вложения
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. ------------------------------------------------------
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.
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 > > > >
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
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
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
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
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
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
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
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
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
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
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