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

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

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 .

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

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

Mike

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

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

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

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?

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

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

Thank you Tomas