HMGforum.com

Posted: **Fri Jan 13, 2012 7:49 pm**

Hello Guys need to write a file type jpeg image in a field of a mysql table and then have to view this image, what query should I use to do what I need.

[u]Moderator Notes[/u] (Pablo César) wrote:Topic moved from Forum en Español.

Posted: **Sat Jan 14, 2012 6:08 am**

There are two ways to achieve this.

1. Blob method:

Have a field as type blob.

buff := sqlite3_file_to_buff( "pngtest.png" )

This is to convert file into buffer.

For reverting back,

sqlite3_buff_to_file( "pngtest1.png", @buff )

This way you can store the image, directly into MySQL (like sqlite).

2. File method:

If your image database is very big, then it is advisable to have a separate directory for images and keep only the names of the image files in the database.

Posted: **Sat Jan 14, 2012 2:52 pm**

Hi Rathi

Useful info, thanks

If I'm not wrong, M type of DBFNTX system too can hold binary large objects; like images etc

Does anyone know; is there a similar or equivalent of these file_to_buff() and buff_to_file() functions in Harbour ?

Regards

--

Esgici

Posted: **Sat Jan 14, 2012 6:03 pm**

There is a way to make some functions instead of mysql sqlite_3

Posted: **Sun Jan 15, 2012 2:02 am**

I need to put the picture of an object image in buff
main.image_1.picture: = buff

rathinagiri wrote:There are two ways to achieve this.

1. Blob method:

Have a field as type blob.

buff := sqlite3_file_to_buff( "pngtest.png" )

This is to convert file into buffer.

For reverting back,

sqlite3_buff_to_file( "pngtest1.png", @buff )

This way you can store the image, directly into MySQL (like sqlite).

2. File method:

If your image database is very big, then it is advisable to have a separate directory for images and keep only the names of the image files in the database.

Posted: **Sat Jan 21, 2012 10:14 pm**

what type of field can use with this buff, blob / text / char / binary, I tried to write to a BLOB field and giving this error.

rathinagiri wrote:There are two ways to achieve this.

1. Blob method:

Have a field as type blob.

buff := sqlite3_file_to_buff( "pngtest.png" )

This is to convert file into buffer.

For reverting back,

sqlite3_buff_to_file( "pngtest1.png", @buff )

This way you can store the image, directly into MySQL (like sqlite).

2. File method:

If your image database is very big, then it is advisable to have a separate directory for images and keep only the names of the image files in the database.

Posted: **Sat Feb 25, 2012 4:17 pm**

Function tested with mysql and MariaDB works perfectly with images.

if anyone knows how to convert to harbor language and add Harbou as a function of the code is below.

Code: Select all

*******************************************************************************************************************
Function File_To_Buff(cFile)
*******************************************************************************************************************
local cBuff:="", fh, nLen
local lRetVal:=.f.
local cFile_in := cFile

/*
  Write file 'Test.gif' to MySQL and MariaDB table 'test.blobtest.blobfield'
*/

fh:=fopen(cFile_in,0)

if fh > -1
  // determine length of file
  nLen := fseek(fh, 0, 2)
  if nLen > 0

    //move file pointer back to begin of file
    fseek(fh, 0, 0)
    cBuff:=space(nLen)
    fread(fh, @cBuff, nLen)
        
    // escapes
    cBuff:=strtran(cBuff, chr(92), "\\")
    cBuff:=strtran(cBuff, chr(0), "\0")
    cBuff:=strtran(cBuff, chr(39), "\'")
    cBuff:=strtran(cBuff, chr(34), '\"')
  endif  
Else
	cBuff := "OFF"        
endif
      
fclose(fh)

Return(cBuff)


*******************************************************************************************************************
Function Buff_To_File(cBuff)
*******************************************************************************************************************
local fh, nLen
local lRetVal:=.f.
Local cFile_out := ""

/*
  Read Blobfield from MySQL and MariaDB table 'test.blobtest' and save it to file 'Test2.gif'
*/

nNFile := INT(RANDOM()%999 +1)
cFile_out := "Foto" + Alltrim(STR(nNFile)) + ".tmp"
cDir_Temp := BaseDeDados("DIR23")

IF !File(cDir_Temp + cFile_out)
    fh := fcreate(cDir_Temp + cFile_out,0)

    IF fh>-1
      nLen:=fwrite(fh, cBuff) 
      FCLOSE(fh)

            IF nLen==len(cBuff)
                // MsgInfo("File written back to "+ cFile_out)
            ENDIF

    ENDIF

ENDIF

RETURN(cDir_Temp+cFile_out)

Posted: **Mon May 27, 2013 9:34 am**

I wanted to have a common function to convert a file into a SQL safe string and string into a file back.

Instead of re-inventing the wheel again, I decided to use the base64 encoding and decoding which is used in attaching the files in a mail.

So, here comes the functions:

Code: Select all

function file2str( cFileName )
   local oEncoder := TIPEncoderBase64():New()
   local hInput
   local cData := ''
   local cBuffer := Space( 1024 )
   IF hb_FileExists( cFileName ) 
      hInput := FOpen( cFileName )
      nLen := FRead( hInput, @cBuffer, 1024 )
      DO WHILE nLen > 0
         IF nLen < 1024
            cData += hb_BLeft( cBuffer, nLen )
         ELSE
            cData += cBuffer
         ENDIF
         nLen := FRead( hInput, @cBuffer, 1024 )
      ENDDO
      FClose( hInput )
      cData := oEncoder:Encode( cData )
      return cData
   ENDIF
return cData


function str2file( cStr, cFileName )
   local oEncoder := TIPEncoderBase64():New()
   local hOutput
   cStr := oEncoder:Decode( cStr )
   hOutput := FCreate( cFileName )
   FWrite( hOutput, cStr )
   FClose( hOutput )
return nil

I have checked in SQLite blob field for an image file and converted back the blob field content into a temporary image file. Everything works fine.

HMGforum.com

write a file type jpeg image in a field of a mysql table

write a file type jpeg image in a field of a mysql table

Re: write a file type jpeg image in a field of a mysql table

Re: write a file type jpeg image in a field of a mysql table

Re: write a file type jpeg image in a field of a mysql table

Re: write a file type jpeg image in a field of a mysql table

Re: write a file type jpeg image in a field of a mysql table

Re: write a file type jpeg image in a field of a mysql table

Re: write a file type jpeg image in a field of a mysql table