VBScript not executing sql statment properly

Question

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!

Can you add some logging to your vb script that writes the values of your variables to a file? — BG100
– BG100, Commented Sep 3, 2010 at 5:59
Yeah, I've done that. I put a msgbox in the first sql statement and it shows that the variable sKeyPrestatiegroep has the right value, but it's just returning no rows when executed — danboh
– danboh, Commented Sep 3, 2010 at 14:31
Something's not adding up here. If oRSGBPrest is returning no rows, you should be getting an error when you assign sRequestPaymodeKey because you're trying to access fields in an empty recordset. — Tmdean
– Tmdean, Commented Sep 3, 2010 at 15:16

crackedcornjimmy · Accepted Answer · 2010-09-03 19:41:56Z

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.

DaveB · Accepted Answer · 2010-09-03 22:36:20Z

A few ideas to try:

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)?
Hard code a valid value in place of sRequestPaymodeKey. Does it work then?

Collectives™ on Stack Overflow

VBScript not executing sql statment properly

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related