We help IT Professionals succeed at work.

Blob a uploaded file into SQL DB

Steynsk
Steynsk asked
on
475 Views
Last Modified: 2014-05-15
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

Comment
Watch Question

Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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
Web Ninja at large
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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?

Author

Commented:
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 MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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

Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
are you getting any error message?
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
any luck with this?

Author

Commented:
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 MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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.

Author

Commented:
Thanks for helping out

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.