You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-cs@ibatis.apache.org by Tony Wang <tw...@quatrogroup.com> on 2005/07/21 22:46:37 UTC
In-Line parameter to Oracle (.NET)
Hello Everyone,
I just installed the NPetshop and (without too much effort) making It
fully working with SQL Server. However, when I switch database to
Oracle, It becomes harder. I have Oracle9i database server running on
NT.
And have tried ODP.NET 9.2 and ODP.NET 10.4 got the same result.
The problem is on the Mapper:
<select id="GetProductListByCategory"
cacheModel="ProductList-cache" resultMap="ProductList"
parameterClass="string">
select
Product_Id,
Product_Name,
Product_Description,
P.Category_Id,
Category_Name,
Category_Description
from Products P, Categories C
where C.Category_ID= P.Category_ID and
P.Category_Id = #value#
</select>
The server log shows Ibatis send the following statement:
select
Product_Id,
Product_Name,
Product_Description,
P.Category_Id,
Category_Name,
Category_Description
from Products P, Categories C
where C.Category_ID= P.Category_ID and
P.Category_Id = :value
Which always return an empty dataset. I wonder Ibatis should directly
replace
#value# to the constant (like 'FISH') or sedning the value as a
parameter to :value?
Please help me out.
Thanks,
Follow is the provider.config section for the test.
<provider
name="oracle9.2"
description="Oracle, Oracle provider V9.2.0.401"
enabled="true"
assemblyName="Oracle.DataAccess, Version=9.2.0.401,
Culture=neutral, PublicKeyToken=89b483f429c47342"
connectionClass="Oracle.DataAccess.Client.OracleConnection"
commandClass="Oracle.DataAccess.Client.OracleCommand"
parameterClass="Oracle.DataAccess.Client.OracleParameter"
parameterDbTypeClass="Oracle.DataAccess.Client.OracleDbType"
parameterDbTypeProperty="OracleDbType"
dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter"
commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder"
usePositionalParameters = "false"
useParameterPrefixInSql = "true"
useParameterPrefixInParameter = "false"
parameterPrefix=":"
useDeriveParameters="false"
/>
<provider
name="oracle10.4"
enabled="false"
assemblyName="Oracle.DataAccess, Version=10.1.0.4.0,
Culture=neutral, PublicKeyToken=89b483f429c47342"
connectionClass="Oracle.DataAccess.Client.OracleConnection"
commandClass="Oracle.DataAccess.Client.OracleCommand"
parameterClass="Oracle.DataAccess.Client.OracleParameter"
parameterDbTypeClass="Oracle.DataAccess.Client.OracleDbType"
parameterDbTypeProperty="OracleDbType"
dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter"
commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder"
usePositionalParameters = "false"
useParameterPrefixInSql = "true"
useParameterPrefixInParameter = "false"
parameterPrefix=":"
useDeriveParameters="false"
/>
<database>
<provider name="oracle9.2"/>
<dataSource name="NPetshop" connectionString="Data
Source=QGPet;Persist Security Info=False;User
ID=NPETSHOP;Password=npetshop;min pool size=3;max pool size=50"/>
</database>
RE: In-Line parameter to Oracle (.NET)
Posted by Tony Wang <tw...@quatrogroup.com>.
After updating the in-line parameter to a parameterMap
that specifies the DbType and Size explicitly, the Oracle
returns correct records.
Not sure what type a parameter Oracle treated :value as.
It did not look like char, varchar(1) or varchar2(1).
Thanks anyway.
Tony
-----Original Message-----
From: Roberto R [mailto:roberto.rabe@gmail.com]
Sent: Saturday, July 23, 2005 1:23 PM
To: user-cs@ibatis.apache.org
Subject: Re: In-Line parameter to Oracle (.NET)
Do you have the logs for the parameter value that is being sent?
I actually haven't tried running NPetshop on Oracle. :-P
Roberto
On 7/21/05, Tony Wang <tw...@quatrogroup.com> wrote:
Hello Everyone,
I just installed the NPetshop and (without too much effort) making It
fully working with SQL Server. However, when I switch database to
Oracle, It becomes harder. I have Oracle9i database server running on
NT.
And have tried ODP.NET 9.2 and ODP.NET 10.4 got the same result.
The problem is on the Mapper:
<select id="GetProductListByCategory"
cacheModel="ProductList-cache" resultMap="ProductList"
parameterClass="string">
select
Product_Id,
Product_Name,
Product_Description,
P.Category_Id,
Category_Name,
Category_Description
from Products P, Categories C
where C.Category_ID= P.Category_ID and
P.Category_Id = #value#
</select>
The server log shows Ibatis send the following statement:
select
Product_Id,
Product_Name,
Product_Description,
P.Category_Id,
Category_Name,
Category_Description
from Products P, Categories C
where C.Category_ID= P.Category_ID and
P.Category_Id = :value
Which always return an empty dataset. I wonder Ibatis should directly
replace
#value# to the constant (like 'FISH') or sedning the value as a
parameter to :value?
Please help me out.
Thanks,
Follow is the provider.config section for the test.
<provider
name="oracle9.2"
description="Oracle, Oracle provider V9.2.0.401"
enabled="true"
assemblyName="Oracle.DataAccess, Version=9.2.0.401,
Culture=neutral, PublicKeyToken=89b483f429c47342"
connectionClass="Oracle.DataAccess.Client.OracleConnection"
commandClass="Oracle.DataAccess.Client.OracleCommand "
parameterClass="Oracle.DataAccess.Client.OracleParameter"
parameterDbTypeClass="Oracle.DataAccess.Client.OracleDbType"
parameterDbTypeProperty="OracleDbType"
dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter"
commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder"
usePositionalParameters = "false"
useParameterPrefixInSql = "true"
useParameterPrefixInParameter = "false"
parameterPrefix=":"
useDeriveParameters="false"
/>
<provider
name="oracle10.4"
enabled="false"
assemblyName="Oracle.DataAccess, Version=10.1.0.4.0,
Culture=neutral, PublicKeyToken=89b483f429c47342"
connectionClass="Oracle.DataAccess.Client.OracleConnection"
commandClass="Oracle.DataAccess.Client.OracleCommand "
parameterClass="Oracle.DataAccess.Client.OracleParameter"
parameterDbTypeClass="Oracle.DataAccess.Client.OracleDbType"
parameterDbTypeProperty="OracleDbType"
dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter"
commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder"
usePositionalParameters = "false"
useParameterPrefixInSql = "true"
useParameterPrefixInParameter = "false"
parameterPrefix=":"
useDeriveParameters="false"
/>
<database>
<provider name="oracle9.2"/>
<dataSource name="NPetshop" connectionString="Data
Source=QGPet;Persist Security Info=False;User
ID=NPETSHOP;Password=npetshop;min pool size=3;max pool size=50"/>
</database>
Re: In-Line parameter to Oracle (.NET)
Posted by Roberto R <ro...@gmail.com>.
Do you have the logs for the parameter value that is being sent?
I actually haven't tried running NPetshop on Oracle. :-P
Roberto
On 7/21/05, Tony Wang <tw...@quatrogroup.com> wrote:
>
> Hello Everyone,
>
> I just installed the NPetshop and (without too much effort) making It
> fully working with SQL Server. However, when I switch database to
> Oracle, It becomes harder. I have Oracle9i database server running on
> NT.
> And have tried ODP.NET <http://ODP.NET> 9.2 and ODP.NET <http://ODP.NET>
> 10.4 got the same result.
>
> The problem is on the Mapper:
> <select id="GetProductListByCategory"
> cacheModel="ProductList-cache" resultMap="ProductList"
> parameterClass="string">
> select
> Product_Id,
> Product_Name,
> Product_Description,
> P.Category_Id,
> Category_Name,
> Category_Description
> from Products P, Categories C
> where C.Category_ID= P.Category_ID and
> P.Category_Id = #value#
> </select>
>
> The server log shows Ibatis send the following statement:
> select
> Product_Id,
> Product_Name,
> Product_Description,
> P.Category_Id,
> Category_Name,
> Category_Description
> from Products P, Categories C
> where C.Category_ID= P.Category_ID and
> P.Category_Id = :value
>
> Which always return an empty dataset. I wonder Ibatis should directly
> replace
> #value# to the constant (like 'FISH') or sedning the value as a
> parameter to :value?
>
> Please help me out.
>
> Thanks,
>
> Follow is the provider.config section for the test.
> <provider
> name="oracle9.2"
> description="Oracle, Oracle provider V9.2.0.401"
> enabled="true"
> assemblyName="Oracle.DataAccess, Version=9.2.0.401,
> Culture=neutral, PublicKeyToken=89b483f429c47342"
>
> connectionClass="Oracle.DataAccess.Client.OracleConnection"
> commandClass="Oracle.DataAccess.Client.OracleCommand"
>
> parameterClass="Oracle.DataAccess.Client.OracleParameter"
>
> parameterDbTypeClass="Oracle.DataAccess.Client.OracleDbType"
> parameterDbTypeProperty="OracleDbType"
>
> dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter"
>
> commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder"
> usePositionalParameters = "false"
> useParameterPrefixInSql = "true"
> useParameterPrefixInParameter = "false"
> parameterPrefix=":"
> useDeriveParameters="false"
> />
> <provider
> name="oracle10.4"
> enabled="false"
> assemblyName="Oracle.DataAccess, Version=10.1.0.4.0,
> Culture=neutral, PublicKeyToken=89b483f429c47342"
>
> connectionClass="Oracle.DataAccess.Client.OracleConnection"
> commandClass="Oracle.DataAccess.Client.OracleCommand"
>
> parameterClass="Oracle.DataAccess.Client.OracleParameter"
>
> parameterDbTypeClass="Oracle.DataAccess.Client.OracleDbType"
> parameterDbTypeProperty="OracleDbType"
>
> dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter"
>
> commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder"
> usePositionalParameters = "false"
> useParameterPrefixInSql = "true"
> useParameterPrefixInParameter = "false"
> parameterPrefix=":"
> useDeriveParameters="false"
> />
>
>
>
> <database>
> <provider name="oracle9.2"/>
> <dataSource name="NPetshop" connectionString="Data
> Source=QGPet;Persist Security Info=False;User
> ID=NPETSHOP;Password=npetshop;min pool size=3;max pool size=50"/>
> </database>
>
>