esql: no error message when db inaccessible

[Lars Huttar <la...@sil.org>]

Hi all,
I've got an application that uses esql in XSP pages for database access.
I use an <esql:error-results> block to collect any error messages and 
pass them to the user.
This works most of the time, e.g. when my XSP page has a wrong table 
name or there is a permissions error: the error is reported in the 
output of the XSP page and I'm able to show it to the user.
However, when the database is stopped, and I run an XSP page with a 
query, no error is reported; there are simply no rows returned. 
(Actually, sometimes the query just hangs and doesn't return; other 
times, it returns but there are no result rows.) So I don't see a way to 
distinguish between "your query ran successfully and the answer is an 
empty set" and "couldn't connect to the database". To the user, the 
difference is large. I've found it very mysterious to run a query and 
"discover" that all the data had disappeared.
Could this be due to some problem in the type of db connection I'm 
using? The datasource config in cocoon.xconf looks like:

  <jdbc name="GEM-Ethnologue-SQLServer-reader">
    <pool-controller min="5" max="10" oradb="true"/>
    
<dburl>jdbc:Microsoft:sqlserver://localhost:1433;DatabaseName=ethnolog;SelectMethod=Cursor;</dburl>
    <user>...</user>
    <password>...</password>
    <autocommit>true</autocommit>
  </jdbc>

Thanks...

Lars


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

Re: esql: no error message when db inaccessible

[Lars Huttar <la...@sil.org>]

Lars Huttar wrote:

> Hi all,
> I've got an application that uses esql in XSP pages for database access.
> I use an <esql:error-results> block to collect any error messages and 
> pass them to the user.
> This works most of the time, e.g. when my XSP page has a wrong table 
> name or there is a permissions error: the error is reported in the 
> output of the XSP page and I'm able to show it to the user.
> However, when the database is stopped, and I run an XSP page with a 
> query, no error is reported; there are simply no rows returned. 
> (Actually, sometimes the query just hangs and doesn't return; other 
> times, it returns but there are no result rows.) So I don't see a way 
> to distinguish between "your query ran successfully and the answer is 
> an empty set" and "couldn't connect to the database". To the user, the 
> difference is large. I've found it very mysterious to run a query and 
> "discover" that all the data had disappeared.
> Could this be due to some problem in the type of db connection I'm 
> using? The datasource config in cocoon.xconf looks like:
>
>  <jdbc name="GEM-Ethnologue-SQLServer-reader">
>    <pool-controller min="5" max="10" oradb="true"/>
>    
> <dburl>jdbc:Microsoft:sqlserver://localhost:1433;DatabaseName=ethnolog;SelectMethod=Cursor;</dburl> 
>
>    <user>...</user>
>    <password>...</password>
>    <autocommit>true</autocommit>
>  </jdbc>
>
> Thanks...
>
> Lars
>
Hmm, experimentally, it looks like I can check for the contents of the 
<esql:no-results> block. When the database is stopped, there are no rows 
returned but the <esql:no-results> block does not evaluate. So if this 
behavior is consistent, I can signal an error when there are no results 
but no <p>Sorry, no results!</p> (as in the following test code). 
However, I don't see this documented anywhere. It still smacks of a bug 
rather than a feature. By the way I'm running Cocoon 2.1.5.1 under 
Tomcat 4.1.
Any ideas on how this condition could be detected more cleanly/robustly?

FWIW, here's my sample test esql XSP page:

<?xml version="1.0" encoding="UTF-8"?>
<!-- an xsp page to test what happens when you try to connect to a 
stopped sql server -->
<xsp:page language="java"
  xmlns:xsp="http://apache.org/xsp"
  xmlns:xsp-request="http://apache.org/xsp/request/2.0"
  xmlns:esql="http://apache.org/cocoon/SQL/v2">
  <test>
    <esql:connection>
      <esql:pool>GEM-Ethnologue-SQLServer-reader</esql:pool>
      <esql:execute-query>
        <esql:query>SELECT
          Ethnologue_Continent_Id id, Ethnologue_Continent_Lb label FROM
          Ethnologue_Continent_base WHERE Ethnologue_Continent_Id = 
99999 ORDER BY Ethnologue_Continent_Lb
        </esql:query>
        <esql:results>
          <table>
            <esql:row-results>
              <tr>
                <td><esql:get-string column="id"/></td>
                <td><esql:get-string column="label"/></td>
              </tr>
            </esql:row-results>
          </table>
        </esql:results>
        <esql:error-results>An error occurred</esql:error-results>
        <esql:no-results>
          <p>Sorry, no results!</p>
        </esql:no-results>
      </esql:execute-query>
    </esql:connection>
  </test>
</xsp:page>

Thanks,
Lars


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