sql insert from cache

I am tasked with using CACHE to insert data retrieved from a CACHE data base and insert it into an sql database. The columns in the sql table that I am trying to insert data into have names that contain underscores such as "ACCESSION_DATE" I found a utility in CACHE to connect to the sql data base and perform inserts. I have the data I need to insert. I need to pass the  data into the sql utility referencing the column names.

I tried using indirection to set the data into underscored variables, but that isn't allowed  in cache object script.

Anyone have suggestions as to how to do that?

Thank you,

Mike Minor

  • 0
  • 0
  • 282
  • 7
  • 1

Answers

In Caché classes the general rule is to remove underscores for internal use.

For Properties you have the parameter SqlFieldName to keep the original name and also use it for SQL access. (e.g. INSERT)
 

Property AccessionDate As %Date [ SqlFieldName = ACCESSION_DATE ];

Comments

"  I found a utility in CACHE "   which one ?

most likely you might pass column names as string parameters "ACCESSION_DATE"

The problem is not using the underscored variable name within the select statement. It comes in creating a variable name in cache , such as "SET ACCESSION_DATE=12/1/2017" Cache won't accept a variable name with an "_" .

Mike

No, a variable name cannot contain the underscore character, because that character is the concatenation operator. If it was allowed in a variable name, how would we interpret this, for example?

WRITE TICK_TOCK

Is it concatenating variables TICK and TOCK, then writing out the result? Or writing the value of a single variable named TICK_TOCK.

If the utility you refer to is expecting you to supply data values in variables whose names match the SQL column names, then the developer of the utility hasn't accounted for the fact that SQL column names can contain underscores but Caché variables can't. So the utility needs fixing.

Mike, can you post a sample of the code you're using and/or details about the utility?

Thank you for the help with my previous question. I got past he underscore problem.

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
pDSN="CGDEV"
usr="WINSURGE_DMP"
pwd="xxxxxxxx"
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 }   
"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

Mike,

please create a new question as the solution is not related to the previous one.

Thx.