Thread: Error using JDBC2 postgres driver and DatabaseMetaData.getTables()

Error using JDBC2 postgres driver and DatabaseMetaData.getTables()

From

"Andrew Lee"

Date:

19 August 2002, 04:15:45

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

Stored procedures

From

Felipe Schnack

Date:

19 August 2002, 11:39:22

  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

Re: Stored procedures

From

Dave Cramer

Date:

19 August 2002, 12:20:13

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
>
>

Re: Stored procedures

From

Felipe Schnack

Date:

19 August 2002, 12:26:49

  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

Re: Error using JDBC2 postgres driver and DatabaseMetaData.getTables()

From

Tom Lane

Date:

19 August 2002, 12:49:46

"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

Re: Error using JDBC2 postgres driver and

From

Dave Cramer

Date:

19 August 2002, 13:29:19

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
>
>

Re: Error using JDBC2 postgres driver and

From

Dave Cramer

Date:

19 August 2002, 14:39:45

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
>
>

Re: Error using JDBC2 postgres driver and DatabaseMetaData.getTables()

From

"Andrew Lee"

Date:

20 August 2002, 01:42:03

> > 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

Re: Error using JDBC2 postgres driver and

From

Dave Cramer

Date:

20 August 2002, 02:03:51

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)
>
>