web stats
SQL/JDBC errors with symbols in first or last name - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 05-22-2014, 01:02 AM
dobunzli dobunzli is offline
OBX.1 Kenobi
 
Join Date: Jun 2010
Posts: 48
dobunzli is on a distinguished road
Default SQL/JDBC errors with symbols in first or last name

Hello,
I have a mirth connect solution that "injects" lab results that I receive in HL7 into a filemaker database via jdbc.

I have a mirth connect channel with a "source transformer" that uses javascript and SQL commands to insert the appropriate informations in my database.

Everything works well as long as there are no special caracters in the first name or last name of the patients.
If the patient is named for instance M. Oliver O'Connor, there is an error in the syntax of the query, probably because of the "'" symbol.

first name is catched with Mapper and the variable is named "patientName_givenName"

last name is catched with Mapper and the variable is named "patientName_familyName"

How can I handle the special caracters like "'" into the SQL commands of my javascript.

Attached is the channel and below the error log.
Thanks in advance for your help.

Quote:
Transformer error
ERROR MESSAGE: Error evaluating transformer
com.mirth.connect.server.MirthJavascriptTransforme rException:
CHANNEL: Dianalab HL7 To Filemaker
CONNECTOR: sourceConnector
SCRIPT SOURCE: TRANSFORMER
SOURCE CODE:
94: lab_comments = lab_comments.replace (/\\.br.\\/g, " ");
95: // remplace les apostrophes ' par un espace
96: lab_comments = lab_comments.replace (/\'/g," ");
97:
98: //insertion des variables dans la base de donnée Filemaker avec une requete SQL
99: var result = dbConn.executeUpdate(
100: "INSERT INTO Laboratoire_Incoming (labin_labopatient_id, labin_labodossier_id, labin_medclipsepatient_id, labin_medclipseinvestigation_id, labin_patient_name, labin_patient_surname, labin_patient_dateofbirth, labin_patient_sex, labin_date_prelevement, labin_date_heure_reception_labo, labin_analysis_bbv_number, labin_analysis_name, labin_analysis_value, labin_analysis_unite, labin_analysis_normal_value, labin_analysis_interpretation, labin_analysis_remarques, labin_order_status, labin_processed, labin_errored) VALUES "
101: + "('"+ $('patientIdInternalId_id')+ "'"
102: + ",'" + $('labo_fillerOrderNumber_entityIdentifier')+ "'"
103: + ",'" + $('patientIdExternalId_id')+ "'"
LINE NUMBER: 99
DETAILS: Wrapped com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] FQL0001/(1:723): There is an error in the syntax of the query.
at 359f5c43-d502-42bd-9356-3c5ba4a2e34a:99 (doTransform)
at 359f5c43-d502-42bd-9356-3c5ba4a2e34a:125 (doScript)
at 359f5c43-d502-42bd-9356-3c5ba4a2e34a:127
at com.mirth.connect.server.transformers.JavaScriptFi lterTransformer$FilterTransformerTask.call(JavaScr iptFilterTransformer.java:134)
at com.mirth.connect.server.transformers.JavaScriptFi lterTransformer$FilterTransformerTask.call(JavaScr iptFilterTransformer.java:100)
at java.util.concurrent.FutureTask$Sync.innerRun(Futu reTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.jav a:138)
at java.util.concurrent.ThreadPoolExecutor$Worker.run Task(ThreadPoolExecutor.java:895)
at java.util.concurrent.ThreadPoolExecutor$Worker.run (ThreadPoolExecutor.java:918)
at java.lang.Thread.run(Thread.java:680)
Caused by: com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] FQL0001/(1:723): There is an error in the syntax of the query.
at com.filemaker.jdbc.FM_API.getAllResults(Unknown Source)
at com.filemaker.jdbc.FM_API.execDirect(Unknown Source)
at com.filemaker.jdbc.FM_API.execDirect(Unknown Source)
at com.filemaker.jdbc1.CommonJ1Statement.execDirect(U nknown Source)
at com.filemaker.jdbc1.CommonJ1Statement.execute(Unkn own Source)
at com.mirth.connect.server.userutil.DatabaseConnecti on.executeUpdate(DatabaseConnection.java:115)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.mozilla.javascript.MemberBox.invoke(MemberBox. java:126)
at org.mozilla.javascript.NativeJavaMethod.call(Nativ eJavaMethod.java:225)
at org.mozilla.javascript.Interpreter.interpretLoop(I nterpreter.java:1473)
at org.mozilla.javascript.Interpreter.interpret(Inter preter.java:815)
at org.mozilla.javascript.InterpretedFunction.call(In terpretedFunction.java:109)
at org.mozilla.javascript.ContextFactory.doTopCall(Co ntextFactory.java:394)
at org.mozilla.javascript.ScriptRuntime.doTopCall(Scr iptRuntime.java:3110)
at org.mozilla.javascript.InterpretedFunction.exec(In terpretedFunction.java:120)
at com.mirth.connect.server.util.javascript.JavaScrip tTask.executeScript(JavaScriptTask.java:49)
at com.mirth.connect.server.transformers.JavaScriptFi lterTransformer$FilterTransformerTask.call(JavaScr iptFilterTransformer.java:123)
... 6 more
Attached Files
File Type: xml Dianalab HL7 To Filemaker.xml (43.5 KB, 4 views)
Reply With Quote
  #2  
