Return-Path: 
 <user-java-return-9809-apmail-ibatis-user-java-archive=ibatis.apache.org@ibatis.apache.org>
Delivered-To: apmail-ibatis-user-java-archive@www.apache.org
Received: (qmail 47197 invoked from network); 17 Nov 2006 14:40:50 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
  by minotaur.apache.org with SMTP; 17 Nov 2006 14:40:50 -0000
Received: (qmail 42501 invoked by uid 500); 17 Nov 2006 14:40:56 -0000
Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org
Received: (qmail 42486 invoked by uid 500); 17 Nov 2006 14:40:56 -0000
Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:user-java-help@ibatis.apache.org>
List-Unsubscribe: <mailto:user-java-unsubscribe@ibatis.apache.org>
List-Post: <mailto:user-java@ibatis.apache.org>
List-Id: <user-java.ibatis.apache.org>
Reply-To: user-java@ibatis.apache.org
Delivered-To: mailing list user-java@ibatis.apache.org
Received: (qmail 42475 invoked by uid 99); 17 Nov 2006 14:40:56 -0000
Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133)
    by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 17 Nov 2006 06:40:56 -0800
X-ASF-Spam-Status: No, hits=2.0 required=10.0
	tests=HTML_MESSAGE,SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (herse.apache.org: domain of jeffgbutler@gmail.com
 designates 66.249.92.170 as permitted sender)
Received: from [66.249.92.170] (HELO ug-out-1314.google.com) (66.249.92.170)
    by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 17 Nov 2006 06:40:44 -0800
Received: by ug-out-1314.google.com with SMTP id m2so661048ugc
        for <user-java@ibatis.apache.org>;
 Fri, 17 Nov 2006 06:40:22 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references;
        b=TwcyoffKj3c2xktlWnbNjGyarz4I+rHQS7Skc1LPLcH3C8Ro9X9tahJqNEIK3fhMDMHeFe1BjdwQSVN/8YM0KCvt2xiMpe/amx9tbvwO1fEW6Yjr8tf9warVCXwPx0TPiR7JJHI9YQC8ldFq6OoyHXv/a45rgzobta96xKweN8M=
Received: by 10.78.142.14 with SMTP id p14mr1945122hud.1163774421393;
        Fri, 17 Nov 2006 06:40:21 -0800 (PST)
Received: by 10.78.153.17 with HTTP; Fri, 17 Nov 2006 06:40:21 -0800 (PST)
Message-ID: <c593b8750611170640u79d51a40y1e7e7d81dec841a4@mail.gmail.com>
Date: Fri, 17 Nov 2006 08:40:21 -0600
From: "Jeff Butler" <jeffgbutler@gmail.com>
To: user-java@ibatis.apache.org
Subject: Re: select list: expression values
In-Reply-To: 
 <OF6B5E68FF.ABA1CD62-ONC1257229.004E6F69-C1257229.0050273D@ads.it>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_54903_28854828.1163774421358"
References: <7376882.post@talk.nabble.com>
	 <OF6B5E68FF.ABA1CD62-ONC1257229.004E6F69-C1257229.0050273D@ads.it>
X-Virus-Checked: Checked by ClamAV on apache.org

------=_Part_54903_28854828.1163774421358
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

You might need to do this:

select elemento - 1 as elemento, ...

sometimes databases rename these calculated columns.

If you run the query in the Squirrel SQL client you can see exactly what's
happening.

Jeff Butler


