Avatar of Steynsk
Steynsk
Flag for Netherlands asked on

Blob a uploaded file into SQL DB

Hi experts,

I'm using this well working  jquery uploading script called uploadify. With the help of Big Monty and Scott Fell I succeeded in pasing true a value to a rename function.

But now I'd like to make the next step.

I'd like to store the file as a blob into my database.

Beneath the code I could think of my self but I can't figure out what my line 34 should end like.  I've put rem quotes for the benameing part. But unremarked this code does its job.

Can someone please help me out?  

	Public Sub Save(path)
		Dim streamFile, fileItem, filePath

		if Right(path, 1) <> "\" then path = path & "\"

		if not uploadedYet then Upload

		For Each fileItem In UploadedFiles.Items
			filePath = path & fileItem.FileName
			Set streamFile = Server.CreateObject("ADODB.Stream")
			streamFile.Type = adTypeBinary
			streamFile.Open
			StreamRequest.Position=fileItem.Start
			StreamRequest.CopyTo streamFile, fileItem.Length
			streamFile.SaveToFile filePath, adSaveCreateOverWrite
			streamFile.close
			Set streamFile = Nothing
			fileItem.Path = filePath
			
			'Dim fso, newFile, caseID
			'Set fso = CreateObject("Scripting.FileSystemObject")
			'uploadsDirVar = "C:\Inetpub\wwwroot\myweb\userfiles" 
			'caseID = Session("templateID")
			'newFile = uploadsDirVar&"\"& templateID & fileItem.FileName
			'fso.MoveFile filePath, newFile
			'set fso = nothing
			
			SQL = "SELECT * FROM myfiletable;"
			Set RS = Server.CreateObject("ADODB.RecordSet") 
			RS.Open SQL, CS, 1, 3 
			RS("filename") = fileItem.FileName
			RS("templateID") = Session("templateID")
			RS("ContentType") = fileItem.ContentType
			RS("file").AppendChunk ..............
			RS.Update
			RS.Close                     
		 Next
	End Sub

Open in new window

ASP

Avatar of undefined
Last Comment
Steynsk

8/22/2022 - Mon
Big Monty

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
Steynsk

ASKER
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
ASKER CERTIFIED SOLUTION
Big Monty

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Steynsk

ASKER
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

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 started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Steynsk

ASKER
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.....
Big Monty

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
Steynsk

ASKER
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

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Big Monty

are you getting any error message?
Big Monty

any luck with this?
Steynsk

ASKER
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Big Monty

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
Steynsk

ASKER
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%>";},

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

ASKER
Thanks for helping out
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.