All geek questions in one place

Compatible DDL (CREATE TABLE) for different SQL databases?

I am working on a desktop application that should support (currently) MS Access and SQL Server. The application is constantly evolving, and changes are often made to the database, mainly adding tables and views to support new features (but also some DROP and ALTER TABLEs to add new columns).

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

My immediate problem is that SQL Server and Access support completely different names for data types, so the CREATE TABLE statement that runs against Access will not run against SQL Server (or, even worse, it will execute, but it will create a table with different types of data).

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

+4
source share
4 answers

Since you are already using ADO, you should learn about Microsoft ADOX

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

ADOX support is not guaranteed by any ADO provider, and the level of ADOX support may vary, even if it is available. But for MS Access and MS SQL Server, I think that you will find all the features you need (and possibly more!)

+3
source

This can be done using DBX in Delphi.

Below are links to sample code showing how to do this. http://cc.embarcadero.com/item/26210

+3
source

I had the same problem. I decided to use the C application in front of the SQL server before executing it. The preprocessor includes macros to handle different dbs.

Stefano

+1
source

Source: https://habr.com/ru/post/1336031/

More articles:


All Articles