public void PqsqlConnectionTest6() { PqsqlConnection connection = new PqsqlConnection(connectionString); connection.Open(); Assert.AreEqual(ConnectionState.Open, connection.State, "wrong connection state"); PqsqlCommand cmd = connection.CreateCommand(); Assert.AreEqual(CommandType.Text, cmd.CommandType, "wrong command type"); Assert.AreEqual(-1, cmd.CommandTimeout, "wrong command timeout"); Assert.AreEqual(string.Empty, cmd.CommandText, "wrong command text"); Assert.AreEqual(connection, cmd.Connection, "wrong command connection"); Assert.AreEqual(null, cmd.Transaction, "wrong command transaction"); cmd.CommandText = "select pg_terminate_backend(pg_backend_pid()); select pg_sleep(5);"; cmd.ExecuteNonQuery(); // must execute both statements cmd.Cancel(); cmd.CommandText = ""; cmd.ExecuteNonQuery(); }
public void PqsqlCommandTest14() { const int n = 600; PqsqlTransaction t = mConnection.BeginTransaction(); StringBuilder sb = new StringBuilder("CREATE TEMP TABLE testcopy (c0 int4,"); for (int i = 1; i < n; i++) { if (i > 1) { sb.Append(','); } sb.AppendFormat("c{0} text", i); } sb.Append(");"); PqsqlCommand cmd = mConnection.CreateCommand(); cmd.Transaction = t; cmd.CommandText = sb.ToString(); cmd.CommandTimeout = 100; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); sb.Clear(); sb.Append("INSERT INTO testcopy VALUES (:p0,"); cmd.Parameters.AddWithValue(":p0", 0); for (int i = 1; i < n; i++) { if (i > 1) { sb.Append(','); } string par = ":p" + i; sb.AppendFormat(par); cmd.Parameters.AddWithValue(par, "01234567890123456789"); } sb.Append(");"); cmd.CommandText = sb.ToString(); try { cmd.ExecuteNonQuery(); } catch (PqsqlException e) { Assert.AreEqual((int)PqsqlState.PROGRAM_LIMIT_EXCEEDED, e.ErrorCode); throw; } Assert.Fail(); }
public void PqsqlConnectionTest9() { PqsqlConnection connection = new PqsqlConnection(connectionString); string s = connection.GetErrorMessage(); Assert.AreEqual(string.Empty, s); PqsqlCommand cmd = connection.CreateCommand(); cmd.CommandText = "foobar command"; try { cmd.ExecuteNonQuery(); } catch (PqsqlException exception) { // SYNTAX_ERROR = 16801924, // 42601 (syntax_error) //Assert.AreNotSame(string.Empty, exception.Hint); Assert.AreEqual("42601", exception.SqlState); Assert.AreEqual(16801924, exception.ErrorCode); } s = connection.GetErrorMessage(); Assert.IsNotNull(s); Assert.AreNotSame(string.Empty, s); }
public void PqsqlTypeRegistryTest6() { Action[] actions = new Action[20]; // stress test user-defined type setup for (int i = 0; i < 20; i++) { actions[i] = () => { using (PqsqlConnection conn = new PqsqlConnection(mConnection.ConnectionString)) using (PqsqlCommand cmd = new PqsqlCommand("select 'hello world'::citext", conn)) using (PqsqlDataReader reader = cmd.ExecuteReader()) { bool read = reader.Read(); Assert.IsTrue(read); object helloWorld = reader.GetValue(0); // must access by GetValue, GetString verifies typoid Assert.AreEqual("hello world", helloWorld); read = reader.Read(); Assert.IsFalse(read); } }; } using (PqsqlCommand check = new PqsqlCommand("select oid from pg_extension where extname='citext'", mConnection)) using (PqsqlCommand create = new PqsqlCommand("create extension citext", mConnection)) using (PqsqlTransaction t = mConnection.BeginTransaction()) { object o = null; try { check.Transaction = t; o = check.ExecuteScalar(); if (o == null) { create.Transaction = t; int aff = create.ExecuteNonQuery(); Assert.AreEqual(0, aff); } t.Commit(); Parallel.Invoke(actions); } finally { if (o == null) { using (PqsqlCommand drop = new PqsqlCommand("drop extension if exists citext", mConnection)) { int aff = drop.ExecuteNonQuery(); Assert.AreEqual(0, aff); } } } } }
public void PqsqlExceptionTest2() { mCmd.CommandText = "syntax error"; try { mCmd.ExecuteNonQuery(); Assert.Fail(); } catch (PqsqlException e) { Assert.IsNotNull(e.Message); Assert.AreNotEqual(string.Empty, e.Message); Assert.AreEqual(string.Empty, e.Hint); Assert.AreEqual("42601", e.SqlState); Assert.AreEqual((int)PqsqlState.SYNTAX_ERROR, e.ErrorCode); } }
public void PqsqlCommandBuilderTest3() { using (PqsqlConnection connection = new PqsqlConnection(connectionString)) using (PqsqlCommand command = connection.CreateCommand()) { PqsqlTransaction transaction = connection.BeginTransaction(); command.Transaction = transaction; command.CommandText = "create temp table temptab (c0 int4 primary key, c1 float8)"; command.CommandType = CommandType.Text; command.ExecuteNonQuery(); transaction.Commit(); // temp table must be visible in the next transaction transaction = connection.BeginTransaction(); PqsqlDataAdapter adapter = new PqsqlDataAdapter("select * from temptab", connection) { SelectCommand = { Transaction = transaction } }; PqsqlCommandBuilder builder = new PqsqlCommandBuilder(adapter); // INSERT INTO "postgres"."pg_temp_2"."temptab" ("c0", "c1") VALUES (:p1, :p2) PqsqlCommand inserter = builder.GetInsertCommand(); inserter.Parameters["p1"].Value = 1; inserter.Parameters["p2"].Value = 2.1; int inserted = inserter.ExecuteNonQuery(); Assert.AreEqual(1, inserted); // UPDATE "postgres"."pg_temp_2"."temptab" // SET "c0" = :p1, "c1" = :p2 // WHERE (("c0" = :p3) AND ((:p4 = 1 AND "c1" IS NULL) OR ("c1" = :p5))) PqsqlCommand updater = builder.GetUpdateCommand(); updater.Parameters["p1"].Value = 2; updater.Parameters["p2"].Value = 2.2; updater.Parameters["p3"].Value = 1; updater.Parameters["p4"].Value = 0; updater.Parameters["p5"].Value = 2.1; int updated = updater.ExecuteNonQuery(); Assert.AreEqual(1, updated); // DELETE FROM "postgres"."pg_temp_2"."temptab" // WHERE (("c0" = :p1) AND ((:p2 = 1 AND "c1" IS NULL) OR ("c1" = :p3))) PqsqlCommand deleter = builder.GetDeleteCommand(); deleter.Parameters["p1"].Value = 2; deleter.Parameters["p2"].Value = 0; deleter.Parameters["p3"].Value = 2.2; int deleted = deleter.ExecuteNonQuery(); Assert.AreEqual(1, deleted); transaction.Rollback(); } }
public void TestInitialize() { mConnection = new PqsqlConnection(connectionString); // force UTC using (var cmd = new PqsqlCommand("set timezone to 'UTC';", mConnection)) { cmd.ExecuteNonQuery(); } mCmd = mConnection.CreateCommand(); }
public void PqsqlCommandBuilderTest2() { using (PqsqlConnection connection = new PqsqlConnection(connectionString)) using (PqsqlCommand command = connection.CreateCommand()) { PqsqlTransaction transaction = connection.BeginTransaction(); command.Transaction = transaction; command.CommandText = "create temp table temptab (c0 int4 primary key, c1 float8)"; command.CommandType = CommandType.Text; command.ExecuteNonQuery(); transaction.Commit(); // temp table must be visible in the next transaction transaction = connection.BeginTransaction(); PqsqlDataAdapter adapter = new PqsqlDataAdapter("select * from temptab", connection) { SelectCommand = { Transaction = transaction }, }; adapter.RowUpdated += Adapter_RowUpdated; PqsqlCommandBuilder builder = new PqsqlCommandBuilder(adapter); DataSet ds = new DataSet(); adapter.FillSchema(ds, SchemaType.Source); adapter.Fill(ds, "temptab"); DataTable temptab = ds.Tables["temptab"]; DataRow row = temptab.NewRow(); row["c0"] = 123; row["c1"] = 1.23; temptab.Rows.Add(row); adapter.Update(ds, "temptab"); command.CommandText = "select * from temptab"; command.CommandType = CommandType.Text; using (PqsqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Assert.AreEqual(123, reader.GetInt32(0)); Assert.AreEqual(1.23, reader.GetDouble(1)); } } transaction.Rollback(); } }
public void PqsqlCopyFromTest1() { PqsqlTransaction tran = mConnection.BeginTransaction(); mCmd.Transaction = tran; mCmd.CommandText = "create temporary table foo ( a int2, b int4, c int8 )"; mCmd.CommandType = CommandType.Text; int affected = mCmd.ExecuteNonQuery(); Assert.AreEqual(0, affected); PqsqlCopyFrom copy = new PqsqlCopyFrom(mConnection) { Table = "foo", ColumnList = "c,a,b", CopyTimeout = 10 }; copy.Start(); for (short i = 9; i >= 0; i--) { copy.WriteInt8(i); copy.WriteInt2(i); copy.WriteInt4(i); } copy.End(); copy.Close(); mCmd.CommandText = "foo"; mCmd.CommandType = CommandType.TableDirect; int value = 9; foreach (IDataRecord rec in mCmd.ExecuteReader()) { object[] o = new object[3]; rec.GetValues(o); Assert.IsInstanceOfType(o[0], typeof(short)); Assert.AreEqual((short)value, o[0]); Assert.IsInstanceOfType(o[1], typeof(int)); Assert.AreEqual(value, o[1]); Assert.IsInstanceOfType(o[2], typeof(long)); Assert.AreEqual((long)value, o[2]); value--; } Assert.AreEqual(-1, value); tran.Rollback(); }
public void PqsqlCommandTest8() { PqsqlTransaction t = mConnection.BeginTransaction(); PqsqlCommand cmd = mConnection.CreateCommand(); cmd.Transaction = t; cmd.CommandText = " ; create temp table temptab (c0 int4); ; insert into temptab values (1); insert into temptab values (2); insert into temptab values (3);"; cmd.CommandTimeout = 10; cmd.CommandType = CommandType.Text; int n = cmd.ExecuteNonQuery(); t.Rollback(); Assert.AreEqual(3, n); }
public void PqsqlCommandTest7() { PqsqlTransaction t = mConnection.BeginTransaction(); PqsqlCommand cmd = mConnection.CreateCommand(); cmd.Transaction = t; cmd.CommandText = "create temp table temptab (c0 int4 primary key, c1 float8); insert into temptab values (1,1.0); insert into temptab values (2,2.0); update temptab set c1 = 3.0 where c0 = 2;"; cmd.CommandTimeout = 10; cmd.CommandType = CommandType.Text; int n = cmd.ExecuteNonQuery(); t.Rollback(); Assert.AreEqual(3, n); }
public void PqsqlCommandBuilderTest4() { using (PqsqlConnection connection = new PqsqlConnection(connectionString)) using (PqsqlCommand command = connection.CreateCommand()) { PqsqlTransaction transaction = connection.BeginTransaction(); command.Transaction = transaction; command.CommandText = "create temp table temptab (c0 int4 primary key, c1 float8, c2 timestamp);"; command.CommandType = CommandType.Text; command.ExecuteNonQuery(); transaction.Commit(); // temp table must be visible in the next transaction transaction = connection.BeginTransaction(); PqsqlDataAdapter adapter = new PqsqlDataAdapter("select * from temptab", connection) { SelectCommand = { Transaction = transaction }, }; adapter.RowUpdated += Adapter_RowUpdated; PqsqlCommandBuilder builder = new PqsqlCommandBuilder(adapter); DataTableMapping mapping = adapter.TableMappings.Add("Table", "temptab"); mapping.ColumnMappings.Add("c0", "id"); mapping.ColumnMappings.Add("c2", "time"); DataSet ds = new DataSet(); adapter.FillSchema(ds, SchemaType.Mapped); adapter.Fill(ds); DataTable tab = ds.Tables[0]; Assert.AreEqual("id", tab.Columns[0].ColumnName); Assert.AreEqual("c1", tab.Columns[1].ColumnName); Assert.AreEqual("time", tab.Columns[2].ColumnName); transaction.Rollback(); } }
public void PqsqlConnectionTest7() { PqsqlConnection connection = new PqsqlConnection(connectionString); bool opened = false; bool closed = true; connection.StateChange += (sender, args) => { if (args.CurrentState == ConnectionState.Closed) { opened = false; closed = true; } if (args.CurrentState == ConnectionState.Open) { opened = true; closed = false; } }; connection.Open(); Assert.AreEqual(ConnectionState.Open, connection.State, "wrong connection state"); Assert.AreEqual(true, opened); Assert.AreEqual(false, closed); try { PqsqlCommand cmd = connection.CreateCommand(); cmd.CommandText = "select pg_terminate_backend(pg_backend_pid()); select pg_sleep(5);"; cmd.ExecuteNonQuery(); // must execute both statements } catch (Exception) { // ignored connection.Close(); } Assert.AreEqual(false, opened); Assert.AreEqual(true, closed); }
public void PqsqlTypeRegistryTest4() { mCmd.CommandText = "select 'YES'::citext"; mCmd.CommandType = CommandType.Text; using (PqsqlCommand check = new PqsqlCommand("select oid from pg_extension where extname='citext'", mConnection)) using (PqsqlCommand create = new PqsqlCommand("create extension citext", mConnection)) using (PqsqlCommand drop = new PqsqlCommand("drop extension if exists citext", mConnection)) { object o = null; try { o = check.ExecuteScalar(); if (o == null) { int aff = create.ExecuteNonQuery(); Assert.AreEqual(0, aff); } PqsqlDataReader reader = mCmd.ExecuteReader(); bool read = reader.Read(); Assert.IsTrue(read); object yes = reader.GetValue(0); // must access by GetValue, GetString verifies typoid Assert.AreEqual("YES", yes); reader.Close(); } finally { if (o == null) { int aff = drop.ExecuteNonQuery(); Assert.AreEqual(0, aff); } } } }
public void PqsqlCopyToTest1() { PqsqlTransaction tran = mConnection.BeginTransaction(); mCmd.Transaction = tran; mCmd.CommandText = "create temporary table foo (a int4, b int4, c int4); " + "insert into foo values (1, 2, 3); " + "insert into foo values (4, 5, 6); "; mCmd.CommandType = CommandType.Text; int affected = mCmd.ExecuteNonQuery(); Assert.AreEqual(2, affected); var copy = new PqsqlCopyTo(mConnection) { Table = "foo", ColumnList = "c,a,b", CopyTimeout = 10, }; copy.Start(); var i = 0; while (copy.FetchRow()) { var c = copy.ReadInt4(); var a = copy.ReadInt4(); var b = copy.ReadInt4(); if (i == 0) { Assert.AreEqual(1, a); Assert.AreEqual(2, b); Assert.AreEqual(3, c); } else if (i == 1) { Assert.AreEqual(4, a); Assert.AreEqual(5, b); Assert.AreEqual(6, c); } i++; } copy.Close(); tran.Rollback(); }
public void PqsqlCopyFromTest3() { PqsqlTransaction t = mConnection.BeginTransaction(); PqsqlCommand cmd = mConnection.CreateCommand(); cmd.Transaction = t; cmd.CommandText = "CREATE TEMP TABLE testcopy (c0 int2, c1 int4, c2 int8, c3 bool, c4 text, c5 float4, c6 float8, c7 timestamp, c8 interval, c9 numeric, c10 date, c11 time, c12 timetz);"; cmd.CommandTimeout = 100; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); PqsqlCopyFrom copy = new PqsqlCopyFrom(mConnection) { Table = "testcopy", ColumnList = "c0,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12", CopyTimeout = 5 }; copy.Start(); DateTime now = new DateTime(2001, 1, 1, 1, 2, 3, DateTimeKind.Utc); DateTime date = DateTime.UtcNow.Date; TimeSpan time = DateTime.UtcNow.TimeOfDay; long timeTicks = time.Ticks; timeTicks = timeTicks - timeTicks % TimeSpan.TicksPerMillisecond; TimeSpan timetz = DateTime.Now.TimeOfDay; long timetzTicks = timetz.Ticks; timetzTicks = timetzTicks - timetzTicks % TimeSpan.TicksPerMillisecond; for (int i = 0; i < 4; i++) { copy.WriteInt2((short)i); copy.WriteInt4(i); copy.WriteInt8(i); copy.WriteBool(i > 0); copy.WriteText(Convert.ToString(i)); copy.WriteFloat4((float)(i + 0.123)); copy.WriteFloat8(i + 0.123); copy.WriteTimestamp(now.AddSeconds(i)); copy.WriteInterval(TimeSpan.FromHours(24) + TimeSpan.FromDays(7) + TimeSpan.FromMinutes(i)); copy.WriteNumeric((decimal)i / 10); copy.WriteDate(date); copy.WriteTime(time); copy.WriteTimeTZ(timetz); } copy.End(); copy.Close(); cmd.Transaction = t; cmd.CommandText = "testcopy"; cmd.CommandType = CommandType.TableDirect; PqsqlDataReader r = cmd.ExecuteReader(); Assert.AreEqual(-1, r.RecordsAffected); int j = 0; foreach (IDataRecord row in r) { Assert.AreEqual((short)j, row.GetInt16(0)); Assert.AreEqual(j, row.GetInt32(1)); Assert.AreEqual(j, row.GetInt64(2)); Assert.AreEqual(j > 0, row.GetBoolean(3)); Assert.AreEqual(Convert.ToString(j), row.GetString(4)); Assert.AreEqual((float)(j + 0.123), row.GetFloat(5)); Assert.AreEqual(j + 0.123, row.GetDouble(6)); Assert.AreEqual(now.AddSeconds(j), row.GetDateTime(7)); Assert.AreEqual(TimeSpan.FromHours(24) + TimeSpan.FromDays(7) + TimeSpan.FromMinutes(j), row.GetValue(8)); Assert.AreEqual((double)j / 10, row.GetValue(9)); Assert.AreEqual(date, row.GetValue(10)); TimeSpan c11 = (TimeSpan)row.GetValue(11); long c11Ticks = c11.Ticks; c11Ticks = c11Ticks - c11Ticks % TimeSpan.TicksPerMillisecond; Assert.AreEqual(timeTicks, c11Ticks); TimeSpan c12 = (TimeSpan)row.GetValue(12); long c12Ticks = c12.Ticks; c12Ticks = c12Ticks - c12Ticks % TimeSpan.TicksPerMillisecond; Assert.AreEqual(timetzTicks, c12Ticks); j++; } t.Rollback(); }
public void PqsqlCommandTest6() { PqsqlTransaction t = mConnection.BeginTransaction(); PqsqlCommand cmd = mConnection.CreateCommand(); cmd.Transaction = t; cmd.CommandText = "create or replace function pg_temp.test_out(p1 out text, i1 inout int, p2 out int, r inout refcursor) as $$begin $1 := 'p1 text'; $2:=$2*-4711; $3:=12345; open r for select * from ( values (1,2,3),(4,5,6),(7,8,9) ) X; end;$$ LANGUAGE plpgsql;"; cmd.CommandTimeout = 10; cmd.CommandType = CommandType.Text; int n = cmd.ExecuteNonQuery(); Assert.AreEqual(0, n); PqsqlParameter p1 = new PqsqlParameter("p1", DbType.String) { Direction = ParameterDirection.Output, Value = "p1_val" }; const int p2_val = 4711; PqsqlParameter p2 = new PqsqlParameter("i1", DbType.Int32, (object)p2_val) { Direction = ParameterDirection.InputOutput, }; PqsqlParameter p3 = new PqsqlParameter("p2", DbType.Int32) { Direction = ParameterDirection.Output, Value = 42 }; const string p4_val = "portal_name"; PqsqlParameter p4 = new PqsqlParameter { ParameterName = "r", PqsqlDbType = PqsqlDbType.Refcursor, Direction = ParameterDirection.InputOutput, Value = p4_val }; cmd.CommandText = "pg_temp.test_out"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); cmd.Parameters.Add(p3); cmd.Parameters.Add(p4); n = cmd.ExecuteNonQuery(); Assert.AreEqual(-1, n); Assert.AreEqual("p1 text", p1.Value); Assert.AreEqual(p2_val * -p2_val, p2.Value); Assert.AreEqual(12345, p3.Value); Assert.AreEqual(p4_val, p4.Value); cmd.CommandText = string.Format("fetch all from {0}", p4.Value); cmd.CommandType = CommandType.Text; PqsqlDataReader r = cmd.ExecuteReader(); int i = 1; while (r.Read()) { Assert.AreEqual(i++, r.GetValue(0)); Assert.AreEqual(i++, r.GetValue(1)); Assert.AreEqual(i++, r.GetValue(2)); } t.Rollback(); }
public void PqsqlCopyFromTest8() { const int n = 600; PqsqlTransaction t = mConnection.BeginTransaction(); StringBuilder sb = new StringBuilder("CREATE TEMP TABLE testcopy (c0 int4,"); for (int i = 1; i < n; i++) { if (i > 1) { sb.Append(','); } sb.AppendFormat("c{0} text", i); } sb.Append(");"); PqsqlCommand cmd = mConnection.CreateCommand(); cmd.Transaction = t; cmd.CommandText = sb.ToString(); cmd.CommandTimeout = 100; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); sb.Clear(); for (int i = 0; i < n; i++) { if (i > 0) { sb.Append(','); } sb.AppendFormat("c{0}", i); } PqsqlCopyFrom copy = new PqsqlCopyFrom(mConnection) { Table = "testcopy", ColumnList = sb.ToString(), CopyTimeout = 50 }; copy.Start(); copy.WriteInt4(1); for (int i = 1; i < n; i++) { copy.WriteText("01234567890123456789"); } try { copy.End(); } finally { copy.Close(); } Assert.Fail(); }