• Welcome, Guest. Please login.
 
August 15, 2020, 10:59:06 am

News:

Welcome to the SQLitening support forums!


need help on structuring single call to update records

Started by Marty Francom, April 22, 2010, 07:27:03 pm

Previous topic - Next topic

Marty Francom

Is it possible to write a function that would make a single call to  slBuildInsertOrUpdate  to update several records to several different Tables.   If it is possible, can someone re-write the following pseudo-code to show me how to do it:


FUNCTION SaveUpdateRecords () AS LONG


  'STEP 1 Save/Update MyTableA Record ********************************************************
 
    lFld = "afldN1, afldN2, afldN3"

    wStr = "rowid = " & gaRid                   
   
    st = afld1 & afld2 & afld3
    rc = slExe (slBuildInsertOrUpdate("TableA", st, lFld, wStr))

  'STEP 2  Save/Update MyTable2 Record *********************************************************

    lFld = "bfld1, bfld2, bfld3"

    wStr = "rowid = " & gbRid
 
    st = bfld1 & bfld2 & bfld3   
    rc = slExe (slBuildInsertOrUpdate("TableB", st, lFld, wStr))

  'STEP 3  Save/Update MyTable3 Record *********************************************************

    lFld = "cfld1, cfld2, cfld3"

    wStr = "rowid = " & gcRid

    st = cfld1 & cfld2 & cfld3   
    rc = slExe (slBuildInsertOrUpdate("TableC", st, lFld, wStr))



END FUNCTION




I actually need to update 11 records each record belongs in a different table. In local mode speed
is not an issue and doing the records in sequence is very fast.  BUT,  in REMOTE mode it becomes
noticably slow.  I am hoping to be able to speed the process by putting all the calls into a single
trip to SQLite. Hopefully this is possible.   

Fred Meier

The following needs to be cleaned up but this should get you started.  You always want to "stack" all your updates into a single slExe when in remote mode.  Always start with Begin and close with End.
FUNCTION SaveUpdateRecords () AS LONG

   Local lsUpdates as String

   lsUpdate = "Begin"

  'STEP 1 Save/Update MyTableA Record ********************************************************
 
    lFld = "afldN1, afldN2, afldN3"

    wStr = "rowid = " & gaRid                   
   
    st = afld1 & afld2 & afld3
    lsUpdates &= ";" & slBuildInsertOrUpdate("TableA", st, lFld, wStr)

  'STEP 2  Save/Update MyTable2 Record *********************************************************

    lFld = "bfld1, bfld2, bfld3"

    wStr = "rowid = " & gbRid
 
    st = bfld1 & bfld2 & bfld3   
    lsUpdates &= ";" & slBuildInsertOrUpdate("TableB", st, lFld, wStr)

  'STEP 3  Save/Update MyTable3 Record *********************************************************

    lFld = "cfld1, cfld2, cfld3"

    wStr = "rowid = " & gcRid

    st = cfld1 & cfld2 & cfld3   
    lsUpdates &= ";" & slBuildInsertOrUpdate("TableC", st, lFld, wStr)


   ' Do the updates
    slExe lsUpdates & ";End"

END FUNCTION

Marty Francom

Fred,
    Thank you.  That worked wonderfully.  I didn't realize
that commands could be stacked like that.  This  will
come in very handy.