is possible?

[Thomas Guettler <gu...@interface-business.de>]

The following code does not work. {@skip} gets ignored.
(I use the get-method (http:asfaf.asf.af/asf?skip=10))
  <query connection="www_odbc" max-rows="2" skip-rows="{@skip}">  
   select * from person 
  </query> 

I found a solution, which I don't like, because it uses xsp.
Has some one an idear how to achieve this without xsp?

Here is my solution:	
  <query connection="www_odbc" max-rows="2">  
  <xsp:attribute
name="skip-rows"><xsp:expr>request.getParameter("skip")</xsp:expr></xsp:attribute>
   select * from person 
  </query> 


Why works the {@foo} inside the query, but not in the attributes?:
  <query connection="www_odbc" max-rows="2" skip-rows="{@skip}">  
   select * from person where id={@id}
  </query> 
If it is not difficult to get it working I could maybe fix it.

thomas

Re: is possible?

[Thomas Guettler <gu...@interface-business.de>]

Sebastien Koechlin I-VISION wrote:
> 
> Paul Russell a écrit :
> >
> > Are you sure? I thought it was part of SQL92. I know it works in
> > recent versions of postgresql, and it works in MS SQLServer (Database
> > admin in a previous life). I'm too poor to afford oracle at the moment,
> > so I can't check it there.
> 
> It does not work with Oracle7.
> 
> You have to rewrite your queries with ROWNUM:
> 
>         For each row returned by a query, the ROWNUM pseudocolumn
>         returns a number indicating the order in which Oracle7 selects
>         the row from a table or set of joined rows. The first row
>         selected has a ROWNUM of 1, the second has 2, and so on.
> 
>                 SELECT * FROM emp WHERE ROWNUM < 10
> 
> --
> Sébastien Koechlin

I searched the oracle8 docu and couldn't find a LIMIT-Statement, too.
I tried rownum<10 and it works, but rownum>10 doesn't work. (maybe OT)
(the docu says that rownum>x always returns false)
That's really stupid, that there doesn't seem to be a portable solution.


Thomas

Re: is possible?

[Sebastien Koechlin I-VISION <sk...@n-soft.com>]

Paul Russell a écrit :
> 
> Are you sure? I thought it was part of SQL92. I know it works in
> recent versions of postgresql, and it works in MS SQLServer (Database
> admin in a previous life). I'm too poor to afford oracle at the moment,
> so I can't check it there.

It does not work with Oracle7.

You have to rewrite your queries with ROWNUM:

	For each row returned by a query, the ROWNUM pseudocolumn
	returns a number indicating the order in which Oracle7 selects
	the row from a table or set of joined rows. The first row
	selected has a ROWNUM of 1, the second has 2, and so on.

		SELECT * FROM emp WHERE ROWNUM < 10

-- 
Sébastien Koechlin

Re: is possible?

[Paul Russell <Pa...@uea.ac.uk>]

On Tue, 2 May 2000, Thomas Guettler wrote:
> Thank you for your quick reply.
> But LIMIT is mysql-spezific. Currently I could
> use it, but we maybe want to switch to an other
> database in the future.

Are you sure? I thought it was part of SQL92. I know it works in
recent versions of postgresql, and it works in MS SQLServer (Database
admin in a previous life). I'm too poor to afford oracle at the moment,
so I can't check it there.


Paul

Re: is possible?

[Thomas Guettler <gu...@interface-business.de>]

Thank you for your quick reply.
But LIMIT is mysql-spezific. Currently I could
use it, but we maybe want to switch to an other
database in the future.

tom stuart wrote:
> 
> On Tue, 2 May 2000, Thomas Guettler wrote:
> 
> > The following code does not work. {@skip} gets ignored.
> >   <query connection="www_odbc" max-rows="2" skip-rows="{@skip}">
> > Has some one an idear how to achieve this without xsp?
> > Why works the {@foo} inside the query, but not in the attributes?:
> 
> I can't speak authoritatively about the SQLProcessor (I haven't looked at
> the source yet) but presumably it only does this parameter interpolation
> on the contents of the <query> tag (ie. your query), but not the tag
> attributes. This makes a certain kind of sense, since you can redefine the
> variable-left-delimiter and variable-right-delimiter with the <query> tag
> attributes, which would make interpolation on the attributes themselves a
> bit self-referential.
> 
> The only obvious solution I can see is to implement the skip-rows
> functionality directly in your SQL query, as in:
> 
>         SELECT * FROM person WHERE id={@id} LIMIT {@skip},10
> 
> which will select 10 rows starting from row (skip+1).
> 
> I know this works with MySQL; YMMV.
> 
> -Tom
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
> For additional commands, e-mail: cocoon-users-help@xml.apache.org

