cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From beyaRecords - The home Urban music <...@beya-records.com>
Subject Re: Cocoon + postgreSQL
Date Sun, 04 Jan 2004 19:30:30 GMT
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 <uzo@beya-records.com>
>> Reply-To: users@cocoon.apache.org
>> To: users@cocoon.apache.org
>> Subject: Re: Cocoon + postgreSQL
>> Date: Sat, 3 Jan 2004 15:23:48 +0000
>>
>> Hi Jürgen,
>> 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ürgen 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ürgen
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
>>> For additional commands, e-mail: users-help@cocoon.apache.org
>>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
>> For additional commands, e-mail: users-help@cocoon.apache.org
>>
>
> _________________________________________________________________
> Send a funky Messenger Christmas card  
> http://www.msn.co.uk/christmascard
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
> For additional commands, e-mail: users-help@cocoon.apache.org
>

Mime
View raw message