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...@yahoo.com> wrote:
From: SHARMILA JOTHIRAJAH <sharmi...@yahoo.com>
Subject: [GENERAL] dbi_link help
To: "General postgres mailing list" <pgsql-general@postgresql.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
