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

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

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