Announcement

**Fredrick Ughimi** · 8 Jun 2017, 05:46 PM

I supposed there must be a way to retrieve an image from an Sqlite Table.

**Michael Mattias** · 8 Jun 2017, 05:59 PM

I supposed there must be a way to retrieve an image from an Sqlite Table.

It depends on the format in which the image is stored. Since I'm not an SQLite guy, I don't know about "image" data types for columns... and if it's not some kind of "IMAGE" datatype (proprietary to the Sqlite product), then you'll need to know even more.

My original gut feel is, the actual data column is likely some kind of LONG BINARY (raw data) field, and represents some standard image format such as BMP, JPEG or PNG. "IF" that is the case I'd probably try to save the contents to a disk file, and then use dpAddGraphic against that file.

BUT...

All that said. ..your existing code does not get the image from a database column... it gets it from the disk file 'VD_App.Path & "Ughimi.jpg"'

Lastly.. there may be some proprietary function in the "VD" development product, but since that product is no longer supported I'd probably exclude any such proprietary function from consideration.

MCM

**Fredrick Ughimi** · 8 Jun 2017, 07:20 PM

Hello MCM,

>>It depends on the format in which the image is stored. Since I'm not an SQLite guy, I don't know about "image" data types for columns... and if it's not some kind of "IMAGE" datatype (proprietary to the Sqlite product), then you'll need to know even more.

The image is stored in the Blob format. What I want to achieve is to read back the stored image.

**Stuart McLachlan** · 8 Jun 2017, 10:56 PM

Once you have the BLOB in memory as a string, you should be able to use parts of this post to convert to a PB bitmap.

"PowerBASIC "universal" functions to decode a memory-buffered image file to a PB bitmap, and to encode a PB bitmap to a memory-buffered image file"
' Uses a memory-buffered file image for input and output (shifting the ' responsibility for reading and writing the file to the caller) for ' flexibility of source and destination without requiring separate ' functions for different applications. The image file to be decoded ' can be from disk, the program's resource, or from within another ' container file, such as a database or a ZIP archive. Likewise for ' encoder output.

**Fredrick Ughimi** · 10 Jun 2017, 10:00 AM

Thank you Stuat, for your response and the link. Looking at it.

**Mike Doty** · 19 Jun 2017, 09:38 AM

I looked at the ddoc source and dpAddGraphic and dpDrawGraphic (in ddoc_gra.bas) are using a temp file and the author mentions someday perhaps a better way.
It looks like someone with graphic abilities could modified a function and copy to a new one to eliminate the need for the temp file. but I think a shell is also involved to ddoc engine to read it.

As Michael Mattias mentioned a file is loaded with dpAddGraphic then written to page using dpDrawgraphic
In local mode it is probably fast enough with disk cacheing, but eliminating the temp file (which must be unique) would be a good thing.
The temp file routine in ddoc uses a seed and other code, but might be replaced with a unique file routine from windows.
The good news it will work now, but needs a unique temp file.
If the database is remote the reading/writing of the ddoc must switch to local mode after reading the remote data
Maybe a graphics expert knows how to get rid of the temp file.

**Fredrick Ughimi** · 30 Jun 2017, 03:41 PM

Hello Mike,

As Michael Mattias mentioned a file is loaded with dpAddGraphic then written to page using dpDrawgraphic

I have no problem implementing that. The thing is, I am printing a Picture and a Name on the same page and can't figure out how to correctly match the appropriate Picture with the Name if the images are stored in disk file. I intend printing multiple pages at a go using a BatchNo.

Any ideas? Thank you for your response.

Best regards,

**Michael Mattias** · 30 Jun 2017, 04:12 PM

The thing is, I am printing a Picture and a Name on the same page and can't figure out how to correctly match the appropriate Picture with the Name if the images are stored in disk file. I intend printing multiple pages at a go using a BatchNo.

HUH?

If that is your challenge/opportunity/issue, then that has nothing to do with ddoc or SQLitening or any other product or PowerBASIC language feature.

Without the DB schema I'm guessing but from your select..

Code:

SELECT RegistrationNo, PixName, Picture FROM tblDPRRegistration...

.. I am guessing that somewhere in your DB you can relate RegistrationNo to Name.

In which case all you have to do is.. (pseudo-code, very handy for problems like this )....

Code:

Select list of names from DB
FOR EACH  NAME IN LIST
    Select picture_data from DB
    Save Picture_data to file
    dpAddGraphic  (file)
    KILL file   ' done with it
    ' print the page
    dpDrawGraphic
    dpText (name)
    dpNewPage
NEXT

As long as you print one at a time like this (or at least dpAddGraphic one at a time) you are not going to get names mixed up with picture data.

Unless the "PixName" column is the name associated with the Picture in each row?

Code:

LOCAL wPixName, wPicture AS STRING
Select PixName, Picture from table order by whatever (PixName?)
 DO
     Fetch next row from DB_result_set   INTO  wPixName, wPicture
     Save wPicture to file
     dpAddGraphic  file
     KILL file  ' done woth it
     DpDrawGraphic (this one)
     dpText  wPixName
  LOOP Until no more rows in result set

You really don't need to do anything else as you have described the problem.

I must be missing something.

MCM

**Fredrick Ughimi** · 1 Jul 2017, 05:51 PM

Hello MCM,

Thank you for your response.

I must be missing something.

I guess so.

Here is where I am having issues. Reading the disk file in DDOC.

Code:

#COMPILE EXE
#DIM ALL

#INCLUDE "sqlitening.inc"
#INCLUDE "DDOC_P32.INC"

GLOBAL sFileName AS STRING

%vbBlack = &H0&

FUNCTION PBMAIN () AS LONG
  LOCAL sPicture, sPixNamePath, sPixName, sCertificateNo, sName, sBatchNo AS STRING
  LOCAL Errorcode&

  sCertificateNo = "309"
  sName = "Mark Ogbe"
  sBatchNo = "122"
  sFileName = "PictureX"

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

  slExe BUILD$("Create Table If Not Exists tblDPRReg(CertificateNo TEXT, Name TEXT, BatchNo TEXT, PixName TEXT, Picture BLOB)")
  slExe "Create UNIQUE Index If Not Exists DPRRegndx ON tblDPRReg(CertificateNo)"

  sPixName = "Ughimi.jpg"

  sPixNamePath = "C:\Charkin ERP\Pictures\" & sPixName

   slGetFile sPixNamePath, sPicture, "E1" '9216 bytes

        Errorcode& = slExeBind(slBuildInsertOrUpdate("tblDPRReg", "?" & $NUL & "?" & $NUL & "?" & $NUL & "?" & $NUL & "?"), _
          slBuildBindDat(sCertificateNo, "T") & _
          slBuildBindDat(sName, "T") & _
          slBuildBindDat(sBatchNo, "T") & _
          slBuildBindDat(sPixName, "T") & _
          slBuildBindDat(sPicture, ("B")),"E")

          slPutFile sFileName, sPicture, "C"

          ReportCertificate()

END FUNCTION

FUNCTION ReportCertificate() AS LONG
      LOCAL LineSpacing!,iHandle%,Row!,TopMargin!,PageSize!,PageNumber&
      LOCAL detailline AS ASCIIZ * 200
      LOCAL SNo AS LONG
      LOCAL sBatchNo AS STRING
      LOCAL hpix AS LONG

      LineSpacing = .2
      TopMargin   = .5
      PAGESIZE    = 10

      ihandle% = dpStartDoc(0,"Mega-Net CharkinERP - Certificate Report","",%DDOC_INCH, %DDOC_PAPER_A4, %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)
           GOTO EndProgram
      END IF

      dpSetTabs iHandle, "L.5W1 L1.1W1 L2.5W4 L5.5W2"

      ROW = PAGESIZE                            'force new heading

      sBatchNo = "122"

     slSel "SELECT * FROM tblDPRReg WHERE BatchNo = '" + sBatchNo  + "'"

     DO WHILE slGetRow()

        IF ROW => PAGESIZE THEN
           IF PageNumber THEN      'If first page don't need a new page
              dpNewPage iHandle%,  %ddoc_PAPER_A4, %DDOC_PORTRAIT, %DDOC_BIN_AUTO
           END IF
           INCR PageNumber
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 20, %vbBlack, "Arial"

           dpText IHandle%, 4.25, 4.6, %DDOC_CENTER, "" + slFN("Name")

           hpix = dpAddGraphic(ihandle%, EXE.PATH$ & "PictureX.dat") ' [B][COLOR=#FF0000]This is where I am having issues[/COLOR][/B]
           IF hpix THEN
              dpDrawGraphic ihandle%, hpix,6.4,6.1,7.8,11
           END IF
           'dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 10, %vbBlack, "Arial"
           'dpTabText iHandle%, Row + .7, "S/No" + $Tab + "CourseCode" + $Tab + "Course" + $Tab + "Amount"
           dpFont ihandle%, %DDOC_FONTNORMAL, 9, %vbBlack, "San Serif"
           ROW =  ROW + LineSpacing * 2
        END IF

        ROW = ROW + LineSpacing                    'increment line counter

      LOOP

      dpEndDoc iHandle%, %DDOC_END_VIEW + %DDOC_END_PRINT
    EndProgram:
