Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 7 of 7 total |
Smart search? |
Thu, Oct 15 2015 11:57 AM | Permanent Link |
Matthew Jones | I am adding some new code to an old application I have which uses
DBISAM. I need to look up multiple matches, so the obvious code is this: WHERE ((msAppName = 'Hello') OR (msAppName = 'Mum') OR (msAppName = 'Morning')) However, the actual strings could be many, which is not an issue, except that they are from "the internet" and therefore need to be parameterised. Thus I now have to do WHERE ((msAppName = ':STR1') OR (msAppName = ':STR2') OR (msAppName = ':STR3')) etc Which made me wonder if there is a DBISAM SQL facility take a load of strings as a single item and match using that? It would save me writing some fiddly code... -- Matthew Jones |
Thu, Oct 15 2015 12:21 PM | Permanent Link |
Raul Team Elevate | On 10/15/2015 11:57 AM, Matthew Jones wrote:
> Which made me wonder if there is a DBISAM SQL facility take a load of > strings as a single item and match using that? It would save me writing > some fiddly code... Not sure about it being smart bu you can definitely do select ... where msAppName in ('Search String A','Search String B') or basically an IN operator and comma delim list Raul |
Thu, Oct 15 2015 1:18 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Matthew,
<< However, the actual strings could be many, which is not an issue, except that they are from "the internet" and therefore need to be parameterised. Thus I now have to do >> You're going to want to use IN, like Raul suggests. However, because of the "many" part, you're probably not going to be able to use parameters, so you'll have to sanitize the SQL inputs manually. Unfortunately, DBISAM doesn't have any nicely-tailored, built-in code for this, but EDB does (as does EWB). But, to get started, you'll want to look a the TExpressionParser class in the dbisamlb.pas unit. Essentially you'll want to assign the incoming strings as an expression using the SetExpression method, and then use the other methods to "walk" though each token, putting it in to your "real" SQL IN string only after it has been verified to be a valid string token and not some mal-formed nonsense with semicolons, other statements, etc. Tim Young Elevate Software www.elevatesoft.com |
Thu, Oct 15 2015 2:37 PM | Permanent Link |
Matthew Jones | Tim Young [Elevate Software] wrote:
> You're going to want to use IN, like Raul suggests. However, because > of the "many" part, you're probably not going to be able to use > parameters, so you'll have to sanitize the SQL inputs manually. Thanks both - I will check out IN, and wish I could convert the project to EDB. One day I might be able to, but right now it is live with years of data, so sticking with it makes sense. -- Matthew Jones |
Fri, Oct 16 2015 3:39 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Matthew
I don't know about recent versions of DBISAM but in my antiquated version IN bombs if there's nothing in the brackets. I'm sure you know that but just a reminder for when you're constructing the sql. Roy Lambert |
Fri, Oct 16 2015 4:49 AM | Permanent Link |
Matthew Jones | Roy Lambert wrote:
> I don't know about recent versions of DBISAM but in my antiquated > version IN bombs if there's nothing in the brackets. I'm sure you > know that but just a reminder for when you're constructing the sql. LOL! How did you know that would happen? I did realise that it would all go pop, and I tested to make sure it would too! But it works fine with :PARAMS so it is a perfect solution. Thanks both. -- Matthew Jones |
Fri, Oct 16 2015 5:39 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Matthew
>> I don't know about recent versions of DBISAM but in my antiquated >> version IN bombs if there's nothing in the brackets. I'm sure you >> know that but just a reminder for when you're constructing the sql. > >LOL! How did you know that would happen? I did realise that it would >all go pop, and I tested to make sure it would too! But it works fine >with :PARAMS so it is a perfect solution. Thanks both. I think it was something to do with being bitten by it, doing tests but not including the "nothing there" case and banging my head against the brick wall unitl I found the problem. Roy Lambert |
This web page was last updated on Wednesday, April 24, 2024 at 11:07 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |