sql server - How to run a database script file from Delphi?

Question

I want to do the following. 1) Create a database. 2) Run a script when creates tables, stored procedures, etc. (this script is created by SMS 'generate scripts' option)

I found the following code: http://www.delphipages.com/forum/showthread.php?t=181685 and modified it to this:

try

ADOQuery.ConnectionString := 'Provider=SQLOLEDB.1;Password=' +

edtPassword.Text + ';Persist Security Info=True;User ID=' + edtUser.Text + ';Initial Catalog=master;Data Source=' + edtServerName.Text;

ADOQuery.SQL.Clear;
ADOQuery.SQL.Text := 'create DataBase ' + edtWebDBName.Text;
ADOQuery.ExecSQL; // should check existance of database
ADOWeb.Connected := false;
ADOWeb.ConnectionString := 'Provider=SQLOLEDB.1;Password=' +

edtPassword.Text + ';Persist Security Info=True;User ID=' + edtUser.Text + ';Initial Catalog=' + edtWebDBName.Text + ';Data Source=' + edtServerName.Text; ADOWeb.Connected := true;

ADOQuery.Connection := ADOWeb;
ADOQuery.SQL.Clear;
ADOQuery.SQL.LoadFromFile(edtScriptFileName.Text);
ADOQuery.ExecSQL;   except

This works up until the point of running the script file. Then it generates an exception: Incorrect Syntax near "GO". If i run the script in SMS on the newly created DB, it is fine. Is this issue due to running more than one SQL command at once (the script is essentially a long list of command/GO statements? How to get around it?

Oh also as a bonus, any thoughts on a quick check to see if the new database actually exists before sending a script to it? (Or is it not necessary since if the create fails it will generate exception?)

Answer 1

Rob the GO statement is not recognized by ADO, so you must remove from your script before execute.

Now to check if a database exist you can execute a query like this

select COUNT(*) from sys.databases where name='yourdatabasename'

check this very basic sample

assume which you have a script like this

CREATE TABLE Dummy.[dbo].tblUsers(ID INT, UserName VARCHAR(50))
GO
INSERT INTO Dummy.[dbo].tblUsers (ID, UserName) VALUES (1, 'Jill')
GO
INSERT INTO Dummy.[dbo].tblUsers (ID, UserName) VALUES (2, 'John')
GO
INSERT INTO Dummy.[dbo].tblUsers (ID, UserName) VALUES (3, 'Jack')
GO

Now to execute this sentence you can do something like this

const
//in this case the script is inside of a const string but can be loaded from a file as well
Script=
'CREATE TABLE Dummy.[dbo].tblUsers(ID INT, UserName VARCHAR(50)) '+#13#10+
'GO '+#13#10+
'INSERT INTO Dummy.[dbo].tblUsers (ID, UserName) VALUES (1, ''Jill'') '+#13#10+
'GO '+#13#10+
'INSERT INTO Dummy.[dbo].tblUsers (ID, UserName) VALUES (2, ''John'') '+#13#10+
'GO '+#13#10+
'INSERT INTO Dummy.[dbo].tblUsers (ID, UserName) VALUES (3, ''Jack'') '+#13#10+
'GO ';

var
  DatabaseExist : Boolean;
  i             : Integer;
begin
  try
    //check the connection
     if not ADOConnection1.Connected then
      ADOConnection1.Connected:=True;
      //make the query to check if the database called Dummy exist  
      ADOQuery1.SQL.Add(Format('select COUNT(*) from sys.databases where name=%s',[QuotedStr('Dummy')]));
      ADOQuery1.Open;
      try
       //get the returned value, if is greater than 0 then exist 
       DatabaseExist:=ADOQuery1.Fields[0].AsInteger>0;
      finally
       ADOQuery1.Close;
      end;


      if not DatabaseExist then
      begin
       //create the database if not exist
       ADOQuery1.SQL.Text:=Format('Create Database %s',['Dummy']);
       ADOQuery1.ExecSQL;
       ADOQuery1.Close;

       //load the script, remember can be load from a file too  
       ADOQuery1.SQL.Text:=Script;
       //parse the script to remove the GO statements
        for i := ADOQuery1.SQL.Count-1 downto 0 do
          if StartsText('GO',ADOQuery1.SQL[i]) then
           ADOQuery1.SQL.Delete(i);
       //execute the script
       ADOQuery1.ExecSQL;
       ADOQuery1.Close;
      end;
  except
      on E:Exception do
        ShowMessage(E.Message);
  end;

end;