MySQL Phrasing

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller

Post Reply
A1Qicks
Posts: 79
Joined: Sat Dec 26, 2015 10:47 am

MySQL Phrasing

Post by A1Qicks » Mon Dec 28, 2015 5:52 pm

Hey, I'm following the "connect to a MySQL Database" tutorial.

The problem with it saying things like "I'm not going to go into this in detail, because it is almost exactly the same as adding a record." is that as soon as you encounter a problem because you're a beginner, you're completely lost :lol:

I'm trying to work out how to just clear the database entirely for the time being just so I can reset it for testing purposes after putting a few things in. Obviously "Mary" and "Smith" are placeholders, but what's the correct phrasing on the line:

DELETE FROM tTableName WHERE NameP = 'Mary' AND MessageP = 'Smith' into tSQL

put "MessagesP" into tTableName
put "NameP, MessageP" into tFields
put the text of field "nameField" into tName
put the text of field "textEntry" into tMessage

DELETE FROM tTableName WHERE NameP = 'Mary' AND MessageP = 'Smith' into tSQL

revExecuteSQL gConnectionID, tSQL, "tName", "tMessage"



(Extra lines included for context)

dunbarx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 6227
Joined: Wed May 06, 2009 2:28 pm
Location: New York, NY

Re: MySQL Phrasing

Post by dunbarx » Mon Dec 28, 2015 6:55 pm

HI.

I do not use SQL databases, so I may be way off base.

But it seems you have your data back in a LC variable, though the line

Code: Select all

 put "MessagesP" into tTableName
places only the literal "MessagesP" into the variable tTableName.

But assuming there are multiple records in that variable, that is, multiple lines of data, you would instead:

Code: Select all

filter tTableName without "Mary"
filter tTableName without "Smith" 
You can do this in one line with a regex. I just wrote it longhand.

Craig Newman

EDIT:

I see you have (or tried to) load supporting data into a field. Note that your example lines also only load literals.
So is the general method I proposed using the "filter" command adequate for you to make your process work?

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: MySQL Phrasing

Post by phaworth » Mon Dec 28, 2015 8:13 pm

The SQL to delete all rows from a table is:

DELETE FROM <tablename>

The absence of the WHERE clause indicates all rows are to be deleted. So your LC code would be:

Code: Select all

revExecuteSQL gConnectionID,"DELETE FROM MessagesP"
if the result is not an integer then
   <insert your error handling here>
end if
If you want to delete the specific entry using bound variables (looks like that's what your code is trying to do), then:

Code: Select all

put the text of field "nameField" into tName
put the text of field "textEntry" into tMessage
revExecuteSQL gConnectionID,"DELETE FROM MessagesP WHERE NameP=:1 AND MessageP=:2","tName","tMessage"
if the result is not an integer then
   <insert your error handling here>
end if
You could also use an array to hold the variable values:

Code: Select all

put the text of field "nameField" into tArray[1]
put the text of field "textEntry" into tArray[2]
revExecuteSQL gConnectionID,"DELETE FROM MessagesP WHERE NameP=:1 AND MessageP=:2","tArray"
if the result is not an integer then
   <insert your error handling here>
end if
In either case, the ":1" and ":2" placeholders are replaced with the contents of the individual variables/array keys.

Pete

A1Qicks
Posts: 79
Joined: Sat Dec 26, 2015 10:47 am

Re: MySQL Phrasing

Post by A1Qicks » Mon Dec 28, 2015 10:05 pm

Perfect. Works as desired. Thanks guys!

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”