"Null" Parameters don't seem to be working

"Null" Parameters don't seem to be working

Postby dsaracini » Thu 19 Mar 2009 00:56

Hello,

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
dsaracini
 
Posts: 17
Joined: Wed 04 Mar 2009 07:50

Postby Shalex » Thu 19 Mar 2009 12:38

Thank you for the bug report. We have reproduced the problem. You will be notified about the results as soon as possible.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby dsaracini » Thu 19 Mar 2009 17:42

May I have a status update? When will this be addressed?

Thank you.
dsaracini
 
Posts: 17
Joined: Wed 04 Mar 2009 07:50

Postby Shalex » Thu 26 Mar 2009 12:47

This is a PostgreSQL server issue. It doesn't recognize the null value of a parameter in the where clause.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL