Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

No exception thrown when inserting nulls in not null column

3 posts in JDBC Connect (product renamed to JConnect) Last posting was on 1997-04-17 12:08:09.0Z
Steve Prell Posted on 1997-04-15 20:10:10.0Z
Message-ID: <3353E0A2.45B5@reveregroup.com>
Date: Tue, 15 Apr 1997 15:10:10 -0500
From: Steve Prell <sprell@reveregroup.com>
Reply-To: sprell@reveregroup.com
X-Mailer: Mozilla 3.01Gold (Win95; I)
MIME-Version: 1.0
Subject: No exception thrown when inserting nulls in not null column
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.jdbcconnect
Lines: 17
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:998
Article PK: 252749

Has anybody encountered this problem?

I have a stored procedure that inserts a record in a table. If I forget
to include a non-nullable column in the insert statement, jConnect still
executes the stored procedure, and does not throw a SQLException. If I
execute this from ISQL, I do get the following error; "The column
[column_name] in table [table_name] does not allow nulls". jConnect
should be trapping this and throwing a SQLException.

Any ideas?

The only other peculiarities are that the stored procedure returns an
output parameter, and tests @@error after the insert, setting the output
parameter accordingly.


David Clegg Posted on 1997-04-16 17:54:12.0Z
Message-ID: <33551244.2551CCE6@sybase.com>
Date: Wed, 16 Apr 1997 10:54:12 -0700
From: David Clegg <davec@sybase.com>
X-Mailer: Mozilla 2.01 (X11; I; Linux 1.2.13 i586)
MIME-Version: 1.0
To: sprell@reveregroup.com
Subject: Re: No exception thrown when inserting nulls in not null column
References: <3353E0A2.45B5@reveregroup.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.jdbcconnect
Lines: 47
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:988
Article PK: 252739

It turns out that when you execute stored procedures on the SQL Server
via RPC commands (everything you do from isql is just a language
command so it isn't the same), the OUTPUT parameters will not come
back unless you send non-null INPUT parameters. There is no such
thing as an OUTPUT-ONLY parameter in SQL-Server, though some databases
do have this concept, and the JDBC standard is written that way.

If you do not setXXX a parameter or explicitly setNull() a parameter
before executing it on a stored procedure, jConnect quietly sends a
"zero" value (as appropriate for the datatype) rather than a null.
Otherwise, if the parameter is an OUTPUT parameter the SQL Server
will always send back a NULL value - even if the stored procedure
tries to set the returned parameter to something else.

This bit of weirdness is documented in the release notes. The JDBC
standard is silent on what the "correct" behavior is when you
do not set all the input parameters before executing a statement.
We used to throw an Exception for this, but... then we were failing
some of the JDBC validation tests so we figured we couldn't just
do that.

Sorry,

dave

>
> Has anybody encountered this problem?
>
> I have a stored procedure that inserts a record in a table. If I forget
> to include a non-nullable column in the insert statement, jConnect still
> executes the stored procedure, and does not throw a SQLException. If I
> execute this from ISQL, I do get the following error; "The column
> [column_name] in table [table_name] does not allow nulls". jConnect
> should be trapping this and throwing a SQLException.
>
> Any ideas?
>
> The only other peculiarities are that the stored procedure returns an
> output parameter, and tests @@error after the insert, setting the output
> parameter accordingly.


Steve Prell Posted on 1997-04-17 12:08:09.0Z
Message-ID: <335612A9.730E@reveregroup.com>
Date: Thu, 17 Apr 1997 07:08:09 -0500
From: Steve Prell <sprell@reveregroup.com>
Reply-To: sprell@reveregroup.com
X-Mailer: Mozilla 3.01Gold (Win95; I)
MIME-Version: 1.0
To: David Clegg <davec@sybase.com>
Subject: Re: No exception thrown when inserting nulls in not null column
References: <3353E0A2.45B5@reveregroup.com> <33551244.2551CCE6@sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.jdbcconnect
Lines: 16
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:977
Article PK: 252728

Thanks for the reply, but I think you have misunderstood my problem. I
am setting all the parameters correctly as far as jdbc and the stored
procedure are concerned. However, within the stored procedure I have an
insert statement like the one below...

insert into mytable (col-1,col-3) values (@parm-1,@parm-2)

However, the table has a col-2 that does not allow nulls. Normally, a
SQL error would be raised, and this is the case when executing this from
ISQL. However, when executing this from jConnect, no exception is
thrown. The insert simply just does not hapen. This makes debugging
difficult.