Thread: Problem asking columns allowing NULL values
Hello,
I'm trying to ask a PostgreSQL server, using its JDBC driver, if the columns of
a table allow NULL values. To do so, I look at the value of the field
columnNullable (class ResultSetMetaData). Simply put, if I have a ResultSet
"rs" filled with rows from a query, I do the following:
ResultSetMetaData rsmeta = rs.getMetaData();
if (rsmeta.columnNullable == 1)
System.out.printl("null values allowed");
else
System.out.printl("null values NOT allowed");
The result says that *all* columns allow NULL values, although I've defined
several of them with the clause "NOT NULL" (I've checked that this columns does
really not allow NULL values, trying INSERT statements with psql).
I'm using the last stable release of the JDBC driver. Somebody else have
suffered the same problem? Or I'm doing something wrong? I'd be very grateful
if somebody could help me.
Can you send me a small test program to demonstrate the problem?
Dave
On Tue, 2003-04-08 at 13:23, . wrote:
> Hello,
> I'm trying to ask a PostgreSQL server, using its JDBC driver, if the columns of
> a table allow NULL values. To do so, I look at the value of the field
> columnNullable (class ResultSetMetaData). Simply put, if I have a ResultSet
> "rs" filled with rows from a query, I do the following:
>
>
>
> ResultSetMetaData rsmeta = rs.getMetaData();
>
> if (rsmeta.columnNullable == 1)
> System.out.printl("null values allowed");
> else
> System.out.printl("null values NOT allowed");
>
>
>
> The result says that *all* columns allow NULL values, although I've defined
> several of them with the clause "NOT NULL" (I've checked that this columns does
> really not allow NULL values, trying INSERT statements with psql).
>
> I'm using the last stable release of the JDBC driver. Somebody else have
> suffered the same problem? Or I'm doing something wrong? I'd be very grateful
> if somebody could help me.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
--
Dave Cramer <Dave@micro-automation.net>
> Can you send me a small test program to demonstrate the problem?
>
> Dave
> On Tue, 2003-04-08 at 13:23, Manel de la Rosa wrote:
> > Hello,
> > I'm trying to ask a PostgreSQL server, using its JDBC driver, if the
> > columns of a table allow NULL values. To do so, I look at the value of the
> > field columnNullable (class ResultSetMetaData). Simply put, if I have a
> > ResultSet"rs" filled with rows from a query, I do the following:
> >
> >
> >
> > ResultSetMetaData rsmeta = rs.getMetaData();
> >
> > if (rsmeta.columnNullable == 1)
> > System.out.printl("null values allowed");
> > else
> > System.out.printl("null values NOT allowed");
> >
> >
> >
> > The result says that *all* columns allow NULL values, although I've defined
> > several of them with the clause "NOT NULL" (I've checked that this columns
> > does really not allow NULL values, trying INSERT statements with psql).
> >
> > I'm using the last stable release of the JDBC driver. Somebody else have
> > suffered the same problem? Or I'm doing something wrong? I'd be very
> > grateful if somebody could help me.
> >
>
Oopss!!!
Sorry, I was doing wrong! I was looking at the value of rsmeta.columnNullable,
a *static* variable, instead of calling to
ResultSetMetaData.isNullable(column). My fault.
But now that I'm doing right, surprise: *all* columns are found as
"columnNullableUnknown" by the JDBC driver. Looking at old mails of this list,
I've found a 2001 message describing the same problem, proposing a temporal
patch and saying this:
"This method is currently unimplemented and always returns
ResultSetMetaData.columnNullable. This is obviously incorrect
when a column is defined with NOT NULL or PRIMARY KEY. And we
have to think of check constraints, views, functions etc.
This patch simply changes the return value to
ResultSetMetaData.columnNullableUnknown. This is until someone
comes up with a real implementation of course."
I'll apreciate if somebody could tell me if someone are working on this. If
not, well, I'm not a Java guru, but perhaps I could help.
Thanks.
On Thursday, April 10, 2003, at 03:13 PM, . wrote: > "This method is currently unimplemented and always returns > ResultSetMetaData.columnNullable. This is obviously incorrect > when a column is defined with NOT NULL or PRIMARY KEY. And we > have to think of check constraints, views, functions etc. > > This patch simply changes the return value to > ResultSetMetaData.columnNullableUnknown. This is until someone > comes up with a real implementation of course." > > I'll apreciate if somebody could tell me if someone are working on > this. If > not, well, I'm not a Java guru, but perhaps I could help. If you need a solution today, I recommend using the DatabaseMetaData to get information on the table, it will properly tell you about column nullability. -pete
The problem here is that there isn't enough information in what the
server returns to the client to answer this question. The client (jdbc
driver in this case) is only told what the column names are in the query
(actually not even that since if you alias a column, the server returns
the alias not the real base column name). It doesn't know what the
table is so there is no way to figure out if the underlying table/column
is nullable or not.
In 7.4 the FE/BE protocol is being enhanced so that where possible the
client will have the information necessary to look up the correct
information.
thanks,
--Barry
. wrote:
>>Can you send me a small test program to demonstrate the problem?
>>
>>Dave
>>On Tue, 2003-04-08 at 13:23, Manel de la Rosa wrote:
>>
>>>Hello,
>>>I'm trying to ask a PostgreSQL server, using its JDBC driver, if the
>>>columns of a table allow NULL values. To do so, I look at the value of the
>>>field columnNullable (class ResultSetMetaData). Simply put, if I have a
>>>ResultSet"rs" filled with rows from a query, I do the following:
>>>
>>>
>>>
>>>ResultSetMetaData rsmeta = rs.getMetaData();
>>>
>>> if (rsmeta.columnNullable == 1)
>>> System.out.printl("null values allowed");
>>> else
>>> System.out.printl("null values NOT allowed");
>>>
>>>
>>>
>>>The result says that *all* columns allow NULL values, although I've defined
>>>several of them with the clause "NOT NULL" (I've checked that this columns
>>>does really not allow NULL values, trying INSERT statements with psql).
>>>
>>>I'm using the last stable release of the JDBC driver. Somebody else have
>>>suffered the same problem? Or I'm doing something wrong? I'd be very
>>>grateful if somebody could help me.
>>>
>>
>
> Oopss!!!
> Sorry, I was doing wrong! I was looking at the value of rsmeta.columnNullable,
> a *static* variable, instead of calling to
> ResultSetMetaData.isNullable(column). My fault.
>
> But now that I'm doing right, surprise: *all* columns are found as
> "columnNullableUnknown" by the JDBC driver. Looking at old mails of this list,
> I've found a 2001 message describing the same problem, proposing a temporal
> patch and saying this:
>
> "This method is currently unimplemented and always returns
> ResultSetMetaData.columnNullable. This is obviously incorrect
> when a column is defined with NOT NULL or PRIMARY KEY. And we
> have to think of check constraints, views, functions etc.
>
> This patch simply changes the return value to
> ResultSetMetaData.columnNullableUnknown. This is until someone
> comes up with a real implementation of course."
>
> I'll apreciate if somebody could tell me if someone are working on this. If
> not, well, I'm not a Java guru, but perhaps I could help.
>
> Thanks.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>