public void PqsqlLargeObjectTest1() { PqsqlTransaction tran = mConnection.BeginTransaction(); PqsqlLargeObject lo = new PqsqlLargeObject(mConnection); uint loid = lo.Create(); Assert.IsTrue(loid > 0); lo.Open(loid, LoOpen.INV_READ | LoOpen.INV_WRITE); Assert.AreEqual(0, lo.Position); Assert.IsTrue(lo.CanSeek); byte[] b = Encoding.ASCII.GetBytes("abc"); lo.Write(b, 0, b.Length); lo.Flush(); Assert.AreEqual(3, lo.Position); Assert.AreEqual(3, lo.Length); lo.SetLength(2); Assert.AreEqual(2, lo.Length); Assert.AreEqual(2, lo.Position); lo.Position = 0; Assert.AreEqual(0, lo.Position); lo.Close(); Assert.IsTrue(lo.Unlink() >= 0); tran.Rollback(); }
public void PqsqlCopyFromTest6() { PqsqlTransaction tran = null; PqsqlCopyFrom copy = null; try { tran = mConnection.BeginTransaction(); mCmd.Transaction = tran; copy = new PqsqlCopyFrom(mConnection) { CopyTimeout = 5 }; copy.Start(); Assert.Fail(); } finally { copy?.Dispose(); tran?.Dispose(); } }
public void PqsqlTypeRegistryTest1() { PqsqlTransaction tran = mConnection.BeginTransaction(); string old_tz = mConnection.TimeZone; mCmd.CommandText = "set timezone to 'Asia/Hong_Kong';" + "select localtimestamp, now(), now() at time zone 'Europe/Vienna', '2016-10-01 12:00:00' at time zone 'UTC', '2016-10-01 12:00:00' at time zone 'Europe/Vienna', utc_offset from pg_timezone_names where name = current_setting('TIMEZONE');"; mCmd.CommandType = CommandType.Text; PqsqlDataReader reader = mCmd.ExecuteReader(); bool read = reader.Read(); Assert.IsFalse(read); string new_tz = mConnection.TimeZone; Assert.AreNotEqual(old_tz, new_tz); bool hasnext = reader.NextResult(); Assert.IsTrue(hasnext); read = reader.Read(); Assert.IsTrue(read); DateTime now0 = reader.GetDateTime(0); DateTime now1 = reader.GetDateTime(1); DateTime now2 = reader.GetDateTime(2); DateTime now3 = reader.GetDateTime(3); DateTime now4 = reader.GetDateTime(4); TimeSpan ts = reader.GetTimeSpan(5); reader.Close(); DateTime nowutc0 = now0.ToUniversalTime(); TimeZoneInfo tzi_from_china_pgsql = TimeZoneInfo.CreateCustomTimeZone(new_tz, ts, new_tz, new_tz); #if WIN32 var tz = "China Standard Time"; #else var tz = "Asia/Shanghai"; #endif TimeZoneInfo tzi_from_china_sys = TimeZoneInfo.FindSystemTimeZoneById(tz); TimeSpan china_off = tzi_from_china_sys.GetUtcOffset(nowutc0); TimeSpan local_off = TimeZoneInfo.Local.GetUtcOffset(nowutc0); DateTimeOffset nowlocal0 = (nowutc0 + local_off - china_off).ToLocalTime(); DateTimeOffset dto_from_pgsql_to_sys = TimeZoneInfo.ConvertTime(now0, tzi_from_china_pgsql, tzi_from_china_sys); DateTimeOffset dto_from_pgsql_to_localtime = TimeZoneInfo.ConvertTime(now0, tzi_from_china_pgsql, TimeZoneInfo.Local); Assert.AreEqual(nowutc0, dto_from_pgsql_to_sys); Assert.AreEqual(nowlocal0, dto_from_pgsql_to_localtime); tran.Rollback(); }
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 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 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 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 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 PqsqlCopyToTest14() { const int len = 1; PqsqlTransaction tran = mConnection.BeginTransaction(); mCmd.Transaction = tran; mCmd.CommandText = "create temporary table foo (a int4, b text, c text); " + "insert into foo values (2, 'hallo pqsql 2', null);"; mCmd.CommandType = CommandType.Text; int affected = mCmd.ExecuteNonQuery(); Assert.AreEqual(len, affected); var copy = new PqsqlCopyTo(mConnection) { Table = "foo", CopyTimeout = 10, SuppressSchemaQuery = true, }; copy.Start(); var res = copy.FetchRow(); Assert.IsTrue(res); try { copy.ReadBoolean(); } catch (InvalidOperationException) {} // try again with correct reader var a0 = copy.ReadInt4(); Assert.AreEqual(2, a0); var b0 = copy.ReadString(); Assert.AreEqual("hallo pqsql 2", b0); var c0 = copy.IsNull(); Assert.IsTrue(c0); res = copy.FetchRow(); Assert.IsFalse(res); copy.Close(); tran.Rollback(); }
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 PqsqlConnectionTest5() { PqsqlConnection connection = new PqsqlConnection(connectionString); connection.Open(); Assert.AreEqual(ConnectionState.Open, connection.State, "wrong connection state"); PqsqlTransaction trans = connection.BeginTransaction(); Assert.AreEqual(IsolationLevel.ReadCommitted, trans.IsolationLevel, "wrong transaction isolation level"); trans.Rollback(); connection.Close(); Assert.AreEqual(ConnectionState.Closed, connection.State, "wrong connection state"); }
public void PqsqlCopyToTest10() { const int len = 2; PqsqlTransaction tran = mConnection.BeginTransaction(); mCmd.Transaction = tran; mCmd.CommandText = "create temporary table foo (a bytea); " + "insert into foo values (null); " + "insert into foo values ('\\x707173716C'); "; mCmd.CommandType = CommandType.Text; int affected = mCmd.ExecuteNonQuery(); Assert.AreEqual(len, affected); var copy = new PqsqlCopyTo(mConnection) { Table = "foo", ColumnList = "a", CopyTimeout = 10, }; copy.Start(); var res = copy.FetchRow(); Assert.IsTrue(res); var a0 = copy.ReadRaw(); Assert.IsNull(a0); res = copy.FetchRow(); Assert.IsTrue(res); var a1 = copy.ReadRaw(); CollectionAssert.AreEqual(new byte[] { 0x70, 0x71, 0x73, 0x71, 0x6C }, a1); res = copy.FetchRow(); Assert.IsFalse(res); copy.Close(); 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 PqsqlCopyToTest11() { const int len = 1; PqsqlTransaction tran = mConnection.BeginTransaction(); mCmd.Transaction = tran; mCmd.CommandText = "create temporary table foo (a text); " + "insert into foo values ('hallo pqsql');"; mCmd.CommandType = CommandType.Text; int affected = mCmd.ExecuteNonQuery(); Assert.AreEqual(len, affected); var copy = new PqsqlCopyTo(mConnection) { Table = "foo", ColumnList = "a", CopyTimeout = 10, }; copy.Start(); var res = copy.FetchRow(); Assert.IsTrue(res); var a0 = copy.ReadString(); Assert.AreEqual("hallo pqsql", a0); res = copy.FetchRow(); Assert.IsFalse(res); copy.Close(); tran.Rollback(); }
public void PqsqlCopyFromTest7() { PqsqlTransaction tran = null; PqsqlCopyFrom copy = null; try { tran = mConnection.BeginTransaction(); mCmd.Transaction = tran; mCmd.CommandText = "CREATE TEMP TABLE temp (id int4, val int4, txt text)"; mCmd.CommandTimeout = 200; mCmd.CommandType = CommandType.Text; int q = mCmd.ExecuteNonQuery(); Assert.AreEqual(0, q); copy = new PqsqlCopyFrom(mConnection) { Table = "temp", CopyTimeout = 5 }; copy.WriteInt4(42); // write without Start() copy.WriteInt4(42); // write without Start() copy.WriteText("42"); // write without Start() copy.End(); copy.Close(); Assert.Fail(); } finally { copy?.Dispose(); tran?.Dispose(); } }
public void PqsqlCopyFromTest4() { PqsqlTransaction tran = null; PqsqlCopyFrom copy = null; try { tran = mConnection.BeginTransaction(); mCmd.Transaction = tran; mCmd.CommandText = "CREATE TEMP TABLE temp (id int4, val int4, txt text)"; mCmd.CommandTimeout = 200; mCmd.CommandType = CommandType.Text; int q = mCmd.ExecuteNonQuery(); Assert.AreEqual(0, q); copy = new PqsqlCopyFrom(mConnection) { Table = "temp", CopyTimeout = 1 }; copy.Start(); System.Threading.Thread.Sleep(1500); copy.End(); Assert.Fail(); } finally { copy?.Dispose(); tran?.Dispose(); } }
public void PqsqlCopyFromTest9() { PqsqlTransaction tran = null; PqsqlCopyFrom copy = null; try { tran = mConnection.BeginTransaction(); mCmd.Transaction = tran; mCmd.CommandText = "CREATE TEMP TABLE temp (col timestamp)"; mCmd.CommandTimeout = 200; mCmd.CommandType = CommandType.Text; int q = mCmd.ExecuteNonQuery(); Assert.AreEqual(0, q); copy = new PqsqlCopyFrom(mConnection) { Table = "temp", CopyTimeout = 5 }; copy.Start(); copy.WriteDate(DateTime.UtcNow); copy.End(); copy.Close(); Assert.Fail(); } finally { copy?.Dispose(); tran?.Dispose(); } }
public void PqsqlCopyToTest2() { const int len = 1; PqsqlTransaction tran = mConnection.BeginTransaction(); mCmd.Transaction = tran; mCmd.CommandText = "create temporary table foo (a int4, b int4, c int4); " + "insert into foo values (null, 2, 3); "; mCmd.CommandType = CommandType.Text; int affected = mCmd.ExecuteNonQuery(); Assert.AreEqual(len, affected); var copy = new PqsqlCopyTo(mConnection) { Table = "foo", ColumnList = "c,a,b", CopyTimeout = 10, }; copy.Start(); var results = new int?[len]; while (copy.FetchRow()) { for (int i = 0; i < len; i++) { int?result; if (copy.IsNull()) { result = null; } else { result = copy.ReadInt4(); } if (i == 0) { // c Assert.IsTrue(result.HasValue); Assert.AreEqual(3, result.Value); } else if (i == 1) { // a Assert.IsFalse(result.HasValue); } else if (i == 2) { // b Assert.IsTrue(result.HasValue); Assert.AreEqual(2, result.Value); } } } copy.Close(); tran.Rollback(); }
public void PqsqlCopyToTest12() { const int len = 3; PqsqlTransaction tran = mConnection.BeginTransaction(); mCmd.Transaction = tran; mCmd.CommandText = "create temporary table foo (a int4, b text, c text); " + "insert into foo values (2, 'hallo pqsql 2', null); " + "insert into foo values (1, 'hallo pqsql 1', 'asd'); " + "insert into foo values (3, 'hallo pqsql 3', null);"; mCmd.CommandType = CommandType.Text; int affected = mCmd.ExecuteNonQuery(); Assert.AreEqual(len, affected); var copy = new PqsqlCopyTo(mConnection) { Query = "select a,b from foo order by a asc; ", CopyTimeout = 10, }; copy.Start(); var res = copy.FetchRow(); Assert.IsTrue(res); var a0 = copy.ReadInt4(); Assert.AreEqual(1, a0); var b0 = copy.ReadString(); Assert.AreEqual("hallo pqsql 1", b0); res = copy.FetchRow(); Assert.IsTrue(res); var a1 = copy.ReadInt4(); Assert.AreEqual(2, a1); var b1 = copy.ReadString(); Assert.AreEqual("hallo pqsql 2", b1); res = copy.FetchRow(); Assert.IsTrue(res); var a2 = copy.ReadInt4(); Assert.AreEqual(3, a2); var b2 = copy.ReadString(); Assert.AreEqual("hallo pqsql 3", b2); res = copy.FetchRow(); Assert.IsFalse(res); copy.Close(); tran.Rollback(); }
public void PqsqlCommandTest10() { StringBuilder sb = new StringBuilder(); const int N = 1664; // postgres can handle at most 1664 columns in a select const int K = 5; // create K*(K+1) / 2 queries PqsqlParameter[] pars = new PqsqlParameter[0]; for (int k = 1; k <= K; k++) { if (k > 1) { sb.Append(';'); } sb.Append("select "); Array.Resize(ref pars, k * N); using (PqsqlTransaction t = mConnection.BeginTransaction()) { for (int i = 1; i <= N; i++) { int j = (k - 1) * N + i - 1; if (i > 1) { sb.Append(','); } sb.Append("generate_series(:p" + j + ",:p" + j + ")"); PqsqlParameter p = new PqsqlParameter { ParameterName = "p" + j, DbType = DbType.Int32, Value = j }; pars[j] = p; } sb.Append(';'); using (PqsqlCommand cmd = mConnection.CreateCommand()) { cmd.Transaction = t; cmd.CommandText = sb.ToString(); cmd.CommandTimeout = 20; cmd.CommandType = CommandType.Text; cmd.Parameters.AddRange(pars.Take(k * N).ToArray()); using (PqsqlDataReader reader = cmd.ExecuteReader()) { for (int n = 0; n < k; n++) { while (reader.Read()) { for (int m = 0; m < N; m++) { int o = reader.GetInt32(m); Assert.AreEqual(n * N + m, o); } } reader.NextResult(); } } } } } }
public void PqsqlCommandTest9() { PqsqlTransaction t = mConnection.BeginTransaction(); PqsqlCommand cmd = mConnection.CreateCommand(); cmd.Transaction = t; cmd.CommandText = @"create or replace function ""pg_temp"".""test me""(i int) returns int as $code$ begin return $1 * i; end; $code$ language plpgsql; select ""pg_temp"".""test me""(:p1); select ""pg_temp"".""test me""($1)" ; cmd.CommandTimeout = 2; cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("p1", 4711); using (PqsqlDataReader r = cmd.ExecuteReader()) { bool good = r.Read(); Assert.IsFalse(good); good = r.NextResult(); Assert.IsTrue(good); good = r.Read(); Assert.IsTrue(good); int n = r.GetInt32(0); Assert.AreEqual(4711 * 4711, n); good = r.Read(); Assert.IsFalse(good); good = r.NextResult(); Assert.IsTrue(good); good = r.Read(); Assert.IsTrue(good); n = r.GetInt32(0); Assert.AreEqual(4711 * 4711, n); good = r.Read(); Assert.IsFalse(good); } cmd.CommandText = "\"pg_temp\".\"test me\""; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("i", 4711); cmd.Parameters.Add(new PqsqlParameter { ParameterName = "\"pg_temp\".\"test me\"", DbType = DbType.Int32, Direction = ParameterDirection.Output }); object x = cmd.ExecuteScalar(); Assert.AreEqual(4711 * 4711, x); t.Rollback(); }
public void PqsqlCopyToTest9() { const int len = 5; // the last 8 ticks (800 nanoseconds) of MinValue can not be stored in postgres' interval type, because of // its resolution to milliseconds. var intervalMinValue = new TimeSpan(TimeSpan.MinValue.Ticks + 8); // the last 7 ticks (700 nanoseconds) of MaxValue can not be stored in postgres' interval type, because of // its resolution to milliseconds. var intervalMaxValue = new TimeSpan(TimeSpan.MaxValue.Ticks - 7); PqsqlTransaction tran = mConnection.BeginTransaction(); mCmd.Transaction = tran; mCmd.CommandText = "create temporary table foo (a interval); " + "insert into foo values (null); " + "insert into foo values ('10675199 02:48:05.477580 ago'); " + "insert into foo values ('10675199 02:48:05.477580'); " + "insert into foo values ('10675199 02:48:05.477581 ago'); " + "insert into foo values ('10675199 02:48:05.477581'); "; mCmd.CommandType = CommandType.Text; int affected = mCmd.ExecuteNonQuery(); Assert.AreEqual(len, affected); var copy = new PqsqlCopyTo(mConnection) { Table = "foo", ColumnList = "a", CopyTimeout = 10, }; copy.Start(); var res = copy.FetchRow(); Assert.IsTrue(res); var a0 = copy.ReadInterval(); Assert.AreEqual(TimeSpan.MinValue, a0); res = copy.FetchRow(); Assert.IsTrue(res); var a1 = copy.ReadInterval(); Assert.AreEqual(intervalMinValue, a1); res = copy.FetchRow(); Assert.IsTrue(res); var a2 = copy.ReadInterval(); Assert.AreEqual(intervalMaxValue, a2); res = copy.FetchRow(); Assert.IsTrue(res); try { // a3 '10675199 02:48:05.477581 ago' var a3 = copy.ReadInterval(); Assert.Fail(); } catch (ArgumentOutOfRangeException) {} res = copy.FetchRow(); Assert.IsTrue(res); try { // a4 '10675199 02:48:05.477581' copy.ReadInterval(); Assert.Fail(); } catch (ArgumentOutOfRangeException) {} res = copy.FetchRow(); Assert.IsFalse(res); 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 PqsqlLargeObjectTest3() { PqsqlTransaction t = mConnection.BeginTransaction(); PqsqlLargeObject low = new PqsqlLargeObject(mConnection); uint oid = low.Create(); Assert.IsTrue(oid > 0); const int size = 20; byte[] wbuf = new byte[size]; byte[] rbuf = new byte[size]; for (int i = size - 1; i > 10; i--) { wbuf[i] = (byte)(i - 10); } const int offset = 10; const int len = 10; int fd = low.Open(oid, (LoOpen.INV_READ | LoOpen.INV_WRITE)); Assert.IsTrue(fd >= 0); long wtell = low.Position; Assert.AreEqual(0, wtell); low.Write(wbuf, offset, len); wtell = low.Position; Assert.AreEqual(len, wtell); low.Write(wbuf, offset, len); wtell = low.Position; Assert.AreEqual(2 * len, wtell); low.Close(); t.Commit(); t = mConnection.BeginTransaction(); PqsqlLargeObject lor = new PqsqlLargeObject(mConnection); fd = lor.Open(oid, LoOpen.INV_READ); Assert.IsTrue(fd >= 0); long rtell = lor.Position; Assert.AreEqual(0, rtell); int read = lor.Read(rbuf, 0, len); Assert.AreEqual(len, read); rtell = lor.Position; Assert.AreEqual(len, rtell); read = lor.Read(rbuf, read, len); Assert.AreEqual(len, read); rtell = lor.Position; Assert.AreEqual(2 * len, rtell); lor.Close(); int ret = low.Unlink(oid); t.Commit(); Assert.IsTrue(ret >= 0); for (int i = 0; i < rbuf.Length; i++) { Assert.AreEqual(wbuf[(i % 10) + 10], rbuf[i]); } // oid must be gone now try { fd = lor.Open(oid, LoOpen.INV_READ); Assert.Fail(); } catch (PqsqlException e) { Assert.IsNotNull(e); } }
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(); }
public void PqsqlCopyToTest8() { const int len = 8; var dateMaxValue = DateTime.MaxValue.Date; PqsqlTransaction tran = mConnection.BeginTransaction(); mCmd.Transaction = tran; mCmd.CommandText = "create temporary table foo (a date); " + "insert into foo values (null); " + "insert into foo values ('0001-01-01'); " + "insert into foo values ('1970-01-01'); " + "insert into foo values ('9999-12-31'); " + "insert into foo values ('31 December, 1 BC'); " + "insert into foo values ('10000-01-01'); " + "insert into foo values ('-infinity'); " + "insert into foo values ('infinity'); "; mCmd.CommandType = CommandType.Text; int affected = mCmd.ExecuteNonQuery(); Assert.AreEqual(len, affected); var copy = new PqsqlCopyTo(mConnection) { Table = "foo", ColumnList = "a", CopyTimeout = 10, }; copy.Start(); var res = copy.FetchRow(); Assert.IsTrue(res); var a0 = copy.ReadDate(); Assert.AreEqual(DateTime.MinValue, a0); res = copy.FetchRow(); Assert.IsTrue(res); var a1 = copy.ReadDate(); Assert.AreEqual(DateTime.MinValue, a1); res = copy.FetchRow(); Assert.IsTrue(res); var a2 = copy.ReadDate(); Assert.AreEqual(DateTime.UnixEpoch, a2); res = copy.FetchRow(); Assert.IsTrue(res); var a3 = copy.ReadDate(); Assert.AreEqual(dateMaxValue, a3); res = copy.FetchRow(); Assert.IsTrue(res); try { // a4 '31 December, 1 BC' copy.ReadDate(); Assert.Fail(); } catch (ArgumentOutOfRangeException) {} res = copy.FetchRow(); Assert.IsTrue(res); try { // a5 '10000-01-01' copy.ReadDate(); Assert.Fail(); } catch (ArgumentOutOfRangeException) {} res = copy.FetchRow(); Assert.IsTrue(res); try { // a6 '-infinity' copy.ReadDate(); Assert.Fail(); } catch (ArgumentOutOfRangeException) {} res = copy.FetchRow(); Assert.IsTrue(res); try { // a7 'infinity' copy.ReadDate(); Assert.Fail(); } catch (ArgumentOutOfRangeException) {} res = copy.FetchRow(); Assert.IsFalse(res); copy.Close(); tran.Rollback(); }
public void PqsqlCopyFromTest2() { PqsqlTransaction tran = mConnection.BeginTransaction(); mCmd.Transaction = tran; mCmd.CommandText = "CREATE TEMP TABLE temp (id int4, val int4, txt text)"; mCmd.CommandTimeout = 200; mCmd.CommandType = CommandType.Text; const int upperbound = 2000000; const string text = "text value with ü and ä "; int q = mCmd.ExecuteNonQuery(); Assert.AreEqual(0, q); PqsqlCopyFrom copy = new PqsqlCopyFrom(mConnection) { Table = "temp", CopyTimeout = 30 }; copy.Start(); for (int i = 0; i < upperbound; i++) { int j = copy.WriteInt4(i); Assert.AreEqual(4, j); j = copy.WriteInt4(i); Assert.AreEqual(4, j); j = copy.WriteText(text + i); Assert.AreEqual(Encoding.UTF8.GetByteCount(text + i), j); // length without nul byte } copy.WriteNull(); // id copy.WriteNull(); // val copy.WriteNull(); // txt copy.End(); copy.Close(); mCmd.CommandText = "select * from temp"; mCmd.CommandTimeout = 30; mCmd.CommandType = CommandType.Text; PqsqlDataReader r = mCmd.ExecuteReader(); int n = 0; int k = 0; while (r.Read()) { if (n++ == upperbound) { Assert.IsTrue(r.IsDBNull(0)); Assert.IsTrue(r.IsDBNull(1)); Assert.IsTrue(r.IsDBNull(2)); } else { Assert.AreEqual(k, r.GetInt32(0)); Assert.AreEqual(k, r.GetInt32(1)); Assert.AreEqual(text + k, r.GetString(2)); k++; } } Assert.AreEqual(upperbound + 1, n); r.Close(); tran.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(); }