VBScript not executing sql statment properly

Question

Hi Gurus, good nite!

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!

Answer 1

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.

Answer 2

A few ideas to try:

1. Before Set oRSPaymodes = connADO.execute(sSql), put in a MsbBox and see what SQL is being executed. Is it valid? Will it run in a Oracle query analyzer(if there is one)?

2. Hard code a valid value in place of sRequestPaymodeKey. Does it work then?