How to run script database file from Delphi?

Question

I want to do the following. 1) Create a database. 2) Run the script when creating tables, stored procedures, etc. (This script is created using an SMS script generation script)

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

to try

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

edtPassword.Text + '; Persist Security Info = True; User ID = '+ edtUser.Text +'; Start Directory = 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 +'; Start Directory = '+ 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 until the script file is run. Then it throws an exception: Invalid syntax next to "GO". If I run the script in SMS on a newly created DB, this is normal. This problem arises due to the simultaneous execution of several SQL commands (the script is essentially a long list of GO commands / commands? How to get around this?

Oh, as well as a bonus, any thoughts on a quick check to see if a new database exists before sending a script to it? (Or is it not necessary, since if creation fails, it will throw an exception?)

Answer 1

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

Now, to check if a database exists, you can run a query like this

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

check out this most basic example

suppose 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 fulfill 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; 

Answer 2

What GO means the end of the batch for only some Microsoft utilities is not a valid T-SQL statement. Try removing every GO event in the script and then executing it. This GO will execute ADOQuery.ExecSQL for you at the end of your script.

And to your second question; you can use, for example, SQL DB_ID to check if your DB exists (of course, you must be on the same server). This function returns the database identifier; otherwise NULL, so if the following SQL statement returns NULL, your database creation failed.

 ADOQuery.SQL.Text := 'SELECT DB_ID(' + edtWebDBName.Text + ')'; ADOQuery.Open; if ADO_Query.Fields[0].IsNull then ShowMessage('Database creation failed'); 

Answer 3

Scripts can contain much more than SQL DDL / DML commands. They may contain variables, small blocks of code, a transaction management operator. Usually there are several operators separated by a terminator (semicolon, slash Oracle, MSSQL GO, etc., Depending on the database used and the script syntax). To execute the script correctly, you need to parse the input file, separate each command and submit it correctly to the database. You can search the library to do this (there are some, IIRC), or you can try using the MS SQL command line tool to feed the script through it.