public void PqsqlCommandTest2()
{
PqsqlCommand cmd = mConnection.CreateCommand();
cmd.CommandText = "bit_length";
cmd.CommandType = CommandType.StoredProcedure;
IDbDataParameter i = cmd.CreateParameter();
i.ParameterName = "i";
i.Value = "postgres";
cmd.Parameters.Add(i);
IDbDataParameter o = cmd.CreateParameter();
o.ParameterName = "bit_length";
o.Direction = ParameterDirection.Output;
cmd.Parameters.Add(o);
PqsqlDataReader r = cmd.ExecuteReader();
bool b = r.Read();
Assert.AreEqual(true, b);
Assert.AreEqual(64, o.Value);
}
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 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 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 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 PqsqlConnectionStringBuilderTest1()
{
PqsqlConnectionStringBuilder builder = new PqsqlConnectionStringBuilder(connectionString);
builder[PqsqlConnectionStringBuilder.keepalives] = "1";
builder[PqsqlConnectionStringBuilder.keepalives_idle] = "23";
builder[PqsqlConnectionStringBuilder.keepalives_count] = "3";
builder[PqsqlConnectionStringBuilder.keepalives_interval] = "3";
string dataSource;
using (PqsqlConnection connection = new PqsqlConnection(builder))
{
// closed connection with service file should give us empty data source
Assert.IsTrue(string.IsNullOrEmpty(connection.DataSource));
using (PqsqlCommand cmd = new PqsqlCommand("show all", connection))
using (PqsqlDataReader r = cmd.ExecuteReader())
{
object value;
if (builder.TryGetValue(PqsqlConnectionStringBuilder.host, out value))
{
Assert.AreEqual(connection.DataSource, value);
dataSource = value.ToString();
}
else // no datasource specified
{
dataSource = connection.DataSource;
}
cmd.Cancel();
}
}
builder[PqsqlConnectionStringBuilder.host] = dataSource;
using (PqsqlConnection connection = new PqsqlConnection(builder))
using (PqsqlCommand cmd = new PqsqlCommand("show all", connection))
using (PqsqlDataReader r = cmd.ExecuteReader())
{
object value;
if (builder.TryGetValue(PqsqlConnectionStringBuilder.host, out value))
{
Assert.AreEqual(connection.DataSource, value);
}
else
{
Assert.Fail("host part is not available");
}
cmd.Cancel();
}
}
public void PqsqlCommandTest19()
{
using (PqsqlCommand cmd =
new PqsqlCommand("select state from pg_stat_activity -- no semicolon", mConnection)
{
CommandType = CommandType.Text
})
{
PqsqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
string s = !r.IsDBNull(0) ? r.GetString(0) : null;
}
}
}
public void PqsqlCommandTest17()
{
using (PqsqlCommand cmd =
new PqsqlCommand("select /*state*/'x' from /* xxx */ pg_stat_activity ", mConnection)
{
CommandType = CommandType.Text
})
{
PqsqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
string s = !r.IsDBNull(0) ? r.GetString(0) : null;
}
}
}
public void PqsqlCommandTest13()
{
PqsqlCommand cmd = new PqsqlCommand("select :貓1;", mConnection)
{
CommandType = CommandType.Text
};
// add non-standard parametername
cmd.Parameters.AddWithValue(":貓1", "喵");
using (PqsqlDataReader r = cmd.ExecuteReader())
{
Assert.Fail("ExecuteReader() must fail");
}
}
public void PqsqlCommandTest1()
{
PqsqlCommand cmd = mConnection.CreateCommand();
cmd.CommandText = "select pg_sleep(1);";
PqsqlDataReader r = cmd.ExecuteReader();
bool b = r.Read();
Assert.AreEqual(true, b);
object v = r.GetValue(0);
Assert.AreEqual("", v);
}
public void PqsqlCommandTest11()
{
PqsqlCommand cmd = new PqsqlCommand("select :p1;", mConnection);
cmd.CommandType = CommandType.Text;
// recursive parameters
PqsqlParameter p1 = cmd.Parameters.AddWithValue(":p1", ":p1");
PqsqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
string s = r.GetString(0);
Assert.AreEqual(p1.Value, s);
}
}
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 PqsqlCommandTest12()
{
PqsqlCommand cmd = new PqsqlCommand("select :p1;", mConnection)
{
CommandType = CommandType.Text,
Parameters =
{
// do not add parameter :p1, add p0 and p2 instead
new PqsqlParameter(":p0", DbType.String, "p0value"),
new PqsqlParameter(":p2", DbType.String, "p2value")
}
};
using (PqsqlDataReader r = cmd.ExecuteReader())
{
Assert.Fail("ExecuteReader() must fail");
}
}
public void PqsqlCommandTest5()
{
PqsqlCommand cmd = mConnection.CreateCommand();
cmd.CommandText = "pg_authid";
cmd.CommandTimeout = 10;
cmd.CommandType = CommandType.TableDirect;
var r = cmd.ExecuteReader();
foreach (var o in r)
{
Assert.IsNotNull(o);
}
cmd.Cancel();
mConnection.Close();
mConnection.Open();
}
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 PqsqlCommandTest4()
{
PqsqlCommand cmd = new PqsqlCommand("generate_series", mConnection);
const int p1_val = -1;
const int p2_val = 2;
PqsqlParameter p1 = cmd.CreateParameter();
p1.ParameterName = "p1";
p1.Value = p1_val;
p1.DbType = DbType.Int32;
PqsqlParameter p2 = cmd.CreateParameter();
p2.ParameterName = "p2";
p2.Value = p2_val;
p2.DbType = DbType.Int32;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(new PqsqlParameter("generate_series", DbType.Int32)
{
Direction = ParameterDirection.Output
});
PqsqlDataReader r = cmd.ExecuteReader();
int n = p1_val - 1;
int j = p1_val;
while (r.Read())
{
int i = r.GetInt32(0);
Assert.AreEqual(j++, i);
n++;
}
Assert.AreEqual(p2_val, n);
}
public void PqsqlCommandTest3()
{
PqsqlCommand cmd = mConnection.CreateCommand();
cmd.CommandText = "upper";
cmd.CommandType = CommandType.StoredProcedure;
IDbDataParameter i = cmd.CreateParameter();
i.ParameterName = "i";
i.Value = "upper";
cmd.Parameters.Add(i);
IDbDataParameter o = cmd.CreateParameter();
o.ParameterName = "output_parametername_does_not_exist";
o.Direction = ParameterDirection.Output;
cmd.Parameters.Add(o);
cmd.ExecuteReader();
Assert.Fail();
}
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 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 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 TestInitialize()
{
mConnection = new PqsqlConnection(connectionString);
mCmd = mConnection.CreateCommand();
}
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 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();
}
}
}
}
}
}
