Consultor Eletrônico

Status: Unverified

FACT(s) (Environment):

Windows Intel
Progress 9.1D

SYMPTOM(s):

SQL statement on a VBScript fails via ODBC

Using a keyword in the query, example: a data field called "area"

' VbScript using sql

' *** Set Database variables ***
Dim objConn ' Database connection
Dim objRS ' Database recordset
Dim strSQL ' SQL statement

' *** Open Database connection and set recordset connection to DB ***

Set objConn = WScript.CreateObject("ADODB.Connection")
Set objRS = WScript.CreateObject("ADODB.Recordset")

objConn.ConnectionString = "DSN=Progress_Live_9_1D_Sys_DSN; User ID = 'user'; Password='password'"
objConn.Open
objRS.ActiveConnection = objConn

strSQL = "SELECT Area" & _
" FROM PUB.JobModel"
objRS.Open strSQL

objRS.Close
objConn.Close


DIAG [37000] [DataDirect-Technologies][ODBC PROGRESS driver][PROGRESS]Syntax error (7587) (-20003)

CAUSE:

As the word Area is a keyword it should be enclosed in single quote chars. VB needs to handle those symbols using CHR function.

FIX:

Due the word "area" is a keyword it should be used between double quotes, but because this is a VBScript the double quotes should be between single quotes.

EXAMPLE OF THE INCORRECT QUERY:

strSQL = "SELECT Area" & _
" FROM PUB.JobModel"


EXAMPLE OF THE CORRECT QUERY:


strSQL = "SELECT PUB.JobModel." & CHR(34) & "Area" & CHR(34) & _
" FROM PUB.JobModel"


The CHR(34) is the single quote char that makes the keyword "AREA" be recognized on its double quotes with in the query.