Re: Cocoon + postgreSQL

Mon, 05 Jan 2004 03:58:51 -0800

Thank you and Luca for your replies.

I'm getting closer to what I want now.

My problem now is processing the resultset from my stored procedure.

Here is what is output from SQLPlus when I execute it.


FIRST_NAME
-----------------------------
tests
Jeff
Simon

And here is my XSP listing, unfortunately I get no XML elements returned, am I missing something?

<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";
 xmlns:xsp-formval="http://apache.org/xsp/formvalidator/2.0";>

<page>
<esql:connection>
<esql:pool>abc</esql:pool>
<esql:execute-query>
<esql:call>
begin
getnames(<esql:parameter direction="out" type="oracle.jdbc.driver.OracleTypes.CURSOR"/>);
end;

        </esql:call>
         <esql:error-results>
        <paragraph><esql:get-message/></paragraph>
      </esql:error-results>

<esql:call-results>
    <esql:row-results>
                <esql:get-columns/>
    </esql:row-results>
</esql:call-results>

</esql:execute-query>
  </esql:connection>

 </page>
</xsp:page>



From: beyaRecords - The home Urban music <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: Cocoon + postgreSQL
Date: Sun, 4 Jan 2004 19:30:30 +0000

Tommy,
here is some code I have managed to put together both stored procedure, which takes 2 parameters, and an xsl page which calls the stored procedure.

1. xsl calling page
<page>
        <xsp:logic>
                String user_name = <xsp-request:get-parameter name="user" />;
                String user_password = <xsp-request:get-parameter name="pass" />;
        </xsp:logic>

<title>Dam this cocoon stuff is good</title>
<content>
<esql:connection>
<esql:pool>postgresql</esql:pool>
<esql:execute-query>
<esql:call>{call stproc_get_userobj(<esql:parameter direction="in" type="string"><xsp:expr>user_name</xsp:expr></esql:parameter>,
<esql:parameter direction="in" type="string"><xsp:expr>user_password</xsp:expr></esql:parameter>)}</ esql:call>
<esql:results>
<tickets>
<esql:row-results>
<ticket>
<user_id><esql:get-string column="user_id"/></user_id>
<first_name><esql:get-string column="first_name"/></first_name>
<last_name><esql:get-string column="last_name" /></last_name>
<address1><esql:get-string column="address1" /></address1>
<address2><esql:get-string column="address2" /></address2>
<address3><esql:get-string column="address3" /></address3>
<postcode><esql:get-string column="postcode" /></postcode>
<country><esql:get-string column="country" /></country>
<email><esql:get-string column="email" /></email>
<home_telephone><esql:get-string column="home_telephone" /></home_telephone>
<mobile_telephone><esql:get-string column="mobile_telephone" /></mobile_telephone>
<date_joined><esql:get-string column="date_joined" /></date_joined>
</ticket>
</esql:row-results>
</tickets>
</esql:results>
<esql:no-results>
<tickets>
<ticket>
<p>Sorry, no results!</p>
</ticket>
</tickets>
</esql:no-results>
</esql:execute-query>
</esql:connection>
</content>
</page>
</xsp:page>

2. Create a user type called users:

create type users as (user_id integer, first_name character varying, last_name character varying, address1 character varying, address2 character varying, address3 character varying, postcode character varying, country character varying, email character varying, home_telephone character varying, mobile_telephone character varying, date_joined timestamp without time zone);

So in this example, the user type mimics your table structure. Then in you proc, as shown below, you would call columns as you have defined in your user type

3. Strored procedure:

create or replace function stproc_get_userobj(text,text) returns setof users as
'
declare
user�ALIAS�for�$1;
pass�ALIAS�for�$2;
r�users%rowtype;
begin
��for�r�in�select�user_id,�first_name,�last_name,�address1,�address2,�ad dress3,�postcode,�country,�email,�home_telephone,�mobile_telephone,�date _joined�from�Usertbl�where�username�=�user�and�userpassword�=�pass�loop
������return�next�r;
��end�loop;
��return;
end
'
language 'plpgsql';

4. for further info : http://techdocs.postgresql.org/guides/SetReturningFunctions

regards

Uzo

Hi,

If you have managed to return a resultset back to Cocoon, I would be very glad to see your solution!

I have tried without much success to return a a resultset back from an Oracle stored procedure (both function & procedure). PostgreSQL is very similar to Oracle in many respects so there may be some common ground on how you invoke it from Cocoon.

I have used XSP, but will now try the SQLTransformer when I get back to work.

However, if you have a little sample code that you wouldn't mind making available that would be great!

Thanks in advance.


From: beyaRecords - The home Urban music <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: Cocoon + postgreSQL
Date: Sat, 3 Jan 2004 15:23:48 +0000

Hi J�Egen,
I was having problems constructing a stored procedure which would return a recordset in postgresql. I have managed to resolve the problem now.

thanks

Uzo
On 3 Jan 2004, at 14:56, J�Egen Haas wrote:

Hi,

is anybody using cocoon and postgresql to query a stored procedure?  I
am trying to return a resultset:
select * from tablename, and am having problems writing the  procedure.
What do you mean by "to query a stored procedure"? I don't understand this.
But I am using Cocoon and PostgreSQL, so maybe I can help you if you can tell
me exactly what you want to do with your query and its results.

CU
J�Egen


---------------------------------------------------------------------
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]


_________________________________________________________________
Send a funky Messenger Christmas card  http://www.msn.co.uk/christmascard


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


_________________________________________________________________
Sign-up for a FREE BT Broadband connection today! http://www.msn.co.uk/specials/btbroadband


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Reply via email to