public void NamedParametersReuseTest() { string sql = "select * from test where int_field >= @lang and int_field <= @lang"; BdpCommand command = new BdpCommand(sql, this.Connection); command.Parameters.Add("@lang", BdpType.Int32).Value = 10; BdpDataReader reader = command.ExecuteReader(); int count = 0; int intValue = 0; while (reader.Read()) { if (count == 0) { intValue = reader.GetInt32(0); } count++; } Assert.AreEqual(1, count, "Invalid number of records fetched."); Assert.AreEqual(10, intValue, "Invalid record fetched."); reader.Close(); command.Close(); }
public void GetSchemaTableWithExpressionFieldTest() { BdpTransaction transaction = Connection.BeginTransaction(); BdpCommand command = new BdpCommand("select TEST.*, 0 AS VALOR from TEST", Connection, transaction); BdpDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly); DataTable schema = reader.GetSchemaTable(); Console.WriteLine(); Console.WriteLine("DataReader - GetSchemaTable Method- Test"); DataRow[] currRows = schema.Select(null, null, DataViewRowState.CurrentRows); foreach (DataColumn myCol in schema.Columns) { Console.Write("{0}\t\t", myCol.ColumnName); } Console.WriteLine(); foreach (DataRow myRow in currRows) { foreach (DataColumn myCol in schema.Columns) { Console.Write("{0}\t\t", myRow[myCol]); } Console.WriteLine(); } reader.Close(); transaction.Rollback(); command.Dispose(); }
public void TextBlobTest() { int id_value = this.GetId(); string text = "Borland Data Provider Clob Field test"; string selectText = "SELECT clob_field FROM TEST WHERE int_field = " + id_value.ToString(); string insertText = "INSERT INTO TEST (int_field, clob_field) values(?, ?)"; Console.WriteLine("\r\n\r\nBinary Blob Test"); Console.WriteLine("Executing insert command"); // Execute insert command BdpTransaction transaction = Connection.BeginTransaction(); BdpCommand insert = new BdpCommand(insertText, Connection, transaction); insert.Parameters.Add("@int_field", BdpType.Int32).Value = id_value; insert.Parameters.Add("@clob_field", BdpType.Blob, BdpType.stHMemo).Value = text; insert.ExecuteNonQuery(); transaction.Commit(); Console.WriteLine("Checking inserted values"); // Check that inserted values are correct BdpCommand select = new BdpCommand(selectText, Connection); string result = new String((char[])select.ExecuteScalar()); Assert.AreEqual(text, result); Console.WriteLine("Finishing test"); }
public void InvalidParameterFormat() { string sql = "update test set timestamp_field = ? where int_field = ?"; bool failed = false; BdpTransaction transaction = this.Connection.BeginTransaction(); try { BdpCommand command = new BdpCommand(sql, this.Connection, transaction); command.Parameters.Add("@timestamp", BdpType.DateTime).Value = 1; command.Parameters.Add("@integer", BdpType.Int32).Value = 1; command.ExecuteNonQuery(); command.Close(); transaction.Commit(); } catch { failed = true; transaction.Rollback(); } Assert.IsTrue(failed, "Bad parameter not detected"); }
public void AbortTransaction() { StringBuilder b1 = new StringBuilder(); b1.AppendFormat("ALTER TABLE \"{0}\" drop \"INT_FIELD\"", "TEST"); BdpTransaction transaction = null; BdpCommand command = null; try { transaction = this.Connection.BeginTransaction(); command = new BdpCommand(b1.ToString(), this.Connection, transaction); command.ExecuteNonQuery(); transaction.Commit(); transaction = null; } catch (Exception) { transaction.Rollback(); transaction = null; } finally { if (command != null) { command.Dispose(); } } }
public void GetValuesTest() { BdpTransaction transaction = Connection.BeginTransaction(); BdpCommand command = new BdpCommand("select * from TEST", Connection, transaction); Console.WriteLine(); Console.WriteLine("DataReader - Read Method - Test"); IDataReader reader = command.ExecuteReader(); while (reader.Read()) { object[] values = new object[reader.FieldCount]; reader.GetValues(values); for (int i = 0; i < values.Length; i++) { Console.Write(values[i] + "\t"); } Console.WriteLine(); } reader.Close(); transaction.Rollback(); command.Dispose(); }
public void IndexerByIndexTest() { BdpTransaction transaction = Connection.BeginTransaction(); BdpCommand command = new BdpCommand("select * from TEST", Connection, transaction); Console.WriteLine(); Console.WriteLine("DataReader - Read Method - Test"); IDataReader reader = command.ExecuteReader(); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { Console.Write(reader[i] + "\t"); } Console.WriteLine(); } reader.Close(); transaction.Rollback(); command.Dispose(); }
public void ExecuteReaderTest() { BdpCommand command = Connection.CreateCommand(); command.CommandText = "select * from TEST"; int rows = 0; // Holds number of rows; object rowNum; // Holds row number fetch from DB BdpDataReader reader = command.ExecuteReader(); while (reader.Read()) { // Check INT field for proper value. rowNum = reader["INT_FIELD"]; Assert.IsTrue((int)rowNum == rows, "Invalid row number"); rows++; } reader.Close(); Assert.IsTrue(Connection.State == ConnectionState.Open, "Connection is not open"); command.Close(); }
public void GetValuesTest() { BdpTransaction transaction = Connection.BeginTransaction(); BdpCommand command = new BdpCommand("select * from TEST", Connection, transaction); Console.WriteLine(); Console.WriteLine("DataReader - Read Method - Test"); IDataReader reader = command.ExecuteReader(); while (reader.Read()) { object[] values = new object[reader.FieldCount]; reader.GetValues(values); for (int i = 0; i < values.Length; i++) { Console.Write(values[i] + "\t"); } Console.WriteLine(); } reader.Close(); transaction.Rollback(); command.Dispose(); }
public void DeleteTest() { string sql = "select * from TEST where int_field = ?"; BdpTransaction transaction = this.Connection.BeginTransaction(); BdpCommand command = new BdpCommand(sql, Connection, transaction); BdpDataAdapter adapter = new BdpDataAdapter(command); adapter.SelectCommand.Parameters.Add("@int_field", BdpType.Int32).Value = 10; BdpCommandBuilder builder = new BdpCommandBuilder(adapter); DataSet ds = new DataSet(); adapter.Fill(ds, "TEST"); Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count"); ds.Tables["TEST"].Rows[0].Delete(); adapter.Update(ds, "TEST"); adapter.Dispose(); builder.Dispose(); command.Dispose(); transaction.Commit(); }
public void AbortTransaction() { StringBuilder b1 = new StringBuilder(); b1.AppendFormat("ALTER TABLE \"{0}\" drop \"INT_FIELD\"", "TEST"); BdpTransaction transaction = null; BdpCommand command = null; try { transaction = this.Connection.BeginTransaction(); command = new BdpCommand(b1.ToString(), this.Connection, transaction); command.ExecuteNonQuery(); transaction.Commit(); transaction = null; } catch (Exception) { transaction.Rollback(); transaction = null; } finally { if (command != null) { command.Dispose(); } } }
public void UpdatedArrayFieldTest() { Console.WriteLine("\r\nUpdate IARRAY field with implicit transaction."); int[] values = new int[4]; values[0] = 10; values[1] = 20; values[2] = 30; values[3] = 40; // Add IARRAY_FIELD column BdpCommand command = new BdpCommand("alter table TEST add IARRAY_FIELD INTEGER[4]", Connection); command.ExecuteNonQuery(); command.Close(); // Now test the update of an array command = new BdpCommand("update TEST set iarray_field = ? where int_field = ?", Connection); command.Parameters.Add("@iarray_field", BdpType.Array).Value = values; command.Parameters.Add("@int_field", BdpType.Int32).Value = 1; int i = command.ExecuteNonQuery(); Assert.AreEqual(i, 1, "Array field update with implicit transaction failed"); // Force the implicit transaction to be committed command.Dispose(); }
public void ExecuteScalarTest() { BdpCommand command = new BdpCommand("select sum(int_field) from TEST", Connection); object actual = command.ExecuteScalar(); Console.WriteLine("\r\nExecuteScalar with implicit transaction: {0}", actual); Assert.AreEqual(4950, actual, "Wrong sum returned."); command.Dispose(); }
private static void CreateTables(string connectionString) { BdpConnection connection = new BdpConnection(connectionString); connection.Open(); StringBuilder commandText = new StringBuilder(); commandText.Append("DROP TABLE TEST"); BdpCommand command = null; try { command = new BdpCommand(commandText.ToString(), connection); command.ExecuteNonQuery(); command.Dispose(); } catch { } commandText = new StringBuilder(); // Table for general purpouse tests commandText.Append("CREATE TABLE TEST ("); commandText.Append("INT_FIELD INTEGER DEFAULT 0 NOT NULL PRIMARY KEY,"); commandText.Append("CHAR_FIELD CHAR(30),"); commandText.Append("VARCHAR_FIELD VARCHAR(100),"); commandText.Append("BIGINT_FIELD BIGINT,"); commandText.Append("SMALLINT_FIELD SMALLINT,"); commandText.Append("DOUBLE_FIELD DOUBLE PRECISION,"); commandText.Append("FLOAT_FIELD FLOAT,"); commandText.Append("NUMERIC_FIELD NUMERIC(15,2),"); commandText.Append("DECIMAL_FIELD DECIMAL(15,2),"); commandText.Append("DATE_FIELD DATE,"); commandText.Append("TIME_FIELD TIME,"); commandText.Append("TIMESTAMP_FIELD TIMESTAMP,"); commandText.Append("CLOB_FIELD BLOB SUB_TYPE 1 SEGMENT SIZE 80,"); commandText.Append("BLOB_FIELD BLOB SUB_TYPE 0 SEGMENT SIZE 80,"); commandText.Append("EXPR_FIELD COMPUTED BY (smallint_field * 1000));"); try { command = new BdpCommand(commandText.ToString(), connection); command.ExecuteNonQuery(); command.Dispose(); } catch { } connection.Close(); }
public void ExecuteReaderWithBehaviorTest() { BdpCommand command = new BdpCommand("select * from TEST", Connection); BdpDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); reader.Close(); Assert.IsTrue(Connection.State == ConnectionState.Closed, "Connection was not closed"); command.Close(); // Check to make sure no exceptions are raised }
public void RecordAffectedTest() { string sql = "insert into test (int_field) values (100000);"; BdpCommand command = new BdpCommand(sql, this.Connection); BdpDataReader reader = command.ExecuteReader(); reader.Close(); Assert.AreEqual(1, reader.RecordsAffected, "RecordsAffected value is incorrect"); }
public void ExecuteReaderWithBehaviorTest() { BdpCommand command = new BdpCommand("select * from TEST", Connection); BdpDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); reader.Close(); Assert.IsTrue(Connection.State == ConnectionState.Closed, "Connection was not closed"); command.Close(); // Check to make sure no exceptions are raised }
public void ExecuteScalarTest() { BdpCommand command = Connection.CreateCommand(); command.CommandText = "select CHAR_FIELD from TEST where INT_FIELD = ?"; command.Parameters.Add("@INT_FIELD", 2); string charFieldValue = command.ExecuteScalar().ToString(); Console.WriteLine("Scalar value: {0}", charFieldValue); Assert.AreEqual(charFieldValue, "IRow 2", "Wrong value."); command.Close(); }
public void DataAdapterFillTest() { BdpCommand command = new BdpCommand("select * from TEST where DATE_FIELD = ?", Connection); BdpDataAdapter adapter = new BdpDataAdapter(command); // adapter.SelectCommand.Parameters.Add("@DATE_FIELD", BdpType.Date, 4, "DATE_FIELD").Value = new DateTime(2003, 1, 5); adapter.SelectCommand.Parameters.Add("@DATE_FIELD", BdpType.Date, 4, "DATE_FIELD").Value = DateTime.Today; BdpCommandBuilder builder = new BdpCommandBuilder(adapter); DataSet ds = new DataSet(); adapter.Fill(ds, "TEST"); Console.WriteLine(); Console.WriteLine("Implicit transactions - DataAdapter Fill Method - Test"); int tables = 0; int rows = 0; foreach (DataTable table in ds.Tables) { foreach (DataColumn col in table.Columns) { Console.Write(col.ColumnName + "\t\t"); } Console.WriteLine(); tables++; foreach (DataRow row in table.Rows) { for (int i = 0; i < table.Columns.Count; i++) { Console.Write(row[i] + "\t\t"); } Console.WriteLine(""); rows++; } } Assert.AreEqual(1, tables, "Wrong number of tables."); Assert.AreEqual(100, rows, "Wrong number of rows."); adapter.Dispose(); builder.Dispose(); command.Dispose(); }
public void DataAdapterFillTest() { BdpCommand command = new BdpCommand("select * from TEST where DATE_FIELD = ?", Connection); BdpDataAdapter adapter = new BdpDataAdapter(command); // adapter.SelectCommand.Parameters.Add("@DATE_FIELD", BdpType.Date, 4, "DATE_FIELD").Value = new DateTime(2003, 1, 5); adapter.SelectCommand.Parameters.Add("@DATE_FIELD", BdpType.Date, 4, "DATE_FIELD").Value = DateTime.Today; BdpCommandBuilder builder = new BdpCommandBuilder(adapter); DataSet ds = new DataSet(); adapter.Fill(ds, "TEST"); Console.WriteLine(); Console.WriteLine("Implicit transactions - DataAdapter Fill Method - Test"); int tables = 0; int rows = 0; foreach (DataTable table in ds.Tables) { foreach (DataColumn col in table.Columns) { Console.Write(col.ColumnName + "\t\t"); } Console.WriteLine(); tables++; foreach (DataRow row in table.Rows) { for (int i = 0; i < table.Columns.Count; i++) { Console.Write(row[i] + "\t\t"); } Console.WriteLine(""); rows++; } } Assert.AreEqual(1, tables, "Wrong number of tables."); Assert.AreEqual(100, rows, "Wrong number of rows."); adapter.Dispose(); builder.Dispose(); command.Dispose(); }
// RPH - Changed parameter names to "?". Parameters are substituted in order. // [Ignore("Named parameters are not support in the Borland Data Provider")] public void NamedParametersAndLiterals() { // string sql = "update test set char_field = '*****@*****.**', bigint_field = @bigint, varchar_field = '*****@*****.**' where int_field = @integer"; string sql = "update test set char_field = '*****@*****.**', bigint_field = ?, varchar_field = '*****@*****.**' where int_field = ?"; BdpCommand command = new BdpCommand(sql, this.Connection); command.Parameters.Add("@bigint", BdpType.Int64).Value = 200; command.Parameters.Add("@integer", BdpType.Int32).Value = 1; int recordsAffected = command.ExecuteNonQuery(); command.Close(); Assert.AreEqual(recordsAffected, 1, "Invalid number of records affected."); }
public void UpdatedClobFieldTest() { Console.WriteLine("\r\nUpdate CLOB field with implicit transaction."); BdpCommand command = new BdpCommand("update TEST set clob_field = ? where int_field = ?", Connection); command.Parameters.Add("@clob_field", BdpType.Blob, BdpType.stMemo).Value = "Clob field update with implicit transaction"; command.Parameters.Add("@int_field", BdpType.Int32).Value = 1; int i = command.ExecuteNonQuery(); Assert.AreEqual(i, 1, "Clob field update with implicit transaction failed"); // Force the implicit transaction to be committed command.Dispose(); }
public void UpdateTimeStampTest() { string sql = "select * from TEST where int_field = ?"; BdpTransaction transaction = this.Connection.BeginTransaction(); BdpCommand command = new BdpCommand(sql, Connection, transaction); BdpDataAdapter adapter = new BdpDataAdapter(command); adapter.SelectCommand.Parameters.Add("@int_field", BdpType.Int32).Value = 1; BdpCommandBuilder builder = new BdpCommandBuilder(adapter); DataSet ds = new DataSet(); adapter.Fill(ds, "TEST"); Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count"); DateTime dtValue = DateTime.Now; ds.Tables["TEST"].Rows[0]["TIMESTAMP_FIELD"] = dtValue; adapter.Update(ds, "TEST"); adapter.Dispose(); builder.Dispose(); command.Dispose(); transaction.Commit(); transaction = Connection.BeginTransaction(); sql = "SELECT timestamp_field FROM TEST WHERE int_field = ?"; command = new BdpCommand(sql, Connection, transaction); command.Parameters.Add("@int_field", BdpType.Int32).Value = 1; DateTime val = (DateTime)command.ExecuteScalar(); transaction.Commit(); Assert.AreEqual(dtValue.Day, val.Day, "timestamp_field has not correct day"); Assert.AreEqual(dtValue.Month, val.Month, "timestamp_field has not correct month"); Assert.AreEqual(dtValue.Year, val.Year, "timestamp_field has not correct year"); Assert.AreEqual(dtValue.Hour, val.Hour, "timestamp_field has not correct hour"); Assert.AreEqual(dtValue.Minute, val.Minute, "timestamp_field has not correct minute"); Assert.AreEqual(dtValue.Second, val.Second, "timestamp_field has not correct second"); }
public void BinaryBlobTest() { int id_value = this.GetId(); string selectText = "SELECT blob_field FROM TEST WHERE int_field = " + id_value.ToString(); string insertText = "INSERT INTO TEST (int_field, blob_field) values(?, ?)"; Console.WriteLine("\r\n\r\nBinary Blob Test"); Console.WriteLine("Generating an array of temp data"); // Generate an array of temp data byte[] insert_values = new byte[100000 * 4]; RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider(); rng.GetBytes(insert_values); Console.WriteLine("Executing insert command"); // Execute insert command BdpTransaction transaction = Connection.BeginTransaction(); BdpCommand insert = new BdpCommand(insertText, Connection, transaction); insert.Parameters.Add("@int_field", BdpType.Int32).Value = id_value; insert.Parameters.Add("@blob_field", BdpType.Blob, BdpType.stHBinary).Value = insert_values; insert.ExecuteNonQuery(); transaction.Commit(); Console.WriteLine("Checking inserted values"); // Check that inserted values are correct BdpCommand select = new BdpCommand(selectText, Connection); byte[] select_values = (byte[])select.ExecuteScalar(); for (int i = 0; i < insert_values.Length; i++) { if (insert_values[i] != select_values[i]) { throw new Exception("differences at index " + i.ToString()); } } Console.WriteLine("Finishing test"); }
public void UpdatedBlobFieldTest() { Console.WriteLine("\r\nUpdate BLOB field with implicit transaction."); BdpCommand command = new BdpCommand("update TEST set blob_field = ? where int_field = ?", Connection); command.Parameters.Add("@blob_field", BdpType.Blob).Value = Encoding.Default.GetBytes("Blob field update with implicit transaction"); command.Parameters.Add("@int_field", BdpType.Int32).Value = 1; int i = command.ExecuteNonQuery(); Assert.AreEqual(i, 1, "Blob field update with implicit transaction failed"); // Force the implicit transaction to be committed command.Dispose(); }
public void GetCharsLengthTest() { string sql = "select clob_field from TEST where int_field = ?"; BdpCommand command = new BdpCommand(sql, this.Connection); command.Parameters.Add("@int_field", BdpType.Int32).Value = 50; BdpDataReader reader = command.ExecuteReader(); reader.Read(); long length = reader.GetChars(0, 0, null, 0, 0); reader.Close(); Assert.AreEqual(14, length, "Incorrect clob length"); }
public void ExecuteNonQueryTest() { Transaction = Connection.BeginTransaction(); BdpCommand command = Connection.CreateCommand(); command.Transaction = Transaction; command.CommandText = "insert into TEST (INT_FIELD) values (?) "; command.Parameters.Add("@INT_FIELD", 100); int affectedRows = command.ExecuteNonQuery(); Assert.AreEqual(affectedRows, 1); Transaction.Rollback(); command.Close(); }
public void BinaryBlobTest() { int id_value = this.GetId(); string selectText = "SELECT blob_field FROM TEST WHERE int_field = " + id_value.ToString(); string insertText = "INSERT INTO TEST (int_field, blob_field) values(?, ?)"; Console.WriteLine("\r\n\r\nBinary Blob Test"); Console.WriteLine("Generating an array of temp data"); // Generate an array of temp data byte[] insert_values = new byte[100000*4]; RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider(); rng.GetBytes(insert_values); Console.WriteLine("Executing insert command"); // Execute insert command BdpTransaction transaction = Connection.BeginTransaction(); BdpCommand insert = new BdpCommand(insertText, Connection, transaction); insert.Parameters.Add("@int_field", BdpType.Int32).Value = id_value; insert.Parameters.Add("@blob_field", BdpType.Blob, BdpType.stHBinary).Value = insert_values; insert.ExecuteNonQuery(); transaction.Commit(); Console.WriteLine("Checking inserted values"); // Check that inserted values are correct BdpCommand select = new BdpCommand(selectText, Connection); byte[] select_values = (byte[])select.ExecuteScalar(); for (int i = 0; i < insert_values.Length; i++) { if (insert_values[i] != select_values[i]) { throw new Exception("differences at index " + i.ToString()); } } Console.WriteLine("Finishing test"); }
public void NextResultTest() { string querys = "select * from TEST order by INT_FIELD asc;" + "select * from TEST order by INT_FIELD desc;"; BdpTransaction transaction = Connection.BeginTransaction(); BdpCommand command = new BdpCommand(querys, Connection, transaction); BdpDataReader reader = command.ExecuteReader(); Console.WriteLine(); Console.WriteLine("DataReader - NextResult Method - Test ( First Result )"); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { Console.Write(reader.GetValue(i) + "\t"); } Console.WriteLine(); } if (reader.NextResult()) { Console.WriteLine("DataReader - NextResult Method - Test ( Second Result )"); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { Console.Write(reader.GetValue(i) + "\t"); } Console.WriteLine(); } } reader.Close(); transaction.Rollback(); command.Dispose(); }
public void FillMultipleWithImplicitTransactionTest() { BdpCommand command = new BdpCommand("select * from TEST", Connection); BdpDataAdapter adapter = new BdpDataAdapter(command); BdpCommandBuilder builder = new BdpCommandBuilder(adapter); DataSet ds1 = new DataSet(); DataSet ds2 = new DataSet(); adapter.Fill(ds1, "TEST"); adapter.Fill(ds2, "TEST"); Assert.AreEqual(100, ds1.Tables["TEST"].Rows.Count, "Incorrect row count (ds1)"); Assert.AreEqual(100, ds2.Tables["TEST"].Rows.Count, "Incorrect row count (ds2)"); adapter.Dispose(); builder.Dispose(); command.Dispose(); }
public void FillTest() { BdpCommand command = new BdpCommand("SELECT * FROM TEST", Connection); BdpDataAdapter adapter = new BdpDataAdapter(command); BdpCommandBuilder builder = new BdpCommandBuilder(adapter); DataSet ds = new DataSet(); adapter.Fill(ds, "TEST"); Assert.AreEqual(100, ds.Tables["TEST"].Rows.Count, "Incorrect row count"); Console.WriteLine(); Console.WriteLine("DataAdapter - Fill Method - Test"); foreach (DataTable table in ds.Tables) { foreach (DataColumn col in table.Columns) { Console.Write(col.ColumnName + "\t\t"); } Console.WriteLine(); foreach (DataRow row in table.Rows) { for (int i = 0; i < table.Columns.Count; i++) { Console.Write(row[i] + "\t\t"); } Console.WriteLine(""); } } adapter.Dispose(); builder.Dispose(); command.Dispose(); }
public void DeriveParameters2() { BdpTransaction transaction = Connection.BeginTransaction(); // BdpCommandBuilder builder = new BdpCommandBuilder(); BdpCommand command = new BdpCommand("GETVARCHARFIELD", Connection, transaction); command.CommandType = CommandType.StoredProcedure; // RPH - DeriveParameters consistently fails with an Invalid Cast exception. // The code below bypasses DeriveParameters and instead calls // GetProcedureParams. // BdpCommandBuilder.DeriveParameters(command); DataTable parameters = Connection.GetMetaData().GetProcedureParams(command.CommandText, ""); Console.WriteLine("Derived Parameters"); /* * for (int i = 0; i < command.Parameters.Count; i++) * { * Console.WriteLine("Parameter name: {0}\tParameter Source Column:{1}\tDirection:{2}", * command.Parameters[i].ParameterName, * command.Parameters[i].SourceColumn, * command.Parameters[i].Direction); * } */ for (int i = 0; i < parameters.Rows.Count; i++) { DataRow row = parameters.Rows[i]; ParameterDirection direction = (ParameterDirection)Convert.ToInt16(row["ParamType"]); Console.WriteLine("Parameter name: {0}\tParameter Source Column: {1}\tDirection: {2}", row["ParamName"], row["ParamPosition"], direction); } transaction.Commit(); }
public void InsertTest() { BdpTransaction transaction = this.Connection.BeginTransaction(); BdpCommand command = new BdpCommand("select * from TEST", Connection, transaction); BdpDataAdapter adapter = new BdpDataAdapter(command); BdpCommandBuilder builder = new BdpCommandBuilder(adapter); DataSet ds = new DataSet(); adapter.Fill(ds, "TEST"); Assert.AreEqual(100, ds.Tables["TEST"].Rows.Count, "Incorrect row count"); DataRow newRow = ds.Tables["TEST"].NewRow(); newRow["INT_FIELD"] = 101; newRow["CHAR_FIELD"] = "ONE THOUSAND"; newRow["VARCHAR_FIELD"] = ":;,.{}`+^*[]\\!|@#$%&/()?_-<>"; newRow["BIGINT_field"] = 100000; newRow["SMALLINT_field"] = 100; newRow["DOUBLE_FIELD"] = 100.01; newRow["NUMERIC_FIELD"] = 100.01; newRow["DECIMAL_FIELD"] = 100.01; newRow["DATE_FIELD"] = new DateTime(100, 10, 10); newRow["TIME_FIELD"] = new DateTime(100, 10, 10, 10, 10, 10, 10); newRow["TIMESTAMP_FIELD"] = new DateTime(100, 10, 10, 10, 10, 10, 10); newRow["CLOB_FIELD"] = "ONE THOUSAND"; ds.Tables["TEST"].Rows.Add(newRow); adapter.Update(ds, "TEST"); Assert.AreEqual(101, ds.Tables["TEST"].Rows.Count, "Incorrect row count"); adapter.Dispose(); builder.Dispose(); command.Dispose(); transaction.Commit(); }
public void FillTest() { BdpCommand command = new BdpCommand("SELECT * FROM TEST", Connection); BdpDataAdapter adapter = new BdpDataAdapter(command); BdpCommandBuilder builder = new BdpCommandBuilder(adapter); DataSet ds = new DataSet(); adapter.Fill(ds, "TEST"); Assert.AreEqual(100, ds.Tables["TEST"].Rows.Count, "Incorrect row count"); Console.WriteLine(); Console.WriteLine("DataAdapter - Fill Method - Test"); foreach (DataTable table in ds.Tables) { foreach (DataColumn col in table.Columns) { Console.Write(col.ColumnName + "\t\t"); } Console.WriteLine(); foreach (DataRow row in table.Rows) { for (int i = 0; i < table.Columns.Count; i++) { Console.Write(row[i] + "\t\t"); } Console.WriteLine(""); } } adapter.Dispose(); builder.Dispose(); command.Dispose(); }
public void UpdateDecimalTest() { string sql = "select * from TEST where int_field = ?"; BdpTransaction transaction = this.Connection.BeginTransaction(); BdpCommand command = new BdpCommand(sql, Connection, transaction); BdpDataAdapter adapter = new BdpDataAdapter(command); adapter.SelectCommand.Parameters.Add("@int_field", BdpType.Int32).Value = 1; BdpCommandBuilder builder = new BdpCommandBuilder(adapter); DataSet ds = new DataSet(); adapter.Fill(ds, "TEST"); Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count"); ds.Tables["TEST"].Rows[0]["DECIMAL_FIELD"] = System.Int32.MaxValue; adapter.Update(ds, "TEST"); adapter.Dispose(); builder.Dispose(); command.Dispose(); transaction.Commit(); transaction = Connection.BeginTransaction(); sql = "SELECT decimal_field FROM TEST WHERE int_field = ?"; command = new BdpCommand(sql, Connection, transaction); command.Parameters.Add("@int_field", BdpType.Int32).Value = 1; decimal val = (decimal)command.ExecuteScalar(); transaction.Commit(); Assert.AreEqual(System.Int32.MaxValue, val, "decimal_field has an incorrect value"); }
public void FillMultipleTest() { BdpTransaction transaction = this.Connection.BeginTransaction(); BdpCommand command = new BdpCommand("select * from TEST", Connection, transaction); BdpDataAdapter adapter = new BdpDataAdapter(command); BdpCommandBuilder builder = new BdpCommandBuilder(adapter); DataSet ds1 = new DataSet(); DataSet ds2 = new DataSet(); adapter.Fill(ds1, "TEST"); adapter.Fill(ds2, "TEST"); Assert.AreEqual(100, ds1.Tables["TEST"].Rows.Count, "Incorrect row count (ds1)"); Assert.AreEqual(100, ds2.Tables["TEST"].Rows.Count, "Incorrect row count (ds2)"); adapter.Dispose(); builder.Dispose(); command.Dispose(); transaction.Commit(); }
public void RecordsAffectedTest() { BdpCommand selectCommand = new BdpCommand("SELECT * FROM TEST WHERE INT_FIELD = -1", Connection); int recordsAffected = selectCommand.ExecuteNonQuery(); Console.WriteLine("\r\nRecords Affected: {0}", recordsAffected); Assert.IsTrue(recordsAffected == -1); selectCommand.Close(); BdpCommand deleteCommand = new BdpCommand("DELETE FROM TEST WHERE INT_FIELD = -1", Connection); recordsAffected = deleteCommand.ExecuteNonQuery(); Console.WriteLine("\r\nRecords Affected: {0}", recordsAffected); Assert.IsTrue(recordsAffected == 0); deleteCommand.Close(); deleteCommand = new BdpCommand("DELETE FROM TEST WHERE INT_FIELD = 3", Connection); recordsAffected = deleteCommand.ExecuteNonQuery(); Console.WriteLine("\r\nRecords Affected: {0}", recordsAffected); Assert.IsTrue(recordsAffected == 1); deleteCommand.Close(); }
public void ReadTest() { BdpTransaction transaction = Connection.BeginTransaction(); BdpCommand command = new BdpCommand("select * from TEST", Connection, transaction); Console.WriteLine(); Console.WriteLine("DataReader - Read Method - Test"); IDataReader reader = command.ExecuteReader(); while (reader.Read()) { for(int i = 0; i < reader.FieldCount; i++) { Console.Write(reader.GetValue(i) + "\t"); } Console.WriteLine(); } reader.Close(); command.Dispose(); transaction.Rollback(); }
private static void InsertTestData(string connectionString) { BdpConnection connection = new BdpConnection(connectionString); connection.Open(); StringBuilder commandText = new StringBuilder(); commandText.Append("DELETE FROM TEST"); BdpCommand command = new BdpCommand(commandText.ToString(), connection); command.ExecuteNonQuery(); command.Dispose(); commandText = new StringBuilder(); commandText.Append("insert into test (int_field, char_field, varchar_field, bigint_field, smallint_field, float_field, double_field, numeric_field, date_field, time_field, timestamp_field, clob_field, blob_field)"); commandText.Append(" values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); BdpTransaction transaction = connection.BeginTransaction(); command = new BdpCommand(commandText.ToString(), connection, transaction); try { // Add command parameters command.Parameters.Add("@int_field", BdpType.Int32); command.Parameters.Add("@char_field", BdpType.String, BdpType.stFixed); command.Parameters.Add("@varchar_field", BdpType.String); command.Parameters.Add("@bigint_field", BdpType.Int64); command.Parameters.Add("@smallint_field", BdpType.Int16); command.Parameters.Add("@float_field", BdpType.Float); command.Parameters.Add("@double_field", BdpType.Double); command.Parameters.Add("@numeric_field", BdpType.Decimal); command.Parameters.Add("@date_field", BdpType.Date); command.Parameters.Add("@time_Field", BdpType.Time); command.Parameters.Add("@timestamp_field", BdpType.DateTime); command.Parameters.Add("@clob_field", BdpType.Blob, BdpType.stHMemo); command.Parameters.Add("@blob_field", BdpType.Blob, BdpType.stHBinary); command.Prepare(); for (int i = 0; i < 100; i++) { command.Parameters["@int_field"].Value = i; command.Parameters["@char_field"].Value = "IRow " + i.ToString(); command.Parameters["@varchar_field"].Value = "IRow Number " + i.ToString(); command.Parameters["@bigint_field"].Value = i; command.Parameters["@smallint_field"].Value = i; command.Parameters["@float_field"].Value = (float)(i + 10) / 5; command.Parameters["@double_field"].Value = Math.Log(i, 10); command.Parameters["@numeric_field"].Value = (decimal)(i + 10) / 5; command.Parameters["@date_field"].Value = DateTime.Now; command.Parameters["@time_field"].Value = DateTime.Now; command.Parameters["@timestamp_field"].Value = DateTime.Now; command.Parameters["@clob_field"].Value = "IRow Number " + i.ToString(); command.Parameters["@blob_field"].Value = Encoding.Default.GetBytes("IRow Number " + i.ToString()); command.ExecuteNonQuery(); } // Commit transaction transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw ex; } finally { command.Dispose(); connection.Close(); } }
private static void CreateTriggers(string connectionString) { BdpConnection connection = new BdpConnection(connectionString); connection.Open(); StringBuilder commandText = new StringBuilder(); BdpCommand command = null; // new_row commandText = new StringBuilder(); commandText.Append("DROP TRIGGER new_row"); try { command = new BdpCommand(commandText.ToString(), connection); command.ExecuteNonQuery(); command.Dispose(); } catch { } commandText = new StringBuilder(); commandText.Append("CREATE TRIGGER new_row FOR test ACTIVE\r\n"); commandText.Append("AFTER INSERT POSITION 0\r\n"); commandText.Append("AS\r\n"); commandText.Append("BEGIN\r\n"); commandText.Append("POST_EVENT 'new row';\r\n"); commandText.Append("END"); try { command = new BdpCommand(commandText.ToString(), connection); command.ExecuteNonQuery(); command.Dispose(); } catch { } // update_row commandText = new StringBuilder(); commandText.Append("DROP TRIGGER update_row"); try { command = new BdpCommand(commandText.ToString(), connection); command.ExecuteNonQuery(); command.Dispose(); } catch { } commandText = new StringBuilder(); commandText.Append("CREATE TRIGGER update_row FOR test ACTIVE\r\n"); commandText.Append("AFTER UPDATE POSITION 0\r\n"); commandText.Append("AS\r\n"); commandText.Append("BEGIN\r\n"); commandText.Append("POST_EVENT 'updated row';\r\n"); commandText.Append("END"); try { command = new BdpCommand(commandText.ToString(), connection); command.ExecuteNonQuery(); command.Dispose(); } catch { } connection.Close(); }
public void RecordsAffectedTest() { BdpCommand selectCommand = new BdpCommand("SELECT * FROM TEST WHERE INT_FIELD = -1", Connection); int recordsAffected = selectCommand.ExecuteNonQuery(); Console.WriteLine("\r\nRecords Affected: {0}", recordsAffected); Assert.IsTrue(recordsAffected == -1); selectCommand.Close(); BdpCommand deleteCommand = new BdpCommand("DELETE FROM TEST WHERE INT_FIELD = -1", Connection); recordsAffected = deleteCommand.ExecuteNonQuery(); Console.WriteLine("\r\nRecords Affected: {0}", recordsAffected); Assert.IsTrue(recordsAffected == 0); deleteCommand.Close(); deleteCommand = new BdpCommand("DELETE FROM TEST WHERE INT_FIELD = 3", Connection); recordsAffected = deleteCommand.ExecuteNonQuery(); Console.WriteLine("\r\nRecords Affected: {0}", recordsAffected); Assert.IsTrue(recordsAffected == 1); deleteCommand.Close(); }
public void UpdatedBlobFieldTest() { Console.WriteLine("\r\nUpdate BLOB field with implicit transaction."); BdpCommand command = new BdpCommand("update TEST set blob_field = ? where int_field = ?", Connection); command.Parameters.Add("@blob_field", BdpType.Blob).Value = Encoding.Default.GetBytes("Blob field update with implicit transaction"); command.Parameters.Add("@int_field", BdpType.Int32).Value = 1; int i = command.ExecuteNonQuery(); Assert.AreEqual(i, 1, "Blob field update with implicit transaction failed"); // Force the implicit transaction to be committed command.Dispose(); }
public void InsertTest() { BdpTransaction transaction = this.Connection.BeginTransaction(); BdpCommand command = new BdpCommand("select * from TEST", Connection, transaction); BdpDataAdapter adapter = new BdpDataAdapter(command); BdpCommandBuilder builder = new BdpCommandBuilder(adapter); DataSet ds = new DataSet(); adapter.Fill(ds, "TEST"); Assert.AreEqual(100, ds.Tables["TEST"].Rows.Count, "Incorrect row count"); DataRow newRow = ds.Tables["TEST"].NewRow(); newRow["INT_FIELD"] = 101; newRow["CHAR_FIELD"] = "ONE THOUSAND"; newRow["VARCHAR_FIELD"] = ":;,.{}`+^*[]\\!|@#$%&/()?_-<>"; newRow["BIGINT_field"] = 100000; newRow["SMALLINT_field"] = 100; newRow["DOUBLE_FIELD"] = 100.01; newRow["NUMERIC_FIELD"] = 100.01; newRow["DECIMAL_FIELD"] = 100.01; newRow["DATE_FIELD"] = new DateTime(100, 10, 10); newRow["TIME_FIELD"] = new DateTime(100, 10, 10, 10, 10, 10, 10); newRow["TIMESTAMP_FIELD"] = new DateTime(100, 10, 10, 10, 10, 10, 10); newRow["CLOB_FIELD"] = "ONE THOUSAND"; ds.Tables["TEST"].Rows.Add(newRow); adapter.Update(ds, "TEST"); Assert.AreEqual(101, ds.Tables["TEST"].Rows.Count, "Incorrect row count"); adapter.Dispose(); builder.Dispose(); command.Dispose(); transaction.Commit(); }
public void DeleteTest() { string sql = "select * from TEST where int_field = ?"; BdpTransaction transaction = this.Connection.BeginTransaction(); BdpCommand command = new BdpCommand(sql, Connection, transaction); BdpDataAdapter adapter = new BdpDataAdapter(command); adapter.SelectCommand.Parameters.Add("@int_field", BdpType.Int32).Value = 10; BdpCommandBuilder builder = new BdpCommandBuilder(adapter); DataSet ds = new DataSet(); adapter.Fill(ds, "TEST"); Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count"); ds.Tables["TEST"].Rows[0].Delete(); adapter.Update(ds, "TEST"); adapter.Dispose(); builder.Dispose(); command.Dispose(); transaction.Commit(); }
public void ExecuteScalarTest() { BdpCommand command = new BdpCommand("select sum(int_field) from TEST", Connection); object actual = command.ExecuteScalar(); Console.WriteLine("\r\nExecuteScalar with implicit transaction: {0}", actual); Assert.AreEqual(4950, actual, "Wrong sum returned."); command.Dispose(); }
public void NamedParametersReuseTest() { string sql = "select * from test where int_field >= @lang and int_field <= @lang"; BdpCommand command = new BdpCommand(sql, this.Connection); command.Parameters.Add("@lang", BdpType.Int32).Value = 10; BdpDataReader reader = command.ExecuteReader(); int count = 0; int intValue = 0; while (reader.Read()) { if (count == 0) { intValue = reader.GetInt32(0); } count++; } Assert.AreEqual(1, count, "Invalid number of records fetched."); Assert.AreEqual(10, intValue, "Invalid record fetched."); reader.Close(); command.Close(); }
// RPH - Changed parameter names to "?". Parameters are substituted in order. // [Ignore("Named parameters are not support in the Borland Data Provider")] public void NamedParametersAndLiterals() { // string sql = "update test set char_field = '*****@*****.**', bigint_field = @bigint, varchar_field = '*****@*****.**' where int_field = @integer"; string sql = "update test set char_field = '*****@*****.**', bigint_field = ?, varchar_field = '*****@*****.**' where int_field = ?"; BdpCommand command = new BdpCommand(sql, this.Connection); command.Parameters.Add("@bigint", BdpType.Int64).Value = 200; command.Parameters.Add("@integer",BdpType.Int32).Value = 1; int recordsAffected = command.ExecuteNonQuery(); command.Close(); Assert.AreEqual(recordsAffected, 1, "Invalid number of records affected."); }
public void InvalidParameterFormat() { string sql = "update test set timestamp_field = ? where int_field = ?"; bool failed = false; BdpTransaction transaction = this.Connection.BeginTransaction(); try { BdpCommand command = new BdpCommand(sql, this.Connection, transaction); command.Parameters.Add("@timestamp", BdpType.DateTime).Value = 1; command.Parameters.Add("@integer", BdpType.Int32).Value = 1; command.ExecuteNonQuery(); command.Close(); transaction.Commit(); } catch { failed = true; transaction.Rollback(); } Assert.IsTrue(failed, "Bad parameter not detected"); }
public void ExecuteNonQueryWithOutputParameters() { BdpCommand command = new BdpCommand("EXECUTE PROCEDURE GETASCIIBLOB(?)", Connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@ID", BdpType.String).Direction = ParameterDirection.Input; command.Parameters.Add("@CLOB_FIELD", BdpType.Blob, BdpType.stMemo).Direction = ParameterDirection.Output; command.Parameters[0].Value = 3; // This will fill output parameters values command.ExecuteNonQuery(); Console.WriteLine("Output Parameters"); Console.WriteLine(command.Parameters[1].Value); // Check that the output parameter has a correct value Assert.AreEqual("IRow Number 3", command.Parameters[1].Value, "Output parameter value is not valid"); // Close command - this will do a transaction commit command.Close(); }
public void UpdateTimeStampTest() { string sql = "select * from TEST where int_field = ?"; BdpTransaction transaction = this.Connection.BeginTransaction(); BdpCommand command = new BdpCommand(sql, Connection, transaction); BdpDataAdapter adapter = new BdpDataAdapter(command); adapter.SelectCommand.Parameters.Add("@int_field", BdpType.Int32).Value = 1; BdpCommandBuilder builder = new BdpCommandBuilder(adapter); DataSet ds = new DataSet(); adapter.Fill(ds, "TEST"); Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count"); DateTime dtValue = DateTime.Now; ds.Tables["TEST"].Rows[0]["TIMESTAMP_FIELD"] = dtValue; adapter.Update(ds, "TEST"); adapter.Dispose(); builder.Dispose(); command.Dispose(); transaction.Commit(); transaction = Connection.BeginTransaction(); sql = "SELECT timestamp_field FROM TEST WHERE int_field = ?"; command = new BdpCommand(sql, Connection, transaction); command.Parameters.Add("@int_field", BdpType.Int32).Value = 1; DateTime val = (DateTime)command.ExecuteScalar(); transaction.Commit(); Assert.AreEqual(dtValue.Day, val.Day, "timestamp_field has not correct day"); Assert.AreEqual(dtValue.Month, val.Month, "timestamp_field has not correct month"); Assert.AreEqual(dtValue.Year, val.Year, "timestamp_field has not correct year"); Assert.AreEqual(dtValue.Hour, val.Hour, "timestamp_field has not correct hour"); Assert.AreEqual(dtValue.Minute, val.Minute, "timestamp_field has not correct minute"); Assert.AreEqual(dtValue.Second, val.Second, "timestamp_field has not correct second"); }
public void UpdateVarCharTest() { string sql = "select * from TEST where int_field = ?"; BdpTransaction transaction = this.Connection.BeginTransaction(); BdpCommand command = new BdpCommand(sql, Connection, transaction); BdpDataAdapter adapter = new BdpDataAdapter(command); adapter.SelectCommand.Parameters.Add("@int_field", BdpType.Int32).Value = 1; BdpCommandBuilder builder = new BdpCommandBuilder(adapter); DataSet ds = new DataSet(); adapter.Fill(ds, "TEST"); Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count"); ds.Tables["TEST"].Rows[0]["VARCHAR_FIELD"] = "ONE VAR THOUSAND"; adapter.Update(ds, "TEST"); adapter.Dispose(); builder.Dispose(); command.Dispose(); transaction.Commit(); transaction = Connection.BeginTransaction(); sql = "SELECT varchar_field FROM TEST WHERE int_field = ?"; command = new BdpCommand(sql, Connection, transaction); command.Parameters.Add("@int_field", BdpType.Int32).Value = 1; string val = (string)command.ExecuteScalar(); transaction.Commit(); Assert.AreEqual("ONE VAR THOUSAND", val.Trim(), "varchar_field has an incorrect value"); }
public void UpdateDecimalTest() { string sql = "select * from TEST where int_field = ?"; BdpTransaction transaction = this.Connection.BeginTransaction(); BdpCommand command = new BdpCommand(sql, Connection, transaction); BdpDataAdapter adapter = new BdpDataAdapter(command); adapter.SelectCommand.Parameters.Add("@int_field", BdpType.Int32).Value = 1; BdpCommandBuilder builder = new BdpCommandBuilder(adapter); DataSet ds = new DataSet(); adapter.Fill(ds, "TEST"); Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count"); ds.Tables["TEST"].Rows[0]["DECIMAL_FIELD"] = System.Int32.MaxValue; adapter.Update(ds, "TEST"); adapter.Dispose(); builder.Dispose(); command.Dispose(); transaction.Commit(); transaction = Connection.BeginTransaction(); sql = "SELECT decimal_field FROM TEST WHERE int_field = ?"; command = new BdpCommand(sql, Connection, transaction); command.Parameters.Add("@int_field", BdpType.Int32).Value = 1; decimal val = (decimal)command.ExecuteScalar(); transaction.Commit(); Assert.AreEqual(System.Int32.MaxValue, val, "decimal_field has an incorrect value"); }
public void TextBlobTest() { int id_value = this.GetId(); string text = "Borland Data Provider Clob Field test"; string selectText = "SELECT clob_field FROM TEST WHERE int_field = " + id_value.ToString(); string insertText = "INSERT INTO TEST (int_field, clob_field) values(?, ?)"; Console.WriteLine("\r\n\r\nBinary Blob Test"); Console.WriteLine("Executing insert command"); // Execute insert command BdpTransaction transaction = Connection.BeginTransaction(); BdpCommand insert = new BdpCommand(insertText, Connection, transaction); insert.Parameters.Add("@int_field", BdpType.Int32).Value = id_value; insert.Parameters.Add("@clob_field", BdpType.Blob, BdpType.stHMemo).Value = text; insert.ExecuteNonQuery(); transaction.Commit(); Console.WriteLine("Checking inserted values"); // Check that inserted values are correct BdpCommand select = new BdpCommand(selectText, Connection); string result = new String((char[])select.ExecuteScalar()); Assert.AreEqual(text, result); Console.WriteLine("Finishing test"); }
public void UpdatedArrayFieldTest() { Console.WriteLine("\r\nUpdate IARRAY field with implicit transaction."); int[] values = new int[4]; values[0] = 10; values[1] = 20; values[2] = 30; values[3] = 40; // Add IARRAY_FIELD column BdpCommand command = new BdpCommand("alter table TEST add IARRAY_FIELD INTEGER[4]", Connection); command.ExecuteNonQuery(); command.Close(); // Now test the update of an array command = new BdpCommand("update TEST set iarray_field = ? where int_field = ?", Connection); command.Parameters.Add("@iarray_field", BdpType.Array).Value = values; command.Parameters.Add("@int_field", BdpType.Int32).Value = 1; int i = command.ExecuteNonQuery(); Assert.AreEqual(i, 1, "Array field update with implicit transaction failed"); // Force the implicit transaction to be committed command.Dispose(); }
public void GetCharsLengthTest() { string sql = "select clob_field from TEST where int_field = ?"; BdpCommand command = new BdpCommand(sql, this.Connection); command.Parameters.Add("@int_field", BdpType.Int32).Value = 50; BdpDataReader reader = command.ExecuteReader(); reader.Read(); long length = reader.GetChars(0, 0, null, 0, 0); reader.Close(); Assert.AreEqual(14, length, "Incorrect clob length"); }
private static void CreateTables(string connectionString) { BdpConnection connection = new BdpConnection(connectionString); connection.Open(); StringBuilder commandText = new StringBuilder(); commandText.Append("DROP TABLE TEST"); BdpCommand command = null; try { command = new BdpCommand(commandText.ToString(), connection); command.ExecuteNonQuery(); command.Dispose(); } catch { } commandText = new StringBuilder(); // Table for general purpouse tests commandText.Append("CREATE TABLE TEST ("); commandText.Append("INT_FIELD INTEGER DEFAULT 0 NOT NULL PRIMARY KEY,"); commandText.Append("CHAR_FIELD CHAR(30),"); commandText.Append("VARCHAR_FIELD VARCHAR(100),"); commandText.Append("BIGINT_FIELD BIGINT,"); commandText.Append("SMALLINT_FIELD SMALLINT,"); commandText.Append("DOUBLE_FIELD DOUBLE PRECISION,"); commandText.Append("FLOAT_FIELD FLOAT,"); commandText.Append("NUMERIC_FIELD NUMERIC(15,2),"); commandText.Append("DECIMAL_FIELD DECIMAL(15,2),"); commandText.Append("DATE_FIELD DATE,"); commandText.Append("TIME_FIELD TIME,"); commandText.Append("TIMESTAMP_FIELD TIMESTAMP,"); commandText.Append("CLOB_FIELD BLOB SUB_TYPE 1 SEGMENT SIZE 80,"); commandText.Append("BLOB_FIELD BLOB SUB_TYPE 0 SEGMENT SIZE 80,"); commandText.Append("EXPR_FIELD COMPUTED BY (smallint_field * 1000));"); try { command = new BdpCommand(commandText.ToString(), connection); command.ExecuteNonQuery(); command.Dispose(); } catch { } connection.Close(); }
public void UpdatedClobFieldTest() { Console.WriteLine("\r\nUpdate CLOB field with implicit transaction."); BdpCommand command = new BdpCommand("update TEST set clob_field = ? where int_field = ?", Connection); command.Parameters.Add("@clob_field", BdpType.Blob, BdpType.stMemo).Value = "Clob field update with implicit transaction"; command.Parameters.Add("@int_field", BdpType.Int32).Value = 1; int i = command.ExecuteNonQuery(); Assert.AreEqual(i, 1, "Clob field update with implicit transaction failed"); // Force the implicit transaction to be committed command.Dispose(); }
private static void CreateProcedures(string connectionString) { BdpConnection connection = new BdpConnection(connectionString); connection.Open(); BdpCommand command = null; StringBuilder commandText = new StringBuilder(); // SELECT_DATA commandText = new StringBuilder(); commandText.Append("DROP PROCEDURE SELECT_DATA"); try { command = new BdpCommand(commandText.ToString(), connection); command.ExecuteNonQuery(); command.Dispose(); } catch { } commandText = new StringBuilder(); commandText.Append("CREATE PROCEDURE SELECT_DATA \r\n"); commandText.Append("RETURNS ( \r\n"); commandText.Append("INT_FIELD INTEGER, \r\n"); commandText.Append("VARCHAR_FIELD VARCHAR(100), \r\n"); commandText.Append("DECIMAL_FIELD DECIMAL(15,2)) \r\n"); commandText.Append("AS \r\n"); commandText.Append("begin \r\n"); commandText.Append("FOR SELECT INT_FIELD, VARCHAR_FIELD, DECIMAL_FIELD FROM TEST INTO :INT_FIELD, :VARCHAR_FIELD, :DECIMAL_FIELD \r\n"); commandText.Append("DO \r\n"); commandText.Append("SUSPEND; \r\n"); commandText.Append("end;"); try { command = new BdpCommand(commandText.ToString(), connection); command.ExecuteNonQuery(); command.Dispose(); } catch { } // GETRECORDCOUNT commandText = new StringBuilder(); commandText.Append("DROP PROCEDURE GETRECORDCOUNT"); try { command = new BdpCommand(commandText.ToString(), connection); command.ExecuteNonQuery(); command.Dispose(); } catch { } commandText = new StringBuilder(); commandText.Append("CREATE PROCEDURE GETRECORDCOUNT \r\n"); commandText.Append("RETURNS ( \r\n"); commandText.Append("RECCOUNT SMALLINT) \r\n"); commandText.Append("AS \r\n"); commandText.Append("begin \r\n"); commandText.Append("for select count(*) from test into :reccount \r\n"); commandText.Append("do \r\n"); commandText.Append("suspend; \r\n"); commandText.Append("end\r\n"); command = new BdpCommand(commandText.ToString(), connection); command.ExecuteNonQuery(); command.Dispose(); // GETVARCHARFIELD commandText = new StringBuilder(); commandText.Append("DROP PROCEDURE GETVARCHARFIELD"); try { command = new BdpCommand(commandText.ToString(), connection); command.ExecuteNonQuery(); command.Dispose(); } catch { } commandText = new StringBuilder(); commandText.Append("CREATE PROCEDURE GETVARCHARFIELD (\r\n"); commandText.Append("ID INTEGER)\r\n"); commandText.Append("RETURNS (\r\n"); commandText.Append("VARCHAR_FIELD VARCHAR(100))\r\n"); commandText.Append("AS\r\n"); commandText.Append("begin\r\n"); commandText.Append("for select varchar_field from test where int_field = :id into :varchar_field\r\n"); commandText.Append("do\r\n"); commandText.Append("suspend;\r\n"); commandText.Append("end\r\n"); command = new BdpCommand(commandText.ToString(), connection); command.ExecuteNonQuery(); command.Dispose(); // GETASCIIBLOB commandText = new StringBuilder(); commandText.Append("DROP PROCEDURE GETASCIIBLOB"); try { command = new BdpCommand(commandText.ToString(), connection); command.ExecuteNonQuery(); command.Dispose(); } catch { } commandText = new StringBuilder(); commandText.Append("CREATE PROCEDURE GETASCIIBLOB (\r\n"); commandText.Append("ID INTEGER)\r\n"); commandText.Append("RETURNS (\r\n"); commandText.Append("ASCII_BLOB BLOB SUB_TYPE 1)\r\n"); commandText.Append("AS\r\n"); commandText.Append("begin\r\n"); commandText.Append("for select clob_field from test where int_field = :id into :ascii_blob\r\n"); commandText.Append("do\r\n"); commandText.Append("suspend;\r\n"); commandText.Append("end\r\n"); try { command = new BdpCommand(commandText.ToString(), connection); command.ExecuteNonQuery(); command.Dispose(); } catch { } connection.Close(); }
public void DeriveParameters2() { BdpTransaction transaction = Connection.BeginTransaction(); // BdpCommandBuilder builder = new BdpCommandBuilder(); BdpCommand command = new BdpCommand("GETVARCHARFIELD", Connection, transaction); command.CommandType = CommandType.StoredProcedure; // RPH - DeriveParameters consistently fails with an Invalid Cast exception. // The code below bypasses DeriveParameters and instead calls // GetProcedureParams. // BdpCommandBuilder.DeriveParameters(command); DataTable parameters = Connection.GetMetaData().GetProcedureParams(command.CommandText, ""); Console.WriteLine("Derived Parameters"); /* for (int i = 0; i < command.Parameters.Count; i++) { Console.WriteLine("Parameter name: {0}\tParameter Source Column:{1}\tDirection:{2}", command.Parameters[i].ParameterName, command.Parameters[i].SourceColumn, command.Parameters[i].Direction); } */ for (int i = 0; i < parameters.Rows.Count; i++) { DataRow row = parameters.Rows[i]; ParameterDirection direction = (ParameterDirection)Convert.ToInt16(row["ParamType"]); Console.WriteLine("Parameter name: {0}\tParameter Source Column: {1}\tDirection: {2}", row["ParamName"], row["ParamPosition"], direction); } transaction.Commit(); }
public void PrepareTest() { try { // Drop the table BdpCommand drop = new BdpCommand("drop table PrepareTest", Connection); drop.ExecuteNonQuery(); drop.Close(); } catch { } // Create a new test table BdpCommand create = new BdpCommand("create table PrepareTest(test_field varchar(20));", Connection); create.ExecuteNonQuery(); create.Close(); // Insert data using a prepared statement BdpCommand command = new BdpCommand( "INSERT INTO PrepareTest (test_field) VALUES (?);", Connection); command.Parameters.Add("@test_field", BdpType.String).Value = DBNull.Value; command.Prepare(); for (int i = 0; i < 5; i++) { if (i < 1) { command.Parameters[0].Value = DBNull.Value; } else { command.Parameters[0].Value = i.ToString(); } command.ExecuteNonQuery(); } command.Close(); try { // Check that data is correct BdpCommand select = new BdpCommand("select * from PrepareTest", Connection); BdpDataReader reader = select.ExecuteReader(); int count = 0; while (reader.Read()) { if (count == 0) { Assert.AreEqual(DBNull.Value, reader[0], "Invalid value."); } else { Assert.AreEqual(count.ToString(), reader.GetString(0).Trim(), "Invalid value."); } count++; } reader.Close(); } catch (Exception ex) { throw ex; } finally { // Drop table BdpCommand drop = new BdpCommand("drop table PrepareTest", Connection); drop.ExecuteNonQuery(); drop.Close(); } }