SQLitening Support Forum

Please login or register.

Login with username, password and session length
Advanced search  

News:

Welcome to the SQLitening support forums!

Author Topic: Easy Insert/Update using array  (Read 678 times)

cj

  • Master Geek
  • ****
  • Posts: 636
    • View Profile
Easy Insert/Update using array
« on: September 25, 2017, 06:35:31 PM »

'Easy insert and update using REPlACE INTO
'Routine adjusts sData() array to the create statement
'Rowids do not change if INTEGER PRIMARY KEY is used with REPLACE INTO

'To modify:
' 1. Drop previous table
' 2. Create a new create statement
' 3. Change GetNextHighestRow("c1") to column name of primary key
'
'Please post any comments or suggestions

#INCLUDE "sqlitening.inc"  'InsertOrReplace.bas
FUNCTION PBMAIN () AS LONG

 LOCAL colnum AS LONG
 LOCAL s,sql, sCreate,sTableName,sColumnName(),sData() AS STRING

 slOpen "test.db3","C"

 sTableName = "t1"
 slexe "drop table if exists t1"
 sCreate    = "create table if not exists "+sTableName+"(c1 INTEGER Primary Key,col2,col3,LastUpdate without rowid)"
 CreateTable sCreate,sColumnName(),sData()

 DO
  'in real application lock before getting highestRow
  s = GetNextHighestRow("c1")     'In a real-world data is supplied here
  s = INPUTBOX$("RowID",sql,s)    'in real application do not wait for user input while locked
  IF LEN(s) = 0 THEN EXIT DO
  sData(1)  = s
  sData(2) = "'two'"
  sData(3) = "'three'"
  sData(4) = "'" + TIME$ + "'"
  slexe "Insert or Replace into " +sTableName + " values("+ JOIN$(sData(),",")+");"
  'in real application unlock after insert or update
  sql = "select * from " + sTableName + " order by LastUpdate Desc"
  ? viewit(sql),,sql
 LOOP

END FUNCTION

FUNCTION viewit(sql AS STRING) AS STRING
 DIM sArray() AS STRING
 slselary sql,sArray(),"Q9"
 FUNCTION = JOIN$(sArray$(),$CR)
END FUNCTION

SUB CreateTable(sCreate AS STRING,sColNames()AS STRING,sData() AS STRING)
 LOCAL x,LastCol AS LONG
 LOCAL s,sTableName AS STRING
 x = INSTR(sCreate,"(")
 s = LEFT$(sCreate,x-1)
 x = INSTR(-1,s," ")
 sTableName = MID$(s,x+1)
 slexe sCreate
 s= slGetTableColumnNames(sTableName) 'column names
 LastCol = PARSECOUNT(s,$NUL)         'get last column number
 DIM sColNames(1 TO LastCol)          'dim array to hold column names
 FOR x=1 TO LastCol                   'column name loop
  sColNames(x)=PARSE$(s,$NUL,x)       ' column name into array
 NEXT                                 'next column n
 REDIM sData(1 TO LastCol)           'init column data array
END SUB

FUNCTION GetNextHighestRow(sColName AS STRING) AS STRING
 LOCAL s AS STRING
 s = "select COALESCE(max(#),0)+1 from T1"
 REPLACE "#" WITH scolName IN s
 slsel s,0,"E0"
 IF slGetErrorNumber = 0 THEN
  slGetRow
  FUNCTION = slf(1)
 ELSE
  ? slGetError,,LCASE$(FUNCNAME$)
 END IF
END FUNCTION

Code: [Select]
[font=courier]'Easy insert and update using REPlACE INTO
'Routine adjusts sData() array to the create statement
'Rowids do not change if INTEGER PRIMARY KEY is used with REPLACE INTO

'To modify:
' 1. Drop previous table
' 2. Create a new create statement
' 3. Change GetNextHighestRow("c1") to column name of primary key
'
'Please post any comments or suggestions

#INCLUDE "sqlitening.inc"  'InsertOrReplace.bas
FUNCTION PBMAIN () AS LONG

 LOCAL colnum AS LONG
 LOCAL s,sql, sCreate,sTableName,sColumnName(),sData() AS STRING

 slOpen "test.db3","C"

 sTableName = "t1"
 slexe "drop table if exists t1"
 sCreate    = "create table if not exists "+sTableName+"(c1 INTEGER Primary Key,col2,col3,LastUpdate without rowid)"
 CreateTable sCreate,sColumnName(),sData()

 DO
  s = GetNextHighestRow("c1")     'In a real-world data is supplied here
  s = INPUTBOX$("RowID",sql,s)
  IF LEN(s) = 0 THEN EXIT DO
  sData(1)  = s
  sData(2) = "'two'"
  sData(3) = "'three'"
  sData(4) = "'" + TIME$ + "'"
  slexe "Insert or Replace into " +sTableName + " values("+ JOIN$(sData(),",")+");"

  sql = "select * from " + sTableName + " order by LastUpdate Desc"
  ? viewit(sql),,sql
 LOOP

