ODBC Firebird Postgresql, query execution in Powershell

    Sometimes system engineers need to get a specific data set directly from the DBMS using Powershell tools. 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 client library GDS32.DLL in the system, its bit depth must be the same as the ODBC driver that must be installed later, you can download it on the manufacturer’s website , it is mandatory during installation check the box to register the library.

    image

    Next, we install ODBC itself, which we also take on the manufacturer’s website , do not forget that its bit capacity should correspond to the bit capacity of the previously installed client. Now the powershell script itself, based on an example in C # 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
    }
    

    Logic of working with the base:

    • Open the connection to the database.
    • Create the Data Adapter, which are used to populate the DataSet.
    • We fill in the SelectCommand property (Gets or sets the SQL statement or stored procedure used to select records in the data source.), For this we create a new OdbcCommand object and pass our request as a string to its constructor, also pass a link to our open connection object.
    • We fill our DataSet with the received result of the executed query.
    • Close the connection to the database.

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

    Now consider the method using the client library; it does not require the installation of an ODBC driver and registration of 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 the package packaged in msi, since it is convenient to distribute it using SCCM. Actually the script itself, it differs from firebird only in the connection string, in which the name of the driver changes. In windows 10 and windows server 2016, a cmdlet has appeared that displays a list of registered ODBC Get-OdbcDrive drivers.

    $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, thanks for your attention.

    Also popular now: