Trouble with outlandish SQL statement but only with MacroSch

[mightycpa]

Hi,

I'm using a QB (Quickbooks) ODBC driver, and I'm having trouble with MacroScheduler and the SQL that I need to use for the driver.

I've tried the Macroscheduler database functions, and the VBScript functions, and a compiled VBScript program that is independent of MS.

I'm using 10.1.21

The SQL is:

SELECT fullname, jobstatus, CASE jobstatus WHEN 'Pending' THEN 'RECEIVED' WHEN 'Awarded' THEN 'IN PROCESS' WHEN 'In Progress' THEN 'SHIPPED' WHEN 'Closed' THEN 'PICKED UP' WHEN 'Not Awarded' THEN 'SUSPENDED' ELSE 'NOA' END as JobStatusDesc FROM Customer where timemodified> {ts'2009-02-25 00:00:00.000'}

This is properly formed SQL for the ODBC driver. In the compiled VBScript program, it returns the right records.

In MacSched "native", it returns fullname and jobstatus values, but blanks for the CASE field. In VBScript, it returns nothing. In VBScript, if I remove the case field, the query works just fine, and returns the correct values.

My trouble seems to all center around the CASE statement.

I'm out of ideas, and I wonder if you can shed any light.

My workaround is of course to do the translation outside of the SQL, but it would be better and cleaner to make it part of the SQL.

Can you help?

Tx,

George

Here's the VB:

Code: Select all

VBSTART

Function GetCustomerName

    Dim SQLString

    set MyDB = CreateObject("ADODB.Connection")
    MyDB.Open "Quickbooks Data"

    SQLString = "SELECT fullname, jobstatus, CASE jobstatus WHEN 'Pending' THEN 'RECEIVED' WHEN 'Awarded' THEN 'IN PROCESS' WHEN 'In Progress' THEN 'SHIPPED'  WHEN 'Closed' THEN 'PICKED UP' WHEN 'Not Awarded' THEN 'SUSPENDED' ELSE 'NOA' END as JobStatusDesc FROM Customer where timemodified> {ts'2009-02-25 00:00:00.000'} "
    set rsCustomers = MyDB.Execute(SQLString)

    If Not rsCustomers.EOF then
        GetCustomerName = rsCustomers.Fields("JobStatus")
    Else
        GetCustomerName = "Not Found"
    End if

	MsgBox GetCustomerName
	
	
    MyDB.Close

End Function

VBEND

VBEval>GetCustomerName,a

When I do it this way, I get an OLEDB error "Invalid Bookmark Value":

Code: Select all

VBSTART

Function  RunSQL
'*****************************************
Const adOpenStatic = 3
Const adLockOptimistic = 3
 
Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"
sSQL = "SELECT fullname, jobstatus, CASE jobstatus WHEN 'Pending' THEN 'RECEIVED' WHEN 'Awarded' THEN 'IN PROCESS' WHEN 'In Progress' THEN 'SHIPPED'  WHEN 'Closed' THEN 'PICKED UP' WHEN 'Not Awarded' THEN 'SUSPENDED' ELSE 'NOA' END as JobStatusDesc FROM Customer where timemodified> {ts'2009-02-25 00:00:00.000'} "
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
 
oConnection.Open sConnectString
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic
sMsg = "**********************" & Chr(10)
Do While (not oRecordset.EOF)
      sMsg = sMsg & oRecordSet.Fields("JobStatus") & Chr(10)
      oRecordset.MoveNext
Loop
sMsg = sMsg & "**********************" & Chr(10)
MsgBox sMsg
 
oRecordset.Close
Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing
'*****************************************
End Function

VBEND

VBEval>RunSQL(),a

Here's the MS:

Code: Select all

Let>str=DRIVER={QODBC Driver for QuickBooks};SERVER=QODBC;DFQ=C:\Documents and Settings\P\My Documents\exp24\uxxx inc.qbw;OLE DB Services=-2;OptimizerOn=No;
DBConnect>str,dbH

Let>SQL=SELECT fullname, CASE jobstatus WHEN 'Pending' THEN 'RECEIVED' WHEN 'Awarded' THEN 'IN PROCESS' WHEN 'InProgress' THEN 'SHIPPED'  WHEN 'Closed' THEN 'PICKED UP' WHEN 'Not Awarded' THEN 'SUSPENDED' ELSE 'NOA' END AS Q22, jobstatus FROM Customer where timemodified> {ts'2009-02-25 00:00:00.000'} 

DBQuery>dbh,SQL,CUSTOMERS,numrecs,numfields

//loop through returned recordset
Let>r=0
Repeat>r
Let>r=r+1
Let>f=0
Repeat>f
   Let>f=f+1
   Let>this_field=CUSTOMERS_%r%_%f%
   Message>this_field
   Wait>1.5
Until>f=numfields
Until>r=numrecs

DBClose>dbH

[mightycpa]

I also tried this in Access, and Access told me that I had a syntax error in the CASE clause. I clicked on the "PASS-THROUGH" option, and the SQL worked just fine.

Is there any chance that MS is somehow "translating" the SQL? Is there a "PASS-THROUGH" option in MS that will send the SQL as-is to the Quickbooks database?

I've traced, and looked at verbose logs, and nothing tells me what the SQL is at Quickbooks.

I do notice that the two programs use a different process number in the logs. Otherwise, the steps seem identical, except that the MS query fails at the end.

[mightycpa]

By changing the connection string from DNS to ODBC, I can get the SQL to work.

sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"

sConnectString = "DRIVER={QODBC Driver for QuickBooks};SERVER=QODBC;DFQ=C:\Documents and Settings\P\My Documents\exp24\uxxx inc.qbw;OLE DB Services=-2;OptimizerOn=No;"


It still doesn't work using DBQUERY, but that, I guess, is a problem for another day.