issues with "where value is null"

Hi,

I'm running SubSonic against a MySQL db, and having some trouble with the querying code.

1. Given this query:
Query qry = Category.CreateQuery().WHERE("ParentCategoryID", DBNull.Value);

qry.GetSql() returns "SELECT  *  FROM `category`;"
which is incorrect. I also get zero rows - it should be many rows... given the GetSQL() output, I should actually be getting all rows.

2. Given this query:
Query qry = new Query(Category.Schema).WHERE("ParentCategoryID is null");

I get:
[FormatException: Input string was not in a correct format.]

3. So then I tried this:
SqlQuery qry = new Select("CategoryID", "CategoryName").From("Category").Where("ParentCategoryID").IsNull();

which gave me:
[SqlQueryException: Need to have at least one From table specified]

4. Finally, I tried this:

            SqlQuery qry = new Select("CategoryID", "CategoryName")
                .From(Category.Schema)
                .Where(Category.ParentCategoryIDColumn).IsNull();

... which worked fine.

The first 3 look like bugs to me.

Another issue: using the generated update() method, my ParentCategoryID field will not update if the value is null. No error is raised - the field in the db just retains its old value.

        /// <summary>
        /// Updates a record, can be used with the Object Data Source
        /// </summary>
        [DataObjectMethod(DataObjectMethodType.Update, true)]
        public void Update(int CategoryID,string CategoryName,int? ParentCategoryID,int CategoryTypeID)
        {
            Category item = new Category();
            item.MarkOld();
            item.IsLoaded = true;
           
            item.CategoryID = CategoryID;
               
            item.CategoryName = CategoryName;

            item.ParentCategoryID = ParentCategoryID;
               
            item.CategoryTypeID = CategoryTypeID;
               
            item.Save(UserName);
        }

On further investigation, it looks like the column is not being set dirty when its value is set to null.

When I do an update with ParentCategoryID null, item.GetUpdateCommand(UserName).CommandSql returns:

"UPDATE `category` SET `CategoryName` = ?CategoryName WHERE `CategoryID` = ?CategoryID; SELECT ?CategoryID AS id"

If I specify a value, it ends up like this:

"UPDATE `category` SET `CategoryName` = ?CategoryName, `ParentCategoryID` = ?ParentCategoryID WHERE `CategoryID` = ?CategoryID; SELECT ?CategoryID AS id"

Notice the missing ParentCategoryID in the first query.

To get around this, I'm doing an:

item.DirtyColumns.Add(Category.ParentCategoryIDColumn)

Ideally, this should be done in the setter for ParentCategoryIDColumn.

(i.e. this last bug is in RecordBase<T>.SetColumnValue)

Anyone? Is this the right place to be reporting bugs?

First off, Im using SQL Server not MYSQL so take this for what its worth to you,

1. I get a similar result with the where NULL query. Im not sure whats at fault here though, because the SQL coming into SQL Server looks OK. The problem seems to be to do with the assignment of NULL in a parameterised query. Subsonic constructs this fragment the same as it would for any other parameterised fragment but SQL Server seems to be doing something odd when the value is null.

2. Not sure why you are getting the invalid string format message, (It looks like it should get a regex match to me) but even if it didn't error there, Subsonic parameterises your Where(("ParentCategoryID is null") so Im guessing it would hit the same snag as above.

3. The IsNull() query works for me.  Are you using Northwind ? The tablename is Categories. Or you could use Category.Schema.

4. Works fine for me also.

This is the right place to discuss issues and decide whether you have found a bug.

But the best place to report a bug or request a change is at http://www.codeplex.com/subsonic/WorkItem/List.aspx

Cheers

Geoff