Database existence checking before MyServerControl.CreateDatabase

Database existence checking before MyServerControl.CreateDatabase

Postby philippe » Sat 05 Nov 2005 16:08

Is there a way to have something like

If Exists(a_Database) then
MyServerControl1.CreateDatabase (a_Database)
?

Previously I tried a workaround with MyConnection.GetDatabseNames(a_List) but a_List remains NIL. What’s wrong ?

TIA
Philippe
philippe
 
Posts: 7
Joined: Fri 28 Oct 2005 17:05
Location: France

Postby GEswin » Sat 05 Nov 2005 18:38

Perhaps a better and faster way:

Code: Select all
myConnection.ExecSQL('create database if not exists DATABASENAME',[]);


Replace the DATABASENAME with name you need, and mysql will create the database if it doesn't exists.
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Postby philippe » Sun 06 Nov 2005 10:37

Thanks J. Pieter.
Better by far.

Philippe
philippe
 
Posts: 7
Joined: Fri 28 Oct 2005 17:05
Location: France

Postby philippe » Sun 06 Nov 2005 15:51

There is still a problem.
Before the SQL statement is executed MyConnection needs to be connected to the Database which perhaps doesn't exist ...

Any idea?

Philippe
philippe
 
Posts: 7
Joined: Fri 28 Oct 2005 17:05
Location: France

Postby GEswin » Sun 06 Nov 2005 23:45

Leave Database blank (It's not obligated to fill) or specify a always existing database like mysql.
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Postby philippe » Mon 07 Nov 2005 09:37

"...specify a always existing database like mysql."
That's the workaround I use.
A little bit frustrating however.
A MySQL database is simply a directory, prone to accidental loss or destruction due to an external attack. A more rigourous verification of Database existence before any further processing would be wise.

Many thanks
Philippe
philippe
 
Posts: 7
Joined: Fri 28 Oct 2005 17:05
Location: France

Postby GEswin » Mon 07 Nov 2005 13:01

WEll, as said before, you can leave it blank, and second, if mysql database is somehow deleted, then you'll have bigger problems than creating new databses, you won't allow to login or do anything, since it's where user & privilege information is stored.
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Postby philippe » Mon 07 Nov 2005 15:31

"...you can leave it blank..."
Yes in Object Inspector but not in Login Prompt which arise very early (maybe due to my TMyConnectDialog, but I did not check this enough...)

"if mysql database is somehow deleted, then you'll have bigger problems than creating new databses, you won't allow to login or do anything, since it's where user & privilege information is stored."
My application must survive in a downgraded mode in case something is wrong with MySQL, or at least offer to the system administrator a clear debugging guide. I try to have as few assumptions as possible about an external process availability, integrity and s.o.

Philippe
philippe
 
Posts: 7
Joined: Fri 28 Oct 2005 17:05
Location: France

Postby GEswin » Mon 07 Nov 2005 17:06

Ok the blank issue it's better people from CrLab respond, i never use LoginPrompt dialog, i normally build my own.

Second point, if mysql database is dropped, then you'll have to reinstall, i'm sure this is the smallest problem of all... You won't be able to connect if this database doesn't exist.
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Postby philippe » Mon 07 Nov 2005 18:20

Second point, if mysql database is dropped, then you'll have to reinstall, i'm sure this is the smallest problem of all... You won't be able to connect if this database doesn't exist.

This is exactly where the problem is. I want the application regenerates itself the missing database - and this is the case at least with the first time run - but one cannot connect immediately to it. I expected TMyServerControl appropriate for that since it contains already CreateDatabase and DropDatabase...

Thanks J. Pieter for useful insight.

Philippe
philippe
 
Posts: 7
Joined: Fri 28 Oct 2005 17:05
Location: France

Postby GEswin » Mon 07 Nov 2005 23:22

Well TMyServerControl also depends on a TMyConnection, so you'll have the same problem if you use Connection Dialog.
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Postby Ikar » Tue 08 Nov 2005 08:47

Geswin is almost rights.CreateDatabase method is a thin wrapper over CREATE DATABASE statement. So, there is no practical difference between their calls. Regarding the main problem, I can advise to use something like this:
Code: Select all
    MyServerControl1.Connection := MyConnection1;
    MyConnection1.Database := 'qwerty';
    try
      MyConnection1.Connect;
    except
      on E: EMyError do
        if E.ErrorCode <> ER_BAD_DB_ERROR then
          raise
        else
        begin
          s := MyConnection1.Database;
          MyConnection1.Database := '';
          MyConnection1.Connect;
          MyServerControl1.CreateDatabase(s);
          MyConnection1.Database := s;
        end;
    end;
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby philippe » Thu 10 Nov 2005 06:55

Thanks Ikar.
I will implement that.

Philippe
philippe
 
Posts: 7
Joined: Fri 28 Oct 2005 17:05
Location: France


Return to MySQL Data Access Components