Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 13 total |
How do parameters default? |
Fri, Sep 19 2008 3:45 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, Sep 19 2008 6:48 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Sep 22 2008 6:06 AM | Permanent Link |
UliBecker | 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 |
Mon, Sep 22 2008 6:38 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Sep 22 2008 8:17 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Sep 22 2008 10:16 AM | Permanent Link |
UliBecker | 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 |
Mon, Sep 22 2008 11:13 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Mon, Sep 22 2008 1:54 PM | Permanent Link |
UliBecker | 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 |
Tue, Sep 23 2008 2:16 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Sep 23 2008 8:03 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
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 |