• Welcome, Guest. Please login.
 
December 13, 2019, 08:27:51 pm

News:

Welcome to the SQLitening support forums!


Easy Insert/Update using array

Started by cj, September 25, 2017, 09:05:31 pm

Previous topic - Next topic

cj

September 25, 2017, 09:05:31 pm Last Edit: September 26, 2017, 02:20:34 am by cj
'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


[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]

Fredrick Ughimi

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,
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

September 26, 2017, 08:13:33 pm #2 Last Edit: September 26, 2017, 08:19:28 pm by cj
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)"

Fredrick Ughimi

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


I know. I mean in functionality.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

September 26, 2017, 08:53:25 pm #4 Last Edit: September 26, 2017, 08:56:11 pm by cj
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.




Fredrick Ughimi

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.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

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


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.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

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.


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,
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

September 28, 2017, 08:06:13 pm #9 Last Edit: September 28, 2017, 09:14:53 pm by cj
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

$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

cj

September 28, 2017, 11:57:53 pm #10 Last Edit: September 28, 2017, 11:59:41 pm by cj
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"