예제 #1
0
        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();
        }
예제 #3
0
		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();
				}
			}
		}
예제 #4
0
        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();
                }
            }
        }
예제 #6
0
        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();
        }
예제 #8
0
//      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 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();
        }
예제 #10
0
        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");
        }
예제 #11
0
		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");
		}
예제 #12
0
        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();
        }
예제 #13
0
        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();
        }
예제 #14
0
        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();
        }
예제 #15
0
        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();
            }
        }
예제 #16
0
		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();
			}
		}
예제 #17
0
		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();
		}
예제 #18
0
		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();
		}
예제 #19
0
		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();
			}
		}
예제 #20
0
        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 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 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();
		}
예제 #23
0
//      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.");
		}
예제 #24
0
		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");
		}
예제 #25
0
        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();
        }
예제 #26
0
		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();
		}
예제 #27
0
        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");
        }
예제 #28
0
        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();
            }
        }
예제 #29
0
		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();
		}
예제 #30
0
		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 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();
		}