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