New post
Question
Alice Shrestha · Nov 27, 2020

Problem trying to execute sqlproc inside multiple folders/package

#Code Snippet #Caché

Hi,

I seem to be able to execute sqlproc using the convention defined here https://docs.intersystems.com/latest/csp/docbook/Doc.View.cls?KEY=GSQL_q... provided the package/folder is a single level. As, soon I have a nested folder structure I get an error while trying to execute this. 

For instance, 

select id, Utils.Users_getRole(id) roles from users.users

works fine while, 

select id, com.xyz.utils.Users_getRole(id) from users.users
does not work and gives,  "User defined SQL function ... does not exist"

I am on Cache 2017. Is there a workaround to making this work? 

0
0 114
Discussion (3)0
Log in or sign up to continue
Eduard Lebedyuk · Nov 27, 2020

Try

SELECT
    id,
    com_xyz_utils.Users_getRole(id)
FROM users.users

Note that package is underscored.

1 0
Alice Shrestha · Nov 27, 2020

That's great! Thanks for the quick reply. 

0 0
Eduard Lebedyuk · Nov 28, 2020

To be honest despite about my 7+ years of experience in exposing class methods as sql procedures I've yet to write a name of a resulting sql procedure correctly.

The trick I use is to open the list of procedures in SMP:

In this list I search for an already existing procedure with the same nesting level, copy it and change identifiers to my values.

That's how I wrote your query.

First I copied: %Atelier_v1_Utils.Extension_AfterUserAction

Then replaced %Atelier_v1_Utils with com_xyz_utils

Finally replaced Extension_AfterUserAction with Users_getRole

1 0