www.webdeveloper.com
Results 1 to 13 of 13

Thread: [RESOLVED] Insert file (pdf or doc) into database

Hybrid View

  1. #1
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582

    resolved [RESOLVED] Insert file (pdf or doc) into database

    Hello, everyone.

    I know that it's generally NOT a good idea to insert a file into the database - it's better to store the file on the server and provide a path to the file for the database.

    HOWEVER..

    I have a client that is insisting that the files must be stored in the database, not on the server, and I am not able to convince the client otherwise.

    I've never had to do this, before - all my other clients heed my warning about file storage in the database - so I'm totally inexperienced with this aspect of SQL. I don't know much, other than the datatype needs to be varbinary(max). I've got the form uploading the file to the web server - what's the next step?

    Thanks,

    ^_^

  2. #2
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    Can anyone please provide me with steps that will insert a file into a varbinary(max) datatype?

    ^_^

  3. #3
    Join Date
    Sep 2010
    Posts
    160
    There is nothing that says that storing files in a database should be worse than storing files on disk.

    This is not a SQL issue though, what programming language are you using? You should re-post this question in the correct forum.

    Archie

  4. #4
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    Quote Originally Posted by zimonyi View Post
    There is nothing that says that storing files in a database should be worse than storing files on disk.
    .. other than making the database bloated and prone to error.

    Quote Originally Posted by zimonyi View Post
    This is not a SQL issue though, what programming language are you using? You should re-post this question in the correct forum.

    Archie
    ColdFusion. But I'm not going to ask a CF forum an SQL question (not issue.) I've got the CF code that will upload the file to the server - that isn't the question. The question is how to insert the uploaded file into the database into a VARBINARY(MAX) datatype.

    ^_^

  5. #5
    Join Date
    Nov 2006
    Location
    Springfield, MO
    Posts
    1,541
    That's still a cold fusion question in the sense of transferring the file into binary data before the insert.
    Ryan Butler

    Ryan Butler.org

  6. #6
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    Quote Originally Posted by ryanbutler View Post
    That's still a cold fusion question in the sense of transferring the file into binary data before the insert.
    Code:
    <cffile action="upload"
         filefield="// Form field that contains file"
         destination="// Where saving to (C:\web\doc\files\)"
         accept="text/plain,application/msword,application/pdf,application/vnd.openxmlformats-officedocument.wordprocessingml.document // file types allowed">
    
    <cffile action="readbinary" 
         file="// location file was just saved to" 
         variable="binFile // variable name that stores binary data">
    This uploads the file, then reads the binary data into a variable called 'binFile', ready for inserting into MS SQL 2005 Server database.

    ^_^

  7. #7
    Join Date
    Sep 2010
    Posts
    160
    Quote Originally Posted by WolfShade View Post
    .. other than making the database bloated and prone to error.
    As opposed to making the file system bloated?

    A database is there to store data. Storing files is data. I do not understand why a file should be more prone to error, you need to back up such statements with good examples, because in my experience I would choose storing files in a database over the file system any day of the week, and twice on sundays. Defenitly for the fact that your table that stores the file can have a bunch of other columns which provide useful information which allows you to search for specific files in a way that it would more complex to do against a file system.

    Quote Originally Posted by WolfShade View Post
    ColdFusion. But I'm not going to ask a CF forum an SQL question (not issue.) I've got the CF code that will upload the file to the server - that isn't the question. The question is how to insert the uploaded file into the database into a VARBINARY(MAX) datatype.

    ^_^
    Unless you have an interface straight into the database it is very much a programming issue. In Java I would use a simple parametized SQL and just set the parameters with the correct types I want to insert and let the JDBC (which does the actual communication with the database) do the rest, I do not need to worry at all.

    Since you are using ColdFusion you need to know how ColdFusion works with your database. There is a tag called <cfquery> which has a subtag called <cfqueryparam> which should solve your problem. It should work the same way as I mentioned Java works. You can define your <cfqueryparam> to accept various types and then you just set them and ColdFusion handles the rest.

    Did that help you get further?

    Archie

  8. #8
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    Quote Originally Posted by zimonyi View Post
    As opposed to making the file system bloated?

    A database is there to store data. Storing files is data. I do not understand why a file should be more prone to error, you need to back up such statements with good examples, because in my experience I would choose storing files in a database over the file system any day of the week, and twice on sundays. Defenitly for the fact that your table that stores the file can have a bunch of other columns which provide useful information which allows you to search for specific files in a way that it would more complex to do against a file system.
    Windows Server is an excellent file server (as opposed to Novell which used to be a good application server) and is excellent at storing files regardless of size or quantity. It's better and easier to upload the file to the server, get the path information and store the path information in the database - not only does it keep the database from becoming bloated (which, let's face it, indexing can only do so much) but it also cuts down on network traffic between the database server and the web server (usually much faster to go from web server to client computer than to go from db server to web server to client computer) and leaves more db server processor power for running large or complicated queries.


    Quote Originally Posted by zimonyi View Post
    Unless you have an interface straight into the database it is very much a programming issue. In Java I would use a simple parametized SQL and just set the parameters with the correct types I want to insert and let the JDBC (which does the actual communication with the database) do the rest, I do not need to worry at all.

    Since you are using ColdFusion you need to know how ColdFusion works with your database. There is a tag called <cfquery> which has a subtag called <cfqueryparam> which should solve your problem. It should work the same way as I mentioned Java works. You can define your <cfqueryparam> to accept various types and then you just set them and ColdFusion handles the rest.

    Did that help you get further?

    Archie
    I'm attempting to write a stored procedure to handle the data and binary file. I will use CFQUERY to EXEC the sp and pass along the data, and can place the data inside CFQUERYPARAM tags (I usually do use CFQUERYPARAM). If you're saying that it's just a matter of passing the file to the database inside a CFQUERYPARAM tag set for CFSQLTYPE of CF_SQL_BINARY and inserting that into the database, I'll give it a shot. But I suspect there's a lot more to it than that.

    ^_^

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles