SQLitening Support Forum

Insert multiple 50-column records using a 2-dimensional array

#INCLUDE "sqlitening.inc"
MACRO bindt(str)=slbuildbinddat(str,"T")

FUNCTION PBMAIN AS LONG

LOCAL c,r,rows,cols AS LONG
cols = 50
rows = 2

REDIM s(1 TO cols,1 TO rows) AS STRING
slOpen    "junk.db3","C"
slexe    "create table if not exists t1(c1 text,c2 text,c3 text,c4 text,c5 text,c6 text,c7 text,c8 text,c9 text,c10 text,c11 text,"  +_
          "c12 text,c13 text,c14 text,c15 text,c16 text,c17 text,c18 text,c19 text,c20 text,c21 text,c22 text,c23 text," +_
          "c24 text,c25 text,c26 text,c27 text,c28 text,c29 text,c30 text,c31 text,c32 text,c33 text,c34 text,c35 text," +_
          "c36 text,c37 text,c38 text,c39 text,c40 text,c41 text,c42 text,c43 text,c44 text,c45 text,c46 text,c47 text," +_
          "c48 text,c49 text,c50 text)
FOR r = 1 TO rows
  FOR c= 1 TO cols
  s(c,r) = bindt(".")  '<--- INSERT DATA (COLUMN,ROW)
  NEXT
NEXT
slexe "begin exclusive"
slexebind "insert into t1 values(" + REPEAT$(cols-1,"?,") + "?)",JOIN$(s(),""),USING$("V#",UBOUND(s,1))
slexe "end"
LOCAL sRecordSet() AS STRING
slSelAry "select rowid, * from t1 order by rowid",sRecordSet(),"Q44"
? JOIN$(sRecordSet(),$CR)
END FUNCTION

Hello CJ,

Thank you for your response and the brilliant solution you provided.

I should have had a search on the forum before asking the question. I ones asked the same question some years ago and you helped me solved the issue. Thank you.

https://sqlitening.planetsquires.com/index.php?topic=3541.msg23962#msg23962

Kind regards.

Eliminate 50-concatenations (increase ? marks to 50)
#INCLUDE ONCE "sqlitening.inc"
MACRO bind(str)=slbuildbinddat(str,"T")

FUNCTION PBMAIN AS LONG
 LOCAL sHospitalNo,sSurName AS STRING
 slOpen "junk.db3","C"
 slexe  "create table if not exists t1(c1 text, c2 text)"
 REDIM s(1 TO 50) AS STRING
 s(1) = bind(sHospitalNo)
 s(2) = bind(sSurName)
 slexebind "insert into t1 values(?,?)",JOIN$(s(),"")
END FUNCTION

Hello Guys!

Been a while in here. I still use SQLitening for most of my desktop applications.

I am having the "Statement to long/complex Error". in the code below.

Glad if someone can point me in the right direction into solving this.

Kind regards.

