Blob a uploaded file into SQL DB

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 all Open 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.....

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 all Open in new window

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.

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 all Open 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.