cocoon-docs mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Cocoon Wiki] Update of "HowtoQueryFromWebBrowser" by petefarmer
Date Sat, 27 Aug 2005 10:19:59 GMT
Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Cocoon Wiki" for change notification.

The following page has been changed by petefarmer:
http://wiki.apache.org/cocoon/HowtoQueryFromWebBrowser

New page:
==  Howto Run SQL Queries From Your Browser ==
- TARGET-AUDIENCE: '''*beginner*''' advanced expert[[BR]]
- COCOON-RELEASES: 2.0.3, 2.0.4[[BR]]
- DOCUMENT-STATUS: '''*draft*''' reviewed released[[BR]]
----

=== What you will get from this page ===
Simple instructions describing how to connect your browser to your SQL database. You will
then be able to execute SQL commands in your browser's location bar, like this:
{{{
http://localhost:8080/cocoon/~pete/sql?stmt=select * from user
}}}
or:
{{{
http://localhost:8080/cocoon/~pete/sql?stmt=insert into user values(NULL,"pete","pete@foo.com")
}}}
or, more dangerously:
{{{
http://localhost:8080/cocoon/~pete/sql?stmt=delete from user where uid="pete"
}}}
or even:(!)
{{{
http://localhost:8080/cocoon/~pete/sql?stmt=drop table user
}}}
...which is why I need to warn you right now:
=== Warning! do not use this on a production system, or a system exposed to the public Internet,
etc. ===
This howto example severly compromises system security. Kids, don't try this at home, don't
run with scissors, don't do drugs, wear a rubber, etc.
=== So why might you want to do this? ===
Well, if you're a developer, or a Cocoon user, you might want to use this as a handy SQL client.
If you're neither, then you probably shouldn't be reading this. This technique might amuse
your audience during software demo presentations, or provide a very minor amount of help with
development, but it will not win you a Nobel Prize, and it is unlikely to enhance your allure
to exotic women. File this under "Stupid Cocoon Tricks" or "Worst Practices" and go outside
and play instead!
=== Your basic skills ===
Modest familiarity with trivial Cocoon applications.
=== Technical prerequisites ===
Database connected to Cocoon.
=== Links to other information sources ===
[:SpecificDatabaseConnection]


----
=== Overview ===
Here are the steps we will follow:
 *  Step 1: add the {{{ServerPagesGenerator}}} component to your {{{sitemap.xmap}}}
 *  Step 2: add a matcher snippet to your {{{<pipeline>}}} to match your SQL requests
 *  Step 3: create an {{{.xsp}}} file to generate the XML from the database query
 *  Step 4: optionally, create an {{{.xsl}}} stylesheet to transform the generated XML output
to a flatter format, which may be better suited to your personal and private wants and needs
=== Step 1 - Add the ServerPagesGenerator to your sitemap.xmap ===
Add this to your {{{sitemap.xmap}}}:
{{{
 <map:components>
  <map:generators>
   <map:generator
        label="content,data"
        logger="sitemap.generator.serverpages"
        name="xsp"
        pool-grow="2"
        pool-max="32"
        pool-min="4"
        src="org.apache.cocoon.generation.ServerPagesGenerator"/>
  </map:generators>
 </map:components>
}}}

=== Step 2: add a matcher to your sitemap.xmap ===
Add this to your {{{<pipeline>}}}:
{{{
   <map:match pattern="sql">
    <map:generate type="xsp" src="sql.xsp">
     <map:parameter name="use-request-parameters" value="true"/>
    </map:generate>
    <map:serialize type="xml"/>
   </map:match>
}}}

=== Step 3: create sql.xsp ===

Copy and save as sql.xsp: (be sure to substitute the name of your pool, as configured in {{{cocoon.xconf}}}
in the {{{<esql:pool>}}} tag below)

