Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 13 total |
Procedure and NULL parameters... |
Tue, Aug 3 2010 5:22 AM | Permanent Link |
Uli Becker | 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 |
Tue, Aug 3 2010 7:34 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Uli
Untried but what about if not VarIsNull(SourceTableMyInteger.Value) then ParamByName('FMyInteger').AsInteger else ParamByName('FMyInteger').Clear Roy Lambert [Team Elevate] |
Tue, Aug 3 2010 8:06 AM | Permanent Link |
Uli Becker | Roy,
> if not VarIsNull(SourceTableMyInteger.Value) then ParamByName('FMyInteger').AsInteger else ParamByName('FMyInteger').Clear Meanwhile I tried this - no change. Uli |
Tue, Aug 3 2010 8:52 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Tue, Aug 3 2010 9:06 AM | Permanent Link |
Uli Becker | 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 |
Tue, Aug 3 2010 10:28 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Tue, Aug 3 2010 1:00 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Aug 4 2010 3:32 AM | Permanent Link |
Uli Becker | 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 |
Wed, Aug 4 2010 5:22 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Wed, Aug 4 2010 6:29 AM | Permanent Link |
Charles Tyson | 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. |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |