VBScript 无法正确执行 sql 语句

发布于 2024-09-17 16:59:41 字数 1324 浏览 4 评论 0原文

这次我给您写信是因为我公司用来从 Oracle 数据库检索信息的应用程序之一的 VBScript 似乎无法正常工作。以下是事实：

部分代码执行以下操作：

<块引用>

sSql = "选择 REQ_PAYMODE" & _
  “来自 SYSADM.GBPRESTATIIEGROEP”& _
  “其中 1=1”& _
  ” AND SLEUTEL = “ & sKeyPrestatiegroep 

设置 oRSGBPrest = connADO.execute(sSql)
如果不是 oRSGBPrest.EOF 那么
  sRequestPaymodeKey = oRSGBPrest("REQ_PAYMODE")
别的
 //错误处理
结束如果

使用 Oracle 语句跟踪器 (www.aboves.com)，我可以捕获相同的语句及其相应的值：
<块引用>
从以下位置选择 REQ_PAYMODE SYSADM.GBPRESTATIIEGROEP 其中 1=1 且斯勒特尔 = 1572499
使用 Oracle语句

现在，VBScript 应该采用该值并执行另一个查询：

<块引用>

sSql = "选择 PAM_CODE" & _
          “来自 SYSADM.PAYMODES”& _
          “其中 1=1”& _
          ” AND PAM_KEY = “ & sRequestPaymodeKey

设置 oRSPaymodes = connADO.execute(sSql)

在最后一行代码中，脚本抛出一个错误：

ORA-00936: 在第 XXX 行缺少表达式 --> Set oRSPaymodes = connADO.execute(sSql) <--

这基本上意味着 (3) 中的查询不正确，这也意味着由于某种原因 sRequestPaymodeKey 为空。我无法确定这一点，因为这个失败的 sql 语句没有出现在语句跟踪器中，但这是我能找到的唯一解释。然而，最糟糕的部分是，在 SQLDeveloper 上运行查询 (2) 时（这就是值 sRequestPaymodeKey 的来源），它显示的行的值不是 null 或零。

我想不出这里可能发生的任何其他事情，也许这只是服务器的问题......不知道。

你们有什么建议吗？有什么方法可以实际调试 VBE 文件吗？

非常感谢您的帮助！

原文

I write you this time because a VBScript that one of the application my company uses to retrieve information from an Oracle database does not seem to be working properly. Here are the facts:

There's part of the code that does the following:

sSql = "SELECT REQ_PAYMODE" & _
  "  FROM SYSADM.GBPRESTATIEGROEP" & _
  " WHERE 1=1" & _
  "   AND SLEUTEL = " & sKeyPrestatiegroep 

Set oRSGBPrest = connADO.execute(sSql)
If Not oRSGBPrest.EOF Then
  sRequestPaymodeKey = oRSGBPrest("REQ_PAYMODE")
Else
 //error handling
End If

Using a Statement Tracer for Oracle (www.aboves.com) I can capture that same statement with its corresponding value:
SELECT REQ_PAYMODE FROM
SYSADM.GBPRESTATIEGROEP WHERE 1=1 AND
SLEUTEL = 1572499

Now, the VBScript is supposed to take that value and execute another query:

sSql = "SELECT PAM_CODE" & _
          "  FROM SYSADM.PAYMODES" & _
          " WHERE 1=1" & _
          "   AND PAM_KEY = " & sRequestPaymodeKey

Set oRSPaymodes = connADO.execute(sSql)

Right in this last line of code, the script throws an error that says:

ORA-00936: missing expression at line XXX --> Set oRSPaymodes = connADO.execute(sSql) <--

Which basically means that the query in (3) is not correct, which also means that for some reason sRequestPaymodeKey is empty. I cannot tell this for sure because this failing sql statement does not appear in the statement tracer, but that's the only explanation I could find. However, the worst part is that when running the query (2) on SQLDeveloper (that's where value sRequestPaymodeKey comes from) it shows a row with a value other than null or zero.

I can't think of anything else that might be happening here, maybe it's just a server thing... no idea.

Any suggestions from you guys? Any way I can actually debug a VBE file?

Your help is much appreciated!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

她比我温柔 2024-09-24 16:59:41

您需要将 sRequestPaymodeKey 转换为与 sql 的 INT 相对应的 vbLong。我假设 PAM_KEY 是 INT。记录集将返回一个字符串值。因此，您的代码将如下所示：

If IsNumeric(sRequestPaymodeKey) Then
     sSql = "SELECT PAM_CODE" & _ 
            "  FROM SYSADM.PAYMODES" & _ 
            " WHERE 1=1" & _ 
            "   AND PAM_KEY = " & CLng(sRequestPaymodeKey)

     Set oRSPaymodes = connADO.execute(sSql)
Else
     'do error handling due to bad returned data(empty string?)
End If

另外，请考虑参数化您的查询以防止 SQL 注入。

You need to cast sRequestPaymodeKey as a vbLong which corresponds to sql's INT. I'm assuming PAM_KEY is an INT. A recordset will return a string value. So, your code would look like this:

If IsNumeric(sRequestPaymodeKey) Then
     sSql = "SELECT PAM_CODE" & _ 
            "  FROM SYSADM.PAYMODES" & _ 
            " WHERE 1=1" & _ 
            "   AND PAM_KEY = " & CLng(sRequestPaymodeKey)

     Set oRSPaymodes = connADO.execute(sSql)
Else
     'do error handling due to bad returned data(empty string?)
End If

Also, consider parameterizing your queries to prevent sql injection.

回复收藏 0 原文