Re: Re: JDBC 2.0 conformance, documentation and todo list

Hello,

I've created a web page that aims to document the level of
conformance of the JDBC driver to the JDBC 2.0 API. It also aims
to document any deviations from the JDBC standard that have been
found.
http://lab.applinet.nl/postgresql-jdbc/

No need to say its still far from complete. I just wrote the
sections on Array and Batch Updates. I'm volunteering to finish
and maintain this page if people find it useful. Any comments?

Any feedback or additions will be greatly appreciated. If you
know about a JDBC 2.0 feature that seems to be missing or a
feature you know is implemented, please post a message on this
list.

The reason I created this page is that I wanted to work on JDBC
2.0 conformance, and except for Blob/Clob support Barry Lind
wrote about a couple of days ago, it wasn't clear what needed to
be done. Investigating and documenting the conformance level and
known deficiencies seemed like a good place to start.

Regards,
René Pijlman

Rene,

Have your run the JDBC conformance tests to help generate this
information? There was a post a few days ago from one of the RedHat
engineers saying they had run it and were going to summarize the results
and post back to the list. (I haven't yet seen that followup). I ran
the tests myself over the weekend, but haven't sorted through the
results either. If you want I could send you the log from that run.

thanks,
--Barry

Rene Pijlman wrote:
> Hello,
>
> I've created a web page that aims to document the level of
> conformance of the JDBC driver to the JDBC 2.0 API. It also aims
> to document any deviations from the JDBC standard that have been
> found.
> http://lab.applinet.nl/postgresql-jdbc/
>
> No need to say its still far from complete. I just wrote the
> sections on Array and Batch Updates. I'm volunteering to finish
> and maintain this page if people find it useful. Any comments?
>
> Any feedback or additions will be greatly appreciated. If you
> know about a JDBC 2.0 feature that seems to be missing or a
> feature you know is implemented, please post a message on this
> list.
>
> The reason I created this page is that I wanted to work on JDBC
> 2.0 conformance, and except for Blob/Clob support Barry Lind
> wrote about a couple of days ago, it wasn't clear what needed to
> be done. Investigating and documenting the conformance level and
> known deficiencies seemed like a good place to start.
>
> Regards,
> René Pijlman
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>

On Wed, 08 Aug 2001 09:50:11 -0700, you wrote:
>Have your run the JDBC conformance tests to help generate this
>information?

Yes, I have run the test (1.2.1 EE version) and looked at some
of the results. This will take more time, since there are
hundreds of messages and it isn't always easy to find out what's
going on.

Unfortunately, I'm getting many failed results with messages
like this:

********************************************************************************
Beginning Test: testAllProceduresAreCallable
********************************************************************************
Using DataSource
ERROR: SQL Exception : No suitable driver
Calling allProceduresAreCallable on DatabaseMetaData
ERROR: Unexpected exception null
ERROR: Call to allProceduresAreCallable is Failed!
ERROR: java.lang.NullPointerException

And I'm quite sure this code in DatabaseMetaData isn't causing
the problem :-)

public boolean allProceduresAreCallable() throws SQLException
{
return true;
}

