Page 1 of 2
Unexpected error with revdb_query
Posted: Wed Oct 29, 2014 9:49 am
by 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?
Re: Unexpected error with revdb_query
Posted: Wed Oct 29, 2014 10:09 am
by nicoloose
Having done further debugging, it always fails where there is a
Where
clause using ":1" ...
Re: Unexpected error with revdb_query
Posted: Wed Oct 29, 2014 5:37 pm
by MaxV
Did you try this?
Code: Select all
put revDataFromQuery(TAB, return, conID,tSQL,pSqlParamsA[1],pSqlParamsA[2] ) into tResults
Re: Unexpected error with revdb_query
Posted: Thu Oct 30, 2014 9:39 am
by 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
Re: Unexpected error with revdb_query
Posted: Thu Oct 30, 2014 12:40 pm
by 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
Re: Unexpected error with revdb_query
Posted: Thu Oct 30, 2014 1:54 pm
by 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?
Re: Unexpected error with revdb_query
Posted: Thu Oct 30, 2014 2:06 pm
by 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.
Re: Unexpected error with revdb_query
Posted: Thu Oct 30, 2014 4:28 pm
by 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
Re: Unexpected error with revdb_query
Posted: Fri Oct 31, 2014 9:03 am
by 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?
Re: Unexpected error with revdb_query
Posted: Fri Oct 31, 2014 10:01 am
by 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.
Re: Unexpected error with revdb_query
Posted: Mon Nov 03, 2014 8:36 am
by 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?
Re: Unexpected error with revdb_query
Posted: Mon Nov 03, 2014 10:14 am
by 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)
Re: Unexpected error with revdb_query
Posted: Mon Nov 03, 2014 11:27 am
by nicoloose
Ok so the output is the same in both LC6 & LC7 so nothing wrong with the Data.
Re: Unexpected error with revdb_query
Posted: Mon Nov 03, 2014 5:03 pm
by MaxV
If the output is the same, please review your code. The problem is probably in code, not in Livecode.
Re: Unexpected error with revdb_query
Posted: Mon Nov 03, 2014 5:21 pm
by bangkok
MaxV wrote: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