END FUNCTION

FUNCTION viewit(sql AS STRING) AS STRING
 DIM sArray() AS STRING
 slselary sql,sArray(),"Q9"
 FUNCTION = JOIN$(sArray$(),$CR)
END FUNCTION

SUB CreateTable(sCreate AS STRING,sColNames()AS STRING,sData() AS STRING)
 LOCAL x,LastCol AS LONG
 LOCAL s,sTableName AS STRING
 x = INSTR(sCreate,"(")
 s = LEFT$(sCreate,x-1)
 x = INSTR(-1,s," ")
 sTableName = MID$(s,x+1)
 slexe sCreate
 s= slGetTableColumnNames(sTableName) 'column names
 LastCol = PARSECOUNT(s,$NUL)         'get last column number
 DIM sColNames(1 TO LastCol)          'dim array to hold column names
 FOR x=1 TO LastCol                   'column name loop
  sColNames(x)=PARSE$(s,$NUL,x)       ' column name into array
 NEXT                                 'next column n
 REDIM sData(1 TO LastCol)           'init column data array
END SUB

FUNCTION GetNextHighestRow(sColName AS STRING) AS STRING
 LOCAL s AS STRING
 s = "select COALESCE(max(#),0)+1 from T1"
 REPLACE "#" WITH scolName IN s
 slsel s,0,"E0"
 IF slGetErrorNumber = 0 THEN
  slGetRow
  FUNCTION = slf(1)
 ELSE
  ? slGetError,,LCASE$(FUNCNAME$)
 END IF
END FUNCTION
[/font]
« Last Edit: September 25, 2017, 11:50:34 PM by cj »
Logged

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 515
    • View Profile
    • Software Development Company
Re: Easy Insert/Update using array
« Reply #1 on: September 26, 2017, 05:20:49 PM »

Hello CJ,

Thanks for sharing. Your code runs fine.

What advantage does "Insert or Replace" have over "Replace"?

I was thinking the smartest thing to do is to find a way of combining the normal Insert and Update in a Function, Method, Subroutine. I am afraid that might involve some search routines that might become problematic in a Client/Server Setup.

Regards,
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

cj

  • Master Geek
  • ****
  • Posts: 636
    • View Profile
Re: Easy Insert/Update using array
« Reply #2 on: September 26, 2017, 05:43:33 PM »

Replace is the short name for Insert or Replace.  https://sqlite.org/lang_replace.html

I just noticed without rowid is in the example.  I could not get that to work.
sCreate    = "create table if not exists "+sTableName+"(c1 INTEGER Primary Key,col2,col3,LastUpdate without rowid)"
« Last Edit: September 26, 2017, 05:49:28 PM by cj »
Logged

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 515
    • View Profile
    • Software Development Company
Re: Easy Insert/Update using array
« Reply #3 on: September 26, 2017, 05:48:55 PM »

Quote
Replace is the short name for Insert or Replace.  https://sqlite.org/lang_replace.html

I know. I mean in functionality.
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

cj

  • Master Geek
  • ****
  • Posts: 636
    • View Profile
Re: Easy Insert/Update using array
« Reply #4 on: September 26, 2017, 06:23:25 PM »

https://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace

I may have to go back to the Upsert command that I was working on.
If duplicate keys are found it sounds like Insert or Replace will modify multiple records!
It also appears that if triggers are used other records might also be affected.
It is strange that when I see the command that these concerns were not mentioned.
I am going back to SQL site and see if this is all documented.

In other words, don't use my code because it may only work in certain cases.
It sure seemed to reduce a lot of coding, but it is also mentioned that it is much
faster to update a single column then multiple columns of the same record.
I can't say that I have ever noticed anything slow, but my tests have only been on small
databases and updating single records.

My code also always updates all columns because the array contains all the columns.
I'm also going to go back to looking at the original examples in SQLitening to see if all columns are always updated.



« Last Edit: September 26, 2017, 06:26:11 PM by cj »
Logged

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 515
    • View Profile
    • Software Development Company
Re: Easy Insert/Update using array
« Reply #5 on: September 27, 2017, 01:13:01 PM »

Quote
I may have to go back to the Upsert command that I was working on.

Great idea.

When I find a little time I write a routine where I would search for a record using the unique or primary column, if exist then Update else Insert. This would be safer I guess.
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

cj

  • Master Geek
  • ****
  • Posts: 636
    • View Profile
