MSSQL LIKE and IN statements in ADO problem

Raja Raman Sundararajan ram0812 at hotmail.com
Thu Jan 19 13:53:14 CET 2006


Ok guys! The problem seems to be much easier to be solved than first
thought. -->Shoot<--
Using the correct CreateParameter statement seems to do the trick.
For example creating the parameter as
cmd.CreateParameter(name,const.adVarChar, const.adParamInput, Size=16,
Value=value[i])
                    # Name, Type, Direction, Size, Value

works pretty good with the LIKE statement

For the IN statement I have not yet found a good way. As of now
in am looping through the values and creating various parameters
Snippet:
##
##Global initialization
##
typeMap= {
           types.IntType: const.adInteger,
           types.LongType: const.adBigInt,
           }

query = "SELECT * FROM tb_name WHERE firstname IN %(in_params)"

##
##add parameters and construct the ? values for the in statements
##Note the code below is a pseudo type thing and can contain syntax
errors
##
in_parameters = [1,2,3,4,5]
n_index = 0
in_params = ''

for i in in_parameters:
    in_params += '?,'
    name = 'name_%s' % i
    p=cmd.CreateParameter(name, typeMap[type(i)], const.adParamInput,
Size=16, Value=i) # Name, Type, Direction, Value
    cmd.Parameters.Append(p)
query = query % {'in_params': in_params[:-1]}

Any inputs to improve the IN statement logic?
My dream is to use just one create parameter for the SQL list
so that the query looks like
    query = "SELECT * FROM tb_name WHERE firstname IN ?"
Nice and easy...

:-)
Thanks in advance!
/Raja Raman




More information about the Python-list mailing list