• Welcome, Guest. Please login.
 
October 21, 2019, 12:53:09 am

News:

Welcome to the SQLitening support forums!


Saving Images Revisited - Best Practice

Started by Fredrick Ughimi, January 02, 2013, 10:44:26 pm

Previous topic - Next topic

Fredrick Ughimi

January 02, 2013, 10:44:26 pm Last Edit: April 11, 2013, 11:32:26 pm by 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.


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

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


#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?

QuoteIs 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.

QuoteHow 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


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


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

Fredrick Ughimi

For my new Update routine I get:

Quote
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.

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

cj

It compiles with PB10


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

cj

April 13, 2013, 04:01:16 am #8 Last Edit: April 13, 2013, 04:29:10 am by 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

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


Should be

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



#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.

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

cj

April 13, 2013, 12:32:42 pm #10 Last Edit: April 13, 2013, 02:03:10 pm by 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:


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

cj

April 14, 2013, 02:59:22 am #12 Last Edit: April 14, 2013, 10:07:43 am by 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?



#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.


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:


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

cj

April 14, 2013, 10:12:14 am #14 Last Edit: April 14, 2013, 10:17:45 am by 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.