MySQL Blob Column Jpg Write,read and display

[sanilpmc]

Hi,

Can somebody provide a sample code for writing and reading a jpg file to a blob field in MySQL using FWH Harbour

For writing to Blob filed, I tried

Code: Select all

cImageData:=MemoRead("D:\Test.Jpg")  // Read the Jpj file contents to a variable
cImageData:=cMimeEnc(cImageData)

Update the table's blob field with cImageData

To display the Image from the database, I prefer to read the blob field content directly into memory variable (ie without writing the blob contents to hard disk, to create a jpg file) and then display the image on the IMAGE control placed on my Dialog using oImage:LoadFromMemory(cImageData)

For reading and displaying on IMAGE Control, I use the following code.

Code: Select all

@ 0, 0 IMAGE oImage SIZE 150, 150 OF oDlg

Code: Select all

cImageData:=oRecSet:Fields("MyBlobColumn"):Value
cImageData:=cMimeDec(cImageData)

oImage:LoadFromMemory(cImageData)
oImage:Refresh()

But nothing displayed.

I have also tried HexToStr() and StrToHex() to write and read from MySQL Blob field but not successful
I have also tried fMimeEnc() and fMimeDec() to write and read from MySQL Blob field but not successful

Searched the forum and could not find any working solution.

TIA

[Jack]

I can't help you with this blob field .

Could you post a sample of your connection string ?
Did you use standard ADO ?

Thanks .

[sanilpmc]

I use ADO

My connection string

Code: Select all

cConnectSring:="Driver={MySQL ODBC 5.1 Driver};Server=192.168.0.110;Port=3306;;Database=pghr;User=root;Password=MyPassword;Option=3;"
oConnection:=CreateObject("ADODB.Connection")

oConnection:ConnectionString:=cConnectSring
    
CursorWait()

TRY
  oConnection:Open()
CATCH oError
  CursorArrow()
  MsgInfo("Failed to Connect to the Database ")
  ShowSqlError(oError)
  RETURN .F.
END

By the way my JPG files which I am trying to store on MySQL Blob column are only less than 10kb in size

[sanilpmc]

The problem occurs only when used with Harbour. With xHarbour it is working fine.

To write jpg file data to a Blob column in MySQL Table, I use the following code. This code is working fine

Code: Select all

cImageData:=MemoRead("D:\MyImage.Jpg")
cImageData:=cMimeEnc(cImageData)

// Update MySQL Blob column with cImageData

To read and display the Image file data from the Blob column, I use the following code. Unfortunately read and display code works fine in xHarbour and fails in Harbour.

Code: Select all

cImageData:=oRecSet:Fields("MyPhoto"):Value
cImageData:=cMimeDec(cImageData)

// Image Control
oImage:LoadFromMemory(cImageData)
oImage:Refresh()

I checked using cMimeEnc() and cMimeDec() with both Harbour and xHarbour and found that both these functions are working fine as expected.

Any idea what is wrong with the data retrieval from blob column using Harbour. ?

I don't have plans to use xHarbour for my project

[sanilpmc]

Code: Select all

// Blob Column. Data stored after cMimeEnc()
cStrData = oRecSet:Fields("Photo"):Value  

MsgInfo( ValType(cStrData) )
 

In Harbour the Valtype is returned as "A", whereas in xHarbour the valtype is "C"
The contents of the array (Harbour) are just numbers, where as in xHarbour it is a string of junk characters

This is the reason that the image is not displayed when using Harbour

Anybody any idea why is this difference in Harbour and xHarbour when the data is retrieved from a Blob Column. Any idea how to handle this case ?. Anybody here experienced similar problem ?

The original Jpg image size is less than 10kb in size

Harbour and xHarbour version used
Harbour ver 3.1.0dev (Rev. 17346)
xHarbour build 1.2.1 (SimpLex) (Rev. 9445)

[Rick Lipkin]

Do not know if this process will help you .. I write and retrieve documents to a SQL database and this method works for both MS Access as well as MS Sql Server ..

Here is the document ( any binary file ) retrieval for xHarbour .. do not know if it will work for Harbour ?

I store the number of bytes of the file to the field 'datalen' and the name of the original file in 'filename' , the binary data in 'charter',

This works for datatypes varbinary or ole object. I have a similar routine to store the binary file to a SQL table if you need it.

Rick Lipkin

Code: Select all

//-------------------------------
Static Func _Viewum( oRsCh, oPROJ, oDLG )

LOCAL nHANDLE, cREAD, cFILENAME

cFILENAME := alltrim(oRsCh:Fields("filename"):Value )

