XSP basic's

["Nicki L. Møller (LMD)" <Ni...@lmd.ericsson.se>]

Hi All,

I've got a quite simple request to anyone who has tried to combine SQL and XSP. Would you please write some examples on how this could be done.

Thanks in advance

-nicki

Re: XSP basic's

[sudhi <su...@planet.net>]

Hi...
Here is a sample..

I generate sql query from xsp.
---------------------------------------------------
<?xml version="1.0" standalone="no"?>
<?cocoon-process type="xsp"?>
<?cocoon-process type="sql"?>
<?cocoon-process type="xslt"?>
<?xml-stylesheet href="result.xsl" type="text/xsl"?>



<xsp:page language="java"
xmlns:xsp="http://www.apache.org/1999/XSP/Core">

<PAGE>
 <connectiondefs>
  <connection name="foo_connection">
   <driver>org.gjt.mm.mysql.Driver</driver>
   <dburl>jdbc:mysql://your.domain.com:3306/database</dburl>
   <username>user</username>
   <password>pwd</password>
  </connection>
 </connectiondefs> 

  <TITLE> Search result</TITLE>
  &HEADER;
  &LEFTMENU;
  <CONTENT>
  <xsp:logic>
   HttpSession sess = request.getSession(true);
   String allowed =  (String)sess.getValue("allowed");
   if(allowed == null || !allowed.equals("yes")){
        response.sendRedirect(response.encodeUrl("index.xml"));
   }
   String srcSubmit = request.getParameter("SEARCH");
   String mailFax   = request.getParameter("Mail/Fax");  
   String prevPage   = request.getParameter("PrevPage");  
   String nextPage   = request.getParameter("NextPage");  
   String query;
   int maxrow = 5;
   int skiprow = 0;
   int pageNum = 1;

   if (srcSubmit != null &amp;&amp; !srcSubmit.equals("")) {
      String fromDate;
      String toDate;
      String prodName;
      String category;
      String upc;
      query = "select * from abc ";
      query = query + "where revdate &lt;= &apos;" +
                   request.getParameter("TOYEAR") + "-" +
                   request.getParameter("TOMONTH") + "-" + 
                   request.getParameter("TODAY") + "&apos; AND " +
                   "revdate >= &apos;" +
                   request.getParameter("FROMYEAR") + "-" +
                   request.getParameter("FROMMONTH") + "-" + 
                   request.getParameter("FROMDAY") + "&apos; ";
                 
      prodName = request.getParameter("PRODUCT_NAME");
      if (prodName != null &amp;&amp; !prodName.equals("")){
        query = query + "AND prodname = lower(&apos;" + prodName +
"&apos;) " ; 
      }
      category = request.getParameter("CATEGORY");
      if (category != null &amp;&amp; !category.equals("")){
         query = query + "AND category = lower(&apos;" + category +
"&apos;) " ; 
      }
      upc = request.getParameter("UPC");
      if (upc != null &amp;&amp; !upc.equals("")){
         query = query + "AND upc = lower(&apos;" + upc + "&apos;) " ; 
      }
   }
  ............
 </xsp:logic>

 <query connection="foo_connection" count-attribute="count"
id-attribute="id">
  <xsp:attribute
name="max-rows"><xsp:expr>maxrow</xsp:expr></xsp:attribute>
  <xsp:attribute
name="skip-rows"><xsp:expr>skiprow</xsp:expr></xsp:attribute>
  <xsp:expr>query</xsp:expr> 
 </query>

  </CONTENT>
  &RIGHTMENU;
  &FOOTER;
</PAGE>
</xsp:page>
---------------------------------------------------

HTH
Sudhi