Re: Execute DB2 stored procedue
agh yes... that makes sense.
Thanks Matt!
On Tue, Feb 28, 2023 at 11:23 AM Matt Burgess wrote:
> Philip,
>
> Those are OUT parameters so ExecuteSQL/PutSQL doesn't know how to get
> the values out after calling the procedure. We'd likely want a
> separate processor like ExecuteStoredProcedure and would have to
> figure out how to handle OUT parameters, maybe adding those fields to
> the outgoing records or something.
>
> Regards,
> Matt
>
> On Tue, Feb 28, 2023 at 11:16 AM Phillip Lord
> wrote:
> >
> > Thanks for replies...
> >
> > I'm trying putSQL to call the following stored-procedure...
> >
> > CREATE OR REPLACE PROCEDURE SMV.RUN_ALL_PS
> > (IN IN_RESET CHAR(1), -> This will always
> be 'N" when called from nifi
> >OUT OUT_SQLSTATE CHAR(5).
> >OUT OUT_RETURN_CODEINTEGER,
> >OUT OUT_ERROR_TEXTVARCHAR(1000),
> >OUT OUT_SQL_STMT VARCHAR(3)
> > )
> >
> >
> > so I'm trying this in putSQL
> >
> > CALL MYPROCEDURE.PROC1('N', ?,?,?,?)
> >
> > and I need to supply sql arg attributes... like...
> >
> > sql.args.1.type = 1
> > sql.args.1.value = not sure what to put here
> > sql.args.2.type = 4
> > sql.args.2.value = not sure what to put here
> > etc...
> >
> > Am I on the right track?
> >
> > Thanks
> >
> >
> >
> >
> >
> >
> > On Mon, Feb 27, 2023 at 8:50 PM Matt Burgess
> wrote:
> >>
> >> Stored procedures that take no output parameters and return ResultSets
> should work fine with ExecuteSQL, but for DBs that allow OUT and INOUT
> parameters, those won’t make it into the outgoing FlowFile (in either
> content or attributes).
> >>
> >> Regards,
> >> Matt
> >>
> >>
> >> On Feb 27, 2023, at 4:19 PM, Dmitry Stepanov
> wrote:
> >>
> >>
> >> We run our procedure using ExecuteSQL.
> >> Just make sure to use proper SQL syntax
> >>
> >> On February 27, 2023 2:09:19 p.m. Phillip Lord
> wrote:
> >>>
> >>> Hello,
> >>>
> >>> Does anyone have any experience executing a DB2 stored procedure?
> Potentially using PutSQL? I don't think it can be done using ExecuteSQL,
> and I can likely use an executeStreamCommand to accomplish this. But
> trying not to reinvent the wheel if I can just do it using a simple Nifi
> processor
> >>>
> >>> Thanks
> >>> Phil
> >>
> >>
>
Re: Execute DB2 stored procedue
Philip,
Those are OUT parameters so ExecuteSQL/PutSQL doesn't know how to get
the values out after calling the procedure. We'd likely want a
separate processor like ExecuteStoredProcedure and would have to
figure out how to handle OUT parameters, maybe adding those fields to
the outgoing records or something.
Regards,
Matt
On Tue, Feb 28, 2023 at 11:16 AM Phillip Lord wrote:
>
> Thanks for replies...
>
> I'm trying putSQL to call the following stored-procedure...
>
> CREATE OR REPLACE PROCEDURE SMV.RUN_ALL_PS
> (IN IN_RESET CHAR(1), -> This will always be
> 'N" when called from nifi
>OUT OUT_SQLSTATE CHAR(5).
>OUT OUT_RETURN_CODEINTEGER,
>OUT OUT_ERROR_TEXTVARCHAR(1000),
>OUT OUT_SQL_STMT VARCHAR(3)
> )
>
>
> so I'm trying this in putSQL
>
> CALL MYPROCEDURE.PROC1('N', ?,?,?,?)
>
> and I need to supply sql arg attributes... like...
>
> sql.args.1.type = 1
> sql.args.1.value = not sure what to put here
> sql.args.2.type = 4
> sql.args.2.value = not sure what to put here
> etc...
>
> Am I on the right track?
>
> Thanks
>
>
>
>
>
>
> On Mon, Feb 27, 2023 at 8:50 PM Matt Burgess wrote:
>>
>> Stored procedures that take no output parameters and return ResultSets
>> should work fine with ExecuteSQL, but for DBs that allow OUT and INOUT
>> parameters, those won’t make it into the outgoing FlowFile (in either
>> content or attributes).
>>
>> Regards,
>> Matt
>>
>>
>> On Feb 27, 2023, at 4:19 PM, Dmitry Stepanov wrote:
>>
>>
>> We run our procedure using ExecuteSQL.
>> Just make sure to use proper SQL syntax
>>
>> On February 27, 2023 2:09:19 p.m. Phillip Lord
>> wrote:
>>>
>>> Hello,
>>>
>>> Does anyone have any experience executing a DB2 stored procedure?
>>> Potentially using PutSQL? I don't think it can be done using ExecuteSQL,
>>> and I can likely use an executeStreamCommand to accomplish this. But
>>> trying not to reinvent the wheel if I can just do it using a simple Nifi
>>> processor
>>>
>>> Thanks
>>> Phil
>>
>>
Re: Execute DB2 stored procedue
Thanks for replies...
I'm trying putSQL to call the following stored-procedure...
CREATE OR REPLACE PROCEDURE SMV.RUN_ALL_PS
(IN IN_RESET CHAR(1), -> This will always be
'N" when called from nifi
OUT OUT_SQLSTATE CHAR(5).
OUT OUT_RETURN_CODEINTEGER,
OUT OUT_ERROR_TEXTVARCHAR(1000),
OUT OUT_SQL_STMT VARCHAR(3)
)
so I'm trying this in putSQL
CALL MYPROCEDURE.PROC1('N', ?,?,?,?)
and I need to supply sql arg attributes... like...
sql.args.1.type = 1
sql.args.1.value = not sure what to put here
sql.args.2.type = 4
sql.args.2.value = not sure what to put here
etc...
Am I on the right track?
Thanks
On Mon, Feb 27, 2023 at 8:50 PM Matt Burgess wrote:
> Stored procedures that take no output parameters and return ResultSets
> should work fine with ExecuteSQL, but for DBs that allow OUT and INOUT
> parameters, those won’t make it into the outgoing FlowFile (in either
> content or attributes).
>
> Regards,
> Matt
>
>
> On Feb 27, 2023, at 4:19 PM, Dmitry Stepanov wrote:
>
>
> We run our procedure using ExecuteSQL.
> Just make sure to use proper SQL syntax
>
> On February 27, 2023 2:09:19 p.m. Phillip Lord
> wrote:
>
>> Hello,
>>
>> Does anyone have any experience executing a DB2 stored procedure?
>> Potentially using PutSQL? I don't think it can be done using ExecuteSQL,
>> and I can likely use an executeStreamCommand to accomplish this. But
>> trying not to reinvent the wheel if I can just do it using a simple Nifi
>> processor
>>
>> Thanks
>> Phil
>>
>
>
Re: Execute DB2 stored procedue
Stored procedures that take no output parameters and return ResultSets should work fine with ExecuteSQL, but for DBs that allow OUT and INOUT parameters, those won’t make it into the outgoing FlowFile (in either content or attributes). Regards, Matt > On Feb 27, 2023, at 4:19 PM, Dmitry Stepanov wrote: > > > We run our procedure using ExecuteSQL. > Just make sure to use proper SQL syntax > >> On February 27, 2023 2:09:19 p.m. Phillip Lord >> wrote: >> >> Hello, >> >> Does anyone have any experience executing a DB2 stored procedure? >> Potentially using PutSQL? I don't think it can be done using ExecuteSQL, and >> I can likely use an executeStreamCommand to accomplish this. But trying not >> to reinvent the wheel if I can just do it using a simple Nifi processor >> >> Thanks >> Phil > >
Re: Execute DB2 stored procedue
We run our procedure using ExecuteSQL. Just make sure to use proper SQL syntax On February 27, 2023 2:09:19 p.m. Phillip Lord wrote: Hello, Does anyone have any experience executing a DB2 stored procedure? Potentially using PutSQL? I don't think it can be done using ExecuteSQL, and I can likely use an executeStreamCommand to accomplish this. But trying not to reinvent the wheel if I can just do it using a simple Nifi processor Thanks Phil
