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 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 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 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 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 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();
}