topic-26623

Board index FiveWin for Harbour/xHarbour Insert compressed file into SQL-field

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Insert compressed file into SQL-field
Posted: Sun Jun 23, 2013 07:59 AM

Hello,

How can I insert a compressed file in a SQL-field.
I a normal DBF-file I use

repl veld with  HB_Compress( cText )

This is working fine.
But if use it in SQL I want to execute

Code (fw): Select all Collapse

oSQL:execute("INSERT INTO bestand (veld), VALUES ('"+HB_Compress(ctext)+"')")

But since it is a compressed file, HB_Compress(ctext), contains a lot of special charcters, that cause sintax-errors in the query..

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Insert compressed file into SQL-field
Posted: Sun Jun 23, 2013 10:29 AM

To accept binary data, the field "veld" must be of a type that accepts binary data. We can use BLOB for sizes upto 64K bytes and MEDIUMBLOB for larger sizes.

CREATE TABLE bestand ( ............. , VELD MEDIUMBLOB, ........ )

Inserting binary data:

"INSERT INTO bestand ( veld ) VALUES ( 0x" + HB_STRTOHEX( HB_Compress(ctext) ) + " )"

We need to convert into hex for the sql statement, but the data is stored as binary data only. When we read, we directly get binary data and not hex data.

If you use FWH 13.05 writing sql statements is made easier

Code (fw): Select all Collapse

#include "fwsqlcmd.ch"
....
cSql := SQL INSERT INTO bestand ( veld ) VALUES ( HB_Compress( cText ) )
oCn:Execute( cSql )

Regards

G. N. Rao.

Hyderabad, India

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: Insert compressed file into SQL-field
Posted: Sun Jun 23, 2013 03:39 PM

Marc

Rao is correct ... if you are inserting a file ( any file ) into a Sql Table .. your Sql field MUST be defined as VarBinary ( sql server ) or Ole Object in Ms Access.

Which ever Sql table you are using .. the field attribute needs to be able to accept binary data. Here is a sample I use to insert any file, picture, .pdf, .xls ( any file ) using xHarbour and a recordset .. not using an INSERT statement ..

This method is not as elegant as Rao's suggestion .. but it may give you some ideas and clarity when inserting a ( binary ) file into a Sql Table.

Rick Lipkin

Code (fw): Select all Collapse

// 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("documenteid"):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("notes"):AppendChunk( VTArrayWrapper():New( 17, cBUFFER ) ) // addes file to notes field

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( "documenteid = '"+cEID+"'" )

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Insert compressed file into SQL-field
Posted: Sun Jun 23, 2013 06:36 PM

Rao, Rick,

Thank you for the support.
I will change the field-type and modify the data and try it.

FiveTech Support Forums

Continue the discussion