On 11/17/06, c.zecca@ads.it <c.zecca@ads.it> wrote:
>
>  Hi all
>
> The following SQL map and query
>
> <resultMap id="mapTipoRiga" class="
> it.finmatica.gpj.ec.tipidato.TipoCampoBean">
> <result property="id" column="TIPO_RIGA_ID" jdbcType="NUMBER"
> javaType="int"/>
> <result property="ordinale" column="ELEMENTO" jdbcType="NUMBER"
> javaType="int"/>
> <result property="nome" column="NOME" typeHandler="
> it.finmatica.gpj.aa.frontebd.NomeTypeHandlerCallback"/>
> <result property="idTipoOrizzontale" column="TIPO_ELEMENTO_ID"
> jdbcType="NUMBER" javaType="int"/>
> </resultMap>
>
> <select id="selectTipoRiga" resultMap="mapTipoRiga">
> select
> TIPO_RIGA_ID, *ELEMENTO - 1*, NOME, TIPO_ELEMENTO_ID
> from TIPI_DATO_RIGA
> where tipo_dato_id = #id:INTEGER#
> </select>
>
>
> raises the following exception
>
>
>    DEBUG [main] - {pstm-100001} PreparedStatement:    select
>     TIPO_RIGA_ID, ELEMENTO - 1, NOME, TIPO_ELEMENTO_ID   from TIPI_DATO_RIGA
>    where tipo_dato_id = ?
>    DEBUG [main] - {pstm-100001} Parameters: [21]
>    DEBUG [main] - {pstm-100001} Types: [java.lang.Integer]
>    *com.ibatis.common.jdbc.exception.NestedSQLException*:
>    --- The error occurred in it/finmatica/gpj/aa/frontebd/TipoRiga.xml.
>
>    --- The error occurred while applying a result map.
>    --- Check the GPJ.mapTipoRiga.
>    --- Check the result mapping for the 'ordinale' property.
>    --- Cause: *java.sql.SQLException*: Invalid colum name
>    Caused by: *java.sql.SQLException*: Invalid colum name
>    at
>    com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
>    (*GeneralStatement.java:185*)
>    at
>    com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList
>    (*GeneralStatement.java:123*)
>
>
>
> I have to load an 1-based index (stored in the ELEMENTO column) to
> transform it into a 0-based Java index. I want to avoid to write a type
> handler, thus i thought to use an expression as part of a select list<http://www.firstsql.com/tutor3.htm#op>
>
> select ELEMENTO - 1
>
> Please note that the query above is correct and is successfully run from
> PL/SQL developer.
>
> Replacing the "ELEMENTO - 1" expression with the "ELEMENTO" column "works
> out" the problem but loades 1-based indexes. :(
>
> Is there anything wrong?
> Shall I write a type handler? (I cannot change the ordinale property of
> the TipoCampoBean)
>
> Regards
>
>

