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

Paul Hope

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Passing a null to stored procedure??

I have a number of InterBase stored procedures used among other things to
create new records.   I pass data in via parameters - how can I tell it to
leave some fields null??

I've tried not setting the parameter, setting the parameter to null and
setting the parameter.AsWhatever property to null - each of which either
doesn't work or creates an error.

Regards
Paul

 
Matt Harwar

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Passing a null to stored procedure??


Try the following code...

with spWhatever.ParamByName('ParamName') do begin
  Clear;
  Bound := true;
end;

Matt Harward
FrontRunner Technologies

Paul Hope <paulh...@allcomm.co.uk> wrote in article
<01bcee2e$9b1ce640$cc644ac3@paul>...

Quote
> I have a number of InterBase stored procedures used among other things to
> create new records.   I pass data in via parameters - how can I tell it
to
> leave some fields null??

> I've tried not setting the parameter, setting the parameter to null and
> setting the parameter.AsWhatever property to null - each of which either
> doesn't work or creates an error.

> Regards
> Paul

Paul Hop

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Passing a null to stored procedure??


Thanks Matt

Quote
> with spWhatever.ParamByName('ParamName') do begin
>   Clear;
>   Bound := true;
> end;

This has strange results with dates.  An apparently null date appears in a
grid as 30/12/99 - which is a bit worrying since that's not long from now
and could be confused with a real date.

However since DateToStr(0) gives 30/12/99 it would indicate that either the
date is null and display routine is behaving as if it is zero, or the value
really is zero.  I suspect the fault is with DateToStr  - which is probably
called by a grid to display a date - returning 30/12/99 for a null date
instead of a null string.

I shall have to look further - any ideas welcome

Regards
Paul

Lucas Franze

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Passing a null to stored procedure??


Quote
Paul Hope wrote:
> Thanks Matt

> > with spWhatever.ParamByName('ParamName') do begin
> >   Clear;
> >   Bound := true;
> > end;

> This has strange results with dates.  An apparently null date appears
> in a
> grid as 30/12/99 - which is a bit worrying since that's not long from
> now
> and could be confused with a real date.

> However since DateToStr(0) gives 30/12/99 it would indicate that
> either the
> date is null and display routine is behaving as if it is zero, or the
> value
> really is zero.  I suspect the fault is with DateToStr  - which is
> probably
> called by a grid to display a date - returning 30/12/99 for a null
> date
> instead of a null string.

> I shall have to look further - any ideas welcome

> Regards
> Paul

   Hi,

CAUTION!!!
A null date is 30/12/99 - that's true - but it is 1899 not 1999!!! (and
only if you're tryin to display the date with some
format-date-time-functions)

if I need a NULL-Value in a StoredProcedure I do use a 0 (or another
value that isn't needed)- in the stored procedure I do set it to null
afterwards.

Luc.

Paul Hop

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Passing a null to stored procedure??


Thanks Luc - I wish I had thought of that!

Think I'll switch to four figure year format!

Regards
Paul

Quote

> CAUTION!!!
> A null date is 30/12/99 - that's true - but it is 1899 not 1999!!! (and
> only if you're tryin to display the date with some
> format-date-time-functions)

> if I need a NULL-Value in a StoredProcedure I do use a 0 (or another
> value that isn't needed)- in the stored procedure I do set it to null
> afterwards.

> Luc.

Matt Harwar

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Passing a null to stored procedure??


Paul,

To get around this particular issue I have used the following solution
(although I typically use this solution with times).  In the OnGetText
event for the field itself place the following code...

if Sender.IsNull then
  Text := '<null>' //null field text
else
  Text := TimeToStr(Sender.AsDateTime);

--

Matt Harward
FrontRunner Technologies
mharw...@frontrunnertech.com
www.frontrunnertech.com

Paul Hope <paulh...@allcomm.co.uk> wrote in article
<01bcefb7$dd1ab560$LocalHost@paul>...

Quote
> Thanks Matt

> > with spWhatever.ParamByName('ParamName') do begin
> >   Clear;
> >   Bound := true;
> > end;

> This has strange results with dates.  An apparently null date appears in
a
> grid as 30/12/99 - which is a bit worrying since that's not long from now
> and could be confused with a real date.

> However since DateToStr(0) gives 30/12/99 it would indicate that either
the
> date is null and display routine is behaving as if it is zero, or the
value
> really is zero.  I suspect the fault is with DateToStr  - which is
probably
> called by a grid to display a date - returning 30/12/99 for a null date
> instead of a null string.

> I shall have to look further - any ideas welcome

> Regards
> Paul

Other Threads

1. Passing NULL parameters to a stored procedure

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

3. Passing null values to a stored procedure

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

5. Pass Null tru Procedure?

6. Passing NULLs in a DBXpress Stored Proc

7. Nulls in Stored Procedure output

8. STORED PROCEDURE WITH NULL RESULT

9. NULL values in stored procedure params

10. Null from stored procedure