problems with database reader

[deepaganu]

hey wanted to ask that when i configure my mirth as a database reader why does it read only one entry? dont you think it should read the entire database ?n well if my polling frequency is say 1 min so after one min is there any way to configure my mirth in such a way that i could read only the modified enteries plz help

[chrisl]

Re: problems with database reader

The database reader will read in ALL the records that meet the criteria - it creates one message for each row read in.

-Chris

[deepaganu]

Re: problems with database reader

well is there any way that i would be reading only modified enteries in the database . or else i have to write some adaptor outside mirth to send changes to mirth?

[chrisl]

Re: problems with database reader

Your SQL statement should check a field, either a timestamp or "status" field. Something like:

SELECT id, patient_name FROM patients WHERE status = 0;

Your on update would be:

UPDATE patients SET status = 1 WHERE id = ${id}

[deepaganu]

Re: problems with database reader

but if the modified entry is a new entry how would i be able to make out ? i will tell you my typical problem . i have data in my database ,which has to read n entered into my system , first time i would read all my data , but the second time i want only modified data so that i can put the changes as you know that after the polling time internval the entire database is read again why would i read the entire data base where as i want to read only the chnges in the database . i s mirth intelligent enough to understand or we need an extra adaptor for that .i think that would be a valuable change in your porject bcoz i wont read the data again if i have already read it

[chrisl]

Re: problems with database reader

When data is initially added to the database, the status should be set to 0. (For example).

Mirth reads in the data and will set the status to 1. The next time Mirth polls the database, it only reads in records where Status = 0.

When new records are added, their status will be 0, so Mirth will pick them up and update to 1.

Hope this helps!

[deepaganu]

Re: problems with database reader

but what if somebody modifies the data at the backend , the status would be still set to status=0, how will i come to know that somebody has modified it? will timestamp work ? if yes how will i distinguish that the data which i retrieved is fresh or modified version of the old data , in that case i will have to use an adapter n then the adapter passes the data to mirth channel? if there any way that i wont need to write an adapter n mirth can help? is mirth that intelligent ? or can we make it intelligent ? please help

[chrisl]

Re: problems with database reader

When a modification is made, the database status would also need to be updated in order for Mirth to read it in. You could also have Mirth operate by timestamp, although it would require a bit of scripting.

[albertosaez]

Reroblems with database reader

In such situations, A ·good· way of detect a new insert/modificaciont is to create a trigger associated with the evetns in the table, and write the data into a new table you can use for messages.

Post edited by: albertosaez, at: 04/24/2007 14:34

[nshaik]

Reroblems with database reader

If you are using SQL Server 2000 / 2005, you could use BINARY_CHECKSUM.

http://msdn2.microsoft.com/en-us/library/aa225956(SQL.80).aspx

Also refer SQL Server Books Online for more info.

Hope it helps!

[nshaik]

Reroblems with database reader

albertosaez suggestions to use triggers would be the best option. Use a default 0 (Not Processed) for the status colum.

When a new row is inserted the status would be 0 and Mirth HL7 Database reader would pick this up, and after processing, sets the status to 1 (Processed).

Add an UPDATE TRIGGER to set the status column back to 0 (Not Processed) if data in specific columns have changed (If you are using SQL Server 2000 / 2005 DELETED INSERTED Tables are typically used to do such checks - SQL Server Books Online has more info). Test for performance!

Hope this helps!