END FUNCTION

Attached Files

**Mike Doty** · 1 Jul 2017, 07:16 PM

hpix = dpAddGraphic(ihandle%, EXE.PATH$ & sFilename
hpix = dpAddGraphic(ihandle%, EXE.PATH$ & "PictureX.dat") ' This is where I am having issues
The .jpg file extension is also required sFileName = "PictureX.jpg" instead of "PictureX"

**Mike Doty** · 1 Jul 2017, 07:47 PM

I changed paths a bit to my \sql\bin folder and everything worked.

Code:

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

GLOBAL sFileName AS STRING
%vbBlack = &H0

FUNCTION PBMAIN AS LONG
 LOCAL sPicture, sPixNamePath, sPixName, sCertificateNo, sName, sBatchNo AS STRING
 LOCAL Errorcode&
 sCertificateNo = "309"
 sName = "Mark Ogbe"
 sBatchNo = "122"
 sFileName = "PictureX.jpg"
 slOpen ("CharkinDB.db3","C")

  slExe BUILD$("Create Table If Not Exists tblDPRReg(CertificateNo TEXT, Name TEXT, BatchNo TEXT, PixName TEXT, Picture BLOB)")
  slExe "Create UNIQUE Index If Not Exists DPRRegndx ON tblDPRReg(CertificateNo)"

  sPixName = "Ughimi.jpg"

  sPixNamePath = "C:\sql\bin\" & sPixName

  slGetFile sPixNamePath, sPicture, "E1" '9216 bytes

  Errorcode& = slExeBind(slBuildInsertOrUpdate("tblDPRReg", "?" & $NUL & "?" & $NUL & "?" & $NUL & "?" & $NUL & "?"), _
          slBuildBindDat(sCertificateNo, "T") & _
          slBuildBindDat(sName, "T") & _
          slBuildBindDat(sBatchNo, "T") & _
          slBuildBindDat(sPixName, "T") & _
          slBuildBindDat(sPicture, ("B")),"E")

          slPutFile sFileName, sPicture, "C"


          ReportCertificate()
END FUNCTION

FUNCTION ReportCertificate() AS LONG
      LOCAL LineSpacing!,iHandle%,Row!,TopMargin!,PageSize!,PageNumber&
      LOCAL detailline AS ASCIIZ * 200
      LOCAL SNo AS LONG
      LOCAL sBatchNo AS STRING
      LOCAL hpix AS LONG

      LineSpacing = .2
      TopMargin   = .5
      PAGESIZE    = 10

      ihandle% = dpStartDoc(0,"Mega-Net CharkinERP - Certificate Report","",%DDOC_INCH, %DDOC_PAPER_A4, %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)
           GOTO EndProgram
      END IF

      dpSetTabs iHandle, "L.5W1 L1.1W1 L2.5W4 L5.5W2"

      ROW = PAGESIZE                            'force new heading

      sBatchNo = "122"

     slSel "SELECT * FROM tblDPRReg WHERE BatchNo = '" + sBatchNo  + "'"

     DO WHILE slGetRow()

        IF ROW => PAGESIZE THEN
           IF PageNumber THEN      'If first page don't need a new page
              dpNewPage iHandle%,  %ddoc_PAPER_A4, %DDOC_PORTRAIT, %DDOC_BIN_AUTO
           END IF
           INCR PageNumber
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 20, %vbBlack, "Arial"

           dpText IHandle%, 4.25, 4.6, %DDOC_CENTER, "" + slFN("Name")

           hpix = dpAddGraphic(ihandle%, EXE.PATH$ & sFileName) ' This is where I am having issues
           IF hpix THEN
              dpDrawGraphic ihandle%, hpix,6.4,6.1,7.8,11
           END IF
           'dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 10, %vbBlack, "Arial"
           'dpTabText iHandle%, Row + .7, "S/No" + $Tab + "CourseCode" + $Tab + "Course" + $Tab + "Amount"
           dpFont ihandle%, %DDOC_FONTNORMAL, 9, %vbBlack, "San Serif"
           ROW =  ROW + LineSpacing * 2
        END IF

        ROW = ROW + LineSpacing                    'increment line counter

      LOOP

      dpEndDoc iHandle%, %DDOC_END_VIEW + %DDOC_END_PRINT
    EndProgram:
END FUNCTION

**Fredrick Ughimi** · 1 Jul 2017, 09:21 PM

Hello Mike,

This line:

Code:

sFileName = "PictureX.jpg"

Is actually referring to the disk file:

Code:

sFileName = "PictureX.dat"

Does it has to PictureX.jpg? Anyway, I would try to put multiple pictures in PictureX.jpg and see how it goes tomorrow morning.

Thank you Mike.

**Mike Doty** · 1 Jul 2017, 09:26 PM

hpix = dpAddGraphic(ihandle%, EXE.PATH$ & sFileName) ' has to be a single image file, not a bunch of files.

**Mike Doty** · 2 Jul 2017, 12:46 AM

The pictures are in the table not PictureX.jpg as the logic is to extract them using Select * from tblDPRReg where BatchNo = '122'
The pictures could be saved as individual files and pointed to from the table or all the pictures placed into one big file and the startbyte and length would be needed.

Code:

#INCLUDE "sqlitening.inc"
#INCLUDE "DDOC_P32.INC"
%DropTable = 1
$TempImageFile = "TempImageFile.jpg" 'must end .jpg
%vbBlack = &H0

FUNCTION PBMAIN AS LONG
 LOCAL sPicture, sPixNamePath, sFileName,sPixName, sCertificateNo, sName, sBatchNo AS STRING
 LOCAL Errorcode&, x AS LONG

 slOpen ("CharkinDB.db3","C")
 IF %DropTable THEN slexe "drop table if exists tblDPRREG"
 slExe "Create Table If Not Exists tblDPRReg(CertificateNo TEXT, Name TEXT, BatchNo TEXT, PixName TEXT, Picture BLOB)"
 slExe "Create UNIQUE Index If Not Exists DPRRegndx ON tblDPRReg(CertificateNo)"

 FOR x = 1 TO 3  'insert records into sqlite database
  sPixName = "Ughimi.jpg"
  sPixNamePath = "C:\sql\bin\" & sPixName
  sCertificateNo = "CerticateNo" + STR$(x)
  sName = "Name" + STR$(x)
  sBatchNo = "122"
  sFileName = "PictureX.jpg"
  slGetFile sPixNamePath, sPicture '21,681 bytes
  Errorcode& = slExeBind("Insert into tblDPRReg values(?,?,?,?,?)",_
               slBuildBindDat(sCertificateNo, "T") & _
               slBuildBindDat(sName, "T") & _
               slBuildBindDat(sBatchNo, "T") & _
               slBuildBindDat(sPixName, "T") & _
               slBuildBindDat(sPicture, ("B")),"E")
  NEXT

  ReportCertificate()
END FUNCTION

FUNCTION ReportCertificate() AS LONG
      LOCAL LineSpacing!,iHandle%,Row!,TopMargin!,PageSize!,PageNumber&
      LOCAL detailline AS ASCIIZ * 200
      LOCAL SNo AS LONG
      LOCAL sBatchNo AS STRING
      LOCAL sBuffer AS STRING
      LOCAL hpix AS LONG
      LineSpacing = .2
      TopMargin   = .5
      PAGESIZE    = 10
      ihandle% = dpStartDoc(0,"Mega-Net CharkinERP - Certificate Report","",%DDOC_INCH, %DDOC_PAPER_A4, %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),,FUNCNAME$
           EXIT FUNCTION
      END IF
      dpSetTabs iHandle, "L.5W1 L1.1W1 L2.5W4 L5.5W2"
      ROW = PAGESIZE                            'force new heading
      sBatchNo = "122"
      slSel "SELECT * FROM tblDPRReg WHERE BatchNo = '" + sBatchNo  + "'"
      DO WHILE slGetRow()
        slPutFile $TempImageFile,slf(5), "C"  '5th column
        IF ROW => PAGESIZE THEN
           IF PageNumber THEN      'If first page don't need a new page
              dpNewPage iHandle%,  %ddoc_PAPER_A4, %DDOC_PORTRAIT, %DDOC_BIN_AUTO
           END IF
           INCR PageNumber
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 20, %vbBlack, "Arial"
           dpText IHandle%, 4.25, 4.6, %DDOC_CENTER, "" + slFN("Name")
           hpix = dpAddGraphic(ihandle%, $TempImageFile) ' This is where I am having issues
           IF hpix THEN
              dpDrawGraphic ihandle%, hpix,6.4,6.1,7.8,11
           ELSE
            ? "image not found",%MB_SYSTEMMODAL,FUNCNAME$
           END IF
           dpFont ihandle%, %DDOC_FONTNORMAL, 9, %vbBlack, "San Serif"
           ROW =  ROW + LineSpacing * 2
        END IF
        ROW = ROW + LineSpacing                    'increment line counter
      LOOP
      dpEndDoc iHandle%, %DDOC_END_VIEW + %DDOC_END_PRINT
END FUNCTION

**Mike Doty** · 2 Jul 2017, 01:29 AM

Binding is suggested to prevent injection so this is a better select statement
Please read comments in the docs about slSelBind

LOCAL sBind AS STRING '
sBind = slBuildBindDat(sBatchNo,"T") 'use a separate variable (do not put on stack per docs)
slSelBind "SELECT * FROM tblDPRReg WHERE BatchNo =?",sBind
DO WHILE slGetRow()

**Fredrick Ughimi** · 2 Jul 2017, 09:32 AM

Hello Mike,

The pictures are in the table not PictureX.jpg as the logic is to extract them using Select * from tblDPRReg where BatchNo = '122'

Yes. You are right.

Just some clarification. In your last code.

Code:

 
 $TempImageFile = "TempImageFile.jpg" 'must end .jpg

And then

Code:

 
 sFileName = "PictureX.jpg"

And then

Code:

 
 hpix = dpAddGraphic(ihandle%, $TempImageFile) ' This is where I am having issues

I am getting a bit confused yet again, just when I thought I was geting a hang of it.

But your last code worked as well.

**Fredrick Ughimi** · 2 Jul 2017, 09:34 AM

Binding is suggested to prevent injection so this is a better select statement
Please read comments in the docs about slSelBind

LOCAL sBind AS STRING '
sBind = slBuildBindDat(sBatchNo,"T") 'use a separate variable (do not put on stack per docs)
slSelBind "SELECT * FROM tblDPRReg WHERE BatchNo =?",sBind
DO WHILE slGetRow()

Thanks for the advice, Mike.

**Michael Mattias** · 2 Jul 2017, 10:01 AM

RE:

Code:

 
 $TempImageFile = "TempImageFile.jpg" 'must end .jpg

This is from the source for the dpaddgraphic function (in DDOC_GRA.BAS)

Code:

'- Create temporary bitmap to hold the jpg
   '
   bmpFile = ""
   if (right$(lcase$(sText), 3) = "jpg") or _
         (right$(lcase$(sText), 4) = "jpeg") then
      hLib = loadLibrary("ddoc_jpg")
      if hLib = 0 then
         function = %false
         exit function
      end if
      pFunction = getProcAddress(hLib, "jpg2bmp")
      if pFunction = 0 then
         function = %false
         exit function
      end if

The conversion is never made if the file has an extension "dat" (or anything other than "jpg" or "jpeg" )

I hope this partly explains my general dislike of inline literals.

**Fredrick Ughimi** · 2 Jul 2017, 10:17 AM

MCM,

The conversion is never made if the file has an extension "dat" (or anything other than "jpg" or "jpeg" )

Thanks for the clarification

Announcement

Embed Image in DDOC from SQLitening

Embed Image in DDOC from SQLitening

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment