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-----Do you have the logs for the parameter value that is being sent?
From: Roberto R [mailto:[EMAIL PROTECTED]
Sent: Saturday, July 23, 2005 1:23 PM
To: [email protected]
Subject: Re: In-Line parameter to Oracle (.NET)
I actually haven't tried running NPetshop on Oracle. :-P
Roberto
On 7/21/05, Tony Wang <[EMAIL PROTECTED]> 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>
