Saving Images Revisited - Best Practice

[Fredrick Ughimi]

Hello,

Is it best practice to save Images in a separate table? Because when I tried saving an image along with other items the image get saved but I null in other fields.

Code Select Expand


slExe "Begin Immediate"
            Errorcode& = slExe (slBuildInsertOrUpdate("tblBioData", "'" & sPHINo & "'" & $Nul & sTitle & $Nul & sSurname & $Nul & sOthernames _
            & $Nul & SQLiteDate(sDateCreated) & $Nul & sAddress & $Nul & sOccupation & $Nul & sSex & $Nul & sChronicConditions & $Nul & sAge _
            & $Nul & sAgeClass & $Nul & sMaritalStatus & $Nul & sSpouseName & $Nul & SQLiteDate(sBirthDate) & $Nul & sReligion _
            & $Nul & sPlan & $Nul & sClass  & $Nul & sFamilySize & $Nul & sGenotype & $Nul & sBloodGroup & $Nul & sKinName & $Nul & sPixName _
            & $Nul & sExpiryDate),"E")
           
           slGetFile sPixName, lsA
           slExeBind slBuildInsertOrUpdate("tblBioData","?", "Images"), slBuildBindDat(lsA)
   
        slExe "End"


[cj]

Removed the delimiter and $CRLF from read dataset to verify file is 9216 bytes.

I have another question.
How do you insert a $NUL into F1 to automatically increment the rowid?
slExe("Create Table If not Exists T1(F1 INTEGER PRIMARY KEY ,F2,F3)")

Code Select Expand


#COMPILE EXE "\sql\bin\BlobTest"
#DIM ALL
#INCLUDE "\sql\inc\sqlitening.inc"

FUNCTION PBMAIN AS LONG
  LOCAL s, sBuffer, sAll AS STRING
  LOCAL x, columns AS LONG
  slGetFile("\windows\write.exe", sBuffer) '9216 bytes
  slOpen("test.db3","C")
  slExe("Drop Table If Exists T1")
  slExe("Create Table If not Exists T1(F1,F2,F3)")
  s = slBuildBindDat("column one") & _
      slBuildBindDat("column two") & _
      SlBuildBindDat(sBuffer)
  slExeBind "Insert into T1 values(?,?,?)", s
  slSel "Select f3 from T1"
  columns = slGetColumnCount
  IF columns = 0 THEN EXIT FUNCTION
  s = ""
  DO WHILE slGetRow
    FOR x = 1 TO Columns
      s = s + slf(x)
    NEXT
    sAll = sAll + s
  LOOP
  ? STR$(LEN(sAll))  '9216 bytes
END FUNCTION


[Bern Ertl]

Fred, if you were trying to UPDATE the record that was inserted after the BEGIN call, you need to add a record/index identifier  (rsWhere parameter) to the slBuildInsertOrUpdate function that is being passed to slExeBind.

[Fred Meier]

Bern's answerer is correct but I have a question --- why are you updating the row immediately after inserting, why not just insert all, including the image, using a single slExeBind?

Is it best practice to save Images in a separate table?

All depends on how often they are accessed. If not as often as the other fields then I would consider using another file and doing slAttach only when the image is needed.

How do you insert a $NUL into F1 to automatically increment the rowid?

slExeBind slBuildInsertOrUpdate("T1", "NULL" & $Nul & "?" & $Nul & "?"), s

[Fredrick Ughimi]

Hello,

I have two questions. Been struggling with this for a while now.

1. How to insert the  "E" parameter of the slBuildInsertOrUpdate function for an INSERT operation to prevent duplicate entries because field HospitalNo is unique

Code Select Expand


slGetFile sPixPath, sPicture '9216 bytes

s = slBuildBindDat(sHospitalNo, "T") & _
      slBuildBindDat(sName, "T") & _
      slBuildBindDat(sPixPath, "T") & _
      SlBuildBindDat(sPicture)
 
slExeBind slBuildInsertOrUpdate("tblTestImages", "?" & $Nul & "?" & $Nul & "?" & $Nul & "?"), s

If Errorcode& = 19 Then
     MsgBox "HospitalNo Already Exist", %MB_TASKMODAL Or %MB_ICONINFORMATION, VD_App.Title 
     Control Set Focus nCbHndl, %ID_FRMUPDATEDOCTORS_TXTNAME
     Exit Function
End If
  ? "Saved!"


2. How to insert the  rsWhere parameter of the slBuildInsertOrUpdate function for an UPdate operation

Code Select Expand


slGetFile sPixPath, sPicture '9216 bytes

s = slBuildBindDat(sHospitalNo, "T") & _
      slBuildBindDat(sName, "T") & _
      slBuildBindDat(sPixPath, "T") & _
      SlBuildBindDat(sPicture)
  'slExeBind "Insert into tblTestImages values(?,?,?,?)", s   
 
  slExeBind slBuildInsertOrUpdate("tblTestImages", "?" & $Nul & "?" & $Nul & "?" & $Nul & "?"), s
 
? "Updated!"
[code]




