Blob a uploaded file into SQL DB

why do you want to save the file directly in the database? it's more cumbersome to manage, doesn't offer any performance gain, and is generally not done unless it's for security.

it would be better to just store the path of the file in the database as a pointer and then use that when you want to manipulate the file

Hi Big,

Because our security officer is demanding it. The uploaded files are copies of id cards and so on. If i would store the files in folders my application would not be allowed to use in hospital. I hope you can help me.
Kind regards,

Steynsk

Hello Big,

I don't think this does not answer my question.
Like you wite in you first comment I would like to upload directly into the database and not download to folder first and than post proces the file.
It seems to me your example processes the file from a location from the server.

In earlier code I succesfully used this:

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open sDSN
Set oPseudoRequest = new PseudoRequestDictionary
oPseudoRequest.ReadRequest()
oPseudoRequest.ReadQuerystring(Request.Querystring)
			sSQL = "SELECT * FROM protocol"
			Set oRS = Server.CreateObject("ADODB.RecordSet")
			oRS.Open sSQL, oConn, 1, 3
			oRS.AddNew
			oRS("bestandsnaam") = SQLEncode(oPseudoRequest.Form("image_binary").FileName)
			oRS("titel") = SQLEncode(oPseudoRequest.Form("titel"))
			if not SQLEncode(oPseudoRequest.Form("URL"))= "" then
				if Left(SQLEncode(oPseudoRequest.Form("URL")),7)="http://" then
					oRS("URL") = SQLEncode(oPseudoRequest.Form("URL"))
				else 
					oRS("URL") = "http://" & SQLEncode(oPseudoRequest.Form("URL"))
				end if
			else 
				oRS("URL") = ""
			end if
			oRS("categorie") = SQLEncode(oPseudoRequest.Form("categorie"))
			oRS("nummer") = SQLEncode(oPseudoRequest.Form("nummer"))
			oRS("geplaatst") = SQLEncode(oPseudoRequest.Form("geplaatst"))
			oRS("bestandstype") = oPseudoRequest.Form("image_binary").ContentType
			oRS("bestand").AppendChunk oPseudoRequest.Form("image_binary").Binary
			oRS.Update
			oRS.Close
			Set oRS = Nothing

response.redirect("inhoudsopgave.asp")

Function SQLEncode(ByVal s)
	SQLEncode = Cstr("" & s)
	SQLEncode = Replace(SQLEncode,"'","''")
End Function


Set oPseudoRequest = Nothing
oConn.Close
Set oConn = Nothing

Select allOpen in new window

But this is dealing with only one file at the time.  And in line 35 and 36 I use the fieldname of the file upload form input to upload the right file. My problem is how to translate this to the code in the class.

In other words

the line you profided :

cSql = "INSERT INTO myfiletable (blob_field) values (?); "

Is containing a question mark. My question is what variable should be used where question mark stands?

I've tried

                  RS("filename") = fileItem.FileName
                  RS("templateID") = Session("caseID")
                  RS("ContentType") = fileItem.ContentType
                  RS("file").AppendChunk(fileItem)

but only the last line is causing problems.....

I don't believe you can upload a file to a memory stream, you need to upload it first to a physical location, then put it into a memory stream, then write it to the database..

the code I gave uses a parameterized query, that's why you see the question mark in the sql. these 2 lines fill in that parameter:

x = ObjStr.Read

oPreparedStatementADO.Parameters.Item(0) = x

Thanks Big,

Now I understand. Thank you for the explanation about the parameter.
I’ve tried to implement this in my code but I think I made mistake in the connection part can you give it a look?

The values in the file path and connect string have been changed for understandable reasons

			Set streamFile = Nothing
			fileItem.Path = filePath
			'costum rename
			'Dim fso, newFile, caseID
			'Set fso = CreateObject("Scripting.FileSystemObject")
			uploadsDirVar = "C:\Inetpub\wwwroot\myweb\userfiles" 
			'caseID = Session("caseID")
			'newFile = uploadsDirVar&"\"& templateID & fileItem.FileName
			'fso.MoveFile filePath, newFile
			'set fso = nothing
			Set ObjStr = Server.CreateObject("ADODB.Stream")
			ObjStr.Type = 1 'AdBinary
			ObjStr.Open
			file = uploadsDirVar&"\" & fileItem.FileName
			ObjStr.LoadFromFile file
			CS.Open "Provider=SQLOLEDB;Data Source=my.data.source;Initial Catalog=mycatalog; User ID=username; Password=password;" 
			oPreparedStatementADO.ActiveConnection = CS
			cSql = "INSERT INTO entree_bestanden(file) values (?);"
			oPreparedStatementADO.CommandText = cSQL
			x = ObjStr.Read
			oPreparedStatementADO.Parameters.Item(0) = x
			set rs = oPreparedStatementADO.Execute
		 Next
	End Sub

Select allOpen in new window

are you getting any error message?

any luck with this?

Hello Big,

Sorry it was so quiet. I became ill inbetween.

No the error I'm getting is produced by the code error handler ("The error was (Object object) ") and when I remove the error handeling I don't get any error.

sorry to hear, hope you're feeling better!

can you post the error handling code, or the line of code you think is causing that error? i don't see it in the code above

Big,

I've found another solution
The version of this script (2.14) that is translated to ASP accepts an event called "onAllComplete"

'onAllComplete'  : function() {self.location ="PostProcessUpload.asp?caseID=<%=caseID%>";},

Select allOpen in new window

So now I can post process the uploaded and renamed files.
I rename them uniquely by adding a number and underscore like 10_filename.jpg
In my post process I process all uploaded files based on the added 10_ into my table adding a caseID with value 10
And then on success delete the files from disk.

Thanks for your help I will give you the points of this question for the time you put into it.

Thanks for helping out