Create (MSSQL) Database in code?

[TyneBridges]

I have managed to run a test successfully connecting to Microsoft SQL Server Express using TMSSQLConnection, TSQLQuery,  TSQLTransaction and TDatasource. I can connect to an existing database, read records from a table and display these using a TDBGrid.

However, when trying to create a *new* database on the server, I come against a "Catch 22" situation because TMSSQLConnection requires a database name to make a connection, and (understandably) throws an error if I give the name of a database that doesn't already exist.

Is there a way to create a new database using the visual components, or do I need to create a console application, omitting the components? I have only a vague idea of how to carry out what I want, and can't seem to find any examples. Can anyone point me to an example of how to create a new MS SQL database in code, or give a few hints?

Thank you.

[BigChimp]

Quick reply:
The master database should always exist; you can use that to connect to.

Also, the .CreateDB procedure of  the connection should work...
 
... look in MS SQL books online (the manual) e.g. on MSDN [1] for more details; IIRC you can specify where log files etc can be kept, but the CreateDB call should work as-is.
(Looking at the code, it seems like you have to first set the DatabaseName property to the name of the new db you want to create...)

[1] http://msdn.microsoft.com/en-us/library/ms130214.aspx

[TyneBridges]

Thanks for the hints, BigChimp. Unfortunately the following fails on the last line with the error "Operation is not supported by this type of database". I had already tried Q.SQL.Text:= 'CREATE DATABASE Bookings;'; Q.ExecSQL; but this gave "Could not locate entry in sysdatabases for database 'Bookings'".

      // Connect to Master database.
      Conn.HostName:= 'SPRINT11';
      Conn.DatabaseName:= 'master';
      Conn.UserName:= 'sa';
      Conn.Password:= 'manager';
      { Q (DSQLQuery) Database already set to Conn
        Q.Datasource set to DS
        T (TSQLTransaction) Database set to Conn }
      Conn.Connected:= True;
      MsgLabel.Caption:= 'Connected to master database';
      // Check for existence of the Bookings database.
      Conn.Connected:= False;
      Try
         Conn.DatabaseName:= 'Bookings';
         Conn.Connected:= True;
         // Error 911 indicates database doesn't exist
      Except
        On E: Exception do
        begin
          Conn.Connected:= False;
          R:= Application.MessageBox('The Bookings database was not found. Create it?',
          AppName, mb_OK + mb_YesNo + mb_IconQuestion);
          If R = idYes then
          begin
               Conn.CreateDB;         

[BigChimp]

The attached project works for me with FPC 2.7.1 (trunk) against MS SQL Server 2008 Express.... more or less the same code as yours, but it does show another way of getting the list of databases.

I couldn't get it to work on 2.6.1; don't know what version you are using.

I had already tried Q.SQL.Text:= 'CREATE DATABASE Bookings;'; Q.ExecSQL; but this gave "Could not locate entry in sysdatabases for database 'Bookings'".

For me it was complaining "create db not allowed within multi statement transction"; see notes in TForm1.CreateDBButtonClick!?!?

[TyneBridges]

Sorry, my version is 1.1  - an interim one downloaded to get a Lazarus with FPC 2.6.1. Date: 2013-02-13 FPC Version: 2.6.1. I have also had the same error as you about the multi-statement transaction while I've experimented with different lines of code. I think that was when I explicitly included the transaction in the code (T.StartTransaction and T.Commit around my single SQL statement - so the message wasn't very helpful).

My SQL Version is MS SQL Server 2005.

I know that Lazarus' developers are busy people who contribute to the project in their free time, but it would be extremely useful if they could provide example code for operations like this, so that we can make full use of their work.

[BigChimp]

It's actually an FPC component, not Lazarus...

If in future you have sample code you'd like to include, please don't hesitate to post it to the bugtracker (project FPC, database components) and we'll have a go at adding it to FPC.

Sorry about not getting it to work on 2.6.1 - I'd have to look into which improvements went into 2.7.1. Maybe I'll get it working with some more digging... no promises though.

I have also had the same error as you about the multi-statement transaction while I've experimented with different lines of code. I think that was when I explicitly included the transaction in the code (T.StartTransaction and T.Commit around my single SQL statement - so the message wasn't very helpful).

Mmm, enclosing that DDL in a specific transaction makes sense to me... but as you'll change db anyway it doesn't matter.

I'll look into this when I have some more time.

[BigChimp]

Ok, figured it out; had to enable autocommit otherwise transactions would still get in the way.

In the zip I uploaded earlier, add

Code: [Select]

procedure TForm1.FormCreate(Sender: TObject);
....
    mrOK:
      begin
....
        FConn.Password:=LoginForm.Config.DBPassword;

//----- add this
        //We need to enable autocommit for create table to work:
        FConn.Params.Add('AutoCommit=true');
//------ end addition

Strange that your code didn't work - could you give the above a try and report if it works?
Perhaps SQL Server 2005 needs some additional work add the db?!?
http://msdn.microsoft.com/en-US/library/ms176061%28v=sql.90%29.aspx

Try creating a db in the MSSQL Management studio; IIRC, you can let it show the commands it runs...

[TyneBridges]

Thanks for your help! That looks promising - will try it later when I have a few minutes.

[BigChimp]

Argh sorry, of course you'll also need the right create database statement:

Code: [Select]

procedure TForm1.CreateDBButtonClick(Sender: TObject);
var
  CurrentDB: string;
begin
  if DatabaseNameEdit.Text='' then
  begin
    showmessage('Empty database name. Please specify database name first.');
    exit;
  end;

  CurrentDB:=FConn.DatabaseName;
  try

    FConn.ExecuteDirect('CREATE DATABASE '+DatabaseNameEdit.Text);
    {
    // This works with FPC 2.7.1, not on 2.6.1
    FConn.CreateDB;
    }
  except
    on E: Exception do
    begin
      showmessage('Exception running CreateDB: '+E.Message);
    end;
  end;

  LoadDatabasesGrid;
  {
  Tried with FPC 2.6.1 but failed:
  // do we need to be connected to master for this to work?
  //this won't work: create db not allowed within multi statement transction
  //FConn.ExecuteDirect('CREATE DATABASE '+DatabaseNameEdit.Text);
  FTran.StartTransaction;
  //The CREATE DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction. For more information, see Autocommit Transactions.
  FQuery.SQL.Text:='SET IMPLICIT_TRANSACTIONS OFF';
  FQuery.ExecSQL;
  FQuery.SQL.Text:='CREATE DATABASE '+DatabaseNameEdit.Text;
  FQuery.ExecSQL;
  FTran.Commit;
  }
end;



.. sorry, doing too many things at once.

[TyneBridges]

You *are* a hero, BigChimp - your example works for me. I just need to follow the principle in my own code and all should be well... Thanks again. 

[BigChimp]

Thanks Glad it works

[BigChimp]

Copied the example to
http://wiki.lazarus.freepascal.org/mssqlconn#Creating_a_database
for easier search/retrieval...