Query error

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
cbarbal
Posts: 114
Joined: Fri May 08, 2015 5:04 pm

Query error

Post by cbarbal » Thu Oct 04, 2018 4:16 pm

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
Attachments
SQLiteStudio001.jpg
LiveCode001.jpg

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 937
Joined: Fri Aug 15, 2008 7:15 am

Re: Query error

Post by bangkok » Thu Oct 04, 2018 6:16 pm

Do you have a :

Code: Select all

global gDatabaseID
in your script ?

And what is the error ?

Does tData starts with "revdberr" ?

cbarbal
Posts: 114
Joined: Fri May 08, 2015 5:04 pm

Re: Query error

Post by cbarbal » Thu Oct 04, 2018 7:03 pm

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

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 937
Joined: Fri Aug 15, 2008 7:15 am

Re: Query error

Post by bangkok » Thu Oct 04, 2018 9:34 pm

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) ?

cbarbal
Posts: 114
Joined: Fri May 08, 2015 5:04 pm

Re: Query error

Post by cbarbal » Fri Oct 05, 2018 10:46 am

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

cbarbal
Posts: 114
Joined: Fri May 08, 2015 5:04 pm

SOLVED: Query error

Post by cbarbal » Fri Oct 05, 2018 1:15 pm

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

Post Reply

Return to “Databases”