Обсуждение: Error using JDBC2 postgres driver and DatabaseMetaData.getTables()
I am attempting to use an Object Relational Code Generation engine (cartographer, http://cartographer.sourceforge.com) with a database that I have in Postgresql 7.2 running under RedHat Linux 7.3 and JDK 1.4.0_01. The error I am encountering, and the stack trace are: //----- Begin Error Message -----// java.sql.SQLException: ERROR: parser: parse error at or near ")" at org.postgresql.core.QueryExecutor.execute(Unknown Source) at org.postgresql.Connection.ExecSQL(Unknown Source) at org.postgresql.Connection.ExecSQL(Unknown Source) at org.postgresql.jdbc2.DatabaseMetaData.getTables(Unknown Source) at cartographer.Mapper.mapDatasource(Mapper.java:133) at cartographer.Cartographer.main(Cartographer.java:109) //----- End Error Message -----// The offending piece of code in the Mapper object follows: //----- Begin Code Block -----// private Properties props; public Mapper(Properties props) { this.props = props; } /** * */ public Vector mapDatasource() { Connection con = null; ResultSet rs = null; String [] tableTypes = { "Table" }; Vector tableNameVector = new Vector(); try { log("Connecting to datasource: " + props.getProperty("url")); ConnectionManager cm = new ConnectionManager(props.getProperty("driver"), props.getProperty("url"), props.getProperty("user"), props.getProperty("pass") ); con = cm.getConnection(); DatabaseMetaData dbmd = con.getMetaData(); log("Database: " + dbmd.getDatabaseProductName()); log("Database Version: " + dbmd.getDatabaseProductVersion()); log("Driver: " + dbmd.getDriverName()); log("Driver Version: " + dbmd.getDriverVersion()); log("\n"); rs = dbmd.getTables(null,null,null,tableTypes); //rs = dbmd.getTables(null,null,null,null); while( rs.next() ) { tableNameVector.addElement(rs.getString(3)); } rs.close(); con.close(); } catch(SQLException sqle) { sqle.printStackTrace(); } catch(Exception e) { e.printStackTrace(); } return tableNameVector; } //----- End Code Block -----// The stack trace points to line 133 which is: rs = dbmd.getTables(null,null,null,tableTypes); I have also tried: rs = dbmd.getTables(null,null,null,null); Both of these methods yield the same error message. I have used this program with other databases and drivers without a problem (e.g. MySql and MS SQL Server 2000). Can anyone see where the problem might be? Or what course of action I could take to fix this problem? Thanks, Andrew Lee alee585@hotmail.com Software Engineer Bensoft, Inc _________________________________________________________________ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx
I know that if I want to return resultsets from stored procedures in PgSQL I should return REFCURSOR return type. But this doesn't return updatable resultsets. How can I do that? -- Felipe Schnack Analista de Sistemas felipes@ritterdosreis.br Cel.: (51)91287530 Linux Counter #281893 Faculdade Ritter dos Reis www.ritterdosreis.br felipes@ritterdosreis.br Fone/Fax.: (51)32303328
Felipe, Only the dev driver on http://jdbc.postgresql.org supports updateable result sets. Dave On Mon, 2002-08-19 at 08:41, Felipe Schnack wrote: > I know that if I want to return resultsets from stored procedures in > PgSQL I should return REFCURSOR return type. But this doesn't return > updatable resultsets. How can I do that? > -- > > Felipe Schnack > Analista de Sistemas > felipes@ritterdosreis.br > Cel.: (51)91287530 > Linux Counter #281893 > > Faculdade Ritter dos Reis > www.ritterdosreis.br > felipes@ritterdosreis.br > Fone/Fax.: (51)32303328 > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > >
I'm using it, but the result from a stored procedure isn't updatable On Mon, 2002-08-19 at 10:17, Dave Cramer wrote: > Felipe, > > Only the dev driver on http://jdbc.postgresql.org supports updateable > result sets. > > Dave > On Mon, 2002-08-19 at 08:41, Felipe Schnack wrote: > > I know that if I want to return resultsets from stored procedures in > > PgSQL I should return REFCURSOR return type. But this doesn't return > > updatable resultsets. How can I do that? > > -- > > > > Felipe Schnack > > Analista de Sistemas > > felipes@ritterdosreis.br > > Cel.: (51)91287530 > > Linux Counter #281893 > > > > Faculdade Ritter dos Reis > > www.ritterdosreis.br > > felipes@ritterdosreis.br > > Fone/Fax.: (51)32303328 > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > > > > > > ---------------------------(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 -- Felipe Schnack Analista de Sistemas felipes@ritterdosreis.br Cel.: (51)91287530 Linux Counter #281893 Faculdade Ritter dos Reis www.ritterdosreis.br felipes@ritterdosreis.br Fone/Fax.: (51)32303328
"Andrew Lee" <alee585@hotmail.com> writes:
> java.sql.SQLException: ERROR: parser: parse error at or near ")"
It's hard to tell from this exactly what the parser is complaining
about. I'd suggest turning on query logging (debug_print_query) and
then a look in the postmaster log should show the exact text of the
query the parser is unhappy with. If that doesn't yield immediate
enlightenment, send it along and we'll see what we can do.
regards, tom lane
Oh.... it's in the driver. it is building the query string improperly, let me look at it again Dave On Mon, 2002-08-19 at 09:49, Tom Lane wrote: > "Andrew Lee" <alee585@hotmail.com> writes: > > java.sql.SQLException: ERROR: parser: parse error at or near ")" > > It's hard to tell from this exactly what the parser is complaining > about. I'd suggest turning on query logging (debug_print_query) and > then a look in the postmaster log should show the exact text of the > query the parser is unhappy with. If that doesn't yield immediate > enlightenment, send it along and we'll see what we can do. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > >
Ok, I have verified that in the dev driver it is possible to call getTables(null, null, null, null) Dave On Mon, 2002-08-19 at 09:49, Tom Lane wrote: > "Andrew Lee" <alee585@hotmail.com> writes: > > java.sql.SQLException: ERROR: parser: parse error at or near ")" > > It's hard to tell from this exactly what the parser is complaining > about. I'd suggest turning on query logging (debug_print_query) and > then a look in the postmaster log should show the exact text of the > query the parser is unhappy with. If that doesn't yield immediate > enlightenment, send it along and we'll see what we can do. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > >
> > java.sql.SQLException: ERROR: parser: parse error at or near ")"
>
>It's hard to tell from this exactly what the parser is complaining
>about. I'd suggest turning on query logging (debug_print_query) and
>then a look in the postmaster log should show the exact text of the
>query the parser is unhappy with. If that doesn't yield immediate
>enlightenment, send it along and we'll see what we can do.
I turned on all of the logging that I could find. Here is the excerpt
from my postgresql.conf file:
//----- BEGIN CONF FILE -----//
debug_level = 4 # range 0-16
debug_print_query = true
debug_print_parse = true
debug_print_rewritten = true
debug_print_plan = true
debug_pretty_print = true
//---- END CONF FILE -----//
Running the same program again under the exact same conditions yielded
the same error and the following entry in the file I directed the startup
script to log to:
//----- BEGIN LOG -----//
postmaster successfully started
/usr/bin/postmaster: PostmasterMain: initial environ dump:
-----------------------------------------
PWD=/var/lib/pgsql
XAUTHORITY=/root/.xauth45pP3j
HOSTNAME=localhost.localdomain
QTDIR=/usr/lib/qt3-gcc2.96
LESSOPEN=|/usr/bin/lesspipe.sh %s
USER=postgres
LS_COLORS=no=00:fi=00:di=01;34:ln=01;36:pi=40;33:so=01;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=01;32:*.cmd=01;
32:*.exe=01;32:*.com=01;32:*.btm=01;32:*.bat=01;32:*.sh=01;32:*.csh=01;32:*.tar=01;31:*.tgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.zi
p=01;31:*.z=01;31:*.Z=01;31:*.gz=01;31:*.bz2=01;31:*.bz=01;31:*.tz=01;31:*.rpm=01;31:*.cpio=01;31:*.jpg=01;35:*.gif=01;35:*.bmp=01;35:*.xb
m=01;35:*.xpm=01;35:*.png=01;35:*.tif=01;35:
MAIL=/var/spool/mail/postgres
INPUTRC=/etc/inputrc
LANG=en_US
DISPLAY=:0.0
LOGNAME=postgres
SHLVL=2
SHELL=/bin/sh
HISTSIZE=1000
TERM=xterm
HOME=/var/lib/pgsql
SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
PGDATA=/var/lib/pgsql/data
PATH=/bin:/usr/bin:/usr/local/bin:/usr/bin/X11:/usr/X11R6/bin
_=/usr/bin/postmaster
-----------------------------------------
FindExec: found "/usr/bin/postgres" using argv[0]
invoking IpcMemoryCreate(size=1417216)
FindExec: found "/usr/bin/postmaster" using argv[0]
2002-08-19 21:35:57 [2331] DEBUG: database system was shut down at
2002-08-19 00:51:35 CDT
2002-08-19 21:35:57 [2331] DEBUG: checkpoint record is at 0/402372C
2002-08-19 21:35:57 [2331] DEBUG: redo record is at 0/402372C; undo
record is at 0/0; shutdown TRUE
2002-08-19 21:35:57 [2331] DEBUG: next transaction id: 51691; next oid:
66197
2002-08-19 21:35:57 [2331] DEBUG: database system is ready
2002-08-19 21:35:57 [2331] DEBUG: proc_exit(0)
2002-08-19 21:35:57 [2331] DEBUG: shmem_exit(0)
2002-08-19 21:35:57 [2331] DEBUG: exit(0)
2002-08-19 21:35:57 [2327] DEBUG: reaping dead processes
2002-08-19 21:36:19 [2327] DEBUG: BackendStartup: forked pid=2349
socket=8
2002-08-19 21:36:19 [2349] DEBUG: connection: host=127.0.0.1 user=bmadmin
database=bmstore
/usr/bin/postmaster child[2349]: starting with (postgres -d4 -v131072 -p
bmstore )
2002-08-19 21:36:19 [2349] DEBUG: InitPostgres
2002-08-19 21:36:19 [2349] DEBUG: StartTransactionCommand
2002-08-19 21:36:19 [2349] DEBUG: query: set datestyle to 'ISO'; select
version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then
'UNKNOWN' else getdatabaseencoding() end;
2002-08-19 21:36:19 [2349] DEBUG: parse tree:
{ QUERY
//----- END LOG -----//
This doesn't really mean much to me. I hope this helps.
Let me know if I need to tweak the debug settings.
Thanks,
Andrew Lee
alee585@hotmail.com
Software Engineer
Bensoft, Inc
_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com
Andrew,
The driver is expecting the table type to be all upper case,
If you use the dev driver it will also accept null for all the args
Dave
On Mon, 2002-08-19 at 22:42, Andrew Lee wrote:
> > > java.sql.SQLException: ERROR: parser: parse error at or near ")"
> >
> >It's hard to tell from this exactly what the parser is complaining
> >about. I'd suggest turning on query logging (debug_print_query) and
> >then a look in the postmaster log should show the exact text of the
> >query the parser is unhappy with. If that doesn't yield immediate
> >enlightenment, send it along and we'll see what we can do.
>
> I turned on all of the logging that I could find. Here is the excerpt
> from my postgresql.conf file:
>
> //----- BEGIN CONF FILE -----//
> debug_level = 4 # range 0-16
>
> debug_print_query = true
> debug_print_parse = true
> debug_print_rewritten = true
> debug_print_plan = true
> debug_pretty_print = true
> //---- END CONF FILE -----//
>
> Running the same program again under the exact same conditions yielded
> the same error and the following entry in the file I directed the startup
> script to log to:
>
> //----- BEGIN LOG -----//
>
> postmaster successfully started
> /usr/bin/postmaster: PostmasterMain: initial environ dump:
> -----------------------------------------
> PWD=/var/lib/pgsql
> XAUTHORITY=/root/.xauth45pP3j
> HOSTNAME=localhost.localdomain
> QTDIR=/usr/lib/qt3-gcc2.96
> LESSOPEN=|/usr/bin/lesspipe.sh %s
> USER=postgres
>
>
LS_COLORS=no=00:fi=00:di=01;34:ln=01;36:pi=40;33:so=01;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=01;32:*.cmd=01;
>
32:*.exe=01;32:*.com=01;32:*.btm=01;32:*.bat=01;32:*.sh=01;32:*.csh=01;32:*.tar=01;31:*.tgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.zi
>
p=01;31:*.z=01;31:*.Z=01;31:*.gz=01;31:*.bz2=01;31:*.bz=01;31:*.tz=01;31:*.rpm=01;31:*.cpio=01;31:*.jpg=01;35:*.gif=01;35:*.bmp=01;35:*.xb
> m=01;35:*.xpm=01;35:*.png=01;35:*.tif=01;35:
> MAIL=/var/spool/mail/postgres
> INPUTRC=/etc/inputrc
> LANG=en_US
> DISPLAY=:0.0
> LOGNAME=postgres
> SHLVL=2
> SHELL=/bin/sh
> HISTSIZE=1000
> TERM=xterm
> HOME=/var/lib/pgsql
> SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
> PGDATA=/var/lib/pgsql/data
> PATH=/bin:/usr/bin:/usr/local/bin:/usr/bin/X11:/usr/X11R6/bin
> _=/usr/bin/postmaster
> -----------------------------------------
> FindExec: found "/usr/bin/postgres" using argv[0]
> invoking IpcMemoryCreate(size=1417216)
> FindExec: found "/usr/bin/postmaster" using argv[0]
> 2002-08-19 21:35:57 [2331] DEBUG: database system was shut down at
> 2002-08-19 00:51:35 CDT
> 2002-08-19 21:35:57 [2331] DEBUG: checkpoint record is at 0/402372C
> 2002-08-19 21:35:57 [2331] DEBUG: redo record is at 0/402372C; undo
> record is at 0/0; shutdown TRUE
> 2002-08-19 21:35:57 [2331] DEBUG: next transaction id: 51691; next oid:
> 66197
> 2002-08-19 21:35:57 [2331] DEBUG: database system is ready
> 2002-08-19 21:35:57 [2331] DEBUG: proc_exit(0)
> 2002-08-19 21:35:57 [2331] DEBUG: shmem_exit(0)
> 2002-08-19 21:35:57 [2331] DEBUG: exit(0)
> 2002-08-19 21:35:57 [2327] DEBUG: reaping dead processes
> 2002-08-19 21:36:19 [2327] DEBUG: BackendStartup: forked pid=2349
> socket=8
> 2002-08-19 21:36:19 [2349] DEBUG: connection: host=127.0.0.1 user=bmadmin
> database=bmstore
> /usr/bin/postmaster child[2349]: starting with (postgres -d4 -v131072 -p
> bmstore )
> 2002-08-19 21:36:19 [2349] DEBUG: InitPostgres
> 2002-08-19 21:36:19 [2349] DEBUG: StartTransactionCommand
> 2002-08-19 21:36:19 [2349] DEBUG: query: set datestyle to 'ISO'; select
> version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then
> 'UNKNOWN' else getdatabaseencoding() end;
> 2002-08-19 21:36:19 [2349] DEBUG: parse tree:
> { QUERY
>
> //----- END LOG -----//
>
> This doesn't really mean much to me. I hope this helps.
>
> Let me know if I need to tweak the debug settings.
>
> Thanks,
> Andrew Lee
> alee585@hotmail.com
> Software Engineer
> Bensoft, Inc
>
> _________________________________________________________________
> Send and receive Hotmail on your mobile device: http://mobile.msn.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>