Adding new line of text to existing multi-line SQLite field

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: FourthWorld, heatherlaine, Klaus, kevinmiller

Post Reply
montymay
Posts: 145
Joined: Thu Jul 18, 2013 5:23 am

Adding new line of text to existing multi-line SQLite field

Post by montymay » Tue Jun 27, 2017 6:31 am

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

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 755
Joined: Fri Jun 27, 2008 9:00 pm

Re: Adding new line of text to existing multi-line SQLite fi

Post by Mikey » Tue Jun 27, 2017 2:27 pm

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:

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
Then dberr would be something like

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

quailcreek
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

Post by quailcreek » Thu Jun 29, 2017 4:24 am

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.

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
You'll also need this in your stack script:

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

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”