Choosing a PostgreSQL connection in Delphi

Question

how to write code like this in delphi:

#include <stdio.h>

EXEC SQL BEGIN DECLARE SECTION;
  char dbname[1024];
EXEC SQL END DECLARE SECTION;
  int main() 
  {
    EXEC SQL CONNECT TO testdb1 AS con1 USER testuser;
    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false);
    EXEC SQL COMMIT; 
    EXEC SQL CONNECT TO testdb2 AS con2 USER testuser; 
    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false);
    EXEC SQL COMMIT; 
    EXEC SQL CONNECT TO testdb3 AS con3 USER testuser;
    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false);
    EXEC SQL COMMIT;
    /* This query would be executed in the last opened database "testdb3". */
    EXEC SQL SELECT current_database() INTO :dbname;
    printf("current=%s (should be testdb3)\n", dbname);
    /* Using "AT" to run a query in "testdb2" */
    EXEC SQL AT con2 SELECT current_database() INTO :dbname;
    printf("current=%s (should be testdb2)\n", dbname);
    /* Switch the current connection to "testdb1". */
    EXEC SQL SET CONNECTION con1;
    EXEC SQL SELECT current_database() INTO :dbname;
    printf("current=%s (should be testdb1)\n", dbname);
    EXEC SQL DISCONNECT ALL; 
    return 0;
  }

Answer 1

In Delphi, you might need the TADOConnection and TADOQuery components. TADOConnection is needed for connecting to a database. TADOQuery is needed for executing SQL queries. I have prepared an example for you on how to connect to a PostgreSQL database using an ODBC driver, set search_path, and retrieve the database name for the current connection. In TADOConnection.СonnectionString, we specify a name of an ODBC driver (in this case, "PostgreSQL Unicode"), a database name, a user name, and a user password. Using TADOConnection.Execute(), we set the search_path. The TADOQuery component is used to retrieve the name of the database for the current connection. I hope this helps you solve your task.

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  SysUtils, ADODB, ActiveX;

var
  Conn1, Conn2, Conn3: TADOConnection;
  DBName: string;

procedure ConnectToDatabase(Conn: TADOConnection; const DBName, UserName, Password: string);
begin
  Conn.ConnectionString := Format('Extended Properties="DRIVER={PostgreSQL Unicode};' +
    'Database=%s;UID=%s;PWD=%s";', [DBName, UserName, Password]);
  Conn.LoginPrompt := False;
  Conn.Open;
  Conn.Execute('SELECT pg_catalog.set_config(''search_path'', '''', false);');
end;

function GetCurrentDatabase(Conn: TADOConnection): string;
var
  Query: TADOQuery;
begin
  Query := TADOQuery.Create(nil);
  try
    Query.Connection := Conn;
    Query.SQL.Text := 'SELECT current_database();';
    Query.Open;
    Result := Query.Fields[0].AsString;
  finally
    Query.Free;
  end;
end;

begin
  try
    CoInitialize(nil);
    try
      Conn1 := TADOConnection.Create(nil);
      Conn2 := TADOConnection.Create(nil);
      Conn3 := TADOConnection.Create(nil);
      try
        // Connect to testdb1
        ConnectToDatabase(Conn1, 'testdb1', 'testuser', 'pass');
        // Connect to testdb2
        ConnectToDatabase(Conn2, 'testdb2', 'testuser', 'pass');
        // Connect to testdb3
        ConnectToDatabase(Conn3, 'testdb3', 'testuser', 'pass');
        // This query would be executed in the last opened database "testdb3".
        DBName := GetCurrentDatabase(Conn3);
        Writeln('current=' + DBName + ' (should be testdb3)');
        // Using "AT" to run a query in "testdb2"
        DBName := GetCurrentDatabase(Conn2);
        Writeln('current=' + DBName + ' (should be testdb2)');
        // Switch the current connection to "testdb1".
        DBName := GetCurrentDatabase(Conn1);
        Writeln('current=' + DBName + ' (should be testdb1)');
        // Disconnect all connections
        Conn1.Close;
        Conn2.Close;
        Conn3.Close;
      finally
        Conn1.Free;
        Conn2.Free;
        Conn3.Free;
      end;
      Readln;
    finally
      CoUninitialize;
    end;
  except
    on E: Exception do
      Writeln('Error: ', E.Message);
  end;
end.

The ConnectToDatabase procedure establishes a connection to a database and also sets search_path. The GetCurrentDatabase function returns the name of the database for a specific connection.