Saving Images Revisited - Best Practice

[cj]

Inserts can be trapped like any other error because an error is returned.
If slSetProcessMods "E0" is used the slGetChangeCount will still return 0 if an insert error occurs.
It is just the update that doesn't return an error.

[Fredrick Ughimi]

Yeah I use the slGetChangeCount function in my SAVE and UPDATE routines. Something like:

Code Select Expand



If slGetChangeCount = 0 Then
       
        ? "Record Not Saved!", %MB_TASKMODAL Or %MB_ICONINFORMATION, VD_App.Title
       
        Exit Method
               
     Else
     ...
     
End IF


[cj]

I missed the question in reply #13 about the syntax error.
The "E" (handle our own errors rsModChar for any lurkers) needs to be at the end of the call.
This is one reason that I separated the slBuildInsertOrUpdate from the same call in the second code block below.
It is a bit hard for me to follow all the parameters.

Code Select Expand


    LOCAL ErrorCode AS LONG
    Errorcode& = slExeBind( _
    slBuildInsertOrUpdate("tblTestImages", "?" & $NUL & "?" & $NUL & "?" & $NUL & "?"), _
     slBuildBindDat(F1, "T") & _
     slBuildBindDat(F2, "T") & _
     slBuildBindDat(F3, "T") & _
     slBuildBindDat(F4, "B"),"E")
     IF Errorcode& THEN
          MSGBOX "Error"
          EXIT FUNCTION
      ELSE
        ? "Saved!"
       END IF

or

Code Select Expand



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

  result = slExeBind(sql,sBindData, "E")
  IF result THEN ? "Trapped error" + STR$(result)


[Fredrick Ughimi]

CJ,

Have you tested your codes above? None of the options traps the error 19.

[cj]

Yes, they all work.
The result code 19 comes back using the "E" parameter.

[cj]

Code Select Expand


#COMPILE EXE "\sql\bin\slexebind.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
  'slSetProcessMods "E0"
  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
  'slExe "drop table if exists " + sTableName 'drop previous table
  sColumnNames = "F1,F2,F3,F4"
  slExe "create table if not exists tblTestImages(F1 UNIQUE,F2,F3,F4)"
  F1 = "Number"
  F2 = "Name"
  F3 = "cloud.jpg"
  slGetFile F3, F4
  sWhere = ""  'insert a record
  'sWhere = "rowid < 11"  'update multiple records

  F1 = "SAME VALUE"  'see if field 1 changed to the time"
  sLayout = "?" & $NUL & "?" & $NUL & "?" & $NUL & "?"
  llChangeCount = slGetChangeCount("T")
  sql = slBuildInsertOrUpdate( _
   sTableName, _                                  'table name
   sLayOut, _                                     '? & NULS
   sColumnNames,                              _   'column names
   sWhere)


   #IF 0
     LOCAL ErrorCode AS LONG
    Errorcode& = slExeBind( _
    slBuildInsertOrUpdate("tblTestImages", "?" & $NUL & "?" & $NUL & "?" & $NUL & "?"), _
     slBuildBindDat(F1, "T") & _
     slBuildBindDat(F2, "T") & _
     slBuildBindDat(F3, "T") & _
     slBuildBindDat(F4, "B"),"E0")
     IF Errorcode& = 19 THEN
          MSGBOX "Error"
          EXIT FUNCTION
     END IF
    ? "Saved!"
  #ENDIF
  sBindData = _ &
   slBuildBindDat(F1, "T") & _
   slBuildBindDat(F2, "T") & _
   slBuildBindDat(F3, "T") & _
   slBuildBindDat(F4, "B")

  LOCAL result AS LONG
  result = slExeBind(sql,sBindData, "E0")
  IF result THEN ? "Trapped error" + STR$(result)

  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]

CJ,

Yeah they all work. Sorry my mistake. I removed the index earlier on when I was testing for other things.

[cj]

I hear ya.

I added a UNIQUE  clause and often forget to delete previous test table.
Not sure about the error 19 being returned, but can live with that.
slExe "create table if not exists tblTestImages(F1 UNIQUE,F2,F3,F4)"

[Fredrick Ughimi]

CJ,

Not sure about the error 19 being returned, but can live with that.

It returns error 19 alright:

Code Select Expand


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


[Fredrick Ughimi]

Hello,

Code Select Expand


slGetFile sPixName, sPicture, "E0" '9216 bytes

