Updateable Result Pane Part II -- How do we deal with data in the updateble result pane (By Ming Chen)

  • Article

In this version updatable result pane, since we rewrite it from ground up.  Beside on the UI fanciness, there are some rule on how do we deal with data in updateable result pane

  1. Minimum data for update/delete.

For example, if user only change 2 columns data in a 12 columns rows, only those two data that’s changed will be in the Set clause of the update statement.   The update commit is base on row commit.  If you change multiple columns in a single row,  you can always come back and hit <escape> to revert your change back to original data.

  1. Optimistic Concurrency Control:

Take the previous example, all 12 columns (note: plus primary keys) of the original data will be put in to the where clause.  The note on the primary keys is that in case of  the primary keys is not in the projection list, we will try to include the primary keys columns in addition to user specified columns.

If this update/delete come back as 0 update, we assume the data in the database has been changed since the last retrieval.  We will offer a dialog to allow user to update only base on the primary keys, or allow user to see what’s the data changed first by retrieve the data back base on primary keys.

The caveats are

    • Some column types are not allow to put into the Where clause.  For example binary data type, xml data type. -- they are degrade to only compare to NULL or non-NULL
    • The data that we failed to read cannot be put into the where clause.
    • If underline data on the database was deleted or the primary keys was updated, then even use the primary keys to update/delete/retrieve, it will obviously failed.  If that’s what happening, the row will then marked as read only.
  1. Clear distinguish Empty String, NULL and “NULL” string

NULL will be represent as NULL

If you need to input a “NULL” string, you need to type in ‘NULL’ (6 characters including single quotes.)  If the data from the database is “NULL” string (4 characters) then it will display as ‘NULL’.  That is, for example,  for a char(10) column if you type in ‘NULL’ data to update the row.  The “NULL” string will be sent to database. However, after successful updated, the data retrieve back will be “NULL       “ (NULL string with 6 padding space). IT will be displayed as NULL    (with 6 space padding)

You might ask that if I really want to put a ‘NULL’ (with quote) into database, well, you are out of luck.  We don’t want to introduce the whole set of escape characters that user have to learn.  Beside, the current implementation, the UI is pretty consistent (so copy & paste will work as consistent as possible – what you see from retrieved data from the database, you can input the same way to the updatable result set.)

 

There are some casualties of the above rules:

  • For new insertion rows – How do you make the NULL into the Values clause?

Well, the original data is nothing—that is why it original show up as NULL.  If you don’t touch anything, well, according to above Rule 1.  Nothing will be put into the Values clause of the insert statement.  So, you might ask, what if you really want to insert a data which happen to be NULL.  Well, you have to type it in literally. (So we can tell it is not original NULL or cause by user hitting the <escape> key in the cell).  Tip: you can also use Ctrl-0 to insert a NULL

You can also tell which NULL we will put in the Values clause – the NULL with a dirty pencil on that cell. The only exception will be if you are currently editing the cell – the textbox will be on top of the whole cell – the dirty pencil is there, but it is under the textbox – you can’t see it.

 

Next subject:   When execute parameterized Query and execute it, if I input NULL in that dialog, why the result pane doesn’t show any record?