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