David,
Here is my test code. This run's fine without the output parameter from
my stored procedure.
**Source code:**
OPTIONS INFORMATION
*
$INCLUDE UNIVERSE.INCLUDE ODBC.H
*
UCMD = ''
*
STATUS = SQLAllocEnv(DBENV)
STATUS = SQLAllocConnect(DBENV,CONENV)
STATUS = SQLConnect(CONENV,'sagefin_old_odbc','webuser','webuser')
STATUS = SQLAllocStmt(CONENV,STMTENV)
UCMD = "{call sp_insert_cmtest ('1*40888','GARY','WILLIAMS','11111
SPRING RD.','MOORPARK','CA','93021','USA')}"
STATUS = SQLPrepare(STMTENV,UCMD)
STATUS = SQLExecute(STMTENV)
CRT 'Status SqlExecute ':STATUS
CRT 'SQL.SUCCESS ':SQL.SUCCESS
STATUS = SQLFreeStmt(STMTENV,SQL.DROP)
STATUS = SQLDisconnect(CONENV)
STATUS = SQLFreeConnect(CONENV)
STATUS = SQLFreeEnv(DBENV)
END
**Output:**
Status SqlExecute 0
SQL.SUCCESS 0
**My sproc with the output parameter commented out:**
CREATE PROCEDURE [dbo].[sp_insert_cmtest]
@cmid as varchar(50),
@fname as varchar(50),
@lname as varchar(50),
@addr1 as varchar(50),
@city as varchar(50),
@state as varchar(50),
@zip as varchar(50),
@country as varchar(10)
[EMAIL PROTECTED] as varchar(50) OUTPUT
AS
SET NOCOUNT ON
INSERT INTO
cmtest (id,firstname,lastname,address1,city,state,zip,country)
VALUES
(@cmid,@fname,@lname,@addr1,@city,@state,@zip,@country)
--SELECT @id_out = @cmid
GO
My test source:
OPTIONS INFORMATION
*
$INCLUDE UNIVERSE.INCLUDE ODBC.H
*
UCMD = ''
DBENV = '' ; STMTENV = '' ; CONENV = ''
TESTCOL = ''
*
STATUS = SQLAllocEnv(DBENV)
STATUS = SQLAllocConnect(DBENV,CONENV)
STATUS = SQLConnect(CONENV,'sagefin_old_odbc','webuser','webuser')
STATUS = SQLAllocStmt(CONENV,STMTENV)
UCMD = "{call sp_insert_cmtest ('1*40999','GARY','WILLIAMS','11111
SPRING RD.','MOORPARK','CA','93021','USA')}"
STATUS = SQLPrepare(STMTENV,UCMD)
STATUS = SQLExecute(STMTENV)
CRT 'Status SqlExecute ':STATUS
CRT 'SQL.SUCCESS ':SQL.SUCCESS
STATUS = SQLFetch(STMTENV)
STATUS = SQLBindCol(STMTENV,1,SQL.B.CHAR,TESTCOL)
STATUS = SQLFreeStmt(STMTENV,SQL.DROP)
STATUS = SQLDisconnect(CONENV)
STATUS = SQLFreeConnect(CONENV)
STATUS = SQLFreeEnv(DBENV)
END
Output:
Status SqlExecute -1
SQL.SUCCESS 0
So, on the 2nd test with the output parameter enabled in the sproc, the
insert fails.
So I guess my question is, how do I get the output from the sproc into
my UV program from a stored procedure.
Any suggestions would be greatly appreciated.
Thx,
Rudy
<<Date: Thu, 16 Mar 2006 23:14:44 +1100
From: "Hona, David S" <[EMAIL PROTECTED]>
Subject: RE: [U2] bci
Rudy
What does your code look like?
BTW...You should use SQLExecDirect when calling stored procedures, when
it doesn't need to be called repeatedly (as it is supposedly more
efficient). Check out the online help or BCI manuals for more info.
Here's a snippet of source code from a real sample of calling a stored
procedure...from BCI under UV...>>
Rudy Cooper
Information Technology
Project Technical Lead
Sage Publications
2455 Teller Road
Thousand Oaks, California 91320
Direct (805) 410-7724
-------
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
