Query problem: where condition with Text

[francof]

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:

Code: Select all

 put "SELECT DOMANDA, NUM from TBLDOMANDE_VARIE WHERE DOMANDA  = '%" & TestoDaCercare & "%' " into tSQL

but return nothing. tried in other ways with other special characters but no result.....

What the hell is the correct syntax
franco

[phaworth]

You need "LIKE" instead of "="
Pete

[francof]

Hi Pete,

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

[SparkOut]

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

Code: Select all

answer field "TestoDaCercare"

If you have not put the contents of the field into a variable named txtTestoDaCercare then when you

Code: Select all

answer txtTestoDaCercare 

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:

Code: Select all

put field "TestoDaCercare" into txtTestoDaCercare
put "SELECT DOMANDA, NUM from TBLDOMANDE_VARIE WHERE DOMANDA LIKE '%" & txtTestoDaCercare & "%' " into tSQL

or just directly from the field:

Code: Select all

put "SELECT DOMANDA, NUM from TBLDOMANDE_VARIE WHERE DOMANDA LIKE '%" & field "TestoDaCercare" & "%' " into tSQL

Hope That Helps

[bangkok]

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 :

Code: Select all

put "SELECT DOMANDA, NUM from TBLDOMANDE_VARIE WHERE DOMANDA LIKE '%" & field "TestoDaCercare" & "%' " into tSQL
answer tSQL

[francof]

Hi SparkOut,
FIELD!, FIELD!, FIELD!, FIELD! I am blind!
here is what's I missing... below the correct query

Code: Select all

put "SELECT DOMANDA, NUM from TBLDOMANDE_VARIE WHERE DOMANDA  LIKE '%" &  field "txtTestoDaCercare" & "%' " into tSQL

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

[phaworth]

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

[francof]

thanks Pete for tips,

ciao
franco

[AxWald]

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!

[Klaus]

Hi friends,

as always I recommend to use some little functions like these to avoid unneccessary typing and fighting with almost unreadable QUOTED stuff

Code: Select all

## Return a QUOTED string
function q tString
  return QUOTE & tString & QUOTE
end q

## SINGLE QUOTE a string, very handy for database stuff!
function q2 tString
  return "'" & tString & "'"
end q2

put q("A string")
-> "A string"
put q2("A string")
-> 'A string'



Best

Klaus

[francof]

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?

[phaworth]

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

[francof]

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

[Klaus]

Hi Franco,

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



Best

Klaus

[francof]

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