[Fredrick Ughimi]

For my new Update routine I get:

slGetFile sPixPath, sPicture '9216 bytes
slExeBind slBuildInsertOrUpdate("tblTestImages", "?" & $Nul & "?" & $Nul & "?" & $Nul & "?", "rowid=" & sRecordNo), _
                                    slBuildBindDat(sHospitalNo, "T") & _
                                    slBuildBindDat(sName, "T") & _
                                    slBuildBindDat(sPixPath, "T") & _
                                    slBuildBindDat(sPicture) 

I get a syntax error.

[cj]

It compiles with PB10

Code Select Expand


#COMPILE EXE "\sql\bin\test.exe"
#DIM ALL
#INCLUDE "\sql\inc\sqlitening.inc"

FUNCTION PBMAIN () AS LONG
  LOCAL sPixPath AS STRING
  LOCAL sPicture AS STRING
  LOCAL sRecordNo AS STRING
  LOCAL sHospitalNo AS STRING
  LOCAL sName AS STRING

  slGetFile sPixPath, sPicture '9216 bytes
  slExeBind slBuildInsertOrUpdate("tblTestImages", "?" & $NUL & "?" & $NUL & "?" & $NUL & "?", "rowid=" & sRecordNo), _
                                    slBuildBindDat(sHospitalNo, "T") & _
                                    slBuildBindDat(sName, "T") & _
                                    slBuildBindDat(sPixPath, "T") & _
                                    slBuildBindDat(sPicture)

END FUNCTION


[Fredrick Ughimi]

Yeah compiles here. But gives a run time syntax error 1 when trying to execute it.

[cj]

There is a comma where a  & should be in the second parameter string the rsValues (the , before "rowid=")
slBuildInsertOrUpdate (rsTable String, rsValues String, [rsColumns String, rsWhere String]) String

Code Select Expand


slExeBind slBuildInsertOrUpdate("tblTestImages", "?" & $Nul & "?" & $Nul & "?" & $Nul & "?", "rowid=" & sRecordNo), _
                                    slBuildBindDat(sHospitalNo, "T") & _
                                    slBuildBindDat(sName, "T") & _
                                    slBuildBindDat(sPixPath, "T") & _
                                    slBuildBindDat(sPicture) 


Should be

Code Select Expand


slExeBind slBuildInsertOrUpdate( _
   "tblTestImages","?" & $NUL & "?" & $NUL & "?" & $NUL & "?" & $NUL & sRecordNo), _
   slBuildBindDat(sHospitalNo, "T") & _
   slBuildBindDat(sName, "T") & _
   slBuildBindDat(sPixPath, "T") & _
   slBuildBindDat(sPicture)


Code Select Expand


#COMPILE EXE "\sql\bin\test.exe"
#DIM ALL
#INCLUDE "\sql\inc\sqlitening.inc"

FUNCTION PBMAIN () AS LONG
  LOCAL sPixPath,sPicture, sRecordNo, sHospitalNo, sName AS STRING
  slConnect "192.168.1.2",51234
  slOpen "test.db3","c"
  slExe "drop table if exists tblTestImages"
  slExe "create table if not exists tblTestImages(f1,f2,f3,f4,F5)"
  sPixPath = "sPixPath"
  'slGetFile sPixPath, sPicture '9216 bytes
  sPicture  = "sPicture"
  sHospitalNo = "sHospitalNo"
  sName       = "sName"
  sRecordNo = "sRecordNo"
   slExeBind slBuildInsertOrUpdate( _
   "tblTestImages","?" & $NUL & "?" & $NUL & "?" & $NUL & "?" & $NUL & sRecordNo), _
   slBuildBindDat(sHospitalNo, "T") & _
   slBuildBindDat(sName, "T") & _
   slBuildBindDat(sPixPath, "T") & _
   slBuildBindDat(sPicture)
END FUNCTION


[Fredrick Ughimi]

This is an UPDATE routine. slBuildInsertOrUpdate is seeing sRecordNo as the fifth value. It isn't. tblTestImages has just four fields. sRecordNo is supposed to be the ROWID.

[cj]

http://www.sqlitening.com/support/index.php?topic=3004.0
I am studying this thread from June 2009 on the subject.
Not finding an example using these three together.
slExeBind
slBuildInsertOrUpdate
slBuildBindData.

ExampleC.bas uses  this format:
slExeBind "Update Customers Set Memo = ? where CustomerID = " & READ$(llDo), slBuildBindDat(lsMemo, "TCN")

Not able to get UPDATE to work with the 3 functions together.

[Fredrick Ughimi]

This UPDATE routine worked perfectly. I clearly forgot to add the field names:

Code Select Expand


slGetFile sPixPath, sPicture '9216 bytes
   slExeBind slBuildInsertOrUpdate( _
   "tblTestImages","?" & $Nul & "?" & $Nul & "?" & $Nul & "?", "HospitalNo, Name, PixPath, Picture", "rowid=" & sRecordNo), _
   slBuildBindDat(sHospitalNo, "T") & _
   slBuildBindDat(sName, "T") & _
   slBuildBindDat(sPixPath, "T") & _
   slBuildBindDat(sPicture)
   
  ? "Updated!"
 


