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]
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]
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 ...
"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 ...
ASKER
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 ...
"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 ...
ASKER
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 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
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... ^_^;;
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
You can return image, but you must set it by Extended stored procedure first. Write some C++ code ...
Good luck !
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
Let me know if you need more details.
Anthony
Or ADODB.Recordset.Fields("Fi eld 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
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.
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
I agree.
Anthony
ASKER
Thanx for fast response.
Br
Johan
Br
Johan
I didn't expect any points here. ^_^;; Thanks all. ^_^
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.