Announcement

Collapse
No announcement yet.

Java Null Pointer Exception when polling DB

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Java Null Pointer Exception when polling DB

    I get this Error sometimes. Anyone can help? I use Mirth Connect Server 3.4.1.770 with Java version: 1.8.0_221

    Code:
    com.mirth.connect.connectors.jdbc.DatabaseReceiver: Failed to poll for messages from the database in channel "PGD_FILE_MEDDOKU_Scancenter"
    java.lang.NullPointerException
    
    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.getParameter(JtdsPreparedStatement.java:560)
    	at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setParameter(JtdsPreparedStatement.java:630)
    	at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setObjectBase(JtdsPreparedStatement.java:616)
    	at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setObject(JtdsPreparedStatement.java:907)
    	at com.mirth.connect.connectors.jdbc.DatabaseReceiverQuery.poll(DatabaseReceiverQuery.java:187)
    	at com.mirth.connect.connectors.jdbc.DatabaseReceiver.poll(DatabaseReceiver.java:108)
    	at com.mirth.connect.donkey.server.channel.PollConnectorJob.execute(PollConnectorJob.java:49)
    	at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
    	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:557)
    Work for a NPO health care provider in Berlin
    • Current Version of MC 3.4.1.770 with the onboard MySQL Database on a Windows Server 2012 R2

  • #2
    Can you post your query?

    Comment


    • #3
      From the Database Reader:

      Code:
      USE [ExterneDaten]
      BEGIN
      SELECT [DOCUMENTID]
            ,[STAPELNUMMER]
            ,[DOCUMENTTYP]
            ,[MANDANT]
            ,[FALLNUMMER]
            ,[PATIENTENID]
            ,[NACHNAME]
            ,[VORNAME]
            ,[GEBDATUM]
            ,[AUFNAHMEDATETIME]
            ,[ENTLASSDATETIME]
            ,[DOCUMENTREGISTER]
            ,[CREATEDEPARTMENT]
            ,[DOCUMENTCLASS]
            ,[DOCUMENTKIND]
            ,[DOCUMENTDATE]
            ,[CREATEDATE]
            ,[DOKUMENTENSTATUS]
            ,[BEARBEITUNGSZEITPUNKT]
            ,[DOKUMENTHERKUNFT]
            ,[BEHANDLUNGSART]
            ,[SCANCLIENT]
            ,[BENUTZER]
            ,LEFT([FALLNUMMER], 2) AS 'MandantID'
            ,[ExterneDaten].[dbo].[Kofax_PatDaten_DocExport].[DokumentPfad] AS [PFAD]
        FROM [ExterneDaten].[dbo].[PatientendatenScancenter]
        INNER JOIN [ExterneDaten].[dbo].[Kofax_PatDaten_DocExport] 
        ON [ExterneDaten].[dbo].[PatientendatenScancenter].[DOCUMENTID] = [ExterneDaten].[dbo].[Kofax_PatDaten_DocExport].[DokumentID]
        WHERE [DOKUMENTENSTATUS] IS NULL;
        
        UPDATE [dbo].[PatientendatenScancenter]
         SET [DOKUMENTENSTATUS] = 'cached'
        WHERE [DOCUMENTID] = ${documentid};
      END
      Work for a NPO health care provider in Berlin
      • Current Version of MC 3.4.1.770 with the onboard MySQL Database on a Windows Server 2012 R2

      Comment


      • #4
        Source Filter #1

        Code:
        var dateString = DateUtil.getCurrentDate('yyyy-dd-MM HH:mm:ss.000');
        var driver = 'net.sourceforge.jtds.jdbc.Driver';
        var address = 'jdbc:jtds:sqlserver://XXX/ExterneDaten';
        var username = 'XXX';
        var password = 'XXX';
        var dbConn = null;
        var result = null;
        var curCaseId;
        var newCaseId;
        var curPatId;
        var newPatId;
        
        logger.debug("Debug: Filter 1: Size of PatID: " + msg['patientenid'].toString().length);
        logger.debug("Debug: Filter 1: Size of CaseID: " + msg['fallnummer'].toString().length);
        logger.debug("Debug: Filter 1: Digit 3 of CaseID: " + msg['fallnummer'].toString().charAt(2));
        
        if (msg['fallnummer'].toString().length == 0 /*Wird nur generiert, wenn Fallnummer & Pat ID leer ist. Ansonsten Fehler*/){
        	logger.debug("Debug: IF Filter 1: msg['fallnummer'].toString().length == 0");
        	try {
        		dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver, address, username, password);
        		var cachedSQL = "SELECT TOP (1) [GeneratedCaseId] FROM [ExterneDaten].[dbo].[GeneratedIds];";
        		result = dbConn.executeCachedQuery(cachedSQL);
        		result.next();
        		curCaseId = result.getObject('GeneratedCaseId');
        		var resultsize = result.size();
        		curCaseId = parseInt(curCaseId);
        		logger.debug("Debug: Anzahl der Eintraege fuer generierte CaseIds: " + resultsize);
        		logger.debug("Debug: Current Generated Case ID (curCaseId): " + curCaseId);	
        	} 
        	catch (e) {
        		logger.info("Reading Generated Case Id error on Channel: " + channelName +" caused by: " + e);
        		return false;
        	} finally {
        		if (dbConn) {
        			dbConn.close();
        		}
        	}
        	//increment curCaseId --> nur wenn db abfrage erfolgreich war oder return false
        	curCaseId++;
        	newCaseId = curCaseId;
        	logger.debug("Debug: New Case id: " + newCaseId);
        	try {
        		try {
        			dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver, address, username, password);
        			var newCaseIdSQL = "USE [ExterneDaten] UPDATE [dbo].[GeneratedIds] SET [GeneratedCaseId] = '" + newCaseId + "';";
        			result = dbConn.executeUpdate(newCaseIdSQL);
        		} 
        		catch (e) {
        		logger.info("Writing Generated Case Id update error on Channel: " + channelName + " caused by: " + e);
        		} finally {
        			if (dbConn) {
        				dbConn.close();
        			}
        		}
        	} // ende try block 1.1
        	catch (e) {
        		logger.info("Writing Generated Case Id error on Channel: " + channelName + " caused by: " + e);
        	} finally {
        		logger.debug("Debug: Writing Generated Case Id error on Channel: " + channelName + " State of dbConn: " + dbConn);
        		if (dbConn) {
        			dbConn.close();
        		}
        	}
        	//update new generated CaseId in Kofax Table
        	try {
        		// Mandantenziffer auslesen
        		try {
        			logger.debug("Debug: Der gefundene Mandant aus der Nachricht: " + msg['mandant'].toString());
        			dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver, address, username, password);
        			var mandantnrSQL = "SELECT [JD_Einrichtung_Kuerzel], [JD_Einrichtung_Nummer] FROM [ExterneDaten].[dbo].[JD_Einrichtung] WHERE [JD_Einrichtung_Kuerzel] = '" + msg['mandant'] + "';";
        			var mandnr = dbConn.executeCachedQuery(mandantnrSQL);
        			mandnr.next();
        			logger.debug("Debug: Die gefundene Mandantennummer aus der DB: " + mandnr);
        			var mandid = mandnr.getObject('JD_Einrichtung_Nummer');
        			//mandid = mandnr.replaceAll("\\s", "");
        			logger.debug("Debug: Das gefundene Mandantenkuerzel aus der DB: " + mandid);
        		} //ende try block 2.1
        		catch(e){
        			logger.info("Fehler beim Mandantenvergleich aus [dbo].[JD_Einrichtung]. New Mandant: " + mandid + ". Old Mandant: " + msg['mandant'].toString() + " on channel: " + channelName);
        			throw('NO MANDANT GENERATED');
        		}finally {
        			if (dbConn) {
        				dbConn.close();
        			}
        		}
        		// Mandantenziffer auslesen
        		try {
        			logger.debug("Debug: DokumentenID = " + msg['documentid'].toString());
        			dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver, address, username, password);
        			var updateSQL = "USE [ExterneDaten] UPDATE [dbo].[PatientendatenScancenter] SET [FALLNUMMER] = '" + mandid + "x" + newCaseId + "' WHERE [DOCUMENTID] = '" + msg['documentid'].toString() + "';";
        			result = dbConn.executeUpdate(updateSQL);
        		} // ende try block 2.2
        		catch(e){
        			logger.info("Fehler update bei der generierten Fallnummer" + e + " on channel: " + channelName);
        			throw('DB ERROR updateSQL');
        		} finally {
        			if (dbConn) {
        				dbConn.close();
        			}
        		}
        		//hier muss noch die Fallnummer mit der message.fallnummer geupdatet werden und in den 2.Filter übetragen werden
        		var genCaseId = mandid + "x" + newCaseId;
        		connectorMap.put('genCaseId',genCaseId);
        		logger.debug("DEBUG PURPOSE, FALLNUMMER (Gen): " + msg['fallnummer'].toString() + genCaseId);
        		return true; // verlassen wenn caseid erfolgreich eingesetzt
        	} // ende try block 1.2
        	catch (e) {
        		logger.info("updateSQL from new Case ID error on Channel: " + channelName + " caused by: " + e);
        		try {
        			dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver, address, username, password);
        			var updateSQL = "USE [ExterneDaten] UPDATE [dbo].[PatientendatenScancenter] SET [DOKUMENTENSTATUS] = 'INVALID MANDANT or ERROR reading db', [BEARBEITUNGSZEITPUNKT] = '" + dateString + "' WHERE [DOCUMENTID] = '" + msg['documentid'].toString() + "'";
        			result = dbConn.executeUpdate(updateSQL);
        		} 
        		catch (e){
        			logger.info("updateSQL from writing back status for new Case ID error on Channel: " + channelName + " caused by: " + e);
        		} finally {
        			if (dbConn) {
        				dbConn.close();
        			}
        		throw('DB ERROR update new Case ID');
        		}
        	}
        } // end --> if fallnummer generieren bei null
        // start --> else if patId leergelassen 
        else if (msg['patientenid'].toString().length != 0 && msg['patientenid'].toString().length != 7 && msg['patientenid'].toString().length != 9) {
        	logger.debug("Debug: Else if Filter 2: msg['patientenid'].toString().length != 0 && msg['patientenid'].toString().length != 7 && msg['patientenid'].toString().length != 9");
        	try {
        		dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver, address, username, password);
        		var updateSQL = "USE [ExterneDaten] UPDATE [dbo].[PatientendatenScancenter] SET [DOKUMENTENSTATUS] = 'PAT ID NOT VALID', [BEARBEITUNGSZEITPUNKT] = '" + dateString + "' WHERE [DOCUMENTID] = '" + msg['documentid'].toString() + "';";
        		result = dbConn.executeUpdate(updateSQL);
        	}
        	catch (e){
        		logger.info("Status of writing back [DOKUMENTENSTATUS]: " + e + " on channel: " + channelName);
        	} finally {
        		if (dbConn) {
        			dbConn.close();
        		}
        	}
        	//Response.setStatusMessage('Db reading error or CaseID not Found in dbo.PatientendatenKIS'); --> funktioniert so nicht, müsste über responsemap configuriert werden
        	logger.info("PAT ID ERROR with PatID: " + msg['patientenid'].toString() + " on channel: " + channelName);
        	throw('PAT ID NOT VALID');
        	return false; //filtern
        } //end --> else if patId leergelassen 
        else { //start --> fallnummer gültig/ungültig
        	logger.debug("Debug: ELSE FILTER 1") //muesste noch angepasst werden falls alte fallnummern nicht gleich 10-stellig also > 0
        	if (msg['fallnummer'].toString().length == 10){
        		logger.debug("Debug: Fallnummer im Filter 1 gueltig");
        		return true; //filter durchlassen, wenn case id 10 stellig
        	}
        	else {
        		try {
        		dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver, address, username, password);
        		var updateSQL = "USE [ExterneDaten] UPDATE [dbo].[PatientendatenScancenter] SET [DOKUMENTENSTATUS] = 'CASE ID NOT VALID', [BEARBEITUNGSZEITPUNKT] = '" + dateString + "' WHERE [DOCUMENTID] = '" + msg['documentid'].toString() + "';";
        		result = dbConn.executeUpdate(updateSQL);
        		}
        		catch (e){
        			logger.info("Status of writing back [DOKUMENTENSTATUS]: " + e + " on channel: " + channelName);
        		} finally {
        			if (dbConn) {
        				dbConn.close();
        			}
        		}
        	logger.info("CASE ID ERROR with CaseId: " + msg['fallnummer'].toString() + " on channel: " + channelName);
        	throw('CASE ID ERROR');
        	return false; //filtern
        	}	
        } // end --> fallnummer gültig/ungültig
        Work for a NPO health care provider in Berlin
        • Current Version of MC 3.4.1.770 with the onboard MySQL Database on a Windows Server 2012 R2

        Comment


        • #5
          Source Filter #2

          Code:
          var dateString = DateUtil.getCurrentDate('yyyy-dd-MM HH:mm:ss.000');
          var driver = 'net.sourceforge.jtds.jdbc.Driver';
          var address = 'jdbc:jtds:sqlserver://XXX/ExterneDaten';
          var username = 'XXX';
          var password = 'XXX';
          var dbConn = null;
          var result = null;
          var kisCaseID;
          var genCaseId = String(connectorMap.get('genCaseId'));
          
          logger.debug("Debug: Current Fallnummer Filter 2: " + msg['fallnummer'].toString() + " oder falls Generiert: " + genCaseId);
          logger.debug("Debug: Filter 2: Digit 3 of CaseID(im msgSegment): " + msg['fallnummer'].toString().charAt(2));
          logger.debug("Debug: Filter 2: Digit 3 of genCaseID Variable: " + genCaseId.charAt(2));
          
          if(msg['fallnummer'].toString().length == 10 && genCaseId.toString().charAt(2) != 'x') /* Dokumentenherkunft KIS Bezug, muesste auch erweitert werden auf > 0*/ {
          	logger.debug("Debug: IF Filter 2: msg['fallnummer'].toString().length == 10 && msg['fallnummer'].toString().charAt(2) != 'x'");
          	try { // start --> try block 1
          		try { // start -- try block 2
          			logger.debug("Debug: 1. Die akzeptierte Fallnummer: " + msg['fallnummer'].toString());
          			dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver, address, username, password);
          			var cachedSQL = "SELECT [FALLNUMMER], [PATIENTENID] FROM [ExterneDaten].[dbo].[PatientendatenKIS] WHERE [FALLNUMMER] = '" + parseInt(msg['fallnummer'].toString()) + "';";
          			// jumps to exception if fails
          			result = dbConn.executeCachedQuery(cachedSQL);
          			result.next();
          			kisCaseId = result.getObject('FALLNUMMER');
          			logger.debug("Debug: 2. Die kisCaseID: " + kisCaseId);
          			var resultsize = result.size();
          			logger.debug("Debug: 3. Anzahl (sollte immer nur 1 sein): " + resultsize);
          			//fallnummer in der KIS Tabelle gefunden
          			if (kisCaseId == msg['fallnummer'].toString()){ 
          				var regnr = kisCaseId.substring(0,2); //funktioniert nicht bei alten caseid aus dem kis ohne mandantenziffer
          				logger.debug("Debug: Die ersten beiden Ziffern der Fallnummer: " + regnr);
          				// Mandantenaustausch
          				try {
          					dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver, address, username, password);
          					var mandantSQL = "SELECT [JD_Einrichtung_Kuerzel], [JD_Einrichtung_Nummer] FROM [ExterneDaten].[dbo].[JD_Einrichtung] WHERE [JD_Einrichtung_Nummer] = '" + parseInt(regnr) + "';";
          					var kuez = dbConn.executeCachedQuery(mandantSQL);
          					kuez.next();
          					kuez = kuez.getObject('JD_Einrichtung_Kuerzel');
          					//kuez = kuez.replaceAll("\\t", "");
          					//kuez = kuez.replaceAll("\\n", "");
          					kuez = kuez.replaceAll("\\s", "");
          					logger.debug("Debug: Der gefundene Mandant aus der DB: " + kuez);
          					msg['mandant'] = kuez;
          					logger.debug("Debug: Der neue Mandant: " + msg['mandant'].toString());
          				} 
          				catch(e){
          					logger.info("Fehler beim Mandantenvergleich aus [dbo].[JD_Einrichtung]. newMandant: " + kuez + " oldMandant: " + msg['mandant'].toString() + " on channel: " + channelName);
          					throw('NO MANDANT FOUND FOR CASEID'); //todo: an anderen channel schicken falls alte caseids gescannt werden sollen
          				} finally {
          					if (dbConn) {
          						dbConn.close();
          					}
          				}
          				// Mandantenaustausch	
          				logger.debug("Debug: KIS Fallnummer gefunden. Nachricht wird weiterverarbeitet");
          				return true;
          			} // end --> if fallnummer in KIS Tabelle gefunden
          			else {
          				//sollte nie erreicht werden. nur für Verarbeitungsfehler, springt eigentlich in die exception
          				logger.info("KIS Fallnummernvergleich fehlgeschlagen" + " on channel: " + channelName);
          			}
          		} // end --> try block 2 --> fehler in der Abfrage PatientendatenKIS
          		catch (e) {
          			logger.debug("Debug: 2a. Comparing Scan with CaseId [" + msg['fallnummer'].toString() + "] error on channel: " + channelName + " caused by: " + e);
          			logger.debug("Debug: 3a. Document ID for comparing: " + msg['documentid'].toString());
          			//setze auf error und verlassen
          			//Response.setStatusMessage('Db reading error or CaseID not Found in dbo.PatientendatenKIS');
          			//Response.setStatus(ERROR);
          			logger.info("Filter 2: Fehler bei der Abfrage der Patientendaten aus [dbo].[PatientendatenKIS]: " + e + " on channel: " + channelName); 
          			return false;
          		} finally {
          			if (dbConn) {
          				dbConn.close();
          			}
          		}
          	} // end --> try block 1
          	catch (e) {
          		logger.info("Filter 2: Comparing Kis Case ID error on Channel: " + channelName + " caused by: " + e);
          		//hier rückmeldung in die Datenbank, dass fehler aufgetreten
          		try {
          			dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver, address, username, password);
          			var updateSQL = "USE [ExterneDaten] UPDATE [dbo].[PatientendatenScancenter] SET [DOKUMENTENSTATUS] = 'NO CASE ID FOUND FOR THIS DOCUMENT', [BEARBEITUNGSZEITPUNKT] = '" + dateString + "' WHERE [DOCUMENTID] = '" + msg['documentid'].toString() + "';";
          			result = dbConn.executeUpdate(updateSQL);
          		}
          		catch (e){
          			logger.info("Status of writing back [DOKUMENTENSTATUS]: " + e + " on channel: " + channelName);
          		} finally {
          			if (dbConn) {
          				dbConn.close();
          			}
          		}
          		//setze auf error und verlassen
          		throw('NO CASE ID FOUND FOR THIS DOCUMENT');
          	} finally {
          		if (dbConn) {
          			dbConn.close();
          		}
          		logger.debug("Debug: Datenbankverbindungsstatus: " + dbConn + " on channel: " + channelName);
          	}
          } // end --> if KIS Abfrage
          
          //hier der fall ohne namenskonvertierung, wenn fall aus der kis patientendaten DB
          else if (genCaseId.charAt(2) == 'x'){
          	logger.debug("Debug: ELSE IF FILTER 2: msg['fallnummer'].toString().charAt(2) == 'x'")
          	//var genCaseId = msg['fallnummer'].toString();
          	var regnr = genCaseId.substring(0,2);
          	logger.debug("Debug: Die ersten beiden Ziffern der Fallnummer: " + regnr);
          	// start --> Mandantenaustausch
          	try {
          		dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver, address, username, password);
          		var mandantSQL = "SELECT [JD_Einrichtung_Kuerzel], [JD_Einrichtung_Nummer] FROM [ExterneDaten].[dbo].[JD_Einrichtung] WHERE [JD_Einrichtung_Nummer] = '" + parseInt(regnr) + "';";
          		var kuez = dbConn.executeCachedQuery(mandantSQL);
          		kuez.next();
          		kuez = kuez.getObject('JD_Einrichtung_Kuerzel');
          		kuez = kuez.replaceAll("\\s", "");
          		logger.debug("Debug: Der gefundene Mandant aus der DB: " + kuez);
          		//änderungen der msg mit neuen werten
          		msg['fallnummer'] = genCaseId;
          		msg['mandant'] = kuez;
          		logger.debug("Debug: Der neue Mandant: " + msg['mandant'].toString());
          		return true;
          		} 
          	catch(e){
          		logger.info("Fehler beim Mandantenvergleich aus [dbo].[JD_Einrichtung]. newMandant: " + kuez + " oldMandant: " + msg['mandant'].toString() + " on channel: " + channelName);
          		throw('NO MANDANT FOUND FOR CASEID');
          	} finally {
          		if (dbConn) {
          			dbConn.close();
          		}
          	}
          	// end --> Mandantenaustausch
          }
          else{
          	logger.debug("Debug: ELSE FILTER 2")
          	try {
          		dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver, address, username, password);
          		var updateSQL = "USE [ExterneDaten] UPDATE [dbo].[PatientendatenScancenter] SET [DOKUMENTENSTATUS] = 'PAT / CASE ID NOT VALID FILTER 2', [BEARBEITUNGSZEITPUNKT] = '" + dateString + "' WHERE [DOCUMENTID] = '" + msg['documentid'].toString() + "';";
          		result = dbConn.executeUpdate(updateSQL);
          	}
          	catch (e){
          		logger.info("Status of writing back [DOKUMENTENSTATUS]: " + e + " on channel: " + channelName);
          	} finally {
          		if (dbConn) {
          			dbConn.close();
          		}
          	}
          	throw('CASE/PAD ID ERROR');
          	return false; //filter durchlassen
          }
          Work for a NPO health care provider in Berlin
          • Current Version of MC 3.4.1.770 with the onboard MySQL Database on a Windows Server 2012 R2

          Comment


          • #6
            Destination database_response

            Code:
            var dateString = DateUtil.getCurrentDate('yyyy-dd-MM HH:mm:ss.000');
            var driver = 'net.sourceforge.jtds.jdbc.Driver';
            var address = 'jdbc:jtds:sqlserver://XXX/ExterneDaten';
            var username = 'XXX';
            var password = 'XXX';
            var dbConn = null;
            var result = null;
            var dateStringDefault = '1900-01-01T00:00:00.000';
            var msgMandant = $('mandant');
            
            if ((msgMandant == 'MLK' || msgMandant == 'EWK') && !!$('pfad')){
            	try {
            		try {
            			dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver, address, username, password);
            			var updateSQL = "USE [ExterneDaten] UPDATE [dbo].[PatientendatenScancenter] SET [DOKUMENTENSTATUS] = 'SUCCESS writing file', [BEARBEITUNGSZEITPUNKT] = '" + dateString + "' WHERE [DOCUMENTID] = '" + $('documentid') + "'";
            			result = dbConn.executeUpdate(updateSQL);
            		} finally {
            			if (dbConn) {
            				dbConn.close();
            			}
            		}
            	}
            	catch (e) {
            		try {
            			dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver, address, username, password);
            			var updateSQL = "USE [ExterneDaten] UPDATE [dbo].[PatientendatenScancenter] SET [DOKUMENTENSTATUS] = 'DB ERROR file write', [BEARBEITUNGSZEITPUNKT] = '" + dateStringDefault + "' WHERE [DOCUMENTID] = '" + $('documentid') + "'";
            			result = dbConn.executeUpdate(updateSQL);
            		} finally {
            			if (dbConn) {
            			dbConn.close();
            			}
            		logger.info("updateSQL error on Channel: " + channelName + " caused by: " + e);
            		}
            	}
            }
            else if ((msgMandant != 'MLK' || msgMandant != 'EWK') || $('pfad') === 'null') {
            	try {
            		try {
            			dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver, address, username, password);
            			var updateSQL = "USE [ExterneDaten] UPDATE [dbo].[PatientendatenScancenter] SET [DOKUMENTENSTATUS] = 'NOFILE / INVALID MANDAT / PATID or ERROR writing file', [BEARBEITUNGSZEITPUNKT] = '" + dateString + "' WHERE [DOCUMENTID] = '" + $('documentid') + "'";
            			result = dbConn.executeUpdate(updateSQL);
            		} 
            		catch (e){
            			logger.info("updateSQL error on Channel: " + channelName + " caused by: " + e);
            		} finally {
            			if (dbConn) {
            				dbConn.close();
            			}
            		}
            	}
            	catch (e) {
            		try {
            			dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver, address, username, password);
            			var updateSQL = "USE [ExterneDaten] UPDATE [dbo].[PatientendatenScancenter] SET [DOKUMENTENSTATUS] = 'DB ERROR while writing file', [BEARBEITUNGSZEITPUNKT] = '" + dateStringDefault + "' WHERE [DOCUMENTID] = '" + $('documentid') + "'";
            			result = dbConn.executeUpdate(updateSQL);
            		}
            		catch (e){
            			logger.info("updateSQL error on Channel: " + channelName + " caused by: " + e);
            			throw('DB ERROR while writing file');
            		} finally {
            			if (dbConn) {
            			dbConn.close();
            			}
            		}
            	}
            	logger.info("Mandant not found for [DOCUMENTID]: " + $('documentid') + ". Expect MLK or EWK. Msg Mandant: " + $('mandant') + " on: " + channelName + ". Throwing expception");
            	throw('INVALID MANDAT or ERROR writing into Database');
            }
            else { //irgendwas schiefgelaufen / share nicht erreichbar
            	logger.debug("Debug: ELSE DEST DB WRITER")
            	try {
            		dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver, address, username, password);
            		var updateSQL = "USE [ExterneDaten] UPDATE [dbo].[PatientendatenScancenter] SET [DOKUMENTENSTATUS] = 'Mandant not found. Expect MLK or EWK', [BEARBEITUNGSZEITPUNKT] = '" + dateString + "' WHERE [DOCUMENTID] = '" + $('documentid') + "';";
            		result = dbConn.executeUpdate(updateSQL);
            	}
            	catch (e){
            		logger.info("updateSQL error on Channel: " + channelName + " caused by: " + e);
            		throw('DB ERROR while writing file');
            	} finally {
            		if (dbConn) {
            		dbConn.close();
            		}
            	}
            	logger.info("NOFILE / MANDAT not found or ERROR writing file for [DOCUMENTID]: " + $('documentid') + ". Msg mandant: " + $('mandant') + ". Msg path: " + $('pfad') + ". on: " + channelName + ". Throwing expception");
            	throw('NOFILE / INVALID MANDAT / PAT or ERROR writing file');
            	return false;
            }
            Work for a NPO health care provider in Berlin
            • Current Version of MC 3.4.1.770 with the onboard MySQL Database on a Windows Server 2012 R2

            Comment


            • #7
              How does ${documentid} get set? From the stack trace, I think this value is null at the time of polling.

              Originally posted by con View Post
              From the Database Reader:

              Code:
              USE [ExterneDaten]
              BEGIN
              SELECT [DOCUMENTID]
                    ,[STAPELNUMMER]
                    ,[DOCUMENTTYP]
                    ,[MANDANT]
                    ,[FALLNUMMER]
                    ,[PATIENTENID]
                    ,[NACHNAME]
                    ,[VORNAME]
                    ,[GEBDATUM]
                    ,[AUFNAHMEDATETIME]
                    ,[ENTLASSDATETIME]
                    ,[DOCUMENTREGISTER]
                    ,[CREATEDEPARTMENT]
                    ,[DOCUMENTCLASS]
                    ,[DOCUMENTKIND]
                    ,[DOCUMENTDATE]
                    ,[CREATEDATE]
                    ,[DOKUMENTENSTATUS]
                    ,[BEARBEITUNGSZEITPUNKT]
                    ,[DOKUMENTHERKUNFT]
                    ,[BEHANDLUNGSART]
                    ,[SCANCLIENT]
                    ,[BENUTZER]
                    ,LEFT([FALLNUMMER], 2) AS 'MandantID'
                    ,[ExterneDaten].[dbo].[Kofax_PatDaten_DocExport].[DokumentPfad] AS [PFAD]
                FROM [ExterneDaten].[dbo].[PatientendatenScancenter]
                INNER JOIN [ExterneDaten].[dbo].[Kofax_PatDaten_DocExport] 
                ON [ExterneDaten].[dbo].[PatientendatenScancenter].[DOCUMENTID] = [ExterneDaten].[dbo].[Kofax_PatDaten_DocExport].[DokumentID]
                WHERE [DOKUMENTENSTATUS] IS NULL;
                
                UPDATE [dbo].[PatientendatenScancenter]
                 SET [DOKUMENTENSTATUS] = 'cached'
                WHERE [DOCUMENTID] = ${documentid};
              END

              Comment


              • #8
                ${documentid} is generated out of the XML from the database query in the source transformer. I think this error occurs, when something went wrong with the mirth. E.g. last time, when the mcservice was not shut down correctly while there was a reboot
                Work for a NPO health care provider in Berlin
                • Current Version of MC 3.4.1.770 with the onboard MySQL Database on a Windows Server 2012 R2

                Comment


                • #9
                  Well, the source transformer runs after your db reader...

                  Comment


                  • #10
                    ok thank you agermano. that wasn't clear. I think I have to write an db Reader script for the source as well to map it before Transformation or set it into a variable before in a T-SQL

                    maybe like this:

                    Code:
                    USE [ExterneDaten]
                    BEGIN
                    DECLARE @curDocID int;
                    SET @curDocID = (SELECT TOP 1 [DOCUMENTID] FROM [ExterneDaten].[dbo].[PatientendatenScancenter].[DOCUMENTID] WHERE [DOKUMENTENSTATUS] IS NULL ORDER BY [DokumentID] ASC) /*get the 1st unprocessed entry*/
                    
                    SELECT TOP 1 [DOCUMENTID] /*Maybe not neccessery if top 1 */
                          ,[STAPELNUMMER]
                          ,[DOCUMENTTYP]
                          ,[MANDANT]
                          ,[FALLNUMMER]
                          ,[PATIENTENID]
                          ,[NACHNAME]
                          ,[VORNAME]
                          ,[GEBDATUM]
                          ,[AUFNAHMEDATETIME]
                          ,[ENTLASSDATETIME]
                          ,[DOCUMENTREGISTER]
                          ,[CREATEDEPARTMENT]
                          ,[DOCUMENTCLASS]
                          ,[DOCUMENTKIND]
                          ,[DOCUMENTDATE]
                          ,[CREATEDATE]
                          ,[DOKUMENTENSTATUS]
                          ,[BEARBEITUNGSZEITPUNKT]
                          ,[DOKUMENTHERKUNFT]
                          ,[BEHANDLUNGSART]
                          ,[SCANCLIENT]
                          ,[BENUTZER]
                          ,LEFT([FALLNUMMER], 2) AS 'MandantID'
                          ,[ExterneDaten].[dbo].[Kofax_PatDaten_DocExport].[DokumentPfad] AS [PFAD]
                      FROM [ExterneDaten].[dbo].[PatientendatenScancenter]
                      INNER JOIN [ExterneDaten].[dbo].[Kofax_PatDaten_DocExport] 
                      ON [ExterneDaten].[dbo].[PatientendatenScancenter].[DOCUMENTID] = [ExterneDaten].[dbo].[Kofax_PatDaten_DocExport].[DokumentID]
                      WHERE [DOKUMENTENSTATUS] IS NULL;
                      
                      UPDATE [dbo].[PatientendatenScancenter]
                       SET [DOKUMENTENSTATUS] = 'cached'
                      WHERE [DOCUMENTID] = @curDocID; 
                    END
                    why I did it : I want to poll only one Inbound message at once. then mark it as cached after the SELECT to not poll this entry again if there are more than 1 entry with Document Status = NULL

                    does a database Reader read only one value sequentially (First In) or how does it generate the XML out of the query (if I use "1" as Max Processing Threads)? I did't get this info out of the Mirth User Guide
                    Last edited by con; 09-04-2019, 06:11 AM. Reason: missing informations
                    Work for a NPO health care provider in Berlin
                    • Current Version of MC 3.4.1.770 with the onboard MySQL Database on a Windows Server 2012 R2

                    Comment


                    • #11
                      When using a db reader, the entire ResultSet from the query is returned, and each row becomes a new message. I believe they are processed in order on a single thread even when multiple source threads are enabled unless you also have the source queue turned on (at which point out-of-order processing is possible when using more than one thread.)

                      Are you saying the documentID may not be unique in the PatientendatenScancenter table, but you only want to process once per documentID per poll? If it is not unique, is there another unique record identifier for that table?

                      The usual way to use the Database Reader is to only place the select statement in your SQL section. The update statement would be placed in the "Run Post-Process SQL" section, and you can set it to run "After each message." The documentID for the current message will be available in the Post-Process section. This way you are not updating the record at the time of the query, but rather when processing of that record is complete.

                      Comment

                      Working...
                      X