You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ibatis.apache.org by ro...@apache.org on 2005/05/12 03:51:17 UTC
svn commit: r169743 - in
/incubator/ibatis/trunk/cs/mapper/IBatisNet.DataMapper.Test:
Maps/Oracle/ODP/Category.xml Maps/Oracle/OracleClient/Category.xml
NUnit/SqlMapTests/Oracle/StatementGenerate.cs
Author: roberto
Date: Wed May 11 18:51:16 2005
New Revision: 169743
URL: http://svn.apache.org/viewcvs?rev=169743&view=rev
Log:
~Updated C# DataMapper NUnit tests for workarounds/resolutions to some issues
Modified:
incubator/ibatis/trunk/cs/mapper/IBatisNet.DataMapper.Test/Maps/Oracle/ODP/Category.xml
incubator/ibatis/trunk/cs/mapper/IBatisNet.DataMapper.Test/Maps/Oracle/OracleClient/Category.xml
incubator/ibatis/trunk/cs/mapper/IBatisNet.DataMapper.Test/NUnit/SqlMapTests/Oracle/StatementGenerate.cs
Modified: incubator/ibatis/trunk/cs/mapper/IBatisNet.DataMapper.Test/Maps/Oracle/ODP/Category.xml
URL: http://svn.apache.org/viewcvs/incubator/ibatis/trunk/cs/mapper/IBatisNet.DataMapper.Test/Maps/Oracle/ODP/Category.xml?rev=169743&r1=169742&r2=169743&view=diff
==============================================================================
--- incubator/ibatis/trunk/cs/mapper/IBatisNet.DataMapper.Test/Maps/Oracle/ODP/Category.xml (original)
+++ incubator/ibatis/trunk/cs/mapper/IBatisNet.DataMapper.Test/Maps/Oracle/ODP/Category.xml Wed May 11 18:51:16 2005
@@ -69,7 +69,6 @@
<update id="UpdateCategoryViaParameterMap" parameterMap="UpdateParam">
update Categories set
- Category_Id = ?,
Category_Name =?,
Category_Guid = ?
where
@@ -87,16 +86,12 @@
<insert id="InsertCategoryGenerate" parameterMap="insert-generate-params">
- <selectKey property="Id" type="post" resultClass="int">
+ <selectKey property="Id" type="pre" resultClass="int">
select s_categories.nextval as value from dual
</selectKey>
<generate table="Categories" />
</insert>
- <!--
- See OracleClient issues for UpdateCategoryGenerate
- and SelectByPKCategoryGenerate
- -->
<update id="UpdateCategoryGenerate" parameterMap="update-generate-params">
<generate table="Categories" by="Category_Id"/>
</update>
@@ -106,9 +101,11 @@
</delete>
<!--
- See OracleClient issues for UpdateCategoryGenerate
- and SelectByPKCategoryGenerate
- -->
+ Do not use a parameterMap with more parameters than the SQL statement needs as parameters or
+ pass in a parameter object that has more properties than the SQL statement needs as parameters
+ since they will be added to the command's parameters collection.
+ Oracle with OracleClient or ODP will throw an error unlike MSSQL and MySQL databases and providers.
+ -->
<select id="SelectByPKCategoryGenerate" resultClass="Category" parameterClass="Category" parameterMap="select-generate-params">
<generate table="Categories" by="Category_Id"/>
</select>
@@ -147,11 +144,29 @@
<parameter property="Id" direction="Output" column="p_Category_Id" dbType="Int32" />
</parameterMap>
+ <!--
+ Unlike MSSQL and MySQL, we need to have Id as a property since this
+ Oracle test uses a "pre" selectKey sequence that is used on insert
+ into Id property parameter.
+
+ MSSQL and MySQL use IDENTITY and AUTOGENERATE Ids that are generated after
+ the insert. Therefore, the Id property is not needed as a parameter for those databases.
+
+ Unfortunately, since the Id property is defined here for an INSERT statement, we cannot
+ extend this parameterMap for an UPDATE statement unlike MSSQL and MySQL because the Id
+ property is already positioned as the first parameter in the parameterMap
+ when it needs to be the last parameter in an UPDATE statement.
+ -->
<parameterMap id="InsertParam">
<parameter property="Id" column="Category_Id" dbType="Int32" />
<parameter property="Name" column="Category_Name" dbType="VarChar2" size="32"/>
<parameter property="GuidString" column="Category_Guid" dbType="VarChar2" size="36"/>
</parameterMap>
+
+ <parameterMap id="InsertParamWithoutId">
+ <parameter property="Name" column="Category_Name" dbType="VarChar2" size="32"/>
+ <parameter property="GuidString" column="Category_Guid" dbType="VarChar2" size="36"/>
+ </parameterMap>
<parameterMap id="insert-null-params">
<parameter property="Id" column="Category_Id" dbType="Int32" />
@@ -159,19 +174,36 @@
<parameter property="GuidString" column="Category_Guid" nullValue="00000000-0000-0000-0000-000000000000" dbType="VarChar2" size="36"/>
</parameterMap>
- <parameterMap id="UpdateParam" extends="InsertParam">
+ <parameterMap id="UpdateParam" extends="InsertParamWithoutId">
<parameter property="Id" column="Category_Id" dbType="Int32"/>
</parameterMap>
<!-- Used by generated statement -->
-
+ <!--
+ Unlike MSSQL and MySQL, we need to have Id as a property since this
+ Oracle test uses a "pre" selectKey sequence that is used on insert
+ into Id property parameter.
+
+ MSSQL and MySQL use IDENTITY and AUTOGENERATE Ids that are generated after
+ the insert. Therefore, the Id property is not needed as a parameter for those databases.
+
+ Unfortunately, since the Id property is defined here for an INSERT statement, we cannot
+ extend this parameterMap for an UPDATE statement unlike MSSQL and MySQL because the Id
+ property is already positioned as the first parameter in the parameterMap
+ when it needs to be the last parameter in an UPDATE statement.
+ -->
<parameterMap id="insert-generate-params">
<parameter property="Id" column="Category_Id" dbType="Int32" />
<parameter property="Name" column="Category_Name"/>
<parameter property="GuidString" column="Category_Guid" dbType="VarChar2"/>
</parameterMap>
+
+ <parameterMap id="insert-generate-params-without-id">
+ <parameter property="Name" column="Category_Name"/>
+ <parameter property="GuidString" column="Category_Guid" dbType="VarChar2"/>
+ </parameterMap>
- <parameterMap id="update-generate-params" extends="insert-generate-params">
+ <parameterMap id="update-generate-params" extends="insert-generate-params-without-id">
<parameter property="Id" column="Category_Id" dbType="Int32"/>
</parameterMap>
@@ -182,8 +214,6 @@
<parameterMap id="select-generate-params">
<parameter property="Id" column="Category_Id" dbType="Int32"/>
- <parameter property="Name" column="Category_Name"/>
- <parameter property="GuidString" column="Category_Guid" dbType="VarChar2"/>
</parameterMap>
</parameterMaps>
Modified: incubator/ibatis/trunk/cs/mapper/IBatisNet.DataMapper.Test/Maps/Oracle/OracleClient/Category.xml
URL: http://svn.apache.org/viewcvs/incubator/ibatis/trunk/cs/mapper/IBatisNet.DataMapper.Test/Maps/Oracle/OracleClient/Category.xml?rev=169743&r1=169742&r2=169743&view=diff
==============================================================================
--- incubator/ibatis/trunk/cs/mapper/IBatisNet.DataMapper.Test/Maps/Oracle/OracleClient/Category.xml (original)
+++ incubator/ibatis/trunk/cs/mapper/IBatisNet.DataMapper.Test/Maps/Oracle/OracleClient/Category.xml Wed May 11 18:51:16 2005
@@ -71,21 +71,6 @@
(?,?,?)
</insert>
- <!--
- Oracle.StatementTest.TestUpdateCategoryWithExtendParameterMap()
- Throws OracleException Invalid number
-
- Parameter order is not correct in CommandText:
- "update Categories set Category_Name = :Id, Category_Guid = :Name where Category_Id = :GuidString"
-
- OracleClient provider works better using an extended parameter map
- on insert instead of update since the order of the properties is important
- (see UpdateCategoryGenerate for related issue).
-
- WorkAround:
- Use a specific parameterMap for the UPDATE
- or use a parameterClass instead of an extended parameterMap
- -->
<update id="UpdateCategoryViaParameterMap" parameterMap="UpdateParam">
update Categories set
Category_Name = ?,
@@ -102,42 +87,12 @@
</procedure>
<insert id="InsertCategoryGenerate" parameterMap="insert-generate-params">
- <selectKey property="Id" type="post" resultClass="int">
+ <selectKey property="Id" type="pre" resultClass="int">
select s_categories.nextval as value from dual
</selectKey>
<generate table="Categories" />
</insert>
- <!--
- Throws OracleException SQL command not properly ended
- Since OracleClient properties list is coming back in a different order from MSSQL/MySql
-
- For example, if the SqlGenerator Update method is fixed to something like the following
- that does not rely on the column order:
-
- int setColumnCount = 0;
- // Create the set statement
- for (int i = 0; i < columnCount; i++)
- {
- ParameterProperty property = (ParameterProperty) statement.ParameterMap.PropertiesList[i];
-
- // Ignore key columns
- if (update.Generate.By.IndexOf(property.ColumnName) < 0)
- {
- if (setColumnCount > 1) {
- output.Append(",");
- }
- output.Append("\t" + property.ColumnName + " = ?");
- }
-
- ++setColumnCount;
- }
-
- Then the actual update statement is still messed up because of the different order when
- applying the parameter map!:
-
- UPDATE \tCategories SET \tCategory_Name = :Id,\tCategory_Guid = :Name WHERE \t Category_Id = :GuidString
- -->
<update id="UpdateCategoryGenerate" parameterMap="update-generate-params">
<generate table="Categories" by="Category_Id"/>
</update>
@@ -147,9 +102,10 @@
</delete>
<!--
- Throws OracleException illegal variable name/number
-
- See UpdateCategoryGenerate for the properties list order issue
+ Do not use a parameterMap with more parameters than the SQL statement needs as parameters or
+ pass in a parameter object that has more properties than the SQL statement needs as parameters
+ since they will be added to the command's parameters collection.
+ Oracle with OracleClient or ODP will throw an error unlike MSSQL and MySQL databases and providers.
-->
<select id="SelectByPKCategoryGenerate" resultClass="Category" parameterClass="Category" parameterMap="select-generate-params">
<generate table="Categories" by="Category_Id"/>
@@ -181,32 +137,67 @@
<parameter property="GuidString" column="p_Category_Guid" dbType="VarChar"/>
<parameter property="Id" column="p_Category_Id" dbType="Int32" type="Int"/>
</parameterMap>
-
+
+ <!--
+ Unlike MSSQL and MySQL, we need to have Id as a property since this
+ Oracle test uses a "pre" selectKey sequence that is used on insert
+ into Id property parameter.
+
+ MSSQL and MySQL use IDENTITY and AUTOGENERATE Ids that are generated after
+ the insert. Therefore, the Id property is not needed as a parameter for those databases.
+
+ Unfortunately, since the Id property is defined here for an INSERT statement, we cannot
+ extend this parameterMap for an UPDATE statement unlike MSSQL and MySQL because the Id
+ property is already positioned as the first parameter in the parameterMap
+ when it needs to be the last parameter in an UPDATE statement.
+ -->
<parameterMap id="InsertParam">
<parameter property="Id" column="Category_Id" dbType="Int32" />
<parameter property="Name" column="Category_Name"/>
<parameter property="GuidString" column="Category_Guid" dbType="VarChar"/>
</parameterMap>
-
+
+ <parameterMap id="InsertParamWithoutId">
+ <parameter property="Name" column="Category_Name"/>
+ <parameter property="GuidString" column="Category_Guid" dbType="VarChar"/>
+ </parameterMap>
+
<parameterMap id="insert-null-params">
<parameter property="Id" column="Category_Id" dbType="Int32" />
<parameter property="Name" column="Category_Name"/>
<parameter property="GuidString" column="Category_Guid" nullValue="00000000-0000-0000-0000-000000000000" dbType="VarChar"/>
</parameterMap>
- <parameterMap id="UpdateParam" extends="InsertParam">
+ <parameterMap id="UpdateParam" extends="InsertParamWithoutId">
<parameter property="Id" column="Category_Id" dbType="Int32"/>
</parameterMap>
<!-- Used by generated statement -->
-
+ <!--
+ Unlike MSSQL and MySQL, we need to have Id as a property since this
+ Oracle test uses a "pre" selectKey sequence that is used on insert
+ into Id property parameter.
+
+ MSSQL and MySQL use IDENTITY and AUTOGENERATE Ids that are generated after
+ the insert. Therefore, the Id property is not needed as a parameter for those databases.
+
+ Unfortunately, since the Id property is defined here for an INSERT statement, we cannot
+ extend this parameterMap for an UPDATE statement unlike MSSQL and MySQL because the Id
+ property is already positioned as the first parameter in the parameterMap
+ when it needs to be the last parameter in an UPDATE statement.
+ -->
<parameterMap id="insert-generate-params">
- <parameter property="Id" column="Category_Id" dbType="Int32" />
+ <parameter property="Id" column="Category_Id" dbType="Int32" />
+ <parameter property="Name" column="Category_Name"/>
+ <parameter property="GuidString" column="Category_Guid" dbType="VarChar"/>
+ </parameterMap>
+
+ <parameterMap id="insert-generate-params-without-id">
<parameter property="Name" column="Category_Name"/>
<parameter property="GuidString" column="Category_Guid" dbType="VarChar"/>
</parameterMap>
- <parameterMap id="update-generate-params" extends="insert-generate-params">
+ <parameterMap id="update-generate-params" extends="insert-generate-params-without-id">
<parameter property="Id" column="Category_Id" dbType="Int32"/>
</parameterMap>
@@ -217,8 +208,6 @@
<parameterMap id="select-generate-params">
<parameter property="Id" column="Category_Id" dbType="Int32"/>
- <parameter property="Name" column="Category_Name"/>
- <parameter property="GuidString" column="Category_Guid" dbType="VarChar"/>
</parameterMap>
</parameterMaps>
Modified: incubator/ibatis/trunk/cs/mapper/IBatisNet.DataMapper.Test/NUnit/SqlMapTests/Oracle/StatementGenerate.cs
URL: http://svn.apache.org/viewcvs/incubator/ibatis/trunk/cs/mapper/IBatisNet.DataMapper.Test/NUnit/SqlMapTests/Oracle/StatementGenerate.cs?rev=169743&r1=169742&r2=169743&view=diff
==============================================================================
--- incubator/ibatis/trunk/cs/mapper/IBatisNet.DataMapper.Test/NUnit/SqlMapTests/Oracle/StatementGenerate.cs (original)
+++ incubator/ibatis/trunk/cs/mapper/IBatisNet.DataMapper.Test/NUnit/SqlMapTests/Oracle/StatementGenerate.cs Wed May 11 18:51:16 2005
@@ -50,6 +50,13 @@
category.Id = (int)sqlMap.Insert("InsertCategoryGenerate", category);
Assert.AreEqual(1, category.Id);
+ // Workaround!
+ // Null out unneeded properties, otherwise those property values will be added
+ // as command parameters for the auto-generated SELECT query even if
+ // only 1 parameter for Id is needed.
+ category.Name = null;
+ category.Guid = Guid.Empty;
+
Category categoryRead = null;
categoryRead = (Category) sqlMap.QueryForObject("SelectByPKCategoryGenerate", category);