RDbDatabase::AlterTable and KErrArgument (-6)

Sharing some experiences...

We wanted to add a new column to an existing database.
We had done this before by calling RDbDatabase::AlterTable
and thought it simple. Turned out it isn't always.

Our code creates a table by constructing a CDbColSet object
with the correct columns, and altering meant just adding
one more column to the set and calling AlterTable. The
AlterTable, however, left with KErrArgument (-6).

According to the documentation this happens 'if the new column set is empty, or there are duplicate column names, or if a column's maximum length is non-positive but not KDbUndefinedLength, or a non-numeric column has the auto-increment attribute, or an indexed column has been dropped, or a column has changed its type or attributes, or a not-null or auto-increment column has been added to a table which is not empty'.
None of this should have been the case.

An old knowledgebase article from 2000 http://www3.symbian.com/SupportCPP.nsf/3fc93746fd510301802565d8004a04a9/f9e90e302b63481f386e950599d8e8ff?OpenDocument
(I assume for ER5) hints that you may have trouble calling AlterTable
with your own CDbColSet, but should instead use RDbDatabase::ColSetL
to get the existing one instead (and modify that). Lo and behold, that
works. But why?

Considering again the documentation, the only reasonable thing that
could go wrong would be that we are changing the type or attributes
of an existing column. But we use exactly the same set of columns
as when creating the database!? It must be that what we request is
not what is created.

And so it is. Printing out the contents of the CDbColSet we use to
create the table and the one acquired with ColSetL after the creation
we can see that out outerincrement column's flags have been
changed from EAutoIncrement (2) to EAutoIncrement|ENotNull (3).
This, in itself, is of course perfectly reasonable - an autoincrement
column cannot be Null. It's just that it then leads to a disrepancy
between what was requested and what actually got created and so
to the database thinking that we want to change the autoincrement
column's attributes.

So by setting the ENotNull flag ourselves in our own CDbColSet
everything is fine.

    Mika Raento

P.S. Maybe somebody will actually get round to updating the
documentation as promised in the KB article in 2000.
P.P.S Or better yet, make the AlterTable have the same logic
in implicitly adding the ENotNull attribute as the creation
does :-)