Return-Path: Delivered-To: apmail-xml-cocoon-users-archive@xml.apache.org Received: (qmail 56446 invoked by uid 500); 24 Apr 2001 17:44:32 -0000 Mailing-List: contact cocoon-users-help@xml.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: list-post: Reply-To: cocoon-users@xml.apache.org Delivered-To: mailing list cocoon-users@xml.apache.org Received: (qmail 56400 invoked from network); 24 Apr 2001 17:44:29 -0000 X-Sender: jp@pop3.dol.ie X-Mailer: QUALCOMM Windows Eudora Pro Version 4.0.1 Date: Tue, 24 Apr 2001 18:46:44 +0100 To: cocoon-users@xml.apache.org From: "JP Moresmau" Subject: RE: [C1] ESQL Stored procedures In-Reply-To: <75FA62F21F96D411B47800508BF9BDC641D000@lontmp01.emea.globa l.bankofamerica.com> Mime-Version: 1.0 Content-Type: multipart/alternative; types="text/plain,text/html"; boundary="=====================_33823625==_.ALT" Message-ID: X-Spam-Rating: h31.sny.collab.net 1.6.2 0/1000/N --=====================_33823625==_.ALT Content-Type: text/plain; charset="us-ascii" I might be wrong, but from looking at the esql taglib it doesn't seem to support multiple result sets, which is what your procedures return. Donald, am I right? Maybe this would solve your problem: (replace the template in the esql.xsl): while (_esql_query.resultset!=null){ while (_esql_query.resultset.next()) { if (_esql_connection.use_limit_clause == 0 && _esql_query.max_rows != -1 && _esql_query.position - _esql_query.skip_rows == _esql_query.max_rows-1) { _esql_query.position++; break; } _esql_query.position++; } _esql_query.resultset=_esql_query.resultset.getStatement().getResultSe t(); } Apologies, this hasn't been tested, my cocoon doesn't work any more (don't ask...) JP At 17:22 24/04/2001 +0100, you wrote: >Hi Donald / JP, > >Further investigation into SP's, I have noticed these behaviours. >I've tried to use both and and have >got the following >results. >With the I get a 0 back - this seems like a return code >and with (note:using get-columns) I get the the results >returned from the >first SP called from within my calling SP i.e the GBP results. Here is my >SP. > >drop procedure MJ_pdtPortfolioDeals >go >CREATE procedure MJ_pdtPortfolioDeals ( @PortfolioId INT ) >AS >BEGIN > > EXEC SP_FundingCurrency 'GBP' > EXEC SP_FundingCurrency 'JPY' > SELECT * FROM Currency where name='SEK' > >END >go >grant execute on MJ_pdtPortfolioDeals to public > >I have attached my .xml file. > > >Thanks >-Mukesh >ps I'm using IAS4.1.1 on WInNT4 / jConnect5.2 / Sybase12 > >-----Original Message----- >From: Jethwa, Mukesh >Sent: 24 April 2001 14:57 >To: cocoon-users@xml.apache.org >Subject: RE:[C1] ESQL Stored procedures > > >Hi donald / JP, > >I've tried some simple quering on SYBASE with both the [C1.8.2 >v1.54]esql.xsl and the [JP v1.68]esql.xsl. >Both seem to work in the same manner for the querying that i'm doing >currently. > >We have numerous Stored Procedures which contain calls to other Stored >Procedures. >Esql calls to these procedures return no data. However,calling SP's which >only contain simple selects >are fine. >Any ideas ? > >-Mukesh > > > > >-----Original Message----- >From: JP Moresmau [mailto:jp@dol.ie] >Sent: 09 April 2001 13:19 >To: cocoon-users@xml.apache.org >Subject: RE: esql:results > > >Mukesh, the current esql does not support stored procedures. What database >are you using? I have modifed the esql.xsl to support them, it hasn't been >approved yet, but I attach the new stylesheet and an example, if you want >to try it. The examples show in, out and return parameters, but you should >be able to return resultsets as well from a database that support returning >result set (not Oracle, since Oracle uses cursor variables, and there seems >to be problems with Sybase as well, a workaround has been found but not >tested...). Drop me a mail if you have any issues!! > >JP > >At 10:17 09/04/2001 +0100, you wrote: >>On the subject of esql, does anyone have any examples of how to call Stored >>Procedures ? >> >>Thanks >>Mukesh >> > > > >_____________________________________________________________________ >Notice to recipient: >This e-mail is meant for only the intended recipient of the transmission, >and may be a communication privileged by law. If you received this e-mail in >error, any review, use, dissemination, distribution, or copying of this >e-mail is strictly prohibited. > >When addressed to our clients any opinions or adviced contained in this >internet e-mail are subject to the terms and conditions expressed in any >applicable governing terms of business or client engagement letter issued by >Bank of America. > >Both Bank of America, N.A and Banc of America Securities Limited are >regulated by The Securities and Futures Authority. >_____________________________________________________________________ > > > >--------------------------------------------------------------------- >Please check that your question has not already been answered in the >FAQ before posting. > >To unsubscribe, e-mail: >For additional commands, e-mail: > > >_____________________________________________________________________ >Notice to recipient: >This e-mail is meant for only the intended recipient of the transmission, >and may be a communication privileged by law. If you received this e-mail in >error, any review, use, dissemination, distribution, or copying of this >e-mail is strictly prohibited. > >When addressed to our clients any opinions or adviced contained in this >internet e-mail are subject to the terms and conditions expressed in any >applicable governing terms of business or client engagement letter issued by >Bank of America. > >Both Bank of America, N.A and Banc of America Securities Limited are >regulated by The Securities and Futures Authority. >_____________________________________________________________________ > > > > > >--------------------------------------------------------------------- >Please check that your question has not already been answered in the >FAQ before posting. > >To unsubscribe, e-mail: >For additional commands, e-mail: JP Moresmau http://frenchstud.io --=====================_33823625==_.ALT Content-Type: text/html; charset="us-ascii" I might be wrong, but from looking at the esql taglib it doesn't seem to support multiple result sets, which is what your procedures return. Donald, am I right?

