cache sql insert into external database

My new question regards connecting to the external database to insert the data. I have the following code:

#include %occInclude
// winsurge6
// insert winsurge data into cancer registry database
// Create new Gateway connection object
set gc=##class(%SQLGatewayConnection).%New()
If gc=$$$NULLOREF quit $$$ERROR($$$GeneralError,"Cannot create %SQLGatewayConnection.")
  
//Make connection to target DSN
s pDSN="CGDEV"
s usr="WINSURGE_DMP"
s pwd="xxxxxxxx"
s sc=gc.Connect(pDSN,usr,pwd,0)

If $$$ISERR(sc) quit sc
if gc.ConnectionHandle="" quit $$$ERROR($$$GeneralError,"Connection failed")
  
set sc=gc.AllocateStatement(.hstmt)
if $$$ISERR(sc) quit sc

EmbeddedSQLInsertHostVarArray

#SQLCompile Path=canreg
//#SQLCompile Path=WINSURGE_DMP
NEW SQLCODE
 
&sql(INSERT INTO WINSURGE_RESULT_FACT VALUES :FIELD())
   IF SQLCODE=0 {
     WRITE !,"Insert succeeded"
     QUIT }
ELSEIF SQLCODE=-119 {
       WRITE !,"Duplicate record not written"
       QUIT }
ELSE {
     WRITE !,"Insert failed, SQLCODE=",SQLCODE }   
B "S"

When compiling it, I'm receiving the following error message:

Compiling routine : winsurge6.MAC

ERROR:winsurge6.MAC(30) : SQLCODE=-30 : Table 'WINSURGE_RESULT_FACT' not found within schemas: CANREG,SQLUSER
 
Detected 1 errors during compilation in 0.026s.

I'm not sure what this error indicates. I've tried a few different things regarding the  #SQLCompile Path=canreg  and the

&sql(INSERT INTO WINSURGE_RESULT_FACT VALUES :FIELD())  sections of code. I'm not sure what needs to be changed.

Thank you in advance.

Mike

  • 0
  • 77
  • 8
  • 1

Answers

In order to use a table in an external DB you need to LINK this table to your Caché instance.
There's a Wizard in Mgmt Portal  System > SQL > Wizards > Link Table

It connects to your external table using  SQLgateway and creates a proxy class in your namespace
that presents the table with all SQLnames ...( underscores, ...) as if it was a local table but with a special external storage

Then you use this proxy class as you would do with a local one.
Table 'WINSURGE_RESULT_FACT' should then be visible and accessible. 

It might be somewhat slower than Globals  wink

Thank you Robert for your response.

I used the link table wizard as you suggested and came up with winsurge_dmp.winsurge_result_fact.

When I try to compile my code, I'm getting an sqlcode=-30 error indicating that table doesn't exist. I think my error is somewhere in this section of the code, but after trying several different things, I can't seem to be able to figure it out.


#SQLCompile Path=cancreg
//#SQLCompile Path=WINSURGE_DMP
NEW SQLCODE
 
&sql(INSERT INTO WINSURGE_DMP.WINSURGE_RESULT_FACT VALUES :FIELD())

Any help will be appreciated.

Mike

#1) check that there exists a valid class WINSURGE*...   

#2) in Mgmt Portal > System Explorer > SQL you should see as Table WINSURGE_DMP.WINSURGE_RESULT_FACT

#3) #SQLCompile Path=cancreg 
seems the real source of your problem as it sets a default package 
cancreg  wherever that may come from

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GCOS_macros#GCOS_macros_mpp_lbSQLCompile_Path

Eliminate it as your table is already full qualified  WINSURGE_DMP.WINSURGE_RESULT_FACT

In addition :  - VALUES :FIELD()
do you really write to ALL fields of the external table ??? 

Hint:

try to execute your SQL statement first from Mgmt Portal using dummy values before coding with strange macros directives

start with a simple statement to see if the connection works as expected:

SELECT COUNT(*) FROM WINSURGE_DMP.WINSURGE_RESULT_FACT

then try:

INSERT INTO WINSURGE_DMP.WINSURGE_RESULT_FACT VALUES (1,2,3,4,5,6)   -- whatever it needs

 

Robert, thank you for all your help.

I ran the following in the management portal

INSERT INTO WINSURGEDMP.WINSURGERESULTFACT VALUES (1,2,3,4,5,6)

and received the following error

ERROR #5540: SQLCODE: -30 Message: Table 'WINSURGEDMP.WINSURGERESULTFACT' not found

the -30 indicates that  the table doesn't exist. However it does show up in the list of classes:

I try running the program and it gives the same error.

Any ideas?

Mike

Sorry you went to CLASSES   not to SQL !!! 
These are different worlds with different rules and syntax.

And while your classname is WINSURGEDMP.WINSURGERESULTFACT

I expect your TABLE to be named WINSURGE_DMP.WINSURGE_RESULT_FACT

It is just by accident if TABLEs and CLASSes have the same name !!!!!
Especially if you refer to an EXTERNAl TABLE in a different (non Caché) database

I'm still unable to determine why I can't insert into the external database. I came across something in the documentation about creating a new DSN for an external database connection. Do you think that might be my problem?

Sorry, please disregard this update. I found that I have already created the dsn but didn't remember doing that.

the DSN is used when you set up your SQLgateway connection.

MgmtPortal > System > Configuration > SQL Gateway Connections

 There all information  on the DSN is stored.

Next if you Link the external table this gateway entry is used and stored for access.

MgmtPortal > System Explorer > SQL > Wizards > LinkTable

Now you bind a Class in a Namespace to a Gateway to a DSN


  

So you are fixed for THIS namespace. This is static.
If you change the Gateway entry or the DSN in some essential way
it could happen that you have to do this again.

Similar for a different target table you have to do the Link again.

A different issue could be that you might just have read access to the foreign table.
Then I'd expect some kind of error message.
At least it is something the partner side has to manage.

Hi Mike, Robert was right that you checked the class name while the table schema/name will probably be different. Let's find out the table name at the SQL page : Management Portal > System Explorer > SQL, click the Tables link in the left menu and check if there is any table with similar schema / name to the one you expect.