codefori / codefori/vscode-db2i
Support for multiple result sets from procedure
- Dominant language
- TypeScript
- Stars
- 76
- Forks
- 54
- Avg merge
- 17m
- Merged PRs (30d)
- 1
Description
> It is possible to return two cursors from SQL procedures, and in ACS, two tabs can be shown for each of them:
>
> 
from _Julie Miranda_
Example
```sql
-------------------------------------------------------------------------------
-- Module - SPTESTING
-- Description - SQL Stored Procedure
-- Example of two open cursors for VSCode
-- Used SAMPLE library, EMPLOYEE table
-- Created - 04/29/2025 Julie Miranda
-------------------------------------------------------------------------------
-- MODIFICATIONS |
-----------------+
-- Mod Date By Description
-- ---- ---------- -------- --------------------------------------------------
-- JMM1 04/29/25 JMiranda Initial Creation
-------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE TwoCursorTesting_SPTESTING
(
IN P_EMPLOYEE CHAR(6)
)
SPECIFIC SPTESTING
LANGUAGE SQL
DYNAMIC RESULT SET 2 -- Must match to opened cursors being returned
SET OPTION DATFMT = *ISO
,DBGVIEW = *SOURCE
,OUTPUT = *PRINT
BEGIN
-------------------------------------------------------------------------------
-- Variable Declaration Section
-------------------------------------------------------------------------------
DECLARE SQLCODE INT DEFAULT 0;
DECLARE SQLSTATE CHAR(5);
DECLARE V_SQLSTATE CHAR(5);
DECLARE V_Status_Flag decimal(1,0);
DECLARE V_MESSAGE_TEXT VARCHAR(100);
-------------------------------------------------------------------------------
-- Cursor Declaration Section
-------------------------------------------------------------------------------
-- Determine if customer numbers are valid
DECLARE SPTESTING_Message_CSR CURSOR
WITH HOLD
WITH RETURN TO CLIENT FOR
select
*
from table
(
values
(v_status_flag, v_message_text))
as t (status_flag, message_text);
-- Cart Information Values for Customer
DECLARE SPTESTING_Employee_CSR CURSOR
WITH HOLD
WITH RETURN TO CLIENT FOR
SELECT
empno as employee_number
,firstnme as first_name
,lastname
FROM sample.employee
WHERE EMPNO = P_EMPLOYEE
;
-------------------------------------------------------------------------------
if p_EMPLOYEE is NOT NULL AND
p_EMPLOYEE <> '' THEN
if not exists (select 1 from sample.employee
where empno = p_employee) then
set v_status_flag = 0 ;
set v_message_text = 'NOTSUCCESS';
end if;
end if;
if v_message_text IS NULL or v_message_text = ' ' THEN
set v_status_flag = 1 ;
set v_message_text = 'SUCCESS';
end if;
-------------------------------------------------------------------------------
-- Open Cursors to return to stored procedures
-------------------------------------------------------------------------------
OPEN SPTESTING_Message_CSR;
OPEN SPTESTING_Employee_CSR;
-------------------------------------------------------------------------------
-- Return all record sets back to client
-------------------------------------------------------------------------------
RETURN 0;
END
```
We need to investigate at multiple angles:
1. What does the Mapepire-server protocol do with this result after execution?
2. vscode-db2i does not ever expect more than one result set in the UI. Changes will need to be made based on what Mapepire-server returns.
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.