Stored Procedure execution accepts Unicode strings while using TdCommand.CommandType StoredProcedure. The command expects the name of the stored procedure in the TdCommand.CommandText property. The Data Provider will issue the command by constructing a Unicode delimited identifier for the procedure name when the stored procedure name cannot be represented within the current session character set. See the SQL "Data Types and Literals" manual for more information on Unicode delimited identifiers.
For example, the following example illustrates calling a stored procedure with a stored procedure name that contains a character not supported in the session character set. The session character set is ASCII.
| C# |
Copy Code |
|---|---|
// The stored procedure definition contains a U+3021 Unicode // character and a column name with U+30A1 Unicode character // // DDL: CREATE PROCEDURE U&"sptest#3021" UEscape'#' // (in col2 INTEGER, out U&"col2#30a1" UEscape'#' INTEGER) // begin // set U&"col2#30a1"UEscape'#' = col2; // end; // TdConnectionStringBuilder blr = new TdConnectionStringBuilder(); blr.DataSource = "teradb01"; // The user id contains a Unicode escape sequence // representing a CJK character blr.UserId = "User\u3029"; blr.Password = "MyPassword"; blr.SessionCharacterSet = "ASCII"; TdConnection con = new TdConnection(blr.ToString()); con.Open(); TdCommand cmd = new TdCommand(); cmd.Connection = con; cmd.CommandType = CommandType.StoredProcedure; // The stored procedure name contains a Unicode escape sequence // representing a CJK character cmd.CommandText = "sptest\u3021"; TdParameter param1 = new TdParameter("param1", TdType.Integer); param1.Direction = ParameterDirection.Input; param1.Value = 10; cmd.Parameters.Add(param1); TdParameter param2 = new TdParameter("param2", TdType.Integer); param1.Direction = ParameterDirection.Output; cmd.Parameters.Add(param1); using (TdDataReader dr = cmd.ExecuteReader()) { // process the data from the output parameter Int32 result = (Int32)cmd.Parameters[1].Value; } con.Close(); | |
As mentioned in our Data Provider Developers Reference (TdCommand.CommandText property), if a dot (.) is included within the stored procedure name, the user must construct the TdCommand.CommandText surrounding the text with double quotes when required or compose the object name with Unicode delimited identifiers when required. The dot character is ambiguous and is sometimes used to separate the database name and the stored procedure name. The following example illustrates calling a stored procedure specifying a database name preceeding the stored procedure name.
| C# |
Copy Code |
|---|---|
// The stored procedure definition contains a U+3021 Unicode // character and a column name with U+30A1 Unicode character. // // DDL: CREATE PROCEDURE U&"sptest#3021" UEscape'#' // (in col2 INTEGER, out U&"col2#30a1" UEscape'#' INTEGER) // begin // set U&"col2#30a1"UEscape'#' = col2; // end; // TdConnectionStringBuilder blr = new TdConnectionStringBuilder(); blr.DataSource = "teradb01"; // The user id contains a Unicode escape sequence // representing a CJK character blr.UserId = "User\u3029"; blr.Password = "MyPassword"; blr.SessionCharacterSet = "ASCII"; TdConnection con = new TdConnection(blr.ToString()); con.Open(); TdCommand cmd = new TdCommand(); cmd.Connection = con; cmd.CommandType = CommandType.StoredProcedure; // Compose the stored procedure name as a Unicode delimited identifier cmd.CommandText = "tdnetdp.U&\"sptest#3021\"UEscape'#'; TdParameter param1 = new TdParameter("param1", TdType.Integer); param1.Direction = ParameterDirection.Input; param1.Value = 10; cmd.Parameters.Add(param1); TdParameter param2 = new TdParameter("param2", TdType.Integer); param1.Direction = ParameterDirection.Output; cmd.Parameters.Add(param1); using (TdDataReader dr = cmd.ExecuteReader()) { // process the data from the output parameter Int32 result = (Int32)cmd.Parameters[1].Value; } con.Close(); | |
Commands that are executed as command text have the option of representing object names that are not representable in the current session character set as Unicode delimited identifiers. Unicode delimited identifiers are fully supported in SQL text. However the metadata from Teradata will contain translation error characters when object names are not representable within the current session character set. This example selects data from a table name and column name that contain characters not representable in the ASCII session character set.
| C# |
Copy Code |
|---|---|
// Table Definition // CREATE TABLE U&"customers#60B1"UEscape'#' // (COL1 INTEGER, COL2 VARCHAR(30)) // PRIMARY INDEX(COL1) // TdConnectionStringBuilder blr = new TdConnectionStringBuilder(); blr.DataSource = "teradb01"; // The user id contains a Unicode escape sequence // representing a CJK character blr.UserId = "User\u3029"; blr.Password = "MyPassword"; blr.SessionCharacterSet = "ASCII"; TdConnection con = new TdConnection(blr.ToString()); con.Open(); // Represent the table name as a Unicode delimited identifier String tblName = "U&\"customers#60B1\"UEscape'#'"; String col1 = "custid"; // Represent the column name as a Unicode delimited identifier String col2 = "U&\"custname#60B3#60B4#60B5\"UEscape'#'"; String commandSelect = String.Format(CultureInfo.InvariantCulture, @" Select {0}, {1} from {2} ", col1, col2, tblName); String commandCreateTable = String.Format(CultureInfo.InvariantCulture, @"create set table {0} ({1} integer, {2} varchar(30))", tblName, col1, col2); String commandInsert = String.Format(CultureInfo.InvariantCulture, @"insert into {0} ( 1, 'john');",tblName) + String.Format(CultureInfo.InvariantCulture, @"insert into {0} ( 2, 'johnny');", tblName); TdCommand queryCmd = new TdCommand(commandCreateTable, con); queryCmd.ExecuteNonQuery(); queryCmd.CommandText = commandInsert; queryCmd.ExecuteNonQuery(); queryCmd.CommandText = commandSelect; using (TdDataReader dr = cmd.ExecuteReader()) { // number of rows returned Int32 result = (Int32)dr.RecordsReturned; } con.Close(); | |