Call a stored procedure?

Hi,

Can anyone give some more information about calling a stored procedure 
with a jdbc connector?

I have looked at the information in the manual, but I don't understand 
how to use it.

Normally if it is an insert with the connector set to addonly or update 
mode, you do all the "mapping and coding" in the output map and it 
writes to the table set in the config tab. But now that I don't want to 
write to a table, what mode for the connector should I use? And where 
should I put the code from the example in the manual?

Regards

Hi Peter,

The Connector's getConnector() method gives you access to the underlying
Connector Interface (in our case, ibmdi.JDBC) which offers a number of
feature above and beyond what a Connector normally uses.

Let's look at an example with a JDBC Connector called DBconnector:

var dbconn = DBconnector.getConnector().getConnection();

Here we grab the JDBC Connection object, which is an instance of a
java.sql.Connection.

Here's a code example illustrating how you can invoke a stored procedure on that

database using this connection object.


// Stored procedure call
command = "{call DatabaseName.dbo.spProcedureName(?,?)}";

try {
     cstmt = dbconn.prepareCall(command);

     // Assign IN parameters (use positional placement)
     cstmt.setString(1, "firstParam");
     cstmt.setString(2, "secondParam");

     cstmt.execute();

     cstmt.close();
     // TDI will close the connection when the AL stops and the Connector
     // is closed.

} catch(e) { // catch and log errors/exceptions
     task.logmsg(e);
}

Hope this helps,

-Eddie

Peter Berggren wrote:
> Hi,
> 
> Can anyone give some more information about calling a stored procedure 
> with a jdbc connector?
> 
> I have looked at the information in the manual, but I don't understand 
> how to use it.
> 
> Normally if it is an insert with the connector set to addonly or update 
> mode, you do all the "mapping and coding" in the output map and
it 
> writes to the table set in the config tab. But now that I don't want to 
> write to a table, what mode for the connector should I use? And where 
> should I put the code from the example in the manual?
> 
> Regards
> 

Thanks Eddie,

What I was wondering was if I should put your code into a hook in the 
JDBC connector or if I should use a script connector after the JDBC 
connector?

/ Peter

Eddie Hartman skrev:
> Hi Peter,
> 
> The Connector's getConnector() method gives you access to the underlying
> Connector Interface (in our case, ibmdi.JDBC) which offers a number of
> feature above and beyond what a Connector normally uses.
> 
> Let's look at an example with a JDBC Connector called DBconnector:
> 
> var dbconn = DBconnector.getConnector().getConnection();
> 
> Here we grab the JDBC Connection object, which is an instance of a
> java.sql.Connection.
> 
> Here's a code example illustrating how you can invoke a stored procedure 
> on that database using this connection object.
> 
> 
> // Stored procedure call
> command = "{call DatabaseName.dbo.spProcedureName(?,?)}";
> 
> try {
>     cstmt = dbconn.prepareCall(command);
> 
>     // Assign IN parameters (use positional placement)
>     cstmt.setString(1, "firstParam");
>     cstmt.setString(2, "secondParam");
> 
>     cstmt.execute();
> 
>     cstmt.close();
>     // TDI will close the connection when the AL stops and the Connector
>     // is closed.
> 
> } catch(e) { // catch and log errors/exceptions
>     task.logmsg(e);
> }
> 
> Hope this helps,
> 
> -Eddie
> 
> Peter Berggren wrote:
>> Hi,
>>
>> Can anyone give some more information about calling a stored procedure

>> with a jdbc connector?
>>
>> I have looked at the information in the manual, but I don't understand

>> how to use it.
>>
>> Normally if it is an insert with the connector set to addonly or 
>> update mode, you do all the "mapping and coding" in the
output map and 
>> it writes to the table set in the config tab. But now that I don't 
>> want to write to a table, what mode for the connector should I use? 
>> And where should I put the code from the example in the manual?
>>
>> Regards
>>

This is solved.

I used a passive JDBC connector with an empty output map and put the 
code into a hook, works fine.

/ Peter

Peter Berggren skrev:
> Thanks Eddie,
> 
> What I was wondering was if I should put your code into a hook in the 
> JDBC connector or if I should use a script connector after the JDBC 
> connector?
> 
> / Peter
> 
> Eddie Hartman skrev:
>> Hi Peter,
>>
>> The Connector's getConnector() method gives you access to the
underlying
>> Connector Interface (in our case, ibmdi.JDBC) which offers a number of
>> feature above and beyond what a Connector normally uses.
>>
>> Let's look at an example with a JDBC Connector called DBconnector:
>>
>> var dbconn = DBconnector.getConnector().getConnection();
>>
>> Here we grab the JDBC Connection object, which is an instance of a
>> java.sql.Connection.
>>
>> Here's a code example illustrating how you can invoke a stored 
>> procedure on that database using this connection object.
>>
>>
>> // Stored procedure call
>> command = "{call DatabaseName.dbo.spProcedureName(?,?)}";
>>
>> try {
>>     cstmt = dbconn.prepareCall(command);
>>
>>     // Assign IN parameters (use positional placement)
>>     cstmt.setString(1, "firstParam");
>>     cstmt.setString(2, "secondParam");
>>
>>     cstmt.execute();
>>
>>     cstmt.close();
>>     // TDI will close the connection when the AL stops and the
Connector
>>     // is closed.
>>
>> } catch(e) { // catch and log errors/exceptions
>>     task.logmsg(e);
>> }
>>
>> Hope this helps,
>>
>> -Eddie
>>
>> Peter Berggren wrote:
>>> Hi,
>>>
>>> Can anyone give some more information about calling a stored 
>>> procedure with a jdbc connector?
>>>
>>> I have looked at the information in the manual, but I don't 
>>> understand how to use it.
>>>
>>> Normally if it is an insert with the connector set to addonly or 
>>> update mode, you do all the "mapping and coding" in the
output map 
>>> and it writes to the table set in the config tab. But now that I 
>>> don't want to write to a table, what mode for the connector should
I 
>>> use? And where should I put the code from the example in the
manual?
>>>
>>> Regards
>>>

Scripting this logic into the Hooks of the Connector
means that your code does not need to reference named
components. Instead you get to use the thisConnector
variable which always point to the component you are
currently scripting in.

If you put this in a passive Connector, I assume that
your code was in Prolog or Epilog Hooks(?) Since Passive
State means that the DataFlow logic is not used when the
AL cycles.

-Eddie

Peter Berggren wrote:
> This is solved.
> 
> I used a passive JDBC connector with an empty output map and put the 
> code into a hook, works fine.
> 
> / Peter
> 
> Peter Berggren skrev:
>> Thanks Eddie,
>>
>> What I was wondering was if I should put your code into a hook in the 
>> JDBC connector or if I should use a script connector after the JDBC 
>> connector?
>>
>> / Peter
>>
>> Eddie Hartman skrev:
>>> Hi Peter,
>>>
>>> The Connector's getConnector() method gives you access to the
underlying
>>> Connector Interface (in our case, ibmdi.JDBC) which offers a number
of
>>> feature above and beyond what a Connector normally uses.
>>>
>>> Let's look at an example with a JDBC Connector called DBconnector:
>>>
>>> var dbconn = DBconnector.getConnector().getConnection();
>>>
>>> Here we grab the JDBC Connection object, which is an instance of a
>>> java.sql.Connection.
>>>
>>> Here's a code example illustrating how you can invoke a stored 
>>> procedure on that database using this connection object.
>>>
>>>
>>> // Stored procedure call
>>> command = "{call
DatabaseName.dbo.spProcedureName(?,?)}";
>>>
>>> try {
>>>     cstmt = dbconn.prepareCall(command);
>>>
>>>     // Assign IN parameters (use positional placement)
>>>     cstmt.setString(1, "firstParam");
>>>     cstmt.setString(2, "secondParam");
>>>
>>>     cstmt.execute();
>>>
>>>     cstmt.close();
>>>     // TDI will close the connection when the AL stops and the
Connector
>>>     // is closed.
>>>
>>> } catch(e) { // catch and log errors/exceptions
>>>     task.logmsg(e);
>>> }
>>>
>>> Hope this helps,
>>>
>>> -Eddie
>>>
>>> Peter Berggren wrote:
>>>> Hi,
>>>>
>>>> Can anyone give some more information about calling a stored 
>>>> procedure with a jdbc connector?
>>>>
>>>> I have looked at the information in the manual, but I don't 
>>>> understand how to use it.
>>>>
>>>> Normally if it is an insert with the connector set to addonly
or 
>>>> update mode, you do all the "mapping and coding" in
the output map 
>>>> and it writes to the table set in the config tab. But now that
I 
>>>> don't want to write to a table, what mode for the connector
should I 
>>>> use? And where should I put the code from the example in the
manual?
>>>>
>>>> Regards
>>>>