Issue with Parameterized Queries in EnsLib.SQL.OutboundAdapter - Ensemble
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!
Comments
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?
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"}.
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 = 2set par(1) = pRequest.AddressKey
set par(2) = pRequest.DoctorNumber
set tSC = ..Adapter.ExecuteUpdateParmArray(.nrows,query,.par)
if 'tSC write" DeleteProviderAddress Failed ",tSC
quit tSC
}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.
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. 🍥
the
ExecuteUpdatemethod 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
Things have slightly changed from our side.
- initially used JSON to serialize data and pass it as a parameter to an
INSERTstatement. - 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 theNVARCHAR(MAX)type, and I am unable to insert the XML content.
Your input is greatly appreciated. Thanks in advance for your help!
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?
@Enrico Parisi
No, NVARCHAR(8000) this doesn't work either.
I am using ensemble 2018 and database is SQL Server Management Studio 20
You may try with:
set tQuery="{ CALL dbo.SavePatientDetails (?) }"Set tParams=1Set tParams(1)=xmlContent
Set tParams(1,"SqlType")=$$$SqlVarchar; or 12 if macro does not resolveSet tSC = ..Adapter.ExecuteProcedureParmArray(.tResultSnapshots, .tOutputParms, tQuery,,.tParams)For details see the SQL adapters documentation on Using Parameters.