Errorcode& = slExeBind(slBuildInsertOrUpdate("tblBioData", "?" & $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(sTitle, "T") & _
      slBuildBindDat(sSurname, "T") & _
      slBuildBindDat(sOthernames, "T") & _     
      slBuildBindDat(SQLiteDate(sDateCreated), "T") & _     
      slBuildBindDat(sPhoneNo, "T") & _     
      slBuildBindDat(sEmailAddress, "T") & _     
      slBuildBindDat(sHomeAddress, "T") & _ 
      slBuildBindDat(sOfficeAddress, "T") & _ 
      slBuildBindDat(sCompany, "T") & _ 
      slBuildBindDat(sOccupation, "T") & _ 
      slBuildBindDat(sSex, "T") & _ 
      slBuildBindDat(sAge, "T") & _ 
      slBuildBindDat(sAgeClass, "T") & _ 
      slBuildBindDat(sMaritalStatus, "T") & _ 
      slBuildBindDat(SQLiteDate(sBirthDate), "T") & _ 
      slBuildBindDat(sReligion, "T") & _ 
      slBuildBindDat(sStateOrigin, "T") & _ 
      slBuildBindDat(sTribe, "T") & _
      slBuildBindDat(sEnroleeNo, "T") & _ 
      slBuildBindDat(sCategory, "T") & _ 
      slBuildBindDat(sPlan, "T") & _
      slBuildBindDat(sEntitlements, "T") & _ 
      slBuildBindDat(sEnroleeExpiry, "T") & _ 
      slBuildBindDat(sHMOName, "T") & _ 
      slBuildBindDat(sLGA, "T") & _
      slBuildBindDat(sBirthPlace, "T") & _
      slBuildBindDat(sNationality, "T") & _
      slBuildBindDat(sPixName, "T") & _
      slBuildBindDat(sKinPhone, "T") & _
      slBuildBindDat(sKinEmail, "T") & _
      slBuildBindDat(sKinAddress, "T" ) & _
      slBuildBindDat(sKinName, "T") & _
      slBuildBindDat(sRelationship, "T") & _
      slBuildBindDat(sPicture, ("B")),"E")



The code above works locally and not remotely and I don't know why. Locally the image savesin the Picture field and can be retrieved easily when needed. But remotely, the image wouldn't save. Has anyone experience this before?

[cj]

No, haven't had a problem.
Place "Hello, world!" in BlobFile.txt" on server

Code Select Expand


#COMPILE EXE "\sql\bin\test"
#INCLUDE "\sql\inc\sqlitening.inc"
$IpAddress = "192.168.1.2"
%PortNumber = 51234
FUNCTION PBMAIN () AS LONG
  LOCAL sPixName, sPicture,sql AS STRING
  LOCAL x, columns AS LONG
  slConnect $IpAddress,%PortNumber
  slOpen "test.db3
  slexe "Drop Table if exists T1"
  slexe "Create Table if not exists T1(F1)"
  sPixName = "Blobfile.txt"
  slGetFile sPixName, sPicture
  slExeBind("Insert into T1 values(?)",slBuildBindDat(sPicture))
  sql = "Select * from T1"
  slSel(sql)
  columns = slGetColumnCount
  DO WHILE slGetRow
    FOR x = 1 TO columns
      ? slf(x),,"slf"
    NEXT
  LOOP
END FUNCTION


[Fredrick Ughimi]

cj,

Executed your program with a little modification. I also created the file ("Blobfile.txt") the project directory -

Code Select Expand



#COMPILE EXE "test"
#INCLUDE "sqlitening.inc"
$IpAddress = "Server"
%PortNumber = 51234
FUNCTION PBMAIN () AS LONG
  LOCAL sPixName, sPicture,sql AS STRING
  LOCAL x, columns AS LONG
  slConnect $IpAddress, %PortNumber
  slOpen ("test.db3","C")
  slexe "Drop Table if exists T1"
  slexe "Create Table if not exists T1(F1)"
  sPixName = "Blobfile.txt"
  slGetFile sPixName, sPicture
  slExeBind("Insert into T1 values(?)",slBuildBindDat(sPicture))
  sql = "Select * from T1"
  slSel(sql)
  columns = slGetColumnCount
  DO WHILE slGetRow
    FOR x = 1 TO columns
      ? slf(x),,"slf"
    NEXT
  LOOP
END FUNCTION                                 


I got error -9 = File does not exist. statement=GetFile "Blobfile.txt"

I guess this line:

Code Select Expand


slGetFile sPixName, sPicture


is the culprite.

[cj]

Was the file missing on the server in your program?
In other words,  did checking for an error in slGetFile solve the problem?

[Fredrick Ughimi]

Hello cj,

You program runs now since I moved the missing file on the server computer.

Was the file missing on the server in your program?

No. I saved it on a client computer. Must it be saved on the Server Computer?

In other words,  did checking for an error in slGetFile solve the problem?

Yes I did check for error by using the "E" modifier no error came out. Just blank.

[Fredrick Ughimi]

Hello,

Just tested my program with the "E2" modifier and I got the FILE NOT FOUND ERROR -9

Code Select Expand


slGetFile sPixName, sPicture, "E2"


But the same command works locally.