PROCEDURE and ReturnResultsets

Question

Question

Alexey Nechaev · Dec 4, 2023

Hi folks,

I made a solution (https://openexchange.intersystems.com/package/iris-pretty-gpt-1) and want to use it like

CREATE FUNCTION ChatGpt(IN prompt VARCHAR)
RETURNS VARCHAR
PROCEDURE
LANGUAGE OBJECTSCRIPT
{
    return ##class(dc.irisprettygpt.main).prompt(prompt)
}


CREATE TABLE people (
name VARCHAR(255),
city VARCHAR(255),
age INT(11)
)


INSERT INTO people ChatGpt("Make a json file with 100 lines of structure [{'name':'%name%', 'age':'%age%', 'city':'%city%'}]")

I know that a FUNCTION can only return one result. But it seems that the PROCEDURE can return several values and then in theory it should work as in the example above

https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...

Can you tell me how this can be implemented? Then it would be very convenient to generate test data

Product version: IRIS 2023.2

Discussion (6)3

Log in or sign up to continue

score 1 · Answer 1 · 2023-12-04T03:44:19-05:00

I think that to return multiple values a stored procedure should return a resultset (i.e.: method signature must contains ReturnResultsets keyword).

Have a look to this post.

HTH,

Enrico

score 1 · Answer 2 · 2023-12-04T08:40:41-05:00

I came up with this quick and dirty example. I did discover a problem when I executed this using the Database Console in IntelliJ - evidently the IRIS Server requires statement results to be only ResultSets! (I've reported that problem). If the result of executing the insert statements is not added to the %sqlcontext then this works through client connections, otherwise an error is reported. I'll include the result of executing this from the command line and then displaying the results. Also, the results from executing this from a client with the insert results suppressed.

DROP TABLE IF EXISTS demo_person;
DROP PROCEDURE IF EXISTS multi_results;

CREATE TABLE demo_person (name VARCHAR(20), age INT, home_city VARCHAR(40));

CREATE PROCEDURE multi_results() RESULT SETS
    LANGUAGE OBJECTSCRIPT
{
    set ins = $system.SQL.Prepare("insert into demo_person(name, age, home_city) VALUES(?,?,?)")
    do $system.SQL.Execute("TRUNCATE TABLE demo_person")
    do %sqlcontext.AddResultSet(ins.execute("Dan", 25, "Miami"))
    do %sqlcontext.AddResultSet(ins.execute("Jorge", 32, "Tampa"))
    do %sqlcontext.AddResultSet(ins.execute("Enrico", 29, "Turin"))
    do %sqlcontext.AddResultSet(ins.execute("Alexy", 21, "London"))
    do %sqlcontext.AddResultSet($system.SQL.Execute("SELECT name, age, home_city FROM demo_person ORDER BY age DESC"))
};

CALL multi_results();

USER>set result = $system.SQL.Execute("call multi_results()")

USER>do result.%Display()

Dumping result #1
1 Row Affected

Dumping result #2
1 Row Affected

Dumping result #3
1 Row Affected

Dumping result #4
1 Row Affected

Dumping result #5
name    age    home_city
Jorge    32    Tampa
Enrico    29    Turin
Dan    25    Miami
Alexy    21    London

4 Rows(s) Affected

The results when not adding the insert results as displayed by a database console:

name	age	home_city
Jorge	32	Tampa
Enrico	29	Turin
Dan	25	Miami
Alexy	21	London

score 0 · Answer 3 · 2023-12-05T10:17:51-05:00

Thank you very much for your response, unfortunately this doesn’t work for me - CALL returns an empty result, I call the command through my REST API (it uses this)

Set sql = ##class(%SQL.Statement).%New()

Set sqlStatus = sql.%Prepare("call multi_results()")

Set sqlResult = sql.%Execute()

score 0 · Answer 4 · 2023-12-05T11:06:21-05:00

Enrico Parisi · Dec 5, 2023

What does sqlStatus contains?

Write $system.Status.GetErrorText(sqlStatus)

Enrico

0 0

score 1 · Answer 5 · 2023-12-05T11:34:50-05:00

"CALL returns an empty result"

How did you determine that? After that 3 lines, run this:

For  {Set rset=sqlResult.%NextResult() q:rset=""  do rset.%Display() Write !}

I get:

1 Row Affected
1 Row Affected
1 Row Affected
1 Row Affected
name age home_city
Jorge 32 Tampa
Enrico 29 Turin
Dan 25 Miami
Alexy 21 London

4 Rows(s) Affected

You may want to check the documentation:

Returning Multiple Result Sets

Enrico

score 0 · Answer 6 · 2023-12-06T08:58:53-05:00

Can you please zwrite sqlResult? It should be an instance of %Library.ProcedureContext. It has properties for sqlcode, message, result set sequence, etc. Knowing what is there will be very helpful.