Database Error

[Shibu Mathew]

Hi Everybody,

I have couple of problems :

1. I am inserting the patient info to a SQL database and its working fine. But every time, there is an apostrophe in the name (fox ex O’MARA) , I am getting “Incorrect Syntax Error”.

“MicrosoftODBC SQL Server DriverSQL Server Incorrect syntax near MARA “

How can I pass the punctuation mark(‘)  in the name to the stored procedure.  

2. How do I remove/by pass this stuck message from the live queue so that the messages following could go through.

I will really appreciate your help. Thank you.

Topic

[Jim Kosloskey]

As for your first issue – this is one reason why I like to invoke Stored Procedures that the receiving system maintains rather than code the SQL myself.

Many of these types of issues can be resolved (or should be) by the receiving system DBA and are not always an integration issue in my opinion. Once the Sql code becomes imbedded in Cloverleaf(R0 the issues do become an integration issue.

As to your second issue, you need to trap the errors in your code then decide what you want to do with the message that triggered the ODBC activity.

One thing you could do is to ERROR the message which would move it from the Recovery to the Error Db and the next pending mesage woul be handled.

There are other options but you need to decide what is best for you.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Shibu Mathew]

Jim,

I will change the code to write the message to error database and move on to the next message, instead of queuing them all in the recovery database. For now, support helped me remove that message from the recovery database.

I am parsing the hl7 fields and passing the values to the stored procedure using the below sql statement.

set sqlStatement “{exec vhs_AddDataToPatientAccounts ‘$mrn’,’$lname’,’$fname’,’$gender’,’$AcctNum’}”

When I have a name like O’HARA in the lname field, the $lname parameter in Sql statement breaks after the “O” because of the punctuation mark (‘). How can I use the regular expression to replace the punctuation mark in the last name with space, before passing it to the stored procedure.

Thank you.

Shibu

[Jim Kosloskey]

Give string map a look.

Something like;

set x [sting map “‘ { }”] $y]

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jim Kosloskey]

You could also ask your Sql Server DBA what encapsulation you could use for the field to allow the ‘ to be treated as a part of the field.

Maybe something like “O’Hara”.

Then add the encapsulation to the field before invoking the exec.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jim Kosloskey]

Actually I think the field when received by SQL Server needs to have the ‘ enclosed in ‘ (O”’Hara for example).

So your command might be like this:

set lname [string map “‘ ”'” $lname]

That should change O’Hara to O”’Hara and that might be acceptable to Sql Server and preserve the ‘.

Of course if you have more than one ‘ in the field you will get multiple substitutions.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Ed Mastascusa]

We encountered this issue before – we’re actually creating the SQL to exec within an XLT and our fix was a tps post proc that does the “‘” character substitution with 2 single quotes.

set newMsg [ string map { ”  ‘ } $msgData ]

[Shibu Mathew]

I was not getting the right output with  string map , so I used regsub to change the single quote to double quotes.  Thanks for your help Jim.

set lctr [regsub -all {‘} $lname {“} newlname]

           echo lctr:$lctr

           if {$lctr == 1} {

           set Lname $newlname

          } else {

           set Lname $lname

          }

[Charlie Bursell]

If you look at almost any database guide it will tell you that in an ascii string you must double the tick (‘) marks.  I don’t know what problems you have with string map but when inserting into a database I *ALWAYS* do something like

set INSERT [string map

$INSERT]  (That is two ticks not double quote)

I have done a hundred or more and never a problem.

If you query for a name like O’Hara you could manually add the double ticks like

O”Hara

[Shibu Mathew]

Charlie,

The two ticks work. Thank you !! I was not doubling the tick, instead I was using double quotes and ended up getting the tcl error , “unmatched open quote in list”.

Shibu

[Author]

Replies