如何从Delphi运行数据库脚本文件？ [英] How to run a database script file from Delphi?

问题描述

HI all，

我想执行以下操作。
1）创建一个数据库。
2）创建表，存储过程等时运行脚本（此脚本由SMS生成脚本选项创建）

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)

我发现以下代码： http://www.delphipages.com/forum/showthread.php?t = 181685
并将其修改为：

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 ='+
edtSe​​rverName.Text;

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 ='+
edtSe​​rverName.Text;
ADOWeb.Connected：= true;

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

直到运行脚本文件为止。然后它会生成一个异常：GO附近的语法不正确。如果我在新创建的数据库中以短信方式运行脚本，那就行了。这个问题是由于一次运行多个SQL命令（脚本本质上是一个很长的命令/ GO语句列表？如何解决？

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?)

谢谢！

推荐答案

罗布 GO 语句不被ADO识别，所以在执行之前必须从脚本中删除。

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

假设你有这样的脚本

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;

这篇关于如何从Delphi运行数据库脚本文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！