springframework-developer

[Springframework-developer] Added BLOB/CLOB support to SqlUpdate and StoredProcedure

[Thomas R. <tho...@tr...>]

OK, I'm done adding support  for BLOB/CLOB to SqlUpdate and 
StoredProcedure.  I have commited these changes and you are welcome to 
take them for a spin.  If  you notice any problems let me know.  I'll 
add some mocked unit tests to our testsuite the next couple of days.  
All tests I have now are live connection ones.

Here is a short example of usage.

I'm reusing the LobHandler that Juergen added recently.  It works very 
well and it supports the following input types for LOBs:

BLOB - byte array or java.io.InputStream
CLOB - String, java.io.InputStream or java.io.Reader

If you are running in an appserver with a connection pool that wraps 
your connection, depending on which LobHandler you use, you might have 
to set a NativeJdbcExtractor.  For Oracle 9i and Weblogic 8.1 I used the 
following setup:

      LobHandler lobHandler = new OracleLobHandler();
      NativeJdbcExtractor jdbcExtractor = new WebLogicNativeJdbcExtractor();
      lobHandler.setNativeJdbcExtractor(jdbcExtractor);

This should of course be set via IoC for a real app.

Now you need to create your SqlUpdate and declare you parameters:

       String sql = "insert into docs (doc_id, content) values(?, ?)";
       SqlUpdate su = new SqlUpdate(ds, sql);
       su.declareParameter(new SqlParameter("id", Types.INTEGER));
       su.declareParameter(new SqlParameter("lob", Types.BLOB, 
lobHandler));
       su.compile();

The LOB parameter is declared with a new constructor that takes the 
LobHandler as the third argument.  If you don't specify a LobHandler we 
will automatically generate a DefaultLobHandler.

Next we need to create an array with all parameters and call the 
update() method to insert each row.  The LOB value must be passed in as 
an SqlLobValue object together with the length (unless you use a byte 
array or String in which case we can easily figure out the length).  
SqlLobValue is a new class that holds the actual LOB value plus the 
LobCreator that the framework internally populates during the update.

       Object[] inval = new Object[2];
       inval[0] = new Integer(newId);
       File in = new File("some.doc");
       InputStream is = new FileInputStream(in);
       inval[1] = new SqlLobValue(is, (int) in.length());
       int count = su.update(inval);
       is.close();

That is it.  It works the same way for CLOBs and it also works for 
parameters passed in to a StoredProcedure.

While making this change I combined the setXxxxx logic for 
PreparedStatement and CallableStatement to one static method that I 
added to JdbcUtils.  If you notice anything odd due to this refactoring, 
please let me know.

Thomas

Re: [Springframework-developer] Added BLOB/CLOB support to SqlUpdate and StoredProcedure

[Tom T. <tom...@pr...>]

Hello Thomas,

We have several stored procedures that take BLOBs as parameters, and I've
just reworked them to use the new built-in LOB support, which works
perfectly, thanks!

However, I'm not entirely happy about the fact that the LobHandler is
being passed as an argument to the SqlParameter constructor.

Our classes extend the StoredProcedure class, and I've declared a
"lobHandler" property to be set in the application context, as the same
LobHandler is used by multiple classes, and it also needs a reference to
the NativeJdbcExtractor. Unfortunately, this implicates that the
declaration of the sql parameters, which we've traditionally been doing
in the constructor, now has to be moved to the afterPropertiesSet()
method, as the LobHandler is not yet available in the constructor.
Moreover, care must be taken that the overridden afterPropertiesSet()
first declares the sql parameters and then calls
super.afterPropertiesSet(), which calls compile(). I don't like this.
Of course, the LobHandler could be passed as a constructor argument, but
I don't like that either ;-)

All RdbmsOperations require the sql parameters to be declared in code,
while the sql string can be set from the application context, which is
what we're doing. This makes sense, as you could look at the sql
parameters as defining a "contract", while the sql string is just an
implementation of that contract. The class only cares about the
"contract", any implementation will do. The sql parameters are thus an
essential part of the class, and it's natural to declare them in the
constructor. If the declaration has to be moved to the
afterPropertiesSet() method, I somehow feel the advantage of declaring
the parameters in code is lost...

As LobHandler is currently the only collaborator possibly needed by a
SqlParameter, I've been wondering whether it's appropriate for it to be
part of the SqlParameter definition. It's not part of the "contract", so,
in fact, does not really belong there. Maybe we should make it part of
the SqlLobValue, which would then encapsulate the LOB value itself, and
the logic for dealing with it. The SqlLobValue is normally constructed in
the execute() method, when the LobHandler is already available. I've had
a look at the LOB support implementation, and it appears this would be
trivial to change!

The same idea can probably be applied to a future CustomSqlTypeHandler
for special parameter types. Instead of passing a CustomSqlTypeHandler to
the constructor of SqlParameter, a CustomSqlTypeValue object would be set
as parameter value, containing both the value and its
CustomSqlTypeHandler, which could be a collaborator.

What do you think?

Kind regards,
Tom.

On Fri, 04 Jun 2004 13:57:14 -0400, "Thomas Risberg"
<tho...@tr...> said:
> OK, I'm done adding support  for BLOB/CLOB to SqlUpdate and 
> StoredProcedure.  I have commited these changes and you are welcome to 
> take them for a spin.  If  you notice any problems let me know.  I'll 
> add some mocked unit tests to our testsuite the next couple of days.  
> All tests I have now are live connection ones.
> 
> Here is a short example of usage.
> 
> I'm reusing the LobHandler that Juergen added recently.  It works very 
> well and it supports the following input types for LOBs:
> 
> BLOB - byte array or java.io.InputStream
> CLOB - String, java.io.InputStream or java.io.Reader
> 
> If you are running in an appserver with a connection pool that wraps 
> your connection, depending on which LobHandler you use, you might have 
> to set a NativeJdbcExtractor.  For Oracle 9i and Weblogic 8.1 I used the 
> following setup:
> 
>       LobHandler lobHandler = new OracleLobHandler();
>       NativeJdbcExtractor jdbcExtractor = new
>       WebLogicNativeJdbcExtractor();
>       lobHandler.setNativeJdbcExtractor(jdbcExtractor);
> 
> This should of course be set via IoC for a real app.
> 
> Now you need to create your SqlUpdate and declare you parameters:
> 
>        String sql = "insert into docs (doc_id, content) values(?, ?)";
>        SqlUpdate su = new SqlUpdate(ds, sql);
>        su.declareParameter(new SqlParameter("id", Types.INTEGER));
>        su.declareParameter(new SqlParameter("lob", Types.BLOB, 
> lobHandler));
>        su.compile();
> 
> The LOB parameter is declared with a new constructor that takes the 
> LobHandler as the third argument.  If you don't specify a LobHandler we 
> will automatically generate a DefaultLobHandler.
> 
> Next we need to create an array with all parameters and call the 
> update() method to insert each row.  The LOB value must be passed in as 
> an SqlLobValue object together with the length (unless you use a byte 
> array or String in which case we can easily figure out the length).  
> SqlLobValue is a new class that holds the actual LOB value plus the 
> LobCreator that the framework internally populates during the update.
> 
>        Object[] inval = new Object[2];
>        inval[0] = new Integer(newId);
>        File in = new File("some.doc");
>        InputStream is = new FileInputStream(in);
>        inval[1] = new SqlLobValue(is, (int) in.length());
>        int count = su.update(inval);
>        is.close();
> 
> That is it.  It works the same way for CLOBs and it also works for 
> parameters passed in to a StoredProcedure.
> 
> While making this change I combined the setXxxxx logic for 
> PreparedStatement and CallableStatement to one static method that I 
> added to JdbcUtils.  If you notice anything odd due to this refactoring, 
> please let me know.
> 
> Thomas
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by the new InstallShield X.
> From Windows to Linux, servers to mobile, InstallShield X is the one
> installation-authoring solution that does it all. Learn more and
> evaluate today! http://www.installshield.com/Dev2Dev/0504
> _______________________________________________
> Springframework-developer mailing list
> Spr...@li...
> https://lists.sourceforge.net/lists/listinfo/springframework-developer

Re: [Springframework-developer] Added BLOB/CLOB support to SqlUpdate and StoredProcedure

[Thomas R. <tho...@tr...>]

Tom,

