Thread: CachedRowSetXImpl() and PostgreSQL
I am having some trouble getting CachedRowSetXImpl() to work with
postgresql-8.1-407.jdbc3.jar in
this part of a SessionBean
CachedRowSetXImpl crsx = new CachedRowSetXImpl();
crsx.setDataSourceName("java:comp/env/jdbc/MyDataSource");
crsx.setTableName("table");
crsx.setCommand("SELECT ALL id, url FROM my.table ");
Getting the data is no problem, but when updating it's not using the
correct tablename
I have tried crsx.setTableName("my.table");
But then I get a "No columns in table" as this statement will not find
the table "my.table":
-----
SELECT
n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description
FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON
(c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON
(a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON
(a.attrelid=def.adrelid AND a.attnum = def.adnum) LEFT JOIN
pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum =
dsc.objsubid) LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid
AND dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON
(dc.relnamespace=dn.oid AND dn.nspname='pg_catalog') WHERE a.attnum > 0
AND NOT a.attisdropped AND c.relname LIKE 'my.table' AND a.attname
LIKE 'id' ORDER BY nspname,relname,attnum
-----
It should have used the name "table" instead of "my.table"
Without the schema name, I get a "relation not found.
Then I tried adding this:
crsx.setSchemaName("my");
But then It stops in an exception with no error message right after
setting transaction level:
-----
StandardContext[/MyApp]Error Description
java.lang.RuntimeException
at
com.sun.data.provider.impl.CachedRowSetDataProvider.commitChanges(CachedRowSetDataProvider.java:878)
-----
How is it possible getting it to work ?
Thanks,
Poul
> I am having some trouble getting CachedRowSetXImpl() to work with > postgresql-8.1-407.jdbc3.jar in > this part of a SessionBean > Is this list dead ? :) Have found a workaround. Avoid using schema names and set the search path: SHOW search_path ALTER USER me SET search_path TO public,my,anotherschema Poul
Hi, Poul, Poul Møller Hansen wrote: > >> I am having some trouble getting CachedRowSetXImpl() to work with >> postgresql-8.1-407.jdbc3.jar in >> this part of a SessionBean >> > Is this list dead ? :) Not yet, but e-mail is no real-time media. You should expect one or two business days delay, as most people here are reading in their free time. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org
>> Is this list dead ? :) >> Of course I wasn't expecting an instant answer. I was just wondering that there hasn't been other emails in the period. Poul
> > If you could at least track it down to a particular metadata call that > the driver is incorrectly implementing that might get a response, but > as it is there is nothing I can debug here. Isn't that what I did ? Here is all statements when setting the table name to "my.table": ----- SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN; SELECT t.typlen FROM pg_catalog.pg_type t, pg_catalog.pg_namespace n WHERE t.typnamespace=n.oid AND t.typname='name' AND n.nspname='pg_catalog'; SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog') WHERE a.attnum > 0 AND NOT a.attisdropped AND c.relname LIKE 'my.table' AND a.attname LIKE 'id' ORDER BY nspname,relname,attnum; SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog') WHERE a.attnum > 0 AND NOT a.attisdropped AND c.relname LIKE 'my.table' AND a.attname LIKE 'url' ORDER BY nspname,relname,attnum; ROLLBACK; SHOW TRANSACTION ISOLATION LEVEL; ----- It will not fine "my.table" as it should have been "table" only. If I set the tablename to "table" the result is this: ----- SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN; SELECT t.typlen FROM pg_catalog.pg_type t, pg_catalog.pg_namespace n WHERE t.typnamespace=n.oid AND t.typname='name' AND n.nspname='pg_catalog'; SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog') WHERE a.attnum > 0 AND NOT a.attisdropped AND c.relname LIKE 'table' AND a.attname LIKE 'id' ORDER BY nspname,relname,attnum; SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog') WHERE a.attnum > 0 AND NOT a.attisdropped AND c.relname LIKE 'table' AND a.attname LIKE 'url' ORDER BY nspname,relname,attnum; SELECT id, url FROM table WHERE id = $1 AND url = $2 2006-09-21 14:30:40 CEST - ERROR: relation "table" does not exist ROLLBACK; SHOW TRANSACTION ISOLATION LEVEL; ----- Please let me know if more is needed. Poul
Poul Møller Hansen wrote: > Please let me know if more is needed. What we need to know is what is the metadata call being made to the driver and how do the results differ from what is expected? BTW, it's possible to have a table called "my.table" (where "my." is part of the table name, not a schema prefix) so the driver seems to be doing the right thing if it is being asked about tables called "my.table" by the RowSet .. But that's why we need to know what metadata call is being made. I think finding out why setSchemaName on your rowset does not work is the first step, that RuntimeException tells me nothing about the real cause of the problem. -O
> What we need to know is what is the metadata call being made to the
> driver and how do the results differ from what is expected?
>
That's pretty much hidden behind the curtains in the Java classes. How
can I log that ?
> BTW, it's possible to have a table called "my.table" (where "my." is
> part of the table name, not a schema prefix) so the driver seems to be
> doing the right thing if it is being asked about tables called
> "my.table" by the RowSet .. But that's why we need to know what
> metadata call is being made.
>
my is the schema name. I have tried 3 setups:
1. setting setTableName("my.table");
2. setTableName("table");
3. setSchemaName("my"); setTableName("table");
And none of them works. When getting the metadata from pg_catalog the
schema name must be left out
and when doing update the schema name must be included or it can't find
the relation.
It's like the setSchemaName has no effect
> I think finding out why setSchemaName on your rowset does not work is
> the first step, that RuntimeException tells me nothing about the real
> cause of the problem.
Exactly, but I'm clueless how.
Poul
Poul Møller Hansen wrote:
>
>> What we need to know is what is the metadata call being made to the
>> driver and how do the results differ from what is expected?
>>
> That's pretty much hidden behind the curtains in the Java classes. How
> can I log that ?
Well, we have exactly the same problem, without seeing the code that
calls the driver it is very difficult to know if the fault lies with the
driver or the calling code.
I think there are some intercepting JDBC driver wrappers that might help
you with the "what is the metadata call" bit (I can't remember the name
of one offhand) but the "how do the results differ" requires some
knowledge of what the calling code is expecting.. which probably means
"contact the implementor of your RowSet".
>> BTW, it's possible to have a table called "my.table" (where "my." is
>> part of the table name, not a schema prefix) so the driver seems to be
>> doing the right thing if it is being asked about tables called
>> "my.table" by the RowSet .. But that's why we need to know what
>> metadata call is being made.
>>
> my is the schema name. I have tried 3 setups:
> 1. setting setTableName("my.table");
> 2. setTableName("table");
> 3. setSchemaName("my"); setTableName("table")
I would expect (3) to be what you need.
>> I think finding out why setSchemaName on your rowset does not work is
>> the first step, that RuntimeException tells me nothing about the real
>> cause of the problem.
>
> Exactly, but I'm clueless how.
Again I think this is going to be "talk to the RowSet implementor"
unfortunately.
-O