Page 1 of 1
error in recording the blob in mysql
Posted: Sun Jan 22, 2012 12:31 am
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
Re: error in recording the blob in mysql
Posted: Sun Jan 22, 2012 4:00 am
by Rathinagiri
I think you have to add extra quote on either side.
cQuery:= "INSERT INTO ordem_servico FOTO_1 VALUES ('" + cFoto + "')"
Re: error in recording the blob in mysql
Posted: Sun Jan 22, 2012 12:59 pm
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 + "')"
Re: error in recording the blob in mysql
Posted: Sun Jan 22, 2012 2:43 pm
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.
Re: error in recording the blob in mysql
Posted: Sun Jan 22, 2012 3:41 pm
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();
}
Re: error in recording the blob in mysql
Posted: Sun Jan 22, 2012 5:34 pm
by Rathinagiri
Have your tried LOAD_FILE() function?
Re: error in recording the blob in mysql
Posted: Sun Jan 22, 2012 5:39 pm
by jairpinho
if I use this function I have to change my whole program structure
rathinagiri wrote:Have your tried LOAD_FILE() function?
Re: error in recording the blob in mysql
Posted: Sat Feb 25, 2012 4:15 pm
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)
Re: error in recording the blob in mysql
Posted: Sat Feb 25, 2012 4:31 pm
by Rathinagiri
That's just great!