error in recording the blob in mysql

HMG en Español

Moderator: Rathinagiri

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

error in recording the blob in mysql

Post by jairpinho »

cFoto := sqlite3_file_to_buff(Alltrim(Form_Novo_OS.Text_6.Value) )

cQuery:= "INSERT INTO ordem_servico FOTO_1 VALUES (" + cFoto + ")"
oQuery := oServer:Query( cQuery )
If oQuery:NetErr()
MsgInfo("Erro (Operação) (): " + oQuery:Error())
Return Nil
Endif
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: error in recording the blob in mysql

Post by Rathinagiri »

I think you have to add extra quote on either side.

cQuery:= "INSERT INTO ordem_servico FOTO_1 VALUES ('" + cFoto + "')"
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
jairpinho
Posts: 420
Joined: Mon Jul 18, 2011 5:36 pm
Location: Rio Grande do Sul - Brasil
Contact:

Re: error in recording the blob in mysql

Post by jairpinho »

Rathi thanks for the help, I forgot to put the code in my example but use as you said, still giving error as shown below


cFoto := sqlite3_file_to_buff(Alltrim(Form_Novo_OS.Text_6.Value) )

cQuery:= "INSERT INTO ordem_servico FOTO_1 VALUES ('" + cFoto + "')"
oQuery := oServer:Query( cQuery )
If oQuery:NetErr()
MsgInfo("Erro (Operação) (): " + oQuery:Error())
Return Nil
Endif





rathinagiri wrote:I think you have to add extra quote on either side.

cQuery:= "INSERT INTO ordem_servico FOTO_1 VALUES ('" + cFoto + "')"
Attachments
imagem blob.gif
imagem blob.gif (10.55 KiB) Viewed 5671 times
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: error in recording the blob in mysql

Post by Rathinagiri »

Hi,

I am sorry.

There is a function load_file() in mysql to directly store the file contents in a blob column.

http://dev.mysql.com/doc/refman/5.0/en/ ... _load-file

Code: Select all

UPDATE t
            SET blob_col=LOAD_FILE('/tmp/picture')
            WHERE id=1;
So, your code should be:

Code: Select all

cQuery:= "INSERT INTO ordem_servico FOTO_1 VALUES LOAD_FILE('" + Form_Novo_OS.Text_6.Value + "')"
For this you need the file reading permission in the server.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
jairpinho
Posts: 420
Joined: Mon Jul 18, 2011 5:36 pm
Location: Rio Grande do Sul - Brasil
Contact:

Re: error in recording the blob in mysql

Post by jairpinho »

Rathi!

found the error when the function returns non sqlite3_file_to_buff lets you add the + " ' " after the result, for compiling the function below to see what happens, if you know how to hit follows function in "c"

cBuffer := " ' " + FILE_TO_BUFF("teste.jpg" ) + " ' "

msginfo(cBuffer)

HB_FUNC( SQLITE3_FILE_TO_BUFF )
{
int handle = hb_fsOpen( ( BYTE * ) hb_parc( 1 ), FO_READ );

if( handle != FS_ERROR )
{
BYTE * buffer;
ULONG iSize;

iSize = ( int ) hb_fsSeek( handle, 0, FS_END );
iSize -= ( int ) hb_fsSeek( handle, 0, FS_SET );
buffer = ( BYTE * ) hb_xgrab( iSize + 1 );
iSize = hb_fsReadLarge( handle, buffer, iSize );
buffer[ iSize ] = '\0';
hb_fsClose( handle );

hb_retclen_buffer( ( char * ) buffer, iSize );
}
else
hb_retc_null();
}
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: error in recording the blob in mysql

Post by Rathinagiri »

Have your tried LOAD_FILE() function?
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
jairpinho
Posts: 420
Joined: Mon Jul 18, 2011 5:36 pm
Location: Rio Grande do Sul - Brasil
Contact:

Re: error in recording the blob in mysql

Post by jairpinho »

if I use this function I have to change my whole program structure
rathinagiri wrote:Have your tried LOAD_FILE() function?
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: error in recording the blob in mysql

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: error in recording the blob in mysql

Post by Rathinagiri »

That's just great!
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
Post Reply