Question
· Dec 4, 2023

PROCEDURE and ReturnResultsets

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

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

"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