Thursday, August 26, 2010

Table Adapters invalid and cannot be generated after altering ODBC Firebird database

I may have generated a unique cause for this error, but it's a scary moment when all your ODBC table adapter queries disappear so I'll document the experience.

This Visual Basic.Net (2008) project started out with an Access database for convenience, and I prefer mixed case table and field names for legibility.

About 10 days ago I switched to a Firebird database, and it was pretty cool that the table adapters worked with Firebird after minor edits (e.g. remove the back-quotes on field names, handle missing native boolean type in Firebird)

But, today after adding some columns in the Firebird database all my table adapters disappeared. The error is briefly summarized as "Failed to generate code. No mapping exists from DbType Object to a known OdbcType" The IDE showed "unknown type" critical errors on every reference in code.

This error would occur even though I could hit Execute Query in the Table Adapter designer and see valid data appear.

After some thrashing and a restore of all the relevant Dataset files from backup I figured out the error was rooted in case-sensitivity. One of the table adapters could no longer reference it's underlying table, and that meant the code for all the table adapters in the class could not be generated. Interestingly, in dataset design view the obviously broken adapter was *not* on the table with the new columns.

There appeared to be problems in the <Mappings> for the newly altered table. I saw both mixed-case to mixed-case and upper-case to upper-case mappings, but no mixed-case to upper-case mappings. In addition, the column types were declared differently in mixed case from upper case, probably because they were based on Access when first generated, and Firebird when regenerated.

This was going to be a problem because all the queries were written in mixed case, but Firebird stores all field and table names in upper case (unless you quote each field and table name, which I figured is only going to cause problems later), so there was no possible way to map the text in the commands and field definitions to the Firebird interface.

After converting queries and fields on the broken table adapter to upper case, and eliminating conflicting mapping statements and using all upper case in the newly altered table, the table adapters all work again. For safety I converted all the other table adapters to upper case as well.

It looked like the behind-the-scenes work to incorporate the newly added columns had a problem with the mixed case of the fields. All the columns of the table were there in upper case

One symptom you can look for is the field list in the table adapter disappears, all that's left is a list of queries. A second symptom which I missed because the relevant table has 30+ fields is that field names are listed twice, once in mixed case and once in all upper.

Another symptom is after doing Configure on any query in the table adapter you get the "unable to convert object to a known ODBC type" error when you hit Finish.

Since the case and field type issues were a result of moving from Access to Firebird I'm not sure how many people are going to run into this. Bottom line, if you think your going to adopt Firebird later then declare everything in upper case to start with or prepare to comb your code for case-dependencies.

1 comment:

Donald Klopper said...

Did you define the DB as SQL dialect 3? If you do it as dialect 1 you don't have case sensitivity issues with FB in my experience.

I'm a long time IB/FB user but sometimes I need to use that OTHER DBMS. My migration to MySQL running on Linux (prod) vs MySQL running on Windows (dev) also caused some confusion, as Linux is case sensitive, and that seemed to translate to table names as well. I suppose this can be configured, but it can be a pain when deploying to production. My habit these days is to define table names as lowercase.

I guess your FB installation is on Windows? I don't know whether it'll really make a difference; it has been a number of years since I deployed a FB instance on anything other than Windoze.