Adding Escape Characters

[oakwood]

I have everything just about finished in transforming .hl7 text files into 4 sql tables, but one problem I'm having is illegal characters in my insert statement. The note field in the HL7 documents sometimes contains ' and other characters that I can't insert into SQL.

I have a variable in my filter named notes, is there some java command that is out there that I can run this through to add escape characters in my filter?

Thanks,
Jeff

[nshaik]

Re: Adding Escape Characters

You could use a Transformer JavaScript step and use the replace function. As regards the ' (single quote), for SQL Server 2000 what I do is doubleup single quote. For e.g, for names link O'Brian,

tempLastName = tempLastName.replace(/'/g, "''");

Hope it helps!

[oakwood]

Re: Adding Escape Characters

Actually, I worked at it and found a solution. Here is what I came up with. I put this in the filter where I created the variable.

Notes is my variable that may contain a '

//Escape illegal characters in notes variable
var index = 0;
var StrOut = "";
//account for any apostrophes in the parameter
for (index = notes.indexOf("'"); index != -1; index = notes.indexOf("\'")) {
// Copy up to the apostrophe
StrOut += notes.substring(0, index);

// Add double apostrophe
StrOut += "''";
notes = notes.substring(index + 1);
//Chop off "used" part
}
StrOut += notes;


I then pass StrOut into SQL and it works great!

[nshaik]

Re: Adding Escape Characters

As I understand, any transformations that need to be done go as part of "Transformer" step. In general, Filter is used to let the types of messages that the channel need to be process, like

var hl7EventType = msg['MSH']['MSH.9']['CM_MSG.2'];

if (hl7EventType == 'A01' ||
    hl7EventType == 'A04' ||
    hl7EventType == 'A08'  ) {
   return true;   
}
else {   
    return false;
}

[oakwood]

Re: Adding Escape Characters

I actually decided to use the filter because I had multiple values that I need to loop through and I saw a tutorial that showed how to do something similar in the filter. So adding that statement to the filter along with the variables I created did the trick. And the whole project is pretty much done at this point!!!

[chirag]

Need help! New to Mirth. I have inbound HL7 message that has single quote for patinet’s first name. When I submit it to mysql I get error. In source transformer I tried to place:
.toString().replace(/'/g, "''");
That did not work. Also I tried to use strig split and join option and that did not work:
globalMap.put("MSH_3_1", msg['MSH']['MSH.3']['MSH.3.1'].toString().split("'").join("\\'"));
by making it a global variable.

It did not work even when I tried to use code template and or use transformer to just do simple “’” and replace it with double single quote “’’”. Please help what I am doing wrong with split and or join statement.