[Mono-bugs] [Bug 79004][Blo] New - ORACLE: Double-byte characters passed as parameters get trimmed

[bugzilla-daemon at bugzilla.ximian.com]

Please do not reply to this email- if you want to comment on the bug, go to the
URL shown below and enter your comments there.

Changed by bpatton at perseus.com.

http://bugzilla.ximian.com/show_bug.cgi?id=79004

--- shadow/79004	2006-08-03 12:33:29.000000000 -0400
+++ shadow/79004.tmp.24528	2006-08-03 12:33:29.000000000 -0400
@@ -0,0 +1,176 @@
+Bug#: 79004
+Product: Mono: Class Libraries
+Version: 1.1
+OS: RHEL 2.1
+OS Details: 
+Status: NEW   
+Resolution: 
+Severity: 
+Priority: Blocker
+Component: Sys.Data
+AssignedTo: tsenganal at novell.com                            
+ReportedBy: bpatton at perseus.com               
+QAContact: mono-bugs at ximian.com
+TargetMilestone: ---
+URL: 
+Cc: 
+Summary: ORACLE: Double-byte characters passed as parameters get trimmed 
+
+Description of Problem:
+Using an OracleParameter with a string containing characters greater than a
+single byte causes the inserted value to be trimmed. It looks like the
+character counting is wrong and the string is trimmed by the number of
+extra bytes the double-byte characters take up. 
+
+The columns and paramters types are NCLOB and NVARCHAR2. NLS_LANG is set to
+American_America.UTF8 and all of the locale is set to UTF8.
+
+Steps to reproduce the problem:
+1. Compile and run the attached code below. 
+2. Open a viewer into the Oracle database (like Enterprise Manager) and
+view the MONO_TEST table.
+
+Actual Results:
+NUM_VALUE = 10
+Q2 = testing 日本 (Japan 
+TXT_VALUE = testing 日本 (Japan 
+
+Expected Results:
+NUM_VALUE = 10
+Q2 = testing 日本 (Japanese)
+TXT_VALUE = testing 日本 (Japanese) 
+
+How often does this happen? 
+Always
+
+Additional Information:
+
+using System;
+using System.Data;
+using System.Data.Common;
+using System.Data.OracleClient;
+ 
+namespace TestOracleClientAdapter {
+	public class Test {
+		static IDbConnection con;
+ 
+		public static void Main (string[] args) 
+		{
+			Console.WriteLine("Apapter Test Begin...");
+			string connectionString = "Server=prometheus.perseus.net;User
+ID=mono;Password=wombat81;";
+			using (con = new OracleConnection(connectionString)) {
+			    con.Open();
+			
+			    Setup();
+			    
+			    con.Close();
+			    con.Open();
+ 
+			    Insert();
+			}
+			Console.WriteLine("Done.");
+		}
+ 
+		static void Setup() 
+		{
+			using (IDbCommand cmd = con.CreateCommand()) {
+				cmd.CommandText = "DROP TABLE MONO_TEST";
+ 
+				try { 
+					cmd.ExecuteNonQuery(); 
+				} catch(OracleException e) { }
+ 
+				cmd.CommandText = 
+					"CREATE TABLE MONO_TEST (" +
+					" num_value int," +
+					" Q2 nclob," +
+					" txt_value nvarchar2(64))";
+				cmd.ExecuteNonQuery();
+			}
+		}
+ 
+		static void Insert() {
+		
+			Console.WriteLine("Insert...");
+			
+			IDbCommand cmd = GetDbCommand(con, 
+				"INSERT INTO MONO_TEST (num_value, Q2, txt_value) VALUES (?, ?, ?)");
+			//cmd.CommandText = "INSERT INTO MONO_TEST (num_value) VALUES (?);";
+			AddParameter(cmd, "num", 10);
+			AddParameter(cmd, "Q2", "testing 日本 (Japanese)");
+			AddParameter(cmd, "txt", "testing 日本 (Japanese)");
+Console.WriteLine("Command: " + cmd.CommandText);
+			cmd.ExecuteNonQuery();
+		}
+		
+		public static IDbCommand GetDbCommand(IDbConnection dbConnection, string
+sql) {
+			IDbCommand cmd = dbConnection.CreateCommand();
+
+			// For readability we break some of our SQL into multiple lines. 
+			// DB2 does not like this so remove the carriage return, line feeds.
+			string command = sql.Replace("\r\n", " ");
+
+			cmd.CommandText = command;
+			return cmd;
+		}
+		
+		/// <summary>
+		/// Add an int parameter to the db command.
+		/// </summary>
+		public static void AddParameter(IDbCommand cmd, string parameterName, int
+parameterValue) {
+			OracleParameter param = CreateSqlParameter(cmd, parameterName,
+OracleType.Int32);
+			param.Value = parameterValue;
+			cmd.Parameters.Add(param);
+		}
+
+		/// <summary>
+		/// Add a string paramter to the db command
+		/// </summary>
+		public static void AddParameter(IDbCommand cmd, string parameterName,
+string parameterValue) {
+			OracleParameter param = CreateSqlParameter(cmd, parameterName,
+OracleType.NVarChar);
+			param.Value = parameterValue;
+			cmd.Parameters.Add(param);
+		}
+
+		/// <summary>
+		/// Add a large string paramter to the db command
+		/// </summary>
+		public static void AddClobParameter(IDbCommand cmd, string parameterName,
+string parameterValue) {
+			OracleParameter param = CreateSqlParameter(cmd, parameterName,
+OracleType.NClob);
+
+			// ORA-01013: User requested cancel of current operation
+			// Oracle throws an error when attempting to insert an empty string into
+an NCLOB field. 
+			// As a workaround set the parameter to null value.
+			if (parameterValue == string.Empty) {
+				param.Value = null;
+			} else {
+				param.Value = parameterValue;
+			}
+			cmd.Parameters.Add(param);
+		}
+		
+		private static OracleParameter CreateSqlParameter(IDbCommand cmd, string
+parameterName, OracleType dbType) {
+			string marker = parameterName.StartsWith("@") ?
+parameterName.Replace('@', ':') : ":" + parameterName;
+			
+			string sql = cmd.CommandText;
+			int index = sql.IndexOf('?');
+			if (index != -1) {
+				cmd.CommandText = sql.Substring(0, index) + marker +
+sql.Substring(index + 1); 
+			}
+			Console.WriteLine("Parameter: " + marker);
+			return new OracleParameter(marker, dbType);
+		}
+	}
+ }