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

Moderator: Rathinagiri

Post Reply
User avatar
jairpinho
Posts: 420
Joined: Mon Jul 18, 2011 5:36 pm
Location: Rio Grande do Sul - Brasil
Contact:

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

Post by jairpinho »

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.
Jair Pinho
HMG ALTA REVOLUÇÃO xBASE
HMG xBASE REVOLUTION HIGH
http://www.hmgforum.com.br
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

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

Post by Rathinagiri »

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.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
esgici
Posts: 4543
Joined: Wed Jul 30, 2008 9:17 pm
DBs Used: DBF
Location: iskenderun / Turkiye
Contact:

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

Post by esgici »

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
Viva INTERNATIONAL HMG :D
User avatar
jairpinho
Posts: 420
Joined: Mon Jul 18, 2011 5:36 pm
Location: Rio Grande do Sul - Brasil
Contact:

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

Post by jairpinho »

There is a way to make some functions instead of mysql sqlite_3
Jair Pinho
HMG ALTA REVOLUÇÃO xBASE
HMG xBASE REVOLUTION HIGH
http://www.hmgforum.com.br
User avatar
jairpinho
Posts: 420
Joined: Mon Jul 18, 2011 5:36 pm
Location: Rio Grande do Sul - Brasil
Contact:

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

Post by jairpinho »

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.
Jair Pinho
HMG ALTA REVOLUÇÃO xBASE
HMG xBASE REVOLUTION HIGH
http://www.hmgforum.com.br
User avatar
jairpinho
Posts: 420
Joined: Mon Jul 18, 2011 5:36 pm
Location: Rio Grande do Sul - Brasil
Contact:

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

Post by jairpinho »

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.
Jair Pinho
HMG ALTA REVOLUÇÃO xBASE
HMG xBASE REVOLUTION HIGH
http://www.hmgforum.com.br
User avatar
jairpinho
Posts: 420
Joined: Mon Jul 18, 2011 5:36 pm
Location: Rio Grande do Sul - Brasil
Contact:

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

Post by jairpinho »

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)


Jair Pinho
HMG ALTA REVOLUÇÃO xBASE
HMG xBASE REVOLUTION HIGH
http://www.hmgforum.com.br
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

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

Post by Rathinagiri »

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.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
Post Reply