• Welcome, Guest. Please login.
 
July 18, 2019, 09:38:23 am

News:

Welcome to the SQLitening support forums!


Saving Images Remotely

Started by Fredrick Ughimi, August 12, 2017, 12:07:14 pm

Previous topic - Next topic

Fredrick Ughimi

Hello,

On a Client/Server arrangement I guess its ok to load images from a
sub directory of the client computer i. e C:\ClientFiles\Pictures
and saved on the remote SQlitening Database.

I get a error "-8 Access Denied" and sometimes I get "-9 - File does not exit"


m_sPixNamePath = Exe.Path$ & "Pictures\" & m_sPixName
slGetFile m_sPixNamePath, m_sPicture, "E1" '9216 bytes


Is there a way around this?
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

Thanks CJ.

Tried out the codes in my program and I got a crash. Same thing happened when I ran it on the PB IDE.


#COMPILE EXE
#DIM ALL
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
    LOCAL sServer AS STRING
    LOCAL sPort AS STRING
    LOCAL sErr AS STRING
    LOCAL Errorcode&

    sServer = "MegaNetSoftware"

    sPort = "Default"

    slConnect(sServer, VAL(sPort))

    sErr = slGetError
    IF VAL(sErr) <> 0 THEN
         MSGBOX sErr, %MB_TASKMODAL OR %MB_ICONINFORMATION, EXE.NAME$
         EXIT FUNCTION
    END IF

   slOpen ("CharkinERPDB.db3","C")

LOCAL m_sPixNamePath AS STRING
LOCAL m_sPixName     AS STRING
LOCAL m_sPicture     AS STRING

m_sPixnamePath = EXE.PATH$ & "Pictures\"
m_sPixName     = "Ughimi.jpg"
slSetProcessMods "L0"
slGetFile m_sPixnamePath & m_sPixName,m_sPicture,"E1"
IF slGetErrorNumber = 0 THEN ? USING$("image length #,",LEN(m_sPicture))
Errorcode& = slExeBind(slBuildInsertOrUpdate("tblDPRRegistration", "?"), _
slBuildBindDat(m_sPicture, ("B")))
END FUNCTION
                               

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

cj

August 15, 2017, 07:07:37 pm #2 Last Edit: August 16, 2017, 09:27:41 pm by cj
Note: Switching between local mode (L0) and remote mode (L1) can be eliminated using GET or GET$ instead of slGetFile reading local .jpg files.

SQlite suggests storing images in a table rather than pointing to there location.
One article mentions that the opening and closing of individual files may take longer than accessing from the database!
I'm curious if attaching to a separate database might have some advantages, but haven't yet found any articles on doing it.
https://sqlite.org/intern-v-extern-blob.html
https://sqlite.org/fasterthanfs.html#read_performance_measurements

Others don't agree storing images in tables (at least the main table.)
https://www.quora.com/Is-it-a-bad-design-to-store-images-as-blobs-in-a-database

Example getting local files without using slSetProcessMods "L0" or slGetFile to stay in remote mode and sent files from local machine.
http://sqlitening.com/support/index.php?topic=9675.0

#INCLUDE "sqlitening.inc"
'-------------------------------------------------------------------------------------------------
FUNCTION PBMAIN () AS LONG 
LOCAL sServer,sPathName,sFilename,sBlob AS STRING
slSetProcessMods "E0"

sServer   = "ip_or_machinename"
sPathName = "my_local_pictures\"
sFileName = "my.jpg"

slconnect sServer
IF slGetErrorNumber THEN ? sServer,%MB_SYSTEMMODAL,"Connect Error":EXIT FUNCTION

slOpen ("mydatabase.db3","C")
slexe "create table if not exists PictureTable(filename unique,blob)"

slSetProcessMods "L0"                    'switch to local processing
slGetFile sPathname + sFilename,sBlob    'get .jpg into a string sBlob$ from local disk
IF slGetErrorNumber = 0 THEN             'if no error
  WriteBlobRemote sFileName,sBlob         'switches to remote processing in SUB and inserts or updates Sqlite table
  GetBlobRemote  sFileName                'just testing (not needed) switches to remote processing in SUB and gets from SQlite table
