Database existence checking before MyServerControl.CreateDatabase

philippe · Post by **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

GEswin · Post by **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.

philippe · Post by **philippe** » Sun 06 Nov 2005 10:37

Thanks J. Pieter.
Better by far.

Philippe

philippe · Post by **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

GEswin · Post by **GEswin** » Sun 06 Nov 2005 23:45

Leave Database blank (It's not obligated to fill) or specify a always existing database like mysql.

philippe · Post by **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

GEswin · Post by **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.

philippe · Post by **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

GEswin · Post by **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.

philippe · Post by **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

GEswin · Post by **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.

Ikar · Post by **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;

philippe · Post by **philippe** » Thu 10 Nov 2005 06:55

Thanks Ikar.
I will implement that.

Philippe