I would have to say that I agree with all your points.  I'll look into 
changing this as soon as I can.

Thanks for ponting this out.  As you said, there really is no need to 
have the LobHandler available when the parameters are declared.  As long 
as we have it when we process the SqlLobValue, we will be fine.

Thomas  

Tom Turelinckx wrote:

>Hello Thomas,
>
>We have several stored procedures that take BLOBs as parameters, and I've
>just reworked them to use the new built-in LOB support, which works
>perfectly, thanks!
>
>However, I'm not entirely happy about the fact that the LobHandler is
>being passed as an argument to the SqlParameter constructor.
>
>Our classes extend the StoredProcedure class, and I've declared a
>"lobHandler" property to be set in the application context, as the same
>LobHandler is used by multiple classes, and it also needs a reference to
>the NativeJdbcExtractor. Unfortunately, this implicates that the
>declaration of the sql parameters, which we've traditionally been doing
>in the constructor, now has to be moved to the afterPropertiesSet()
>method, as the LobHandler is not yet available in the constructor.
>Moreover, care must be taken that the overridden afterPropertiesSet()
>first declares the sql parameters and then calls
>super.afterPropertiesSet(), which calls compile(). I don't like this.
>Of course, the LobHandler could be passed as a constructor argument, but
>I don't like that either ;-)
>
>All RdbmsOperations require the sql parameters to be declared in code,
>while the sql string can be set from the application context, which is
>what we're doing. This makes sense, as you could look at the sql
>parameters as defining a "contract", while the sql string is just an
>implementation of that contract. The class only cares about the
>"contract", any implementation will do. The sql parameters are thus an
>essential part of the class, and it's natural to declare them in the
>constructor. If the declaration has to be moved to the
>afterPropertiesSet() method, I somehow feel the advantage of declaring
>the parameters in code is lost...
>
>As LobHandler is currently the only collaborator possibly needed by a
>SqlParameter, I've been wondering whether it's appropriate for it to be
>part of the SqlParameter definition. It's not part of the "contract", so,
>in fact, does not really belong there. Maybe we should make it part of
>the SqlLobValue, which would then encapsulate the LOB value itself, and
>the logic for dealing with it. The SqlLobValue is normally constructed in
>the execute() method, when the LobHandler is already available. I've had
>a look at the LOB support implementation, and it appears this would be
>trivial to change!
>
>The same idea can probably be applied to a future CustomSqlTypeHandler
>for special parameter types. Instead of passing a CustomSqlTypeHandler to
>the constructor of SqlParameter, a CustomSqlTypeValue object would be set
>as parameter value, containing both the value and its
>CustomSqlTypeHandler, which could be a collaborator.
>
>What do you think?
>
>Kind regards,
>Tom.
>
>On Fri, 04 Jun 2004 13:57:14 -0400, "Thomas Risberg"
><tho...@tr...> said:
>  
>
>>OK, I'm done adding support  for BLOB/CLOB to SqlUpdate and 
>>StoredProcedure.  I have commited these changes and you are welcome to 
>>take them for a spin.  If  you notice any problems let me know.  I'll 
>>add some mocked unit tests to our testsuite the next couple of days.  
>>All tests I have now are live connection ones.
>>
>>Here is a short example of usage.
>>
>>I'm reusing the LobHandler that Juergen added recently.  It works very 
>>well and it supports the following input types for LOBs:
>>
>>BLOB - byte array or java.io.InputStream
>>CLOB - String, java.io.InputStream or java.io.Reader
>>
>>If you are running in an appserver with a connection pool that wraps 
>>your connection, depending on which LobHandler you use, you might have 
>>to set a NativeJdbcExtractor.  For Oracle 9i and Weblogic 8.1 I used the 
>>following setup:
>>
>>      LobHandler lobHandler = new OracleLobHandler();
>>      NativeJdbcExtractor jdbcExtractor = new
>>      WebLogicNativeJdbcExtractor();
>>      lobHandler.setNativeJdbcExtractor(jdbcExtractor);
>>
>>This should of course be set via IoC for a real app.
>>
>>Now you need to create your SqlUpdate and declare you parameters:
>>
>>       String sql = "insert into docs (doc_id, content) values(?, ?)";
>>       SqlUpdate su = new SqlUpdate(ds, sql);
>>       su.declareParameter(new SqlParameter("id", Types.INTEGER));
>>       su.declareParameter(new SqlParameter("lob", Types.BLOB, 
>>lobHandler));
>>       su.compile();
>>
>>The LOB parameter is declared with a new constructor that takes the 
>>LobHandler as the third argument.  If you don't specify a LobHandler we 
>>will automatically generate a DefaultLobHandler.
>>
>>Next we need to create an array with all parameters and call the 
>>update() method to insert each row.  The LOB value must be passed in as 
>>an SqlLobValue object together with the length (unless you use a byte 
>>array or String in which case we can easily figure out the length).  
>>SqlLobValue is a new class that holds the actual LOB value plus the 
>>LobCreator that the framework internally populates during the update.
>>
>>       Object[] inval = new Object[2];
>>       inval[0] = new Integer(newId);
>>       File in = new File("some.doc");
>>       InputStream is = new FileInputStream(in);
>>       inval[1] = new SqlLobValue(is, (int) in.length());
>>       int count = su.update(inval);
>>       is.close();
>>
>>That is it.  It works the same way for CLOBs and it also works for 
>>parameters passed in to a StoredProcedure.
>>
>>While making this change I combined the setXxxxx logic for 
>>PreparedStatement and CallableStatement to one static method that I 
>>added to JdbcUtils.  If you notice anything odd due to this refactoring, 
>>please let me know.
>>
>>Thomas
>>
>>
>>
>>-------------------------------------------------------
>>This SF.Net email is sponsored by the new InstallShield X.
>>From Windows to Linux, servers to mobile, InstallShield X is the one
>>installation-authoring solution that does it all. Learn more and
>>evaluate today! http://www.installshield.com/Dev2Dev/0504
>>_______________________________________________
>>Springframework-developer mailing list
>>Spr...@li...
>>https://lists.sourceforge.net/lists/listinfo/springframework-developer
>>    
>>
>
>
>-------------------------------------------------------
>This SF.Net email is sponsored by: GNOME Foundation
>Hackers Unite!  GUADEC: The world's #1 Open Source Desktop Event.
>GNOME Users and Developers European Conference, 28-30th June in Norway
>http://2004/guadec.org
>_______________________________________________
>Springframework-developer mailing list
>Spr...@li...
>https://lists.sourceforge.net/lists/listinfo/springframework-developer
>
>
>
>  
>

