Board index » delphi » Passing null values to a stored procedure

Nick Barrett

Delphi Developer
Mon, 24 May 2004 17:19:25 GMT

Passing null values to a stored procedure

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

 
Team

Delphi Developer
Mon, 24 May 2004 22:56:10 GMT

Re:Passing null values to a stored procedure


Quote
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 Barret

Delphi Developer
Tue, 25 May 2004 04:06:53 GMT

Re:Passing null values to a stored procedure


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...

Quote

> 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

Team

Delphi Developer
Tue, 25 May 2004 05:47:31 GMT

Re:Passing null values to a stored procedure


Quote
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.

        -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

Dan Palle

Delphi Developer
Tue, 25 May 2004 05:54:19 GMT

Re:Passing null values to a stored procedure


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...

Quote

> 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.

> -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 Barret

Delphi Developer
Tue, 25 May 2004 06:24:45 GMT

Re:Passing null values to a stored procedure


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...

Quote

> 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.

> -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 Barret

Delphi Developer
Tue, 25 May 2004 06:25:52 GMT

Re:Passing null values to a stored procedure


Thanks Dan, I'll give that a go.

Cheers,
Nick Barrett

Quote
"Dan Palley" <d...@trams.com> wrote in message news:3c0fe983$1_1@dnews...
> 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 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.

> > -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

Other Threads

1. Passing NULL parameters to a stored procedure

2. how to pass null parameter to a stored procedure with TAdoStoredProc

3. Passing a null to stored procedure??

4. Stored Procedure - Passing Argument in to a Stored Procedure

5. NULL values in stored procedure params

6. Pass Null tru Procedure?

7. Passing NULL value as actual Parameter !!!

8. Passing Null Values to Query's Parameters

9. Passing Null values in a query?

10. Passing NULLs in a DBXpress Stored Proc