SQL Server stored prcedures with output parameters

**Steve Holden** · Jul 18 '05, 05:47 PM

Re: SQL Server stored prcedures with output parameters

Steve Holden wrote:
[color=blue]
> Has anyone, with any driver whatsoever, managed to retrieve output
> parameters from a SQL Server stored procedure? I've just been rather
> embarrassed to find out it's not as easy as it might seem, and people
> are saying bad things about Python as a result :-(
>
> mx.ODBC, which I regard as a highly-capable module, does not support the
> callproc() API, and suggests use of the ODBC call format. It has caveats
> in (some of) the documentation implying that output parameters cannot be
> handled due to their indeterminate memory requirements, however.
>
> The adodbapi module does have a stored procedure call in its unit tests,
> and manages to pass that test, but when I call my own stored procedure
> (which retrieves columns into the output parameters rather than using
> SET to establish their values) I get back whatever value I supplied as
> the output parameter rather than what the stored procedure is returning.
>
> So, can anyone help me to fulfill what must be a very common database
> requirement?
>
> regards
> Steve[/color]

Hmmm, following up, added another test to adodbapi to retrieve a value
from a table into an output parameter it works, so this puts the
suspicion on to the stored procedure. Since this was written by the
client I'll take a look at it later (when I'm back in the office) and
see what the problem might be.

happily-talking-away-to-myself-ly y'rs - steve
--

Opalstack: no site here.... yet!

http://www.holdenweb.com

http://pydish.holdenweb.com

Holden Web LLC +1 800 494 3119

**Steve Holden** · Jul 18 '05, 05:47 PM

Re: SQL Server stored prcedures with output parameters

Steve Holden wrote:
[color=blue]
>
> Hmmm, following up, added another test to adodbapi to retrieve a value
> from a table into an output parameter it works, so this puts the
> suspicion on to the stored procedure. Since this was written by the
> client I'll take a look at it later (when I'm back in the office) and
> see what the problem might be.
>
> happily-talking-away-to-myself-ly y'rs - steve[/color]

However, the stored procedure below appears to consistently return zero
for the link_id when called with:

columnist_id, url, title, description, link_id = \
curs.callproc(" sp_InsertArticl e",
(columnist_id, url, title, description, 0))

so maybe there really *is* something wrong. Or, hopefully, I'm just
making a simple mistake in the calls? Whatever value is used as the
fifth (output) parameter seems to get returned, despite the apparent
change in the stored procedure.

Create PROCEDURE dbo.sp_InsertAr ticle
(
@columnist_id int,
@url varchar(255),
@title varchar(99),
@description text,
@link_id int OUTPUT
)
AS
BEGIN
INSERT INTO link (columnist_id,u rl,title,descri ption)
VALUES (@columnist_id, @url,@title,@de scription)

SELECT @link_id = @@IDENTITY
END

GO

not-quite-so-happi-ly y'rs - steve
--

Opalstack: no site here.... yet!

http://www.holdenweb.com

http://pydish.holdenweb.com

Holden Web LLC +1 800 494 3119

**Aahz** · Jul 18 '05, 05:47 PM

Re: SQL Server stored prcedures with output parameters

In article <HHWmd.10924$nj .1324@lakeread0 1>,
Steve Holden <steve@holdenwe b.com> wrote:[color=blue]
>
>Has anyone, with any driver whatsoever, managed to retrieve output
>parameters from a SQL Server stored procedure? I've just been rather
>embarrassed to find out it's not as easy as it might seem, and people
>are saying bad things about Python as a result :-(
>
>mx.ODBC, which I regard as a highly-capable module, does not support
>the callproc() API, and suggests use of the ODBC call format. It has
>caveats in (some of) the documentation implying that output parameters
>cannot be handled due to their indeterminate memory requirements,
>however.[/color]

Keep in mind that it's been several years since I used Python with SQL
Server and about all I remember is that we used stored procedures
heavily with no problems. I have no clue whether we tried using stored
procedures with output parameters, but I wonder why you can't just use a
SELECT to call the stored procedure and return the values. Or at least
right another stored proc that does something roughly similar.
--
Aahz (aahz@pythoncra ft.com) <*> http://www.pythoncraft.com/

WiFi is the SCSI of the 21st Century -- there are fundamental technical
reasons for sacrificing a goat. (with no apologies to John Woods)

**Aahz** · Jul 18 '05, 05:48 PM

Re: SQL Server stored prcedures with output parameters

In article <cnif70$an1$1@p anix1.panix.com >, Aahz <aahz@pythoncra ft.com> wrote:[color=blue]
>
>Keep in mind that it's been several years since I used Python with SQL
>Server and about all I remember is that we used stored procedures
>heavily with no problems. I have no clue whether we tried using stored
>procedures with output parameters, but I wonder why you can't just use a
>SELECT to call the stored procedure and return the values. Or at least
>right another stored proc that does something roughly similar.[/color]

s/right/write/, but you knew that.
--
Aahz (aahz@pythoncra ft.com) <*> http://www.pythoncraft.com/

WiFi is the SCSI of the 21st Century -- there are fundamental technical
reasons for sacrificing a goat. (with no apologies to John Woods)

**Brian McErlean** · Jul 18 '05, 05:52 PM

Re: SQL Server stored prcedures with output parameters

Steve Holden <steve@holdenwe b.com> wrote in message news:<oSYmd.109 31$nj.6308@lake read01>...[color=blue]
> Steve Holden wrote:
>[color=green]
> >
> > Hmmm, following up, added another test to adodbapi to retrieve a value
> > from a table into an output parameter it works, so this puts the
> > suspicion on to the stored procedure. Since this was written by the
> > client I'll take a look at it later (when I'm back in the office) and
> > see what the problem might be.
> >
> > happily-talking-away-to-myself-ly y'rs - steve[/color]
>
> However, the stored procedure below appears to consistently return zero
> for the link_id when called with:
>
> columnist_id, url, title, description, link_id = \
> curs.callproc(" sp_InsertArticl e",
> (columnist_id, url, title, description, 0))
>
> so maybe there really *is* something wrong. Or, hopefully, I'm just
> making a simple mistake in the calls? Whatever value is used as the
> fifth (output) parameter seems to get returned, despite the apparent
> change in the stored procedure.
>
> Create PROCEDURE dbo.sp_InsertAr ticle
> (
> @columnist_id int,
> @url varchar(255),
> @title varchar(99),
> @description text,
> @link_id int OUTPUT
> )
> AS
> BEGIN
> INSERT INTO link (columnist_id,u rl,title,descri ption)
> VALUES (@columnist_id, @url,@title,@de scription)
>
> SELECT @link_id = @@IDENTITY
> END
>
> GO
>[/color]

I had a similar problem doing this once before that I think was
related to the connection string. Changing to use the SQLOLEDB
provider solved it. I've just tested the below script here and got
the expected results:

Stored procedure:

CREATE PROCEDURE TestSP
@param INTEGER OUTPUT
AS
BEGIN
select @param = 10
END

Python test:

import adodbapi

server='localho st'
dbname='brian'
user='sa'
password=''

db=adodbapi.con nect('Provider= SQLOLEDB.1;Data Source=%s;Initi al
Catalog=%s;User ID=%s;Password= %s;'%(server, dbname,user,pas sword))
cur = db.cursor()
print cur.callproc('T estSP',(999,))
[10]

--
Brian

SQL Server stored prcedures with output parameters

SQL Server stored prcedures with output parameters

Comment

Comment

Comment

Comment

Comment