Kbase P864: MS SQL Server 7, Latest 9.1Bpatch resolves Stored Procedures
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
05/02/2003 |
|
Status: Unverified
FACT(s) (Environment):
Progress 9.1B
FACT(s) (Environment):
Windows 32 Intel
FACT(s) (Environment):
Windows NT 32 Intel
FACT(s) (Environment):
ODBC DataServer
SYMPTOM(s):
Stored procedure OUTPUT and RETURN values are not set when the procedure is closed
The problem is seen when using the Merant 3.5 Microsoft SQL Server 7 driver
The problem is not seen when using the Merant 3.5 SQL Server 6.5 driver
Check the list of fixed bugs for the reference 20000908-026
CAUSE:
The Microsoft driver bug pertains to the use of non-forward-only cursors on stored procedures with output parameters. You do see data in the Progress proc-text-buffer as this technique does not use non-forward-only cursors.
The MS SQL Server 7 stored procedure should look like the following (its a modification on the 'pcust' stored procedure examples given in the Progress documentation. The procedure relies on the progress databases SPORTS or SPORTS2000):
/* modified pcust definition MS SQL7 */
CREATE PROCEDURE pcust (@num INT, @varout INT OUT) AS
SELECT customer.cust_num, customer.name FROM customer
WHERE customer.cust_num > @num
SELECT @varout = 1234
RETURN 4321
The following Progress 4GL code can be used to access the above MS SQL Server 7 stored procedure.
The proc-text-buffer contains Sports.customer information related to the customer number given as the input to the stored procedure. The OUPUT variable 'pcust.varout' should contain the value of 1234 and the RETURN variable 'stat' should contain 4321:
DEF VAR h AS INT INIT 0.
DEF VAR stat AS INT.
run STORED-PROC pcust h = PROC-HANDLE (70, OUTPUT 0 ).
IF ERROR-STATUS:ERROR
THEN DO:
MESSAGE "Stored Procedure failed to run".
END.
for each proc-text-buffer where proc-handle = h:
display proc-text-buffer.
end.
CLOSE STORED-PROCEDURE pcust stat = PROC-STATUS.
DISPLAY pcust.varout.
DISPLAY stat.