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 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 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 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 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 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 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();
}
public void PqsqlCopyToTest7()
{
const int len = 5;
var timeMinValue = DateTime.UnixEpoch;
var timeMaxValue = DateTime.UnixEpoch.AddDays(1);
var timeTzMinValue = new DateTimeOffset(1970, 1, 1, 0, 0, 0, new TimeSpan(14, 0, 0));
var timeTzMaxValue = new DateTimeOffset(1970, 1, 2, 0, 0, 0, new TimeSpan(-14, 0, 0));
PqsqlTransaction tran = mConnection.BeginTransaction();
mCmd.Transaction = tran;
mCmd.CommandText = "create temporary table foo (a time, b timetz); " +
"insert into foo values (null, null); " +
"insert into foo values ('00:00:00', '00:00:00+14'); " +
"insert into foo values ('24:00:00', '24:00:00-14'); " +
"insert into foo values (null, '00:00:00+14:01'); " +
"insert into foo values (null, '24:00:00-14:01'); ";
mCmd.CommandType = CommandType.Text;
int affected = mCmd.ExecuteNonQuery();
Assert.AreEqual(len, affected);
var copy = new PqsqlCopyTo(mConnection)
{
Table = "foo",
ColumnList = "a,b",
CopyTimeout = 10,
};
copy.Start();
var res = copy.FetchRow();
Assert.IsTrue(res);
var a0 = copy.ReadTime();
Assert.AreEqual(DateTime.MinValue, a0);
var b0 = copy.ReadTimeTZ();
Assert.AreEqual(DateTimeOffset.MinValue, b0);
res = copy.FetchRow();
Assert.IsTrue(res);
var a1 = copy.ReadTime();
Assert.AreEqual(timeMinValue, a1);
var b1 = copy.ReadTimeTZ();
Assert.AreEqual(timeTzMinValue, b1);
res = copy.FetchRow();
Assert.IsTrue(res);
var a2 = copy.ReadTime();
Assert.AreEqual(timeMaxValue, a2);
var b2 = copy.ReadTimeTZ();
Assert.AreEqual(timeTzMaxValue, b2);
res = copy.FetchRow();
Assert.IsTrue(res);
// a3
copy.ReadTime();
try
{
// b3 '00:00:00+14:01'
copy.ReadTimeTZ();
Assert.Fail();
}
catch (ArgumentOutOfRangeException) {}
res = copy.FetchRow();
Assert.IsTrue(res);
// a4
copy.ReadTime();
try
{
// b4 '24:00:00-14:01'
copy.ReadTimeTZ();
Assert.Fail();
}
catch (ArgumentOutOfRangeException) {}
res = copy.FetchRow();
Assert.IsFalse(res);
copy.Close();
tran.Rollback();
}
public void PqsqlCopyToTest4()
{
const int len = 3;
PqsqlTransaction tran = mConnection.BeginTransaction();
mCmd.Transaction = tran;
mCmd.CommandText = "create temporary table foo (a int2, b int4, c int8); " +
"insert into foo values (null, null, null); " +
"insert into foo values (-32768, -2147483648, -9223372036854775808); " +
"insert into foo values (32767, 2147483647, 9223372036854775807); ";
mCmd.CommandType = CommandType.Text;
int affected = mCmd.ExecuteNonQuery();
Assert.AreEqual(len, affected);
var copy = new PqsqlCopyTo(mConnection)
{
Table = "foo",
ColumnList = "a,b,c",
CopyTimeout = 10,
};
copy.Start();
var res = copy.FetchRow();
Assert.IsTrue(res);
var a0 = copy.ReadInt2();
Assert.AreEqual(0, a0);
var b0 = copy.ReadInt4();
Assert.AreEqual(0, b0);
var c0 = copy.ReadInt8();
Assert.AreEqual(0, c0);
res = copy.FetchRow();
Assert.IsTrue(res);
var a1 = copy.ReadInt2();
Assert.AreEqual(short.MinValue, a1);
var b1 = copy.ReadInt4();
Assert.AreEqual(int.MinValue, b1);
var c1 = copy.ReadInt8();
Assert.AreEqual(long.MinValue, c1);
res = copy.FetchRow();
Assert.IsTrue(res);
var a2 = copy.ReadInt2();
Assert.AreEqual(short.MaxValue, a2);
var b2 = copy.ReadInt4();
Assert.AreEqual(int.MaxValue, b2);
var c2 = copy.ReadInt8();
Assert.AreEqual(long.MaxValue, c2);
res = copy.FetchRow();
Assert.IsFalse(res);
copy.Close();
tran.Rollback();
}
public void PqsqlCopyToTest5()
{
const int len = 3;
PqsqlTransaction tran = mConnection.BeginTransaction();
mCmd.Transaction = tran;
mCmd.CommandText = "create temporary table foo (a float4, b float8); " +
"insert into foo values (null, null); " +
"insert into foo values (-3.40282347E+38, -1.7976931348623157E+308); " +
"insert into foo values (3.40282347E+38, 1.7976931348623157E+308); ";
mCmd.CommandType = CommandType.Text;
int affected = mCmd.ExecuteNonQuery();
Assert.AreEqual(len, affected);
var copy = new PqsqlCopyTo(mConnection)
{
Table = "foo",
ColumnList = "a,b",
CopyTimeout = 10,
};
copy.Start();
var res = copy.FetchRow();
Assert.IsTrue(res);
var a0 = copy.ReadFloat4();
Assert.AreEqual(0, a0);
var b0 = copy.ReadFloat8();
Assert.AreEqual(0, b0);
res = copy.FetchRow();
Assert.IsTrue(res);
var a1 = copy.ReadFloat4();
Assert.AreEqual(float.MinValue, a1);
var b1 = copy.ReadFloat8();
Assert.AreEqual(double.MinValue, b1);
res = copy.FetchRow();
Assert.IsTrue(res);
var a2 = copy.ReadFloat4();
Assert.AreEqual(float.MaxValue, a2);
var b2 = copy.ReadFloat8();
Assert.AreEqual(double.MaxValue, b2);
res = copy.FetchRow();
Assert.IsFalse(res);
copy.Close();
tran.Rollback();
}
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 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 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 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 PqsqlCopyToTest3()
{
const int len = 1;
PqsqlTransaction tran = mConnection.BeginTransaction();
mCmd.Transaction = tran;
mCmd.CommandText = "create temporary table foo (a int2, b int4, c int8, d boolean, e boolean, f float4, " +
"g float8, h text, i timestamp, j time, k timetz, l timetz, m date, n interval); " +
"insert into foo values (5, 1000001, 42949672950, true, false, 3.14, 3.14, 'hallo 1', " +
"TIMESTAMP '1999-01-08 04:05:06', '04:05:06.789', '04:05:06-08:00', '04:05:06+08:00', " +
"'1999-01-08', '3 4:05:06');";
mCmd.CommandType = CommandType.Text;
int affected = mCmd.ExecuteNonQuery();
Assert.AreEqual(len, affected);
var copy = new PqsqlCopyTo(mConnection)
{
Table = "foo",
ColumnList = "a,b,c,d,e,f,g,h,i,j,k,l,m,n",
CopyTimeout = 10,
};
copy.Start();
while (copy.FetchRow())
{
var a = copy.ReadInt2();
Assert.AreEqual(5, a);
var b = copy.ReadInt4();
Assert.AreEqual(1000001, b);
var c = copy.ReadInt8();
Assert.AreEqual(42949672950, c);
var d = copy.ReadBoolean();
Assert.IsTrue(d);
var e = copy.ReadBoolean();
Assert.IsFalse(e);
var f = copy.ReadFloat4();
Assert.AreEqual(3.14, f, 0.00001);
var g = copy.ReadFloat8();
Assert.AreEqual(3.14, g, 0.00001);
var h = copy.ReadString();
Assert.AreEqual("hallo 1", h);
var i = copy.ReadTimestamp();
Assert.AreEqual(new DateTime(1999, 1, 8, 4, 5, 6), i);
var j = copy.ReadTime();
Assert.AreEqual(new DateTime(1970, 1, 1, 4, 5, 6, 789), j);
var k = copy.ReadTimeTZ();
Assert.AreEqual(new DateTimeOffset(1970, 1, 1, 4, 5, 6, 0, new TimeSpan(-8, 0, 0)), k);
var l = copy.ReadTimeTZ();
Assert.AreEqual(new DateTimeOffset(1970, 1, 1, 4, 5, 6, 0, new TimeSpan(8, 0, 0)), l);
var m = copy.ReadDate();
Assert.AreEqual(new DateTime(1999, 1, 8), m);
var n = copy.ReadInterval();
Assert.AreEqual(new TimeSpan(3, 4, 5, 6), n);
}
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 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 PqsqlCopyToTest6()
{
const int len = 8;
// the highest representable System.DateTime tick in postgres is 9 less than
// System.DateTime.MaxValue.Ticks, because the resolution in postgres is to the millisecond whereas
// System.DateTime's resolution is to 100 nanoseconds. This means the last 900 nanoseconds before the year
// 10000 cannot be represented in postgres' timestamp.
var postgresDateTimeMaxValueTicks = DateTime.MaxValue.Ticks - 9;
PqsqlTransaction tran = mConnection.BeginTransaction();
mCmd.Transaction = tran;
mCmd.CommandText = "create temporary table foo (a timestamp, b timestamptz); " +
"insert into foo values (null, null); " +
"insert into foo values ('0001-01-01 00:00:00.000000 UTC', '0001-01-01 00:00:00.000000 UTC'); " +
"insert into foo values ('1970-01-01 00:00:00.000000 UTC', '1970-01-01 00:00:00.000000 UTC'); " +
"insert into foo values ('9999-12-31 23:59:59.999999 UTC', '9999-12-31 23:59:59.999999 UTC'); " +
"insert into foo values ('31 December, 1 BC 23:59:59.999999', '31 December, 1 BC 23:59:59.999999'); " +
"insert into foo values ('10000-01-01 00:00:00.000000 UTC', '10000-01-01 00:00:00.000000 UTC'); " +
"insert into foo values ('-infinity', '-infinity'); " +
"insert into foo values ('infinity', 'infinity'); ";
mCmd.CommandType = CommandType.Text;
int affected = mCmd.ExecuteNonQuery();
Assert.AreEqual(len, affected);
var copy = new PqsqlCopyTo(mConnection)
{
Table = "foo",
ColumnList = "a,b",
CopyTimeout = 10,
};
copy.Start();
var res = copy.FetchRow();
Assert.IsTrue(res);
var a0 = copy.ReadTimestamp();
Assert.AreEqual(DateTime.MinValue, a0);
var b0 = copy.ReadTimestamp();
Assert.AreEqual(DateTime.MinValue, b0);
res = copy.FetchRow();
Assert.IsTrue(res);
var a1 = copy.ReadTimestamp();
Assert.AreEqual(DateTime.MinValue, a1);
var b1 = copy.ReadTimestamp();
Assert.AreEqual(DateTime.MinValue, b1);
res = copy.FetchRow();
Assert.IsTrue(res);
var a2 = copy.ReadTimestamp();
Assert.AreEqual(DateTime.UnixEpoch, a2);
var b2 = copy.ReadTimestamp();
Assert.AreEqual(DateTime.UnixEpoch, b2);
res = copy.FetchRow();
Assert.IsTrue(res);
var a3 = copy.ReadTimestamp();
Assert.AreEqual(new DateTime(postgresDateTimeMaxValueTicks), a3);
var b3 = copy.ReadTimestamp();
Assert.AreEqual(new DateTime(postgresDateTimeMaxValueTicks), b3);
res = copy.FetchRow();
Assert.IsTrue(res);
try
{
// a4 '31 December, 1 BC 23:59:59.999999'
copy.ReadTimestamp();
Assert.Fail();
}
catch (ArgumentOutOfRangeException) {}
try
{
// b4 '31 December, 1 BC 23:59:59.999999'
copy.ReadTimestamp();
Assert.Fail();
}
catch (ArgumentOutOfRangeException) {}
res = copy.FetchRow();
Assert.IsTrue(res);
try
{
// a5 '10000-01-01 00:00:00.000000 UTC'
copy.ReadTimestamp();
Assert.Fail();
}
catch (ArgumentOutOfRangeException) {}
try
{
// b5 '10000-01-01 00:00:00.000000 UTC'
copy.ReadTimestamp();
Assert.Fail();
}
catch (ArgumentOutOfRangeException) {}
res = copy.FetchRow();
Assert.IsTrue(res);
// a6 '-infinity'
var a6 = copy.ReadTimestamp();
Assert.AreEqual(DateTime.MinValue, a6);
// b6 '-infinity'
var b6 = copy.ReadTimestamp();
Assert.AreEqual(DateTime.MinValue, b6);
res = copy.FetchRow();
Assert.IsTrue(res);
// a7 'infinity'
var a7 = copy.ReadTimestamp();
Assert.AreEqual(DateTime.MaxValue, a7);
// b7 'infinity'
var b7 = copy.ReadTimestamp();
Assert.AreEqual(DateTime.MaxValue, b7);
res = copy.FetchRow();
Assert.IsFalse(res);
copy.Close();
tran.Rollback();
}