Error When Inserting a HL 7 Message into SQL Database

Question

Question

Pardha Mutyala · Mar 14, 2018

I am using SQL Outbound adapter and inserting a HL 7 Message by executing a stored procedure and getting an error

ERROR #6022: Gateway failed: Execute.
+
ERROR <Ens>ErrGeneral: SQLState: (22001) NativeError: [0] Message: [Microsoft][ODBC SQL Server Driver]String data, right truncation

The reason i found is that segment delimiter is causing an issue and following is sample code

Method AddMessage(pRequest As EnsLib.HL7.Message, Output pResponse As Ens.Response) As %Status
{
   Set tQuery="{call dbo.HL7Message_Insert(?,?,?) }"
   Set params = 3


   set params(1)= "XYZ"
    set params(1,"SqlType") = 12

   set params(2)= pRequest.RawContent
    set params(2,"SqlType") = 12

   set params(3) = ""
   set params(3, "SqlType") = 12

   set tSC = ..Adapter.ExecuteProcedureParmArray(.tRTs,.tOutParms,tQuery,"iio",.params)

   return tSC
}

My understanding is that the segment delimiter is causing the issue as it is truncating the message which is causing the issue, i tried to insert only the Message header and it works -- so no issue with stored procedure or connecting to database, similarly i removed delimiter between message header and PID segment and removed other segments from message and it inserts the message.

Any help to resolve this will be greatly appreciated.

Discussion (9)1

Log in or sign up to continue

Scott Roth · Mar 15, 2018

I have always called it the other way around with the outbound before the I in the Execute Procedure Parm Array.

Method InsertProviderDivisionSp(pRequest As osuwmc.CPD.DataStructures.InsertProviderDivision, Output pResponse As Ens.Response) As %Status
{
set SPQuery = "{ ?= call usp_Interfaces_Insert_ProviderDivision_Ens(?,?,?,?) }"
set par = 4
set par(1) = pRequest.DoctorNumber
set par(2) = pRequest.Division
set par(3) = pRequest.UpdatedBy
set par(4) = pRequest.OrderBy

set tSC = ..Adapter.ExecuteProcedureParmArray(.InsertDivision,.outputs,SPQuery,"oiiii",.par)

if 'tSC write " failed ",tSC
quit tSC
}

or you can call it like...

Method InsertProviderPreference(pRequest As osuwmc.CPD.DataStructures.InsertPreferences, Output pResponse As Ens.Response) As %Status
{
set SPQuery = "{ ?= call usp_Interfaces_Insert_ProviderPreference(?,?,?,?,?) }"

set parm = 6
set parm(1,"SqlType")=$$$SQLVARCHAR
set parm(1,"IOTypes")=$$$SQLPARAMOUTPUT

set parm(2) = pRequest.DoctorNumber
set parm(2,"SqlType")=$$$SQLVARCHAR
set parm(2,"IOTypes")=$$$SQLPARAMINPUT

set parm(3) = pRequest.Preference
set parm(3,"SqlType")=$$$SQLNUMERIC
set parm(3,"IOTypes")=$$$SQLPARAMINPUT

set parm(4) = pRequest.PreferenceValue
set parm(4,"SqlType")=$$$SQLNUMERIC
set parm(4,"IOTypes")=$$$SQLPARAMINPUT

set parm(5) = pRequest.PreferenceDesc
set parm(5,"SqlType")=$$$SQLVARCHAR
set parm(5,"IOTypes")=$$$SQLPARAMINPUT

set parm(6) = pRequest.UpdatedBy
set parm(6,"SqlType")=$$$SQLVARCHAR
set parm(6,"IOTypes")=$$$SQLPARAMINPUT

set tSC = ..Adapter.ExecuteProcedureParmArray(.InsertPreference,.outputs,SPQuery,"oiiiii",.parm)

if 'tSC write "Failed",tSC
quit tSC
}

0 0

score 0 · Answer 1 · 2022-03-17T00:08:02-04:00

Hi there,

I'm trying to insert HL7 messages into a DB using the Stored Procedure.

Stored procedure contains two parameter - 1 input (for HL7 raw content) , 1 input/output parameter.

Execute SPROC as - EXEC sp_insertMessage @message, @result = @output OUTPUT;

Below code is not inserting the message as required. Not sure how to define the input/output parameter

               set arrParam = 2

               //for input and output parameter
               set arrParam(1,"IOType") = 2
               set arrParam(1,"SqlType") = $$$SqlLongVarchar

               //for input parameter
               set arrParam(2)= Message
               set arrParam(2,"SqlType") = $$$SqlLongVarchar
               set arrParam(2,"CType") = $$$SqlWChar
               set arrParam(2,"IOType") = 1
               set arrParam(2,"Prec") = 8000
               set arrParam(2,"LOB") = 1
               set arrParam(2,"UseLOBLen")= 1
               set arrParam(2,"SqlTypeName") = "TEXT"

Set tQuery="{?=call dbo.sp_insertMessage(?,?) }"

Set tSC = ..Adapter.ExecuteProcedureParmArray(,.tOutParams,tQuery,"bi",.arrParam)

Thank you.

score 0 · Answer 2 · 2022-03-17T10:49:22-04:00

