How do I create a database on the fly in an install program?

I'm doing an install program in Delphi 5, all native code & components, and I need to have the option to create a database via scripts on the fly.  I also need to install BDE and set aliases AND test the connection.  I've done a bit of DML type db programming in Delphi, but nothing like this.  Any help gratefully appreciated.
posconsultantAsked:
Who is Participating?
 
tomer_engelCommented:
ok,, I sat and thought about your problem,and as i can see it(SORRY VERY MUCH in case i'm wrong)but it's parsing the sql statments

in that case make a new Class(or something like that)lets call it TSqlStatment, that have the Parse,LoadFormFile... methods  and using TStringList to hold alot of staments--that can make debugging easier, and be more efficiant  ,,hope i helped :)
0
 
tomer_engelCommented:
i'll help ya throuh it but  first the porblems are?
1)how to intall bde?
2)creating dbat runtime -in the code or in the install shild(i didn't quite get it.
3)after the application is running test the conn ection?

wating to help ya
                 
0
 
posconsultantAuthor Commented:
Thanks for offering to help.
1) I'm using Youseful installation components (w/ sourcecode) , no InstallShield.  It comes with a TInstallBDE component which seems to work OK.  Prior to running sql scripts to create db, I will have installed BDE and the appropriate alias (user has choice of Oracle, Sybase, InterBase, and SQL Server).
2) All in the code.  What I'm trying is to add a TDataBase component to the project and use the execute method to run SQL scripts.  Haven't tested it yet though.
3) If I can create the db this way I think I can test the connection this way
too.
Here's what I have so far.  I'm still debugging other stuff so not sure if this will work:

case DBInstallType of
    idInstallLater:  exit;
    idAlreadyInstalled: exit;
    idInstallNow:
      begin
        try
          tempDB := TDatabase.Create(nil);
          tempDB.AliasName := 'MYALIAS';
          tempDB.LoginPrompt := false;
          tempDB.params.Add('PASSWORD = FOO');//ALIAS PROVIDES USER NAME
          tempDB.connected := true;
          scriptPath :=  GetDestDir + '\Server\Scripts';//GET SQL SCRIPTS (ALREADY INSTALLED ON CLIENT AT THIS POINT)
          tempStringList := TStringList.Create;
          tempStringList.LoadFromFile( scriptPath + '\Script1.sql');
          tempDB.execute( tempStringList.Text, nil, false, nil);
          tempStringList.LoadFromFile( scriptPath + '\Script2.sql');
          tempDB.execute( tempStringList.Text, nil, false, nil);
          tempStringList.LoadFromFile( scriptPath + '\Script3.sql');
          tempDB.execute( tempStringList.Text, nil, false, nil);
          tempDB.connected := false;
          tempDB.free;
          tempStringList.free;
        except
          on E: Exception do
          begin
            tempDB.free;
            tempStringList.free;
            MessageDlg(E.Message, mtError, [mbOk], 0);
          end;
        end;
      end;
    end;
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
tomer_engelCommented:
i'm sorry ,Id didnt' quite get it--you're installing the DB component-just to check the connection at runtime?
and to run sql at runtime?
let me rephrase it: is the whole purpose of the code atrtached is to run sql at run time,without having any db component ?
0
 
posconsultantAuthor Commented:
tomer,
The purpose is to create tables, triggers, and stored procedures.  The code below kinda works: the problems are:
-loading the SQL scripts into a Stringlist via loadfromfile appends carriage return and line feed (#$A#$D) at the end of each String in the list, which needs to be parsed.  
-semicolons after SQL statements cause exceptions (ie 'select * from users' ok, 'select * from users;'is not).
My scripts have many statements (create tables, triggers, etc).  Parsing semicolons is a problem because the scripts contain procedures with semicolons that are not indicative of an 'end of SQL statement' condition.

The good news is that you can run a  simple script (ith a little parsing)to create tables and stuff.I'll leave this question open for a bit if anyone has comments or suggestions...
 
procedure TCustomInstall.InstallTheDatabase;
var
  tempDB : TDatabase;
  tempQuery : TQuery;
  tempStringList : TStringList;
  sqlStatement : String;
  scriptPath : String;  
begin
  //do we need to install?
  case DBInstallType of
    idInstallLater:  exit;
    idAlreadyInstalled: exit;//deprecated
    idInstallNow:
      begin
        try
          tempDB := TDatabase.Create(nil);
          tempQuery := TQuery.Create(nil);
          tempDB.AliasName := 'MYALIAS';
          tempDB.LoginPrompt := false;
          tempDB.DatabaseName := 'tempDB';
          tempDB.params.Add('USER NAME:BOB');//UserName
          tempDB.params.Add('PASSWORD:BOB');//+DBPassword
          tempQuery.DatabaseName := 'tempDB';
          tempDB.connected := true;
          scriptPath :=  GetDestDir + '\scripts';
          tempStringList := TStringList.Create;
         with tempQuery do
          begin
            sql.loadfromFile( scriptPath + '\test.sql');            
            Prepare;
            ExecSQL;
            SQL.Clear;
            tempStringList.Clear;
          end;  
          showMessage('Database created');//success!
          tempDB.connected := false;
          tempDB.free;
          tempStringList.free;
          tempQuery.free;
        except
          on E: Exception do
          begin
            tempDB.free;
            tempStringList.free;
            tempQuery.free;
            MessageDlg(E.Message, mtError, [mbOk], 0);
          end;
        end;
      end;
    end;
end;
0
 
tomer_engelCommented:
ok,, I sat and thought about your problem,and as i can see it(SORRY VERY MUCH in case i'm wrong)but it's parsing the sql statments

in that case make a new Class(or something like that)lets call it TSqlStatment, that have the Parse,LoadFormFile... methods  and using TStringList to hold alot of staments--that can make debugging easier, and be more efficiant  ,,hope i helped :)
0
 
tomer_engelCommented:
ok,, I sat and thought about your problem,and as i can see it(SORRY VERY MUCH in case i'm wrong)but it's parsing the sql statments

in that case make a new Class(or something like that)lets call it TSqlStatment, that have the Parse,LoadFormFile... methods  and using TStringList to hold alot of staments--that can make debugging easier, and be more efficiant  ,,hope i helped :)
0
 
tomer_engelCommented:
ok,, I sat and thought about your problem,and as i can see it(SORRY VERY MUCH in case i'm wrong)but it's parsing the sql statments

in that case make a new Class(or something like that)lets call it TSqlStatment, that have the Parse,LoadFormFile... methods  and using TStringList to hold alot of staments--that can make debugging easier, and be more efficiant  ,,hope i helped :)
0
 
posconsultantAuthor Commented:
OK, never thought of making a new class but it makes sense.  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.