Create Database and Table at runtime

[question]

Hi,
I would like to create Database and table in runtime, i have used Datamodule component and created two procedure, Procedure CreateDB; Procedure CreateTB;

Code:

Procedure CreateDB; 
begin
// I would like to test if the "TestDB" not Exist then i would like to create the DataBase "TestDB"
End;

procedure TDataModule1.CreateTB;
begin
//I would like to check first if the table " titles" not exist then i would like to create this table
  Query1.Close;
  Query1.SQL.Add('CREATE TABLE titles(
  Query1.SQL.Add('  emp_no      INT          NOT NULL,');
  Query1.SQL.Add('title       VARCHAR(50)  NOT NULL,');
  Query1.SQL.Add(' from_date   DATE         NOT NULL, ');
  Query1.SQL.Add('to_date     DATE,');
  Query1.SQL.Add(' Primary KEY         (emp_no),');
  Query1.ExecSQL;

end;

can anybody help me please?

[jobo]

There is not a simple answer to this task.
You have would have to use a Query to check the dictionary of the database for the table in question.
Therefor You need a Database connection. Which itself is in question.
So You have to start with a connection to "Your" database.
If this succeeds, speaking "connect succeeds", You probably like to check, if the database retrieved by given name is the one, You are looking for.
Query for known tables, check Your own privileges.

If everything is fine, start with your datamodel commands above.

some dictionary links:

Code:

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db_name'
  [AND table_name LIKE 'wild']

SHOW TABLES
  FROM db_name
  [LIKE 'wild']

http://dev.mysql.com/doc/refman/5.0/...les-table.html

[Furtbichler]

The only part being questionable is 'how do I know if a database exist?'.
Doing the same for tables (do they exist?) is easy as it is a built in function in the TADOConnection-component ('GetTableNames'). But, you need to connect to the database first in order to be able to use the TADOConnection.

For MSSQL it's rather simple:
1. Connect to the master-DB of the server.
2. execute SELECT * FROM master..sysdatabases where name = 'YourDatabase' to check for the existence of the DB.
3. if it does not exists, create it using the 'CREATE DATABASE' command
4. connect to the database
5. use 'GetTableNames' to find out if the tables you need exist.
6. create them using the 'CREATE TABLE' command.

It is very easy, once you've learned how to use google.

Another very simple approach for SQL-Server is to create an empty database file and tell the connection to use it. In order to do that, simply start SSMS, create the database and the tables and detatch the DB. The file is now ready to use.

For other RDBMS there are other approaches for points 1-3.

[Bernhard Geyer]

Zitat von Furtbichler:

For MSSQL it's rather simple:
....

You missed some points
7, Create roles
8, Create/add user to role.

[DeddyH]

http://dev.mysql.com/doc/refman/5.1/...-database.html

Zitat:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

There is a similar syntax for the CREATE TABLE-Statement as well.

[Furtbichler]

Zitat von Bernhard Geyer:

You missed some points..Create/add...

In a dynamic database? Which was created with admin credentials anyway? Well, yeah. Maybe.