{{{
<?xml version="1.0" encoding="iso-8859-1"?>

<xsp:page
     language="java"
     xmlns:xsp="http://apache.org/xsp"
     xmlns:esql="http://apache.org/cocoon/SQL/v2"
     xmlns:xsp-request="http://apache.org/xsp/request/2.0">

<resultset>
 <esql:connection>
  <esql:pool>mydevpool</esql:pool> <!-- your pool here -->
  <esql:execute-query>
   <esql:query>
    <xsp-request:get-parameter name="stmt"/>
   </esql:query>
   <esql:results>
    <esql:row-results>
     <xsp:element name="result">
      <esql:get-columns/>
     </xsp:element>
    </esql:row-results>
   </esql:results>
   <esql:no-results/>
   <esql:error-results>
    <xsp:element name="result">
     <esql:get-message/>
     <esql:get-stacktrace/>
     <esql:to-string/>
    </xsp:element>
   </esql:error-results>
   <esql:update-results>
    <xsp:element name="result">
     <update>
     <esql:get-update-count/>
     </update>
    </xsp:element>
   </esql:update-results>
  </esql:execute-query>
 </esql:connection>
</resultset>
</xsp:page>
}}}

=== Test ===
Now you should be able to execute an SQL statement on your database by typing the statement
in the location bar of your browser like this:
{{{
http://path_to_your_db_host:your_port/path_to_your_cocoon_work_dir/sql?stmt=select * from
user
}}}
Of course, whether this works or not depends on whether you have a table called 'user' in
the db specified by your {{{<datasource>}}} (defined in {{{cocoon.xconf}}})
If it works, you should see something like:
{{{
<resultset>
 <result>
  <recnum>1</recnum>
  <uid>pete</uid>
  <name>Pete Farmer</name>
 </result>
 <result>
  <recnum>2</recnum>
  <uid>bob</uid>
  <name>Robert Vesco</name>
 </result>
 <result>
  <recnum>3</recnum>
  <uid>sonny</uid>
  <name>Sonny Barger</name>
 </result>
<resultset>
}}}
If it doesn't work, you may want to make sure that your database is configured properly to
sing and dance merrily with Cocoon.
See [:SpecificDatabaseConnection]

=== Step 4: (optional) Post-process the results ===
You may want to flatten your {{{<resultset>}}}, and collapes the XML elements returned
by the XSP generator into attributes of the {{{<result>}}} element. This may be convienient
if your are using other products in your software stack, or whatever. Otherwise, you're done.
Go outside and ride your skateboard!

If you've decided you're not done, then add this line to {{{<pipeline>}}} in your {{{sitemap.xmap}}}:
{{{
    <map:transform src="sql.xsl"/>
}}}
so that it now looks like this:
{{{
   <map:match pattern="sql">
    <map:generate type="xsp" src="sql.xsp">
     <map:parameter name="use-request-parameters" value="true"/>
    </map:generate>
    <map:transform src="sql.xsl"/>
    <map:serialize type="xml"/>
   </map:match>

}}}

And create the file {{{sql.xsl}}}:
{{{
<?xml version="1.0" encoding="iso-8859-1"?>
<xsl:stylesheet
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"
>
<xsl:strip-space elements="*"/>

 <xsl:template match="*">
  <xsl:copy>
   <xsl:for-each select="@*|*[not(* or @*)]">
    <xsl:attribute name="{name(.)}"><xsl:value-of select="."/>
    </xsl:attribute>
   </xsl:for-each>
   <xsl:apply-templates select="*[* or @*]|text()"/>
  </xsl:copy>
 </xsl:template>
</xsl:stylesheet>
}}}

Now when you run your query your results will look like this:
{{{
<resultset>
 <result recnum="1" uid="pete" name="Pete Farmer"/>
 <result recnum="2" uid="bob" name="Robert Vesco"/>
 <result recnum="3" uid="sonny" name="Sonny Barger"/>
<resultset>
}}}

which, depending on your application, may be more useful.

=== The End ===
With a few drops of the magical Cocoon krazy glue, you have now "glued" your browser to your
db. Enjoy.
That's all. Finito. What do you want for nothing?
----
===  page metadata ===
- AUTHOR:petefarmer[[BR]]
- AUTHOR-CONTACT: mailto:pete.farmer@gmail.com[[BR]]
- REVIEWED-BY:[[BR]]
- REVIEWER-CONTACT:[[BR]]

Mime
View raw message