Upload pdf-files to mysql database

[Willy]

Hello,

I've build a database with fivewin and mysql. It has to store several files like excel, word, pdf, jpg etc.

It works fine when I add simple notepad files. I gives an error when I upload complex files like pdf.

Does I have to convert them first to another format

Thanks,

Willy Hermans

[James Bott]

Willy,

You have to store that type of data in a field defined as blob. See this documentation.

http://dev.mysql.com/doc/refman/5.0/en/blob.html

Regards,
James

[Willy]

Thanks James,

That does not solve the problem.

I already store the data in a longblob it works for several files. (flat ascii files) It does not work for bmp, gif, pdf and so on.

Code: Select all  Expand view

cComm := [update document set doc = Hex(']+cFilebuffer+[') where docid = '00000001']
? cComm
UpdateQ(cComm,"document")

THis is the code I use.

The content of the variable cFileBuffer contains the file. It containbs also single quotes like ['].

I think that causes the problem.

I Think there is need for a function that converts cFileBuffer first in xHarbour.
Does anybody has an idea.

Greetings

Willy

[Rick Lipkin]

Willy

Consider the following code for MS Sql server .. using a VarBinary(max ) "charter" field in the table :

This uses (x)Harbour ADO methods .. notice the AppendChunk method ..

Rick Lipkin

Code: Select all  Expand view

// 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("chartereid"):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("charter"):AppendChunk( VTArrayWrapper():New( 17, cBUFFER ) )

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

oDLG:END()
RETURN(.T.)

[Willy]

Hello Rick,

Code: Select all  Expand view

oRsCh:Fields("charter"):AppendChunk( VTArrayWrapper():New( 17, cBUFFER ) )


Indeed this line could contain the solution.

I wonder if VTArrayWrapper is written in xHarbourcode and available.

The same for the appendchunk method.

I do not find any function of method in a xharbour or fivewin object.

Maybe it still has to be added to the commercial sql driver.

Patrick ?

Greetings,

Willy Hermans.

[Rick Lipkin]

Willy

I had the same problem as you .. I needed to be able to store any document in a database .. I am using just plain ADO .. not any commercial library .. AppendChunk, GetCHunk are well documented ADO methods on MSDN ..

Code: Select all  Expand view

oRsCh:Fields("charter"):AppendChunk( VTArrayWrapper():New( 17, cBUFFER ) )


This is native xHarbour .. I got the solution from Ron on the xHarbour NG .. GetChunk does not need any special wrapper to work if you need to extract the document to view .. you will need to store the number of bytes of the document and the filename and extention in your table .. here is the extraction code.. make sure you use a VarBinary(max ) or equivelant field type ..

Code: Select all  Expand view

/-------------------------------
Static Func _Viewum( oRsCh  )

LOCAL nHANDLE, cREAD, cFILENAME

cFILENAME := alltrim(oRsCh:Fields("filename"):Value )

cREAD := oRsCh:Fields("charter"):GetChunk( oRsCh:Fields("datalen"):Value)
FERASE( xVOL+"\DBTMP\"+cFILENAME )

nHANDLE := FCREATE(  xVOL+"\DBTMP\"+cFILENAME, 0 )
IF FERROR() <> 0
   SAYING := "Error Creating file "+(xVOL+"\DBTMP\"+cFILENAME)+CHR(10)
   SAYING += "Error "+STR(FERROR())+CHR(10)
   MsgInfo( SAYING )
   RETURN(.F.)
ENDIF

FWRITE( nHANDLE, cREAD )   // write out the file
FCLOSE( nHANDLE )


SysReFresh()

SHELLEXECUTE( "", "open", (xVOL+"\DBTMP\"+cFILENAME),"","",1)

SysReFresh()

RETURN(.T.)

[Willy]

Hello Rick,

Already many thanks for the effort.

oRsCh seems to be an instance of an ado object. I asume it is the free AdoRDD.

So I can not use appendchunk in my SQL rdd. I did not find an equivalent in the short manual. So I think I have to build a new environment based on AdoRDD.

I saw you have much experience with ADO.

Can you solve all/most database problems with it.
Is it fast.
Can you switch to another DBMS without rewriting code.

I set up a new environment with VMWare and try ADO.

Thanks,

Willy

[Rick Lipkin]

Willy

ADO is not difficult .. and it takes nothing to learn how to use it. ADO is just a way of connecting to Sql databases .. learning the methods for movenext, find, eof, filter, movelast, movetop ..you can start with downloading the free adordd .. look at the code and see how the rdd creates its recordsets.

The one advantage with ADOrdd is that you can use it like dbf\cdx .. work areas .. just like any rdd.

There are many topics on the subject in this forum .. search on ADO and you will find all kinds of examples from Access databases, to Oracle, Sql server, my sql ..

As far as portability .. the ADO methods are the same .. the only thing that changes is the connection string to your flavor of database. The same programming applies to SQL Server, Access, Oracle, MySql .. my choice is MS Sql server because the Windows OS has everything it needs to natively talk with SQL server ( so does access ) .. with other enterprise SQL databases .. you have to download their client and load it to each pc ..

Download first the adordd .. look at the code .. I personally use ADO from scratch .. no rdd. It is a matter of choice and what you feel comfortable with.

If you have the MS Office suite .. start with learning how to connect to an Access database ( again .. search the forum for access examples ) .. you don't have to have a SQL database running somewhere .. the programming code is the same for all . .

Hollar if I can help

Rick Lipkin

[Lautaro]

Willy,

You can transform the pdf file with fmimeenc and store this on the mysql database in the medium or long text field.

After you use fmimedec for obtain the original pdf file.

I'm use this witout problems for store all types of files in the mysql database.

atte.,

Lautaro

PD: Sorry for my english, is very bad.

[Willy]

Thanks,

That does the job. It is working now with relative small pdf-files.

I use already a longblob. Normaly it can contain a file of more then 100 MB.

But the system can not load a file of 2 MB.

In the MySQL manual I found something. But it is not very clear. I should have to change a setting in the server.

Does anybody now what I exactly have to change to execute large sql statements.

Greetings,

Willy.

[Lautaro]

Willy,

In Mysql server increase the max_allowed_packet and net_buffer_length for upload files with more of 2mb.

I'm work with files of 10mb of size and it's ok.


Atte.,

Lautaro Moreira

PD. Sorry if you not understand , My english is more bad every day