cache sql insert into external database

Question

Question

Mike Minor · Apr 13, 2018

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

Discussion (21)0

Log in or sign up to continue

Robert Cemper · Apr 13, 2018

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

1 0

Robert Cemper · Apr 24, 2018

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.

0 1

Robert Cemper · Apr 27, 2018

to access remote tables you should use LInk Table to work in both directions

I wrote this already in line 2 of my answer to this question 2 weeks ago

https://community.intersystems.com/post/cache-sql-insert-external-database##node-446881

0 0

Robert Cemper · May 7, 2018

I finally got the code to compile with no errors.
When I run it, the code will insert a row in the table "global" in cache.
However, it isn't inserting the information in the external database.

From what you describe you have generated a Local Table as there is a GLOBAL and you write to your local DB. As you have seen.

If you use Link Table Wizzard to create it, then there is just no global.

The correctly generated class looks similar to this:

/// Generated by the Link Table wizard on 2018-05-07 23:20:46.
Note that you can access the data in this class only when the external database is accessible.
Class GTY.Person Extends %Library.Persistent [. . . . , StorageStrategy = GSQLStorage ]
{
/// Specifies details for the SQL Gateway Connection that this class uses
Parameter CONNECTION = ". . . . . . ,NOCREATE";
.......

Storage GSQLStorage
{
<StreamLocation>^####S</StreamLocation>
<Type>%CacheSQLStorage</Type>
}

}

If you do not see the bolded text in your class, it is not generated by Link Table Wizard and you are in the wrong Class / Table with no access to he external table.

0 0

score 0 · Answer 1 · 2018-04-16T15:20:22-04:00

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

score 0 · Answer 2 · 2018-04-16T15:53:00-04:00

#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

score 0 · Answer 3 · 2018-04-17T10:53:15-04:00

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

score 0 · Answer 4 · 2018-04-17T12:07:00-04:00

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

score 0 · Answer 5 · 2018-04-24T09:51:57-04:00

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?

score 0 · Answer 6 · 2018-04-24T10:13:43-04:00

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

score 0 · Answer 7 · 2018-04-27T09:35:10-04:00

In my Management Portal > System Explorer > SQL page, I do not see a tables Link. Where would that be.

On another note, I have run the data import wizard and I was able to import data from the Oracle database into Cache. I did this just to make sure my connection was setup properly. From the Oracle developer application, I ran the following manually with success:

INSERT INTO WINSURGE_DMP.WINSURGE_RESULT_FACT (LAB) VALUES ('AAD');

