Re: Saving file into database
I've been reading this thread, but I can't see the advantages of storing files in a database. I've always had the impression that a *file* system was the appropriate place to store files. Access can be arranged easily by other means. Replication seems more silly, since one also copies the stored files to the slave. This not only adds to the traffic between slave and master, but also wastes diskspace. Furthermore large files may cause you to hit the max_allowed_packet size (PDF's of 5MB or larger are no exception in real life). I've built a system which includes the possibility of downloading one's own bills in PDF format. This system runs on load balanced webservers with one mysql server. The files are stored on a different machine that cannot be accessed directly from the web. Scripts validate access to the PDF and serve it to the client. Works like a charm ;-) Regards, Jigal. - Original Message - From: [EMAIL PROTECTED] To: Eve Atley [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, March 11, 2004 5:42 PM Subject: RE: Saving file into database It does make the database larger.. as far as overhead... As you can't just store the file as a blob.. You'll need some referencing data in order to find it, and restore it back out of the database.. I just checked out my database (100's of files) which has: Total file size: 1765.34MB Mysql files are: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving file into database
On Fri, 12 Mar 2004, Jigal van Hemert wrote: I've been reading this thread, but I can't see the advantages of storing files in a database. I've always had the impression that a *file* system was the appropriate place to store files. Scalability, searching, security.. just a few.. Replication seems more silly, since one also copies the stored files to the slave. This not only adds to the traffic between slave and master, but also wastes diskspace. Disk is relatively cheap.. And if your using mysql file storage on a large site you'll probably need replication to feed those data hungry frontend webservers.. Furthermore large files may cause you to hit the max_allowed_packet size (PDF's of 5MB or larger are no exception in real life). Anyone implementing mysql file storage using largeblobs, I feed needs to re-address their storage implementation. I've built a system which includes the possibility of downloading one's own bills in PDF format. This system runs on load balanced webservers with one mysql server. The files are stored on a different machine that cannot be accessed directly from the web. Scripts validate access to the PDF and serve it to the client. Works like a charm ;-) Sweet.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Saving file into database
Use the BLOB, Luke! See your local MySQL manual for details. We're using BLOBs to store PDF in our database, and through the use of HTTP headers, we're able to let user download the PDFs without having to store a local copy on disk, directly from the database (content-disposition header). Hi Kurt, I have been using MySQL to store links to PDFs which live in other directories. Is there an advantage to storing the PDFs directly into the database? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Saving file into database
Is there an advantage to storing the PDFs directly into the database? I'm also curious how large this would make a database. Is there any space saved through this method, or would they still be the same size as the original PDF? - Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving file into database
Erich Beyrent wrote: Use the BLOB, Luke! See your local MySQL manual for details. We're using BLOBs to store PDF in our database, and through the use of HTTP headers, we're able to let user download the PDFs without having to store a local copy on disk, directly from the database (content-disposition header). Hi Kurt, I have been using MySQL to store links to PDFs which live in other directories. Is there an advantage to storing the PDFs directly into the database? -Erich- Not all PDF's are viewable for all users. First it is determined whether the user can be granted access to the file, before he's presented with a hyperlink. This authorization is stored in the database, thus our web app can access and present the PDF using the same protocol it used for authentication/authorization. It can even use the same open database connection. No need to deal with other protocols and protection/authorization of those files on file system- and web server-level: if you're not authorized, you can't access them in any way. Backoffice maintenance and content management can be done with only a single database connection. In our case, this resulted in much simpler and safer code. Other advantages I can think of are, amongst other, master/slave replication of the database, and no required needs to tune your database server for anything else than MySQL activity. Disadvantages which come to mind are a much larger database, which is often more difficult to maintain than large file systems. To some people, at least. Just ideas, food for thought. Regards, Kurt.
Re: Saving file into database
Eve Atley wrote: Is there an advantage to storing the PDFs directly into the database? I'm also curious how large this would make a database. Is there any space saved through this method, or would they still be the same size as the original PDF? - Eve There's a percentage of disk space lost to database overhead (headers and such). Fragmentation, internal and external, is comparable to storage at the file-system level. I don't have exact figures what this would mean to MySQL, since our app currently runs on Oracle. Easy enough to test this in lab environments, though. Regards, Kurt
RE: Saving file into database
I store any kind of files, PDF/word/etc.. I just like not having lots of directories with 1000's of files each in them... Seems more organized to me.. On Thu, 11 Mar 2004, Erich Beyrent wrote: Use the BLOB, Luke! See your local MySQL manual for details. We're using BLOBs to store PDF in our database, and through the use of HTTP headers, we're able to let user download the PDFs without having to store a local copy on disk, directly from the database (content-disposition header). Hi Kurt, I have been using MySQL to store links to PDFs which live in other directories. Is there an advantage to storing the PDFs directly into the database? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Saving file into database
It does make the database larger.. as far as overhead... As you can't just store the file as a blob.. You'll need some referencing data in order to find it, and restore it back out of the database.. I just checked out my database (100's of files) which has: Total file size: 1765.34MB Mysql files are: -rw-r-1 mysqlmysql 23216 Feb 27 03:49 file.MYD -rw-r-1 mysqlmysql 10240 Feb 28 03:50 file.MYI -rw-r-1 mysqlmysql8756 Feb 23 2003 file.frm -rw-r-1 mysqlmysql1808037152 Feb 27 03:53 filedata.MYD -rw-r-1 mysqlmysql 400384 Feb 28 03:50 filedata.MYI -rw-r-1 mysqlmysql8614 Feb 23 2003 filedata.frm So it's not too bad as far as overhead.. On Thu, 11 Mar 2004, Eve Atley wrote: Is there an advantage to storing the PDFs directly into the database? I'm also curious how large this would make a database. Is there any space saved through this method, or would they still be the same size as the original PDF? - Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving file into database
Are you running a web server (or ftp server) as well? Because if you are, then you can upload the files to a separate directory using perl and just store the links to that file into a table in your database... If you're not running a webserver (or ftp)... then lemme konw if you get a viable suggestion. HEre's my 2cents. Since mysql is a relational database, it would be difficult to display that particular column/row containing the file (esp, binary). You can use perl (or another converter) to convert the binary file into uue (or other text format)... and then import that... make sure you remove the linefeeds and store information about the column widths of the uue (or other text format) into a table in your database. but geez, if the file is considerably large... like i said, it would put a strain on your server. (unless you have GIGS of ram and extra processing spd). good luck, paul At 01:49 AM 3/9/2004, Isa Wolt wrote: Hi, I would like to save a binary file into a mysql database, for later being able to use the file. I am using a perl interafce. Is this at all possible??? And would it be possible to then read that file from a c++ interface? would be greatful for any help/advices! Isa _ Hitta rätt på nätet med MSN Sök http://search.msn.se/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Paul Rigor [EMAIL PROTECTED] Go Bruins! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving file into database
Use the BLOB, Luke! See your local MySQL manual for details. We're using BLOBs to store PDF in our database, and through the use of HTTP headers, we're able to let user download the PDFs without having to store a local copy on disk, directly from the database (content-disposition header). Hope this helps. Kurt Haegeman Mediargus.com Paul Rigor wrote: Are you running a web server (or ftp server) as well? Because if you are, then you can upload the files to a separate directory using perl and just store the links to that file into a table in your database... If you're not running a webserver (or ftp)... then lemme konw if you get a viable suggestion. HEre's my 2cents. Since mysql is a relational database, it would be difficult to display that particular column/row containing the file (esp, binary). You can use perl (or another converter) to convert the binary file into uue (or other text format)... and then import that... make sure you remove the linefeeds and store information about the column widths of the uue (or other text format) into a table in your database. but geez, if the file is considerably large... like i said, it would put a strain on your server. (unless you have GIGS of ram and extra processing spd). good luck, paul At 01:49 AM 3/9/2004, Isa Wolt wrote: Hi, I would like to save a binary file into a mysql database, for later being able to use the file. I am using a perl interafce. Is this at all possible??? And would it be possible to then read that file from a c++ interface? would be greatful for any help/advices! Isa _ Hitta rätt på nätet med MSN Sök http://search.msn.se/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Paul Rigor [EMAIL PROTECTED] Go Bruins!
Re: Saving file into database
Thanks for all the answers!!! I have been trying the longblob's and it seems to work! Now all I have to is to figure out how to read it out.. but how hard can it be ;) thanks again for all good suggestions! /isa From: Kurt Haegeman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Saving file into database Date: Wed, 10 Mar 2004 14:25:33 +0100 Use the BLOB, Luke! See your local MySQL manual for details. We're using BLOBs to store PDF in our database, and through the use of HTTP headers, we're able to let user download the PDFs without having to store a local copy on disk, directly from the database (content-disposition header). Hope this helps. Kurt Haegeman Mediargus.com Paul Rigor wrote: Are you running a web server (or ftp server) as well? Because if you are, then you can upload the files to a separate directory using perl and just store the links to that file into a table in your database... If you're not running a webserver (or ftp)... then lemme konw if you get a viable suggestion. HEre's my 2cents. Since mysql is a relational database, it would be difficult to display that particular column/row containing the file (esp, binary). You can use perl (or another converter) to convert the binary file into uue (or other text format)... and then import that... make sure you remove the linefeeds and store information about the column widths of the uue (or other text format) into a table in your database. but geez, if the file is considerably large... like i said, it would put a strain on your server. (unless you have GIGS of ram and extra processing spd). good luck, paul At 01:49 AM 3/9/2004, Isa Wolt wrote: Hi, I would like to save a binary file into a mysql database, for later being able to use the file. I am using a perl interafce. Is this at all possible??? And would it be possible to then read that file from a c++ interface? would be greatful for any help/advices! Isa _ Hitta rätt på nätet med MSN Sök http://search.msn.se/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Paul Rigor [EMAIL PROTECTED] Go Bruins! _ Chatt: Träffa nya nätkompisar på Habbo Hotel http://habbohotel.msn.se/habbo/sv/channelizer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving file into database
Yes, it's possible. Just make sure you quote it (see the Perl DBI docs for the quote method) before you insert it. j- k- On Tuesday 09 March 2004 12:49 am, Isa Wolt wrote: Hi, I would like to save a binary file into a mysql database, for later being able to use the file. I am using a perl interafce. Is this at all possible??? And would it be possible to then read that file from a c++ interface? -- Joshua J. Kugler Fairbanks, Alaska Computer Consultant--Systems Designer .--- --- ... ..- .--.- ..- --. .-.. . .-. [EMAIL PROTECTED] ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving file into database
Check this article: http://php.dreamwerx.net/forums/viewtopic.php?t=6 Port code/design to perl or whatever client language you want.. mysql could care less once it's got the data (correctly) On Tue, 9 Mar 2004, Isa Wolt wrote: Hi, I would like to save a binary file into a mysql database, for later being able to use the file. I am using a perl interafce. Is this at all possible??? And would it be possible to then read that file from a c++ interface? would be greatful for any help/advices! Isa _ Hitta rätt på nätet med MSN Sök http://search.msn.se/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]