Delphi - Creating MySQL database at runtime

delphi, mysql, runtime, sql

Solution

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...

Problem

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.

Original source