[cj]

Fredrick,
Thank you for posting the code!

I will work on a function to encapsulate slExeBind
because I do not like hardcoding data values.

1) Separated the SQL statement from the bind data
to come up with CALL slExeBind(sql$,sBindData$)
which makes it a bit easier to understand and
allows looking at the SQL part before calling.

2) Update failures ARE NOT REPORTED so added slGetChangeCount.

3) New function will automatically create the "?" and $NULs.
Others have probably already done this.

4) slExe "BEGIN" before first slGetChangeCount and slExe "END" after second slGetChangeCount?

Code Select Expand


#COMPILE EXE "\sql\bin\test.exe"
#DIM ALL
#INCLUDE "\sql\inc\sqlitening.inc"

FUNCTION PBMAIN () AS LONG
  LOCAL sIpAddress    AS STRING
  LOCAL PortNumber    AS LONG
  LOCAL sDataBase     AS STRING
  LOCAL sTableName    AS STRING
  LOCAL sColumnNames  AS STRING
  LOCAL F1,F2,F3,F4   AS STRING
  LOCAL sWhere        AS STRING
  LOCAL sql           AS STRING
  LOCAL sBindData     AS STRING
  LOCAL sLayout       AS STRING
  LOCAL llChangeCount AS LONG

  sIpAddress = "192.168.1.2"
  PortNumber = 0                  'default port
  sDataBase  = "test.db3"         'database name
  sTableName = "tblTestImages"    'table  name
  slConnect sIpAddress,PortNumber 'connect if remote
  slOpen sDataBase, "C"           'open/create database
  REM slExe "drop table if exists " + sTableName 'drop previous table
  sColumnNames = "F1,F2,F3,F4"
  slExe "create table if not exists " + sTableName + " (" + sColumnNames +")"
  F2 = "Name"
  F3 = "cloud.jpg"
  slGetFile F3, F4
  sWhere = ""  'insert a record
  sWhere = "rowid < 11"  'update multiple records

  F1 = TIME$  'see if field 1 changed to the time"
  sLayout = "?" & $NUL & "?" & $NUL & "?" & $NUL & "?"

  llChangeCount = slGetChangeCount("T") 'total changes since open

  sql = slBuildInsertOrUpdate( _
   sTableName, _                                  'table name
   sLayOut, _                                     '? & NULS
   sColumnNames,                              _   'column names
   sWhere)                                        'where clause

  sBindData = slBuildBindDat(F1, "T") & _
   slBuildBindDat(F2, "T") & _
   slBuildBindDat(F3, "T") & _
   slBuildBindDat(F4, "B") 'blob/image

  slExeBind sql,sBindData 
  'total changes now minus before calling slExeBind.
  llChangeCount = slGetChangeCount("T") - llChangeCount
  IF LEN(sWhere) THEN  'updating
    IF llChangeCount THEN
      ? "Update" + STR$(llChangeCount)
    ELSE
      ? "Update failed",%MB_ICONERROR,sWhere
    END IF
  ELSE                  'inserting
    IF llChangeCount THEN
      ? "Insert" + STR$(llChangeCount)
    ELSE
      ? "Insert failed",%MB_ICONERROR,sWhere
    END IF
  END IF

END FUNCTION


[Fredrick Ughimi]

Hello CJ,

Thanks for sharing your thoughts

Hello,

The next step is trap error 19 in the SAVE (INSERT) routine. I get syntax error with code below. I tried various option. No dice.

Code Select Expand


Errorcode& = slExeBind(slBuildInsertOrUpdate("tblTestImages", "?" & $Nul & "?" & $Nul & "?" & $Nul & "?"), "E"), _
                                    slBuildBindDat(sHospitalNo, "T") & _
                                    slBuildBindDat(sName, "T") & _
                                    slBuildBindDat(sPixPath, "T") & _
                                    slBuildBindDat(sPicture)
 
If Errorcode& = 19 Then
          MsgBox "HospitalNo Already Exists ", %MB_TASKMODAL Or %MB_ICONINFORMATION, VD_App.Title 
          Control Set Focus nCbHndl, %ID_FRMCHART_TXTHOSPITALNO
          Exit Function
End If

? "Saved!"


There is another means to trap error 19 though. The code below works:

Code Select Expand


slSEL "SELECT HospitalNo From tblTestImages WHERE HospitalNo = '" + sHospitalNo + "' " 
If IsTrue slGetRow Then
     MsgBox "HospitalNo Already Exist",%MB_TASKMODAL Or %MB_ICONINFORMATION, VD_App.Title
     Control Set Focus nCbHndl,  %ID_FRMCHART_TXTHOSPITALNO
     Exit Function
End If


But I'd like to see the first code option work.

[cj]

slGetInsertID () Quad   
Returns the rowid key of the most recent insert into the database.
Returns zero if an error occurs and the global return errors flag is on.