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

This site is best viewed in a modern browser with JavaScript enabled.

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

WolfShade

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,

WolfShade

Can anyone please provide me with steps that will insert a file into a varbinary(max) datatype?

zimonyi

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

WolfShade

zimonyi;1122459 wrote:
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.

zimonyi;1122459 wrote:
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.

ryanbutler

That's still a cold fusion question in the sense of transferring the file into binary data before the insert.

zimonyi

WolfShade;1122958 wrote:
.. 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.

WolfShade;1122958 wrote:
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

WolfShade

ryanbutler;1123013 wrote:
That's still a cold fusion question in the sense of transferring the file into binary data before the insert.

<cffile action="upload"
     filefield="[color=blue]// Form field that contains file[/color]"
     destination="[color=blue]// Where saving to (C:\web\doc\files\)[/color]"
     accept="text/plain,application/msword,application/pdf,application/vnd.openxmlformats-officedocument.wordprocessingml.document [color=blue]// file types allowed[/color]">

<cffile action="readbinary" 
     file="[color=blue]// location file was just saved to[/color]" 
     variable="binFile [color=blue]// variable name that stores binary data[/color]">

This uploads the file, then reads the binary data into a variable called 'binFile', ready for inserting into MS SQL 2005 Server database.

WolfShade

zimonyi;1123018 wrote:
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.

zimonyi;1123018 wrote:
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.

zimonyi

WolfShade;1123028 wrote:
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.

WolfShade;1123028 wrote:
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

WolfShade

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,

zimonyi

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

WolfShade

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:

<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,

zimonyi

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