Re: Easy Insert/Update using array
« Reply #6 on: September 27, 2017, 01:18:48 PM »

Definitely.  Lots of things can happen if delete and insert is used by replace into.

You asked what was the advantage of the code.

Answer:
The same code can be used in all programs.
No insert or update statements are needed.
The code automatically adjusts to the create statement and handles the columns.
I am going back to regular insert/update code because of the possible side-effects of INSERT OR REPLACE.

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 515
    • View Profile
    • Software Development Company
Re: Easy Insert/Update using array
« Reply #7 on: September 27, 2017, 04:05:29 PM »

Code: [Select]
I am going back to regular insert/update code because of the possible side-effects of INSERT OR REPLACE.

Sure. Me too. Its cool to play safe.
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 515
    • View Profile
    • Software Development Company
Re: Easy Insert/Update using array
« Reply #8 on: September 27, 2017, 05:25:04 PM »

Hello CJ,

This seems to be doing what I want. Do you see any caveat here? This is just the strip down amongst other things.

Code: [Select]
slSEL "SELECT RowID as RecordNo, * FROM tblName WHERE Company = '" + m_sCompany + "' "   
If Len(sRecordNo) <> 0 Then
   sSQL = slBuildInsertOrUpdate("tblName", m_sCompany, "Company", "rowid=" & sRecordNo)
      slExe "Begin"
         slExe sSQL
      slExe "End" 
         Else
    sSQL = slBuildInsertOrUpdate("tblName", m_sCompany)
      slExe "Begin"
          slExe sSQL
      slExe "End"
End if   

Regards,
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

cj

  • Master Geek
  • ****
  • Posts: 636
    • View Profile
Upsert using any primary key
« Reply #9 on: September 28, 2017, 05:36:13 PM »

It appears to me that Fred wrote a routine to  format for an Upsert when he wrote slBuildInsertOrUpdate!
No duplicate values are allowed for a primary key so the correct 1-record should be found when updating.

The extra work here is because primary key can be in any column and rowid may not exist.
The two causes might be using integer primary key or if without rowid is used.

I could only get to work on any column by wrapping each column of the incoming data in sValues (which doesn't seem correct)
and searching on the primary column without wrapping the search field because the data is already wrapped.
I think I'm doing this backwards, but have struggled getting it to work any other way.

1. Is it normal to wrap each column before passing to slBuildInsertOrUpdate?
sValues = WRAP$("pear" + FORMAT$(recnum),$SQ,$SQ) + $NUL + _   'fruit
             WRAP$(FORMAT$(recnum),$SQ,$SQ)                    + $NUL + _   'random num
             WRAP$(FORMAT$(TIMER),$SQ,$SQ)                                           'timer

Upsert sTable,sValues,sColumns,sWhere,sPrimaryKey



If i didn't wrap each column (parsed out of sValues$) then a search for '1' would not return 1 so record not found would
cause an insert error because the primary column must be unique.
I think everything may be backward, but it works.  If you see an error, please let me know.

Pretty sure which column is the primary key and getting the correct data could be handled better.
sWhere = sPrimaryKey + "=" & WRAP$(slf(colnum),$SQ,$SQ)   'could use slfnx, but have the colnum.

FUNCTION Upsert(sTable AS STRING,sValues AS STRING,sColumns AS STRING, sWhere AS STRING,sPrimaryKey AS STRING) AS LONG

 LOCAL sSql AS STRING, sSearch AS STRING, s AS STRING, i,colnum AS LONG

 FOR i = 1 TO PARSECOUNT(sColumns)  'need primary column number so we use correct values
  s = PARSE$(LCASE$(sColumns),i)   
  IF s = LCASE$(sPrimaryKey) THEN   
   colnum= i                       
   EXIT FOR             
  END IF
 NEXT
 IF colnum = 0 THEN ? "Could not find primary key",,FUNCNAME$:EXIT FUNCTION

 sSearch  = PARSE$(sValues,$NUL,colnum)    'use primary key data parsed from sValues
 s = "SELECT * FROM " + sTable + " WHERE "+ sPrimaryKey + "="+sSearch
 slSEL s

 IF slGetRow THEN
   sWhere = sPrimaryKey + "=" & WRAP$(slf(colnum),$SQ,$SQ)
   slCloseSet
   sSQL = slBuildInsertOrUpdate(sTable, sValues, sColumns,sWhere)
   slExe "Begin Immediate"
   slExe sSQL
   slExe "End"
 ELSE
   sSQL = slBuildInsertOrUpdate(sTable, sValues)
   slExe "Begin Immediate"
   slExe sSQL
   slExe "End"
 END IF
END FUNCTION
Code: [Select]
$Title = "SQLitening Upsert using primary key in any column"
#INCLUDE "sqlitening.inc"
GLOBAL gs_Show_Executed_Statements AS STRING

'Upsert2.bas
'1 sColumn$     =  fruit,cool,timer
'2 sPrimaryKey$ =  cool

FUNCTION PBMAIN () AS LONG
 slSetProcessMods "E2"
 LOCAL sTable,sValues,sColumns,sWhere,sPrimaryKey AS STRING
 LOCAL WriteLoop,recnum AS LONG

 slOpen  "test.db3","C"
 sTable  = "Food"

 slexe "drop table if exists " + sTable
 '----------------------------------------------------------------------------------------------------
 slexe "create table if not exists " + sTable +"(fruit,cool primary key,timer)without rowid"
1 sColumns = "fruit,Cool,timer"  'column names required
2 sPrimaryKey = "COOL"           'primary key  required
 '----------------------------------------------------------------------------------------------------
 FOR WriteLoop = 1 TO 2 'do this twice to get duplicates
  FOR recnum = 1 TO 11  'write 11 records
   sValues = WRAP$("pear" + FORMAT$(recnum),$SQ,$SQ) + $NUL + _   'fruit
             WRAP$(FORMAT$(recnum),$SQ,$SQ)          + $NUL + _   'random num
             WRAP$(FORMAT$(TIMER),$SQ,$SQ)                        'timer
   Upsert sTable,sValues,sColumns,sWhere,sPrimaryKey
  NEXT
 NEXT
 DIM sArray() AS STRING
 'rowid may not exist with integer primary key or without rowid clause
 slSelAry "select * from " + sTable + " order by " + sPrimaryKey,sArray(),"Q9"
 ? "(Primary key " + sPrimaryKey + ")" + $CR + $CR + _
                    JOIN$(sArray(),$CR) + $CR + $CR +  gs_Show_Executed_Statements,,_
                    $Title
END FUNCTION

FUNCTION Upsert(sTable AS STRING,sValues AS STRING,sColumns AS STRING, sWhere AS STRING,sPrimaryKey AS STRING) AS LONG
 'matches call to slBuildInsertOrUpdate, but needs additional parameter PrimaryKey
 LOCAL sSql AS STRING, sSearch AS STRING, s AS STRING, i,colnum AS LONG
 FOR i = 1 TO PARSECOUNT(sColumns)  'find what column is primary
  s = PARSE$(LCASE$(sColumns),i)    'so case matches
  IF s = LCASE$(sPrimaryKey) THEN   'found primary key
   colnum= i                        'set colnum
   EXIT FOR                         'success exit
  END IF
 NEXT
 IF colnum = 0 THEN ? "Could not find primary key",,FUNCNAME$:EXIT FUNCTION
 sSearch  = PARSE$(sValues,$NUL,colnum) 'svalues are NUL delimited
 s = "SELECT * FROM " + sTable + " WHERE "+ sPrimaryKey + "="+sSearch
 slSEL s
 IF slGetRow THEN
   sWhere = sPrimaryKey + "=" & WRAP$(slf(colnum),$SQ,$SQ)
   slCloseSet
   sSQL = slBuildInsertOrUpdate(sTable, sValues, sColumns,sWhere)
   slExe "Begin Immediate"
   slExe sSQL
   slExe "End"
 ELSE
   sSQL = slBuildInsertOrUpdate(sTable, sValues)
   slExe "Begin Immediate"
   slExe sSQL
   slExe "End"
 END IF
 gs_Show_Executed_Statements+=sSQL + $CR
END FUNCTION
« Last Edit: September 28, 2017, 06:44:53 PM by cj »
Logged

cj

  • Master Geek
  • ****
  • Posts: 636
    • View Profile
Re: Easy Insert/Update using array
« Reply #10 on: September 28, 2017, 09:27:53 PM »

In the docs nothing is wrapped with $SQ (single quotes) at the top of the page.

slBuildInsertOrUpdate("TableA", "ABC" & $NUL & "123")
slBuildInsertOrUpdate("TableA", "ABC", "ColA")
slBuildInsertOrUpdate("TableA", "ABC" & $NUL & "123", "ColA,ColB", "Rowid=1")
slBuildInsertOrUpdate("TableA", "ABC" & $NUL & "123", "ColA,ColB", "*")
slBuildInsertOrUpdate("TableA", "ABC" & $NUL & "123", "ColA,ColB") and slBuildInsertOrUpdate("TableA", "ABC" & $NUL & "123", "ColA,ColB", "")

At the bottom of the same page the numeric column 003214523 is wrapped.
vStr = "MyCompany" & $NUL & "'003214523'" & $NUL & "MyProduct" & $NUL & "MyType"
« Last Edit: September 28, 2017, 09:29:41 PM by cj »
Logged