Old 05-22-2014, 02:29 AM
StefanScholte StefanScholte is offline
 
Join Date: May 2009
Location: Netherlands, Harderwijk
Posts: 321
StefanScholte is on a distinguished road
Default

You can wrap the query with sql escape like this:

Code:
var escaped = org.apache.commons.lang.StringEscapeUtils.escapeSql(sqlQuery);
Reply With Quote
  #3  
Old 05-22-2014, 03:28 AM
dobunzli dobunzli is offline
OBX.1 Kenobi
 
Join Date: Jun 2010
Posts: 48
dobunzli is on a distinguished road
Default

Thanks for your suggestions
I tried like this
Quote:
var escaped = org.apache.commons.lang.StringEscapeUtils.escapeSq l(
INSERT INTO Laboratoire_Incoming (labin_labopatient_id, labin_labodossier_id, labin_medclipsepatient_id, labin_medclipseinvestigation_id, labin_patient_name, labin_patient_surname, labin_patient_dateofbirth, labin_patient_sex, labin_date_prelevement, labin_date_heure_reception_labo, labin_analysis_bbv_number, labin_analysis_name, labin_analysis_value, labin_analysis_unite, labin_analysis_normal_value, labin_analysis_interpretation, labin_analysis_remarques, labin_order_status, labin_processed, labin_errored) VALUES "
+ "('"+ $('patientIdInternalId_id')+ "'"
+ ",'" + $('labo_fillerOrderNumber_entityIdentifier')+ "'"
+ ",'" + $('patientIdExternalId_id')+ "'"
+ ",'" + $('medclipse_fillerOrderNumber_entityIdentifier')+ "'"
+ ",'" + $('patientName_familyName')+ "'"
+ ",'" + $('patientName_givenName')+ "'"
+ ",'" + $('dateOrTimeOfBirth_value')+ "'"
+ ",'" + $('patientIdentification_sex')+ "'"
+ ",'" + $('date_prelevement_observationDateOrTime')+ "'"
+ ",'" + $('date_reception_labo_specimenReceivedDateOrTime' )+ "'"
+ ",'" + lab_number + "'"
+ ",'" + lab_name + "'"
+ ",'" + lab_result + "'"
+ ",'" + lab_unite + "'"
+ ",'" + lab_normes + "'"
+ ",'" + lab_interpretation + "'"
+ ",'" + lab_comments + "'"
+ ",'" + $('labo_orderStatus')+ "'"
+ ",'0'"
+ ",'0')
);
var result = dbConn.executeUpdate(escaped);
But then I received this error

