Page 1 of 1

MySQL Phrasing

Posted: Mon Dec 28, 2015 5:52 pm
by A1Qicks
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)

Re: MySQL Phrasing

Posted: Mon Dec 28, 2015 6:55 pm
by dunbarx
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?

Re: MySQL Phrasing

Posted: Mon Dec 28, 2015 8:13 pm
by phaworth
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

Re: MySQL Phrasing

Posted: Mon Dec 28, 2015 10:05 pm
by A1Qicks
Perfect. Works as desired. Thanks guys!