I'm writing a few queries like the following

DELETE FROM Calls WHERE
_fkProjects = :ProjectID
AND
(_fkCompanies = :CompanyID OR :IgnoreCompany)
AND
_fkContacts = :ContactID
AND
(_fkSites = :SiteID OR :IgnoreSite)

Where :IgnoreSite and :IgnoreCompany are booleans. If I don't supply the parameters what would they default to? I'm guessing NULL.

The reason for the question is so that I (being a lazy sod) can alter the queries so that I only have to supply the parameters where required.

Roy Lambert

Roy,

<< Where :IgnoreSite and :IgnoreCompany are booleans. If I don't supply the
parameters what would they default to? I'm guessing NULL. >>

Yep, that is correct.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< Where :IgnoreSite and :IgnoreCompany are booleans. If I don't supply the
parameters what would they default to? I'm guessing NULL. >>

Yep, that is correct.

I am not sure if this should be the expected behaviour. Just spent an hour or so to debug code that returned a wrong result after comparing with
an old DBISAM-version.

 ...  
 sql.add('select privat from PraxisScheine where PatientenID = :PatientenID and status = ''A''');
 open;
 ..

Finally I found out that I had deleted the parameter accidentally. Don't you think that raising an error here would be more logical (and often
helpful)?

Regards Uli

UliBecker

>Finally I found out that I had deleted the parameter accidentally. Don't you think that raising an error here would be more logical (and often
>helpful)?

It depends on wether or not Tim can detect that no parameter has been supplied rather than a NULL has been intentionally supplied. If he can I would probably support your idea.

Roy Lambert

Uli,

<< Finally I found out that I had deleted the parameter accidentally. Don't
you think that raising an error here would be more logical (and often
helpful)? >>

I'm not sure if I can do this.  I'll have to think about it a bit more.  The
issue is that customers can do anything they want with the parameters,
including providing *too many* parameters, so I'm not sure that I can just
starting making a rule that the number of client-provided parameters must
match the number of parameters declared in the statement.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<
I'm not sure if I can do this.  I'll have to think about it a bit more.  The
issue is that customers can do anything they want with the parameters,
including providing *too many* parameters, so I'm not sure that I can just
starting making a rule that the number of client-provided parameters must
match the number of parameters declared in the statement.
>>

I understand.

The problem is, that there can be some weird results from a query when the parameter is missing.

Look at this query:

  with TempQuery do
   begin
     sql.add('select PatientenID,privat from PraxisScheine where PatientenID = :PatientenID');
     open;
     try
       if TempQuery.FieldByName('privat').asBoolean = true then
         ShowMessage('1')
       else if TempQuery.FieldByName('privat').asBoolean = false then
         ShowMessage('0');
     except
       ShowMessage('-1');
     end;
   end;

Though this query returns 0 rows, the result of the boolean field "privat" is false, not null. That's why it took me so long to find the bug. (Sure I am
aware that I should have checked the number of returned rows before returning a result.)


Regards Uli

Uli


What you've stumbled into is one reason I hate the way SQL handles NULLs. Delphi CANNOT return a NULL from a .AsBoolean and obviously elects to return false in the same way that it returns '' from .AsString or 0 from .AsInteger.

Your posting caused me to think things through a bit more.

select PatientenID,privat from PraxisScheine where PatientenID = :PatientenID

I don't know how Tim is substituting parameters but in my simple world when you don't supply one that should translate to

select PatientenID,privat from PraxisScheine where PatientenID = NULL

which should go bang so I suspect that this isn't what is happening behind the scenes.

I await Tim's answer with interest.

Roy Lambert [Team Elevate]

Roy,

Delphi CANNOT return a NULL from a .AsBoolean and obviously elects to return false in the same way that it returns '' from .AsString or 0 from
.AsInteger.

That's true and I didn't think about it.

BTW: It was not my intention to initiate another "Null-discussion".

Regards Uli

UliBecker

>BTW: It was not my intention to initiate another "Null-discussion".

To late you've already started it! However, I think the main question is why does the sql run rather than bomb out.

Roy Lambert

Roy,

<< I don't know how Tim is substituting parameters but in my simple world
when you don't supply one that should translate to

select PatientenID,privat from PraxisScheine where PatientenID = NULL

which should go bang so I suspect that this isn't what is happening behind
the scenes. >>

The simple answer is that parameters are an exception because they are a
runtime function, and the SQL compiler cannot possibly check their values.
Think of parameters like column values, and you'll understand.  For example,
this is completely valid:

select PatientenID,privat from PraxisScheine where PatientenID = 100

yet PatientenID could very well be NULL in some rows, resulting in an actual
comparison of:

select PatientenID,privat from PraxisScheine where NULL = 100

of which the result is False.

--
Tim Young
Elevate Software
www.elevatesoft.com