Welcome to the SQLitening support forums!
Started by Marty Francom, February 22, 2011, 05:03:22 pm
'rc = slExe (slBuildInsertOrUpdate(sTbl, wk, lDgF, wStr), "E") allRecs = slBuildInsertOrUpdate(sTbl, wk, lDgF, wStr) cnt2 += 1 sTxt = Parse$(DrgRec, Chr$(127), 5) & "!" Loop Wend rc = slExe (allRecs, "E")
Local fstr As String 'the fields string Local vstr As String 'the values string nuls between each field except for last Local wstr As String 'the where string'use ROWID where possible to help with insert speedslexe "BEGIN" for i = 1 to RECCOUNT'in here set variables to values needed for update fStr = "MANUF,REDREF,PRODUCT,TYPE" vStr = MyCompany & $NUL & REFNUM & $NUL & _ MyProduct & $NUL & MyType wStr = "rowid = " & rownum slExe slBuildInsertOrUpdate ("Parts", vstr, fStr, wStr) next islexe "END"
QuoteYou may use slExeBind to affect one or multiple records. The default is to affect only one record. Add the V ModChar (see below) to affect multiple records. Using the V ModChar is a highly effecent/fast way to insert or update many records. Using slExeBind this way tells SQLite3 to only prepare/compile the SQL statement one time and then affect each row re-using the prepared/compiled statement. See the example below and also look at ExampleB.Bas. The following example will insert four columns into a single record: The column types are -- 1 = Blob, 2 = Compressed Text, 3 = Compressed Encrypted Blob, 4 = Double . slExeBind "Insert into T1 values(?, ?, ?, ?)", _ slBuildBindDat("This is some Blob data") & _ slBuildBindDat("This is some compressed Text", "TC") & _ slBuildBindDat("This is a compressed and encrypted Blob", "CE") & _ slBuildBindDat("123.456", "D") The following example will insert three columns into 50 records: The column types are -- 1 = Ingeger 32 bit, 2 = Null, 3 = Integer 64 bit dim lsaRows(1 to 100) as String for llDo = 1 to ubound(lsaRows) lsaRows(llDo) = iif$(llDo mod 2, slBuildBindDat(format$(llDo), "i"), slBuildBindDat(format$(llDo + 1000^4), "I")) next slExeBind "Insert into T1 values(?, Null, ?)", join$(lsaRows(), ""), "V2" Note the use of an array and the join$ command which is much faster then standard concatenation.