CODEWITHSUNDEEP
sql-serverdelphims-accessadoms-jet-ace
Larry Lustig
Larry Lustig

Reputation: 50990

Compatible DDL (CREATE TABLE) across different SQL databases?

I'm working on a desktop application that must support (currently) MS Access and SQL Server back ends. The application is under constant development and changes are frequently being made to the database, mostly the addition of tables and views to support new features (but also some DROPs and ALTER TABLEs to add new columns).

I have a system that compiles the DDL into the executable, checks the database to see if the executable has any new DDL that has to be executed, and executes it. This works fine for a single database.

My immediate problem is that SQL Server and Access support wildly different names for data types so a CREATE TABLE statement that executes against Access will not execute against SQL Server (or worse, will execute but create a table with different datatypes).

Is there a method that can be used to create DDL (especially CREATE TABLE commands) that can be executed through ADO against both of these databases without having to craft separate DDL for each provider?

Upvotes: 4

Views: 882

Answers (4)

Robert Love
Robert Love

Reputation: 12581

This can be done using DBX in Delphi.

The following is links to sample code showing how this can be done. http://cc.embarcadero.com/item/26210

Upvotes: 3

Stefano Moratto
Stefano Moratto

Reputation: 31

I had the same problem. I resolved it applying a C preproccessor to the SQL before executing it. The preprocessor includes macros in order to handle the different dbs.

Stefano

Upvotes: 1

Deltics
Deltics

Reputation: 23036

Since you are already using ADO, you should look into Microsoft ADOX

This allows you to manipulate structures in a data source using an ADO object model that is independent of the underlying data source type. i.e. without resorting to explicit DDL

Support for ADOX is not guaranteed by any given ADO Provider, and the level of ADOX support may vary even when it is available. But for MS Access and MS SQL Server I think you will find all the capability you require (and quite possibly more!)

Upvotes: 3

user497849
user497849

Reputation:

Did you check

http://db.apache.org/ddlutils/

or

http://publib.boulder.ibm.com/infocenter/wtelecom/v6r1/index.jsp?topic=%2Fcom.ibm.twss.plan.doc%2Fdb_scripts.html

Upvotes: 0

Related Questions