Passing byte array to stored procedure

[dRaVEn]

Hi....

I'd like to be able to pass a byte array from Visual Basic to a stored
procedure
in SQL Server 2K. How do I define the input parameter to the store proc as a
byte
array? Also, how can the byte array be subsequently read in the stored proc
?

Any pointers to sample code or websites would be much appreciated.

Thanx.

[Uri Dimant]

dRaVEn ,
You cannot pass array to stored procedure
This is an example of Joe Celco.
CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);

INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
-- ...
select * from InputStrings
select * from Parmlist
> ---This will be the table that gets the outputs, in the form of the
> ---original key column and one parameter per row.
select identity(int,1,1) as seq
into Sequence
from master..sysobjects
select * from Sequence

CREATE TABLE Parmlist
(keycol CHAR(5) NOT NULL PRIMARY KEY,
parm INTEGER NOT NULL);

> ---It makes life easier if the lists in the input strings start and end
> ---with a comma. You will also need a table called Sequence, which is a
> ---set of integers from 1 to (n).
>
SELECT keycol,
CAST (SUBSTRING (',' + I1.input_string + ',', MAX(S1.seq + 1),
(S2.seq - MAX(S1.seq + 1)))
AS INTEGER),
COUNT(S2.seq) AS place
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' + I1.input_string + ',' , S1.seq, 1) = ','
AND SUBSTRING (',' + I1.input_string + ',' , S2.seq, 1) = ','
AND S1.seq < S2.seq
AND S2.seq <= LEN(I1.input_string) + 2
GROUP BY I1.keycol, I1.input_string, S2.seq
ORDER BY I1.keycol, I1.input_string, S2.seq

dRaVEn
dRaVEn <v...@retcol.com> wrote in message
news:eAy#nwOTCHA.1672@tkmsftngp12...

[Tibor Karaszi]

Depending on what you want to do with it, you can pass a comma separated list and parse out the
elements in the proc. You might even has a UDF that does the parsing. See, for example:
http://www.algonet.se/~sommar/dynamic_sql.html

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver

"dRaVEn" <v...@retcol.com> wrote in message news:eAy#nwOTCHA.1672@tkmsftngp12...

[Yves Breton]

You could use a text (or ntext) parameter to forward a xml to the stored
procedure like :
'<Params><Row><Param1>value</Param1><Param2>other
value</Param2></Row><Row><Param1>value
next</Param1><Param2>other...</Param2></Row></Params>'

In the stored proc, you just have to use sp_xml_preparedocument and with a
for openxml you can retrieve your parameters. (never foget to call
sp_xml_removedocument).

the syntaxe is like this :

create procedure XML_Array( @xml ntext )
as
begin
declare @hDoc int
exec sp_preparedocument @hDoc Output, @xml

select
parameter1
, parameter2
from openxml ( @hDoc, N'Params/Row')
with
(
parameter1 varchar(40) 'Param1'
, parameter2 varchar(40) 'Param2'
)

exec sp_removedocument @hDoc
end

just call XML_Array N'<Params><Row><Param1>value</Param1><Param2>other
value</Param2></Row><Row><Param1>value
next</Param1><Param2>other...</Param2></Row></Params>'...

I use varchar in this example, but you can use int, tinyint instead.

Yves Breton

"Uri Dimant" <ur...@iscar.co.il> wrote in message
news:e7mvz2OTCHA.2024@tkmsftngp08...

[aSH]

Thanx for all the help.

I am trying out some of the ways suggested. What I'm actually trying to
achieve
is to save an image to to the table via a stored procedure. How do I pass
the
image to a stored proc ?

I am aware that the ADO Stream object can be used but I need to do this
via a stored proc.

Cheers,
Dinesh

"dRaVEn" <v...@retcol.com> wrote in message
news:eAy#nwOTCHA.1672@tkmsftngp12...

[Tibor Karaszi]

Can't you just pass the data as an image parameter?

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver

"aSH" <vali...@post1.com> wrote in message news:udulvyPTCHA.2012@tkmsftngp13...

[Joel Askey]

Don't forget using a single NText parameter containing an XML DOM, which you
can parse via OPENXML...

"dRaVEn" <v...@retcol.com> wrote in message
news:eAy#nwOTCHA.1672@tkmsftngp12...

[Ivan Demkovitch]

Hi!

You can use stored procedure Image parameter to pass data into stored
procedure.

"aSH" <vali...@post1.com> wrote in message
news:udulvyPTCHA.2012@tkmsftngp13...

[Bob Pfeiff [MS]]

This VB 6 code puts an image into SQL Server (table and stored proc DDL is
commented in the code):

Sub Main()

'Import a file from the file system to SQL Server via stored procedure call

Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Dim adoParam As ADODB.Parameter
Dim adoStrm As ADODB.Stream
Dim arrImage() As Byte

Set adoConn = New ADODB.Connection
Set adoCmd = New ADODB.Command
Set adoStrm = New ADODB.Stream

'open connection to SQL Server
adoConn.Open "Provider=SQLOLEDB.1;Data Source=rpfeiffm700;Initial
Catalog=sandbox;Integrated Security=SSPI;App=ImageImport"

'imagetable definition:
'create table imagetable
'(
'ident int identity(1, 1) not null,
'imagedata image
')

'proc definition
'create proc p_insertimage
'@image image
'as
'insert Into
' image_table
' (
' image_data
' )
'values
' (
' @image
' )
'return

'set up stream object to binary and open it to read a file

adoStrm.Type = adTypeBinary

adoStrm.Open

adoStrm.LoadFromFile "C:\Documents and Settings\rpfeiff\My Documents\My
Pictures\VSNet.bmp"

arrImage = adoStrm.Read

Set adoCmd = New ADODB.Command

With adoCmd
.ActiveConnection = adoConn
.CommandText = "p_insertimage"
.CommandType = adCmdStoredProc
Set adoParam = .CreateParameter("@image", adLongVarBinary,
adParamInput, -1, arrImage)
.Parameters.Append adoParam
.Execute
End With

'clean up

adoStrm.Close
adoConn.Close

Set adoCmd = Nothing
Set adoStrm = Nothing
Set adoConn = Nothing
End Sub

--
Bob
Microsoft Consulting Services
------
This posting is provided AS IS with no warranties, and confers no rights.

[Greg Linwood]

this is a nice solution..

Cheers,
Greg Linwood

"Yves Breton" <ybr...@sgt.fr> wrote in message
news:akd59b$mne$1...@wanadoo.fr...