Hi,

I want to read some values from a table (SourceTable) and pass these
values to a procedure. The procedure inserts a new record using these
parameters.

Let's assume this simple case:

SourceTable:

MyInteger: integer;
MyDate: date;

I am using something like this:

with MyProc do
begin
  ParamByName('FMyInteger').AsInteger := SourceTableMyInteger.asInteger;
  ParamByName('FMyDate').AsDateTime:= SourceTableMyDate.asDateTime;
end;

The problem occurs, if the passed values are NULL. The procedure inserts
0 as integer and 1899-12-30 as date into the table.

I tried this:

if not VarIsNull(SourceTableMyInteger.Value) then
  ParamByName('FMyInteger').AsInteger...

thus just skipping the parameter, if the value is Null

with no success. How can I pass NULL values?

Uli

Uli


Untried but what about

if not VarIsNull(SourceTableMyInteger.Value) then ParamByName('FMyInteger').AsInteger else ParamByName('FMyInteger').Clear

Roy Lambert [Team Elevate]

Roy,

> if not VarIsNull(SourceTableMyInteger.Value) then ParamByName('FMyInteger').AsInteger else ParamByName('FMyInteger').Clear

Meanwhile I tried this - no change.

Uli

Uli


I'm quite surprised because a while ago I had trouble with a procedure where I wasn't passing any data in to a parameter and it was treated as null and I got a load of stuff being displayed I wasn't expecting. Tim and I both thought it was a good feature to keep in.

1. can you post the procedure code and 2) how are you testing the result to see if its not null?

Roy Lambert [Team Elevate]

Roy,

> I'm quite surprised because a while ago I had trouble with a procedure where I wasn't passing any data in to a parameter and it was treated as null and I got a load of stuff being displayed I wasn't expecting. Tim and I both thought it was a good feature to keep in.

Same here. I remember this discussion (I had a similar one).

> 1. can you post the procedure code and 2) how are you testing the result to see if its not null?

Here it is:

CREATE PROCEDURE "CreateOrActivateSchein" (IN "FPatientenID" INTEGER, IN
"FPrivat" BOOLEAN, IN "FStatus" VARCHAR(1) COLLATE ANSI, IN
"FWartezimmer" BOOLEAN, IN "FDatumWartezimmer" DATE, IN "FStatAufnahme"
DATE, IN "FStatEntlassung" DATE, IN "FWartezimmerStat" BOOLEAN, IN
"FGruppe" VARCHAR(10) COLLATE DEU_CI, IN "FDiagnose" CLOB COLLATE
DEU_CI, IN "FSaetzeID" INTEGER, OUT "FGeneratedID" INTEGER)
BEGIN
   DECLARE crScheine Cursor FOR Stmt;
   DECLARE FNewScheineID integer;

   PREPARE Stmt FROM
    'SELECT ScheineID from Scheine where PatientenID = ? and Status = ?
and Privat = ?';
  OPEN crScheine using FPatientenID, FStatus, FPrivat;
  IF ROWCOUNT(crScheine) > 0 THEN
    Fetch First from crScheine(ScheineID) into FNewScheineID;
  ELSE
    Close crScheine;
    PREPARE Stmt FROM
      'Insert into Scheine
(ScheineID,PatientenID,Privat,Status,Wartezimmer,DatumWartezimmer,StatAufnahme,StatEntlassung,
      Wartezimmerstationaer,Gruppe,Diagnose,SaetzeID,DatumAnlage)
values (?, ?,?,?,?,?,?,?,?,?,?,?,CURRENT_DATE)';
    Execute Stmt using
FGeneratedID,FPatientenID,FPrivat,FStatus,FWartezimmer,FDatumWartezimmer,FStatAufnahme,FStatEntlassung,
      FWartezimmerstat,FGruppe,FDiagnose,FSaetzeID;
    SET FNewScheineID = FGeneratedID;
  END IF;
  SET FGeneratedID = FNewScheineID;
END

I test the result just by looking at the entered values in EDBManager.

Regards Uli

Uli


Something I should have asked earlier - environment eg for me

PC: Dell Vostro 1700 RAM: 3Gb Disk 250Gb
OS: Vista
Delphi 2006 SP2
ElevateDB 2.03b17 non-unicode

It would be good if this was the start of all new posts for assistance (ie not in suggestions or the off-topic ngs)

I've been doing a bit of simple testing with

CREATE PROCEDURE "fred" (IN "bert" DATE)
BEGIN    
if bert is null then
execute immediate 'INSERT into tfrlog(_Table) values (''roy'')';
end if;
END

and that works as expected so my guess is the ? substitution

Can you add something into your procedure to test as well. Any old junk file will do

Roy Lambert [Team Elevate]

Uli,

<< I tried this:

if not VarIsNull(SourceTableMyInteger.Value) then
  ParamByName('FMyInteger').AsInteger...

thus just skipping the parameter, if the value is Null >>

That won't skip the field like you want.  What you need is this:

if not SourceTableMyInteger.IsNull then
  ParamByName('FMyInteger').AsInteger...

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> That won't skip the field like you want.  What you need is this:
>
> if not SourceTableMyInteger.IsNull then
>   ParamByName('FMyInteger').AsInteger...

Thank you. That works indeed. (Though I don't understand why VarIsNull
doesn't work).

One more question: if I execute the procedure several times with
different parameters: have I to clear the parameters that I want to pass
as NULL like this:

if not dm.ScheineTableSaetzeID.IsNull then
      ParamByName('FSaetzeID').AsInteger :=
dm.ScheineTableSaetzeID.AsInteger
    else
      ParamByName('FSaetzeID').Clear;

or is the .clear not necessary?

Uli

Uli

>Thank you. That works indeed. (Though I don't understand why VarIsNull
>doesn't work).

Me neither but I use .IsNull out of habit so I wouldn't encounter that.

>One more question: if I execute the procedure several times with
>different parameters: have I to clear the parameters that I want to pass
>as NULL like this:
>
>if not dm.ScheineTableSaetzeID.IsNull then
> ParamByName('FSaetzeID').AsInteger :=
>dm.ScheineTableSaetzeID.AsInteger
> else
> ParamByName('FSaetzeID').Clear;
>
>or is the .clear not necessary?

My understanding is that parameters are persistent so you'll need to set each time they change.

Roy Lambert [Team Elevate]

On 8/4/2010 12:32 AM, Uli Becker wrote:
> Tim,
>
>> That won't skip the field like you want. What you need is this:
>>
>> if not SourceTableMyInteger.IsNull then
>> ParamByName('FMyInteger').AsInteger...
>
> Thank you. That works indeed. (Though I don't understand why VarIsNull
> doesn't work).

TIntegerField.Value must return a Longint.  When it encounters a null it
returns 0.  So VarIsNull(SourceTableMyInteger.Value) = VarIsNull(0) =
false.

VarIsNull(SourceTableMyInteger.AsVariant) would possibly do what you wanted.