"Update" instead of "insert" on MySQL Database

[Svetlomir Kasabov]

Hello everyone,

I have the following situation:
Mirth receives an ADT-message for an admission of a patient. If the patient doesn't exist in the DB, mirth should write the new patient and his data to the database (that works fine). But if the patient already exists in the DB (this should be checked for example by the PID), I would like that Mirth executes an Update of the patients information, leaving his PID untouched.

My question is: How can I design the whole situation? Should I use Javascript and exactly where in mirth (There is possibility to use Javascript in the "destination" tab or on the "scripts" tab or as a filter rule). Is there a way to use Java instead of Javascript for such situations?

Thanks for any help! I appreciate it.

Svetlomir

[albertosaez]

Re:"Update" instead of "insert" on MySQL Database

You have two options:

* Use JavaScript to apply a Insert/update depending on the situation. This can be done in a transformer o in a destination DB. Of course, instead of JS you can use Java, but Why ?
* Use a stored procedure with do all the db stuff. This is my recomendation.

Post edited by: albertosaez, at: 02/17/2008 05:22

[Svetlomir Kasabov]

Re:"Update" instead of "insert" on MySQL Database

Hi albertosaez,

The problem is - I don't have good javascript knowedge. Do you know how to adjust Mirth so, that I can use Java in order to access the DB and execute database manipulations?

Should I understand under "stored procedure" many SQL statements? If yes - how can SQL support if-else statements? As far as I know, SQL doesn't support control flow.

Thanks.
Svetlomir

[albertosaez]

Re:"Update" instead of "insert" on MySQL Database

You can use Java in the transformer, using the import Javascript function (you can find examples of its use in the forum). But I don't recomend it. I think the JS approach is easier.

By "Stored procedure" I mean the SQL subroutines mosth db engines allow [http://en.wikipedia.org/wiki/Stored_procedure] What db are you using ?

[Svetlomir Kasabov]

Re:"Update" instead of "insert" on MySQL Database

Hello,
I use the MySQL database. I hope I can apply stored procedures to it.

Svetlomir

[albertosaez]

Re:"Update" instead of "insert" on MySQL Database

MySql allow storage procedures for verion >=5

But anyway, you can use JS code.

[Svetlomir Kasabov]

Re:"Update" instead of "insert" on MySQL Database

Hello albertosaez,

I learned how to write stored procedures from the "MySQL-AB" manual. I face now the following problem. When I create the stored procedure in the "Destinations"-tab in Mirth and send a HL7-Message to my channel, I get the following error-mesage:

ERROR-406: JDBC Connector error
ERROR MESSAGE: Error writing to database:
java.lang.IllegalArgumentException: Write statement should be an insert / update / delete sql statement
at com.webreach.mirth.connectors.jdbc.JdbcMessageDisp atcher.doDispatch(JdbcMessageDispatcher.java:120)


or


ERROR-406: JDBC Connector error
ERROR MESSAGE: Error writing to database:
java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';

create procedure insertUpdate(out return_val int)
begin
declare a, b int;
' at line 1 Query: INSERT IGNORE INTO patient2 (pid, firstname, lastname, status) values (?, ?, ?, 0);


Schould I save my procedure directly in the DB (not via Mirth)? And if so - how can I call my procedures in Mirth ?

My sql statement is:
INSERT IGNORE INTO patient2 (pid, firstname, lastname, status) values (${pid}, ${firstname}, ${lastname}, 0);
but I don't know how to invoke it in a stored procedure. Do you have any idea? Thanks for your help!

Svetlomir

[Svetlomir Kasabov]

Re:"Update" instead of "insert&quot



Post edited by: Svetlomir Kasabov, at: 02/19/2008 15:26

[albertosaez]

Re:"Update" instead of "insert" on MySQL Database

Yes, you have to create a stored procedure in mysql, not in Mirth.


To call to a stored procedure in mysql you have to execute "CALL nsertUpdate(pid,firstname,sencondName)" (being patientUpdate the name of the procedure, an pid, firstanem... the data

[Svetlomir Kasabov]

Re:"Update" instead of "insert" on MySQL Database

And how can I call the stored procedure in mirth when it's stored in the db? The call procedure doesnt work in mirth...
Thanks
Svetlomir

[albertosaez]

Re:"Update" instead of "insert" on MySQL Database

I think there are a lot of similar experiences in the forum, you could search "procedure" to find a lot of examples

[Svetlomir Kasabov]

Re:"Update" instead of "insert" on MySQL Database

Hello,

I read the thread "Calling stored procedure" and as far as I understood, I can call the stored procedure with javascript.

My guestion is: is there a way to call the stored procedure in mirth without using javacript?

Can you please paste some instert / update / delete statements triggering a stored procedure?

Thank you very much!

Svetlomir

[Svetlomir Kasabov]

Re:"Update" instead of "insert" on MySQL Database

Sorry, I didn't see your message.

Thanks a lot for your advice, it was very helpful.

Svetlomir