codefori / codefori/vscode-db2i

Support for multiple result sets from procedure

Open
#387 3 comments 0 reactions 3 assignees Claimed by @ThePrez View on GitHub
enhancement
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:
>
> ![Image](https://github.com/user-attachments/assets/84beaf2b-4618-42ad-9137-31966e023292)

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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.