Maybe this would solve your problem: (replace the template in the esql.xsl):
      <xsl:template match="esql:results//esql:row-results">
            <xsp:logic>
while (_esql_query.resultset!=null){
    while (_esql_query.resultset.next()) {
      <xsl:apply-templates/>
      if (_esql_connection.use_limit_clause == 0 &amp;&amp; _esql_query.max_rows != -1 &amp;&amp; _esql_query.position - _esql_query.skip_rows == _esql_query.max_rows-1) {
        _esql_query.position++;
        break;
      }
      _esql_query.position++;
    }
      _esql_query.resultset=_esql_query.resultset.getStatement().getResultSet();
}
  </xsp:logic>
      </xsl:template>

Apologies, this hasn't been tested, my cocoon doesn't work any more (don't ask...)

JP

At 17:22 24/04/2001 +0100, you wrote:
>Hi Donald / JP,
>
>Further investigation into SP's, I have noticed these behaviours.
>I've tried to use both <esql:param-results> and <esql:row-results> and have
>got the following
>results.
>With the <esql:param-results> I get a 0 back - this seems like a return code
>and with <esql:row-results> (note:using get-columns) I get the the results
>returned from the
>first SP called from within my calling SP i.e the GBP results. Here is my
>SP.
>
>drop procedure MJ_pdtPortfolioDeals
>go
>CREATE procedure MJ_pdtPortfolioDeals ( @PortfolioId INT )
>AS
>BEGIN  
>
>  EXEC SP_FundingCurrency 'GBP'
>  EXEC SP_FundingCurrency 'JPY'
>  SELECT * FROM Currency where name='SEK'
>
>END 
>go
>grant execute on MJ_pdtPortfolioDeals to public
>
>I have attached my .xml file.
>
>
>Thanks
>-Mukesh 
>ps I'm using IAS4.1.1 on WInNT4 / jConnect5.2 / Sybase12
>
>-----Original Message-----
>From: Jethwa, Mukesh
>Sent: 24 April 2001 14:57
>To: cocoon-users@xml.apache.org
>Subject: RE:[C1] ESQL Stored procedures
>
>
>Hi donald / JP,
>
>I've tried some simple quering on SYBASE with both the [C1.8.2
>v1.54]esql.xsl and the [JP v1.68]esql.xsl.
>Both seem to work in the same manner for the querying that i'm doing
>currently.
>
>We have numerous Stored Procedures which contain calls to other Stored
>Procedures.
>Esql calls to these procedures return no data.  However,calling SP's which
>only contain simple selects
>are fine.
>Any ideas ?
>
>-Mukesh
>
>
>
>
>-----Original Message-----
>From: JP Moresmau [mailto:jp@dol.ie]
>Sent: 09 April 2001 13:19
>To: cocoon-users@xml.apache.org
>Subject: RE: esql:results
>
>
>Mukesh, the current esql does not support stored procedures. What database
>are you using? I have modifed the esql.xsl to support them, it hasn't been
>approved yet, but I attach the new stylesheet and an example, if you want
>to try it. The examples show in, out and return parameters, but you should
>be able to return resultsets as well from a database that support returning
>result set (not Oracle, since Oracle uses cursor variables, and there seems
>to be problems with Sybase as well, a workaround has been found but not
>tested...). Drop me a mail if you have any issues!!
>
>JP
>
>At 10:17 09/04/2001 +0100, you wrote:
>>On the subject of esql, does anyone have any examples of how to call Stored
>>Procedures ?
>>
>>Thanks
>>Mukesh
>>
>
>
>
>_____________________________________________________________________
>Notice to recipient:
>This e-mail is meant for only the intended recipient of the transmission,
>and may be a communication privileged by law. If you received this e-mail in
>error, any review, use, dissemination, distribution, or copying of this
>e-mail is strictly prohibited.
>
>When addressed to our clients any opinions or adviced contained in this
>internet e-mail are subject to the terms and conditions expressed in any
>applicable governing terms of business or client engagement letter issued by
>Bank of America.
>
>Both Bank of America, N.A and Banc of America Securities Limited are
>regulated by The Securities and Futures Authority.
>_____________________________________________________________________
>
>
>
>---------------------------------------------------------------------
>Please check that your question has not already been answered in the
>FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
>
>To unsubscribe, e-mail: <cocoon-users-unsubscribe@xml.apache.org>
>For additional commands, e-mail: <cocoon-users-help@xml.apache.org>
>
>
>_____________________________________________________________________
>Notice to recipient:
>This e-mail is meant for only the intended recipient of the transmission,
>and may be a communication privileged by law. If you received this e-mail in
>error, any review, use, dissemination, distribution, or copying of this
>e-mail is strictly prohibited.
>
>When addressed to our clients any opinions or adviced contained in this
>internet e-mail are subject to the terms and conditions expressed in any
>applicable governing terms of business or client engagement letter issued by
>Bank of America.
>
>Both Bank of America, N.A and Banc of America Securities Limited are
>regulated by The Securities and Futures Authority.
>_____________________________________________________________________
>
>
> 
>
>
>---------------------------------------------------------------------
>Please check that your question has not already been answered in the
>FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
>
>To unsubscribe, e-mail: <cocoon-users-unsubscribe@xml.apache.org>
>For additional commands, e-mail: <cocoon-users-help@xml.apache.org>

JP Moresmau
http://frenchstud.io --=====================_33823625==_.ALT--