Page 1 of 1
Upload pdf-files to mysql database
Posted: Fri Oct 24, 2008 5:58 pm
by 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
Posted: Fri Oct 24, 2008 9:04 pm
by 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
Posted: Sat Oct 25, 2008 10:29 am
by 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
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
Posted: Sat Oct 25, 2008 2:59 pm
by 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
// 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.)
Posted: Sat Oct 25, 2008 4:17 pm
by Willy
Hello Rick,
Code: Select all
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.
Posted: Sat Oct 25, 2008 6:48 pm
by 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
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
/-------------------------------
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.)
Posted: Sun Oct 26, 2008 11:32 am
by 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
Posted: Sun Oct 26, 2008 1:31 pm
by 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
Posted: Mon Oct 27, 2008 2:57 pm
by 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.
Posted: Tue Oct 28, 2008 8:04 pm
by 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.
Posted: Wed Oct 29, 2008 3:05 am
by 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