PostgreSQL Connection

PostgreSQL Connection

Majorsilence Reporting works equally well with PostgreSQL. The "world's most advanced open source database" is a great way to experiment with professional quality databases at zero cost (personally I work with MS-SQL and decided to try and get my application working at home, but rather than mess about with the Express version of SQL server I decided to try "Post-Gres" and get to know it).

New Report wizard

In the Connection tab, PostgreSQL is an option in the drop-down list of Connection types (using a built-in copy of Npgsql so nothing extra needs to be installed). You then just need to edit the Connection string with your database name, user and password (assuming default port and localhost 127.0.0.1)
Server=127.0.0.1;Port=5432;Database=testdb;User Id=postgres;Password=postgres;

The connection details can be seen later in Data..Data Sources from the top menubar, and will appear in the RDL XML as in this example :

    <DataSources>
     <DataSource Name="DS1">
      <ConnectionProperties>
        <DataProvider>PostgreSQL</DataProvider>
        <ConnectString>Server=127.0.0.1;Port=5432;Database=testdb;Uid=postgres;Pwd=postgres;</ConnectString>
      </ConnectionProperties>
     </DataSource>
    </DataSources>
    ...
    <DataSets>
      <DataSet Name="Data">
        <Query>
          <DataSourceName>DS1</DataSourceName>
          <CommandText>SELECT test1,test2 FROM test</CommandText>
        </Query>

If you are working from home, and have replicated your office's database in PostgreSQL, you should be able to swap between your work database and PostgreSQL at home by just changing the Dataprovider and ConnectString items (provided that you have the same tables and fields at your disposal).

##Notes on PostgreSQL installation I followed the postgresql.org link to the enterprisedb.com page and downloaded Installer version Version 9.2.2 - don't worry about previous versions, there's no good reason not to take the latest. The .msi file installed as easily as any other software, I accepted the defaults and used a simple "postgres" as the password as my home PC is always offline and secure.

Once installed, I tried the excellent pgAdmin III (part of the install), the standard PostgreSQL equivalent of "Management Studio" (or Enterprise Manager) from the MS SQL Server world. I easily added a 'testdb' database (using 'postgres' as the owner, amongst other obvious settings), a 'test' table, and a couple of test columns of type 'text'. A quick couple of INSERTs and a SELECT verified that this really was as simple as I'd hoped.

At first I was slightly alarmed by half a dozen PostgreSQL processes in Task Manager. A quick search revealed that it's nothing to worry about - there will be one for each connection, and a number always running for various features of PostgreSQL.

I wondered about stopping and starting the database. It runs as a service, and can be found via : My Computer (right-click).. Manage.. Services.. it will be listed as "postgresql-9.2 - PostgreSQL Server 9.2" amongst all the others. It starts off as Automatic, but I changed mine to Manual.

For some reason I didn't get STOP and START options in my XP Start Menu programs listing, but solved that by adding a couple of simple batch files in the menu folder - one NET STOP and another NET START :

    @NET STOP postgresql-9.2
    @pause

###ODBC

I then decided to try ODBC with PostgreSQL. I downloaded psqlodbc_09_01_0200-1.zip from postgresql.org and ran the psqlodbc.msi to install (easy). Then, in Data Sources (ODBC) it showed up as an option when adding a new System DSN (PSQLODBC35W.DLL). I entered the following

    Database: testdb
    Server: localhost
    User Name: postgres
    Port: 5432
    Password: postgres

and the Test button verified "Connection Successful"

###PowerShell test My next trick was to see if I could read the PostgreSQL test data from a PowerShell .ps1 script. This meant installing PowerShell v2 for XP SP3 (WindowsXP-KB968930-x86-ENG.exe).

PowerShell is a bit fussy about running scripts at first. You have to open a cmd window, run powershell, and enter the following at its prompt
PS C:\Wherever> Set-ExecutionPolicy RemoteSigned - this persuades it to trust any local scripts from then onwards.

I then find it easier to run the ps1 script from a batch file with a pause at the end, so I get a chance to see the results before they vanish :

    @echo off
     rem  Before running, run PowerShell and execute command:  PS C:\Wherever> Set-ExecutionPolicy RemoteSigned
    powershell -noexit C:\filepath\test-pg.ps1
    pause

and here's the test-pg.ps1 script that uses a {PostgreSQL UNICODE} ODBC connection string in a function "Get-ODBC-Data", and displays the SQL query results neatly in a pop-up grid dialog (out-gridview ) :

    #############
    # ODBC Data #
    #############

    function Get-ODBC-Data
    {
       param([string]$serverName=$(throw 'serverName is required.'),
         [string]$databaseName=$(throw 'databaseName is required.'),
           [string]$query=$(throw 'query is required.'))

      $conn=New-Object System.Data.Odbc.OdbcConnection
      $connStr = "Driver={PostgreSQL UNICODE};Server=$serverName;Port=5432;Database=$databaseName;Uid=postgres;Pwd=postgres;"
      $conn.ConnectionString= $connStr

      # display :
      " "
      "Connection :"
      $connStr
      " "
      "SQL :"
      $query
      " "

      [void]$conn.open
      $cmd=new-object System.Data.Odbc.OdbcCommand($query,$conn)
      $cmd.CommandTimeout=15
      $ds=New-Object system.Data.DataSet
      $da=New-Object system.Data.odbc.odbcDataAdapter($cmd)
      [void]$da.fill($ds)
      $ds.Tables[0] | out-gridview
      [void]$conn.close()
    }

    # main:
    $cSQL = "select * from test"
    Get-ODBC-Data -server localhost -database testdb -query $cSQL

###Npgsql As an alternative to ODBC, you can use Npgsql - an Open Source .Net Data Provider for Postgresql - to allow your .net programs to speak to Postgres, which is how Majorsilence Reporting interfaces with it (Npgsql.dll is used in the Majorsilence Reporting source code). Just "Add Reference" Npgsql.dll and Mono.Security.dll ...

Using Npgsql is very similar to other data access methods, using the familiar Connection and Command objects, etc -

    ' set a connection ... = new Npgsql.NpgsqlConnection("Server=127.0.0.1;Port=5432; etc    
    ' hook up a command object to that ... = new Npgsql.NpgsqlCommand("select * from table1", conn)
    ' add params if required ... command.Parameters.Add(new Npgsql.NpgsqlParameter(...
    ' Read data using DataReader ( read-only, forward-only stream of data; you must loop through it )
    '   or DataAdapters ( populate a DataSet in one go )
    ' ... NpgsqlDataReader  or  NpgsqlDataAdapter

###Good, isn't it? I hope this demonstrates how easy it is to play with the PostgreSQL database, and try Majorsilence Reporting completely free of charge.