SQLitening Support Forum

Please login or register.

Login with username, password and session length
Advanced search  

News:

Welcome to the SQLitening support forums!

Pages: [1] 2 3 ... 5

Author Topic: Saving Images Revisited - Best Practice  (Read 14688 times)

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 601
    • View Profile
    • Software Development Company
Saving Images Revisited - Best Practice
« on: January 02, 2013, 09:14:26 PM »

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]
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"
« Last Edit: April 11, 2013, 09:02:26 PM by Fredrick Ughimi »
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

cj

  • Master Geek
  • ****
  • Posts: 700
    • View Profile
Re: Saving Images Revited - Best Practice
« Reply #1 on: January 03, 2013, 07:38:55 AM »

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]
#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
Logged

Bern Ertl

  • Master Geek
  • ****
  • Posts: 496
  • Excellent
    • View Profile
    • InterPlan Systems
Re: Saving Images Revited - Best Practice
« Reply #2 on: January 03, 2013, 09:44:40 AM »

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

  • Master Geek
  • ****
  • Posts: 806
    • View Profile
Re: Saving Images Revited - Best Practice
« Reply #3 on: January 03, 2013, 10:12:33 AM »

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?

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

Quote
How do you insert a $NUL into F1 to automatically increment the rowid?
slExeBind slBuildInsertOrUpdate("T1", "NULL" & $Nul & "?" & $Nul & "?"), s
Logged

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 601
    • View Profile
    • Software Development Company
Re: Saving Images Revited - Best Practice
« Reply #4 on: April 11, 2013, 09:01:44 PM »

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


Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 601
    • View Profile
    • Software Development Company
Re: Saving Images Revisited - Best Practice
« Reply #5 on: April 12, 2013, 07:56:07 AM »

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.

Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

cj

  • Master Geek
  • ****
  • Posts: 700
    • View Profile
Re: Saving Images Revisited - Best Practice
« Reply #6 on: April 12, 2013, 10:16:08 AM »

It compiles with PB10

Code: [Select]
#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
Logged

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 601
    • View Profile
    • Software Development Company
Re: Saving Images Revisited - Best Practice
« Reply #7 on: April 12, 2013, 05:39:58 PM »

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

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

cj

  • Master Geek
  • ****
  • Posts: 700
    • View Profile
Re: Saving Images Revisited - Best Practice
« Reply #8 on: April 13, 2013, 01:31:16 AM »

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]
slExeBind slBuildInsertOrUpdate("tblTestImages", "?" & $Nul & "?" & $Nul & "?" & $Nul & "?", "rowid=" & sRecordNo), _
                                    slBuildBindDat(sHospitalNo, "T") & _
                                    slBuildBindDat(sName, "T") & _
                                    slBuildBindDat(sPixPath, "T") & _
                                    slBuildBindDat(sPicture) 

Should be
Code: [Select]
slExeBind slBuildInsertOrUpdate( _
   "tblTestImages","?" & $NUL & "?" & $NUL & "?" & $NUL & "?" & $NUL & sRecordNo), _
   slBuildBindDat(sHospitalNo, "T") & _
   slBuildBindDat(sName, "T") & _
   slBuildBindDat(sPixPath, "T") & _
   slBuildBindDat(sPicture)

Code: [Select]
#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
« Last Edit: April 13, 2013, 01:59:10 AM by cj »
Logged

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 601
    • View Profile
    • Software Development Company
Re: Saving Images Revisited - Best Practice
« Reply #9 on: April 13, 2013, 03:52:35 AM »

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.

Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

cj

  • Master Geek
  • ****
  • Posts: 700
    • View Profile
slExeBind update
« Reply #10 on: April 13, 2013, 10:02:42 AM »

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.
« Last Edit: April 13, 2013, 11:33:10 AM by cj »
Logged

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 601
    • View Profile
    • Software Development Company
Re: Saving Images Revisited - Best Practice
« Reply #11 on: April 13, 2013, 08:10:48 PM »

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

Code: [Select]
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!"
 
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

cj

  • Master Geek
  • ****
  • Posts: 700
    • View Profile
Re: Saving Images Revisited - Best Practice
« Reply #12 on: April 14, 2013, 12:29:22 AM »

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]
#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
« Last Edit: April 14, 2013, 07:37:43 AM by cj »
Logged

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 601
    • View Profile
    • Software Development Company
Re: Saving Images Revisited - Best Practice
« Reply #13 on: April 14, 2013, 07:38:59 AM »

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]
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]
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.
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

cj

  • Master Geek
  • ****
  • Posts: 700
    • View Profile
Re: Saving Images Revisited - Best Practice
« Reply #14 on: April 14, 2013, 07:42:14 AM »

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.

« Last Edit: April 14, 2013, 07:47:45 AM by cj »
Logged
Pages: [1] 2 3 ... 5