Check if a database exists - PostGres
lazarus / 16-Databases

----------------------------------------------------------------------
fpcoder · 2017-02-21 16:53

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
DBExists(const ADBName: string; AConn: TSQLConnection): Boolean;begin 
Result:= False;  AConn.DatabaseName:=ADBName;  try    try     
AConn.Open;      Result:= AConn.Connected;    except      ;    end; 
finally    AConn.Close;  end;end; 
Would it be useful to have a similar function within the
TSQLConnection? To call like this:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i if
Conn.DBExists('dbname') then ..

----------------------------------------------------------------------
valdir.marcos · 2017-02-21 17:06

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Program
Example38; { This program demonstrates the FileExists function } Uses
SysUtils; begin  if FileExists(ParamStr(0)) then    WriteLn ('All is
well, I seem to exist.');end.

----------------------------------------------------------------------
balazsszekely · 2017-02-21 17:17

Quote@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 · 2017-02-21 17:27

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 · 2017-02-21 17:33

Quote from: GetMem on February 21, 2017, 06:17:08
pmQuote@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 · 2017-02-21 17:36

Good catch @molly:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
DBExists(const ADBName: string; AConn: TSQLConnection): Boolean;begin 
Result:= False;  AConn.DatabaseName:=ADBName;  try    AConn.Open;   
Result:= AConn.Connected;    if Result then       AConn.Close;  except
     end;    end; 
But even the modified code won't prevent the IDE to raise an exception
if the connection fails.

----------------------------------------------------------------------
paweld · 2017-02-21 17:47

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
DBExists(const ADBName: string; AConn: TSQLConnection): Boolean;begin 
Result:= False;  AConn.DatabaseName:='postgres';  try    AConn.Open; 
 Query1.SQL.Text:=' select 1 from pg_database where name=:dbname ';  
Query1.ParamByName('dbname').AsString:=ADBName;    Query1.Open;   
Result:=Query1.RecordCount>0;    AConn.Close;  except      end;  
 end;

----------------------------------------------------------------------
molly · 2017-02-21 17:59

Quote from: GetMem on February 21, 2017, 06:36:57 pmBut 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 · 2017-02-21 18:40

Quote@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:
QuoteWhen 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 · 2017-02-21 18:44

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).