cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tommy Smith" <>
Subject Re: Cocoon + postgreSQL
Date Mon, 05 Jan 2004 10:56:44 GMT
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.


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

<xsp:page language="java"

          getnames(<esql:parameter direction="out" 





>From: beyaRecords - The home Urban music <>
>Subject: Re: Cocoon + postgreSQL
>Date: Sun, 4 Jan 2004 19:30:30 +0000
>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  
>1. xsl calling 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"  
>				<esql:parameter direction="in"  

>				<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"  
>								<mobile_telephone><esql:get-string column="mobile_telephone"  
>								<date_joined><esql:get-string column="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>
>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
>language 'plpgsql';
>4. for further info :  
>>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 
>>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 <>
>>>Subject: Re: Cocoon + postgreSQL
>>>Date: Sat, 3 Jan 2004 15:23:48 +0000
>>>Hi JEgen,
>>>I was having problems constructing a stored procedure which would  return 
>>>a recordset in postgresql. I have managed to resolve the  problem now.
>>>On 3 Jan 2004, at 14:56, JEgen Haas wrote:
>>>>>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 
>>>>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.
>>>>To unsubscribe, e-mail:
>>>>For additional commands, e-mail:
>>>To unsubscribe, e-mail:
>>>For additional commands, e-mail:
>>Send a funky Messenger Christmas card
>>To unsubscribe, e-mail:
>>For additional commands, e-mail:

Sign-up for a FREE BT Broadband connection today!

To unsubscribe, e-mail:
For additional commands, e-mail:

View raw message