go to post Mike Minor · May 9, 2018 Robert,where did you addProperty null As %Integer; I added it as shown below, but it still won't compileClass 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 ];
go to post Mike Minor · May 9, 2018 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 usesParameter 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 classClass 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 ];}
go to post Mike Minor · May 7, 2018 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 .
go to post Mike Minor · Apr 27, 2018 sorry for not fully understanding that link table was for data transfer in both directions.
go to post Mike Minor · Apr 27, 2018 Actually, rather than the data import wizard, I ran the data migration wizard.... Mike
go to post Mike Minor · Apr 27, 2018 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
go to post Mike Minor · Apr 27, 2018 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
go to post Mike Minor · Apr 24, 2018 Sorry, please disregard this update. I found that I have already created the dsn but didn't remember doing that.
go to post Mike Minor · Apr 24, 2018 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?
go to post Mike Minor · Apr 17, 2018 Robert, thank you for all your help.I ran the following in the management portalINSERT INTO WINSURGEDMP.WINSURGERESULTFACT VALUES (1,2,3,4,5,6)and received the following errorERROR #5540: SQLCODE: -30 Message: Table 'WINSURGEDMP.WINSURGERESULTFACT' not foundthe -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
go to post Mike Minor · Apr 16, 2018 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_DMPNEW SQLCODE &sql(INSERT INTO WINSURGE_DMP.WINSURGE_RESULT_FACT VALUES :FIELD())Any help will be appreciated.Mike
go to post Mike Minor · Apr 13, 2018 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 objectset gc=##class(%SQLGatewayConnection).%New()If gc=$$$NULLOREF quit $$$ERROR($$$GeneralError,"Cannot create %SQLGatewayConnection.") //Make connection to target DSNs pDSN="CGDEV"s usr="WINSURGE_DMP"s pwd="xxxxxxxx"s sc=gc.Connect(pDSN,usr,pwd,0)If $$$ISERR(sc) quit scif gc.ConnectionHandle="" quit $$$ERROR($$$GeneralError,"Connection failed") set sc=gc.AllocateStatement(.hstmt)if $$$ISERR(sc) quit scEmbeddedSQLInsertHostVarArray#SQLCompile Path=canreg//#SQLCompile Path=WINSURGE_DMPNEW 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.MACERROR: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
go to post Mike Minor · Apr 7, 2018 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
