sqloracle-databasevbscriptora-00936

VBScript not executing sql statment properly

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:

  1. 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

  2. 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

  3. 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!

Solution

  • 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.