Quote:
Transformer error
ERROR MESSAGE: Error evaluating transformer
com.mirth.connect.server.MirthJavascriptTransforme rException:
CHANNEL: Dianalab HL7 To Filemaker
CONNECTOR: sourceConnector
SCRIPT SOURCE: TRANSFORMER
SOURCE CODE:
117: + ",'" + lab_comments + "'"
118: + ",'" + $('labo_orderStatus')+ "'"
119: + ",'0'"
120: + ",'0')"
121: );
122: var result = dbConn.executeUpdate(escaped);
123: }
124: dbConn.close();
125: if ('xml' === typeof msg && msg.hasSimpleContent()) { msg = msg.toXMLString(); }if ('xml' === typeof tmp && tmp.hasSimpleContent()) { tmp = tmp.toXMLString(); }
126: }
LINE NUMBER: 122
DETAILS: Wrapped com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] FQL0001/(1:525): There is an error in the syntax of the query.
at 09ed4cc9-7980-4c74-989d-fb3fe305e336:122 (doTransform)
at 09ed4cc9-7980-4c74-989d-fb3fe305e336:127 (doScript)
at 09ed4cc9-7980-4c74-989d-fb3fe305e336:129
at com.mirth.connect.server.transformers.JavaScriptFi lterTransformer$FilterTransformerTask.call(JavaScr iptFilterTransformer.java:134)
at com.mirth.connect.server.transformers.JavaScriptFi lterTransformer$FilterTransformerTask.call(JavaScr iptFilterTransformer.java:100)
at java.util.concurrent.FutureTask$Sync.innerRun(Futu reTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.jav a:138)
at java.util.concurrent.ThreadPoolExecutor$Worker.run Task(ThreadPoolExecutor.java:895)
at java.util.concurrent.ThreadPoolExecutor$Worker.run (ThreadPoolExecutor.java:918)
at java.lang.Thread.run(Thread.java:680)
Could it be that the HL7 file is wrongly created, here is its content (anonymized)

Quote:
MSH|^~\&|LABCH007|L_BBV|MED|HL7|20140515101305||OR U^R01|15254352|P|2.3
PID|||021777003^^^GLIMS||O'CONNOR^OLIVER||19820408 000000|F|||Bas 2^^gnhr^^2341^CH
ORC|NW|71131136|5-148563-2204S^L_BZV||CM||^^^20140513092521|||||NAME^NAME Vorname^^^^^^^^GLIMS|||20140515000000||||
OBR|1||5-140513-2204S^L_BBV||||20140513082200|||||||20140513092521 ||NAME^NAME Vorname^^^^^^GLIMS||||POED||20140515000000|||F|||| ||||||||||||||
OBX|1|SN|VITB12^Vitamine B12^GLIMS||248|pmol/L|123-487|N|||F||||||
OBX|2|SN|FOL^Folates^GLIMS||25.1|nmol/L|10.4-42.4|N|||F||||||
Thanks
Reply With Quote
  #4  
Old 05-22-2014, 05:00 AM
rodrosa rodrosa is offline
OBX.2 Kenobi
 
Join Date: Jul 2013
Posts: 79
rodrosa is on a distinguished road
Default

I think that this usage are wrong, but i not sure about that, cause you are trying to use build your query inside the escape function.

I suggest you to build your query and them scape the final SQL using escape function.
Reply With Quote
  #5  
Old 05-22-2014, 06:18 AM
dobunzli dobunzli is offline
OBX.1 Kenobi
 
Join Date: Jun 2010
Posts: 48
dobunzli is on a distinguished road
Default

Hello,
Actually resolution of the problem was finally quite easy.
In my source transformer, with my name variables selected, I used "String replacement"
--> "Regular Expression" --> "'"
--> "Replace With" --> "''"

And everything works well.
Thanks for your ideas and support !
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 11:27 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation