1

I have a PostgreSQL database in use for a complex php web site (And VB.Net/C# management applications) which require access to execute stored procedures on a Microsoft SQL Server 2008 database. The method chosen to perform this interaction is to use plperl functions within the PostgreSQL database to wrap access to the MSSQL stored procedures.

We are using the perl DBI module to handle the data retrieval from MSSQL database.

I am a neophyte when it comes to perl in general and specifically when using it with PostgreSQL. I have created the function shown below to return a set of composite type

CREATE TYPE "public"."permissions_return" AS (
  "rolename" TEXT,
  "roledescription" TEXT,
  "permissionname" TEXT,
  "permissiondescription" TEXT
);

The stored proc called from within the function works fine and returns data when run through a slightly different perl script run from the command-line, or directly from the MSSQL Server. I have not been able to figure out how to return data from my function when using:

SELECT * FROM fn_perltest(153);

The result is always an empty set.

CREATE FUNCTION fn_perltest(integer) RETURNS SETOF permissions_return AS $$
    use strict;
    use DBI;

    my $data_source = q/dbi:ODBC:Production/;
    my $user = q/afunkyusername/;
    my $password = q/afunkierpassword/;

    my $dbh = DBI->connect($data_source, $user, $password);

    my $sth = $dbh->prepare(q/up_DCORsel_getUserPermissionByUserID $1 ;/);

    $sth->execute();

    while ( defined ( my $row = $sth->fetchrow_array() )) {
        return next ({ 
            rolename => $row->{RoleName}, 
            roledescription => $row->{RoleDescription},
            permissionname => $row->{PermissionName}, 
            permissiondescription => $row->{PermissionDescription}
       });
    }
    return;

$$ LANGUAGE 'plperlu'

If this helps, Postgres is running on a Fedora 13 server. Access to MSSQL Server is configured using unixODBC with the freetds driver. Access to the MSSQL server has been tested and works fine using the isql command-line tool and a simple perl script.

Any ideas would be greatly appreciated. I'm concerned I may run out of hair to pull out.

Regards

Shane

Shane Brodie
  • 117
  • 1
  • 1
  • 8
  • Only idea I have is to simply debug that PL/Perl code to find out where (precisely) problem occurs. There are three (elementary) steps: establish connection, query execution and returning result. – Grzegorz Szpetkowski Jun 09 '11 at 20:24

1 Answers1

1

This doesn't answer your question directly, but I have used dblink when attempting to have one database query data in another database. It seemed to work well. Obvious plperlu has a lot more power than dblink, but I don't have any experience with it (just perl and postgresql :-)

dblink can be found in postgresql's contrib directory.

Seth Robertson
  • 30,608
  • 7
  • 64
  • 57
  • Thank you for your response. I researched dblink earlier. Unfortunately, unless I have missed something, dblink only works when connecting to remote PostgreSQL servers. In my case I am executing stored procedures on an MSSQL 2008 Server from within PostgreSQL. – Shane Brodie Jun 09 '11 at 16:17
  • Actually if you use *DBI-link* (do a google search for it) you can connect to any db that Perl can reach :-) – Chris Travers Mar 13 '13 at 08:50