You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Jeff Butler <je...@gmail.com> on 2007/10/30 18:00:05 UTC
Re: RV: Problem with Stored Procedure SQLServer + iBatis
I doubt this is an iBATIS issue - it must be a bug with your driver. JDBC
is officially case insensitive when retrieving columns from a result set by
name, see here (6th or 7th paragraph):
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html
You could probably code around this by using "columnIndex" instead of
"column" in your <result> element.
Jeff Butler
On 10/30/07, guillen.olmos.angel@servexternos.repsolypf.com <
guillen.olmos.angel@servexternos.repsolypf.com> wrote:
>
> Hi,
> I have find the problem, but it seems a bug, because I don't find
> documentation about it.
>
> This is my procedure, now I paste more code than yesterday:
>
> CREATE PROCEDURE
> [dbo].[proc_IMPUESTOSPAGADOS_SELECT_Devuelve_DirGen_UniNegPais_Paises]
> @chrIdioma char(2),
> @chrUsuario varchar(25),
> @intIdDireccionGeneral int,
> @intIdUniNegImpPag int
>
> /**** MORE ***/
>
> IF @chrIdioma = 'ES'
> BEGIN
> SET @NombrePais = 'chrNombreSp as NombrePais'
> END
> ELSE IF @chrIdioma = 'EN'
> BEGIN
> SET @NombrePais = 'chrNombreEn as NombrePais'
> END
>
>
> SET @sentencia ='
> SELECT ImpuestosPagados_DirGen_UniNegPais.intIdPais,' + @NombrePais
> + '
> FROM ImpuestosPagados_DirGen_UniNegPais
> INNER JOIN Paises_Paises
> ON ImpuestosPagados_DirGen_UniNegPais.intIdPais =
> Paises_Paises.intIdPais
> '
>
>
> /***** MORE ****/
>
> END
> GO
>
> This is my sqlMap:
>
> <resultMap id="paisesPorDGPorUNResult" class="paisesMulti">
> <result property="intIdPais" column="intIdPais"
> jdbcType="INTEGER" javaType="java.lang.String"/>
> <result property="nombrePais" column="NombrePais"/>
> </resultMap>
>
> <procedure id="getPaisesPorDGPorUN"
> parameterClass="unidadesNegocio" resultMap="paisesPorDGPorUNResult">
> {call
> dbo.proc_IMPUESTOSPAGADOS_SELECT_Devuelve_DirGen_UniNegPais_Paises
> (#strIdioma:VARCHAR#,#strUsuario:VARCHAR#,
> #intIdDireccionGeneral:INTEGER#,#intIdUniNegImpPag:INTEGER#)}
>
> </procedure>
>
> This procedure works fine using jdbc, but when I execute this procedure using
> ibatis I have this exception:
>
> Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in
> com/repsolypf/srf/adm/imptopag/dominio/DireccionesGenerales.xml.
> --- The error occurred while applying a result map.
> --- Check the DireccionesGenerales.paisesPorDGPorUNResult.
> --- Check the result mapping for the 'intIdPais' property.
> --- Cause: com.microsoft.sqlserver.jdbc.SQLServerException: The column
> name intIdPais is not valid.
>
>
> Changing the DB alias NombrePais to nombrePais, and changing too the
> column name NombrePais to nombrePais in the resultMap it works fine.
>
> ¿It's alowed for iBATIS to use column (or alias) names in database
> beginning by uppercase or not?
>
> I use iBATIS 2.3.0.677 whit SQLServer 2000, but using the SQLServer 2005
> driver.
>
> Thanks for your answer.
>
> Angel Guillén
>
>
> ------------------------------
> *Enviado el:* martes, 30 de octubre de 2007 10:50
> *Para:* user-java@ibatis.apache.org
> *Asunto:* RV: Problem with Stored Procedure SQLServer + iBatis
>
>
>
> Hello
>
> I have a problem mapping a stored procedure of SQL SERVER from iBATIS.
>
> This is my sqlMap:
>
> <resultMap id="paisesPorDGPorUNResult" class="paisesMulti">
> <result property="intIdPais" column="intIdPais"
> jdbcType="INTEGER" javaType="java.lang.String"/>
> <result property="nombrePais" column="NombrePais"/>
> </resultMap>
>
> <procedure id="getPaisesPorDGPorUN"
> parameterClass="unidadesNegocio" resultMap="paisesPorDGPorUNResult">
> {call
> dbo.proc_IMPUESTOSPAGADOS_SELECT_Devuelve_DirGen_UniNegPais_Paises
> (#strIdioma:VARCHAR#,#strUsuario:VARCHAR#,
> #intIdDireccionGeneral:INTEGER#,#intIdUniNegImpPag:INTEGER#)}
>
> </procedure>
>
> The DB Server is a SQLServer 2000 but we are using the SQLServer 2005 JBDC
> Driver (because it fix a bug that causes problems working with iBatis)
>
> When I execute this procedure i have this exception:
>
> Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in
> com/repsolypf/srf/adm/imptopag/dominio/DireccionesGenerales.xml.
> --- The error occurred while applying a result map.
> --- Check the DireccionesGenerales.paisesPorDGPorUNResult.
> --- Check the result mapping for the 'intIdPais' property.
> --- Cause: com.microsoft.sqlserver.jdbc.SQLServerException: The column
> name intIdPais is not valid.
>
> The column name is ok when I execute this procedure from SqlServer client,
> this is the SP:
>
> CREATE PROCEDURE
> [dbo].[proc_IMPUESTOSPAGADOS_SELECT_Devuelve_DirGen_UniNegPais_Paises]
> @chrIdioma char(2),
> @chrUsuario varchar(25),
> @intIdDireccionGeneral int,
> @intIdUniNegImpPag int
>
> /**** MORE ***/
>
> 'SELECT* intIdPais*,' +
> @NombrePais + '
> FROM
> ImpuestosPagados_DirGen_UniNegPais
> INNER JOIN Paises_Paises
> ON
> ImpuestosPagados_DirGen_UniNegPais.intIdPais = Paises_Paises.intIdPais'
>
> /***** MORE ****/
>
> END
> GO
>
> Do you know where is the problem? I can't see it.
>
> Greetings and thanks
>
> Angel Guillén
>
Re: RV: Problem with Stored Procedure SQLServer + iBatis
Posted by Larry Meadors <lm...@apache.org>.
Not to mention that the JDBC drivers from M$ are notoriously crummy.
If you had some time to test the JTDS driver, it might be worth a shot
- I've had great success with it and SQL 2K5.
Larry
On 10/30/07, Jeff Butler <je...@gmail.com> wrote:
> I doubt this is an iBATIS issue - it must be a bug with your driver. JDBC
> is officially case insensitive when retrieving columns from a result set by
> name, see here (6th or 7th paragraph):
>
> http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html
>
> You could probably code around this by using "columnIndex" instead of
> "column" in your <result> element.
>
> Jeff Butler
>
>
>
> On 10/30/07, guillen.olmos.angel@servexternos.repsolypf.com
> < guillen.olmos.angel@servexternos.repsolypf.com> wrote:
> >
> >
> > Hi,
> > I have find the problem, but it seems a bug, because I don't find
> documentation about it.
> >
> > This is my procedure, now I paste more code than yesterday:
> >
> >
> > CREATE PROCEDURE
> [dbo].[proc_IMPUESTOSPAGADOS_SELECT_Devuelve_DirGen_UniNegPais_Paises]
> > @chrIdioma char(2),
> > @chrUsuario varchar(25),
> > @intIdDireccionGeneral int,
> > @intIdUniNegImpPag int
> >
> > /**** MORE ***/
> >
> > IF @chrIdioma = 'ES'
> > BEGIN
> > SET @NombrePais = 'chrNombreSp as NombrePais'
> > END
> > ELSE IF @chrIdioma = 'EN'
> > BEGIN
> > SET @NombrePais = 'chrNombreEn as NombrePais'
> > END
> >
> >
> > SET @sentencia ='
> > SELECT
> ImpuestosPagados_DirGen_UniNegPais.intIdPais,' +
> @NombrePais + '
> > FROM ImpuestosPagados_DirGen_UniNegPais
> > INNER JOIN Paises_Paises
> > ON ImpuestosPagados_DirGen_UniNegPais.intIdPais =
> Paises_Paises.intIdPais
> > '
> >
> >
> > /***** MORE ****/
> >
> > END
> > GO
> >
> >
> > This is my sqlMap:
> >
> > <resultMap id="paisesPorDGPorUNResult" class="paisesMulti">
> > <result property="intIdPais" column="intIdPais"
> jdbcType="INTEGER" javaType=" java.lang.String"/>
> > <result property="nombrePais" column="NombrePais"/>
> > </resultMap>
> >
> > <procedure id="getPaisesPorDGPorUN"
> parameterClass="unidadesNegocio"
> resultMap="paisesPorDGPorUNResult">
> > {call
> dbo.proc_IMPUESTOSPAGADOS_SELECT_Devuelve_DirGen_UniNegPais_Paises
> (#strIdioma:VARCHAR#,#strUsuario:VARCHAR#,
>
> #intIdDireccionGeneral:INTEGER#,#intIdUniNegImpPag:INTEGER#)}
>
> >
> > </procedure>
> >
> > This procedure works fine using jdbc, but when I execute this procedure
> using ibatis I have this exception:
> >
> > Caused by:
> com.ibatis.common.jdbc.exception.NestedSQLException:
> > --- The error occurred in
> com/repsolypf/srf/adm/imptopag/dominio/DireccionesGenerales.xml.
>
> > --- The error occurred while applying a result map.
> > --- Check the
> DireccionesGenerales.paisesPorDGPorUNResult.
> > --- Check the result mapping for the 'intIdPais' property.
> > --- Cause:
> com.microsoft.sqlserver.jdbc.SQLServerException: The column
> name intIdPais is not valid.
> >
> >
> > Changing the DB alias NombrePais to nombrePais, and changing too the
> column name NombrePais to nombrePais in the resultMap it works fine.
> >
> > ¿It's alowed for iBATIS to use column (or alias) names in database
> beginning by uppercase or not?
> >
> > I use iBATIS 2.3.0.677 whit SQLServer 2000, but using the SQLServer 2005
> driver.
> >
> > Thanks for your answer.
> >
> > Angel Guillén
> >
> >
> > ________________________________
> Enviado el: martes, 30 de octubre de 2007 10:50
> > Para: user-java@ibatis.apache.org
> > Asunto: RV: Problem with Stored Procedure SQLServer + iBatis
> >
> >
> >
> >
> >
> > Hello
> >
> > I have a problem mapping a stored procedure of SQL SERVER from iBATIS.
> >
> > This is my sqlMap:
> >
> > <resultMap id="paisesPorDGPorUNResult" class="paisesMulti">
> > <result property="intIdPais" column="intIdPais"
> jdbcType="INTEGER" javaType=" java.lang.String"/>
> > <result property="nombrePais" column="NombrePais"/>
> > </resultMap>
> >
> > <procedure id="getPaisesPorDGPorUN"
> parameterClass="unidadesNegocio"
> resultMap="paisesPorDGPorUNResult">
> > {call
> dbo.proc_IMPUESTOSPAGADOS_SELECT_Devuelve_DirGen_UniNegPais_Paises
> (#strIdioma:VARCHAR#,#strUsuario:VARCHAR#,
>
> #intIdDireccionGeneral:INTEGER#,#intIdUniNegImpPag:INTEGER#)}
>
> >
> > </procedure>
> >
> > The DB Server is a SQLServer 2000 but we are using the SQLServer 2005 JBDC
> Driver (because it fix a bug that causes problems working with iBatis)
> >
> > When I execute this procedure i have this exception:
> >
> > Caused by:
> com.ibatis.common.jdbc.exception.NestedSQLException:
> > --- The error occurred in
> com/repsolypf/srf/adm/imptopag/dominio/DireccionesGenerales.xml.
>
> > --- The error occurred while applying a result map.
> > --- Check the
> DireccionesGenerales.paisesPorDGPorUNResult.
> > --- Check the result mapping for the 'intIdPais' property.
> > --- Cause:
> com.microsoft.sqlserver.jdbc.SQLServerException: The column
> name intIdPais is not valid.
> >
> > The column name is ok when I execute this procedure from SqlServer client,
> this is the SP:
> >
> > CREATE PROCEDURE
> [dbo].[proc_IMPUESTOSPAGADOS_SELECT_Devuelve_DirGen_UniNegPais_Paises]
> > @chrIdioma char(2),
> > @chrUsuario varchar(25),
> > @intIdDireccionGeneral int,
> > @intIdUniNegImpPag int
> >
> > /**** MORE ***/
> >
> > 'SELECT intIdPais,' + @NombrePais
> + '
> > FROM
> ImpuestosPagados_DirGen_UniNegPais
> > INNER JOIN Paises_Paises
> > ON
> ImpuestosPagados_DirGen_UniNegPais.intIdPais =
> Paises_Paises.intIdPais'
> >
> > /***** MORE ****/
> >
> > END
> > GO
> >
> > Do you know where is the problem? I can't see it.
> >
> > Greetings and thanks
> >
> > Angel Guillén
>
>