Thread: JDBC

Hi,

I've downloaded the pg74.215.jdbc3.jar-driver (I run  jdk 1.4). Do I now
have to install or build it or is it ready for use? I've tried pointing it
out with a classpath and loaded it with
"Class.forName("org.postgresql.Driver")". I get an exception but I don't
why.

/Hanna

On Tue, 24 Aug 2004, Hanna Tapani wrote:

> I've downloaded the pg74.215.jdbc3.jar-driver (I run  jdk 1.4). Do I now
> have to install or build it or is it ready for use? I've tried pointing it
> out with a classpath and loaded it with
> "Class.forName("org.postgresql.Driver")". I get an exception but I don't
> why.

You don't need to do anything other than make the jar file available to
your application via the classpath which I would guess is not set
correctly in this case.

Kris Jurka

I created a new, empty database and I want to test whether a table
exists or not.  I've googled and thought what I read @ stackoverflow
indicated the following should let me know (I've also seen the same at
other sites):

At instantiation result is set to false.

    private boolean checkEntity()
        // this checks whether this is a new database and tables have
not yet been created and prevent a null pointer exception
        // entities table must exist to use the system
        try {
            stat = connection.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(ClientConstants.class.getName()).log(Level.SEVERE,
null, ex);
        }

        try {
            result = stat.execute("select exists (select 1 from
information_schema.tables where table_name = 'entities')");
            System.out.println("the checkEntity returned a result of "
+ result);
        } catch (SQLException ex) {
            Logger.getLogger(ClientConstants.class.getName()).log(Level.SEVERE,
null, ex);
            result = false;
            return result;
        }

        return result;

    }

Even though no objects have been created in the database this result
is always returned true.  The documentation says the
information_schema is unique for each database so this statement
should return false until the table is created....I think.

I've also used this version with the same result:
            result = stat.execute("SELECT EXISTS (SELECT 1 FROM
information_schema.tables WHERE table_schema = 'jpl' AND table_name =
'entities')");

The only thing I can think of is that the information_schema is
actually for the cluster.  I have other databases in the cluster that
do have an entities table.

Any direction will be appreciated.

Chuck Davis <cjgunzel@gmail.com> writes:
> I created a new, empty database and I want to test whether a table
> exists or not.

Are you sure the DB is in fact empty?

> The only thing I can think of is that the information_schema is
> actually for the cluster.

It is not.

> I have other databases in the cluster that
> do have an entities table.

I'm betting that you created such a table in template1, so that it's
getting cloned into new databases by CREATE DATABASE.

            regards, tom lane

        try {
            result = stat.execute("select exists (select 1 from
information_schema.tables where table_name = 'entities')");
            System.out.println("the checkEntity returned a result of "
+ result);
        } catch (SQLException ex) {
            Logger.getLogger(ClientConstants.class.getName()).log(Level.SEVERE,
null, ex);
            result = false;
            return result;
        }

Yes, I've double checked.  There are no user space tables in the database.

On Sun, Jun 10, 2018 at 1:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Chuck Davis <cjgunzel@gmail.com> writes:
>> I created a new, empty database and I want to test whether a table
>> exists or not.
>
> Are you sure the DB is in fact empty?
>
>> The only thing I can think of is that the information_schema is
>> actually for the cluster.
>
> It is not.
>
>> I have other databases in the cluster that
>> do have an entities table.
>
> I'm betting that you created such a table in template1, so that it's
> getting cloned into new databases by CREATE DATABASE.
>
>                         regards, tom lane

If I try to return a ResultSet the class will not compile with the
message that boolean cannot be converted to a ResultSet.  And this is
in synch with the documentation that states the "exists" subquery will
return a boolean -- not a ResultSet.

There are a number of sites that indicate the statement is the way to
find out if a table exists.  Are the sites for an older version of PG?
 I'm running on 10.

Thanks.

