Microsoft SQL Server
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
 
User Name:
Password:
Remember me
Go Back   ASP Free Forums > Database > Microsoft SQL Server
Reload this Page

Blob I I

Discuss Blob I I in the Microsoft SQL Server forum on ASP Free.
Blob I I Microsoft SQL Server forum discussing administration and other SQL Server related topics. MS SQL Server is used for high performance and enterprise level applications.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
  #1  
Old February 18th, 2005, 03:45 PM
A2k's Avatar
A2k A2k is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Dec 2004
Posts: 166 A2k User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 18 h 44 m 10 sec
Reputation Power: 4
Blob I I
FIRSTLY:
Mods... this may be better placed in the ASP Section, but to avoid duplicating it I will leave it here.

SECONDLY:
Call me cynical, but, I don't really expect anybody to answer my question, it would be nice just ocasionally! No offence intended to those who have so kindly helped me out... both times.

THIRDLY:
The question:
I have an ASP Page, and a neat tool from StrongCube to upload blobs (files to you and me). I would like to insert said blob into a table in my sql 2000 database. My prefered method is through a stored procedure due to the nature of the system, and its security setting, stored procedures offer the most flexible arrangements. So, what are the design implications of creating such a stored procedure, and what are the design implications of writing such an ASP script.

Thanks in advance...

The usual rules apply regarding reputation points: no win no fee.
Reply With Quote
  #2  
Old February 18th, 2005, 03:51 PM
Memnoch's Avatar
Memnoch Memnoch is offline
Unholy Moderator
ASP Free God 14th Plane (11500 - 11999 posts)
  
Join Date: Oct 2003
Location: In hell, where did you think?
Posts: 11,776 Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level) 
Time spent in forums: 3 Weeks 5 Days 8 h 27 m 42 sec
Reputation Power: 470
What exactly are the blobs you are uploading? Images?
Reply With Quote
  #3  
Old February 18th, 2005, 04:09 PM
A2k's Avatar
A2k A2k is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Dec 2004
Posts: 166 A2k User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 18 h 44 m 10 sec
Reputation Power: 4
Non-specific - the data type is IMAGE (not to be confused with pictures), however the blob could be anything jpg, word file, code samples (even though thats text, the file headers must be retained)
Reply With Quote
  #4  
Old February 18th, 2005, 04:50 PM
Memnoch's Avatar
Memnoch Memnoch is offline
Unholy Moderator
ASP Free God 14th Plane (11500 - 11999 posts)
  
Join Date: Oct 2003
Location: In hell, where did you think?
Posts: 11,776 Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level) 
Time spent in forums: 3 Weeks 5 Days 8 h 27 m 42 sec
Reputation Power: 470
Quote:
Originally Posted by A2k
So, what are the design implications of creating such a stored procedure, and what are the design implications of writing such an ASP script.

Your question itself is kind of vague.
What concerns do you have about doing this?
Reply With Quote
  #5  
Old February 18th, 2005, 05:01 PM
A2k's Avatar
A2k A2k is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Dec 2004
Posts: 166 A2k User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 18 h 44 m 10 sec
Reputation Power: 4
My main problem is this:

How do you pass a blob to a stored procedure with an asp script?

Can you just dump it into a SQL Server variable of type image?

A snippet of code may be able to shed some light..

Thanks again

alex
Reply With Quote
  #6  
Old February 18th, 2005, 05:31 PM
Memnoch's Avatar
Memnoch Memnoch is offline
Unholy Moderator
ASP Free God 14th Plane (11500 - 11999 posts)
  
Join Date: Oct 2003
Location: In hell, where did you think?
Posts: 11,776 Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level) 
Time spent in forums: 3 Weeks 5 Days 8 h 27 m 42 sec
Reputation Power: 470
There are numerous examples of storing BLOBS in SQL Server on google.

Yes, I would use the Image data type.
The Image data type just stores binary information.
Reply With Quote
  #7  
Old February 20th, 2005, 07:27 AM
A2k's Avatar
A2k A2k is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Dec 2004
Posts: 166 A2k User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 18 h 44 m 10 sec
Reputation Power: 4
Quote:
Originally Posted by Memnoch
There are numerous examples of storing BLOBS in SQL Server on google.

Yes, I would use the Image data type.
The Image data type just stores binary information.


Thanks for that useful comment... Here is one such example:

http://www.stardeveloper.com/articl...d.html?tid=1010
Reply With Quote
  #8  
Old February 20th, 2005, 01:55 PM
A2k's Avatar
A2k A2k is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Dec 2004
Posts: 166 A2k User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 18 h 44 m 10 sec
Reputation Power: 4
OK, so after much soul searching, and many a sware break, I have the following ASP code:

these are lines 42 through to 53(highlighted)
Code:
	cmdObj.CommandType = 4
    cmdObj.commandText = "exec sp_uploadNewFile"
	cmdobj.parameters.append cmdObj.createParameter("folderID", 3, 1, len(varFolderID), varFolderID)
	cmdObj.Parameters.Append cmdobj.createParameter("personID", 3, 1, len(varPersonID), varPersonID)
	cmdObj.Parameters.Append cmdobj.createParameter("projectID", 3, 1, len(varProjectID), varProjectID)
	cmdObj.Parameters.Append cmdObj.createParameter("fileName", 12, 1, len(varFileName), varFileName)
	cmdObj.Parameters.Append cmdobj.createParameter("description", 200, 1, len(varFileDesc), varFileDesc)
	cmdObj.Parameters.Append cmdobj.createParameter("@contentType", 200, 1, len(varContentType), varContentType)
	cmdObj.Parameters.Append cmdobj.createParameter("@theFile", 204, 1, varFileSize)

	cmdObj.Parameters("theFile").AppendChunk varTheFile
	cmdObj.Execute


and the stored procedure I had written earlier
Code:
ALTER   procedure sp_uploadNewFile (
	@folderID numeric,
	@personID numeric,
	@projectID numeric,
	@fileName nvarchar(255),	
	@theFile image,
	@description nvarchar(255)=null,
	@contentType nvarchar(255)) as

insert into [file] values(@folderid, @personID, @projectID, @fileName, 0.00, 0, @theFile, @description, @contentType)


and finally the error:

error '80040e14'

/vm/modules/genUpload.asp, line 53

OK so two questons:

1) what would cause this error? I have checked the opperation of the stored procedure in Query analyzer, and have checked that the variables (varPersonID etc...) have values execpt varFileDesc because this is an optional field.

2) how can I see what the query string that is being executed looks like?
Reply With Quote
  #9  
Old February 27th, 2005, 03:41 PM
A2k's Avatar
A2k A2k is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Dec 2004
Posts: 166 A2k User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 18 h 44 m 10 sec
Reputation Power: 4
WOOT! I've fixed the problem...

After many more sware breaks, fully aimed at microsoft for such a clumsy interface. I did the following to remadiate the issue:

update my Microsoft Data Access Components from what ever version I had to v2.8. This then gave me some error messages at least. rather than just an error code.

The other issue that I discovered, is that using the append parameter method, is that ADO doen't care about what you name your SQL parameters, you have to supply the parameters in the order that you have placed them in the stored procedure. I was trying to put them create the parameters in a manner to that looked nice and logical from a coding point of view, and ADO didn't like it.

Any how all sorted now... The code above is correct an should work, and can be adapted how you wish.

MODS: can I have reputation for my self please!
Reply With Quote
Reply

Viewing: ASP Free Forums > Database > Microsoft SQL Server > Blob I I

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 3 hosted by Hostway. 
Stay green...Green IT