INSERT into a SQL table in another namespace

Question

Question

Terri Tattan · May 16, 2016

#Field Tests #SQL #Caché

We have a routine in one namespace.

We have a Cache SQL table in a different namespace.

What is the syntax for inserting into this table from the routine in the other namespace?

Can this be done without using ZN?

Thanks.

Discussion (5)0

Log in or sign up to continue

Timothy Leavitt · May 16, 2016

You could map the package containing the class related to that table using a package mapping, and the globals containing the table's data using global mappings.

You can see which globals the class uses in its storage definition - since the entire package is mapped, it might make sense to add a global mapping with the package name and a wildcard (*).

After taking those steps, you can insert to the table the way you usually would, without any special syntax or using zn/set $namespace.

2 0

score 0 · Answer 1 · 2016-05-17T18:18:39-04:00

You can use ZN to change namespace, do the INSERT and then ZN back. The danger is trying to do stuff between the 2 namespaces with Objects. If you open an object in one namespace, then ZN to a different namespace and try to reference the object it will get an error.

score 1 · Answer 2 · 2016-05-18T06:34:58-04:00

There are lots of ways to do this:

1) Expose as stored procedure and call via the SQL gateway interface...

2) Wrap in a web service and return the results that way

3) ZN

4) Use extended syntax

5) Map the class / table / routine

score 0 · Answer 3 · 2020-10-14T06:18:21-04:00

you can use the class %RemoteResultSet to insert from another namespace.

Example:

set rs=##class(%RemoteResultSet).%New()
set rs.ConnectionString="localhost[1972]:NAMESPACE"
Do rs.Prepare("insert into tablename (property1,property2 ) values (?,?) ")
Do rs.Execute(var1,var2)
Do rs.Close()

score 0 · Answer 4 · 2020-10-16T04:23:07-04:00

This quick solution, alas, will not survive the upgrade to IRIS as %Library.RemoteResultSet class was excluded from IRIS.