Page 1 of 1
livecode query of sqlite
Posted: Thu Aug 09, 2012 4:20 am
by cusingerBUSCw5N
I am trying to delete records from a SQLite database that are connected to a specific card. So, I have added a field to the database with the cardname. Now I am trying to delete records connected to that card - but the query fails with the error: (no condition)
When I use "answer" before the code, it shows the correct query. But when it executes the query it fails.
What am I doing wrong?
Here is the code
put name of current card into tusename
put replaceText(tusename, "card","") into tusename2
put char 2 of tusename2 into rid
put replaceText(tusename2, rid,"") into tusename3
put char 1 of tusename3 into ride
put replaceText(tusename3,ride,"") into tusename4
answer tusename4
## Build the SQL delete statement
## Execute it
put "DELETE from tasks WHERE topic='& tusename4 & '" into tSQL
revExecuteSQL the cDatabaseID of me, tSQL
end deleteAllTasks
thanks!
Re: livecode query of sqlite
Posted: Thu Aug 09, 2012 4:32 am
by sturgis
cusingerBUSCw5N wrote:I am trying to delete records from a SQLite database that are connected to a specific card. So, I have added a field to the database with the cardname. Now I am trying to delete records connected to that card - but the query fails with the error: (no condition)
When I use "answer" before the code, it shows the correct query. But when it executes the query it fails.
What am I doing wrong?
Here is the code
put name of current card into tusename
put replaceText(tusename, "card","") into tusename2
put char 2 of tusename2 into rid
put replaceText(tusename2, rid,"") into tusename3
put char 1 of tusename3 into ride
put replaceText(tusename3,ride,"") into tusename4
answer tusename4
## Build the SQL delete statement
## Execute it
## This line builds the string, but the way its currently built, you are actually putting the following into the tSql variable
"Delete from tasks where topic='& tusername4 & '"
##Your single tick quotes are inside the only set of double quotes so your tusername variable is never evaluated.
##So, the delete will never occur since nothign will match "tusername4"
##Change this: put "DELETE from tasks WHERE topic='& tusename4 & '" into tSQL
## To this:
put "DELETE from tasks where topic ='" & tusername4 & "'" into tSql
## close quotes after the ' concatenate the contents of the varialbe tusername4, concatenate the final part of the string "'" (the single quote)
##This way the tSql variable contains the correct query string:
## Delete from tasks where topic ='theContentsOfTusername4'
## Any variable that you need to evaluate must be outside the double quotes or it is seen as just another part of the string.
revExecuteSQL the cDatabaseID of me, tSQL
end deleteAllTasks
thanks!
Re: livecode query of sqlite
Posted: Thu Aug 09, 2012 3:56 pm
by cusingerBUSCw5N
Yes - of course you are correct. I've done it so many times, that the single and double quotes are beginning to blur in my head...
anyway, I fixed that, copied your code directly, but it still doesn't work - same error - no condition (even though if you put an "answer" line before it it will tell you what is in tusername4..... Here is the code
on deleteAllTasks
put name of current card into tusename
put replaceText(tusename, "card","") into tusename2
put char 2 of tusename2 into rid
put replaceText(tusename2, rid,"") into tusename3
put char 1 of tusename3 into ride
put replaceText(tusename3,ride,"") into tusename4
## Build the SQL delete statement
## Execute it
put "DELETE from tasks where topic ='" & tusername4 & "'" into tSql
revExecuteSQL the cDatabaseID of me, tSQL
end deleteAllTasks
by the way...earlier I tried just calling a variable that was set in the card stack and it failed on that one too. I don't understand why a variable can be seen in the card stack but when you call: deleteAllTasks myvariable it suddenly can't find the variable it had on the card....that's why I changed everything to tracking the card name and replacing characters. Seems very inefficient....
Anyway - the more important issue is why the sql line isn't working.
Re: livecode query of sqlite
Posted: Thu Aug 09, 2012 4:08 pm
by sturgis
If you
answer tSql
before the revexecutesql it does show the query string correctly?
If so, do the following
After this line:
revExecuteSQL the cDatabaseID of me, tSQL
add these
Code: Select all
if the result is an integer then -- if its an integer it returns the number of table rows affected
answer information the result && "Rows were affected" -- let you know that it worked, even if 0 rows were affected
else -- if the result is not an integer there was an error
answer information "There was an error: " && the result -- show you the error.
end if
As for variables..
You have to declare their scope.
If you declare a variable as a local at the top of a card script (outside of handlers) it is local to that card. If you declare it as local inside a handler its local to that handler only. If you have a command on the card script that has access to a variable local to itself or the card script, you don't pass the variable reference to the handler. If you wish to pass a value to your hander (function, command whatever) you need to define your command with a placeholder.
Code: Select all
command mySuperCommand pVar -- pvar is a parameter passed to the command
answer pVar -- show what was passed in
## if there is also a local variable that can be seen by this handler you can hit it directly
answer theLocalVariable -- no need to pass a reference, it can already see it.
end mySuperCommand
YOu can find some good lessons here.
http://lessons.runrev.com, as well as this
http://www.runrev.com/developers/lesson ... nferences/
If you want, post your code using the variable that wasn't working as you hoped so that we can take a peek at it.
Re: livecode query of sqlite
Posted: Thu Aug 09, 2012 4:26 pm
by cusingerBUSCw5N
This is just weird. It crashes on the "put" line - not the actual SQL line. So, if I put answer tusername4 it gives me the name of the card. But the next line it fails and it never gets to the answer sql.
Just for the record...I manually added the field "tasks" into the SQLlite database - (you can see the field using SQLite Manager)...It shouldn't change anything. Even if I hardcode the topic into the statement to be: put "DELETE from tasks where topic = 'mytopic'" into tSql it fails with "no condition".
Anyway, this is the official code:
on deleteAllTasks
put name of current card into tusename
put replaceText(tusename, "card","") into tusename2
put char 2 of tusename2 into rid
put replaceText(tusename2, rid,"") into tusename3
put char 1 of tusename3 into ride
put replaceText(tusename3,ride,"") into tusename4
## Build the SQL delete statement
## Execute it
answer tusername4
put "DELETE from tasks where topic ='" & tusername4 & "'" into tSql
answer tSql
revExecuteSQL the cDatabaseID of me, tSQL
end deleteAllTasks
Re: livecode query of sqlite
Posted: Thu Aug 09, 2012 5:17 pm
by cusingerBUSCw5N
First - the earlier post had tusername not tusename - so I corrected that. But it still fails.
I tried renaming the variable tSQL to ttry and it shows up in answer....but the same code fails when the variable name is tSQL
Do you know why?
on deleteAllTasks
put name of current card into tusename
put replaceText(tusename, "card","") into tusename2
put char 2 of tusename2 into rid
put replaceText(tusename2, rid,"") into tusename3
put char 1 of tusename3 into ride
put replaceText(tusename3,ride,"") into tusename4
## Build the SQL delete statement
## Execute it
put "DELETE from tasks where topic ='" & tusename4 & "'" into ttry
answer ttry
put "DELETE from tasks where topic ='" & tusename4 & "'" into tSql
answer tSql
revExecuteSQL the cDatabaseID of me, tSQL
end deleteAllTasks
Re: livecode query of sqlite
Posted: Thu Aug 09, 2012 5:22 pm
by mwieder
What's "the cDatabaseID of me"?
Code: Select all
I manually added the field "tasks" into the SQLlite database
tasks should be a table, not a field. Is there a topic field in the tasks table?
Re: livecode query of sqlite
Posted: Thu Aug 09, 2012 9:07 pm
by sturgis
The "no condition" message makes me think that the variable holding your conditional is empty. (where field = ' '.. meaning nothing on the right of the =) but it sounds like you fixed the variable name so thats not an issue any more.
What do you mean by "but the same code fails when the variable name is tSQL" does the revexecutesql statement never fire?
Does the "answer tSql" statement display a valid sql statement?
If revExecuteSql statement does execute, then the small error check if code that I posted, does the "There was an error: .." message still pop up? Or does it show instead how many rows were affected?
As mark asked, does the cDatabaseId of me contain a valid database id? You might be careful of this and use an absolute path to the property on the off chance that you are looking at a property of "me" from the wrong place.
Another thing you might do to simplify a little bit.. You use "put name of current card into tusename. Then you remove the word "card" from tusename and go through some gymnastics to end up with just the card name.
Instead just "put the short name of the current card into tusename" This way if your card is named "fredly" then tusename ends up with fredly in it.
Lacking much time today but will look at your responses when I get a chance.
Re: livecode query of sqlite
Posted: Mon Aug 13, 2012 3:57 pm
by cusingerBUSCw5N
It works. For others, here's what I learned:
1) "no condition" means a query problem - probably with single and double quotes.
2) for inserting data, you have to include all fields.
3) Use an answer line just after you set the query so you can visually see it.
Re: livecode query of sqlite
Posted: Mon Aug 13, 2012 5:30 pm
by Klaus
Hi friends,
I highly recommend to put these little functions into your stack script for use with db queries (and more) to save a lot of typing and errors!
Code: Select all
## 1. for "normal" quotes: "
function q tString
return QUOTE & tString & QUOTE
end q
## 2. for SINGLE" quotes: '
function q2 tString
return "'" & tString & "'"
end q2
answer "The string without quotes" -> The string without quotes
answer q("The string without quotes1") -> "The string without quotes1"
answer q2("The string without quotes2") -> 'The string without quotes1'
You get the picture
Best
Kklaus
Re: livecode query of sqlite
Posted: Mon Aug 13, 2012 9:34 pm
by sturgis
After using it for a few days I highly recommend dbLib. Its simple, straightforward and seems to be easily extensible (it took 12 seconds for me to add a "DISTINCT" clause to the library). Check out the video available here.
http://www.andregarzia.com/page/dblib
It takes a LOT of the pain out of using simple databases.
Its geared for sqlite but as mentioned in the docs, it uses the underlying revdb stuff so should work with other rev supported dbs also.
Re: livecode query of sqlite
Posted: Sat Aug 18, 2012 12:53 am
by phaworth
I'd also recommend using the parameter replacement options in rev database calls. Here's how.
Change the line that creates the DELETE statement to:
put "DELETE from tasks where topic =:1" into tSql
Change the line that calls revExecuteSQL to:
revExecuteSQL the cDatabaseID of me, tSQL,"tusername4".
Notice the quotes around your variable name. The dictionary will explain fully but you can see that avoids any confusion with single/double quotes and, if it applies to your application, it also takes care of escaping single quotes within the string.
Pete