Statement to long/complex Error

[Fredrick Ughimi]

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.

Code Select Expand

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") 


[cj]

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

[Fredrick Ughimi]

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.

[cj]

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

[cj]

Demonstrate using a UDT with a blob column

Read/write files to blob https://sqlitening.planetsquires.com/index.php?topic=9676.msg25764#msg25764
Upsert could also be used  https://sqlitening.planetsquires.com/index.php?topic=9709.msg26107#msg26107

#INCLUDE "sqlitening.inc"
%DropTable=1

TYPE RecordType
 num1 AS LONG
 num2 AS CUX
 time AS STRING * 8
END TYPE

FUNCTION PBMAIN AS LONG 'fim10.bas
LOCAL rec    AS RecordType
LOCAL sKey,s,sPrompt,sTitle AS STRING

slOpen "junk.db3","C"
IF %DropTable THEN slexe "drop table if exists VER"
slexe  "create table if not exists VER(key text unique,blob)"
sKey = "fim"
DO
 sKey = INPUTBOX$(sPrompt,sTitle,sKey)
 IF LEN(sKey) = 0 THEN EXIT FUNCTION
 slexe "begin immediate

 slSel "select key,blob from VER where key=" + WRAP$(sKey,$SQ,$SQ)

 IF slGetRow THEN      'UPDATE
  s = slfn("blob")    'put blob into dynamic string
  TYPE SET rec = s    'set UDT  with dynamic string

  'modify members
  INCR rec.num1
  INCR rec.num2
  rec.time = TIME$
  s=rec                'put UDT into dynamic string
  s=slBuildBindDat(s)  'bind dynamic string
  slexebind  "Update ver Set blob = ? where key=?",s + slBuildBindDat(sKey,"T")
  IF slGetChangeCount = 0 THEN sTitle = "update failed" ELSE sTitle = "Update"
 ELSE
  'modify members
  rec.num1 = 1          'modify member1
  rec.num2 = rec.num1 +1'modify member2
  rec.time = TIME$      'modify member3

  s=rec                'put UDT into into dynamic string
  s=slBuildBindDat(s)  'bind dynamic string
  slexebind "insert into VER values(?,?)",slBuildBindDat(sKey,"T") + s
  IF slGetChangeCount = 0 THEN sTitle = "insert failed" ELSE sTitle = "Insert"
 END IF

 slexe "end"

 slSel "select * from VER where key=" + WRAP$(sKey,$SQ,$SQ)
 DO WHILE slGetRow
  TYPE SET rec = slfn("blob")
  sPrompt = USING$("num1=#&num2=#&&",rec.num1,$CR,rec.num2,$CR,rec.time)
 LOOP
LOOP
END FUNCTION