?
Solved

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

Posted on 2000-03-28
9
Medium Priority
?
178 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Screencast - Getting to Know the Pipeline

840 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