ELSE
  ? slGetError,,sFilename
END IF

slSetProcessMods "L1" 'prevent possible slDisconnect crash if not in remote mode
slDisconnect
END FUNCTION
'-------------------------------------------------------------------------------------------------
SUB WriteBlobRemote(sFileName AS STRING,sBlob AS STRING)
LOCAL NumberOfChanges AS LONG
slSetProcessMods "L1"
NumberOfChanges = slGetChangeCount("T")
slexeBind "replace into PictureTable values('" + sFileName + "',?)",slBuildBindDat(sBlob)
IF slGetErrorNumber THEN
   ? slGetError,%MB_SYSTEMMODAL,"WriteBlobRemote"
   EXIT SUB
END IF
NumberOfChanges = slGetChangeCount("T") - NumberOfChanges
IF NumberOfChanges <> 1 THEN ? "Picture could not be written"
END SUB
'-------------------------------------------------------------------------------------------------
SUB GetBlobRemote(sFileName AS STRING)
slSetProcessMods "L1"
slSel "select filename,blob from PictureTable where filename ='"+sFilename + "'"
IF slGetErrorNumber THEN
  ? slGetError,,"Show Blob Remote"
  EXIT SUB
END IF
IF slGetRow THEN
  ? USING$("&   #, bytes",slf(1),LEN(slf(2))),%MB_SYSTEMMODAL,"Picture"
ELSE
  ? sFileName,,"Remote Blob Not Found"
END IF
END SUB

Fredrick Ughimi

Hello CJ,

Quote
Keeping track of the images in a directory by using a pointer or storing in the database?
I can't decide, but if storing remotely they will still have to be accessed in the remote location and use slGetFile.
I think I would try just putting in another table and if it slows things down go the pointer route.
Storing in a table would seem simplier and more efficient to me.


Might as well continue on this part until I complete the project.  If the images slows things down later I would go the pointer route.

What advantages would it present if images are stored in a different table?

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

Fredrick Ughimi

CJ,

Quote
By the way, the picture table above is already a separate table.


Yeah. I am thinking of the best way to link the images table with the main table. Use the image name or an IDNo.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

Quote
Note: Switching between local mode (L0) and remote mode (L1) can be eliminated using GET or GET$ instead of slGetFile reading local .jpg files.


http://sqlitening.com/support/index.php?topic=9675.0

Fredrick Ughimi

Hello CJ,

I have been able to adapt your code to my project:

This worked great while I am saving the records.


  'Pictures
        m_sPixNamePath = Exe.Path$ & "Pictures\"
        m_sPixName = VD_GetText (hfrmDPRRegistrationForm, %ID_FRMDPRREGISTRATIONFORM_TXTPICTURENAME)
        slSetProcessMods "L0"
        slGetFile m_sPixnamePath + m_sPixName, m_sPicture,"E1"
        slSetProcessMods "L1" 'prevent slDisconnect failing if a local error occurred   


But I am still getting error -8 - Access Denied while reporting.

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

cj

Reporting, done locally?  Did you return to local mode using slSetProcessMods "L0" before reporting?
Does the server and the local machine use the same version of SQLite?

%SQLitening_AccessDenied =  -8 - Access denied

cj

Insufficent code to see where you connect and open database and get image


  'Pictures
        m_sPixNamePath = Exe.Path$ & "Pictures\"
        m_sPixName = VD_GetText (hfrmDPRRegistrationForm, %ID_FRMDPRREGISTRATIONFORM_TXTPICTURENAME)
        slSetProcessMods "L0"
        slGetFile m_sPixnamePath + m_sPixName, m_sPicture,"E1"
       
       
        GetBlobRemote(m_sPicture)
        slSetProcessMods "L0"
        'process locally

        'slSetProcessMods "L1"
         slDisconnect

Fredrick Ughimi

CJ,

Quote
Reporting, done locally?  Did you return to local mode using slSetProcessMods "L0" before reporting?
Does the server and the local machine use the same version of SQLite?


Reporting is done remotely.

Yes. I returned to local mode using slSetProcessMods "L0" before reporting. Sorry, I get  - error 21. See attached.


