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

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

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

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

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

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

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