www.webdeveloper.com
Results 1 to 13 of 13

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

  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.

  6. #6
    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

  7. #7
    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.

    ^_^

  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.

    ^_^

  9. #9
    Join Date
    Sep 2010
    Posts
    160
    Quote Originally Posted by WolfShade View Post
    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.
    All good points. Let us just say we have to agree to disagree.


    Quote Originally Posted by WolfShade View Post
    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.

    ^_^
    No that is about it.

    I think you should use CF_SQL_VARBINARY instead of CF_SQL_BINARY and you simply need to use your defined variable (which you named binFile) as the value for the <cfqueryparam> and you should be good to go.

    Let us know if it worked.

    Archie

  10. #10
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    I'm stunned. I honestly didn't think that it would be that simple. Thought it would be much more complex. Thank you for your advice.

    Now.. to take this one step further.

    This works excellent if only one file is uploaded. However, if the form allows (let's say) three files to be uploaded at once. I'd like to be able to set a blank variable, loop through the INPUT/FILE objects while appending the query in the variable, then just CFQUERY using the variable. However, a ByteArray cannot be used as a string, so that kills that idea.

    Aside from putting a loop within the CFQUERY tag, is there a better way to do this? I'm thinking of keeping database connections to a minimum, if possible.

    Thanks,

    ^_^

  11. #11
    Join Date
    Sep 2010
    Posts
    160
    Nice that it worked.

    About your loops, here you are on your own, I have never worked with ColdFusion so I don't know how to do loops and stuff there.

    This would be a perfect question for the other forum though.

    Archie

  12. #12
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    The only difference between ColdFusion and other languages is syntax. The logic is the same, the process pretty much the same. I'm not asking for CF specific advice - I can provide that, myself. Since a ByteArray cannot be used as a string or part of a string, the next simplest process would be to put a loop within a CFQUERY:
    Code:
    <cfquery [blah blah blah]>
      <cfloop from=1 to=3 index="idx">
        INSERT into TABLE(col1, col2, col3)
        VALUES ('#ff1_[idx]#', '#ff2_[idx]#', #binary[idx]#) /* all within CFQUERYPARAM tags */
      </cfloop>
    </cfquery>
    This opens a single db connection, loops the insert, then closes the connection; more efficient than three db connections in a row. Since I can't just put the whole query in a variable and use the variable as the query (because of the ByteArray), can you think of any logic that would be better/faster?

    Thanks,

    ^_^

  13. #13
    Join Date
    Sep 2010
    Posts
    160
    I am really sorry but I cannot agree with you at all on this point.

    I am very happy that you can store your files and that I could have been of help there.

    But although most programming languages can look the same, no two really are exactly the same.

    In Java a String is a byte array. There is no differance at all. So your sentence means absolutely nothing for me in that regard.

    Also, in Java you don't really put the SQL statement in a variable so that point is moot too.

    From a logical point of view, what you have done sounds pretty good, open connection once, loop a bunch of times and for each loop execute the query.

    But as I said in the beginning of this reply, I cannot agree with you because this is, in my opinion, purely a ColdFusion question and nothing else.

    Archie

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