cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tommy Smith" <jeff0...@hotmail.com>
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.


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 <uzo@beya-records.com>
>Reply-To: users@cocoon.apache.org
>To: users@cocoon.apache.org
>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
>userALIASfor$1;
>passALIASfor$2;
>rusers%rowtype;
>begin
>forrinselectuser_id,first_name,last_name,address1,address2,ad 
>dress3,postcode,country,email,home_telephone,mobile_telephone,date 
>_joinedfromUsertblwhereusername=useranduserpassword=passloop
>returnnextr;
>endloop;
>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 JEgen,
>>>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, JEgen 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
>>>>JEgen
>>>>
>>>>
>>>>---------------------------------------------------------------------
>>>>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
>>

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


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


Mime
View raw message