Solved

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

Posted on 2000-03-28
9
168 Views
Last Modified: 2010-04-04
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.
0
Comment
Question by:posconsultant
  • 6
  • 3
9 Comments
 
LVL 1

Expert Comment

by:tomer_engel
ID: 2722463
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
 

Author Comment

by:posconsultant
ID: 2722708
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
 
LVL 1

Expert Comment

by:tomer_engel
ID: 2722774
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
 

Author Comment

by:posconsultant
ID: 2728131
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Expert Comment

by:tomer_engel
ID: 2728585
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
 
LVL 1

Expert Comment

by:tomer_engel
ID: 2728586
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
 
LVL 1

Expert Comment

by:tomer_engel
ID: 2728587
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
 
LVL 1

Accepted Solution

by:
tomer_engel earned 190 total points
ID: 2728588
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
 

Author Comment

by:posconsultant
ID: 2730651
OK, never thought of making a new class but it makes sense.  Thanks!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now