LiveCode Forums.

Hi all,

I'm going crazy.... I have tried without success in the foum but no solution founded.
I have into a txt field "TestoDaCercare" a part of word, and I want to find all the recurrences in a table "TBLDOMANDE_VARIE"
I tried so: but return nothing. tried in other ways with other special characters but no result.....

What the hell is the correct syntax
franco

You need "LIKE" instead of "="
Pete

Hi Pete,

phaworth wrote:You need "LIKE" instead of "="
Pete

I've tried without solve....

a doubt: I put this "answer txtTestoDaCercare" in the code to show what in the text field are.
nothing is returned into the answer message. something is wrong, very wrong....

franco

In your code sample you have used TestoDaCercare not txtTestoDaCercare.

You stated that you have a text field TestoDaCercare on the card. To show the contents of the field you need to If you have not put the contents of the field into a variable named txtTestoDaCercare then when you then LiveCode will look for the variable with that name and as it is empty, it will show the empty answer dialog.

You could put the contents of the field into the variable txtTestoDaCercare and use it in the SQL construction:or just directly from the field: Hope That Helps

Another advice : when confronted to SQL errors, always have a look at the query that is sent.
It will allow you to check what is really sent to the SQL server.

So in your case, for instance :

Hi SparkOut,
FIELD!, FIELD!, FIELD!, FIELD! I am blind!
here is what's I missing... below the correct query and, for more confusion, I wrong to type the name of the text field, that is: "txtTestoDaCercare" while "TestoDaCercare" don't exist nowhere
thanks for letting me to see it.

but there is some guide, about queries, showing the types of syntax. it's different using, into a condition, a variable or a field ect. nothing on the web?

thanks again
franco

If you're looking for SQLite syntax, theSQLite home page is www.sqlite.org.

Another way to protect yourself from errors like this is to switch Strict Compile Mode on (on the Script Editor tab of preferences). With that on, a compile error would have been flagged on your put statement because txtTestoDaCercare would have been treated as a variable and it would not have been declared in a local statement.

NOt directly related to this but I also like to use the form of revDataFromQuery that allows references to variables to be substituted into the SQL statement. For example:

put field "txtTestoDaCercare" into tSelection
put revDataFromQuery(,,<dbID>,"SELECT ...... WHERE DOMANDA LIKE '%:1%'","tSelection")

":1" will be replaced by the contents of tSelection. This also takes care of any issues where txtTestoDaCercare might contain single or double quotes - not need to escape them.

HTH

Pete

thanks Pete for tips,

ciao
franco

Hi,

may be off topic, but I have found it a good way to create my SQL statements in a reliable environment (SQLite Manager, MS-Access, HeidiSQL, ...) at first, test that they give the proper results with random arguments, and only then copy the query over into LC.

I'm using a text editor (EditPad) for this, in the middle. This helps me to replace, for instance, the variable in (WHERE MyNum = '1' AND ....). I select the 1 and insert a prefab value: (" & MyVarName & ") . Replace MyVarName with the actual variable, and you're done ;-)

Even adding (the put "[MySelection]" into MySQLStr) can be added semi-automagically, as well as proper quotes at the beginning, and proper (" & \ ) at the end of the lines ...

This saves a lot of work, and saves me from major debugging in case I'd have misspelled one of the many brackets and quotes in an a bit more challenging SQL statement!

Edit: Just see, it's a chaos of quote chars here ... So I omitted any quotes of this text, only leaving the ones used in statements - using brackets instead ...

Have fun!

Hi friends,

as always I recommend to use some little functions like these to avoid unneccessary typing and fighting with almost unreadable QUOTED stuff put q("A string")
-> "A string"
put q2("A string")
-> 'A string'



Best

Klaus

Hi all, thanks for help.
being a beginner, I would just like to know what special characters "$%&/()'= LIKE, and who knows what other, must use. for exemple:

SELECT..... WHERE column_name_of_table FROM table_name = MYVAR
or
= MYFIELD
or
= MYCONSTANT
ect.

I know that there is a syntiax, a grammar, that changes on a case by case. but I never found a guide for that. everyone made your own?

I have a new product coming out soon that will eliminate most if not all of the need to write SQL statements or scripts that execute them. See www.lcsql.com/liquidsql.html for a brief description. If you're interested, let me know off list and I'll add you to my mailing list.
Pete

phaworth wrote:I have a new product coming out soon that will eliminate most if not all of the need to write SQL statements or scripts that execute them. See http://www.lcsql.com/liquidsql.html for a brief description. If you're interested, let me know off list and I'll add you to my mailing list.
Pete

thanks, I send you.
one more question:
from 2 tables I must get all the records containing the same word into the same column. must I use "join"?

franco

Hi Franco,

please take a look here: http://www.w3schools.com/sql/



Best

Klaus

Klaus wrote:Hi Franco,

please take a look here: http://www.w3schools.com/sql/



Best

Klaus

ciao Klaus,
maybe "UNION ALL"?
with this operator works.... thanks for the suggestion (learn, learn, learn)

franco