ODBC Firebird Postgresql, execution of requests in Powershell

Sometimes system engineers need to get a specific set of data directly from the DBMS itself using Powershell. In this article I want to demonstrate two methods of working with firebird, postgresql through the odbc driver and the client library.

Let's start with firebird and work with the database through the ODBC driver, first you need to register the GDS32.DLL client library in the system, its bit width should be the same as the ODBC driver that should be installed further, you can download it on the manufacturer’s site , you must check the library registration.


Next, we install ODBC itself, which we also take on the manufacturer’s website , do not forget that its bit depth should correspond to the bit depth of the previously installed client. Now the powershell script itself is actually a C # example for postgresql.

$dbServerName = "localhost:base.gdb" $dbUser = "SYSDBA" $dbPass = "masterkey" [string]$szConnect = "Driver={Firebird/InterBase(r) driver};Dbname=$dbServerName;Pwd=$dbPass;CHARSET=WIN1251;UID=$dbUser" $cnDB = New-Object System.Data.Odbc.OdbcConnection($szConnect) $dsDB = New-Object System.Data.DataSet try { $cnDB.Open() $adDB = New-Object System.Data.Odbc.OdbcDataAdapter $adDB.SelectCommand = New-Object System.Data.Odbc.OdbcCommand("Select * From users", $cnDB) $adDB.Fill($dsDB) $cnDB.Close() } catch [System.Data.Odbc.OdbcException] { $_.Exception $_.Exception.Message $_.Exception.ItemName } foreach ($row in $dsDB[0].Tables[0].Rows) { $row } 

The logic of working with the database:
')

• Open the connection to the database.
• Create a Data Adapter that is used to populate the DataSet.
• Fill in the SelectCommand property (Returns or sets the SQL statement or stored procedure used to select records in the data source.) To do this, create a new OdbcCommand object and pass its query as a string to its designer, also pass a link to our open connection object.
• We fill our DataSet with the result of the executed query.
• Close the connection to the database.

At the end of the script, the usual foreach loop goes through the received data, it is possible to add your own logic here, for example, create a mailbox according to the login, or add a user to a specific group.

Now consider the method using the client library, it does not require installing the ODBC driver and registering the client library in the system. The script is based on examples for C #, available on the manufacturer's website.

 function SelestFireBirdDB ($string) { [Reflection.Assembly]::LoadFile("C:\files\dll\FirebirdSql.Data.FirebirdClient.dll") $TestLog = "D:\tmp\TestLog.txt" #  $connectionString = "User=SYSDBA;Password=masterkey;Database=base.gdb;DataSource=localhost;Dialect=1;Pooling=true;MaxPoolSize=3;Connection Lifetime=60" $connection= New-Object FirebirdSql.Data.FirebirdClient.FbConnection($connectionString) try { $connection.Open() } catch { $_.Exception $_.Exception.Message $_.Exception.ItemName } # $Transaction = New-Object FirebirdSql.Data.FirebirdClient.FbTransactionOptions $BeginTransaction = $connection.BeginTransaction($Transaction) #  $Command= New-Object FirebirdSql.Data.FirebirdClient.FbCommand($string,$connection,$BeginTransaction) $Command.Parameters.Clear $Command.Parameters.AddWithValue("Speed", 100) #   $FbDataAdapter= New-Object FirebirdSql.Data.FirebirdClient.FbDataAdapter($Command) $FbDataAdapter $DataSet= New-Object System.Data.DataSet try { $FbDataAdapter.Fill($DataSet) $Selest= $DataSet.Tables[0] } catch { $_.Exception $_.Exception.Message $_.Exception.ItemName } finally { $BeginTransaction.Rollback() $connection.Close() } return ,$Selest } 

I conclude the article with an example for postgresql , using the ODBC driver. First you need to install it, I used a package packed in msi, because it is convenient to distribute it using SCCM. Actually the script itself, it differs from firebird only in the connection string, in which the driver name changes. In windows 10 and windows server 2016, a cmdlet appeared that shows the list of registered ODBC drivers Get-OdbcDrive.

 $dbServer = "192.168.0.10" $dbName = "core" $dbUser = "postgres" $dbPass = "postgres" $port = "5432" [string]$szConnect = "Driver={PostgreSQL Unicode(x64)};Server=$dbServer;Port=$port;Database=$dbName;Uid=$dbUser;Pwd=$dbPass;" $cnDB = New-Object System.Data.Odbc.OdbcConnection($szConnect) $dsDB = New-Object System.Data.DataSet try { $cnDB.Open() $adDB = New-Object System.Data.Odbc.OdbcDataAdapter $adDB.SelectCommand = New-Object System.Data.Odbc.OdbcCommand("SELECT id, name, age, login FROM public.users" , $cnDB) $adDB.Fill($dsDB) $cnDB.Close() } catch [System.Data.Odbc.OdbcException] { $_.Exception $_.Exception.Message $_.Exception.ItemName } foreach ($row in $dsDB[0].Tables[0].Rows) { $row.login $row.age } 

I hope this article will be useful, thank you for your attention.