Execute a SQL Stored Procedure

[Gary]

Hi All,

Can anybody tell me why this doesn't work please?

set tSC
= ..Adapter.ExecuteProcedure(.ResultSnapshots,.tOutParms,"EXECUTE
dbo.spTest ?",,39)

This is a business operation with the EnsLib.SQL.OutboudAdapter.

If I take the parameter out of the stored procedure and use the
statement below then it works fine:

set tSC
= ..Adapter.ExecuteProcedure(.ResultSnapshots,.tOutParms,"Execute
dbo.spTest",,)

The stored procedure works fine in both scenarios so I am assuming I
am doing something wrong at the Ensemble side. When I set it up with
the parameter tSC gets set to the following error text:

0 I † Execute 3 zExecute+2^%Library.SQLGatewayConnection.
1:EDLPOCÉ <Ens>ErrGeneralz SQLState: (07002) NativeError: [0]
Message: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or
syntax error 5 zaddErrorList+6^EnsLib.SQL.OutboundAdapter.
1:EDLPOC

Which I believe is a standard error from SQL probably indicating
incorrect syntax. So if anybody could point me in the correct
direction of executing a MS SQL stored procedure with a single inbound
parameter of type Integer which returns a resultset then I would be
grateful.

Thanks,
Gary

[Nigel Timothy Bloom-Salm]

Hi

Shouldn't the syntax be:

set tSC=..Adapter.ExecuteProcedure(.ResultSnapshots,.tOutParms,"EXECUTE dbo.spTest(?)",,39)

Nigel Timothy Bloom-Salm

Technical Specialist

Hi All,

Thanks,
Gary

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com
To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en

[Gary]

Hi Nigel,

Thanks for the response, I have tried it with the brackets round the
parameter but I receive a warning "Found no Parameter 1 (used as 1)
for query 'EXECUTEdbo.spTest(?)'" from Ensemble followed by this from
SQL

0 I † Execute 3 zExecute+2^%Library.SQLGatewayConnection.

1:EDLPOCÖ <Ens>ErrGeneral‡ SQLState: (42000) NativeError: [170]
Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect syntax near '@P1'. 5 zaddErrorList
+6^EnsLib.SQL.OutboundAdapter.1:EDLPOCÖ <Ens>ErrGeneral‡ SQLState:
(42000) NativeError: [8180] Message: [Microsoft][ODBC SQL Server
Driver][SQL Server]Statement(s) could not be prepared.
5 zaddErrorList+6^EnsLib.SQL.OutboundAdapter.1:EDLPOC

Regards,
Gary

On May 24, 8:49 pm, "Nigel Timothy Bloom-Salm" <nigel.s...@gmail.com>
wrote:

> For more options, visit this group athttp://groups.google.com/group/Ensemble-in-Healthcare?hl=en- Hide quoted text -
>
> - Show quoted text -

[Dale du Preez]

Hi Gary,

How is your stored procedure defined? That may help in working out what the syntax should be. Does it take any arguments? Or does it just return some resultsets?

Dale
________________________________________
From: ensemble-in...@googlegroups.com [ensemble-in...@googlegroups.com] On Behalf Of Gary [fail...@googlemail.com]
Sent: 25 May 2011 10:07 AM

To: InterSystems: Ensemble in Healthcare

Subject: [InterSystems-EnsHlth] Re: Execute a SQL Stored Procedure

[Gary]

As an added note if I use the following statement then it works:

set tSC
= ..Adapter.ExecuteProcedure(.ResultSnapshots,.tOutParms,"EXECUTE

dbo.spTest 39",,)

> > For more options, visit this group athttp://groups.google.com/group/Ensemble-in-Healthcare?hl=en-Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -

[Gary]

Hi Dale,

I am just trying this out so my first test was to try a stored
procedure without any arguments and returning a resultset which worked
fine, then I tried adding a single input parameter of type Integer and
updating the object script to match, still just returning a resultset
only this time filtered. I am assuming that I am doing something wrong
somewhere at the object script level.

On May 25, 3:08 pm, Dale du Preez <Dale.duPr...@intersystems.com>
wrote:

> Hi Gary,
>
> How is your stored procedure defined? That may help in working out what the syntax should be. Does it take any arguments? Or does it just return some resultsets?
>
> Dale
> ________________________________________

> From: ensemble-in...@googlegroups.com [ensemble-in...@googlegroups.com] On Behalf Of Gary [fail.g...@googlemail.com]

> > For more options, visit this group athttp://groups.google.com/group/Ensemble-in-Healthcare?hl=en-Hide quoted text -

[Dale du Preez]

Hi Gary,

You may or may not be doing anything wrong in ObjectScript. The real trouble here is that things are complicated by the way the SQL adapter uses ODBC to pass arguments to your stored procedure, and the way your stored procedure and associated ODBC drivers expect the arguments to be passed. If I know what the definition of the stored procedure is in the target database, at least in terms of arguments and return values, I may be able to suggest a query that is slightly "better", both in terms of what the target database (and drivers) prefer, and what the SQL adapter prefers.

My guess is that you may want to start by trying CALL instead of execute (according to this MSDN site, SQL Server has an explicit preference for CALL syntax: http://msdn.microsoft.com/en-us/library/aa198007%28v=sql.80%29.aspx).

Did you have any luck trying either of the following?
= No arguments format
CALL dbo.spTest
= Single argument format
CALL dbo.spTest(?)

I hope that helps,
Dale
________________________________________
From: ensemble-in...@googlegroups.com [ensemble-in...@googlegroups.com] On Behalf Of Gary [fail...@googlemail.com]
Sent: 25 May 2011 10:14 AM

[Gary]

Hi Dale,

I have tried CALL for both no arguments and a single argument but
neither work so it seems that Execute must be the way forward, my
stored procedure is very simple:

CREATE PROCEDURE dbo.spTest

@ID AS INTEGER

AS

SELECT * FROM EDL_Discharge_Expired WHERE ID = @ID
GO

I can get it to work by specifying the parameter in the query string
argument as follows:

set tSC
= ..Adapter.ExecuteProcedure(.ResultSnapshots,.tOutParms,"EXECUTE

dbo.spTest 39",,) so there is a work around but I would just like to
do it as described in the documentation assuming that it would be best
practice etc.

Thanks,
Gary




On May 25, 3:45 pm, Dale du Preez <Dale.duPr...@intersystems.com>
wrote:
> Hi Gary,
>

> You may or may not be doing anything wrong in ObjectScript. The real trouble here is that things are complicated by the way the SQL adapter uses ODBC to pass arguments to your stored procedure, and the way your stored procedure and associated ODBC drivers expect the arguments to be passed. If I know what the definition of the stored procedure is in the target database, at least in terms of arguments and return values, I may be able to suggest a query that is slightly "better", both in terms of what the target database (and drivers) prefer, and what the SQL adapter prefers.
>
> My guess is that you may want to start by trying CALL instead of execute (according to this MSDN site, SQL Server has an explicit preference for CALL syntax:http://msdn.microsoft.com/en-us/library/aa198007%28v=sql.80%29.aspx).
>
> Did you have any luck trying either of the following?
>   = No arguments format
>       CALL dbo.spTest
>   = Single argument format
>       CALL dbo.spTest(?)
>
> I hope that helps,
> Dale
> ________________________________________

> > > For more options, visit this group athttp://groups.google.com/group/Ensemble-in-Healthcare?hl=en-Hidequoted text -

[Nigel Timothy Bloom-Salm]

Here is an example of how I use the adapter

Class User.DemographicsOperation Extends Ens.BusinessOperation [ ProcedureBlock ]
{

Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";

Parameter INVOCATION = "Queue";

Method UpdateCDWDemographics(pRequest As User.DemographicsRequest, Output pResponse As User.DemographicsResponse) As %Status
{
set tSC=$$$OK,$ztrap="Error"
// Set Auto Commit to True
set tSC=..Adapter.SetAutoCommit(1)
set pResponse=##class(User.DemographicsResponse).%New()
if pResponse=$$$NULLOREF $$$LOGERROR("CDW Demographics: Unable to Create Response Message") goto End
set tQuery="{ call PACK_LOAD_STAGE.PROC_INS_DEMO(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }"
set p(1)=pRequest.LabNumber,p(2)=pRequest.PatientName,p(3)=pRequest.PatientAddress1,p(4)=pRequest.PatientAddress2,p(5)=pRequest.PatientAddress3
set p(6)=pRequest.PatientAddress4,p(7)=pRequest.PatientPostCode,p(8)=pRequest.PatientTelephoneNumber,p(9)=pRequest.AuthorizationNumber
set p(10)=pRequest.HospitalId,p(11)=pRequest.NationalId,p(12)=pRequest.Nationality,p(13)=pRequest.SpecimenReferenceNumber,p(14)=pRequest.BirthDate
set p(15)=+pRequest.AgeYears, p(16)=pRequest.AgeMonths, p(17)=pRequest.AgeDays
set p(18)=pRequest.AgeStatus,p(19)=pRequest.Sex,p(20)=pRequest.Race,p(21)=pRequest.Location,p(22)=pRequest.Ward,p(23)=pRequest.HealthCareRegion,p(24)=pRequest.OracleRegion,p(25)=pRequest.SpecimenTypeCode
set p(26)=pRequest.ClinicalDiagnosisCode,p(27)=pRequest.ICD10Code,p(28)=pRequest.Infectious,p(29)=pRequest.Therapy,p(30)=pRequest.Priority
set p(31)=pRequest.PhlebotomistCode,p(32)=pRequest.Note,p(33)=pRequest.TakenDate,p(34)=pRequest.RegisteredDate,p(35)=pRequest.RegisteredBy
set p(36)=pRequest.ModifiedDateTime,p(37)=pRequest.ModifiedBy,p(38)=pRequest.FirstPrintedDate,p(39)=pRequest.ReferringDrCode,p(40)=pRequest.ReferringDrName
set p(41)=pRequest.BillableAccountNumber,p(42)=pRequest.MedialAid,p(43)=pRequest.MedicalAidNumber,p(44)=pRequest.FinancialClass
set p(45)=pRequest.InOutPatient,p(46)=pRequest.AdmissionDate,p(47)=pRequest.VisitNumber,p(48)=pRequest.RepositoryRegdataDate
// Now call the Adapter
set p=48
set tSC=..Adapter.ExecuteProcedureParmArray(.pResultSnapshots,.pOutParam,tQuery,"*",.p)
if +$g(p(49)) {
set pResponse.Success=0,pResponse.ErrorMessage="CDW Demographics: Update Error: SQL Code: "_p(49)
}
elseif 'tSC {
set pResponse.Success=0,pResponse.ErrorMessage="CDW Demographics: Update Error: "_$system.Status.GetErrorText(tSC)
}
else {
set pResponse.Success=1,pResponse.ErrorMessage=""
}
End ;
quit tSC
Error ;
set $ztrap=""
set tSC=$system.Status.Error(5001,"CDW Demographics: Update Error: "_$ze)
goto End
}

XData MessageMap
{
<MapItems>
<MapItem MessageType="User.DemographicsRequest">
<Method>UpdateCDWDemographics</Method>
</MapItem>
</MapItems>

[Gary]

Hi Nigel,

Thanks for that example, I have basically edited this for my
requirements and as you would expect it works! This is what my code is
now like:

set p(1) = 39
set p=1

set tSC
= ..Adapter.ExecuteProcedureParmArray(.ResultSnapshots,.tOutParms,"{ CALL
dbo.spTest (?) }","*",.p)

I then tried to take it apart piece by piece to find out what I was
doing wrong and from what I found the reason mine was failing was
because I didn't wrap the query in curly brackets, I also successfully
tried the following code:

set tSC = ..Adapter.ExecuteProcedure(.ResultSnapshots, .tOutParms,
"{ CALL dbo.spTest (?)}", , 39)

Having looked at the example in the documentation, it does include the
curly brackets so that is what I was doing wrong. Thanks again for
your help.

Thanks Nigel / Dale,

Gary



On May 25, 7:14 pm, "Nigel Timothy Bloom-Salm" <nigel.s...@gmail.com>
wrote:

> Here is an example of how I use the adapter
>
> Class User.DemographicsOperation Extends Ens.BusinessOperation [ ProcedureBlock ]
> {
>
> Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";
>
> Parameter INVOCATION = "Queue";
>
> Method UpdateCDWDemographics(pRequest As User.DemographicsRequest, Output pResponse As User.DemographicsResponse) As %Status
> {
>         set tSC=$$$OK,$ztrap="Error"
>         // Set Auto Commit to True
>         set tSC=..Adapter.SetAutoCommit(1)
>         set pResponse=##class(User.DemographicsResponse).%New()
>         if pResponse=$$$NULLOREF $$$LOGERROR("CDW Demographics: Unable to Create Response Message") goto End

>         set tQuery="{ call PACK_LOAD_STAGE.PROC_INS_DEMO(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?­,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }"
>         set p(1)=pRequest.LabNumber,p(2)=pRequest.PatientName,p(3)=pRequest.PatientAddr­ess1,p(4)=pRequest.PatientAddress2,p(5)=pRequest.PatientAddress3
>         set p(6)=pRequest.PatientAddress4,p(7)=pRequest.PatientPostCode,p(8)=pRequest.P­atientTelephoneNumber,p(9)=pRequest.AuthorizationNumber
>         set p(10)=pRequest.HospitalId,p(11)=pRequest.NationalId,p(12)=pRequest.National­ity,p(13)=pRequest.SpecimenReferenceNumber,p(14)=pRequest.BirthDate
>         set p(15)=+pRequest.AgeYears, p(16)=pRequest.AgeMonths, p(17)=pRequest.AgeDays
>         set p(18)=pRequest.AgeStatus,p(19)=pRequest.Sex,p(20)=pRequest.Race,p(21)=pRequ­est.Location,p(22)=pRequest.Ward,p(23)=pRequest.HealthCareRegion,p(24)=pReq­uest.OracleRegion,p(25)=pRequest.SpecimenTypeCode
>         set p(26)=pRequest.ClinicalDiagnosisCode,p(27)=pRequest.ICD10Code,p(28)=pReques­t.Infectious,p(29)=pRequest.Therapy,p(30)=pRequest.Priority
>         set p(31)=pRequest.PhlebotomistCode,p(32)=pRequest.Note,p(33)=pRequest.TakenDat­e,p(34)=pRequest.RegisteredDate,p(35)=pRequest.RegisteredBy
>         set p(36)=pRequest.ModifiedDateTime,p(37)=pRequest.ModifiedBy,p(38)=pRequest.Fi­rstPrintedDate,p(39)=pRequest.ReferringDrCode,p(40)=pRequest.ReferringDrNam­e
>         set p(41)=pRequest.BillableAccountNumber,p(42)=pRequest.MedialAid,p(43)=pReques­t.MedicalAidNumber,p(44)=pRequest.FinancialClass
>         set p(45)=pRequest.InOutPatient,p(46)=pRequest.AdmissionDate,p(47)=pRequest.Vis­itNumber,p(48)=pRequest.RepositoryRegdataDate

>         // Now call the Adapter
>         set p=48

>         set tSC=..Adapter.ExecuteProcedureParmArray(.pResultSnapshots,.pOutParam,tQuery­,"*",.p)

> > For more options, visit this group athttp://groups.google.com/group/Ensemble-in-Healthcare?hl=en-Hide quoted text -