Hi Rebecca,
this is my first submission to the cocoon user's mailing list and I hope
I do it right.
Please excuse the badly formatted XML-snippets below.
Here's my little example for you (code fragment from one of my
XSP-files):
[--- Snippet ----]
<esql:execute-query>
<esql:query>
select orggroup.* from orggroup
<xsp:expr>whereclause</xsp:expr> order by orggroup_name, orggroup_id
</esql:query>
<esql:results>
<user-groups>
<esql:row-results>
<group>
<esql:get-columns/>
<esql:execute-query>
<esql:query>
select * from
usergroup,benutzer where usergr_group=<esql:parameter
type="string"><esql:get-string ancestor="1"
column="orggroup_id"/></esql:parameter>
and benutzer.user_id =
usergroup.usergr_member
</esql:query>
<esql:results>
<members>
<esql:row-results>
<user>
<esql:get-columns/>
</user>
</esql:row-results>
</members>
</esql:results>
<esql:error-results>
<error>
<message><esql:get-message/></message>
</error>
</esql:error-results>
</esql:execute-query>
</group>
</esql:row-results>
</user-groups>
</esql:results>
<esql:no-results>
<user-groups><group></group></user-groups>
</esql:no-results>
<esql:error-results>
<error>
<message><esql:get-message/></message>
</error>
</esql:error-results>
</esql:execute-query>
[--- Snippet ----]
As you can see, I build a XML-Output from nested tables, here to get all
Members of Table BENUTZER (BENUTZER is german and means a table holding
users :-) ) assigned to a Group ORGGROUP by the Table usergroup.
To bind the column in the nested query to a column value of the outer
query, I use the following:
[--- Snippet ----]
where usergr_group=<esql:parameter type="string"><esql:get-string
ancestor="1" column="orggroup_id"/></esql:parameter>
[--- Snippet ----]
The magic eqsl tag is here the <esql:parameter
type="string">...</esql:parameter>. The second important thing is the
<esql:get-string ancestor="1" column="orggroup_id"/> to bind the
parameter at the column of the parent query (Mention the ancestor="1"
attribute).
Next, if you want to bind parameters for Stored Procs, look at the
follwing snippet:
[--- Snippet ----]
<esql:execute-query>
<esql:call>
begin
PACK_Account.setEnvironment(
<esql:parameter direction="in"
type="string"><xsp:expr>sessID</xsp:expr></esql:parameter>,
<esql:parameter direction="in"
type="string"><xsp-request:get-parameter name="lng"/></esql:parameter>
);
end;
</esql:call>
<esql:call-results>
<!-- Do something here -->
</esql:call-results>
[--- Snippet ----]
Here, the esql tag is
[--- Snippet ----]
<esql:parameter direction="in"
type="string"><xsp:expr>sessID</xsp:expr></esql:parameter>
[--- Snippet ----]
here I bind an object named "sessID" to the first argument of the
package PACK_Account.setEnvironment(?,?);
In general you use the <esql:parameter ...> tag anywhere in your
SQL-Statement where you normally would set a placeholder:
select * from usergroup where usergr_group=?
you do it in esql this way:
select * from usergroup where usergr_group=<esql:parameter
type="string"><esql:get-string ancestor="1"
column="orggroup_id"/></esql:parameter>
Hope that helps
kind regards from germany
Mike
> -----Urspr�ngliche Nachricht-----
> Von: R Hunt [mailto:[EMAIL PROTECTED]
> Gesendet: Donnerstag, 26. Februar 2004 16:53
> An: [EMAIL PROTECTED]
> Betreff: binding parameters in SELECT (SqlTransformer or ESQL)
>
>
> Thanks in advance to any hints.
> I'm exploring the best way to use Cocoon to select some data
> from an Oracle
> database. It's important that I be able to bind parameters.
> I can't find
> any documentation on how to do this in SqlTransformer or
> ESQL. Is it
> possible and I just haven't found the documentation? Has
> anyone else dealt
> with this and have any recommendations?
> Sorry this is a bit of a broad question; I appreciate pointers.
> Thanks,
> Rebecca
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
