Delphi-在运行时创建MySQL数据库 [英] Delphi - Creating MySQL database at runtime

问题描述

我有一个连接到MySQL数据库的delphi应用程序，但是，我想为我的最终用户提供一种简便的方法来实现MySQL数据库。我考虑过在我的应用程序中创建一个按钮，用户可以按此按钮删除该scehma的任何当前实例，并创建一个具有我的应用程序需要起作用的正确表和列的新架构。

我已经编写了创建新数据库的代码。如下所示：

 如果不存在假模式，则创建模式； 
使用fuseschema； 
创建表table1 
（IDtable1 int（11）主键非空AUTO_INCREMENT，
 Line1 varchar（45），
 Line2 varchar（45））; 
  

代码在MySQL中正常运行，但是在执行代码时收到SQL语法错误。我得到的是：

您在USE假模式附近的SQL语法中的错误； CREATE TABLE table1（IDtable1 int（11）PRIMARY KEY NO'

我正在使用 ADOConnection 链接到数据源。按下按钮后，我正在编写连接字符串。我正在使用 ADOQuery

这是我用来连接数据库的代码片段：

  ADOC.ConnectionString：='PROVIDER = MSDASQL; DRIVER = {MySQL ODBC 3.51 Driver}; 
 SERVER = localhost;数据源= faketest; DATABASE = fakeschema;用户ID = root; 
 PASSWORD = pass; OPTION = 3;'; 
 ADOC.DefaultDatabase：='fakeschema'; 
 ADOC.Connected：= True; 
  

我使用了错误的工具/方法吗？我是MySQL的新手，我正在学习Delphi。

individual SQL语句。 ery组件。

在理想的世界中，您将拥有诸如MyDAC之类的组件，该组件具有可用于代替TAdoQuery的脚本组件（MyDAC也会为您带来其他好处，例如因为不必通过ODBC进行连接）。我不知道是否有免费的MySQL组件包含脚本组件。

另一种方法是可以创建脚本文件（例如createFakeSchema.sql），然后通过命令行执行它。例如：

createFakeSchema.sql：

 如果不存在假模式，则创建模式； 
使用fuseschema； 
创建表table1 
（IDtable1 int（11）主键非空AUTO_INCREMENT，
 Line1 varchar（45），
 Line2 varchar（45））; 
  

以及示例源代码：

 过程TfrmMain.DoExecuteScriptFile; 
 var 
 cmd：字符串; 
 KeepOpen：布尔值； 
开始
 KeepOpen：= True； 
 
 //用于在执行完成后自动关闭窗口的选项
 //用于释放您不希望其保持打开状态，但是如果KeepOpen然后
可以方便地调试
 cmd：='/ k'
 else 
 cmd：='/ c'; 
 
 cmd：= cmd + Format（'mysql -uroot -proot -D％s<％s'，['FakeSchema'，'createFakeSchema.sql']）; 
 ShellExecute（句柄，打开， cmd.exe，Pchar（cmd），nil，SW_SHOW）； 
结尾； 
  

这样，您可以在外部某个地方创建脚本文件，然后通过MySQL自己进行测试，然后再知道脚本正在运行，您可以通过程序运行它。如果要在执行时隐藏命令窗口，请将ShellExecute中的SW_SHOW更改为SW_HIDE。这样，您甚至根本不需要任何组件-只需在路径中访问mysql.exe或在cmd语句中包含完整路径即可。

在MySQL 5.1中，因此希望能在3.5 ......

I have a delphi application which connects to MySQL database, however, I would like to give create an easy way for my end user to implement the MySQL database. I thought about creating a button within my application which the user could press to delete any current instances of the scehma, and create a new schema with the correct tables and columns which my application requires to function.

I have written the code to create the new database. It is as follows:

CREATE SCHEMA IF NOT EXISTS fakeschema;   
USE fakeschema;  
CREATE TABLE table1  
(IDtable1 int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,  
Line1 varchar(45),  
Line2 varchar(45));  

The code functions correctly within MySQL, however I am receiving an SQL Syntax error when executing the code. I am getting of:

error in your SQL syntax near 'USE fakeschema; CREATE TABLE table1 (IDtable1 int(11) PRIMARY KEY NO'

I am using an ADOConnection to link to the datasource. I am writing the connection string once the button has been pressed. I am using an ADOQuery to execute the SQL code.

Here is a snippet of the code which I am using to connect to the database:

ADOC.ConnectionString := 'PROVIDER = MSDASQL; DRIVER={MySQL ODBC 3.51 Driver};
SERVER=localhost; Data Source=faketest; DATABASE=fakeschema; USER ID=root;
PASSWORD=pass; OPTION=3;';
ADOC.DefaultDatabase := 'fakeschema';  
ADOC.Connected := True;  

Am I using the wrong tools/methods? I am new to MySQL and I am currently learning Delphi.

解决方案

As mentioned in one of my comments, the issue is trying to execute multiple individual SQL statements in a single TAdoQuery component.

In an ideal world, you would have a component such as MyDAC which has a script component you could use in place of the TAdoQuery (MyDAC would give you other benefits too such as not having to connect via ODBC). I don't know if there any free MySQL components out there which have a scripting component.

Another approach is you could create a script file (eg createFakeSchema.sql) and execute it through the command line. eg:

createFakeSchema.sql:

CREATE SCHEMA IF NOT EXISTS fakeschema;   
USE fakeschema;  
CREATE TABLE table1  
(IDtable1 int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,  
Line1 varchar(45),  
Line2 varchar(45));

and example source code:

procedure TfrmMain.DoExecuteScriptFile;
var
  cmd: string;
  KeepOpen: Boolean;
begin
  KeepOpen := True;

  // option to automatically close window once execution is done
  // for releasing you would not want it kept open, but handy for debugging
  if KeepOpen then
    cmd := '/k '
  else
    cmd := '/c ';

  cmd := cmd + Format(' mysql -uroot -proot -D%s < "%s"', ['FakeSchema', 'createFakeSchema.sql']);
  ShellExecute(handle,'open', 'cmd.exe', Pchar(cmd), nil, SW_SHOW );
end;

This way you can create your script file externally somewhere, test it through MySQL yourself then when you know your script is working, you can run it through your program. If you want to hide the command window while executing change the SW_SHOW in ShellExecute to SW_HIDE. This way you don't even need any components at all - just have mysql.exe accessible in the path or include the full path in the cmd statement.

This was done in MySQL 5.1, so hopefully works for 3.5...

这篇关于Delphi-在运行时创建MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！