Re: [Springframework-developer] Added BLOB/CLOB support to SqlUpdate and StoredProcedure

[<tho...@tr...>]

I have made the changes to the SqlParameters and SqlLobValue - the LobHandler
reference has been moved from SqlParameters to SqlLobValue.

Here is an example of how you would use it:

   LobHandler lobHandler = new OracleLobHandler();
   NativeJdbcExtractor jdbcExtractor = new WebLogicNativeJdbcExtractor();
   lobHandler.setNativeJdbcExtractor(jdbcExtractor);

   String sql = "insert into docs (doc_id, content) values(?, ?)";
   SqlUpdate su = new SqlUpdate(ds, sql);
   su.declareParameter(new SqlParameter("id", Types.INTEGER));
   su.declareParameter(new SqlParameter("lob", Types.BLOB));
   su.compile();

   Object[] inval = new Object[2];
   inval[0] = new Integer(newId);
   File in = new File("some.doc");
   InputStream is = new FileInputStream(in);
   inval[1] = new SqlLobValue(is, (int) in.length(), lobHandler);
   int count = su.update(inval);
   is.close();

If you don't provide a LobHandler for the SqlLobValue, then a DefaultLobHandler
will automatically be created.

Let me know if there are some other issues.

Thomas



Quoting Thomas Risberg <tho...@tr...>:

