Page 1 of 1
Query error
Posted: Thu Oct 04, 2018 4:16 pm
by cbarbal
Hi,
I have a function, which selects data from two tables. With LiveCode I get an error.
Code: Select all
function selectFieldsPisos
local tQuery, tData
put "SELECT f.finca, p.pis, p.lloguer, p.rebut FROM Pisos AS p, Finques AS f WHERE p.pisID ='" & field "ID" & "' AND p.fincaID = f.fincaID" into tQuery
put textDecode(revDataFromQuery(tab, cr, gDatabaseID, tQuery),"UTF-8") into tData
return tData
end selectFieldsPisos
If I directly execute the same query in SQliteStudio, it works
What is It wrong in the function?
Carles
Re: Query error
Posted: Thu Oct 04, 2018 6:16 pm
by bangkok
Do you have a :
in your script ?
And what is the error ?
Does tData starts with "revdberr" ?
Re: Query error
Posted: Thu Oct 04, 2018 7:03 pm
by cbarbal
Hi bangkok,
The error and the value of gDatabaseID are seen in the second attachment. The first line of Card Script is:
Re: Query error
Posted: Thu Oct 04, 2018 9:34 pm
by bangkok
Sorry, I didn't see it.
Ok the error message doesn't make sense (there is no "?" in your query).
The textDecode (that takes binary data in input) function might be"degrading" the error message, contained in tData.
You need to separate the 2 operations, to remove any interference.
put "SELECT f.finca, p.pis, p.lloguer, p.rebut FROM Pisos AS p, Finques AS f WHERE p.pisID ='" & field "ID" & "' AND p.fincaID = f.fincaID" into tQuery
put revDataFromQuery(tab, cr, gDatabaseID, tQuery) into tData
put textDecode(tData),"UTF-8") into tDataDecoded
If you look at the doc :
The revDataFromQuery function should not be used if any of the data being retrieved is binary, doing so will probably produce unexpected results.
One question : which DB do you use (MySQL, Posgresql etc) ?
Re: Query error
Posted: Fri Oct 05, 2018 10:46 am
by cbarbal
Same error.
With SQLiteStudio it works.
The database is SQLite.
This search function works?
Code: Select all
function selectPisos pSearch
local tQuery, tData
put "SELECT p.pisID, p.referencia, f.finca || ' ' || p.pis AS adreca, p.lloguer FROM Pisos AS p, Finques AS f WHERE (p.referencia LIKE '%" & pSearch & "%' OR f.finca LIKE '%" & pSearch & "%' OR lloguer LIKE '%" & pSearch & "%') AND p.fincaID = f.fincaID" into tQuery
put textDecode(revDataFromQuery(tab, cr, gDatabaseID, tQuery),"UTF-8") into tData
return tData
end selectPisos
SOLVED: Query error
Posted: Fri Oct 05, 2018 1:15 pm
by cbarbal
I have tried the same query that works with SQLite Studio, in RazorSQL and DB Browser for SQLite. In these two last ones, he has given me error near the AND. I have deleted only the previous space, the AND, and the subsequent space. I've re-typed it and it works.
I can not understand why one worked and the other two did not. Now I know you have to try it with just one.
Thank you bangkok,
Carles