Solved

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

Posted on 2000-03-28
9
175 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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

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!

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
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…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

724 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