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 :

Code: Select all

global gDatabaseID
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:

Code: Select all

global gDatabaseID, gIdioma

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.
LiveCode002.jpg
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