Issue with Parameterized Queries in EnsLib.SQL.OutboundAdapter - Ensemble

Question

Question

Nimisha Joseph · Dec 2, 2024

Hi everyone,

I’m using EnsLib.SQL.OutboundAdapter in my InterSystems Ensemble operation to execute an insert statement in Sql Server Management Studio 20. While inline queries (e.g., constructing the query string with _ concatenation) work fine, I'm facing issues when trying to use parameterized queries.

Serialized JSON: {"CHI":"2894","Forename":"Janis"}

Include Ensemble

Class TNHS.Operation.RDS.PatientOperation Extends Ens.BusinessOperation{

Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";
Property Adapter As EnsLib.SQL.OutboundAdapter;
Method OnMessage(pRequest As TNHS.Data.RDS.Patient) As %Status
{

        $$$TRACE("Inside OnMessage..  method")

        Set patientJSON = pRequest.ToJSON()

        $$$TRACE("Serialized JSON: "_patientJSON)

        set sql="INSERT INTO RawData (DrugDetails) VALUES (?)"
        //perform the Insert
        set tSC = ..Adapter.ExecuteUpdate(.nrows,sql,patientJSON)        

         If $$$ISERR(tSC) {

        $$$LOGSTATUS(tSC) // Log error if insertion fails
        Quit tSC

        }

       Quit $$$OK
}

}

Is there a specific way to pass parameters using objectscript to sql server
Are there any known limitations or special configurations needed for parameterized queries with this adapter?
Could this be an issue with the SQL driver or database itself?

Any suggestions or best practices would be greatly appreciated. Thanks in advance!

Discussion (10)4

Log in or sign up to continue

score 0 · Answer 1 · 2024-12-02T11:39:01-05:00

Can you give some more information about what's happening? Is it returning an error? Does the JSON string look right in the trace log?

score 0 · Answer 2 · 2024-12-02T12:36:34-05:00

There are no errors returned. An empty string is being inserted into the database field, even though the trace shows the JSON correctly as {"CHI":"2894","Forename":"Janis"}.

score 0 · Answer 3 · 2024-12-02T14:26:46-05:00

I have never used JSON before to execute queries with an EnsLib.SQL.OutboundAdapter, but I have executed using

..Adapter.ExecuteUpdateParmArray. I would assume it works the same way

Method DeleteProviderAddress(pRequest As osuwmc.CPD.DataStructures.DeleteProviderAddress, Output pResponse As Ens.Response) As %Status
{

  set query = "DELETE FROM ProviderAddresses where Address_k = ? and DoctorNumber = ?"


  set par = 2

  set par(1) = pRequest.AddressKey

  set par(2) = pRequest.DoctorNumber



  set tSC = ..Adapter.ExecuteUpdateParmArray(.nrows,query,.par)



  if 'tSC write " DeleteProviderAddress Failed ",tSC

  quit tSC

}

score 0 · Answer 4 · 2024-12-09T08:40:26-05:00

I can't understand why would JSON be any good as a parameter option here.

However, just looking at the SQL fields and values do differ (don't know the table's structure, but guessing based on what I see).

DrugDetails vs. two fields/values in JSON.

@Scott Roth example shows what's an approach (not only though).

So, a bit more information would be helpful.

score 0 · Answer 5 · 2024-12-09T11:14:06-05:00

To resolve the issue with parameterized queries in the EnsLib.SQL.OutboundAdapter, make sure you're passing the parameters correctly using an array, as the ExecuteUpdate method expects the parameters to be passed in as an array. Here’s how you can adjust the code: objectscript

Set sql="INSERT INTO RawData (DrugDetails) VALUES (?)" Set params(1) = patientJSON Set tSC = ..Adapter.ExecuteUpdate(.nrows, sql, .params)

This method will bind the parameters to the query correctly. Ensure that the SQL Server ODBC driver and database configurations are compatible with parameterized queries. You may also want to check if the Ensemble configuration requires any additional settings for parameterized queries. 🍥

score 0 · Answer 6 · 2024-12-09T15:27:41-05:00

the ExecuteUpdate method expects the parameters to be passed in as an array

Actually, no, it expects parameters as single/literal values.

The method ExecuteUpdateParmArray() expects parameters to be passed in as an array

score 0 · Answer 7 · 2024-12-10T11:17:25-05:00

Things have slightly changed from our side.

initially used JSON to serialize data and pass it as a parameter to an INSERT statement.
Recently, we switched to storing XML data instead of JSON, and I am now trying to store this XML using the stored procedure

set tQuery="{ CALL dbo.SavePatientDetails (?) }"

Set tSC = ..Adapter.ExecuteProcedure(.tResultSnapshots, .tOutputParms, tQuery,,xmlContent)

Problem:

Parameterized Query: Works fine when using VARCHAR(8000) as the parameter type in the stored procedure.
Fails when using NVARCHAR(MAX) as the parameter type for passing XML data. The issue appears only when using the NVARCHAR(MAX) type, and I am unable to insert the XML content.

Your input is greatly appreciated. Thanks in advance for your help!

score 0 · Answer 8 · 2024-12-10T11:44:26-05:00

Does it works if you use NVARCHAR(8000)?

My guess is that IRIS does not understand "MAX", if so...maybe there is a possible solution.

P.S.: what version of IRIS?

score 0 · Answer 9 · 2024-12-10T11:59:48-05:00

@Enrico Parisi
No, NVARCHAR(8000) this doesn't work either.

I am using ensemble 2018 and database is SQL Server Management Studio 20

score 0 · Answer 10 · 2024-12-10T12:45:31-05:00

You may try with:

set tQuery="{ CALL dbo.SavePatientDetails (?) }"

Set tParams=1
Set tParams(1)=xmlContent
Set tParams(1,"SqlType")=$$$SqlVarchar ; or 12 if macro does not resolve
Set tSC = ..Adapter.ExecuteProcedureParmArray(.tResultSnapshots, .tOutputParms, tQuery,,.tParams)

For details see the SQL adapters documentation on Using Parameters.