------=_Part_54903_28854828.1163774421358
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<div>You might need to do this:</div>
<div>&nbsp;</div>
<div>select elemento - 1 as elemento, ...</div>
<div>&nbsp;</div>
<div>sometimes databases rename these calculated columns.</div>
<div>&nbsp;</div>
<div>If you run the query in the Squirrel SQL client you can see exactly what's happening.</div>
<div>&nbsp;</div>
<div>Jeff Butler<br><br>&nbsp;</div>
<div><span class="gmail_quote">On 11/17/06, <b class="gmail_sendername"><a href="mailto:c.zecca@ads.it">c.zecca@ads.it</a></b> &lt;<a href="mailto:c.zecca@ads.it">c.zecca@ads.it</a>&gt; wrote:</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">
<div>
<p>Hi all<br><br>The following SQL map and query<br><br><tt><font size="2">&lt;resultMap id=&quot;mapTipoRiga&quot; class=&quot;it.finmatica.gpj.ec.tipidato.TipoCampoBean&quot;&gt;</font></tt><br><tt><font size="2">&lt;result property=&quot;id&quot; column=&quot;TIPO_RIGA_ID&quot; jdbcType=&quot;NUMBER&quot; javaType=&quot;int&quot;/&gt;
</font></tt><br><tt><font size="2">&lt;result property=&quot;ordinale&quot; column=&quot;ELEMENTO&quot; jdbcType=&quot;NUMBER&quot; javaType=&quot;int&quot;/&gt;</font></tt><br><tt><font size="2">&lt;result property=&quot;nome&quot; column=&quot;NOME&quot; typeHandler=&quot;
it.finmatica.gpj.aa.frontebd.NomeTypeHandlerCallback&quot;/&gt;</font></tt><br><tt><font size="2">&lt;result property=&quot;idTipoOrizzontale&quot; column=&quot;TIPO_ELEMENTO_ID&quot; jdbcType=&quot;NUMBER&quot; javaType=&quot;int&quot;/&gt;
</font></tt><br><tt><font size="2">&lt;/resultMap&gt;</font></tt><br><tt><font size="2">&nbsp; &nbsp; </font></tt><br><tt><font size="2">&lt;select id=&quot;selectTipoRiga&quot; resultMap=&quot;mapTipoRiga&quot;&gt;</font></tt><br>
<tt><font size="2">select</font></tt><br><tt><font size="2">TIPO_RIGA_ID, </font></tt><tt><b><font size="2">ELEMENTO - 1</font></b></tt><tt><font size="2">, NOME, TIPO_ELEMENTO_ID</font></tt><br><tt><font size="2">from TIPI_DATO_RIGA
</font></tt><br><tt><font size="2">where tipo_dato_id = #id:INTEGER# </font></tt><br><tt><font size="2">&lt;/select&gt; &nbsp; &nbsp;</font></tt><br><br><br>raises the following exception<br><br>
<ul><tt><font size="2">DEBUG [main] - {pstm-100001} PreparedStatement: &nbsp; &nbsp;select &nbsp; &nbsp;TIPO_RIGA_ID, ELEMENTO - 1, NOME, TIPO_ELEMENTO_ID &nbsp; from TIPI_DATO_RIGA &nbsp; where tipo_dato_id = ? &nbsp; </font></tt><br><tt><font size="2">DEBUG [main] - {pstm-100001} Parameters: [21]
</font></tt><br><tt><font size="2">DEBUG [main] - {pstm-100001} Types: [java.lang.Integer]</font></tt><br><tt><u><font color="#000080" size="2">com.ibatis.common.jdbc.exception.NestedSQLException</font></u></tt><tt><font color="#ff0000" size="2">
: &nbsp; </font></tt><br><tt><font color="#ff0000" size="2">--- The error occurred in it/finmatica/gpj/aa/frontebd/TipoRiga.xml. &nbsp;</font></tt><br><tt><font color="#ff0000" size="2">--- The error occurred while applying a result map. &nbsp;
</font></tt><br><tt><font color="#ff0000" size="2">--- Check the GPJ.mapTipoRiga. &nbsp;</font></tt><br><tt><font color="#ff0000" size="2">--- Check the result mapping for the 'ordinale' property. &nbsp;</font></tt><br><tt><font color="#ff0000" size="2">
--- Cause: </font></tt><tt><u><font color="#000080" size="2">java.sql.SQLException</font></u></tt><tt><font color="#ff0000" size="2">: Invalid colum name</font></tt><br><tt><font color="#ff0000" size="2">Caused by: </font>
</tt><tt><u><font color="#000080" size="2">java.sql.SQLException</font></u></tt><tt><font color="#ff0000" size="2">: Invalid colum name</font></tt><br><tt><font color="#ff0000" size="2">at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
(</font></tt><tt><u><font color="#000080" size="2">GeneralStatement.java:185</font></u></tt><tt><font color="#ff0000" size="2">)</font></tt><br><tt><font color="#ff0000" size="2">at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList
(</font></tt><tt><u><font color="#000080" size="2">GeneralStatement.java:123</font></u></tt><tt><font color="#ff0000" size="2">)</font></tt></ul><br><br>I have to load an 1-based index (stored in the ELEMENTO column) to transform it into a 0-based Java index. I want to avoid to write a type handler, thus i thought to use an 
<a onclick="return top.js.OpenExtLink(window,event,this)" href="http://www.firstsql.com/tutor3.htm#op" target="_blank">expression as part of a select list</a><br><br>select ELEMENTO - 1<br><br>Please note that the query above is correct and is successfully run from PL/SQL developer.
<br><br>Replacing the &quot;ELEMENTO - 1&quot; expression with the &quot;ELEMENTO&quot; column &quot;works out&quot; the problem but loades 1-based indexes. :(<br><br>Is there anything wrong?<br>Shall I write a type handler? (I cannot change the 
<tt><font size="2">ordinale</font></tt> property of the <tt><font size="2">TipoCampoBean</font></tt>)<br><br>Regards 
<p></p></p></div></blockquote></div><br>

------=_Part_54903_28854828.1163774421358--