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 Maxime Levesque <ma...@mail.mcgill.ca> on 2005/04/21 18:27:46 UTC
problems with param mapping, and params becoming null on oracle
I wanted to sen a zipped project for illustrating the problem,
But the mailing list will accepts no zip, so I appended the info
At the end of the email.
1st problem :
I execute this :
SqlMapper m = Mapper.get();
m.BeginTransaction();
Usager u1 = new Usager();
u1.Nom = "Popo";
u1.NomUsager = "popo1212";
u1.Prenom = "zozo";
m.Insert("InsertUsager", u1);
m.CommitTransaction();
then I look in the DB, and all the string fields (varchar2 in the DB) are
null.
2nd problem :
the oracle type 'number', won't map to either C# int or long
(more details in Program.cs)
Here's my provider config :
<providers>
<clear/>
<provider
name="oracle10g"
enabled="true"
assemblyName="Oracle.DataAccess, Version=10.1.0.301,
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=":"
/>
</providers>
c# classes :
#region Using directives
using System;
using System.Collections.Generic;
using System.Text;
using IBatisNet.DataMapper;
using IBatisNet.DataMapper.Configuration;
using IBatisNet.Common.Utilities;
#endregion
namespace IBatisInsertTest {
class Program {
/**
Database : Oracle 10g (10.1.0.2)
Client dll : Oracle.DataAccess version 10.1.0.3.01
create sequence UsagersSeq;
create table Usagers (
id number,
nom_usager varchar2(16),
nom varchar2(32),
prenom varchar2(32),
mot_de_passe_hash varchar2(20)
);
*/
static void Main(string[] args) {
try {
SqlMapper m = Mapper.get();
m.BeginTransaction();
Usager u1 = new Usager();
u1.Nom = "Popo";
u1.NomUsager = "popo1212";
u1.Prenom = "zozo";
m.Insert("InsertUsager", u1);
/*
Problem # 1
all string properties are inserted as 'null' in the database ....
*/
m.CommitTransaction();
m.BeginTransaction();
/*
Problem# 2
System.InvalidCastException: Specified cast is not valid.
at Oracle.DataAccess.Client.OracleDataReader.GetInt64(Int32 i)
at
IBatisNet.DataMapper.TypesHandler.Int64TypeHandler.GetValueByName(ResultProp
erty
*/
Usager u = (Usager) m.QueryForObject("GetUsagers", "");
}
catch(Exception e) {
Console.WriteLine(e);
}
}
}
public class Usager {
public Usager() {}
private int _id;
private string _nomUsager;
private string _nom;
private string _prenom;
public int Id {
get {
return _id;
}
set {
_id = value;
}
}
public string NomUsager {
get {
return _nomUsager;
}
set {
_nomUsager = value;
}
}
public string Nom {
get {
return _nom;
}
set {
_nom = value;
}
}
public string Prenom {
get {
return _prenom;
}
set {
_prenom = value;
}
}
}
class Mapper {
private static volatile SqlMapper mapper;
protected static void Configure(object o) {
mapper = (SqlMapper) o;
}
protected static void InitMapper() {
ConfigureHandler handler = new ConfigureHandler(Configure);
mapper = SqlMapper.ConfigureAndWatch("../../SqlMap.config",
handler);
}
public static SqlMapper get() {
if(mapper != null)
return mapper;
lock(typeof(Mapper)) {
if(mapper != null)
return mapper;
InitMapper();
return mapper;
}
}
}
}
mapping files :
<?xml version="1.0" encoding="utf-8"?>
<sqlMapConfig xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="SqlMapConfig.xsd">
<settings>
<setting useStatementNamespaces="false"/>
<setting cacheModelsEnabled="false"/>
</settings>
<database>
<provider name="oracle10g"/>
<dataSource
name="Oracle"
connectionString="Data
Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dell3)(PORT=1
521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));User
Id=scott;Password=oracle;"/>
</database>
<sqlMaps>
<sqlMap resource="Usagers.xml"/>
</sqlMaps>
</sqlMapConfig>
<?xml version="1.0" encoding="UTF-8" ?>
<sqlMap namespace="LineItem"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="SqlMap.xsd">
<alias>
<typeAlias alias="Usager" type="IBatisInsertTest.Usager"/>
</alias>
<resultMaps>
<resultMap id="UsagerResult" class="Usager">
<result property="Id" column="id"/>
<result property="NomUsager" column="nom_usager"/>
<result property="Nom" column="nom"/>
<result property="Prenom" column="prenom"/>
</resultMap>
</resultMaps>
<statements>
<insert id="InsertUsager" parameterClass="Usager">
Insert Into Usagers
(id, nom_usager, nom, prenom)
Values
(UsagersSeq.nextVal, #NomUsager#, #Nom#, #Prenom#)
</insert>
<select id="GetUsagers" parameterClass="string"
resultMap="UsagerResult">
Select id, nom_usager, nom, prenom from Usagers
</select>
</statements>
</sqlMap>
RE: problems with param mapping, and params becoming null on oracle
Posted by Maxime Levesque <ma...@mail.mcgill.ca>.
Ok, using oracle's number(9) type solved the casting problem
(number(10) or bigger causes a type cast exception, which is reasoneable)
I still have the "strings become null",
interestingly number(9) (c# int types), and Date (c# DateTime)
get inserted correctly, so far, oracle varchar2 (c# strings)
are problematic (they are saved as null in the DB)...
-----Original Message-----
From: Ron Grabowski [mailto:rongrabowski@yahoo.com]
Sent: Thursday, April 21, 2005 1:27 PM
To: ibatis-user-cs@incubator.apache.org
Subject: Re: problems with param mapping, and params becoming null on oracle
> SqlMapper m = Mapper.get();
>
> m.BeginTransaction();
>
> Usager u1 = new Usager();
>
> u1.Nom = "Popo";
>
> u1.NomUsager = "popo1212";
>
> u1.Prenom = "zozo";
>
> m.Insert("InsertUsager", u1);
>
> m.CommitTransaction();
FYI, there is a good base class for IBatisNet if you want to start out
doing basic INSERT, SELECTS, etc. We use this class in all of our
projects. It keeps classes that extend it nice and tidy:
http://tinyurl.com/8fl5f
http://svn.apache.org/repos/asf/incubator/ibatis/trunk/cs/npetshop/NPetshop.
Persistence/MapperDao/BaseSqlMapDao.cs
Sample usage:
public Insert(Usager usager)
{
if (usager == null)
{
throw new ArgumentNullException("usager");
}
try
{
return (int)ExecuteInsert("InsertUsager", usager);
}
catch(IBatisNetException ex)
{
// TODO: throw application specific exception
}
}
Have you tried using the transactions ala C#'s using syntax as
described here:
http://tinyurl.com/b5wwg
http://svn.apache.org/repos/asf/incubator/ibatis/trunk/cs/mapper/IBatisNet.D
ataMapper/ChangeLog.txt
using ( IDalSession session = sqlMap.BeginTransaction() )
{
...db operations...
session.Complete(); // Commit
}
The NUnit test case that shows more examples:
http://tinyurl.com/8yvqu
http://svn.apache.org/repos/asf/incubator/ibatis/trunk/cs/mapper/IBatisNet.D
ataMapper.Test/NUnit/SqlMapTests/TransactionTest.cs
> the oracle type 'number', won't map to either C# int or long
According to this link:
http://tinyurl.com/9h222
http://issues.apache.org/jira/browse/IBATISNET-27#action_61688
.Net may be interpreting those as decimals.
The tests cases for Oracle databases:
http://tinyurl.com/9pveh
http://svn.apache.org/repos/asf/incubator/ibatis/trunk/cs/mapper/IBatisNet.D
ataAccess.Test/Scripts/Oracle/account-init.sql
use INTEGER or NUMBER(x) (where x is a number like 6, 10, 20, etc.) to
map database primary keys to System.Int32.
Here are the test cases for calling an insert statement for an Oracle
database and retrieving a primary key back as an int:
http://tinyurl.com/datft
http://svn.apache.org/repos/asf/incubator/ibatis/trunk/cs/mapper/IBatisNet.D
ataMapper.Test/NUnit/SqlMapTests/Oracle/StatementTest.cs
The version in source control has improved logging to show statements
being sent to the database. Perhaps you could build against that for a
while to verify the correct data is being sent to the database. If you
alreaddy have SVN installed, the following line will get the latest
version from source control and place it in a directory called
IBatisNet-svn:
svn co
http://svn.apache.org/repos/asf/incubator/ibatis/trunk/cs/mapper/
IBatisNet-svn
You should be able to double-click on the IBatisNet.sln file and build.
Please post if you any more questions. I've been using IBatisNet
without issue for the past several months against a Sql Server 2000
database, Access database (I think there are 2 people using Access
now!), and I've seen it work against an Oracle 9 database.
- Ron
Re: problems with param mapping, and params becoming null on oracle
Posted by Ron Grabowski <ro...@yahoo.com>.
> SqlMapper m = Mapper.get();
>
> m.BeginTransaction();
>
> Usager u1 = new Usager();
>
> u1.Nom = "Popo";
>
> u1.NomUsager = "popo1212";
>
> u1.Prenom = "zozo";
>
> m.Insert("InsertUsager", u1);
>
> m.CommitTransaction();
FYI, there is a good base class for IBatisNet if you want to start out
doing basic INSERT, SELECTS, etc. We use this class in all of our
projects. It keeps classes that extend it nice and tidy:
http://tinyurl.com/8fl5f
http://svn.apache.org/repos/asf/incubator/ibatis/trunk/cs/npetshop/NPetshop.Persistence/MapperDao/BaseSqlMapDao.cs
Sample usage:
public Insert(Usager usager)
{
if (usager == null)
{
throw new ArgumentNullException("usager");
}
try
{
return (int)ExecuteInsert("InsertUsager", usager);
}
catch(IBatisNetException ex)
{
// TODO: throw application specific exception
}
}
Have you tried using the transactions ala C#'s using syntax as
described here:
http://tinyurl.com/b5wwg
http://svn.apache.org/repos/asf/incubator/ibatis/trunk/cs/mapper/IBatisNet.DataMapper/ChangeLog.txt
using ( IDalSession session = sqlMap.BeginTransaction() )
{
...db operations...
session.Complete(); // Commit
}
The NUnit test case that shows more examples:
http://tinyurl.com/8yvqu
http://svn.apache.org/repos/asf/incubator/ibatis/trunk/cs/mapper/IBatisNet.DataMapper.Test/NUnit/SqlMapTests/TransactionTest.cs
> the oracle type 'number', won't map to either C# int or long
According to this link:
http://tinyurl.com/9h222
http://issues.apache.org/jira/browse/IBATISNET-27#action_61688
.Net may be interpreting those as decimals.
The tests cases for Oracle databases:
http://tinyurl.com/9pveh
http://svn.apache.org/repos/asf/incubator/ibatis/trunk/cs/mapper/IBatisNet.DataAccess.Test/Scripts/Oracle/account-init.sql
use INTEGER or NUMBER(x) (where x is a number like 6, 10, 20, etc.) to
map database primary keys to System.Int32.
Here are the test cases for calling an insert statement for an Oracle
database and retrieving a primary key back as an int:
http://tinyurl.com/datft
http://svn.apache.org/repos/asf/incubator/ibatis/trunk/cs/mapper/IBatisNet.DataMapper.Test/NUnit/SqlMapTests/Oracle/StatementTest.cs
The version in source control has improved logging to show statements
being sent to the database. Perhaps you could build against that for a
while to verify the correct data is being sent to the database. If you
alreaddy have SVN installed, the following line will get the latest
version from source control and place it in a directory called
IBatisNet-svn:
svn co
http://svn.apache.org/repos/asf/incubator/ibatis/trunk/cs/mapper/
IBatisNet-svn
You should be able to double-click on the IBatisNet.sln file and build.
Please post if you any more questions. I've been using IBatisNet
without issue for the past several months against a Sql Server 2000
database, Access database (I think there are 2 people using Access
now!), and I've seen it work against an Oracle 9 database.
- Ron