24.10.2016 16:03
Didenko_G 5 2400 Источник
Иногда у системных инженеров возникает необходимость получить определенный набор данных непосредственно из самой СУБД средствами Powershell. В данной статье хочу продемонстрировать два метода работы c firebird, postgresql через odbc драйвер и клиентскую библиотеку.

Начнем с firebird и работу с базой через ODBC драйвер, для начала нужно зарегистрировать в системе клиентскую библиотеку GDS32.DLL, ее разрядность должна быть такой же как и ODBC драйвера который должен быть установлен далее, скачать можно на сайте производителя, обязательно во время установки необходимо поставить галочку о регистрации библиотеки.

image

Далее устанавливаем сам ODBC, который также берем на сайте производителя, не забываем что его разрядность должна соответствовать разрядности ранее установленного клиента. Теперь собственно сам powershell скрипт, за его основу был взят пример на C# для 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
}

Логика работы с базой:

  • Открываем подключение к базе.
  • Создаем Data Adapter, которые используются для заполнения DataSet.
  • Заполняем свойство SelectCommand (Возвращает или задает инструкцию SQL или хранимую процедуру, используемую для выбора записей в источнике данных.), для этого создаем новый объект OdbcCommand и его конструктору передаем в виде строки наш запрос, также передаем ссылку на объект нашего открытого подключения.
  • Заполняем наш DataSet полученным результатом выполненного запроса.
  • Закрываем подключение к базе.

В конце скрипта обычным циклом foreach, идет перебор полученных данных, здесь возможно дописать собственную логику, к примеру создать почтовый ящик согласно логина, или добавить пользователя в определенную группу.

Теперь рассмотрим способ с использованием клиентской библиотеки, он не требует установки ODBC драйвера и регистрации клиентской библиотеки в системе. Скрипт основан на примерах для C#, доступны на сайте производителя.

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
}

Завершаю статью примером для postgresql, с использованием ODBC драйвера. Сначала необходимо его установить, я использовал упакованный в msi пакет, поскольку его удобно распространять средствами SCCM. Собственно сам скрипт, он отличается от firebird только строкой подключения, в которой меняется название драйвера. В windows 10 и windows server 2016 появился командлет, который покажет список зарегистрированных ODBC драйверов 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
}

Надеюсь данная статья будет полезной, спасибо за внимание.
Поделиться с друзьями
-->

Комментарии (5)


  1. Diaskhan
    24.10.2016 21:39
    #9875812
    -1

    Мсьё Вы забыли поставить кавычку после «Select * From users


    1. Didenko_G
      24.10.2016 22:06
      #9875854

      Спасибо поправил.


  1. DeniSun
    26.10.2016 00:23
    #9877774

    Немного замечаний.
    1. При формировании ConntcntionString лучше использовать «Dialect=3». Диалект 1-й был давно для совместимости с InterBase 5.x.
    2. Лучше прямо указать место расположения базы «Database=c:\fb_base\base.gdb»
    3. Чтобы не использовать стандартную GDS32.DLL достаточно в строке подключения указать нужную «ClientLibrary=c:\fbemb\fbclient.dll»
    4. Если разговор идет о Powershell то вероятнее всего DataSet не нужен, достаточно однонаправленного FirebirdClient.FbDataReader, много быстрее и менее требователен к памяти (в памяти только текущая строка из БД), но есть свои минусы.
    5. При подключении в параметрах транзакции указать readonly.


    1. Didenko_G
      26.10.2016 00:38
      #9877796

      Большое спасибо за замечания, критика очень важна :)
      1. При формировании ConntcntionString лучше использовать «Dialect=3». Диалект 1-й был давно для совместимости с InterBase 5.x. — можно ссылку на статью чтобы понять принципиальное отличие.
      2. Лучше прямо указать место расположения базы «Database=c:\fb_base\base.gdb» — не соглашусь, лучше указывать сетевой путь, поскольку можно в последствии контролировать подключение на уровне сетевого подключения, удобно но linux серверах.
      3. Чтобы не использовать стандартную GDS32.DLL достаточно в строке подключения указать нужную «ClientLibrary=c:\fbemb\fbclient.dll» — использование абсолютных путей в промышленных решениях не желательно, при необходимости развертывания на большом количестве серверов.
      4. Если разговор идет о Powershell то вероятнее всего DataSet не нужен, достаточно однонаправленного FirebirdClient.FbDataReader, много быстрее и менее требователен к памяти (в памяти только текущая строка из БД), но есть свои минусы. — если реализация метода подключения вынесена в отдельную функцию, то результат лучше возвращать в DataSet, за идею спасибо, но пока не выполнял на столько тяжелых запросов, что бы прибегать к оптимизации потребления памяти.
      5. При подключении в параметрах транзакции указать readonly.- это только при select, а если необходимо выполнять CRUD, придется делать коминт.


      1. DeniSun
        26.10.2016 06:40
        #9877898

        ОК.
        1. Статей достаточно много на сайте www.ibase.ru. Как пример миграция статья от 2000г!
        2. С сетевым путем работать не будет (если база в расшаренной сетевой папке), FireBird принимает в качестве строки подключения локальный путь на указанном сервере или данная настройка (alias) должна быть выполнена в конфиге Firebird.
        В общем виде строка подключения выглядит как ServerName:local\path\to\database.fdb если локальный путь не указан, то database.fdb будет искаться рядом с исполняемым файлом.
        3. Абсолютный путь указан как пример, достаточно указать нужную ClientLibrary и положить ее рядом с исполняемым файлом (без указания пути). Такой подход дает возможность вообще не ставить на клиента Firebird, достаточно скопировать папку с файлами (из настроенного Firebird embedded, например Win 64-bit Embedded).
        Не совсем понял про промышленные решения, статья начиналась

        Иногда у системных инженеров возникает необходимость получить определенный набор данных непосредственно из самой СУБД средствами Powershell.
        Я так понимаю это какие-то разовые мероприятия.
        5. Поскольку в примере после $adDB.Fill($dsDB) идет сразу $cnDB.Close(), не вижу смысла открывать пишущую транзакцию. При обновлениях из Dataset'а нужна отдельная короткая пишущая, при чтении сильно рекомендуется открывать транзакцию с параметром readonly см. статью про транзакции:
        Для читаемых (справочных) данных имеет смысл выделить отдельную транзакцию read read_committed rec_version, поскольку такая транзакция стартует в состоянии committed и может длиться вечно. Также можно попробовать использовать ClientDataSet, чтобы прочитать данные и использовать их вообще без привязки к транзакции.

        PS: чтобы детально описать принцип работы с Firebird потребуется отдельная статья.