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 Joe Chandler <jo...@autodesk.com> on 2005/01/27 22:32:29 UTC
Oracle 9.2 Problems
I am trying to use Oracle 9.2 as the provider in .NET c# and it seems
that the simple SQL Maps
Work just fine. When I try to pass a parameter the value does not get
substituted. For example,
<select id="MoveRequestSummaries"
resultMap="MoveRequestSummaryResult"
parameterClass="MoveRequest">
SELECT
ID,
STATUS,
REQUESTER_EMPLOYEE_ID,
CREATED_BY,
CREATED,
MOVE_DATE
FROM
MM_OWNER_V8.RQST_MOVE
WHERE
STATUS = #Status#
</select>
Always returns 0 rows or an empty result.
When I switch to the MS Oracle provider and run the same Query
Without changing code it returns the expected result.
Here is my provider for oracle:
<provider
name="oracle9.2"
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="Oracle.DataAccess.Client.OracleDbType"
dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter"
commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder"
usePositionalParameters = "false"
useParameterPrefixInSql = "true"
useParameterPrefixInParameter = "false"
parameterPrefix=":"
/>
Does anyone have any suggestions?
Thanks in advance,
Joe
RE: Oracle 9.2 Problems
Posted by Kit Cragin <ki...@entervalent.com>.
Have you tried the Microsoft supplied client, System.Data.OracleClient? It
worked well for me.
If there not a reason not to use it (e.g. some incompatibility), you should
give it a try. Here's some info that might be useful:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/ht
ml/ODPvsMSDP.asp.
Here's the XML for the providers.config entry:
<!-- Microsoft supplied Oracle provider -->
<provider
name="Oracle 9i"
enabled="true"
default="false"
assemblyName="System.Data.OracleClient, Version=1.0.5000.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089"
connectionClass="System.Data.OracleClient.OracleConnection"
commandClass="System.Data.OracleClient.OracleCommand"
parameterClass="System.Data.OracleClient.OracleParameter"
parameterDbTypeClass="System.Data.OracleClient.OracleType"
parameterDbTypeProperty="OracleType"
dataAdapterClass="System.Data.OracleClient.OracleDataAdapter"
commandBuilderClass="System.Data.OracleClient.OracleCommandBuilder"
usePositionalParameters = "false"
useParameterPrefixInSql = "true"
useParameterPrefixInParameter = "false"
parameterPrefix=":"
/>
- Kit
_____
From: Joe Chandler [mailto:joe.chandler@autodesk.com]
Sent: Thursday, January 27, 2005 3:32 PM
To: ibatis-user-cs@incubator.apache.org
Subject: Oracle 9.2 Problems
I am trying to use Oracle 9.2 as the provider in .NET c# and it seems that
the simple SQL Maps
Work just fine. When I try to pass a parameter the value does not get
substituted. For example,
<select id="MoveRequestSummaries"
resultMap="MoveRequestSummaryResult"
parameterClass="MoveRequest">
SELECT
ID,
STATUS,
REQUESTER_EMPLOYEE_ID,
CREATED_BY,
CREATED,
MOVE_DATE
FROM
MM_OWNER_V8.RQST_MOVE
WHERE
STATUS = #Status#
</select>
Always returns 0 rows or an empty result.
When I switch to the MS Oracle provider and run the same Query
Without changing code it returns the expected result.
Here is my provider for oracle:
<provider
name="oracle9.2"
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="Oracle.DataAccess.Client.OracleDbType"
dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter"
commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder"
usePositionalParameters = "false"
useParameterPrefixInSql = "true"
useParameterPrefixInParameter = "false"
parameterPrefix=":"
/>
Does anyone have any suggestions?
Thanks in advance,
Joe
RE: Oracle 9.2 Problems
Posted by roberto <ro...@theaegis.org>.
Using the ODP.NET provider takes a bit more work over using
System.Data.OracleClient. If "STATUS" is a string/VarChar2, you'll need to
create a parameterMap that explicitly defines the dbType and size. For
example:
<parameterMap id="MoveRequestSummariesParams">
<parameter property="Status" dbType="VarChar2" size="20"/>
</parameterMap>
Another example with other types:
<parameterMap id="MoveRequestInsertParams">
<parameter property="SomeIdKeyIntProperty" dbType="Int32"/>
<parameter property="Status" dbType="VarChar2" size="20"/>
<parameter property="SomeOtherStringProperty" dbType="VarChar2"
size="200" nullValue="ack"/>
<parameter property="SomeDecimalProperty" dbType="Decimal" precision="10"
scale="2"/>
<parameter property="SomeBlobProperty" dbType="Blob" size="4000"/>
</parameterMap>
Ints seem to work fine for inline parameters as #MyId:Int32#. It's
everything else .
You can also use the substitution format of STATUS = '$Status$' (not a great
option though).
Hope this helps!
Roberto
-----Original Message-----
From: Joe Chandler [mailto:joe.chandler@autodesk.com]
Sent: Thursday, January 27, 2005 4:32 PM
To: ibatis-user-cs@incubator.apache.org
Subject: Oracle 9.2 Problems
I am trying to use Oracle 9.2 as the provider in .NET c# and it seems that
the simple SQL Maps
Work just fine. When I try to pass a parameter the value does not get
substituted. For example,
<select id="MoveRequestSummaries"
resultMap="MoveRequestSummaryResult"
parameterClass="MoveRequest">
SELECT
ID,
STATUS,
REQUESTER_EMPLOYEE_ID,
CREATED_BY,
CREATED,
MOVE_DATE
FROM
MM_OWNER_V8.RQST_MOVE
WHERE
STATUS = #Status#
</select>
Always returns 0 rows or an empty result.
When I switch to the MS Oracle provider and run the same Query
Without changing code it returns the expected result.
Here is my provider for oracle:
<provider
name="oracle9.2"
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="Oracle.DataAccess.Client.OracleDbType"
dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter"
commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder"
usePositionalParameters = "false"
useParameterPrefixInSql = "true"
useParameterPrefixInParameter = "false"
parameterPrefix=":"
/>
Does anyone have any suggestions?
Thanks in advance,
Joe