Retrieve the SQL statement being executed

[Ricky Murphy <ri...@hotmail.com>]

Hello: 

I am using Spring 2 together with iBatis 2.2. I need in my program to retrieve the SQL statement being executed from the SqlMap.

Spring's SqlMapClientDaoSupport class has getSqlMapClient() ,which gives com.ibatis.sqlmap.client.SqlMapClient as a result object, from there on, I started searching thru all related the iBatis classes, haven't found one method that will return the SQL statement being executed. Please advise.

Thank you.

-Rick


_________________________________________________________________
Connect and share in new ways with Windows Live.
http://www.windowslive.com/share.html?ocid=TXT_TAGHM_Wave2_sharelife_012008

Re: Retrieve the SQL statement being executed

[Larry Meadors <la...@gmail.com>]

Essentially, the RowHandler is an interface with one method that
provides just one row at a time as a Map or Bean - what you do with it
at that point is up to you.

If you want to crank out xml, you can do that, but I'm not sure what
you mean by "How can rowhandler help me to look into columns?" - you
get a bean with typed properties, or a Map with key/value pairs (very
resultset-ish), so I'm not sure what else you need. Can you elaborate?

If you want to create a fast-lane reader for reporting, you can do
that, too (that might get a bit trickier, but it's totally doable).
This pattern is by it's nature messy, but you would just place a bean
containing a sqlmapclient reference into request scope and execute it
with a scriptlet to provide the output stream and get the RH spewing
data into it. Not a real pretty pattern, but a stinking fast one.

Larry


On Mon, Mar 10, 2008 at 8:23 PM, Ricky Murphy <ri...@hotmail.com> wrote:
>
> Thank you all again for your suggestions.
>
>  One of the use cases of mine is to create an XML in the end. I don't have
> an object serve as a middle stage to use with Rowhandler (in RowHandler's
> term, I need to map the valueObject to something, right?). I also did read
> some post posted by someone a couple of years ago. I didn't think that fits
> my needs. what I need is that my final XML should have structure, not flat
> (i.e. not to simply convert each row into XML format). Therefore, I need to
> check each field/column to determine what tag I should give when I loop. I
> think this is better and easily done if I loop through my resultSet row by
> row and check each field/column by field/column. How can rowhandler help me
> to look into columns? I do use iBatis for other sql/object mapping and it
> works out great. As I mentioned in my earlier post, I certainly didn't want
> to move some special SQLs somewhere else so I thought there might be a way
> to get my SQLs out from sqlMap.xml and use them by spring JDBC instead.
>
>  The other use case is that I need to generate some report, which is also
> very convenient to just loop through resultSet without introducing the
> middle layer object/list (rowHandler might be helpful in that case, but I
> have to evaluate further...)
>
> Thank you
>
>  -Rick
>
>
>  ________________________________
>
>  > Date: Mon, 10 Mar 2008 06:23:24 -0600
> > From: larry.meadors@gmail.com
>
>
> > To: user-java@ibatis.apache.org
> > Subject: Re: Retrieve the SQL statement being executed
> >
> > Depending on your iBATIS version, I think you can get to what you
> > want, but it is pretty fragile, because you have to do some casting to
> > internal classes which could change.
> >
> > I think your safest bet is the RowHandler or a stored procedure - and
> > I say stored procedure only because it sounds like you need to process
> > a lot of data for just a little bit of information. If that is the
> > case, a SP will kick butt over everything else in terms of both memory
> > footprint and performance.
> >
> > Larry
> >
> >
> > On Sun, Mar 9, 2008 at 8:44 PM, Ricky Murphy <ri...@hotmail.com>
> wrote:
> > >
> > > Thank you for your reply. Well, I have no problem doing log activity.
> let me
> > > elaborate what I want to do. I have a SQLMap file which contains all my
> SQLs
> > > maps related to the same domain. In one case, I need to use ResultSet
> > > directly and not need iBatis. However, I want to keep all domain related
> > > SQLs in the same place. In iBatis, it seems there is no decent way to
> > > handle this (RowHandler seems to get the result up in memory (i,e, after
> > > data is obtained, the db connection is closed) first, then I can treat
> each
> > > row as a object. I don't want to go that extra step. To work with each
> row
> > > in ResultSet seemed to be efficient to me without extra steps. This is
> the
> > > only SQL that does not need iBatis. However, in order to keep all SQL
> maps
> > > that tackle the same domain of problems together, I want to put this
> special
> > > SQL together with sqlMaps together in the same XML file. Because this, I
> > > want sqlMapClient to somehow read the SQL and then in my code retrieve
> the
> > > SQL and execute it with Spring jdbcTemplate to get my resultSet. I hope
> that
> > > I explained clearly.
> > >
> > > iBatis has a session scope, and requestScope objects that seem to be OK
> to
> > > get the SQL statement. however, they are protect method and not open to
> > > public. Therefore, I am trying to see if there is any way to get that
> SQL
> > > before it is executed.
> > >
> > > -Rick
> > >
> > > ________________________________
> > > From: nathan.maves@gmail.com
> > > To: user-java@ibatis.apache.org
> > > Subject: Re: Retrieve the SQL statement being executed
> > > Date: Sat, 8 Mar 2008 07:33:38 -0700
> > >
> > >
> > >
> > > Please take a look at the logging section in one of the following 3
> > > places...
> > >
> > > The website
> > > The developers guide
> > > The book
> > >
> > > Sent from my iPhone
> > >
> > > On Mar 8, 2008, at 5:55 AM, Ricky Murphy <ri...@hotmail.com>
> wrote:
> > >
> > >
> > >
> > > Hello:
> > >
> > > I am using Spring 2 together with iBatis 2.2. I need in my program to
> > > retrieve the SQL statement being executed from the SqlMap.
> > >
> > > Spring's SqlMapClientDaoSupport class has getSqlMapClient() ,which gives
> > > com.ibatis.sqlmap.client.SqlMapClient as a result object, from there on,
> I
> > > started searching thru all related the iBatis classes, haven't found one
> > > method that will return the SQL statement being executed. Please advise.
> > >
> > > Thank you.
> > >
> > > -Rick
> > >
> > >
> > > ________________________________
> > > Connect and share in new ways with Windows Live. Get it n
> > > ________________________________
> > > Climb to the top of the charts! Play the word scramble challenge with
> star
> > > power. Play now!
>
>
> ________________________________
> Shed those extra pounds with MSN and The Biggest Loser! Learn more.

RE: Retrieve the SQL statement being executed

[Ricky Murphy <ri...@hotmail.com>]

Thank you all again for your suggestions. 
 
One of the use cases of mine is to create an XML in the end. I don't have an object serve as a middle stage to use with Rowhandler (in RowHandler's term, I need to map the valueObject to something, right?). I also did read some post posted by someone a couple of years ago. I didn't think that fits my needs. what I need is that my final XML should have structure, not flat (i.e. not to simply convert each row into XML format). Therefore, I need to check each field/column to determine what tag I should give when I loop. I think this is better and easily done if I loop through my resultSet row by row and check each field/column by field/column. How can rowhandler help me to look into columns? I do use iBatis for other sql/object mapping and it works out great. As I mentioned in my earlier post, I certainly didn't want to move some special SQLs somewhere else so I thought there might be a way to get my SQLs out from sqlMap.xml and use them by spring JDBC instead. 
 
The other use case is that I need to generate some report, which is also very convenient to just loop through resultSet without introducing the middle layer object/list (rowHandler might be helpful in that case, but I have to evaluate further...)Thank you
 
-Rick



> Date: Mon, 10 Mar 2008 06:23:24 -0600> From: larry.meadors@gmail.com> To: user-java@ibatis.apache.org> Subject: Re: Retrieve the SQL statement being executed> > Depending on your iBATIS version, I think you can get to what you> want, but it is pretty fragile, because you have to do some casting to> internal classes which could change.> > I think your safest bet is the RowHandler or a stored procedure - and> I say stored procedure only because it sounds like you need to process> a lot of data for just a little bit of information. If that is the> case, a SP will kick butt over everything else in terms of both memory> footprint and performance.> > Larry> > > On Sun, Mar 9, 2008 at 8:44 PM, Ricky Murphy <ri...@hotmail.com> wrote:> >> > Thank you for your reply. Well, I have no problem doing log activity. let me> > elaborate what I want to do. I have a SQLMap file which contains all my SQLs> > maps related to the same domain. In one case, I need to use ResultSet> > directly and not need iBatis. However, I want to keep all domain related> > SQLs in the same place. In iBatis, it seems there is no decent way to> > handle this (RowHandler seems to get the result up in memory (i,e, after> > data is obtained, the db connection is closed) first, then I can treat each> > row as a object. I don't want to go that extra step. To work with each row> > in ResultSet seemed to be efficient to me without extra steps. This is the> > only SQL that does not need iBatis. However, in order to keep all SQL maps> > that tackle the same domain of problems together, I want to put this special> > SQL together with sqlMaps together in the same XML file. Because this, I> > want sqlMapClient to somehow read the SQL and then in my code retrieve the> > SQL and execute it with Spring jdbcTemplate to get my resultSet. I hope that> > I explained clearly.> >> > iBatis has a session scope, and requestScope objects that seem to be OK to> > get the SQL statement. however, they are protect method and not open to> > public. Therefore, I am trying to see if there is any way to get that SQL> > before it is executed.> >> > -Rick> >> > ________________________________> > From: nathan.maves@gmail.com> > To: user-java@ibatis.apache.org> > Subject: Re: Retrieve the SQL statement being executed> > Date: Sat, 8 Mar 2008 07:33:38 -0700> >> >> >> > Please take a look at the logging section in one of the following 3> > places...> >> > The website> > The developers guide> > The book> >> > Sent from my iPhone> >> > On Mar 8, 2008, at 5:55 AM, Ricky Murphy <ri...@hotmail.com> wrote:> >> >> >> > Hello:> >> > I am using Spring 2 together with iBatis 2.2. I need in my program to> > retrieve the SQL statement being executed from the SqlMap.> >> > Spring's SqlMapClientDaoSupport class has getSqlMapClient() ,which gives> > com.ibatis.sqlmap.client.SqlMapClient as a result object, from there on, I> > started searching thru all related the iBatis classes, haven't found one> > method that will return the SQL statement being executed. Please advise.> >> > Thank you.> >> > -Rick> >> >> > ________________________________> > Connect and share in new ways with Windows Live. Get it n> > ________________________________> > Climb to the top of the charts! Play the word scramble challenge with star> > power. Play now!
_________________________________________________________________
Shed those extra pounds with MSN and The Biggest Loser!
http://biggestloser.msn.com/

Re: Retrieve the SQL statement being executed

[Larry Meadors <la...@gmail.com>]

Depending on your iBATIS version, I think you can get to what you
want, but it is pretty fragile, because you have to do some casting to
internal classes which could change.

I think your safest bet is the RowHandler or a stored procedure - and
I say stored procedure only because it sounds like you need to process
a lot of data for just a little bit of information. If that is the
case, a SP will kick butt over everything else in terms of both memory
footprint and performance.

Larry


On Sun, Mar 9, 2008 at 8:44 PM, Ricky Murphy <ri...@hotmail.com> wrote:
>
> Thank you for your reply. Well, I have no problem doing log activity. let me
> elaborate what I want to do. I have a SQLMap file which contains all my SQLs
> maps related to the same domain. In one case, I need to use ResultSet
> directly and not need iBatis. However, I want to keep all domain related
> SQLs in the same place.    In iBatis, it seems there is no decent way to
> handle this (RowHandler seems to get the result up in memory (i,e, after
> data is obtained, the db connection is closed) first, then I can treat each
> row as a object. I don't want to go that extra step. To work with each row
> in ResultSet seemed to be efficient to me without extra steps. This is the
> only SQL that does not need iBatis. However, in order to keep all SQL maps
> that tackle the same domain of problems together, I want to put this special
> SQL together with sqlMaps together in the same XML file. Because this, I
> want sqlMapClient to somehow read the SQL and then in my code retrieve the
> SQL and execute it with Spring jdbcTemplate to get my resultSet. I hope that
> I explained clearly.
>
> iBatis has a session scope, and requestScope objects that seem to be OK to
> get the SQL statement. however, they are protect method and not open to
> public. Therefore, I am trying to see if there is any way to get that SQL
> before it is executed.
>
> -Rick
>
> ________________________________
> From: nathan.maves@gmail.com
> To: user-java@ibatis.apache.org
> Subject: Re: Retrieve the SQL statement being executed
> Date: Sat, 8 Mar 2008 07:33:38 -0700
>
>
>
> Please take a look at the logging section in one of the following 3
> places...
>
> The website
> The developers guide
> The book
>
> Sent from my iPhone
>
> On Mar 8, 2008, at 5:55 AM, Ricky Murphy <ri...@hotmail.com> wrote:
>
>
>
> Hello:
>
> I am using Spring 2 together with iBatis 2.2. I need in my program to
> retrieve the SQL statement being executed from the SqlMap.
>
> Spring's SqlMapClientDaoSupport class has getSqlMapClient() ,which gives
> com.ibatis.sqlmap.client.SqlMapClient as a result object, from there on, I
> started searching thru all related the iBatis classes, haven't found one
> method that will return the SQL statement being executed. Please advise.
>
> Thank you.
>
> -Rick
>
>
> ________________________________
> Connect and share in new ways with Windows Live. Get it n
> ________________________________
> Climb to the top of the charts! Play the word scramble challenge with star
> power. Play now!

Re: Retrieve the SQL statement being executed

[Christopher Lamey <cl...@localmatters.com>]

I'm not sure why a RowHandler wouldn't fit your needs.  It basically is a
callback that gets invoked on each row returned from the query.  Only one
resultant row is mapped at a time, so the memory usage is very small.  The
result does get mapped into an object, but the overhead is minimal - and you
can map it to a java.util.Map if you want it to look more like a ResultSet.

The amount of effort to get a RowHandler working for you compared to the
effort of getting at iBATIS' generated SQL without actually using the SqlMap
to execute the query doesn't seem close.

But then, I could be misunderstanding your situation...

On 3/9/08 8:44 PM, "Ricky Murphy" <ri...@hotmail.com> wrote:

> Thank you for your reply. Well, I have no problem doing log activity. let me
> elaborate what I want to do. I have a SQLMap file which contains all my SQLs
> maps related to the same domain. In one case, I need to use ResultSet directly
> and not need iBatis. However, I want to keep all domain related SQLs in the
> same place.    In iBatis, it seems there is no decent way to handle this
> (RowHandler seems to get the result up in memory (i,e, after data is obtained,
> the db connection is closed) first, then I can treat each row as a object. I
> don't want to go that extra step. To work with each row in ResultSet seemed to
> be efficient to me without extra steps. This is the only SQL that does not
> need iBatis. However, in order to keep all SQL maps that tackle the same
> domain of problems together, I want to put this special SQL together with
> sqlMaps together in the same XML file. Because this, I want sqlMapClient to
> somehow read the SQL and then in my code retrieve the SQL and execute it with
> Spring jdbcTemplate to get my resultSet. I hope that I explained clearly.
> 
> iBatis has a session scope, and requestScope objects that seem to be OK to get
> the SQL statement. however, they are protect method and not open to public.
> Therefore, I am trying to see if there is any way to get that SQL before it is
> executed. 
> 
> -Rick
> 
> From: nathan.maves@gmail.com
> To: user-java@ibatis.apache.org
> Subject: Re: Retrieve the SQL statement being executed
> Date: Sat, 8 Mar 2008 07:33:38 -0700
> 
> Please take a look at the logging section in one of the following 3
> places...The websiteThe developers guideThe book
> 
> Sent from my iPhone
> On Mar 8, 2008, at 5:55 AM, Ricky Murphy <ri...@hotmail.com> wrote:
> 
> Hello: 
> 
> I am using Spring 2 together with iBatis 2.2. I need in my program to retrieve
> the SQL statement being executed from the SqlMap.
> 
> Spring's SqlMapClientDaoSupport class has getSqlMapClient() ,which gives
> com.ibatis.sqlmap.client.SqlMapClient as a result object, from there on, I
> started searching thru all related the iBatis classes, haven't found one
> method that will return the SQL statement being executed. Please advise.
> 
> Thank you.
> 
> -Rick
> 
> 
> Connect and share in new ways with Windows Live. Get it n
> _________________________________________________________________
> Climb to the top of the charts! Play the word scramble challenge with star
> power.
> http://club.live.com/star_shuffle.aspx?icid=starshuffle_wlmailtextlink_jan

RE: Retrieve the SQL statement being executed

[Ricky Murphy <ri...@hotmail.com>]

Thank you for your reply. Well, I have no problem doing log activity. let me elaborate what I want to do. I have a SQLMap file which contains all my SQLs maps related to the same domain. In one case, I need to use ResultSet directly and not need iBatis. However, I want to keep all domain related SQLs in the same place.    In iBatis, it seems there is no decent way to handle this (RowHandler seems to get the result up in memory (i,e, after data is obtained, the db connection is closed) first, then I can treat each row as a object. I don't want to go that extra step. To work with each row in ResultSet seemed to be efficient to me without extra steps. This is the only SQL that does not need iBatis. However, in order to keep all SQL maps that tackle the same domain of problems together, I want to put this special SQL together with sqlMaps together in the same XML file. Because this, I want sqlMapClient to somehow read the SQL and then in my code retrieve the SQL and execute it with Spring jdbcTemplate to get my resultSet. I hope that I explained clearly.

iBatis has a session scope, and requestScope objects that seem to be OK to get the SQL statement. however, they are protect method and not open to public. Therefore, I am trying to see if there is any way to get that SQL before it is executed. 

-Rick

From: nathan.maves@gmail.com
To: user-java@ibatis.apache.org
Subject: Re: Retrieve the SQL statement being executed
Date: Sat, 8 Mar 2008 07:33:38 -0700

Please take a look at the logging section in one of the following 3 places...The websiteThe developers guideThe book

Sent from my iPhone
On Mar 8, 2008, at 5:55 AM, Ricky Murphy <ri...@hotmail.com> wrote:

Hello: 

I am using Spring 2 together with iBatis 2.2. I need in my program to retrieve the SQL statement being executed from the SqlMap.

Spring's SqlMapClientDaoSupport class has getSqlMapClient() ,which gives com.ibatis.sqlmap.client.SqlMapClient as a result object, from there on, I started searching thru all related the iBatis classes, haven't found one method that will return the SQL statement being executed. Please advise.

Thank you.

-Rick


Connect and share in new ways with Windows Live. Get it n
_________________________________________________________________
Climb to the top of the charts! Play the word scramble challenge with star power.
http://club.live.com/star_shuffle.aspx?icid=starshuffle_wlmailtextlink_jan

Re: Retrieve the SQL statement being executed

[Nathan Maves <na...@gmail.com>]

Please take a look at the logging section in one of the following 3  
places...

The website
The developers guide
The book

Sent from my iPhone

On Mar 8, 2008, at 5:55 AM, Ricky Murphy <ri...@hotmail.com>  
wrote:

> Hello:
>
> I am using Spring 2 together with iBatis 2.2. I need in my program  
> to retrieve the SQL statement being executed from the SqlMap.
>
> Spring's SqlMapClientDaoSupport class has getSqlMapClient() ,which  
> gives com.ibatis.sqlmap.client.SqlMapClient as a result object, from  
> there on, I started searching thru all related the iBatis classes,  
> haven't found one method that will return the SQL statement being  
> executed. Please advise.
>
> Thank you.
>
> -Rick
>
>
> Connect and share in new ways with Windows Live. Get it n