Provide support for SQL shell keyword 'call' to execute/invoke stored procedures

[swils024]

The keyword 'call' isn't recognised in the /sql shell on webterminal. How do you then execute stored procedures from the shell?

The goal would be to run something like: call CustomNameSpace.myStoredProc('param1')

and produce something like the Caché Terminal output except with Web Terminal's nice table formatting

Possible workaround options: 1) copy the contents of the stored proc into the shell modifying replacing SQL params with actual values. 2) don't use the SQL shell in Web Terminal and use %SQL.Statement from the Objectscript shell

Workaround 1 does produce a nicely formatted table Workaround 2 doesn't produce a nicely formatted table with Do resultSet.%Display()

[nikitaeverywhere]

Hi @swils024! Thanks for your feedback!

/sql in WebTerminal is a simple wrapper around %ResultSet (see here). According to this doc,

%Library.ResultSet Does Not Support CALL

However, the doc says that

If the called routine is a function, %Library.ResultSet can use SELECT to invoke it (instead of CALL)

Please let me know if this helps! I tried using SELECT, it works, however, I am not sure whether it gives the right result:

[swils024]

Thanks for the documentation links.

The definition in ##class(Sample.Person).StoredProcTest(string,string) simply ignores the parameters passed and returns 29 via the Quit 29 statement. Dodgy hard-to-understand sample code I think!

I changed the definition slightly to return the reponse variable so an input of select Sample.Stored_Procedure_Test('Doe,John','')

returns Doe,John||Doe,John

The difference in my definition is that I have a Class Query defined as a SqlProc instead of a ClassMethod defined as a SqlProc

[swils024]

When trying to use select

CODE:SQL > select Code.AllSetsByDisp('BIO') Mistake in SQL statement, ERROR #5540: SQLCODE: -357 Message: User defined SQL function name 'CODE.ALLSETSBYDISP' references a procedure not defined as a function procedure

Query AllSetsByDisp(DepttoList As %String) As %SQLQuery(CONTAINID = 1) [ SqlName = AllSetsByDisp, SqlProc ] { // Some sql here }

[nikitaeverywhere]

@swils024, need some time to look deeper at this case. I think I can make use of %SQL.Statement instead in WebTerminal code, which seems to support CALL queries without complications.

Hey @eduard93 :) Any thoughts here? Does it makes sense to replace this %ResultSet with %SQL.Statement to support CALLs? Thanks!

[evshvarov]

Hi Nikita! It’s definitely worth to change ResultSet to SQL.Statement - just because it’s faster.

ср, 24 июля 2019 г. в 17:54, Nikita Savchenko notifications@github.com:

@swils024 https://github.com/swils024, need some time to look deeper at this case. I think I can make use of %SQL.Statement instead, which seems to support CALL queries https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_dynsql without complications.

Hey @eduard93 https://github.com/eduard93 :) Any thoughts here? Does it makes sense to replace this https://github.com/intersystems-community/webterminal/blob/4d6dc00c9e1c166a3f9b63092dd722812c4b98d5/src/cls/WebTerminal/Handlers.cls#L202 %ResultSet with %SQL.Statement to support CALLs?

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/intersystems-community/webterminal/issues/126?email_source=notifications&email_token=AAVHEP3X3DJCP3FTDXTX3ATQBBUMFA5CNFSM4IGOH732YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD2WTDRY#issuecomment-514666951, or mute the thread https://github.com/notifications/unsubscribe-auth/AAVHEPZVYB35PKCZO2C7YE3QBBUMFANCNFSM4IGOH73Q .

[nikitaeverywhere]

Alright, now this and all SQL.Statement-powered things work well with /sql. Update WebTerminal to v4.9.0

[eduard93]

Late to the party but it should have been:

SELECT * FROM proc

and not:

SELECT proc

Anyway move to SQL statement is great.