LiveCode Forums

Hi Everyone.

A newbee here ...

In my softball program I'm writing I'm having an issue appending records to a SQLiite database table when the field data contains a single quote ... like Tom's Team or Kaua'i Sluggers. All data without single quotes appends properly.

The only way I've been able to get data appended is to use single quote and comma delimiting around each field of data(See below). I tried tab delimiting and also double quote comma delimiting and 2 single quotes and comma with no luck.

Each line L in theLine in the code below that works looks like:
'1350', '1', 'Masters', 'Giants', 'AAA', 'B'
But doesn't work if a field value in line L in theLine looks like:
'1350', '1', 'Masters', 'Kaua'i Sluggers', 'AAA', 'B'

Here's the code I'm using to add the records:

repeat for each line L in tList
put "INSERT INTO standings(id, eventno, setno, " & \
"division, teamname, bracket, pool) " & \
"VALUES(null, '" & L & ")" into tSQL

-- This displays it for me for testing
answer "Line 40 in btnCalcStdgs tSQL = " & cr & tSQL with "Cancel" or "OK"
if it is "cancel" then exit mouseUp

--> EXECUTE SQL APPENDING 1 RECORD TO STANDINGS TABLE
put revdb_execute(gConID,tSQL) into tTmp
handleRevDBerror tTmp
if the result is not empty then
answer warning the result
exit mouseUp
end if
end repeat

Any help would be greatly appreciated,
Lonnie

Hi Lonnie,

Regarding extra single quotes - you can 'escape' them by preceding them with a "\"
eg: You can bypass this issue (and the surrounding single quotes altogether) by using parameters, eg. valueX replaces ":1" and takes care of all the quotes etc

Instead of repeatedly calling INSERT in a loop, you may want to build a parameter array in a loop and perform a single INSERT.
In SQLite you can insert several records with a single statement: In live code you have the benefit of statements like: The content of the element myArray[1] is substituted for the ":1" in the SQLQuery (in both places where ":1" appears), and the content of myArray[2] is substituted for ":2".

Hope that helps,
Stam

Thank you soooooooooo much Stam. This is so helpful!

In this app each time a round of games is played and scores are posted this table has to be repopulated.
My first thought was to DROP or Delete and recreate the table. Then I decided to delete the contents of the table.

Of all the methods you've demonstrated is there a "Best Practice" method I should adopt? Any speed benefits from one or the other?

Thanks again,
Lonnie

Lonnie wrote: ↑

Sun Sep 06, 2020 8:54 pm

Of all the methods you've demonstrated is there a "Best Practice" method I should adopt? Any speed benefits from one or the other?

Well i consider myself a LiveCode newbie as only been using this for a couple of months... so not sure i can advise about best practice!
But in my mind, using parameters (:1, :2, etc) is the easiest way to maintain the code as you won't need to manage surrounding single quotes or escape characters etc - it's all done for you. And more importantly you prevent SQL injection attacks.

I've used this with the array method (i.e. i build up a list of values in an array to correspond with the :n parameters) which is easy to do in LiveCode, as it's easier to maintain the code. But speed wise i doubt you'll notice any difference no matter what approach you take, SQLite is highly optimised and really very fast in any environment...

I was able to enter multiple records of data into a table called standings, by inserting 1 line of a text file using:

revExecuteSQL gConID, "INSERT INTO standings VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19)","theLine"

theLine contains a comma delimited list of 19 values of data for one record. A repeat loop, as mentioned above from Stam, does insert each record properly but does require running the revExecuteSQL in a repeat loop for each record added.

I'd like to insert multiple records, as Stam has suggested, but I can't seem to get the syntax correct. I'm trying:"

I build theList, which at this point doesn't work, in the following form:

(1,1,1,1350, ...(total of 19 values)..., 0,0,0),
(2,1,1,1350, ...(total of 19 values)..., 0,0,0),
(3,1,1,1350, ...(total of 19 values)..., 0,0,0),
...
...
...
(26,1,1,1350, .......................... 0,0,0),

Then I issue the command:

revExecuteSQL gConID, "INSERT INTO standings VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19)","theList"

Also having trouble finding code to trim the last comma from the last record in text in theList ...
tried:
put theList-1 into theList ... no luck.

I saw charsToTrimFromKey to trim leading characters. Need similar to trim last comma off last line.

Thanks in advance gang!

Lonnie

Hi Lonnie,

Also having trouble finding code to trim the last comma from the last record in text in theList ...
tried:
put theList-1 into theList ... no luck.

do not think too complicated!

What would you do if you have this list on a piece of paper? You would just remove the last character somehow, right?
And we can do exactly this in LC: Best

Klaus

Lonnie.

What Klaus said, three different ways.

You have to practice with what in xTalks we call "chunk" expressions. These are fun and astonishingly powerful. They allow you to manipulate all aspects of text, lines, items, words, etc. down to the character level, as Klaus demonstrated. For example: You get "CAT".

The User Guide and other lessons are invaluable. You need to know this.

Craig

Lonnie wrote: ↑

Fri Sep 11, 2020 7:56 am

I build theList, which at this point doesn't work, in the following form:

(1,1,1,1350, ...(total of 19 values)..., 0,0,0),
(2,1,1,1350, ...(total of 19 values)..., 0,0,0),
(3,1,1,1350, ...(total of 19 values)..., 0,0,0),
...
...
...
(26,1,1,1350, .......................... 0,0,0),

Hi Lonie,
The parameter can be an [array], rather than a comma-delimited list. Sometimes it's easier to build an array and then just put it in the execute SQL statement. The benefit is that an array may be easier to maintain and use directly in controls and can itself be stored in a custom property for ready use - and can just be dropped into a revExecuteSQL command. If you use an array it needs to be numerically keyed (i.e. theList[1] = (1,1,1,1350, ...(total of 19 values)..., 0,0,0) etc)

Lonnie wrote: ↑

Fri Sep 11, 2020 7:56 am

Also having trouble finding code to trim the last comma from the last record in text in theList ...
tried:
put theList-1 into theList ... no luck.

As Klaus mentions, you can refer to the last character of a string as 'the last char' or 'char -1' and just delete it or put empty into it etc.
If on the other hand you use the [array] for a parameter list, you don't need to worry about commas

Hi Gang,

Still fiddling with this ...

Trying to find the correct syntax to use for the SQL command "INSERT INTO" to add an array of 26 rows each containing 19 fields.
Here's what I've tried and the best I've been able to accomplish is getting 1 row or record in the table properly populated ...

set the columndelimiter to comma --return --cr --crlf (all produce same 1 record inserted)
set the rowDelimiter to crlf --cr --return --";;" (all produce same 1 record inserted)
split theList by comma -- (produces same 1 record with proper field data inserted)
revExecuteSQL gConID, "INSERT INTO standings VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19)","theList"

Interesting though is that when I use:

split theList by cr
revExecuteSQL gConID, "INSERT INTO standings VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19)","theList"

This puts all 19 fields in first row of array theList into field 1 of record 1 in the table
-- then all 19 fields of 2nd row of array into field 2 in row 1 of the table.
-- It fills all 19 fields in record 1 of the table with the first 19 rows of data in array theList

This gives me hope that with some tweaking of the syntax I can get 26 records populated the 19 uniquw fields of data.

Any suggestions? Thanks,
Lonnie