SQLExecutor Problem

[Sam Neuner]

Testing the new SQLExecutor with MYSQL Database. I have this in an text file with "Include_Text" as a Constant:

SELECT Name, M_Addr, M_City, M_St, M_Zipcode
FROM BillInfo WHERE M_City = ${City} AND M_St = ${State}

It works fine in the Studio with the "Prepare" and "Execute" in the SQLTester. However, when I run it in my program is does not work. Here is the code in my "wo":

WebGet psValue of oCitySel to sCity
WebGet psValue of oStateSel to sState

Send SQLPrepare of ghoSQLExecutor C_SQLBillInfoList
Send SQLSetParameter of ghoSQLExecutor "City" sCity
Send SQLSetParameter of ghoSQLExecutor "State" sState
Get SQLExecute of ghoSQLExecutor to aTheRows

I get no Rows! What is my problem?

[Samuel Pizarro]

you get no errors ? please check the err global variable after each of those 4 commands..

In my opinion the single quotes ' ' , surrounding each parameter is missing..

WHERE M_City = '${City}' AND M_St = '${State}'

Now, how does it work when running from SQLTester, I don't know, as it should also fail.., unless you are providing them with the value when running with the Sqltester, and missing the quotes when running from your code.

[Sam Neuner]

No Errors! It works fine as I said in the SQLExecutor in the Studio. I had already tried putting the quotes around the variables with no success.

[Marco]

Should you specify the schema/owner?
Perhaps you use a different user between your studio and your app?

[Sam Neuner]

No, my application works fine other than this view.

This code works only when both parameters are blank! (It does work in the Studio!!)

SELECT Name, M_Addr, M_City, M_St, M_Zipcode
FROM BillInfo
WHERE CASE
   WHEN ${State} <> '' AND ${City} <> '' THEN M_City = ${City} AND M_St = ${State}
   WHEN ${State} <> '' AND ${City} = '' THEN M_St = ${State}
   WHEN ${State} = '' AND ${City} <> '' THEN M_City = ${City}
   ELSE M_City > '' AND M_St > ''
END
ORDER BY CASE
   WHEN ${State} = '' THEN M_St
   ELSE Name
END, Name

[Marcia Booth]

Hi Sam,

What version of MySQL/ODBC Connector are you using? If you can, attach a simplified version of your WO that shows the problem so we can see what else your wo is doing. For example, in your code, are you setting the connection to the correct database? Also, have you tried including the query directly in your code instead of using include_text?

[starzen]

how about using the query analyzer to see what the query actually looks like

[Sam Neuner]

There is only one database in the SQL Server, ODBC 8.0.
The "CASE" statement appears to be the problem. Works in the SQLTester, not in program as a string or constant in a text file.

In WebApp.src:

Object oSQLExecutor is a cSQLExecutor
Move Self to ghoSQLExecutor

Set psConnectionId to "MrLoadID"
End_Object


In the view:

Include_Text BillInfoList.sql as C_SQLBillInfoList

BillInfoList.sql contains:

SELECT Name, M_Addr, M_City, M_St, M_Zipcode
FROM BillInfo
WHERE M_City = ${City} AND M_St = ${State}
ORDER BY M_St, M_City

This works as a string and in the text file:

Send SQLPrepare of ghoSQLExecutor C_SQLBillInfoList (or sQuery when testing as a string)
Send SQLSetParameter of ghoSQLExecutor "City" sCity
Send SQLSetParameter of ghoSQLExecutor "State" sState
Get SQLExecute of ghoSQLExecutor to aTheRows


This works in the SQLTester but not in the program as a string or from the text file:

SELECT Name, M_Addr, M_City, M_St, M_Zipcode
FROM BillInfo
WHERE CASE
WHEN ${State} <> '' THEN M_City = ${City} AND M_St = ${State}
END
ORDER BY M_St, M_City

[Sam Neuner]

Using MySQL, each time I try to "Prepare" with the following Query in the Query Tester the Studio closes!! I think there is a big problem with Case statements!

SELECT Name, M_Addr, M_City, M_St, M_Zipcode
FROM BillInfo
WHERE 1 = (CASE WHEN ${State} <> '' AND M_St = ${State} THEN 1 WHEN ${State} = '' THEN 1 End) AND
1 = (CASE WHEN ${City} <> '' AND M_City = ${City} THEN 1 WHEN ${City} = '' THEN 1 End)

[Sam Neuner]

This works:

Move (SFormat("SELECT Name, M_Addr, M_City, M_St, M_Zipcode from BillInfo WHERE 1 = (Case WHEN '%1' <> '' and M_St = '%1' THEN 1 WHEN '%1' = '' THEN 1 End) and",sState,sCity)) to sQuery
Move (Append(sQuery, SFormat(" 1 = (CASE WHEN '%2' <> '' AND M_City = '%2' THEN 1 WHEN '%2'= '' THEN 1 End) ORDER BY CASE WHEN '%1' = '' THEN M_St ELSE Name End, Name", sState, sCity))) to sQuery

Get SQLExecDirect of ghoSQLExecutor sQuery to aTheRows