Link to home
Start Free TrialLog in
Avatar of johan_asplund
johan_asplund

asked on

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]

Avatar of chaos_hooi
chaos_hooi

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.
Avatar of johan_asplund

ASKER

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

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

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
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
I mean, use a pointer... not a point... ^_^;;
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
ASKER CERTIFIED SOLUTION
Avatar of chaos_hooi
chaos_hooi

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of ispaleny
You can return image, but you must set it by Extended stored procedure first. Write some C++ code ...

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

Let me know if you need more details.

Anthony
Or ADODB.Recordset.Fields("Field name").GetChunk, but it is more for some controls forum :)
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
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.

ispaleny,

I agree.

Anthony
Thanx for fast response.

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