I've search the forum and other sources on this question and could find nothing that I could understand. I have s SQLite table where some fields have several lines. I need to construct a SQL INSERT or UPDATE statement that will add a new line of text anywhere to such a field, but it would be nice if I could control the insertion point. From my reading I get the impression that a "cr" or "/n" or something like those should be included SQL query. Am I on the right track? Thanks for any pointers or tips.
Monty May
Adding new line of text to existing multi-line SQLite field
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller
Re: Adding new line of text to existing multi-line SQLite fi
Monty
Look up revDataFromQuery
The quick and dirty method is
pull the db column you're trying to modify
Put the db column into an LC container
Update the contents of the LC container
use revDataFromQuery and a parameter-query to update the table
No promises on this being error/typo free, it's only here to give you a rough guide of how you would do what you're trying to do:
Then dberr would be something like
Look up revDataFromQuery
The quick and dirty method is
pull the db column you're trying to modify
Put the db column into an LC container
Update the contents of the LC container
use revDataFromQuery and a parameter-query to update the table
No promises on this being error/typo free, it's only here to give you a rough guide of how you would do what you're trying to do:
Code: Select all
put theRowID into args[1]
put revDataFromQuery(,,dbid,"SELECT someField FROM myTable WHERE id=:1,args) into foo
dberr foo
#<YOUR CODE TO MODIFY THE CONTENTS OF FOO GOES HERE>
# perhaps something like put CR&goop after char x of foo
#</YOUR CODE TO MODIFY THE CONTENTS OF FOO GOES HERE>
put foo into args[2]
put revDataFromQuery(,,dbid,"UPDATE myTable SET someField=:2 WHERE id=:1",args) into foo
dberr foo
Code: Select all
on dberr what
set the itemDelimiter to tab
if item 1 of what is "revdberr" then
answer "FAIL!"&&item 1 of what
exit to top
end dberr
-
- Posts: 746
- Joined: Sun Feb 04, 2007 11:01 pm
- Location: McKenna, WA
Re: Adding new line of text to existing multi-line SQLite fi
Here is some example code for updating/inserting into a SQLite Db table. I put the data into an array too which eliminates the single/double quote issues and makes the code easier to read and understand.
You'll also need this in your stack script:
Code: Select all
on touchEnd
## This is for INSERT
put tChipID into SQLArray[1]
put tPlaying into SQLArray[2]
put tName into SQLArray[3]
put tNumeric into SQLArray[4]
get executeSQL("BEGIN")
if it is not an integer then
-- error handling code
answer "An error occured BEGIN"
exit touchEnd
end if
put "INSERT into ChipData(ChipID,Playing,Name,Numeric) VALUES (:1,:2,:3,:4)" into tSQLStatement
revExecuteSQL the uDatabaseID of this stack, tSQLStatement, "SQLArray"
if the result is not an integer then
get executeSQL("ROLLBACK")
-- error handling here
answer "An error occured ROLLBACK Inserted"
exit touchEnd
end if
get executeSQL("COMMIT")
if the result is an integer then
if tCustom = "true" then show btn "EditChipData"
mobileControlSet "outPut","enabled", "false"
focus nothing
hide me
end if
## This is for UPDATE
get executeSQL("BEGIN")
if it is not an integer then
-- error handling code
answer "An error occured BEGIN"
exit touchEnd
end if
put "UPDATE ChipData SET Playing = :2, Name = :3, Numeric = :4 WHERE ChipID = :1" into tSQLUpdateStatement
revExecuteSQL the uDatabaseID of this stack, tSQLUpdateStatement, "SQLArray"
if the result is not an integer then
get executeSQL("ROLLBACK")
-- error handling here
answer "An error occured ROLLBACK Update"
exit touchEnd
end if
get executeSQL("COMMIT")
if the result is an integer then
if tCustom = "true" then show btn "EditChipData"
mobileControlSet "outPut","enabled", "false"
focus nothing
hide me
end if
end touchEnd
Code: Select all
function executeSQL psql,pparm
if pparm is empty then
revExecuteSQL the uDatabaseID of this stack, psql
else
revExecuteSQL the uDatabaseID of this stack, psql,"pparm"
end if
return the result
end executeSQL
Tom
MacBook Pro OS Mojave 10.14
MacBook Pro OS Mojave 10.14