PROCEDURE and ReturnResultsets
Hi folks,
I made a solution (https://openexchange.intersystems.com/package/iris-pretty-gpt-1) and want to use it like
CREATEFUNCTION ChatGpt(INpromptVARCHAR)
RETURNSVARCHARPROCEDURELANGUAGE OBJECTSCRIPT
{
return ##class(dc.irisprettygpt.main).prompt(prompt)
}
CREATETABLE people (
nameVARCHAR(255),
city VARCHAR(255),
age INT(11)
)
INSERTINTO 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.cl…
Can you tell me how this can be implemented? Then it would be very convenient to generate test data
Comments
I think that to return multiple values a stored procedure should return a resultset (i.e.: method signature must contains ReturnResultsets keyword).
HTH,
Enrico
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.
DROPTABLEIFEXISTS demo_person;
DROPPROCEDUREIFEXISTS multi_results;
CREATETABLE demo_person (nameVARCHAR(20), age INT, home_city VARCHAR(40));
CREATEPROCEDURE multi_results() RESULTSETSLANGUAGE 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) AffectedThe 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 |
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()What does sqlStatus contains?
Write $system.Status.GetErrorText(sqlStatus)
Enrico
"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
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.