Re: dbi_link help

Hi,
Another try to get a reply for this dbi-link problem...
I was trying to look into the dbi_link.sql code and try to figure out the problem
This is from the dbi_link.sql code

CREATE OR REPLACE FUNCTION dbi_link.add_dbi_connection_environment(
    in_data_source_id BIGINT,
    in_settings YAML
)
RETURNS VOID
LANGUAGE plperlU
AS $$
my ($data_source_id, $settings_yaml) = @_;

return unless (defined  $settings_yaml);

my $settings = Load($settings_yaml);
warn Dump($settings) if $_SHARED{debug};
die "In dbi_link.add_dbi_connection_environment, settings is a >@{[
    ref($settings)
]}<, not an array reference"
    unless (ref($settings) eq 'ARRAY');
my $count = 0;
foreach my $setting (@$settings) {
    die "In dbi_link.add_dbi_connection_environment, setting $count is not even a hash reference"
        unless (ref($setting) eq 'HASH');
    die "In dbi_link.add_dbi_connection_environment, setting $count does have the proper components"
        unless (
            exists $setting->{env_name} &&
            exists $setting->{env_value} &&
            exists $setting->{env_action}
        );
    die "In dbi_link.add_dbi_connection_environment, setting $count does have the proper right-hand sides"
        if (
            ref($setting->{env_name}) ||
            ref($setting->{env_value}) ||
            ref($setting->{env_action})
        );
    foreach my $setting (qw(env_name env_value env_action)) {
        if (defined $setting->{$setting}) {
            $setting->{$setting} = $_SHARED{quote_literal}->(
                $setting->{$setting}
            );
        }
        else {
            $setting->{$setting} = 'NULL';
        }
    }
    my $sql = <<SQL;

INSERT INTO dbi_link.dbi_connection_environment (
    data_source_id,
    env_name,
    env_value,
    env_action
)
VALUES (
    $data_source_id,
    $settings->{env_name},
    $settings->{env_value},
    $settings->{env_action}
)
SQL
    warn "In dbi_link.add_dbi_connection_environment, executing:\n$sql";
    my $rv = spi_exec_query($sql);
    if ($rv->{status} ne 'SPI_OK_INSERT') {
        die "In dbi_link.add_dbi_connection_environment, could not insert into dbi_link.dbi_connection_environment: $rv->{status}";
    }
}
return;
$$;

Here it is trying to insert rows into dbi_link.dbi_connection_environment table
INSERT INTO dbi_link.dbi_connection_environment (
    data_source_id,
    env_name,
    env_value,
    env_action
)
VALUES (
    $data_source_id,
    $settings->{env_name},
    $settings->{env_value},
    $settings->{env_action}
)
SQL

When I execute this sql, it runs fine and gives no errors. But if I query this table
=# select * from dbi_link.dbi_connection_environment;
 data_source_id | env_name | env_value | env_action
----------------+----------+-----------+------------
(0 rows)

it returns no rows...
Why is this table NULL ?

If anyone is using dbi-link and has some solution pls reply

Thanks
Sharmila

--- On Mon, 2/9/09, SHARMILA JOTHIRAJAH <sharmi_jo(at)yahoo(dot)com> wrote:
From: SHARMILA JOTHIRAJAH <sharmi_jo(at)yahoo(dot)com>
Subject: [GENERAL] dbi_link help
To: "General postgres mailing list" <pgsql-general(at)postgresql(dot)org>
Date: Monday, February 9, 2009, 2:11 PM

Hi,
Im new to dbi_link. I had installed dbi_link and run the dbi_link.sql script . This is the script that I ran after that and it didn't have any errors.  Now the schemas dbi_link and EMPLOYEE are created in my postgres database. The user is "postgres" in both the databases with the same password in both.

UPDATE
    pg_catalog.pg_settings
SET
    setting =
        CASE WHEN 'dbi_link' = ANY(string_to_array(setting, ','))
        THEN setting
        ELSE 'dbi_link,' || setting
        END
WHERE
    name = 'search_path'
;

SELECT make_accessor_functions(
   
'dbi:Oracle:database=postgres;host=...;sid=....;port=1521',
    'postgres',
    'postgres',
    '---
AutoCommit: 1
RaiseError: 1
',
    NULL,
NULL,
     NULL,
    'employee'

);

When I try to insert into or select from the oracle database I get this error...

ERROR:  error from Perl function "remote_select": error from Perl function "cache_connection": DBI connect('database=postgres;host=...;sid=...;port=1521','postgres',...) failed: ERROR OCIEnvNlsCreate. Check ORACLE_HOME (Linux) env var  or PATH (Windows) and or NLS settings, permissions, etc. at line 137 at line 13.

Im not sure what this error is. I had set my ORACLE_HOME, NLS settings etc. Is there anything else that needs to be done ?

Thanks
Sharmila