Page 1 of 1
MySQL Blob Column Jpg Write,read and display
Posted: Sun May 27, 2012 7:45 am
by 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
Re: MySQL Blob Column Jpg Write,read and display
Posted: Sun May 27, 2012 8:17 am
by 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 .
Re: MySQL Blob Column Jpg Write,read and display
Posted: Sun May 27, 2012 8:29 am
by 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
Re: MySQL Blob Column Jpg Write,read and display
Posted: Mon May 28, 2012 9:13 am
by 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
Re: MySQL Blob Column Jpg Write,read and display
Posted: Tue May 29, 2012 6:26 am
by 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)
Re: MySQL Blob Column Jpg Write,read and display
Posted: Tue May 29, 2012 12:30 pm
by 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.)
Re: MySQL Blob Column Jpg Write,read and display
Posted: Wed May 30, 2012 6:20 am
by 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)
Rick Lipkin wrote: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
Re: MySQL Blob Column Jpg Write,read and display
Posted: Wed May 30, 2012 12:28 pm
by 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.)
Re: MySQL Blob Column Jpg Write,read and display
Posted: Mon Jun 04, 2012 5:58 am
by sanilpmc
Dear Rick,
Thanks for the support. But the result is same.It is working fine with xHarbour. Unfortunately not working with Harbour.
TIA