> Tom,
>
> I would have to say that I agree with all your points.  I'll look into
> changing this as soon as I can.
>
> Thanks for ponting this out.  As you said, there really is no need to
> have the LobHandler available when the parameters are declared.  As long
> as we have it when we process the SqlLobValue, we will be fine.
>
> Thomas
>
> Tom Turelinckx wrote:
>
> >Hello Thomas,
> >
> >We have several stored procedures that take BLOBs as parameters, and I've
> >just reworked them to use the new built-in LOB support, which works
> >perfectly, thanks!
> >
> >However, I'm not entirely happy about the fact that the LobHandler is
> >being passed as an argument to the SqlParameter constructor.
> >
> >Our classes extend the StoredProcedure class, and I've declared a
> >"lobHandler" property to be set in the application context, as the same
> >LobHandler is used by multiple classes, and it also needs a reference to
> >the NativeJdbcExtractor. Unfortunately, this implicates that the
> >declaration of the sql parameters, which we've traditionally been doing
> >in the constructor, now has to be moved to the afterPropertiesSet()
> >method, as the LobHandler is not yet available in the constructor.
> >Moreover, care must be taken that the overridden afterPropertiesSet()
> >first declares the sql parameters and then calls
> >super.afterPropertiesSet(), which calls compile(). I don't like this.
> >Of course, the LobHandler could be passed as a constructor argument, but
> >I don't like that either ;-)
> >
> >All RdbmsOperations require the sql parameters to be declared in code,
> >while the sql string can be set from the application context, which is
> >what we're doing. This makes sense, as you could look at the sql
> >parameters as defining a "contract", while the sql string is just an
> >implementation of that contract. The class only cares about the
> >"contract", any implementation will do. The sql parameters are thus an
> >essential part of the class, and it's natural to declare them in the
> >constructor. If the declaration has to be moved to the
> >afterPropertiesSet() method, I somehow feel the advantage of declaring
> >the parameters in code is lost...
> >
> >As LobHandler is currently the only collaborator possibly needed by a
> >SqlParameter, I've been wondering whether it's appropriate for it to be
> >part of the SqlParameter definition. It's not part of the "contract", so,
> >in fact, does not really belong there. Maybe we should make it part of
> >the SqlLobValue, which would then encapsulate the LOB value itself, and
> >the logic for dealing with it. The SqlLobValue is normally constructed in
> >the execute() method, when the LobHandler is already available. I've had
> >a look at the LOB support implementation, and it appears this would be
> >trivial to change!
> >
> >The same idea can probably be applied to a future CustomSqlTypeHandler
> >for special parameter types. Instead of passing a CustomSqlTypeHandler to
> >the constructor of SqlParameter, a CustomSqlTypeValue object would be set
> >as parameter value, containing both the value and its
> >CustomSqlTypeHandler, which could be a collaborator.
> >
> >What do you think?
> >
> >Kind regards,
> >Tom.
> >
> >On Fri, 04 Jun 2004 13:57:14 -0400, "Thomas Risberg"
> ><tho...@tr...> said:
> >
> >
> >>OK, I'm done adding support  for BLOB/CLOB to SqlUpdate and
> >>StoredProcedure.  I have commited these changes and you are welcome to
> >>take them for a spin.  If  you notice any problems let me know.  I'll
> >>add some mocked unit tests to our testsuite the next couple of days.
> >>All tests I have now are live connection ones.
> >>
> >>Here is a short example of usage.
> >>
> >>I'm reusing the LobHandler that Juergen added recently.  It works very
> >>well and it supports the following input types for LOBs:
> >>
> >>BLOB - byte array or java.io.InputStream
> >>CLOB - String, java.io.InputStream or java.io.Reader
> >>
> >>If you are running in an appserver with a connection pool that wraps
> >>your connection, depending on which LobHandler you use, you might have
> >>to set a NativeJdbcExtractor.  For Oracle 9i and Weblogic 8.1 I used the
> >>following setup:
> >>
> >>      LobHandler lobHandler = new OracleLobHandler();
> >>      NativeJdbcExtractor jdbcExtractor = new
> >>      WebLogicNativeJdbcExtractor();
> >>      lobHandler.setNativeJdbcExtractor(jdbcExtractor);
> >>
> >>This should of course be set via IoC for a real app.
> >>
> >>Now you need to create your SqlUpdate and declare you parameters:
> >>
> >>       String sql = "insert into docs (doc_id, content) values(?, ?)";
> >>       SqlUpdate su = new SqlUpdate(ds, sql);
> >>       su.declareParameter(new SqlParameter("id", Types.INTEGER));
> >>       su.declareParameter(new SqlParameter("lob", Types.BLOB,
> >>lobHandler));
> >>       su.compile();
> >>
> >>The LOB parameter is declared with a new constructor that takes the
> >>LobHandler as the third argument.  If you don't specify a LobHandler we
> >>will automatically generate a DefaultLobHandler.
> >>
> >>Next we need to create an array with all parameters and call the
> >>update() method to insert each row.  The LOB value must be passed in as
> >>an SqlLobValue object together with the length (unless you use a byte
> >>array or String in which case we can easily figure out the length).
> >>SqlLobValue is a new class that holds the actual LOB value plus the
> >>LobCreator that the framework internally populates during the update.
> >>
> >>       Object[] inval = new Object[2];
> >>       inval[0] = new Integer(newId);
> >>       File in = new File("some.doc");
> >>       InputStream is = new FileInputStream(in);
> >>       inval[1] = new SqlLobValue(is, (int) in.length());
> >>       int count = su.update(inval);
> >>       is.close();
> >>
> >>That is it.  It works the same way for CLOBs and it also works for
> >>parameters passed in to a StoredProcedure.
> >>
> >>While making this change I combined the setXxxxx logic for
> >>PreparedStatement and CallableStatement to one static method that I
> >>added to JdbcUtils.  If you notice anything odd due to this refactoring,
> >>please let me know.
> >>
> >>Thomas
> >>
> >>
> >>
> >>-------------------------------------------------------
> >>This SF.Net email is sponsored by the new InstallShield X.
> >>From Windows to Linux, servers to mobile, InstallShield X is the one
> >>installation-authoring solution that does it all. Learn more and
> >>evaluate today! http://www.installshield.com/Dev2Dev/0504
> >>_______________________________________________
> >>Springframework-developer mailing list
> >>Spr...@li...
> >>https://lists.sourceforge.net/lists/listinfo/springframework-developer
> >>
> >>
> >
> >
> >-------------------------------------------------------
> >This SF.Net email is sponsored by: GNOME Foundation
> >Hackers Unite!  GUADEC: The world's #1 Open Source Desktop Event.
> >GNOME Users and Developers European Conference, 28-30th June in Norway
> >http://2004/guadec.org
> >_______________________________________________
> >Springframework-developer mailing list
> >Spr...@li...
> >https://lists.sourceforge.net/lists/listinfo/springframework-developer
> >
> >
> >
> >
> >
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: GNOME Foundation
> Hackers Unite!  GUADEC: The world's #1 Open Source Desktop Event.
> GNOME Users and Developers European Conference, 28-30th June in Norway
> http://2004/guadec.org
> _______________________________________________
> Springframework-developer mailing list
> Spr...@li...
> https://lists.sourceforge.net/lists/listinfo/springframework-developer
>

