Using the schema from an existing table to create a new table

Problem

---

When I try to create a new table in the database with the schema of an existing table, I expect the new table to have the same schema as that of the old table. This doesn't always happen.

---

Cause

---

Whenever you get a schema from an existing table, the column types are mapped with the DBTools datatypes as shown in the tables 1 & 2 of Chapter 2 in the DBTools access library manuals(oracle,sybase,Informix,odbc,MS SQL server, DB2).

This mapping does not have a one to one relationship. There is one to many and many to one mapping between the native database datatypes and DBTools datatypes. So the new table created may or may not have the same schema as that of the old one.

---

Action

---

What should you do now ?

RWDBTable mytable = mydatabase.table(cool_dude_table);

//here you get the schema from the existing table
myTable.fetchSchema();
RWDBSchema schema = mytable.schema();
for( int i = 0; i < schema.entries(); i++ )
{                                              
  schema[i].type( RWDBValue::NoType );        
}         

//RWDBValue::NoType forces the schema to use the native database schema and will not do mapping with DBTools datatypes.

  
myDatabase.createTable( new_table, schema );

Now your new table will have the exactly same schema as that of the old table.