Aaaaah, ok! I get it, thanks for explaining :-) Regards --
Hippo Oosteinde 11 1017WT Amsterdam The Netherlands Tel +31 (0)20 5224466 ------------------------------------------------------------- [EMAIL PROTECTED] / http://www.hippo.nl <http://www.hippo.nl/> -------------------------------------------------------------- -----Original Message----- From: Andrew Stevens [mailto:[EMAIL PROTECTED] Posted At: maandag 2 juli 2007 13:45 Posted To: Cocoon User List Conversation: Cocoon database access strategy Subject: RE: Cocoon database access strategy > Date: Mon, 2 Jul 2007 10:25:35 +0200 > From: [EMAIL PROTECTED] > > Hello Andrew, > > I am not sure, but I really do not understand your email? You are talking > about caching the prepared statement, but I do not get what you try to > achieve with it...? > > The sql transformer will never cache, because it does not implement > CacheableProcessingComponent. You're right, you didn't understand my email ;-) This was nothing to do with Cocoon caching the output of the transformer, which as you say doesn't happen because the transformer isn't a CacheableProcessingComponent. The problem I was replying to was to do with the caching of prepared statements within the database connection so that running the same statement with different parameters doesn't need the database server to recompile the query each time. That's what makes it "prepared" rather than just a Statement... If the sql:query's where clause includes the parameters with sql:substitute-value, then a separate statement is cached for each set of parameters (so there is no benefit). If the where clause uses "?" and sql:in-parameter to supply the value, a single copy will be stored and re-used. Andrew. -- http://pseudoq.sourceforge.net/ > > Ard > > > > > > > Bloody hotmail appears to have stripped out all my sql & xsl > > namespaced XML elements :-( > > Anyone know how to disable this in the new "improved" interface? > > > > > From: [EMAIL PROTECTED] > > > Date: Sun, 1 Jul 2007 02:35:03 +0100 > > > > > > > Date: Sun, 24 Jun 2007 11:14:38 +0200 > > > > From: [EMAIL PROTECTED] > > > > > > > > Rob Frohwein wrote: > > > > > - query1.xml using SqlTransformer on table1 > > > > > - cleansql.xsl rename > > > > > - process1.xsl reorganize > > > > > - query2.xsl query different table > > > > > - cleansql.xsl rename > > > > > - process2.xsl reorganize > > > > > ... > > > > > > > > > > It works, but is this the "right" approach? > > > > > > > ... > > > > > > > > It works well, but it has some security and performance > > problems (more > > > > on that in a minute) so I'd like to know if there's a > > better approach. > > > > > > > > One of the problems with the SQL Transformer, when used > > with XSLT in > > > > this fashion, is that it lacks support for prepared statements and > > > > parameters. Without parameters ( in XSP) you have the > > > > same security problems you would have in badly written PHP: > > > > > > > > SELECT ... > > > > FROM ... > > > > WHERE name = '' > > > > > > > > You did think of this problem, didn't you? :) I can post > > my:addslashes() > > > > if you want. In any case you will agree that composing > > queries this way > > > > is... "antiquated" at best. > > > > > > > > The compiled query cannot be cached (as a prepared > > statement would), > > > > because it changes with every request, and I fear the SQL > > Transformer > > > > doesn't even try to cache it. > > > > > > What about > > > > > > > substitute brackets with less than & greater than symbols as > > appropriate... > > > > (sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0";) > > (sql:query) > > SELECT ... > > FROM ... > > WHERE name = ? > > (/sql:query) > > (sql:in-parameter nr="1") > > (xsl:attribute name="value")(xsl:value-of > > select="$whatever"/)(/xsl:attribute) > > (/sql:in-parameter) > > (/sql:execute-query) > > > > > ? The in-parameter may change, but the query itself > > doesn't so the prepared statement ought to cache it. > > > > > > Admittedly, as written above you've got to supply the > > in-parameter value as a map:parameter to the XSL transformer > > rather than the SQL transformer, since it's not valid XML to > > put elements inside attributes (so > > > > (sql:in-parameter nr="1" value="(sql:substitute-value > > name="whatever"/)"/) > > > > >isn't valid). To work around that, we customised the > > transformer so that in-parameter can take either a constant > > value (in the value attribute) or the name of a supplied > > parmeter (in a new "param" attribute) which is substituted in > > the same fashion as substitute-value. That shortens it to > > > > (sql:query) > > SELECT ... > > FROM ... > > WHERE name = ? > > (/sql:query) > > (sql:in-parameter nr="1" param="whatever"/) > > > > > > Andrew. _____ The next generation of MSN Hotmail has arrived - Windows Live Hotmail <http://www.newhotmail.co.uk>