Re: [Springframework-developer] Added BLOB/CLOB support to SqlUpdate and StoredProcedure

[Tom T. <tom...@pr...>]

Thanks for changing this so fast, Thomas!

I've adapted our StoredProcedures accordingly, and it all works fine now.

Thanks!
Tom.

On Thu, 10 Jun 2004 16:16:21 -0400, tho...@tr... said:
> I have made the changes to the SqlParameters and SqlLobValue - the
> LobHandler
> reference has been moved from SqlParameters to SqlLobValue.
> 
> Here is an example of how you would use it:
> 
>    LobHandler lobHandler = new OracleLobHandler();
>    NativeJdbcExtractor jdbcExtractor = new WebLogicNativeJdbcExtractor();
>    lobHandler.setNativeJdbcExtractor(jdbcExtractor);
> 
>    String sql = "insert into docs (doc_id, content) values(?, ?)";
>    SqlUpdate su = new SqlUpdate(ds, sql);
>    su.declareParameter(new SqlParameter("id", Types.INTEGER));
>    su.declareParameter(new SqlParameter("lob", Types.BLOB));
>    su.compile();
> 
>    Object[] inval = new Object[2];
>    inval[0] = new Integer(newId);
>    File in = new File("some.doc");
>    InputStream is = new FileInputStream(in);
>    inval[1] = new SqlLobValue(is, (int) in.length(), lobHandler);
>    int count = su.update(inval);
>    is.close();
> 
> If you don't provide a LobHandler for the SqlLobValue, then a
> DefaultLobHandler
> will automatically be created.
> 
> Let me know if there are some other issues.
> 
> Thomas
> 
> 
> 
> Quoting Thomas Risberg <tho...@tr...>:
> 
> > Tom,
> >
> > I would have to say that I agree with all your points.  I'll look into
> > changing this as soon as I can.
> >
> > Thanks for ponting this out.  As you said, there really is no need to
> > have the LobHandler available when the parameters are declared.  As long
> > as we have it when we process the SqlLobValue, we will be fine.
> >
> > Thomas
> >
> > Tom Turelinckx wrote:
> >
> > >Hello Thomas,
> > >
> > >We have several stored procedures that take BLOBs as parameters, and I've
> > >just reworked them to use the new built-in LOB support, which works
> > >perfectly, thanks!
> > >
> > >However, I'm not entirely happy about the fact that the LobHandler is
> > >being passed as an argument to the SqlParameter constructor.
> > >
> > >Our classes extend the StoredProcedure class, and I've declared a
> > >"lobHandler" property to be set in the application context, as the same
> > >LobHandler is used by multiple classes, and it also needs a reference to
> > >the NativeJdbcExtractor. Unfortunately, this implicates that the
> > >declaration of the sql parameters, which we've traditionally been doing
> > >in the constructor, now has to be moved to the afterPropertiesSet()
> > >method, as the LobHandler is not yet available in the constructor.
> > >Moreover, care must be taken that the overridden afterPropertiesSet()
> > >first declares the sql parameters and then calls
> > >super.afterPropertiesSet(), which calls compile(). I don't like this.
> > >Of course, the LobHandler could be passed as a constructor argument, but
> > >I don't like that either ;-)
> > >
> > >All RdbmsOperations require the sql parameters to be declared in code,
> > >while the sql string can be set from the application context, which is
> > >what we're doing. This makes sense, as you could look at the sql
> > >parameters as defining a "contract", while the sql string is just an
> > >implementation of that contract. The class only cares about the
> > >"contract", any implementation will do. The sql parameters are thus an
> > >essential part of the class, and it's natural to declare them in the
> > >constructor. If the declaration has to be moved to the
> > >afterPropertiesSet() method, I somehow feel the advantage of declaring
> > >the parameters in code is lost...
> > >
> > >As LobHandler is currently the only collaborator possibly needed by a
> > >SqlParameter, I've been wondering whether it's appropriate for it to be
> > >part of the SqlParameter definition. It's not part of the "contract", so,
> > >in fact, does not really belong there. Maybe we should make it part of
> > >the SqlLobValue, which would then encapsulate the LOB value itself, and
> > >the logic for dealing with it. The SqlLobValue is normally constructed in
> > >the execute() method, when the LobHandler is already available. I've had
> > >a look at the LOB support implementation, and it appears this would be
> > >trivial to change!
> > >
> > >The same idea can probably be applied to a future CustomSqlTypeHandler
> > >for special parameter types. Instead of passing a CustomSqlTypeHandler to
> > >the constructor of SqlParameter, a CustomSqlTypeValue object would be set
> > >as parameter value, containing both the value and its
> > >CustomSqlTypeHandler, which could be a collaborator.
> > >
> > >What do you think?
> > >
> > >Kind regards,
> > >Tom.
> > >
> > >On Fri, 04 Jun 2004 13:57:14 -0400, "Thomas Risberg"
> > ><tho...@tr...> said:
> > >
> > >
> > >>OK, I'm done adding support  for BLOB/CLOB to SqlUpdate and
> > >>StoredProcedure.  I have commited these changes and you are welcome to
> > >>take them for a spin.  If  you notice any problems let me know.  I'll
> > >>add some mocked unit tests to our testsuite the next couple of days.
> > >>All tests I have now are live connection ones.
> > >>
> > >>Here is a short example of usage.
> > >>
> > >>I'm reusing the LobHandler that Juergen added recently.  It works very
> > >>well and it supports the following input types for LOBs:
> > >>
> > >>BLOB - byte array or java.io.InputStream
> > >>CLOB - String, java.io.InputStream or java.io.Reader
> > >>
> > >>If you are running in an appserver with a connection pool that wraps
> > >>your connection, depending on which LobHandler you use, you might have
> > >>to set a NativeJdbcExtractor.  For Oracle 9i and Weblogic 8.1 I used the
> > >>following setup:
> > >>
> > >>      LobHandler lobHandler = new OracleLobHandler();
> > >>      NativeJdbcExtractor jdbcExtractor = new
> > >>      WebLogicNativeJdbcExtractor();
> > >>      lobHandler.setNativeJdbcExtractor(jdbcExtractor);
> > >>
> > >>This should of course be set via IoC for a real app.
> > >>
> > >>Now you need to create your SqlUpdate and declare you parameters:
> > >>
> > >>       String sql = "insert into docs (doc_id, content) values(?, ?)";
> > >>       SqlUpdate su = new SqlUpdate(ds, sql);
> > >>       su.declareParameter(new SqlParameter("id", Types.INTEGER));
> > >>       su.declareParameter(new SqlParameter("lob", Types.BLOB,
> > >>lobHandler));
> > >>       su.compile();
> > >>
> > >>The LOB parameter is declared with a new constructor that takes the
> > >>LobHandler as the third argument.  If you don't specify a LobHandler we
> > >>will automatically generate a DefaultLobHandler.
> > >>
> > >>Next we need to create an array with all parameters and call the
> > >>update() method to insert each row.  The LOB value must be passed in as
> > >>an SqlLobValue object together with the length (unless you use a byte
> > >>array or String in which case we can easily figure out the length).
> > >>SqlLobValue is a new class that holds the actual LOB value plus the
> > >>LobCreator that the framework internally populates during the update.
> > >>
> > >>       Object[] inval = new Object[2];
> > >>       inval[0] = new Integer(newId);
> > >>       File in = new File("some.doc");
> > >>       InputStream is = new FileInputStream(in);
> > >>       inval[1] = new SqlLobValue(is, (int) in.length());
> > >>       int count = su.update(inval);
> > >>       is.close();
> > >>
> > >>That is it.  It works the same way for CLOBs and it also works for
> > >>parameters passed in to a StoredProcedure.
> > >>
> > >>While making this change I combined the setXxxxx logic for
> > >>PreparedStatement and CallableStatement to one static method that I
> > >>added to JdbcUtils.  If you notice anything odd due to this refactoring,
> > >>please let me know.
> > >>
> > >>Thomas
> > >>
> > >>
> > >>
> > >>-------------------------------------------------------
> > >>This SF.Net email is sponsored by the new InstallShield X.
> > >>From Windows to Linux, servers to mobile, InstallShield X is the one
> > >>installation-authoring solution that does it all. Learn more and
> > >>evaluate today! http://www.installshield.com/Dev2Dev/0504
> > >>_______________________________________________
> > >>Springframework-developer mailing list
> > >>Spr...@li...
> > >>https://lists.sourceforge.net/lists/listinfo/springframework-developer
> > >>
> > >>
> > >
> > >
> > >-------------------------------------------------------
> > >This SF.Net email is sponsored by: GNOME Foundation
> > >Hackers Unite!  GUADEC: The world's #1 Open Source Desktop Event.
> > >GNOME Users and Developers European Conference, 28-30th June in Norway
> > >http://2004/guadec.org
> > >_______________________________________________
> > >Springframework-developer mailing list
> > >Spr...@li...
> > >https://lists.sourceforge.net/lists/listinfo/springframework-developer
> > >
> > >
> > >
> > >
> > >
> >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by: GNOME Foundation
> > Hackers Unite!  GUADEC: The world's #1 Open Source Desktop Event.
> > GNOME Users and Developers European Conference, 28-30th June in Norway
> > http://2004/guadec.org
> > _______________________________________________
> > Springframework-developer mailing list
> > Spr...@li...
> > https://lists.sourceforge.net/lists/listinfo/springframework-developer
> >
> 
> 
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by the new InstallShield X.
> From Windows to Linux, servers to mobile, InstallShield X is the
> one installation-authoring solution that does it all. Learn more and
> evaluate today! http://www.installshield.com/Dev2Dev/0504
> _______________________________________________
> Springframework-developer mailing list
> Spr...@li...
> https://lists.sourceforge.net/lists/listinfo/springframework-developer