>There was a post a few days ago from one of the RedHat
>engineers saying they had run it and were going to summarize the results
>and post back to the list. (I haven't yet seen that followup).

Would be good to here from them.

>I ran the tests myself over the weekend, but haven't sorted
>through the results either.

Could you do a quick grep for the message quoted above? I'd
really like to know if this is caused by a problem in my
setup... thanks.

>Rene Pijlman wrote:
>> I've created a web page that aims to document the level of
>> conformance of the JDBC driver to the JDBC 2.0 API. It also aims
>> to document any deviations from the JDBC standard that have been
>> found.
>> http://lab.applinet.nl/postgresql-jdbc/

Regards,
René Pijlman

At 02:55 PM 8/8/2001, Rene Pijlman wrote:
>Unfortunately, I'm getting many failed results with messages
>like this:
>
>********************************************************************************
>Beginning Test: testAllProceduresAreCallable
>********************************************************************************
>Using DataSource
>ERROR: SQL Exception : No suitable driver
>Calling allProceduresAreCallable on DatabaseMetaData
>ERROR: Unexpected exception null
>ERROR: Call to allProceduresAreCallable is Failed!
>ERROR: java.lang.NullPointerException

The first error tells me the JAR with the driver is not in your classpath?
The manager cannot find the PostgreSQL JDBC driver, and thus you are
getting a null connection object from DriverManager.getConnection().

The second error then is a result of doing

result = conn.allProceduresAreCallable();

with conn == null.

Peace,
Dave

On Wed, 08 Aug 2001 15:11:03 -0700, Dave Harkness wrote:
>>Unfortunately, I'm getting many failed results with messages
>>like this:
>>ERROR: SQL Exception : No suitable driver
>>ERROR: java.lang.NullPointerException
>
>The first error tells me the JAR with the driver is not in your classpath?
>The manager cannot find the PostgreSQL JDBC driver, and thus you are
>getting a null connection object from DriverManager.getConnection().

Yes, but the same test suite has hundreds of tests that *do*
succeed. I can't imagine the driver is flipping in and out of
the classpath :-) But I'll doublecheck. Thanks.