eCode = GetLocalFile(sPicturePath, sPicture)
If eCode Then ? "Picture not found error." + Str$(eCode), %MB_SYSTEMMODAL Or %MB_ICONINFORMATION, VD_App.Title
Errorcode& = slExeBind(slBuildInsertOrUpdate("tblAntenatalBioData", "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & _
"?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & _
"?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & _
"?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?"), _
slBuildBindDat(sHospitalNo, "T") & _
slBuildBindDat(sSurname, "T") & _
slBuildBindDat(sMaidenSurname, "T") & _
slBuildBindDat(sOthernames, "T") & _     
slBuildBindDat(SQLiteDate(sDate), "T") & _     
slBuildBindDat(sCategory, "T") & _
slBuildBindDat(SQLiteDate(sBirthDate), "T") & _     
slBuildBindDat(sAge, "T") & _     
slBuildBindDat(sConsultant, "T") & _ 
slBuildBindDat(SQLiteDate(sLMP), "T") & _
slBuildBindDat(SQLiteDate(sEDD), "T") & _ 
slBuildBindDat(sAddress, "T") & _ 
slBuildBindDat(sPhoneNo, "T") & _ 
slBuildBindDat(sEthnicGroup, "T") & _ 
slBuildBindDat(sOccupation, "T") & _ 
slBuildBindDat(sGravada, "T") & _ 
slBuildBindDat(sPara, "T") & _ 
slBuildBindDat(sKinName, "T") & _
slBuildBindDat(sKinAddress, "T") & _
slBuildBindDat(sKinPhoneNo, "T") & _ 
slBuildBindDat(sReligion, "T") & _ 
slBuildBindDat(sEnroleeNo, "T") & _ 
slBuildBindDat(SQLiteDate(sCardExpiryDate), "T") & _
slBuildBindDat(sPicturePath, "T") & _     
slBuildBindDat(sMaritalStatus, "T") & _ 
slBuildBindDat(SQLiteDate(sMarriageDate), "T") & _
slBuildBindDat(sHusbandName, "T") & _
slBuildBindDat(sHusbandPhoneNo, "T") & _ 
slBuildBindDat(sHusbandOccupation, "T") & _ 
slBuildBindDat(sBloodGroup, "T") & _ 
slBuildBindDat(sBloodTransfusion, "T") & _
slBuildBindDat(sMenstrualCycle, "T") & _ 
slBuildBindDat(sGenotype, "T") & _
slBuildBindDat(sRhesus, "T") & _
slBuildBindDat(sSpecialComments, "T") & _
slBuildBindDat(sCardiacDisease, "T" ) & _
slBuildBindDat(sKidneyDisease, "T") & _
slBuildBindDat(sRheumaticDisease, "T" ) & _   
slBuildBindDat(sMeasles, "T") & _
slBuildBindDat(sTuberculosis, "T") & _
slBuildBindDat(sOtherIllnesses, "T") & _
slBuildBindDat(sOperations, "T") & _
slBuildBindDat(sFamilyTuberculosis, "T") & _
slBuildBindDat(sDiabetes, "T") & _
slBuildBindDat(sHypertension, "T") & _
slBuildBindDat(sTwins, "T") & _
slBuildBindDat(sOtherDiseases, "T") & _
slBuildBindDat(sPicture, "B") & _
slBuildBindDat(gUsername, ("T")),"E") 


Example: Get every 7th row
select rowid from parts where rowid%7=0 order by rowid

This example shows 3-ways to get price column to order by numerically
if printf with "AS" using the "price" column name is used.
1. Use no AS and ignore the long column name
2. Specify parts.price in the order by
3. Use a different name with "AS" than the price column

#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING

FUNCTION PBMAIN AS LONG
 slOpen "junk.db3"
 slexe  "drop table if exists parts"
 slexe  "create table if not exists parts(price integer)"
 slexe  "insert into parts values(1995),(1300),(4995),(101),(703)"

 'if AS is used with same name as column with printf the order by will be alpha
 rs "select printf('%.2f',price*.01) AS price from parts order by price"

 'these order numerically
 rs "select printf('%.2f',price*.01) from parts order by price"                'no AS
 rs "select printf('%.2f',price*.01) AS price from parts order by parts.price" 'use table.column
 rs "select printf('%.2f',price*.01) AS XYZ from parts order by price"         'AS different
 ? gs
END FUNCTION

FUNCTION rs(sql AS STRING) AS STRING
 LOCAL sArray() AS STRING
 IF ISFALSE(slSelAry(sql,sArray(),"Q9 E2")) THEN
  gs+= JOIN$(sArray(),$CR) + $CR + $CR
 ELSE
  gs = "error:" + $CR + sql + $CR + $CR
 END IF
END FUNCTION

Setup machine running SQLiteningServer.exe
1 type ipconfig at command prompt
2 look for IpV4 address under ethernet adapter, example 192.168.0.2
3 router software, port forward 51234 to 192.168.0.2
4 allow an app through windows firewall, click change settings, add sqliteningserver

Create shortcut
1 https://freemyip.com
2 enter unique word "klum", check availability, claim it and copy link provided
3 create new shortcut and paste link provided into location and name it "remote"
4 click "remote" shortcut and browser must return "Ok"
5 slconnect "klum.freemyipcom",51234

Execute "remote" shortcut at Windows startup if remote ip address changes

Fredrick,
I didn't see this old thread.
Hope you are doing great!!

Forum: https://sqlite.org/forum/forum
First post about SQLitening, by anyone,  on SQLite forum:  https://sqlite.org/forum/forumpost/a0a775c72e
Someone asked about client/server for SQLite and I couldn't help myself.