Question
Terri Tattan · May 16, 2016

INSERT into a SQL table in another namespace

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.

00
3 0 5 929

Replies

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.

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.

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

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()
 

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