ADO中的MSSQL LIKE和IN语句问题 [英] MSSQL LIKE and IN statements in ADO problem

问题描述

Helo家伙，

我正在尝试使用ADO查询MSSQL数据库。

我无法使LIKE语句获取正确的结果。

有谁可以告诉我需要做些什么才能让它正常工作？

下面是代码片段：


import win32com.client < br $>
const = win32com.client.constants

#conn =建立SQL连接

cmd = win32com.client.Dispatch（" ADODB.Command）

cmd.ActiveConnection = conn


name =''@ fname''

value =" ''raj"

p = cmd.CreateParameter（name，const.adVarchar，Value = value）

cmd.Parameters.Append（p）

cmd.CommandText = \

" SELECT * FROM tb_name WHERE firstname LIKE @ fname"

cmd.CommandType = const.adCmdText

（rs，dummy）= cmd.Execute（）

而不是rs.EOF：

打印rs.Fields（''firstname''）。值

rs.MoveNext（）

rs.Close（）


我最初使用''％？％''符号，但现在不起作用，现在，

i将它更改为@fname，但这会返回一个追溯，告诉我需要

来声明@fname。


此外，我还有另一个问题，即使用 IN" SQL声明。


我提前感谢您的帮助，


谢谢，

Raja Raman

Helo guys,
I am trying to query the MSSQL DB using ADO.
I am not able to make the LIKE statement fetch the correct results.
Can anyone tell me what I need to do to get this working?
Below is the code snippet:

import win32com.client
const = win32com.client.constants

#conn = establish SQL connection
cmd = win32com.client.Dispatch("ADODB.Command")
cmd.ActiveConnection = conn

name = ''@fname''
value = "''raj"
p=cmd.CreateParameter(name, const.adVarchar, Value=value)
cmd.Parameters.Append(p)
cmd.CommandText = \
"SELECT * FROM tb_name WHERE firstname LIKE @fname"
cmd.CommandType = const.adCmdText
(rs, dummy) = cmd.Execute()
while not rs.EOF:
print rs.Fields(''firstname'').Value
rs.MoveNext()
rs.Close()

I originally was using the ''%?%'' symbol but that did not work and now,
i changed it to @fname but this returns a traceback telling that I need
to declare @fname.

Also, I have another probelm with using the "IN" SQL statement.

I appreciate your help in advance,

Thank you,
Raja Raman

推荐答案

你不能摆脱创建参数部分并直接传递

你的价值寻找？类似......


name =''raj''

cmd.CommandText = \

" SELECT * FROM tb_name WHERE firstname LIKE %%% s" ％name


这样，当调用

CommandText方法时，name变量的值会被传递。顺便说一句，这看起来太痛苦了，就像

Visual Basic而不是Python :-)开玩笑（有点）

Can''t you get rid of the Create Parameter part and directly pass along
the value you are looking for? Something like...

name = ''raj''
cmd.CommandText= \
"SELECT * FROM tb_name WHERE firstname LIKE %%%s" % name

This way the value of the name variable gets passed along when the
CommandText method is invoked. BTW, this looks too painfully much like
Visual Basic than Python :-) Just kidding (kind of)

对不起忘了用字符串替换的东西解释一下，你可以通过加倍百分号来逃避百分号。在我的例子中，我想

在值之前保留前导百分号，在这种情况下我想要

LIKE％raj出现。所以我加倍了。这就是为什么连续三个百分比b $ b％的迹象。最后一个是与name变量的

字符串替换相关联的那个。有道理吗？

Sorry forgot to explain that with the string substitution stuff you can
escape the percent sign by doubling it up. In my example I wanted to
retain the leading percent sign before the value, in this case I wanted
LIKE %raj to appear. So I doubled it up. That''s why there are three
percent signs in a row. The last one is the one associated with the
string substitution for the name variable. Make sense?

gregarican写道：

gregarican wrote:

抱歉忘了解释用字符串替换的东西你可以逃脱通过加倍百分号。在我的例子中，我想在值之前保留前导百分号，在这种情况下我想要
LIKE％raj出现。所以我加倍了。这就是为什么连续三个百分号的原因。最后一个是与name变量的
字符串替换相关联的那个。有意义吗？

Sorry forgot to explain that with the string substitution stuff you can
escape the percent sign by doubling it up. In my example I wanted to
retain the leading percent sign before the value, in this case I wanted
LIKE %raj to appear. So I doubled it up. That''s why there are three
percent signs in a row. The last one is the one associated with the
string substitution for the name variable. Make sense?

现在谷歌的sql注入漏洞并告诉我们为什么这是一个坏主意。


问候

Steve

-

Steve Holden [phone] [phone]

Holden Web LLC www.holdenweb.com

PyCon TX 2006 www.python.org/pycon/

Now Google for "sql injection vulnerability" and tell us why this is a
bad idea.

regards
Steve
--
Steve Holden [phone] [phone]
Holden Web LLC www.holdenweb.com
PyCon TX 2006 www.python.org/pycon/

这篇关于ADO中的MSSQL LIKE和IN语句问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！