Method ReportCertificateOfTrainingInAdvancedRiggingAndLiftingGearInspectionCourse(ByVal nCbHndl As Long) As Long
      Local LineSpacing!,iHandle%,Row!,TopMargin!,PageSize!,PageNumber&
      Local detailline As Asciiz * 200
      Local sBatchNo As String
      Local hpix As Long   
      Local sTempImageFile As String
      Local sTempSignatureFile As String
      Local hSignature As Long
      Local sBind As String
     
      LineSpacing = .2
      TopMargin   = 0
      PAGESIZE    = 11.4

      ihandle% = dpStartDoc(0,"Mega-Net CharkinERP - Print Certificate of Training in Advanced Rigging and Lifting Gear Inspection Course","",%DDOC_INCH, _
      %DDOC_PAPER_CUSTOM, %DDOC_PORTRAIT, %DDOC_SYSTEM_DEFAULT, %DDOC_BIN_AUTO Or %DDOC_ALLOWSMTP Or %DDOC_ALLOWSAVE Or %DDOC_ZOOMFIT)

      If ihandle < 1 Then
           MsgBox "Could not StartDoc. Error number" + Str$(Ihandle),%MB_TASKMODAL Or %MB_ICONINFORMATION,VD_App.Title
           Exit Method
      End If
     
      dpPaperSize ihandle%, 8.0,11.4
     
      ROW = PAGESIZE                            'force new heading
     
      ReportsInformation() 
           
     sBatchNo = VD_GetText(nCbHndl, %ID_FRMCERTIFICATEOFTRAININGINADVANCEDRIGGINGANDLIFTINGGEARINSPECTIONCOURSERPTDLG_TXTBATCHNO)
     
     slSetProcessMods "L0"

     slSEL "SELECT * FROM tblDPRRegistration WHERE BatchNo = '" + sBatchNo + "'" 
     
     Do While slGetRow()
        slSetProcessMods "L0"
        sTempImageFile=GuidTxt$(Guid$) + ".jpg"    'change 2
        slPutFile sTempImageFile, slFN("Picture"), "C"  '25th column 'change 3 
       
        sTempSignatureFile=GuidTxt$(Guid$) + ".jpg"    'change 2
        slPutFile sTempSignatureFile, slFN("Signature"), "C"  '27th column 'change 3
       
        If ROW => PAGESIZE Then
           If PageNumber Then      'If first page don't need a new page
              dpNewPage iHandle%, %DDOC_PAPER_CUSTOM, %DDOC_PORTRAIT, %DDOC_BIN_AUTO
           End If
           Incr PageNumber
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 20, %vbBlack, "Arial" 
           dpText IHandle%, 3.1,2.0, %DDOC_LEFT, "" + slFN("CertificateNo")
           dpText IHandle%, 2.9, 3.5, %DDOC_LEFT, "" + slFN("FirstName") & " " & slFN("Surname")  & " " &  slFN("Othernames")
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
           dpText IHandle%, 3.0,4.35, %DDOC_LEFT, "" + NigeriaDate(slFN("BirthDate"))
           dpText IHandle%, 5.7,8.6, %DDOC_LEFT, "" + NigeriaDate(slFN("Signature"))
           dpText IHandle%, 2.6,8.3, %DDOC_LEFT, "" + NigeriaDate(slFN("CourseEndDate"))
           hpix = dpAddGraphic(ihandle%, sTempImageFile+$Nul)
           Kill sTempImageFile  'change 5
           If hpix Then
              dpDrawGraphic ihandle%, hpix,5.9,6.9,7.1,8.2
           Else
            ? "Picture not found",%MB_TASKMODAL Or %MB_ICONINFORMATION,Exe.Name$
           End If   
           
           hSignature = dpAddGraphic(ihandle%, sTempSignatureFile+$Nul)
           Kill sTempSignatureFile  'change 5
           If hSignature Then
              dpDrawGraphic ihandle%, hSignature,4.1,6.8,5.6,7.0
           Else
            ? "Signature not found",%MB_TASKMODAL Or %MB_ICONINFORMATION,Exe.Name$
           End If
           
           ROW =  ROW + LineSpacing * 2
        End If
        ROW = ROW + LineSpacing                    'increment line counter
      Loop
      'slSetProcessMods "L1"
   dpEndDoc iHandle%, %DDOC_END_VIEW + %DDOC_END_PRINT

