Stored Procedure with variable parameters

[Brett Knights <br...@knightsofthenet.com>]

Hello,

I am using ibatis as my persistence layer in an application that I'm 
rewriting. I have some older code that uses JDBC directly and I can't 
see how to integrate it with iBatis in any useful way.


The old code calls a stored procedure. After contact information is set 
the number of arguments that might be set is arbitrary up to 100 extra 
arguments. Is there any way to do this with a SQLMap? I suppose I can 
just move the jdbc code inside my DAO object and then flush the caches 
manually.

TIA for any advice.


         CallableStatement pstmt = SQLCallUtil.prepareCallable(con, 
"tewouPlaceUpdate", true, 117); // build the "{ ? =  call   
tewouPlaceUpdate ? ? ? ? ... ?}" string with 117 parameter placeholders
         pstmt.clearParameters();
         pstmt.registerOutParameter(1, java.sql.Types.INTEGER);
         pstmt.registerOutParameter(2, java.sql.Types.INTEGER);
         pstmt.setInt(3, iOwnerId);
         pstmt.registerOutParameter(4, java.sql.Types.INTEGER);
         pstmt.setInt(4, iContactId);
         pstmt.setString(5, contactFirstName);
         pstmt.setString(6, contactLastName);
         pstmt.setString(7, contactTitle);
         pstmt.setString(8, contactAddr1);
         pstmt.setString(9, contactAddr2);
         pstmt.setString(10, contactAddr3);
         pstmt.setString(11, contactCity);
         pstmt.setString(12, contactCtryCode);
         pstmt.setString(13, contactRegn);
         pstmt.setString(14, contactPC);
         pstmt.setString(15, contactPhone);
         pstmt.setString(16, contactFax);
         pstmt.setString(17, contactEmail);
         pstmt.setInt(18, isPlaceable ? 1 : 0);

         int k = 18;
         Iterator packageIds = updateIds.iterator();
         while(packageIds.hasNext())pstmt.setString(++k, (String) 
packageIds.next());

         for(;k < 118; ) pstmt.setNull(++k, java.sql.Types.VARCHAR); // 
null the remaining params
        pstmt.execute();

Re: Stored Procedure with variable parameters

[Brandon Goodin <br...@gmail.com>]

Take a look at dynamic SQL. You may need to do something like use the
<iterate> tag to "iterate" over a List of Strings.

Brandon

On 2/6/07, Brett Knights <br...@knightsofthenet.com> wrote:
>
> Hello,
>
> I am using ibatis as my persistence layer in an application that I'm
> rewriting. I have some older code that uses JDBC directly and I can't
> see how to integrate it with iBatis in any useful way.
>
>
> The old code calls a stored procedure. After contact information is set
> the number of arguments that might be set is arbitrary up to 100 extra
> arguments. Is there any way to do this with a SQLMap? I suppose I can
> just move the jdbc code inside my DAO object and then flush the caches
> manually.
>
> TIA for any advice.
>
>
>          CallableStatement pstmt = SQLCallUtil.prepareCallable(con,
> "tewouPlaceUpdate", true, 117); // build the "{ ? =  call
> tewouPlaceUpdate ? ? ? ? ... ?}" string with 117 parameter placeholders
>          pstmt.clearParameters();
>          pstmt.registerOutParameter(1, java.sql.Types.INTEGER);
>          pstmt.registerOutParameter(2, java.sql.Types.INTEGER);
>          pstmt.setInt(3, iOwnerId);
>          pstmt.registerOutParameter(4, java.sql.Types.INTEGER);
>          pstmt.setInt(4, iContactId);
>          pstmt.setString(5, contactFirstName);
>          pstmt.setString(6, contactLastName);
>          pstmt.setString(7, contactTitle);
>          pstmt.setString(8, contactAddr1);
>          pstmt.setString(9, contactAddr2);
>          pstmt.setString(10, contactAddr3);
>          pstmt.setString(11, contactCity);
>          pstmt.setString(12, contactCtryCode);
>          pstmt.setString(13, contactRegn);
>          pstmt.setString(14, contactPC);
>          pstmt.setString(15, contactPhone);
>          pstmt.setString(16, contactFax);
>          pstmt.setString(17, contactEmail);
>          pstmt.setInt(18, isPlaceable ? 1 : 0);
>
>          int k = 18;
>          Iterator packageIds = updateIds.iterator();
>          while(packageIds.hasNext())pstmt.setString(++k, (String)
> packageIds.next());
>
>          for(;k < 118; ) pstmt.setNull(++k, java.sql.Types.VARCHAR); //
> null the remaining params
>         pstmt.execute();
>
>