How do I use session parameters in a ESQL-Logicsheet

[Urs Iwert <ur...@sysinf.ch>]

Hi

I need data from a database using ESQL-Logicsheet. The sql statement
must be filtered depending on the current session (user logon) and on
session parameters. The session parameters are used, because there are
quite a lot of parameters and all of them are used to query different
data.

What if done yet

- Logon using session framework
- Saving parameters in a session using <setxml/>
- ESQL LogicSheet to access data (all records by now)

What's missing is the last step to bring the filter criterias to the
where clause of my sql statement. This has to be done before execution
of the sql statement. Here is a simplified example 

<xsp:page 
  language="java" 
  xmlns:xsp="http://apache.org/xsp" 
  xmlns:esql="http://apache.org/cocoon/SQL/v2">
  
  <content>
    <esql:connection>
      <esql:pool>mysql-pool</esql:pool>
      <esql:execute-query>
      
        <!-- how do I bring the filter criterias to sql statement below
-->
        <esql:query>
          select group_id, lastname, firstname, birthdate,  from
customer
          where group_id = [session variable group_id]
            and birthdate <= [session variable date]
          order by lastname, firstname
        </esql:query>
          
        <esql:results>
          <customer>
            <esql:row-results>
              <lastname><esql:get-string column="lastname"/></lastname>
              <firstname><esql:get-string
column="firstname"/></firstname>
            </esql:row-results>
          </customer>
        </esql:results>
        
      </esql:execute-query>
    </esql:connection>
  </content>
</xsp:page>


Thanks in advance
Urs


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org

Re: How do I use session parameters in a ESQL-Logicsheet

[Victoria Vitaver <vi...@tt.com.au>]

Urs Iwert wrote:
> Hi
> 
> I need data from a database using ESQL-Logicsheet. The sql statement
> must be filtered depending on the current session (user logon) and on
> session parameters. The session parameters are used, because there are
> quite a lot of parameters and all of them are used to query different
> data.

Dont forget that even though you are using esql, it is still an XSP page,

so I think you could quite easily declare  xmlns:xsp-session="http://apache.org/xsp/session/2.0" namespace like :

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

and do something like :

<esql:query>
 ... 
 WHERE    p.package_code = <esql:parameter type="string"><xsp-session:get-id/></esql:parameter>
 ....

</esql:query>


Inside your code. I have not tested this  .. but I think it should work.

Have a look at  http://cocoon.apache.org/2.1/userdocs/xsp/sessions.html for more detailed information.

HTH.

Vica

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org