cREAD := oRsCh:Fields("charter"):GetChunk( oRsCh:Fields("datalen"):Value)
FERASE( xVOL+"\DBTMP\"+cFILENAME )

nHANDLE := FCREATE(  xVOL+"\DBTMP\"+cFILENAME, 0 )
IF FERROR() <> 0
   SAYING := "Error Creating file "+(xVOL+"\DBTMP\"+cFILENAME)+CHR(10)
   SAYING += "Error "+STR(FERROR())+CHR(10)
   MsgInfo( SAYING )
   RETURN(.F.)
ENDIF

FWRITE( nHANDLE, cREAD )   // write out the file
FCLOSE( nHANDLE )


SysReFresh()

SHELLEXECUTE( "", "open", (xVOL+"\DBTMP\"+cFILENAME),"","",1)

SysReFresh()

RETURN(.T.)

[sanilpmc]

Dear Rick,

Thanks for the support.

I tried your code. It is working fine with xHarbour. Unfortunately not working with Harbour. Harbour returns the valtype of Blob column as array. I wonder why nobody has not noticed this behavior in Harbour.

I had to make a slight change in your code to make the picture fully visible while reading from table and displaying

For your info
Column File_Data is the BLOB column which stores the contents of JPG image file. I write to BLOB column after cMimeEnc()
Column File_Size store the file size of the Image file

Rick's code

Code: Select all

cImageData:=oRecSet:Fields("File_Data"):GetChunk( oRecSet:Fields("File_Size"):Value)

Changed to

Code: Select all

cImageData:=oRecSet:Fields("File_Data"):GetChunk( Len(oRecSet:Fields("File_Data"):Value))

For your information. The following code is also working fine with xHarbour ie you don't have to use GetChunk

Code: Select all

cImageData:=oRecSet:Fields("File_Data"):Value
cImageData:=cMimeDec(cImageData)

I have a similar routine to store the binary file to a SQL table if you need it.

Would you mind sharing the code that you use to write image file data to the Table. Just wanted to confirm whether anything wrong in the way/technique that I use to write image file contents to the blob column. Anyhow, I am able to read the image data using xHarbour and the problem occurs only when using Harbour, so I assume that there is no mistake in the table writing part.

Would you mind testing it with Harbour.

I can create a sample prg for you. You just have to create a table with the following column

Table : BlobTest
Column File_Name Char(50)
Column File_Size Int
Column File_Data LongBlob

TIA

[Rick Lipkin]

Here you have the Binary file import .. I would suspect it is specific to xHarbour.

oRsCh:Fields("charter"):AppendChunk( VTArrayWrapper():New( 17, cBUFFER ) )

Rick

Code: Select all

//------------------------------
Static Func _Doit( oRsProj, oRsCh, cPROJECTEID, cType )

LOCAL cFILENAME, nSTRING, nLEN, nSTART, SAYING, nDATETIME
LOCAL nHANDLE, nBYTES, cEID, cFILE, dDATE

LOCAL cBUFFER   //cBinaryData        // <------- This is the actual data to be stored
LOCAL nBytesRead

cFILE := ALLTRIM( cPATH )     // C:\DBTMP\CHARTER.DOC
nLEN  := LEN( cFILE )

nSTART := RAT( "\", cFILE )

IF nSTART > 0
ELSE
   SAYING := "INVALID File name or Location .. Aborting"
   MsgInfo( SAYING )
   oDLG:END()
   RETURN(.F.)
ENDIF

// get file name //
cFILENAME := SUBSTR( cPATH, nSTART+1, nLEN-nSTART )    // CHARTER.PDF

IF LEN(cFILENAME) > 35
   SAYING := "Sorry .. the maximum length of your file"+chr(10)
   SAYING += cFILENAME+CHR(10)
   SAYING += "is longer than 35 characters. Please re-name"+chr(10)
   SAYING += "your file to meet the 35 max length"+chr(10)
   MsgInfo( saying )
   oDlg:end()
   RETURN(.F.)
ENDIF


// open file //
nHANDLE := FOpen( cFILE )
IF FERROR() <> 0
   SAYING := "Error reading file "+cFILE+CHR(10)
   SAYING += " "+STR(FERROR())+CHR(10)
   MsgInfo( SAYING )
   oDLG:END()
   RETURN(.F.)
ENDIF

// get number of bytes in file
nBYTES := FSEEK( nHANDLE, 0,2 )

// pad the buffer nBytes+1
cBUFFER := SPACE(nBYTES+1)

FSeek( nHANDLE, 0, 0 )
nBytesRead   := FRead( nHANDLE, @cBuffer, nBytes )

FClose( nHANDLE )

if nBytesRead != nBytes
   SAYING := "nBytesRead = "+str(nBYTESREAD)+CHR(10)
   SAYING += "nBytes     = "+str(nBYTES)+CHR(10)
   SAYING += "Error Reading Data"+chr(10)
   MsgInfo( saying )
   oDLG:END()
   RETURN ( .F. )
endif

cEID := _GenEid()
IF cEID = "BOGUS"
   oDlg:End()
   RETURN(.F.)
ENDIF

nDateTime := dtoc(date())+" "+time()

oRsCh:AddNew()

oRsCh:Fields("chartereid"):Value    := cEID
oRsCH:Fields("projecteid"):Value    := cPROJECTEID
oRsCh:Fields("date_imported"):Value := nDateTime
oRsCh:Fields("imported_by"):Value   := xLOGIN
oRsCh:Fields("datalen"):Value       := nBYTES
oRsCh:Fields("filename"):Value      := cFILENAME
oRsCh:Fields("IMPORTTYPE"):Value    := cType
oRsCh:Fields("charter"):AppendChunk( VTArrayWrapper():New( 17, cBUFFER ) )

oRsCh:Update()

SysReFresh()

SAYING := "Bytes Read   = "+str(nBYTESREAD)+CHR(10)
SAYING += "Bytes Stored = "+str(nBYTES)+CHR(10)
SAYING += "  "+CHR(10)
SAYING += "Upload Complete for file name "+cFILENAME+chr(10)
MsgInfo( saying )

oRsCh:Sort("date_imported")
oRsCh:MoveFirst()
oRsCh:Find( "chartereid = '"+cEID+"'" )

oDLG:END()
RETURN(.T.)

[sanilpmc]

Dear Rick,

Thanks for the support. But the result is same.It is working fine with xHarbour. Unfortunately not working with Harbour.

TIA