Unexpected error with revdb_query

[nicoloose]

Good day,

When I run my app using 6.6.2 I have no errors but when I run it using livecode 7, I get an error at the following line of code saying that there is a syntax error in my select statement:

Code: Select all

put revdb_query(sDBConnectionId, pQuery, "pSqlParamsA") into tCursorId

The pQuery looks like this :

Code: Select all

SELECT * FROM sales WHERE sales_salesDate >= :1 AND sales_salesDate <= :2 

The pSqlParamsA array looks like this:

1: "2014/01/01"
2: "2014/12/31"

Can anyone explain why this would happen in LC7 and not LC6.6?

[nicoloose]

Having done further debugging, it always fails where there is a

Where

clause using ":1" ...

[MaxV]

Did you try this?

Code: Select all

put revDataFromQuery(TAB, return, conID,tSQL,pSqlParamsA[1],pSqlParamsA[2] ) into tResults

[nicoloose]

After trying your suggestion of:

Code: Select all

put revDataFromQuery(TAB, return, conID,tSQL,pSqlParamsA[1],pSqlParamsA[2] ) into tResults

I got the following error still:

revdberr,You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':1' at line 1

[MaxV]

Ok, please try this:

Code: Select all

put "2014/01/01" into pSqlParamsA[1]
put "2014/12/31" into pSqlParamsA[2]
put "SELECT * FROM sales WHERE sales_salesDate >= ':1' AND sales_salesDate <= ':2'" into tSQL
put revDataFromQuery(TAB, return, conID,tSQL,"pSqlParamsA[1]","pSqlParamsA[2]" ) into tResults
answer tResults

[Klaus]

From the docs about "revDataFromQuery":
.....................
You can also use the name of a numerically indexed array, instead of a list of variable names. In this case, the elements of the array are substituted for the corresponding placeholders.
.....................
So it SHOULD work with that arrayname in quotes like in the initital posting!
A new bug in LC 7?

[nicoloose]

The result is blank.

I have multiple libraries which utilise the same code to return results and the puzzling thing is, there is no problem at all with earlier versions of LC. I have based my application of Jan's Tea Lib http://www.quartam.com/downloads/tea.zip example.

[MaxV]

If the result is blank, the query was correctly processed.
What database are you using?
If you use an SQLite DB, use this to check your DB content and your query result: http://livecodeshare.runrev.com/stack/7 ... administer

[nicoloose]

I am using a mySQL database. It doesn't explain why there is no issues with LC6.6 but the same code will not run using LC7?

[MaxV]

I suspect that the database has wrong data or the query is wrong.
If you get an empty result, the query is executed and the result is "no result" for that query.
So, try this:

Code: Select all

put "SELECT * FROM sales WHERE sales_salesDate >= '2014/01/01' AND sales_salesDate <= 2014/12/31'; COMMIT;" into tSQL
put revDataFromQuery(TAB, return, conID,tSQL ) into tResults
answer tResults

and this:

Code: Select all

put "SELECT * FROM sales WHERE sales_salesDate >= '2014/01/01'  ; COMMIT;" into tSQL
put revDataFromQuery(TAB, return, conID,tSQL ) into tResults
answer tResults

and this:

Code: Select all

put "SELECT * FROM sales   ; COMMIT;" into tSQL
put revDataFromQuery(TAB, return, conID,tSQL ) into tResults
answer tResults

Note that I added the ";" and the end of the query. On SQLite is not necessary. Moreover I added the COMMIT at the end, because if you started a transaction, you'll never get a result until you launch the commit command.
Try the previous codes with and without COMMIT.

[nicoloose]

I suspect that the database has wrong data or the query is wrong.

Does LC6 treat data and queries differently than LC7? I'm sorry to keep going back to this but if the data was wrong or the query was incorrect, surely when I run the code in LC6 I would also get an error?

[MaxV]

Open the same script in Livecode 6, what is the output of answer tResults in livecode 6?
(You may save your LC7 stack in LC6 version just using SAVE AS... and choosing the old file format)

[nicoloose]

Ok so the output is the same in both LC6 & LC7 so nothing wrong with the Data.

[MaxV]

If the output is the same, please review your code. The problem is probably in code, not in Livecode.

[bangkok]

If the output is the same, please review your code. The problem is probably in code, not in Livecode.

We should remain cautious.

A bug with database was found between LC Community Server 7.01 and LC Community Server 6.01

http://forums.livecode.com/viewtopic.ph ... 15#p113032