ODBC Firebird, Postgresql, executing queries in Powershell

Sometimes, system engineers may need to obtain specific data directly from the DBMS by means of Powershell. In this article I want to demonstrate two methods of operating firebird, postgresql through the odbc driver and client library.

let's Start with the firebird and work with the database through the ODBC driver, you first need to register in the system client library GDS32.DLL, its bit width should be the same as the ODBC driver which should be installed next, download on the manufacturer's website, be sure during installation you need to tick on the registration of the library.

Next install itself ODBC, who also take on the manufacturer's website, do not forget that the bit size should match the bit count of a previously installed client. Now actually have a powershell script, it was based on example in C# for postgresql.

the

$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
}

Logic work with the database:

the

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

At the end of the script the usual foreach loop is iterating the obtained data, it is possible to add custom logic, for example to create a mailbox under the login or add a user to a specific group.

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

the

function SelestFireBirdDB ($string)
{
[Reflection.Assembly]::LoadFile("C:\files\dll\FirebirdSql.Data.FirebirdClient.dll")
$TestLog = "D:\tmp\TestLog.txt"

#Connection string
$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

$Transaction = New-Object FirebirdSql.Data.FirebirdClient.FbTransactionOptions
$BeginTransaction = $connection.BeginTransaction($Transaction)

#Create the query 

$Command= New-Object FirebirdSql.Data.FirebirdClient.FbCommand($string,$connection,$BeginTransaction)
$Command.Parameters.Clear
$Command.Parameters.AddWithValue("Speed", 100)

#Create data adapter

$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
}

Conclude with an example for postgresql, using the ODBC driver. First, you need to install, I used Packed in the msi package because it is convenient to extend the SCCM tools. The script itself, it only differs from the firebird connection string, which changes the driver name. In windows 10 and windows server 2016 appeared cmdlet that will show the list of registered ODBC drivers Get-OdbcDrive.
the

$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 was useful, thank you for your attention.

Article based on information from habrahabr.ru