I have a table with a varchar field in it (and it accepts nulls). For testing purposes let's call it moo. Here is the table structure:
CREATE TABLE moo
(
id serial NOT NULL,
moo_name character varying(30),
test_name character varying(30)
)
WITH (OIDS=FALSE);
ALTER TABLE moo OWNER TO postgres;
When I first connect to the database, I send a cmd to transform null equals -- via:
cmd.CommandText = "set transform_null_equals to on;";
cmd.ExecuteNonQuery();
this all works fine. However, I cannot seem to get my queries to return data when I use parameters (and nulls). See the follow test code that will demo the problem.
(assume a winform app with two buttons and a multiline textbox)
private void button1_Click(object sender, EventArgs e)
{
pgSqlConnection1.Open();
try
{
PgSqlCommand cmd1 = new PgSqlCommand();
cmd1.Connection = pgSqlConnection1;
cmd1.CommandText = "set transform_null_equals to on;";
cmd1.ExecuteNonQuery();
PgSqlCommand cmd2 = new PgSqlCommand();
cmd2.Connection = pgSqlConnection1;
cmd2.CommandText = "select moo_name from moo where test_name = :test_name";
PgSqlParameter param = new PgSqlParameter();
param.PgSqlType = PgSqlType.VarChar;
param.IsNullable = true;
param.Direction = ParameterDirection.Input;
param.Value = DBNull.Value;
param.ParameterName = "test_name";
// param.PgSqlValue = DBNull.Value; <---tried! does not work!
// param.Value = null; <----tried! does not work!
cmd2.Parameters.Add(param);
StringBuilder sb = new StringBuilder();
using (PgSqlDataReader reader = cmd2.ExecuteReader())
{
while (reader.Read())
{
sb.AppendLine(reader.GetString(0));
}
}
textBox1.Text = sb.ToString(); //<----NO DATA
}
finally
{
pgSqlConnection1.Close();
}
}
private void button2_Click(object sender, EventArgs e)
{
pgSqlConnection1.Open();
try
{
PgSqlCommand cmd1 = new PgSqlCommand();
cmd1.Connection = pgSqlConnection1;
cmd1.CommandText = "set transform_null_equals to on;";
cmd1.ExecuteNonQuery();
PgSqlCommand cmd2 = new PgSqlCommand();
cmd2.Connection = pgSqlConnection1;
cmd2.CommandText = "select moo_name from moo where test_name = null";
StringBuilder sb = new StringBuilder();
using (PgSqlDataReader reader = cmd2.ExecuteReader())
{
while (reader.Read())
{
sb.AppendLine(reader.GetString(0));
}
}
textBox1.Text = sb.ToString(); //will have data in it!!!
}
finally
{
pgSqlConnection1.Close();
}
}
Please let me know if there is something I'm doing wrong above... however, it really seems like a bug.
Note: I'm currently using ver 4.00.22
Thanks,
David