Check if a database exists - PostGres

[kapibara]

To check if a database exist in Postgres, I wrote this function. When run from the IDE it raises an exception if the database doesn't exist. (Need to press continue) I know its possible to disable the exception in the IDE. But maybe there is a another way to check if a database exist that doesnt raise an exception?

Code: Pascal  [Select][+][-]

1. function DBExists(const ADBName: string; AConn: TSQLConnection): Boolean;
2. begin
3.   Result:= False;
4.   AConn.DatabaseName:=ADBName;
5.   try
6.     try
7.       AConn.Open;
8.       Result:= AConn.Connected;
9.     except
10.       ;
11.     end;
12.   finally
13.     AConn.Close;
14.   end;
15. end;
16.  

Would it be useful to have a similar function within the TSQLConnection? To call like this:

Code: Pascal  [Select][+][-]

1. if Conn.DBExists('dbname') then ..

[valdir.marcos]

You could try FileExists function:

http://www.freepascal.org/docs-html/rtl/sysutils/fileexists.html
http://stackoverflow.com/questions/1137060/where-does-postgresql-store-the-database

Code: Pascal  [Select][+][-]

1. Program Example38;
2.  
3. { This program demonstrates the FileExists function }
4.  
5. Uses SysUtils;
6.  
7. begin
8.   if FileExists(ParamStr(0)) then
9.     WriteLn ('All is well, I seem to exist.');
10. end.
11.  

[balazsszekely]

@valdir.marcos
You could try FileExists function:

What if the database is on a remote server? In my opinion the only reliable way to check if a db exists is a successful connect. 

[molly]

Pardon my intrusion but afaik something is not quite right with the flow of the shown code as well.

In shown case, finally is always called. Also in case of an exception. afaik closing a not open connection will in itself raise an exception ? Either use force or add exception handler ? (or better yet, use another flow)

And to answer the question what to add to the exception part:

Code: [Select]

    except
      On E: Exception do
      begin
        WriteLn('Exception');
        Result := False;
      end;

Still requires to ignore the (specific) exception in the ide though as it will still trigger by default.

[valdir.marcos]

@valdir.marcos
You could try FileExists function:

What if the database is on a remote server? In my opinion the only reliable way to check if a db exists is a successful connect. 

I agree with you and also use the "try ... except" solution, but @kapibara is searching for other alternatives.

[balazsszekely]

Good catch @molly:

Code: Pascal  [Select][+][-]

1. function DBExists(const ADBName: string; AConn: TSQLConnection): Boolean;
2. begin
3.   Result:= False;
4.   AConn.DatabaseName:=ADBName;
5.   try
6.     AConn.Open;
7.     Result:= AConn.Connected;
8.     if Result then
9.       AConn.Close;
10.   except    
11.   end;    
12. end;
13.  

But even the modified code won't prevent the IDE to raise an exception if the connection fails.

[paweld]

Code: Pascal  [Select][+][-]

1. function DBExists(const ADBName: string; AConn: TSQLConnection): Boolean;
2. begin
3.   Result:= False;
4.   AConn.DatabaseName:='postgres';
5.   try
6.     AConn.Open;
7.     Query1.SQL.Text:=' select 1 from pg_database where name=:dbname ';
8.     Query1.ParamByName('dbname').AsString:=ADBName;
9.     Query1.Open;
10.     Result:=Query1.RecordCount>0;
11.     AConn.Close;
12.   except    
13.   end;    
14. end;

[molly]

But even the modified code won't prevent the IDE to raise an exception if the connection fails.

Doesn't ide options debugger ignore language exception work for you ? You have to be explicit about the exception though.

Once the exception triggered the ide even ask if it is allowed to ignore the specific exception. Or does opening a non existent database causes an external sigv error ?

[balazsszekely]

@molly
Doesn't ide options debugger ignore language exception work for you ? You have to be explicit about the exception though.

Sure, it works, I only respond to OPs first post:

When run from the IDE it raises an exception if the database doesn't exist. (Need to press continue) I know its possible to disable the exception in the IDE. But maybe there is a another way to check if a database exist that doesnt raise an exception?

[molly]

Sorry GetMem. I realized too late that you was and i did not wanted to delete my post (i could have edited it though but instead left it).