Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 806
  • Last Modified:

Output parameter for a stored procedure

Hello

Is it possible to return the data from an Image column as an output parameter?

The DataFile column in table Application can contain an word, pdf or a text document.

Example
CREATE PROCEDURE [dbo].[getApplication]
@DataFile as Image OUTPUT
AS

SELECT @DataFile = DataFile FROM Application WHERE ID = 1
GO

Example Table def
CREATE TABLE [dbo].[Application] (
     [ID] [int] NOT NULL ,
     [DataFile] [image] NOT NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

0
johan_asplund
Asked:
johan_asplund
  • 6
  • 5
  • 3
  • +2
1 Solution
 
chaos_hooiCommented:
Yes... Here is the except from SQL Server Book Online.

OUTPUT
Indicates that the parameter is a return parameter. The value of this option can be returned to EXEC[UTE]. Use OUTPUT parameters to return information to the calling procedure. Text, ntext, and image parameters can be used as OUTPUT parameters. An output parameter using the OUTPUT keyword can be a cursor placeholder.
0
 
johan_asplundAuthor Commented:
Ok but when i create my stored procedure i get an error saying

"Error 409: The assignment operator operation cannot take a image datatyle as an argument."

I guess that i have to use something else instead of
... @DataFile = DataFile ...

0
 
johan_asplundAuthor Commented:
Yes the help says its possible to have image as a parameter but when i try to use it i get an error

"Error 409: The assignment operator operation cannot take a image data type as an argument"

I guess there is some other trick to use instead of

... @DataFile = DataFile ...

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
johan_asplundAuthor Commented:
Sorry for the 2 posts but i did not see that the first one was sent/saved.
I had more than one explorer open.

/Johan
0
 
chaos_hooiCommented:
I looked it up, you have to use a point to point at the image (TEXTPTR), and then use READTEXT to read the image off the column... here is an example from the MS SQL Book Online....

USE pubs
GO
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(pr_info)
   FROM pub_info pr INNER JOIN publishers p
      ON pr.pub_id = p.pub_id
      AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @ptrval 1 25
GO
0
 
chaos_hooiCommented:
I mean, use a pointer... not a point... ^_^;;
0
 
johan_asplundAuthor Commented:
But that means that i cant have a image parameter in my stored procedure?

i guess that the result of the READTEXT is a recordset?

i tried and that did not work.

CREATE PROCEDURE [dbo].[getApplication]
@DataFile as Image OUTPUT
AS
DECLARE @DataFilePtr varbinary(16)

SELECT @DataFilePtr = TEXTPTR(DataFile) FROM Application WHERE ID = 1

@DataFile = READTEXT db.Application @DataFilePtr 0 0

GO



/Johan
0
 
chaos_hooiCommented:
Sorry, I had been doing some reading, after your post, but cannot find anything to help you... if your image size is less than 8 kb, you can using substring of an image, which will return you a varbinary datatype...

"In general, use varbinary for storing binary data, unless the length of the data exceeds 8 KB, in which case you should use image. It is recommended that the defined length of a binary column be no larger than the expected maximum length of the binary data to be stored."

Actually, I read about WRITETEXT reading from a data to the column, so I assume READTEXT should be able to do the opposite... sigh... Really sorry... FYI, actually, you cannot create a temporary image variable...
0
 
johan_asplundAuthor Commented:
No problem Thanx for your time.

I guess that Microsoft should put a NOT in the text
"Text, ntext, and image parameters can be used as OUTPUT parameters." in SQL Server Book Online.

So then the answer to this this question must be No it is not possible unless you have a Image column with a small amount of data.


/Johan
0
 
ispalenyCommented:
You can return image, but you must set it by Extended stored procedure first. Write some C++ code ...

Good luck !
0
 
Anthony PerkinsCommented:
As a workaround consider using ADO's Stream object instead of an output parameter.

Let me know if you need more details.

Anthony
0
 
ispalenyCommented:
Or ADODB.Recordset.Fields("Field name").GetChunk, but it is more for some controls forum :)
0
 
Computer101Commented:
A request for deletion or PAQ has been made.  If no response or you feel this is in error, comment.  If no objection, myself or another Moderator will handle this question in three days.

Computer101
E-E Admin
0
 
ispalenyCommented:
Computer101,
  Do not delete it, because of it has some value. Especially chaos_hooi helped johan to find unsolvability of this question. Recover points and move this thread to PAQ. If johan wants to spare points, I award chaos_hooi by myself. But I will wait for johan's reply.

johan,
  it is possible (03/14/2003 06:41AM PST), but it is not so easy. I haven't write an extended stored procedure yet. I scheduled an refresh of my C++ knowledge to the next month :)
  Also acperkins offered a code replacement of the text parameter. It is better to send to your thread before you post to SC.
  But in a simple SQL, you cannot set a BLOB to a variable, as you have found and I tried it before, many times.
  chaos_hooi helped you to find the answer, even it is negative. If you think 500 points is too much, reduce the points.

Best regards, Ivo Spaleny.

0
 
Anthony PerkinsCommented:
ispaleny,

I agree.

Anthony
0
 
johan_asplundAuthor Commented:
Thanx for fast response.

Br
Johan
0
 
chaos_hooiCommented:
I didn't expect any points here. ^_^;; Thanks all. ^_^
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now