All
Accepted answers
Mike Minor · Apr 7, 2018 go to post

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

Mike Minor · Apr 13, 2018 go to post

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

Mike Minor · Apr 16, 2018 go to post

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

Mike Minor · Apr 17, 2018 go to post

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

Mike Minor · Apr 24, 2018 go to post

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?

Mike Minor · Apr 24, 2018 go to post

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

Mike Minor · Apr 27, 2018 go to post

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

Mike Minor · Apr 27, 2018 go to post

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

Mike Minor · Apr 27, 2018 go to post

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

Mike

Mike Minor · Apr 27, 2018 go to post

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

Mike Minor · May 7, 2018 go to post

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 .

Mike Minor · May 9, 2018 go to post

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

Mike Minor · May 9, 2018 go to post

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