On Sun, Jun 10, 2018 at 1:44 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Sunday, June 10, 2018, Chuck Davis <cjgunzel@gmail.com> wrote:
>>
>>
>>         try {
>>             result = stat.execute("select exists (select 1 from
>> information_schema.tables where table_name = 'entities')");
>>             System.out.println("the checkEntity returned a result of "
>> + result);
>>         } catch (SQLException ex) {
>>
>> Logger.getLogger(ClientConstants.class.getName()).log(Level.SEVERE,
>> null, ex);
>>             result = false;
>>             return result;
>>         }
>
>
> Your query is putting "false" into cell (0,0) of a ResultSet.  You are
> failing to even look at the resultset to see if it holds a true or false.
>
> The query should never fail since you aren't using the table name directly
> but are checking for it as a value in another table that always exists. The
> inner query returns zero records when the table doesn't exist and the EXISTS
> construct converts that to false.
>
> David J.
>

If I query without the "exists" clause I get the PG exception that the
relation does not exist (which I guess tells me the table does not
exist) but I'd like a better way to test for existence than
exceptions.

On Sun, Jun 10, 2018 at 4:15 PM, Dave Cramer <pg@fastcrypt.com> wrote:
>
> On 10 June 2018 at 19:09, Chuck Davis <cjgunzel@gmail.com> wrote:
>>
>> If I try to return a ResultSet the class will not compile with the
>> message that boolean cannot be converted to a ResultSet.  And this is
>> in synch with the documentation that states the "exists" subquery will
>> return a boolean -- not a ResultSet.
>>
>> There are a number of sites that indicate the statement is the way to
>> find out if a table exists.  Are the sites for an older version of PG?
>>  I'm running on 10.
>>
>> Thanks.
>>
>> On Sun, Jun 10, 2018 at 1:44 PM, David G. Johnston
>> <david.g.johnston@gmail.com> wrote:
>> > On Sunday, June 10, 2018, Chuck Davis <cjgunzel@gmail.com> wrote:
>> >>
>> >>
>> >>         try {
>> >>             result = stat.execute("select exists (select 1 from
>> >> information_schema.tables where table_name = 'entities')");
>> >>             System.out.println("the checkEntity returned a result of "
>> >> + result);
>> >>         } catch (SQLException ex) {
>> >>
>> >> Logger.getLogger(ClientConstants.class.getName()).log(Level.SEVERE,
>> >> null, ex);
>> >>             result = false;
>> >>             return result;
>> >>         }
>> >
>> >
>> > Your query is putting "false" into cell (0,0) of a ResultSet.  You are
>> > failing to even look at the resultset to see if it holds a true or
>> > false.
>> >
>> > The query should never fail since you aren't using the table name
>> > directly
>> > but are checking for it as a value in another table that always exists.
>> > The
>> > inner query returns zero records when the table doesn't exist and the
>> > EXISTS
>> > construct converts that to false.
>> >
>> > David J.
>> >
>>
>
>
> idiomatic java suggests that you need to do
>
> resultset rs = statement.query(your select statement)
>
> if rs.next then
> result = rs.getInt(1)
>
> close result set and statement
>
> return result == 1
>

If I try to return a ResultSet the class will not compile with the
message that boolean cannot be converted to a ResultSet.  And this is
in synch with the documentation that states the "exists" subquery will
return a boolean -- not a ResultSet.

There are a number of sites that indicate the statement is the way to
find out if a table exists.  Are the sites for an older version of PG?
 I'm running on 10.

Thanks David.  That got me going.  Getting the "false" value I expected.

On Sun, Jun 10, 2018 at 4:26 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Sunday, June 10, 2018, Chuck Davis <cjgunzel@gmail.com> wrote:
>>
>> If I try to return a ResultSet the class will not compile with the
>> message that boolean cannot be converted to a ResultSet.  And this is
>> in synch with the documentation that states the "exists" subquery will
>> return a boolean -- not a ResultSet.
>>
>
> The PostgreSQL documentation doesn't talk in terms JDBC, the Oracle JDBC API
> specification covers that.
>
> https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html
>
> JDBC uses ResultSet to return the results of queries.
>
>>
>> There are a number of sites that indicate the statement is the way to
>> find out if a table exists.  Are the sites for an older version of PG?
>>  I'm running on 10.
>
>
> This really has nothing to do with PostgreSQL specifically, you are failing
> to use the JDBC API correctly.  In the short term you should probably just
> use "executeQuery" instead of "execute" since you are in fact executing a
> query that returns a ReaultSet (which is exactly what execute's true boolean
> result is telling you).
>
> David J.
>