End Method     







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

Fredrick Ughimi

Quote
Insufficent code to see where you connect and open database and get image


I still use PwrDev IDE, so my codes are not one place.


'Connect/Open
sServer = "MegaNetSoftware"
   
    sPort = "Default"
   
    slConnect(sServer, Val(sPort))   
   
    sErr = slGetError
    If Val(sErr) <> 0 Then
         MsgBox sErr, %MB_TASKMODAL Or %MB_ICONINFORMATION, VD_App.Title
         Exit Function
    End If       
       
   slOpen ("CharkinERPDB.db3","C")

   Get Image:

        m_sPixNamePath = Exe.Path$ & "Pictures\"
        m_sPixName = VD_GetText (hfrmDPRRegistrationForm, %ID_FRMDPRREGISTRATIONFORM_TXTPICTURENAME)
        slSetProcessMods "L0"
        slGetFile m_sPixnamePath + m_sPixName, m_sPicture,"E1"
        slSetProcessMods "L1" 'prevent slDisconnect failing if a local error occurred   
       
        Errorcode& = slExeBind(slBuildInsertOrUpdate("tblDPRRegistration", "?" & $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(SQLiteDate(m_sDate), "T") & _
          slBuildBindDat(m_sBatchNo, "T") & _ 
          slBuildBindDat(m_sIDMeans, "T") & _
          slBuildBindDat(m_sIDNo, "T") & _
          slBuildBindDat(m_sFirstName, "T") & _
          slBuildBindDat(m_sSurname, "T") & _   
          slBuildBindDat(m_sOthernames, "T") & _
          slBuildBindDat(SQLiteDate(m_sCourseStartDate), "T") & _   
          slBuildBindDat(SQLiteDate(m_sCourseEndDate), "T") & _
          slBuildBindDat(SQLiteDate(m_sExpiryDate), "T") & _   
          slBuildBindDat(m_sCourseCode, "T") & _     
          slBuildBindDat(m_sEmailAddress, "T") & _   
          slBuildBindDat(m_sCertificateNo, "T") & _
          slBuildBindDat(m_sCorrAddress, "T") & _ 
          slBuildBindDat(SQLiteDate(m_sBirthDate), "T") & _ 
          slBuildBindDat(m_sBirthPlace, "T") & _ 
          slBuildBindDat(m_sSponsor, "T") & _ 
          slBuildBindDat(m_sDepartment, "T") & _ 
          slBuildBindDat(m_sEmployeeIDNo, "T") & _ 
          slBuildBindDat(m_sNationality, "T") & _ 
          slBuildBindDat(m_sOccupation, "T") & _ 
          slBuildBindDat(m_sPhoneNo, "T") & _ 
          slBuildBindDat(m_sKinName, "T") & _
          slBuildBindDat(m_sKinPhoneNo, "T") & _   
          slBuildBindDat(m_sPixName, "T") & _
          slBuildBindDat(m_sPicture, ("B")) & _
               

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

cj

Quote
Reporting is done remotely.

Yes. I returned to local mode using slSetProcessMods "L0" before reporting. Sorry, I get  - error 21. See attached.


The data is on the server, but where do you get it?
You are in local mode.


Fredrick Ughimi

Quote
The data is on the server, but where do you get it?


Here:

slSEL "SELECT * FROM tblDPRRegistration WHERE BatchNo = '" + sBatchNo + "'"
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

Quote
Is there a reason to keep requesting local routines?
slSetProcessMods "L0"

     slSEL "SELECT * FROM tblDPRRegistration WHERE BatchNo = '" + sBatchNo + "'" 
     
     Do While slGetRow()
        slSetProcessMods "L0"


I forgot to remove the second slSetProcessMods "L0". My mistake.

When I run the application with the correction made, I now get crash.


slSetProcessMods "L0"
slSEL "SELECT * FROM tblDPRRegistration WHERE BatchNo = '" + sBatchNo + "'"


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

Fredrick Ughimi

Quote
You are in local mode.


I am running the application on a remote mode.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet