Thread: driver fails to handle strings in query statements properly
I am using jdbc7.0-1.2.jar. I am getting a "No class found for
unkown" when I use strings in my query statements such as 'Container'
seen in the query below. These queries work fine when I use Oracle or
MySQL. This looks like a bug to me. I can work around this by
explicitly casting the string in the query statement like:
'Container'::varchar.
*****************Inside LDMSQLPLUGIN About to query db ----> select
container_20_ft_qty AS QTY_OH, 'Container' AS NOMENCLATURE from
ue_summary_mtmc
where uic = 'WAQJAA'
EEEEEEEEEEEEEEE I caught an exception on resultSet.getObject(i + 1) -->
Container
No class found for unknown.
at org.postgresql.Connection.getObject(Connection.java:621)
at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:741)
at
org.cougaar.domain.mlm.plugin.ldm.LDMSQLPlugIn.executeSQL(LDMSQLPlugIn.java:432
)
Is there a fix for this?
Thanks in advance,
Lora Goldston
This is coming back from the backend as type unknown. If the single
quotes are removed it works ok?
I suppose we could return it as a string when we are in doubt? Is this
reasonable since there are many other types it should be returned as?
Dave
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Lora Goldston
Sent: October 1, 2001 2:33 PM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] driver fails to handle strings in query statements
properly
I am using jdbc7.0-1.2.jar. I am getting a "No class found for
unkown" when I use strings in my query statements such as 'Container'
seen in the query below. These queries work fine when I use Oracle or
MySQL. This looks like a bug to me. I can work around this by
explicitly casting the string in the query statement like:
'Container'::varchar.
*****************Inside LDMSQLPLUGIN About to query db ----> select
container_20_ft_qty AS QTY_OH, 'Container' AS NOMENCLATURE from
ue_summary_mtmc
where uic = 'WAQJAA'
EEEEEEEEEEEEEEE I caught an exception on resultSet.getObject(i + 1) -->
Container
No class found for unknown.
at org.postgresql.Connection.getObject(Connection.java:621)
at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:741)
at
org.cougaar.domain.mlm.plugin.ldm.LDMSQLPlugIn.executeSQL(LDMSQLPlugIn.j
ava:432
)
Is there a fix for this?
Thanks in advance,
Lora Goldston
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Dave, My thoughts as well. If we encounter an 'unknown' type, just return it as a String. Do you have time to fix this? As a work around using an explicit cast should also work: select container_20_ft_qty AS QTY_OH, 'Container'::text AS NOMENCLATURE from ue_summary_mtmc where uic = 'WAQJAA' thanks, --Barry Dave Cramer wrote: > This is coming back from the backend as type unknown. If the single > quotes are removed it works ok? > > I suppose we could return it as a string when we are in doubt? Is this > reasonable since there are many other types it should be returned as? > > Dave > > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Lora Goldston > Sent: October 1, 2001 2:33 PM > To: pgsql-jdbc@postgresql.org > Subject: [JDBC] driver fails to handle strings in query statements > properly > > > I am using jdbc7.0-1.2.jar. I am getting a "No class found for > unkown" when I use strings in my query statements such as 'Container' > seen in the query below. These queries work fine when I use Oracle or > MySQL. This looks like a bug to me. I can work around this by > explicitly casting the string in the query statement like: > 'Container'::varchar. > > *****************Inside LDMSQLPLUGIN About to query db ----> select > container_20_ft_qty AS QTY_OH, 'Container' AS NOMENCLATURE from > ue_summary_mtmc > where uic = 'WAQJAA' > EEEEEEEEEEEEEEE I caught an exception on resultSet.getObject(i + 1) --> > > Container > No class found for unknown. > at org.postgresql.Connection.getObject(Connection.java:621) > at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:741) > at > org.cougaar.domain.mlm.plugin.ldm.LDMSQLPlugIn.executeSQL(LDMSQLPlugIn.j > ava:432 > ) > > > Is there a fix for this? > > Thanks in advance, > > Lora Goldston > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >
Barry Lind <barry@xythos.com> writes:
> My thoughts as well. If we encounter an 'unknown' type, just return it
> as a String. Do you have time to fix this?
>> I suppose we could return it as a string when we are in doubt? Is this
>> reasonable since there are many other types it should be returned as?
The backend will presently default to treating 'unknown' as type 'text'
if it hasn't got any better idea what to do, so I think it's reasonable
for JDBC to do likewise.
I believe the only case in which you could see 'unknown' reported as a
query result type is if a string literal is SELECTed without any
processing being done on it, viz
SELECT 'foo'
As soon as the literal is passed to a function or operator, sorted,
grouped, etc, the system will insist on resolving it to a non-unknown
type. If the usage context doesn't provide any other hint, 'text' will
be the chosen type.
regards, tom lane
Ok, sounds good, I have time and will take care of it. Dave -----Original Message----- From: Barry Lind [mailto:barry@xythos.com] Sent: October 2, 2001 4:21 PM To: Dave@micro-automation.net Cc: pgsql-jdbc@postgresql.org Subject: Re: driver fails to handle strings in query statements properly Dave, My thoughts as well. If we encounter an 'unknown' type, just return it as a String. Do you have time to fix this? As a work around using an explicit cast should also work: select container_20_ft_qty AS QTY_OH, 'Container'::text AS NOMENCLATURE from ue_summary_mtmc where uic = 'WAQJAA' thanks, --Barry Dave Cramer wrote: > This is coming back from the backend as type unknown. If the single > quotes are removed it works ok? > > I suppose we could return it as a string when we are in doubt? Is this > reasonable since there are many other types it should be returned as? > > Dave > > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Lora Goldston > Sent: October 1, 2001 2:33 PM > To: pgsql-jdbc@postgresql.org > Subject: [JDBC] driver fails to handle strings in query statements > properly > > > I am using jdbc7.0-1.2.jar. I am getting a "No class found for > unkown" when I use strings in my query statements such as 'Container' > seen in the query below. These queries work fine when I use Oracle or > MySQL. This looks like a bug to me. I can work around this by > explicitly casting the string in the query statement like: > 'Container'::varchar. > > *****************Inside LDMSQLPLUGIN About to query db ----> select > container_20_ft_qty AS QTY_OH, 'Container' AS NOMENCLATURE from > ue_summary_mtmc > where uic = 'WAQJAA' > EEEEEEEEEEEEEEE I caught an exception on resultSet.getObject(i + 1) --> > > Container > No class found for unknown. > at org.postgresql.Connection.getObject(Connection.java:621) > at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:741) > at > org.cougaar.domain.mlm.plugin.ldm.LDMSQLPlugIn.executeSQL(LDMSQLPlugIn.j > ava:432 > ) > > > Is there a fix for this? > > Thanks in advance, > > Lora Goldston > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >
I tried it on a table which id was an int
Using "select 'id' as xxx from ..." Returns xxx as an unknown type
Using "select id as xxx from... " returns xxx as an int
I have no idea how the backend handles this, but if it can figure it out
in one instance why can't it figure it out when I put single quotes
around it?
Dave
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Tom Lane
Sent: October 2, 2001 5:19 PM
To: Barry Lind
Cc: Dave@micro-automation.net; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] driver fails to handle strings in query statements
properly
Barry Lind <barry@xythos.com> writes:
> My thoughts as well. If we encounter an 'unknown' type, just return
> it
> as a String. Do you have time to fix this?
>> I suppose we could return it as a string when we are in doubt? Is
>> this reasonable since there are many other types it should be
>> returned as?
The backend will presently default to treating 'unknown' as type 'text'
if it hasn't got any better idea what to do, so I think it's reasonable
for JDBC to do likewise.
I believe the only case in which you could see 'unknown' reported as a
query result type is if a string literal is SELECTed without any
processing being done on it, viz
SELECT 'foo'
As soon as the literal is passed to a function or operator, sorted,
grouped, etc, the system will insist on resolving it to a non-unknown
type. If the usage context doesn't provide any other hint, 'text' will
be the chosen type.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
"Dave Cramer" <Dave@micro-automation.net> writes:
> I tried it on a table which id was an int
> Using "select 'id' as xxx from ..." Returns xxx as an unknown type
> Using "select id as xxx from... " returns xxx as an int
> I have no idea how the backend handles this, but if it can figure it out
> in one instance why can't it figure it out when I put single quotes
> around it?
Uh ... when you put single quotes around it, it's a literal, not
a column reference ... and whatever one might think about 'id',
it's certainly not an integer.
regards, tom lane
As I said, I have no idea how the backend handles it when it is quoted.
You mention that it's not a column reference, but it does get data from
the id column?
Dave
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Tom Lane
Sent: October 2, 2001 6:16 PM
To: Dave@micro-automation.net
Cc: 'Barry Lind'; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] driver fails to handle strings in query statements
properly
"Dave Cramer" <Dave@micro-automation.net> writes:
> I tried it on a table which id was an int
> Using "select 'id' as xxx from ..." Returns xxx as an unknown type
> Using "select id as xxx from... " returns xxx as an int I have no idea
> how the backend handles this, but if it can figure it out in one
> instance why can't it figure it out when I put single quotes around
> it?
Uh ... when you put single quotes around it, it's a literal, not a
column reference ... and whatever one might think about 'id', it's
certainly not an integer.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: driver fails to handle strings in query statements properly (ignore my last post)
From
"Dave Cramer"
Date:
Ok, I need some sleep, now I see what it does, which is probably not
what the user wanted, but I have fixed the driver to it doesn't throw an
exception.
Dave
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Dave Cramer
Sent: October 2, 2001 9:12 PM
To: 'Tom Lane'
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] driver fails to handle strings in query statements
properly
As I said, I have no idea how the backend handles it when it is quoted.
You mention that it's not a column reference, but it does get data from
the id column?
Dave
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Tom Lane
Sent: October 2, 2001 6:16 PM
To: Dave@micro-automation.net
Cc: 'Barry Lind'; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] driver fails to handle strings in query statements
properly
"Dave Cramer" <Dave@micro-automation.net> writes:
> I tried it on a table which id was an int
> Using "select 'id' as xxx from ..." Returns xxx as an unknown type
> Using "select id as xxx from... " returns xxx as an int I have no idea
> how the backend handles this, but if it can figure it out in one
> instance why can't it figure it out when I put single quotes around
> it?
Uh ... when you put single quotes around it, it's a literal, not a
column reference ... and whatever one might think about 'id', it's
certainly not an integer.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
While fixing the handling of "unknown" data type in the result set I was faced with wading through the Serialize code. I am wondering if this is really a required/desireable feature? How many people out there are actually using it? Do we need/want it? My thoughts are: 1) There are plenty of persistence layers which do this job much better. 2) I don't think this belongs in a driver. 3) The code will be simpler. Dave
On Fri, 2001-10-05 at 14:57, Dave Cramer wrote: > While fixing the handling of "unknown" data type in the result set I was > faced with wading through the Serialize code. > > I am wondering if this is really a required/desireable feature? > > How many people out there are actually using it? > > Do we need/want it? > > My thoughts are: > > 1) There are plenty of persistence layers which do this job much better. > > 2) I don't think this belongs in a driver. > 3) The code will be simpler. How do I do a database request via JDBC and save the result set to a text file on the client computer? Isn't that what serialize is for? If so I need it in the driver.(and instructions on how to get it to work =:-b) Cheers Tony Grant -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html
Actually, no that's not what it's for. What it does is "Serialize" a class into the db "automagically". There are much better products out there with much better functionality See http://www.ambysoft.com/persistenceLayer.html I personally use Artom Rudoy's persistence layer www.sourceforge.net/projects/player The following code is an example of how to use Serialize in the postgres database package postgrestest; /** * Title: Postgres Tests * Description: * Copyright: Copyright (c) 2001 * Company: Ebox Inc * @author Dave Cramer * @version 1.0 */ /* * Java sample program */ import java.io.*; import java.sql.*; import org.postgresql.util.*; public class lili implements Serializable { public String myString; public int myNumber; public lili() throws ClassNotFoundException, FileNotFoundException, IOException, SQLException { myString="theString"; myNumber=4; } public boolean equals(lili l) { return (l.myString.equals(this.myString) && myNumber == this.myNumber); } private void testmethod(){}; public void testmethod2x(){}; public static void main(String args[]) { org.postgresql.Connection conn=null; try { lili mylili = new lili(); lili test = null; Class.forName("org.postgresql.Driver"); // load database interface // connect to the database conn = (org.postgresql.Connection)DriverManager.getConnection("jdbc:postgresql: //alpha.ebox.com/davec", "davec", ""); // works // test of serialisation org.postgresql.util.Serialize.create(conn, mylili); // makes problems org.postgresql.util.Serialize s = new Serialize(conn,mylili); int oid = s.store(mylili); test = (lili)s.fetch(oid); if (mylili.equals(test)){ System.out.println("Success"); }else{ System.out.println("Failure"); } } catch(Exception exc){ System.err.println("Exception caught.\n" + exc); exc.printStackTrace(); }finally{ try { if (conn != null) conn.close(); } catch (Exception ex){ ; } } } } -----Original Message----- From: Tony Grant [mailto:tony@animaproductions.com] Sent: October 5, 2001 9:34 AM To: Dave@micro-automation.net Cc: jdbc list Subject: Re: [JDBC] Serialize On Fri, 2001-10-05 at 14:57, Dave Cramer wrote: > While fixing the handling of "unknown" data type in the result set I > was faced with wading through the Serialize code. > > I am wondering if this is really a required/desireable feature? > > How many people out there are actually using it? > > Do we need/want it? > > My thoughts are: > > 1) There are plenty of persistence layers which do this job much > better. > > 2) I don't think this belongs in a driver. > 3) The code will be simpler. How do I do a database request via JDBC and save the result set to a text file on the client computer? Isn't that what serialize is for? If so I need it in the driver.(and instructions on how to get it to work =:-b) Cheers Tony Grant -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html
| "Dave Cramer" <Dave@micro-automation.net> wrote: | | There are much better products out there with much better functionality | | See http://www.ambysoft.com/persistenceLayer.html | | I personally use Artom Rudoy's persistence layer | | www.sourceforge.net/projects/player Turbine at jakarta.apache.org has a persistence layer based on Scott Amblers paper as well. Maybe a list of persistence layers(with a short review of how they work with PostgreSQL) would be a good addon for the JDBC site. -- Gunnar Rønning - gunnar@polygnosis.com Senior Consultant, Polygnosis AS, http://www.polygnosis.com/
Dave, I share your concerns about this functionality. In fact last month I wrote the following in response to a patch to this functionality. I wouldn't mind this being pulled out of the main code line for now and moved to contrib until it becomes more robust. >>Robert, >> >> >>Thanks for the patch. While I didn't review it too closely, what I >>saw seemed fine. >> >>However, I have concerns about the feature as a whole, which really >>has nothing to do with your patch. >> >>I don't like the approach taken here for serialization. It seems to >>me that this is a half baked attempt to reimplement native java >>serialization. Why not just use java serialization to do what it was >>intended for? We could then store the results of the serialization in >>a bytea column, or a LargeObject, or even in a separate table as is >>done here. However, I am unsure of the desireabilty of creating these >>additional tables. The bigest reason I don't like the additional >>tables is that the serialized objects don't ever get deleted. >> >>To the extent that this is documented, I think the feature should be >>marked as experimental with a caution that it may be changed in major >>non-backwardly compatible ways in the future. >> >>thanks, >>--Barry There are two areas in the current code that I am uncomfortable with. This serialize code and the code in org/postgresql/xa. Have you looked at the xa code? thanks, --Barry Dave Cramer wrote: > While fixing the handling of "unknown" data type in the result set I was > faced with wading through the Serialize code. > > I am wondering if this is really a required/desireable feature? > > How many people out there are actually using it? > > Do we need/want it? > > My thoughts are: > > 1) There are plenty of persistence layers which do this job much better. > > 2) I don't think this belongs in a driver. > 3) The code will be simpler. > > Dave > >