MSSQL LIKE and IN statements in ADO problem
Raja Raman Sundararajan
ram0812 at hotmail.com
Thu Jan 19 07:53:14 EST 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