sql insert from cache

Question

Question

Mike Minor · Apr 6, 2018

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

Discussion (8)2

Log in or sign up to continue

John Murray · Apr 9, 2018

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.

0 0

Robert Cemper · Apr 7, 2018

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 ];

1 0

score 0 · Answer 1 · 2018-04-07T02:02:00-04:00

" I found a utility in CACHE " which one ?

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

score 0 · Answer 2 · 2018-04-07T06:32:29-04:00

Eduard Lebedyuk · Apr 7, 2018

Use double quotes?

SELECT "ACCESSION_DATE"
FROM Table

0 0

score 0 · Answer 3 · 2018-04-07T08:58:47-04:00

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

score 0 · Answer 4 · 2018-04-07T10:26:44-04:00

Marc Mundt · Apr 7, 2018

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

0 0

score 0 · Answer 5 · 2018-04-13T13:24:54-04:00

Robert Cemper · Apr 13, 2018

Mike,

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

Thx.

0 0

score 0 · Answer 6 · 2018-04-13T10:47:48-04:00

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