You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by fcbc <fc...@donetsk.fc.kiev.ua> on 2002/09/30 18:07:40 UTC
MSSQL Stored procedure & ESQL
I'm trying to get a result set from stored proc but it ends with error:
org.apache.cocoon.ProcessingException: Exception in ServerPagesGenerator.generate(): java.lang.RuntimeException: Error
getting clob data: null
the code snippet:
<esql:connection>
<esql:pool>sqlserver</esql:pool>
<esql:execute-query>
<esql:call>
{? = call Rp_OperDocs(<esql:parameter type="int" direction="in">
<xsp:expr>37521</xsp:expr>
</esql:parameter>)}
</esql:call>
<esql:call-results>
<esql:row-results>
<esql:get-string column="Kind"/>
</esql:row-results>
</esql:call-results>
</esql:execute-query>
</esql:connection>
What wrong? Help please.
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: MSSQL Stored procedure & ESQL
Posted by Christian Haul <ha...@dvs1.informatik.tu-darmstadt.de>.
On 01.Oct.2002 -- 12:03 PM, fcbc wrote:
> Hello Christian,
>
> ???????, 1 ??????? 2002 ?., you wrote:
>
> CH> Please see your MS docs whether your call will return a ResultSet
> CH> or not. The syntax depends on that, see Apache Cocoon docs for
> CH> details.
>
> I changed code in compliance with MS docs and JDBC driver syntax and
> now script fails with error:
> org.apache.cocoon.ProcessingException: Exception in ServerPagesGenerator.generate():
> java.lang.RuntimeException: Error getting clob data: ResultSet is closed
Please try to use <esql:results> instead of <esql:call-results>.
If that doesn't help, and your SP *does* use out parameters, you need
to declare them according to jdbc, using <esql:parameter
direction="out" type="whatever"/> tags in <esql:call>.
Please try to search the mailing list archives, I'm quite sure there
have been others that use MSSQL SPs with ESQL.
BTW most likely you don't need the "needs-query='true'".
Chris.
--
C h r i s t i a n H a u l
haul@informatik.tu-darmstadt.de
fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re[2]: MSSQL Stored procedure & ESQL
Posted by fcbc <fc...@donetsk.fc.kiev.ua>.
Hello Christian,
вторник, 1 октября 2002 г., you wrote:
CH> Please see your MS docs whether your call will return a ResultSet
CH> or not. The syntax depends on that, see Apache Cocoon docs for
CH> details.
CH> In addition, return parameters from CallableStatements are never
CH> named. They are *only* accessible by number.
CH> Chris.
CH> Please follow up summarizing your problem and which suggested solution
CH> / information worked for you when you consider your problem
CH> solved. Add "SUMMARY: " to the subject line. This will make FAQ
CH> generation and searching the list easier. In addition, it makes
CH> helping you more fun. Thank you.
Thanks for you reply.
I changed code in compliance with MS docs and JDBC driver syntax and
now script fails with error:
org.apache.cocoon.ProcessingException: Exception in ServerPagesGenerator.generate():
java.lang.RuntimeException: Error getting clob data: ResultSet is closed
the code snipped is:
<?xml version="1.0" encoding="ISO-8859-1"?>
<xsp:page language="java"
xmlns:xsp="http://apache.org/xsp"
xmlns:esql="http://apache.org/cocoon/SQL/v2">
<page>
<title>dbTest</title>
<content>
<esql:connection>
<esql:pool>sqlserver</esql:pool>
<esql:execute-query>
<esql:call needs-query="true">{call Rp_OperDocs(<esql:parameter direction="in" type="Int"><xsp:expr>37521</xsp:expr></esql:parameter>)}</esql:call>
<esql:call-results>
<esql:row-results>
<para><esql:get-string column="1"/></para>
</esql:row-results>
</esql:call-results>
<esql:no-results>
<para>No results</para>
</esql:no-results>
<esql:error-results>
<esql:get-message/><br/>
</esql:error-results>
</esql:execute-query>
</esql:connection>
</content>
</page>
</xsp:page>
The stored procedure is:
CREATE PROCEDURE Rp_OperDocs @DayDate int
AS
CREATE TABLE #OperDocs(
Kind INT,
FileNo INT,
CurrencyTag CHAR(4) ,
Code CHAR(32),
SourceCode CHAR(32),
TargetCode CHAR(32),
DebitCode CHAR(32),
CreditCode CHAR(32),
Amount MONEY ,
UserId INT ,
Invert TINYINT NULL
)
INSERT INTO #OperDocs (
Kind,
FileNo,
CurrencyTag,
Code,
SourceCode,
TargetCode,
DebitCode,
CreditCode,
Amount,
UserId,
Invert
) EXEC Rp_DayDocs @DayDate, 0
INSERT INTO #OperDocs (
Kind,
FileNo,
CurrencyTag,
Code,
SourceCode,
TargetCode,
DebitCode,
CreditCode,
Amount,
UserId,
Invert
) EXEC Rp_DayDocs @DayDate, 1
SELECT Kind, SUM(Amount) AS 'RepAmount' FROM #OperDocs GROUP BY Kind
DROP TABLE #OperDocs
in generated test_xsp.java I found this lines:
do {
if (_esql_query.hasResultSet()) {
_esql_query.getResultRows();
if (_esql_query.nextRow()) {
switch (_esql_query.getResultCount()) {
}
} else {
switch (_esql_query.getUpdateCountCount()) {
case 1:
default:
this.characters("\n\t\t\t\t\t\t\t");
this.contentHandler.startElement(
"",
"para",
"para",
xspAttr
);
xspAttr.clear();
this.characters("No results");
this.contentHandler.endElement(
"",
"para",
"para"
);
this.characters("\n\t\t\t\t\t\t");
break;
}
}
_esql_query.getResultSet().close(); // it seems that ResultSet
// is closed there
} else {
if (_esql_query.getUpdateCount() > 0) {
switch (_esql_query.getUpdateCountCount()) {
}
} else {
switch (_esql_query.getUpdateCountCount()) {
case 1:
default:
this.characters("\n\t\t\t\t\t\t\t");
this.contentHandler.startElement(
"",
"para",
"para",
xspAttr
);
xspAttr.clear();
this.characters("No results");
this.contentHandler.endElement(
"",
"para",
"para"
);
this.characters("\n\t\t\t\t\t\t");
break;
}
}
}
} while(_esql_connection.multipleResults() && _esql_query.getMoreResults());
// call results
// call results2
this.characters("\n\t\t\t\t\t\t\t");
this.contentHandler.startElement("http://apache.org/cocoon/SQL/v2",
"row-results",
"esql:row-results",
xspAttr
);
xspAttr.clear();
this.characters("\n\t\t\t\t\t\t\t\t");
this.contentHandler.startElement(
"",
"para",
"para",
xspAttr
);
xspAttr.clear();
XSPObjectHelper.xspExpr(contentHandler,
EsqlHelper.getAscii(_esql_query.getResultSet(),1) // ResultSet
// already
// closed
);
Why? Where my mistake? Please help.
Best regards,
fcbc mailto:fcbc@donetsk.fc.kiev.ua
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: MSSQL Stored procedure & ESQL
Posted by Christian Haul <ha...@dvs1.informatik.tu-darmstadt.de>.
On 30.Sep.2002 -- 06:07 PM, fcbc wrote:
> I'm trying to get a result set from stored proc but it ends with error:
> org.apache.cocoon.ProcessingException: Exception in ServerPagesGenerator.generate(): java.lang.RuntimeException: Error
> getting clob data: null
>
> the code snippet:
> <esql:connection>
> <esql:pool>sqlserver</esql:pool>
> <esql:execute-query>
> <esql:call>
> {? = call Rp_OperDocs(<esql:parameter type="int" direction="in">
> <xsp:expr>37521</xsp:expr>
> </esql:parameter>)}
> </esql:call>
> <esql:call-results>
> <esql:row-results>
> <esql:get-string column="Kind"/>
> </esql:row-results>
> </esql:call-results>
> </esql:execute-query>
> </esql:connection>
Please see your MS docs whether your call will return a ResultSet or
not. The syntax depends on that, see Apache Cocoon docs for details.
In addition, return parameters from CallableStatements are never
named. They are *only* accessible by number.
Chris.
Please follow up summarizing your problem and which suggested solution
/ information worked for you when you consider your problem
solved. Add "SUMMARY: " to the subject line. This will make FAQ
generation and searching the list easier. In addition, it makes
helping you more fun. Thank you.
--
C h r i s t i a n H a u l
haul@informatik.tu-darmstadt.de
fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>