Perhaps it has something to do with connection pooling (these
tests run in J2EE EJB's)...

In case someone wants to have a look at it, the (huge) log file
is available on
http://lab.applinet.nl/postgresql-jdbc/PostgreSQL_7.1.2_CTS_1.2.1a_jdbc-tests_log.txt
(6.4 MB). And the test suite can be downloaded from
http://java.sun.com/products/jdbc/jdbctestsuite-1_2_1.html. It
requires a J2EE platform, such as Sun's reference implementation
in the J2EE SDK.

Regards,
René Pijlman

My guess is that is is a setup problem. I get a success for this test.

--Barry

********************************************************************************
Beginning Test: testAllProceduresAreCallable
********************************************************************************
SVR: Using DataSource
SVR: intTabSize: 5
SVR: intTabTypeSize: 5
SVR: createString1: create table ctstable1 (TYPE_ID int, TYPE_DESC
varchar(32), primary key(TYPE_ID))
SVR: createString: create table ctstable2 (KEY_ID int, COF_NAME
varchar(32), PRICE float, TYPE_ID int, primary key(KEY_ID), foreign
key(TYPE_ID) references ctstable1)
SVR: Created the tables ctstable1 and ctstable2
SVR: Calling allProceduresAreCallable on DatabaseMetaData
SVR: allProceduresAreCallable method called by the current user
SVR: Removed the tables ctstable1 and ctstable2
SVR: Closed the database connection
SVR: Cleanup ok;
SVR: Test running in ejb vehicle passed
Test: testAllProceduresAreCallable returned from running in ejb vehicle
********************************************************************************
End Test: testAllProceduresAreCallable...........PASSED
********************************************************************************

Rene Pijlman wrote:
> On Wed, 08 Aug 2001 09:50:11 -0700, you wrote:
>
>>Have your run the JDBC conformance tests to help generate this
>>information?
>>
>
> Yes, I have run the test (1.2.1 EE version) and looked at some
> of the results. This will take more time, since there are
> hundreds of messages and it isn't always easy to find out what's
> going on.
>
> Unfortunately, I'm getting many failed results with messages
> like this:
>
> ********************************************************************************
> Beginning Test: testAllProceduresAreCallable
> ********************************************************************************
> Using DataSource
> ERROR: SQL Exception : No suitable driver
> Calling allProceduresAreCallable on DatabaseMetaData
> ERROR: Unexpected exception null
> ERROR: Call to allProceduresAreCallable is Failed!
> ERROR: java.lang.NullPointerException
>
> And I'm quite sure this code in DatabaseMetaData isn't causing
> the problem :-)
>
> public boolean allProceduresAreCallable() throws SQLException
> {
> return true;
> }
>
>
>>There was a post a few days ago from one of the RedHat
>>engineers saying they had run it and were going to summarize the results
>>and post back to the list. (I haven't yet seen that followup).
>>
>
> Would be good to here from them.
>
>
>>I ran the tests myself over the weekend, but haven't sorted
>>through the results either.
>>
>
> Could you do a quick grep for the message quoted above? I'd
> really like to know if this is caused by a problem in my
> setup... thanks.
>
>
>>Rene Pijlman wrote:
>>
>>>I've created a web page that aims to document the level of
>>>conformance of the JDBC driver to the JDBC 2.0 API. It also aims
>>>to document any deviations from the JDBC standard that have been
>>>found.
>>>http://lab.applinet.nl/postgresql-jdbc/
>>>
>
> Regards,
> René Pijlman
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>

Rene,

First off, thank you for pulling this information together in one place.
It is really appreciated.

I was going through your list of issues and I had the following comments
to add:

Batch Updates
The current implementation is poor. As you point out the
implementation of storing up the statements and then executing them one
by one defeats the purpose of the batch methods. The intended behaviour
is to send a set of updates/inserts in one round trip to the database.
The server does support this functionality (you can send multiple
statements in one call by using a semicolon as a statement separator).
The server will then execute them all at once. The one limitation is
that the oid/row count returned by such a batch update only reflects the
oid/row count of the last statement in the batch. In reading the spec
this behaviour is in conformance if not ideal.

DatabaseMetaData

getDatabaseProductVersion - I get a pass on this test when I run.
supportsANSI92EntryLevelSQL - Since postgres now does support outer
joins, I think the answer here should be yes. I think the general
feeling is that if there is a deviation from entry level SQL92 it is a bug.

PreparedStatement

The bytea type is documented for 7.2. You can see it in the current
docs off of the developers corner links.

The driver does implement setBlob the same way as setBinaryStream. In
fact it uses setBinaryStream in it's implementation. I believe that
setBlob is functionally correct in it's assumptions that the underlying
type is oid and thus a LargeObject.

General Requirements

ODBC escape processing is minimally handled. The escapes for date
format are supported, but not the rest.

thanks,
--Barry

Rene Pijlman wrote:
> Hello,
>
> I've created a web page that aims to document the level of
> conformance of the JDBC driver to the JDBC 2.0 API. It also aims
> to document any deviations from the JDBC standard that have been
> found.
> http://lab.applinet.nl/postgresql-jdbc/
>
> No need to say its still far from complete. I just wrote the
> sections on Array and Batch Updates. I'm volunteering to finish
> and maintain this page if people find it useful. Any comments?
>
> Any feedback or additions will be greatly appreciated. If you
> know about a JDBC 2.0 feature that seems to be missing or a
> feature you know is implemented, please post a message on this
> list.
>
> The reason I created this page is that I wanted to work on JDBC
> 2.0 conformance, and except for Blob/Clob support Barry Lind
> wrote about a couple of days ago, it wasn't clear what needed to
> be done. Investigating and documenting the conformance level and
> known deficiencies seemed like a good place to start.
>
> Regards,
> René Pijlman
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>

Hello Barry,

Thanks a lot. I've incorporated your items
on http://lab.applinet.nl/postgresql-jdbc/

I'll also incorporate your postings from last week about bytea,
large objects and such.

Regards,
René Pijlman

On Wed, 08 Aug 2001 20:56:05 -0700, you wrote:
>Rene,
>
>First off, thank you for pulling this information together in one place.
> It is really appreciated.
>
>I was going through your list of issues and I had the following comments
>to add:
>
>Batch Updates
> The current implementation is poor. As you point out the
>implementation of storing up the statements and then executing them one
>by one defeats the purpose of the batch methods. The intended behaviour
>is to send a set of updates/inserts in one round trip to the database.
>The server does support this functionality (you can send multiple
>statements in one call by using a semicolon as a statement separator).
>The server will then execute them all at once. The one limitation is
>that the oid/row count returned by such a batch update only reflects the
>oid/row count of the last statement in the batch. In reading the spec
>this behaviour is in conformance if not ideal.
>
>DatabaseMetaData
>
>getDatabaseProductVersion - I get a pass on this test when I run.
>supportsANSI92EntryLevelSQL - Since postgres now does support outer
>joins, I think the answer here should be yes. I think the general
>feeling is that if there is a deviation from entry level SQL92 it is a bug.
>
>
>PreparedStatement
>
>The bytea type is documented for 7.2. You can see it in the current
>docs off of the developers corner links.
>
>The driver does implement setBlob the same way as setBinaryStream. In
>fact it uses setBinaryStream in it's implementation. I believe that
>setBlob is functionally correct in it's assumptions that the underlying
>type is oid and thus a LargeObject.
>
>General Requirements
>
>ODBC escape processing is minimally handled. The escapes for date
>format are supported, but not the rest.
>
>
>thanks,
>--Barry

I'm still here :) There have been a lot of holidays here so I'm starting
to go through the JDBC test results now. Hopefully it won't take too
long, but there are a lot of results. In any case, I've attached a quick
list of failures for reference and/or comparison. Tests that failed
multiple time are only listed once. There's no context as to which group
the tests fail in etc. so it's more for just a bird's-eye view of what's
not working.

As for your problem, Rene, I concur that it is likely a problem with
your setup as a grep on three test logs does not turn up any failures
like the ones you're getting.

Liam

--
Liam Stewart :: Red Hat Canada, Ltd. :: liams(at)redhat(dot)com

Ooops.. hit send there too quickly and didn't attach that list. Here it
is.

Liam

--
Liam Stewart :: Red Hat Canada, Ltd. :: liams(at)redhat(dot)com

Todays impossible question =:-D

I really need an ARRAY but I am stuck with JDBC 1.0.

the table looks like this

festival | filmid
------------------------------------------
1979 | 102, 103, 104
1980 | 258, 369, 489, 568

ad nauseum

How can I simulate an ARRAY of this type? If I can't - where is the
nearest bridge?

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

> Hello Barry,
>
> Thanks a lot. I've incorporated your items
> on http://lab.applinet.nl/postgresql-jdbc/
>
> I'll also incorporate your postings from last week about bytea,
> large objects and such.

Wow, this looks great.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Tony,

Offhand, I would say you need to wrap this with some code which takes an
array, and stores it comma separated, and vice versa.

This is a pretty simple solution, is there something I am missing?

Dave
-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Tony Grant
Sent: August 9, 2001 10:52 AM
To: pgsql-jdbc(at)postgresql(dot)org
Subject: [JDBC] tough one

Todays impossible question =:-D

I really need an ARRAY but I am stuck with JDBC 1.0.

the table looks like this

festival | filmid
------------------------------------------
1979 | 102, 103, 104
1980 | 258, 369, 489, 568

ad nauseum

How can I simulate an ARRAY of this type? If I can't - where is the
nearest bridge?

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

On 09 Aug 2001 11:24:20 -0400, Dave Cramer wrote:

> Offhand, I would say you need to wrap this with some code which takes an
> array, and stores it comma separated, and vice versa.
>
> This is a pretty simple solution, is there something I am missing?

That's what I'm looking at now. Trying to get some functions worked out
that read the array looking for the right values.

I am still getting up to speed on queries that are more complicated than
select x from z where y=var stuff. But I must admit I am getting there
slowly but surely.

Thanks for pointing me in the right direction.

Cheers

Tony

--
RedHat Linux on Sony Vaio C1XD/S
http://www.animaproductions.com/linux2.html
Macromedia UltraDev with PostgreSQL
http://www.animaproductions.com/ultra.html

Barry Lind writes:

> supportsANSI92EntryLevelSQL - Since postgres now does support outer
> joins, I think the answer here should be yes. I think the general
> feeling is that if there is a deviation from entry level SQL92 it is a bug.

Outer joins are not required for entry level SQL. Nevertheless,
PostgreSQL does not and probably will never comply with entry level SQL to
the letter, so the answer "false" is correct.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://funkturm.homeip.net/~peter

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Barry Lind writes:
>> supportsANSI92EntryLevelSQL - Since postgres now does support outer
>> joins, I think the answer here should be yes. I think the general
>> feeling is that if there is a deviation from entry level SQL92 it is a bug.

> Outer joins are not required for entry level SQL. Nevertheless,
> PostgreSQL does not and probably will never comply with entry level SQL to
> the letter, so the answer "false" is correct.

I think the biggest remaining shortcoming compared to "entry SQL92" is
lack of schema support. I haven't groveled through the spec in detail
to see what else is missing, however. (Once we have schemas, we can
try to run the NIST compliance tests and see what they complain of...)

regards, tom lane

I would also be willing to bet that most other databases don't support
entry level sql to the letter either (look at how Oracle treats the
empty string (i.e. '') as null). But I bet Oracle claims they support
entry level SQL in their JDBC driver since it is a requirement for J2EE.
I don't see any problem with claiming support for entry level even
though there are a few exceptions.

thanks,
--Barry

Peter Eisentraut wrote:
> Barry Lind writes:
>
>
>>supportsANSI92EntryLevelSQL - Since postgres now does support outer
>>joins, I think the answer here should be yes. I think the general
>>feeling is that if there is a deviation from entry level SQL92 it is a bug.
>>
>
> Outer joins are not required for entry level SQL. Nevertheless,
> PostgreSQL does not and probably will never comply with entry level SQL to
> the letter, so the answer "false" is correct.
>
>

Barry Lind writes:

> I don't see any problem with claiming support for entry level even
> though there are a few exceptions.

I might be able to agree if the only exceptions were that we fold
identifier case the wrong way and have different reserved words, but
certain things like schemas are rather largish exceptions.

I'm not really sure what the point of this function is anyway. We could
leave it as "false" perpetually as a means of protest against the
contortions of the SQL standard. ;-)

