Passing null values to a stored procedure

Nick Barrett

unread,

Dec 6, 2001, 4:19:25 AM12/6/01

to

I'm having problems passing a null value using a TIBStoredProc to a stored
procedure. The input parameter is of type Date. Inside the stored proc the
value of the parameter is used in an INSERT query. Here is an example of my
setup.

create table "My_Table" ("Date_One" Date not null, "Date_Two" Date);

set term ^ ;
create procedure "My_Stored_Proc" (A_Date Date, Another_Date Date)
as
begin
insert into My_Table (Date_One, Date_Two) values (:A_Date,
:Another_Date);
end
^
set term ; ^

In the above example I would expect a null in the 'A_Date' parameter would
cause an error as the table requires a value to be present in the field
'Date_One'. But I did not expect an error when setting the 'Another_Date'
parameter to null as the table doesn't require a value in the 'Date_Two'
field.

To set the parameter null in the TIBStoredProc I tried using the Clear
method as well as assigning the Variant property to null.

I played around a bit and found that if I placed the line
Another_Date = null;
just before the insert query then there would be no error.

I have used a TIBQuery with parameters set to null before and they have
worked fine. Can some one please help me. I would much rather used stored
than queries so as to keep the logic on the server.

Cheers,
Nick Barrett

Craig Stuntz (TeamB)

unread,

Dec 6, 2001, 9:56:10 AM12/6/01

to

Nick Barrett wrote:
>
> In the above example I would expect a null in the 'A_Date' parameter would
> cause an error as the table requires a value to be present in the field
> 'Date_One'. But I did not expect an error when setting the 'Another_Date'
> parameter to null as the table doesn't require a value in the 'Date_Two'
> field.

What is the error (exact text, please) and what is your code?

-Craig

--
Craig Stuntz (TeamB) · Vertex Systems Corp. · Columbus, OH
We're hiring: http://www.vertexsoftware.com/careerops.htm#sd
Delphi/InterBase WebLog: http://delphi.weblogs.com

Nick Barrett

unread,

Dec 6, 2001, 3:06:53 PM12/6/01

to

Sorry Craig, the error that comes up says 'Required Param value not set.'

Continuing the example in my original post, here is some code that I use:

with dmMyDMod.spIBInsertNewDates do
begin
ParamByName('A_Date').AsDateTime := Date;
ParamByName('Another_Date').Clear;
ExecProc;
end;

The error arises upon calling the ExecProc method.

Cheers,
Nick Barrett

"Craig Stuntz (TeamB)" <cstuntz@no_spam.vertexsoftware.com> wrote in message
news:3C0F870A.9A46A25E@no_spam.vertexsoftware.com...

Craig Stuntz (TeamB)

unread,

Dec 6, 2001, 4:47:31 PM12/6/01

to

Nick Barrett wrote:
>
> Sorry Craig, the error that comes up says 'Required Param value not set.'
>

> with dmMyDMod.spIBInsertNewDates do
> begin
> ParamByName('A_Date').AsDateTime := Date;
> ParamByName('Another_Date').Clear;

After you call TParam.Clear you need to set TParam.Bound := TRUE. It's
designed this way for some odd reason.

Dan Palley

unread,

Dec 6, 2001, 4:54:19 PM12/6/01

to

Or, as Jeff always says, don't use a TIBStoredProc, use TIBSQL instead.

Dan

"Craig Stuntz (TeamB)" <cstuntz@no_spam.vertexsoftware.com> wrote in message

news:3C0FE773.A8610C68@no_spam.vertexsoftware.com...

Nick Barrett

unread,

Dec 6, 2001, 5:24:45 PM12/6/01

to

Thanks Craig. Your help is much appreciated.

Cheers,
Nick Barrett

"Craig Stuntz (TeamB)" <cstuntz@no_spam.vertexsoftware.com> wrote in message

news:3C0FE773.A8610C68@no_spam.vertexsoftware.com...

Nick Barrett

unread,

Dec 6, 2001, 5:25:52 PM12/6/01

to

Thanks Dan, I'll give that a go.

Cheers,
Nick Barrett

"Dan Palley" <d...@trams.com> wrote in message news:3c0fe983$1_1@dnews...