Re: is possible?

[tom stuart <to...@obsess.com>]

On Tue, 2 May 2000, Thomas Guettler wrote:

> The following code does not work. {@skip} gets ignored.
>   <query connection="www_odbc" max-rows="2" skip-rows="{@skip}">  
> Has some one an idear how to achieve this without xsp?
> Why works the {@foo} inside the query, but not in the attributes?:

I can't speak authoritatively about the SQLProcessor (I haven't looked at
the source yet) but presumably it only does this parameter interpolation
on the contents of the <query> tag (ie. your query), but not the tag
attributes. This makes a certain kind of sense, since you can redefine the
variable-left-delimiter and variable-right-delimiter with the <query> tag
attributes, which would make interpolation on the attributes themselves a
bit self-referential.

The only obvious solution I can see is to implement the skip-rows
functionality directly in your SQL query, as in:

	SELECT * FROM person WHERE id={@id} LIMIT {@skip},10

which will select 10 rows starting from row (skip+1).

I know this works with MySQL; YMMV.

-Tom

Re: sql tag-library

[Rob Parker <ro...@webcybernetics.com>]

----- Original Message -----
From: Michele Bianchi <mi...@exsense.com>
To: cocoon-users <co...@xml.apache.org>
Sent: Monday, May 08, 2000 4:15 AM
Subject: sql tag-library


>
snip...
> to change the dbms.  the second one to have a choice specify a
dbconnection
> or the dburl.  the db connection it's a really expensive operation it
seems
> imho to create a global var and reuse it.  please tell me if it is stupid,
cos
> i'm new in servlet programming.
>
Your on the right track in that opening a connection to a DB is an expensive
operation. I guess you're suggesting allocating a single global instance
which
persists for the lifetime of the 'servlet'. This does save the connection
overhead,
but you may run into some concurrency issues.

Consider what happens when the 'servlet' receives multiple concurrent
requests,
each request will result in a thread using your single DB connection. Hence
if the driver
is thread safe, all may be ok. If not you'll get strange behaviours. Now I'm
no expert, but as
far as I know a lot of drivers aren't thread safe. I believe some that are,
really just serialize
access to the database, hence your scalability is poor.

What you're after is a DB connection pool. This object acts like a factory
which hands out
connection objects on request. It maintains a collection of connections
ready to go, hence you
remove the connection overhead, and you get thread safety. There is a lot of
discussion at the
moment about integrating connection pools into the sql/xsp tag libraries.

hope this helps

Rob

sql tag-library

[Michele Bianchi <mi...@exsense.com>]

i'm wondering about 2 ideas on the sql tag-library.  the first to specify the
driver name in the config file (or smth like that) so it could be really easy
to change the dbms.  the second one to have a choice specify a dbconnection
or the dburl.  the db connection it's a really expensive operation it seems
imho to create a global var and reuse it.  please tell me if it is stupid, cos
i'm new in servlet programming.

-------------------------------------------------------------------------------
michele_bianchi                                  exsense | integrated_solutions
phone://+39_348_7651874                                       via_scrimiari_45b
http://mic.aesthetica.it                                     37132_verona_italy

Re: is possible?

[Donald Ball <ba...@webslingerZ.com>]

On Tue, 2 May 2000, Thomas Guettler wrote:

> The following code does not work. {@skip} gets ignored.
> (I use the get-method (http:asfaf.asf.af/asf?skip=10))
>   <query connection="www_odbc" max-rows="2" skip-rows="{@skip}">  
>    select * from person 
>   </query> 
> 
> I found a solution, which I don't like, because it uses xsp.
> Has some one an idear how to achieve this without xsp?
> 
> Here is my solution:	
>   <query connection="www_odbc" max-rows="2">  
>   <xsp:attribute
> name="skip-rows"><xsp:expr>request.getParameter("skip")</xsp:expr></xsp:attribute>
>    select * from person 
>   </query> 
> 
> 
> Why works the {@foo} inside the query, but not in the attributes?:
>   <query connection="www_odbc" max-rows="2" skip-rows="{@skip}">  
>    select * from person where id={@id}
>   </query> 
> If it is not difficult to get it working I could maybe fix it.

Because {@...} processing is only done for the query text nodes, not the
query's attribute node values. I strongly suggest you move to using XSP
and the XSP SQL taglib instead as it will likely make your life much
easier in this regard.

- donald