If the Stored Procedure is just returning a RETURN statement then you should not need to define it in tQuery. So I am imagining that it would look something like this... If you are returning something other than just the RETURN statement from the Stored Procedure it will be a little different.

               set arrParam = 2

               //for input and output parameter
               set arrParam(1,"IOType") = 2
               set arrParam(1,"SqlType") = $$$SqlLongVarchar

               //for input parameter
               set arrParam(2)= Message
               set arrParam(2,"SqlType") = $$$SqlLongVarchar
               set arrParam(2,"CType") = $$$SqlWChar
               set arrParam(2,"IOType") = 1
               set arrParam(2,"Prec") = 8000
               set arrParam(2,"LOB") = 1
               set arrParam(2,"UseLOBLen")= 1
               set arrParam(2,"SqlTypeName") = "TEXT"

Set tQuery="{?=call dbo.sp_insertMessage(?) }"

Set tSC = ..Adapter.ExecuteProcedureParmArray(.InsertMessage,.tOutParams,tQuery,"oi",.arrParam)

score 0 · Answer 3 · 2022-03-17T21:03:07-04:00

Hi Scott,

Thanks for your reply.

The stored procedure doesn't have any return value. Instead it has a Input/Output Parameter (IO type -2). So As you said, I would like to return the OUTPUT value from the stored procedure. Below code is inserting the HL7 message into the DB. But I want to know how do I capture the output value returned by the SPROC.

//Two parameters, one input and one input/output
               set arrParam = 2

               //define input parameter
               set arrParam(1)= HL7Message
               set arrParam(1,"SqlType") = $$$SqlLongVarchar
              set arrParam(1,"CType") = $$$SqlWChar
               set arrParam(1,"Prec") = 8000
               set arrParam(1,"LOB") = 1
               set arrParam(1,"UseLOBLen")= 1
               set arrParam(1,"SqlTypeName") = "TEXT"

               //define input/output parameter
               set arrParam(2) = ""
               set params(2, "SqlType") = $$$SqlLongVarchar

             //define the query without return value syntax. No return value defined in SP
             Set tQuery="{call dbo.sp_insertMessage(?,?) }"

         Set tSC = ..Adapter.ExecuteProcedureParmArray(,.tOutParams,tQuery,"ib",.arrParam)    $$$TRACE(tSC)

   //Here I would like to get the Stored procedure's output value - NOT WORKING currently
    set sOut = tOutParams.GetNext(.key)
   $$$TRACE("output " _sOut)

   if 'tSC write " failed ",tSC
quit tSC

score 0 · Answer 4 · 2022-03-18T08:18:00-04:00

I am not sure I am following what you are trying to do. In my experience with calling stored procs from an external SQL database, I have always returned a RETURN value to tell me if the procedure executed successfully.

I have done a few cases that MS SQL Server assigned a Identity value after the stored procedure executed. In that case I define the output as Output pResponse As EnsLib.SQL.Snapshot within the Method definition, then I will return/loop through the EnsLib.SQL.Snapshot within the Business Process to get the value I need.

Method FetchHospitalEntityKSp(pRequest As osuwmc.CPD.DataStructures.FetchHospitalEntityK, Output pResponse As EnsLib.SQL.Snapshot) As %Status

{

set SPQuery = "{ ?= call CPD.dbo.usp_Interfaces_Fetch_HospitalEntityK_Ens(?) }"

s parm=2

s parm(1,"SqlType")=$$$SQLVARCHAR

s parm(1,"IOTypes")=$$$SQLPARAMOUTPUT

s parm(2)=pRequest.Entity

s parm(2,"SqlType")=$$$SQLVARCHAR

s parm(2,"IOTypes")=$$$SQLPARAMINPUT

set tSC = ..Adapter.ExecuteProcedureParmArray(.FetchEntities,.outputs,SPQuery,"oi",.parm)

if tSC = 1

{

set pResponse = FetchEntities.GetAt(1)

}

quit tSC

}

score 0 · Answer 5 · 2022-03-18T18:10:11-04:00

To execute the SP we need to write sql query like below;

EXEC sp_insertMessage @message, @result = @output OUTPUT;

print @result

@result parameter returns string "Failed" or "Passed"

So my input/out parameter in the code should return the SP OUTPUT value.

score 0 · Answer 6 · 2022-03-18T09:20:45-04:00

The value returned for the output parameter will be stored in the tOutParams array.

Are you still getting an error?

score 0 · Answer 7 · 2022-03-18T18:16:27-04:00

No, tOutParams returns null because I'm passing null value during the input/output parameter definition. So it is just returning the initial value. Somehow I need to define this parameter to grab the SP output value.

Stored Procedure - EXEC sp_insertMessage @message, @result = @output OUTPUT;

     //define input/output parameter
               set arrParam(2) = ""
               set params(2, "SqlType") = $$$SqlLongVarchar

             //define the query without return value syntax. No return value defined in SP
             Set tQuery="{call dbo.sp_insertMessage(?,?) }"

         Set tSC = ..Adapter.ExecuteProcedureParmArray(,.tOutParams,tQuery,"ib",.arrParam)    $$$TRACE(tSC)

   //Here I would like to get the Stored procedure's output value - NOT WORKING currently
    set sOut = tOutParams.GetNext(.key)
   $$$TRACE("output " _sOut)

score 0 · Answer 8 · 2022-03-31T00:51:24-04:00

Nisha Kugappiriyan · Mar 31, 2022

Resolution/workaround - https://community.intersystems.com/post/sql-outbound-adapter-execute-pro...

0 0