Renaming a SQL CE Table From a .NET CF Application

Yes, I have addressed this issue a couple of times in the past. The difference now is that I'm providing a means for .NET CF applications to rename SQL CE table names through a native code DLL. Read about it here.

ALTER TABLE / ALTER COLUMN

So how do you change a column type in SQL CE? Going back to 2.0, there was no built-in support in the SQL language. The ALTER TABLE / ALTER COLUMN options were restricted to adding or dropping the default value, or changing the IDENTITY column properties.

If you compare the 2.0 BOL with the 3.0 BOL you see no difference. The same syntax is supported with the same limitations. Well, actually not.

With SQL CE 3.0 you can change a column data type using an ALTER TABLE command. The Syntax is very simple:

ALTER TABLE table ALTER COLUMN column type

I just got a confirmation from Microsoft that this is missing from the documentation so you should see it included in future versions.

Implications of this discovery are very important to me, especially for the SQL CE table editor in Data Port Console (now in Draft 11):

1. Editing tables for SQL CE 3.0 just got easier. Instead of recreating a table every time a user requests a change in column data types, I only need to generate an appropriate SQL command and I'm done with it.
2. Now, how do I keep backwards compatibility with SQL CE 2.0? Well, I don't. When using the SQL CE 2.0 engine the Console code will have to recreate the table (and hope the user has the missing msdadc.dll on the device - a highly unlikely scenario).

Back to the VS2005 editor...

Biting the DB_NUMERIC bullet

I tried hard to avoid it. I really did. I always looked at the DB_NUMERIC structure with a mix of shock and horror. How do you handle such a beast? How do you put there a number and how do you handle it?

Well, you avoid biting the bullet until you definitely have to do it. Right now I'm working on a native code text-to-SDF conversion tool for a customer and one of the types I need to handle is the dreaded numeric.

It's interesting that this is a very popular type due to the fact that it represents a fixed-precision number. What's more, you get to define the precision. The downside to this type is its sheer size: 19 bytes! As a matter of fact, a numeric value is represented as a DB_NUMERIC structure. If you look it up either on MSDN or on oledb.h, you will see what I'm talking about. The damned thing is a monster.

Now my problem was to deserialize a text representation of a numeric value and store it in a DB_NUMERIC structure so I could feed it to the SQL CE OLE DB provider. If you are thinking about using the IDataConvert interface then you can forget it because Microsoft does not provide the msdadc.dll file anymore, so you have to convert the beast by yourself.

Firstly I tried a two tier approach: convert the text to a double and then convert the double to a DB_NUMERIC (there's some code out there that does this). You have to be careful with rounding to get the conversion done, but it works... slowly.

My second approach was actually based on the first one, but I removed most of the floating point calculations. It is fast but limited to numbers with a precision of 18 digits. Take a look:

bool CDbNumeric::Parse(LPCTSTR pszText)
{
    __int64    big        = 0;
    LPCTSTR    pCur;
    int        nScale    = -1;
    int        i;

    for(pCur = pszText; *pCur == ' '; ++pCur)
        ;

    if(*pCur == '-')
    {
        ++pCur;
        sign = 0;
    }
    else
        sign = 1;

    for(; *pCur && nScale; ++pCur, --nScale)
    {
        if(IsNumeric(*pCur))
        {
            big = big * 10 + (*pCur - '0');
        }
        else if(*pCur == '.')
        {
            if(nScale > 0)
                return false;

            nScale = scale + 1;
        }
        else
            return false;
    }

    memset(val, 0, sizeof(val));

    for(i = 0; i < 16 && big != 0; ++i)
    {
        val[i] = (BYTE)(big & 0xFF);

        big >>= 8;
    }

    return true;
}

It's faster, believe me.

Just another database?

Is SQL Mobile / Everywhere / Compact Edition just another database? Read the DDJ interview with Mark Jewett and Steve Lasker for more insight on this:

SQL Server Everywhere: Just Another Database?

Thank you Rui!

Thank you to my fellow MVP Rui Silva for all the stuff he sends me that helps me keep my mental health.

Data Port Console Draft 6

This is a bit boring now... I will not make any more posts about the Data Port Console Draft versions unless they are worthy of notice. If you want to get the latest version of the Draft, you can use this link.

Data Port Console Draft 5

The Draft 5 of Data Port Console is now online. There are a couple of new features such as the revamped table designer (not fully functional yet), that integrates both column editing and index / primary key editing. Fellow MVPs Jan Yeh and Alberto Silva have been helping a lot with suggestions and bug reports, so here's my Thank You.

Anybody else out there with some bright ideas?

Microsoft SQL Server 2005 Compact Edition RC1

The Microsoft SQL Server 2005 Compact Edition RC1 is now available for download here.

JScript support on Windows Mobile

This has got to be one of the coolest things around - exposing your Windows Mobile application's features via JScript!

Draft 3 Notes

There is something I forgot to mention about Draft 3: you have to manually deploy the new RemSqlCe.dll file to the device. Please remember that this is far from being a finished product so you will see lots of missing features, and this is one of them.

The new RemSqlCe.dll (marked as 1.50.xxxx) now supports multiple databases per physical connection. I was looking forward to implement this new feature some time ago. It was not quite necessary for products like the Wizard where you actually process one device database per connection. With DesktopSqlCe I started to feel the need to implement this but as the sales took off I simply got too busy supporting my customers cleaning off some very stupid bugs, and this was delayed.

I finally decided to rewrite the console and implementing this feature was on the top of my to-do list. Designing the multi-database support code was quite easy: I extracted most of the code that was on a single monolithic server and implemented it in a separate class. This new server class is instantiated whenever a new database is created or an existing one is opened, and a pointer to it is stored in an internal array of 256 elements (I believe you will never need to open 256 databases on a device at the same time, will you?). When you open a database using the Draft 3 code you can see the server index in the database properties window. If you get a -1 after opening the database then you are using an older version of RemSqlCe.dll - please replace it. Note that the index value will not automatically refresh after opening the database. To do so, please select another node in the tree and select the database node again.

Please send all your comments and suggestions to this forum. The best ideas will get a free license. What do you think should be in this product?

• Integrated import / export features?
• If so, what formats should I support?
• What database editing features are the most important for you?
• Should I implement some sort of SQL Command batch processing?
• Do you want to manage other Microsoft databases from this console?
• Should I integrate the Data Port Sync features?

Let's hear it from you!

Data Port Console Draft 3

Draft 3 of the Data Port Console is now online. You can now:

• Export the table data to an Excel sheet and
• Specify database open and creation properties

As always, remember that this is not a product yet. Most of the features you will see on the menus will not work. Although my tests have shown that the data handling is now stable (the underlying native code DLLs have been revamped to support multiple connections per Pipe), please refrain to use your production SDF files with this and use copies instead.