I tried running the following from the cache terminal and it works:

  SET myquery = "SELECT TOP 1 CLIA FROM WINSURGE_DMP.WINSURGE_RESULT_FACT"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 { WRITE "%Prepare failed",$System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()

Is there something I have missed which controls the insert of data into an external database?

Thank you,

Mike

score 0 · Answer 8 · 2018-04-27T09:42:36-04:00

I just realized that running the query from the terminal does not work. Yesterday I had run the data import wizard and copied the database from Oracle into Cache. The code worked on the cache data. I killed the globals that were created from the import and tried to code again, and it returned no data. It did no give any errors, it simply found no data.

If I import the data and update it in cache with new data, will that update the external database as well? I doubt that would be the case.

thanks,

Mike

score 0 · Answer 9 · 2018-04-27T11:01:15-04:00

Mike Minor · Apr 27, 2018

Actually, rather than the data import wizard, I ran the data migration wizard....

Mike

0 0

score 0 · Answer 10 · 2018-04-27T15:27:50-04:00

Mike Minor · Apr 27, 2018

sorry for not fully understanding that link table was for data transfer in both directions.

0 0

score 0 · Answer 11 · 2018-04-27T16:07:45-04:00

no need to excuse.
the wording (and help text) sometimes not really guiding.
but you motivated me to use more screenshot for explanations.

score 0 · Answer 12 · 2018-05-09T09:37:29-04:00

I fund the class that was created. It has part of what you say is needed but I don't see this part

{
/// Specifies details for the SQL Gateway Connection that this class uses
Parameter CONNECTION = ". . . . . . ,NOCREATE";
.......

Storage GSQLStorage
{
<StreamLocation>^####S</StreamLocation>
<Type>%CacheSQLStorage</Type>
}

can I edit the class and insert that? If so, where should it go? Here is the complete class

Class WINSURGEDMP.WINSURGERESULTFACT Extends %Library.Persistent [ ClassType = persistent, Not ProcedureBlock, SqlRowIdPrivate, SqlTableName = WINSURGE_RESULT_FACT, StorageStrategy = GSQLStorage ]
{

Parameter CONNECTION = "canreg,NOCREATE";

Parameter EXTDBNAME = "Oracle";

Parameter EXTERNALTABLENAME = "WINSURGE_DMP.WINSURGE_RESULT_FACT";

Property ACCCREATEDDATE As %TimeStamp(EXTERNALSQLNAME = "ACC_CREATED_DATE", EXTERNALSQLTYPE = 93) [ SqlColumnNumber = 39, SqlFieldName = ACC_CREATED_DATE ];

Property ACCESSION As %String(EXTERNALSQLNAME = "ACCESSION", EXTERNALSQLTYPE = 12, MAXLEN = 100) [ SqlColumnNumber = 19, SqlFieldName = ACCESSION ];

Property ACCPTNTDOB As %TimeStamp(EXTERNALSQLNAME = "ACC_PTNT_DOB", EXTERNALSQLTYPE = 93) [ SqlColumnNumber = 32, SqlFieldName = ACC_PTNT_DOB ];

Property AMENDMENTNOTES As %Stream.GlobalCharacter(CLASSNAME = 2, EXTERNALSQLNAME = "AMENDMENT_NOTES", EXTERNALSQLTYPE = -1) [ SqlColumnNumber = 44, SqlFieldName = AMENDMENT_NOTES ];

Property BILLINGID As %String(EXTERNALSQLNAME = "BILLING_ID", EXTERNALSQLTYPE = 12, MAXLEN = 400) [ SqlColumnNumber = 56, SqlFieldName = BILLING_ID ];

Property BLACKBARNAME As %String(EXTERNALSQLNAME = "BLACKBAR_NAME", EXTERNALSQLTYPE = 12, MAXLEN = 400) [ SqlColumnNumber = 59, SqlFieldName = BLACKBAR_NAME ];

Property BLACKBARRELEASEDATE As %TimeStamp(EXTERNALSQLNAME = "BLACKBAR_RELEASE_DATE", EXTERNALSQLTYPE = 93) [ SqlColumnNumber = 43, SqlFieldName = BLACKBAR_RELEASE_DATE ];

Property CANCERINDICATOR As %String(EXTERNALSQLNAME = "CANCER_INDICATOR", EXTERNALSQLTYPE = 12, MAXLEN = 1) [ SqlColumnNumber = 54, SqlFieldName = CANCER_INDICATOR ];

Property CASECOMMENTS As %Stream.GlobalCharacter(CLASSNAME = 2, EXTERNALSQLNAME = "CASE_COMMENTS", EXTERNALSQLTYPE = -1) [ SqlColumnNumber = 48, SqlFieldName = CASE_COMMENTS ];

Property CLIA As %String(EXTERNALSQLNAME = "CLIA", EXTERNALSQLTYPE = 12, MAXLEN = 1000) [ SqlColumnNumber = 2, SqlFieldName = CLIA ];

Property CLINICALHISTORY As %Stream.GlobalCharacter(CLASSNAME = 2, EXTERNALSQLNAME = "CLINICAL_HISTORY", EXTERNALSQLTYPE = -1) [ SqlColumnNumber = 40, SqlFieldName = CLINICAL_HISTORY ];

Property CLINICALICD As %String(EXTERNALSQLNAME = "CLINICAL_ICD", EXTERNALSQLTYPE = 12, MAXLEN = 4000) [ SqlColumnNumber = 52, SqlFieldName = CLINICAL_ICD ];

Property CLINICALINFO As %Stream.GlobalCharacter(CLASSNAME = 2, EXTERNALSQLNAME = "CLINICAL_INFO", EXTERNALSQLTYPE = -1) [ SqlColumnNumber = 41, SqlFieldName = CLINICAL_INFO ];

Property CREATEDDATE As %TimeStamp(EXTERNALSQLNAME = "CREATED_DATE", EXTERNALSQLTYPE = 93) [ SqlColumnNumber = 55, SqlFieldName = CREATED_DATE ];

Property DIAGNOSTICICD As %String(EXTERNALSQLNAME = "DIAGNOSTIC_ICD", EXTERNALSQLTYPE = 12, MAXLEN = 4000) [ SqlColumnNumber = 53, SqlFieldName = DIAGNOSTIC_ICD ];

Property FINALDIAGNOSIS As %Stream.GlobalCharacter(CLASSNAME = 2, EXTERNALSQLNAME = "FINAL_DIAGNOSIS", EXTERNALSQLTYPE = -1) [ SqlColumnNumber = 47, SqlFieldName = FINAL_DIAGNOSIS ];

Property GROSSPATHOLOGY As %Stream.GlobalCharacter(CLASSNAME = 2, EXTERNALSQLNAME = "GROSS_PATHOLOGY", EXTERNALSQLTYPE = -1) [ SqlColumnNumber = 45, SqlFieldName = GROSS_PATHOLOGY ];

Property LAB As %String(EXTERNALSQLNAME = "LAB", EXTERNALSQLTYPE = 12, MAXLEN = 1000) [ SqlColumnNumber = 3, SqlFieldName = LAB ];

Property LOINCCODE As %String(EXTERNALSQLNAME = "LOINC_CODE", EXTERNALSQLTYPE = 12, MAXLEN = 1000) [ SqlColumnNumber = 51, SqlFieldName = LOINC_CODE ];

Property LOINCCODENAME As %String(EXTERNALSQLNAME = "LOINC_CODE_NAME", EXTERNALSQLTYPE = 12, MAXLEN = 1000) [ SqlColumnNumber = 50, SqlFieldName = LOINC_CODE_NAME ];

Property MICROPATHOLOGY As %Stream.GlobalCharacter(CLASSNAME = 2, EXTERNALSQLNAME = "MICRO_PATHOLOGY", EXTERNALSQLTYPE = -1) [ SqlColumnNumber = 46, SqlFieldName = MICRO_PATHOLOGY ];

Property PATHOLOGISTFIRSTNM As %String(EXTERNALSQLNAME = "PATHOLOGIST_FIRST_NM", EXTERNALSQLTYPE = 12, MAXLEN = 400) [ SqlColumnNumber = 17, SqlFieldName = PATHOLOGIST_FIRST_NM ];

Property PATHOLOGISTLASTNM As %String(EXTERNALSQLNAME = "PATHOLOGIST_LAST_NM", EXTERNALSQLTYPE = 12, MAXLEN = 400) [ SqlColumnNumber = 16, SqlFieldName = PATHOLOGIST_LAST_NM ];

Property PATHOLOGISTMI As %String(EXTERNALSQLNAME = "PATHOLOGIST_MI", EXTERNALSQLTYPE = 12, MAXLEN = 400) [ SqlColumnNumber = 18, SqlFieldName = PATHOLOGIST_MI ];

Property PATIENTADDRESSLINE1 As %String(EXTERNALSQLNAME = "PATIENT_ADDRESS_LINE1", EXTERNALSQLTYPE = 12, MAXLEN = 1000) [ SqlColumnNumber = 34, SqlFieldName = PATIENT_ADDRESS_LINE1 ];

Property PATIENTADDRESSLINE2 As %String(EXTERNALSQLNAME = "PATIENT_ADDRESS_LINE2", EXTERNALSQLTYPE = 12, MAXLEN = 1000) [ SqlColumnNumber = 35, SqlFieldName = PATIENT_ADDRESS_LINE2 ];

Property PATIENTCITY As %String(EXTERNALSQLNAME = "PATIENT_CITY", EXTERNALSQLTYPE = 12, MAXLEN = 1000) [ SqlColumnNumber = 36, SqlFieldName = PATIENT_CITY ];

Property PATIENTETHNICITY As %String(EXTERNALSQLNAME = "PATIENT_ETHNICITY", EXTERNALSQLTYPE = 12, MAXLEN = 400) [ SqlColumnNumber = 30, SqlFieldName = PATIENT_ETHNICITY ];

Property PATIENTETHNICITYCODE As %String(EXTERNALSQLNAME = "PATIENT_ETHNICITY_CODE", EXTERNALSQLTYPE = 12, MAXLEN = 400) [ SqlColumnNumber = 28, SqlFieldName = PATIENT_ETHNICITY_CODE ];

Property PATIENTETHNICITYDESC As %String(EXTERNALSQLNAME = "PATIENT_ETHNICITY_DESC", EXTERNALSQLTYPE = 12, MAXLEN = 400) [ SqlColumnNumber = 29, SqlFieldName = PATIENT_ETHNICITY_DESC ];

Property PATIENTFIRSTNAME As %String(EXTERNALSQLNAME = "PATIENT_FIRST_NAME", EXTERNALSQLTYPE = 12, MAXLEN = 400) [ SqlColumnNumber = 26, SqlFieldName = PATIENT_FIRST_NAME ];

Property PATIENTGENDER As %String(EXTERNALSQLNAME = "PATIENT_GENDER", EXTERNALSQLTYPE = 12, MAXLEN = 400) [ SqlColumnNumber = 33, SqlFieldName = PATIENT_GENDER ];

Property PATIENTLASTNAME As %String(EXTERNALSQLNAME = "PATIENT_LAST_NAME", EXTERNALSQLTYPE = 12, MAXLEN = 400) [ SqlColumnNumber = 25, SqlFieldName = PATIENT_LAST_NAME ];

Property PATIENTMI As %String(EXTERNALSQLNAME = "PATIENT_MI", EXTERNALSQLTYPE = 12, MAXLEN = 400) [ SqlColumnNumber = 27, SqlFieldName = PATIENT_MI ];

Property PATIENTMPI As %String(EXTERNALSQLNAME = "PATIENT_MPI", EXTERNALSQLTYPE = 12, MAXLEN = 400) [ SqlColumnNumber = 24, SqlFieldName = PATIENT_MPI ];

Property PATIENTMRN As %String(EXTERNALSQLNAME = "PATIENT_MRN", EXTERNALSQLTYPE = 12, MAXLEN = 400) [ SqlColumnNumber = 23, SqlFieldName = PATIENT_MRN ];

Property PATIENTSSN As %String(EXTERNALSQLNAME = "PATIENT_SSN", EXTERNALSQLTYPE = 12, MAXLEN = 20) [ SqlColumnNumber = 31, SqlFieldName = PATIENT_SSN ];

Property PATIENTSTATE As %String(EXTERNALSQLNAME = "PATIENT_STATE", EXTERNALSQLTYPE = 12, MAXLEN = 200) [ SqlColumnNumber = 37, SqlFieldName = PATIENT_STATE ];

Property PATIENTZIP As %String(EXTERNALSQLNAME = "PATIENT_ZIP", EXTERNALSQLTYPE = 12, MAXLEN = 200) [ SqlColumnNumber = 38, SqlFieldName = PATIENT_ZIP ];

Property PROCNM As %String(EXTERNALSQLNAME = "PROC_NM", EXTERNALSQLTYPE = 12, MAXLEN = 1000) [ SqlColumnNumber = 22, SqlFieldName = PROC_NM ];

Property READINGLAB As %String(EXTERNALSQLNAME = "READING_LAB", EXTERNALSQLTYPE = 12, MAXLEN = 1000) [ SqlColumnNumber = 5, SqlFieldName = READING_LAB ];

Property READINGLABCLIA As %String(EXTERNALSQLNAME = "READING_LAB_CLIA", EXTERNALSQLTYPE = 12, MAXLEN = 1000) [ SqlColumnNumber = 4, SqlFieldName = READING_LAB_CLIA ];

Property REFLOCADDRESS1 As %String(EXTERNALSQLNAME = "REF_LOC_ADDRESS1", EXTERNALSQLTYPE = 12, MAXLEN = 1000) [ SqlColumnNumber = 7, SqlFieldName = REF_LOC_ADDRESS1 ];

Property REFLOCADRPHNBR As %String(EXTERNALSQLNAME = "REFLOC_ADR_PH_NBR", EXTERNALSQLTYPE = 12, MAXLEN = 300) [ SqlColumnNumber = 11, SqlFieldName = REFLOC_ADR_PH_NBR ];

Property REFLOCCITY As %String(EXTERNALSQLNAME = "REF_LOC_CITY", EXTERNALSQLTYPE = 12, MAXLEN = 1000) [ SqlColumnNumber = 8, SqlFieldName = REF_LOC_CITY ];

Property REFLOCNM As %String(EXTERNALSQLNAME = "REF_LOC_NM", EXTERNALSQLTYPE = 12, MAXLEN = 1000) [ SqlColumnNumber = 6, SqlFieldName = REF_LOC_NM ];

Property REFLOCSTATE As %String(EXTERNALSQLNAME = "REF_LOC_STATE", EXTERNALSQLTYPE = 12, MAXLEN = 200) [ SqlColumnNumber = 9, SqlFieldName = REF_LOC_STATE ];

Property REFLOCZIPCODE As %String(EXTERNALSQLNAME = "REF_LOC_ZIP_CODE", EXTERNALSQLTYPE = 12, MAXLEN = 200) [ SqlColumnNumber = 10, SqlFieldName = REF_LOC_ZIP_CODE ];

Property REFPHYSFIRSTNM As %String(EXTERNALSQLNAME = "REFPHYS_FIRST_NM", EXTERNALSQLTYPE = 12, MAXLEN = 400) [ SqlColumnNumber = 13, SqlFieldName = REFPHYS_FIRST_NM ];

Property REFPHYSLASTNM As %String(EXTERNALSQLNAME = "REFPHYS_LAST_NM", EXTERNALSQLTYPE = 12, MAXLEN = 400) [ SqlColumnNumber = 12, SqlFieldName = REFPHYS_LAST_NM ];

Property REFPHYSMI As %String(EXTERNALSQLNAME = "REFPHYS_MI", EXTERNALSQLTYPE = 12, MAXLEN = 400) [ SqlColumnNumber = 14, SqlFieldName = REFPHYS_MI ];

Property REFPHYSNPI As %String(EXTERNALSQLNAME = "REFPHYS_NPI", EXTERNALSQLTYPE = 12, MAXLEN = 400) [ SqlColumnNumber = 15, SqlFieldName = REFPHYS_NPI ];

Property REFPHYSUPIN As %String(EXTERNALSQLNAME = "REFPHYS_UPIN", EXTERNALSQLTYPE = 12, MAXLEN = 400) [ SqlColumnNumber = 57, SqlFieldName = REFPHYS_UPIN ];

Property RESULTSTATUS As %String(EXTERNALSQLNAME = "RESULT_STATUS", EXTERNALSQLTYPE = 12, MAXLEN = 400) [ SqlColumnNumber = 49, SqlFieldName = RESULT_STATUS ];

Property SPECIMENPARTID As %String(EXTERNALSQLNAME = "SPECIMEN_PART_ID", EXTERNALSQLTYPE = 12, MAXLEN = 100) [ SqlColumnNumber = 20, SqlFieldName = SPECIMEN_PART_ID ];

Property SPECIMENTYPE As %String(EXTERNALSQLNAME = "SPECIMEN_TYPE", EXTERNALSQLTYPE = 12, MAXLEN = 1000) [ SqlColumnNumber = 21, SqlFieldName = SPECIMEN_TYPE ];

Property SPECMNCOLLECTIONDATE As %TimeStamp(EXTERNALSQLNAME = "SPECMN_COLLECTION_DATE", EXTERNALSQLTYPE = 93) [ SqlColumnNumber = 42, SqlFieldName = SPECMN_COLLECTION_DATE ];

Property SPECMNRECVDATE As %TimeStamp(EXTERNALSQLNAME = "SPECMN_RECV_DATE", EXTERNALSQLTYPE = 93) [ SqlColumnNumber = 58, SqlFieldName = SPECMN_RECV_DATE ];

Index MainIndex On null [ IdKey, PrimaryKey ];

}

score 0 · Answer 13 · 2018-05-09T10:36:55-04:00

- you have

Parameter CONNECTION = "canreg,NOCREATE";

- so the name of your Gateway definition is canreg
- you have no storage for streams defined.
Strange but might be an issue of Caché version version issue.
in 2016.2 it just doesn't compile without.
- more strange is this:

Index MainIndex On null [ IdKey, PrimaryKey ];

The property null doesn't exist in your class.
Without a property null it doesn't compile for me.

adding manually the missing Storage section and

Property null As %Integer;

let it compile for me.

Though this may be a temporary fix I distrust your Link Wizard.
Eventually, you should contact WRC to investigate the reason.

score 0 · Answer 14 · 2018-05-09T12:04:44-04:00

Robert,

where did you add

Property null As %Integer;

I added it as shown below, but it still won't compile

Class WINSURGEDMP.WINSURGERESULTFACT Extends %Library.Persistent [ ClassType = persistent, Not ProcedureBlock, SqlRowIdPrivate, SqlTableName = WINSURGE_RESULT_FACT, StorageStrategy = GSQLStorage ]
{

Parameter CONNECTION = "canreg,NOCREATE";

Parameter EXTDBNAME = "Oracle";

Parameter EXTERNALTABLENAME = "WINSURGE_DMP.WINSURGE_RESULT_FACT";

Property null As %Integer;

Property ACCCREATEDDATE As %TimeStamp(EXTERNALSQLNAME = "ACC_CREATED_DATE", EXTERNALSQLTYPE = 93) [ SqlColumnNumber = 39, SqlFieldName = ACC_CREATED_DATE ];

score 0 · Answer 15 · 2018-05-09T14:00:25-04:00

I added it just to the end. But thas nit important.

Did you also add at the end this: ??

Storage GSQLStorage
{
<StreamLocation>^Mike.StreamS</StreamLocation>
<Type>%CacheSQLStorage</Type>
}

If this is missing the class also doesn't compile.
the name of the global for streams is not important.

BT: What error did you get ?

score 0 · Answer 16 · 2018-05-07T16:09:17-04:00

ok....it's been a while since my last post...

I finally got the code to compile with no errors. When I run it, the code will insert a row in the table "global" in cache. However, it isn't inserting the information in the external database. I have also been looking at manually updating the external sql database by using the management portals data export function to create a file for importing into the external database. However, the data export only creates a .txt. The external database import function only accepts either csv or xml files. Is there a way to make the export function create a .csv or xml file? If not, I can write something to create .csv file .

score 0 · Answer 17 · 2018-04-25T04:41:17-04:00

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.