--
Peter Eisentraut peter_e(at)gmx(dot)net http://funkturm.homeip.net/~peter

OK, I didn't realize that schema support was part of entry level.
Given that it is, I agree that 'false' is a better response at this
time. However after schema support is added in 7.3 (hopefully), then I
would like this to be 'true'. Or are their other large features like
this missing from postgres that are part of entry level 92 that I am not
aware of?

I will submit a patch to add comments to the code reflecting this
discussion.

thanks,
--Barry

Peter Eisentraut wrote:
> Barry Lind writes:
>
>
>> I don't see any problem with claiming support for entry level even
>>though there are a few exceptions.
>>
>
> I might be able to agree if the only exceptions were that we fold
> identifier case the wrong way and have different reserved words, but
> certain things like schemas are rather largish exceptions.
>
> I'm not really sure what the point of this function is anyway. We could
> leave it as "false" perpetually as a means of protest against the
> contortions of the SQL standard. ;-)
>
>

At 16:51 2001/08/09 +0200, Tony Grant wrote:
>festival | filmid
>------------------------------------------
>1979 | 102, 103, 104
>1980 | 258, 369, 489, 568
>
>How can I simulate an ARRAY of this type? If I can't - where is the
>nearest bridge?

you could define a table with this data:

festival | filmid
------------------------------------------
1979 | 102
1979 | 103
1979 | 104
1980 | 258
1980 | 369
1980 | 489
1980 | 568

with a query like this:

select filmid from films where festival = '1980'

and use a loop like this to generate an array

Vector films = new Vector();
while ( rs.next() )
{
films.add ( rs.getString ( "filmid" ) );
}

bye
John