Problems with PostgreSQL DBI-Link / DBD-ODBC

On Mon, Oct 29, 2007 at 03:44:05PM +0100, Pit M. wrote:

I have the following problem with PostgreSQL and hope you guys can
help me out in this matter: I try to get a remote connection to any
database (e.g. MS-SQL or MS Access) using DBI-Link and DBD:ODBC. My
problem is, that everything seems fine so far, however I don't know
how to use the parameters for "make_accessor_functions()". I
searched the net for it, but can only find samples for
Perl-Scripting, but I need a sample for using it directly in SQL.

This is what I've come up so far. Hope you can point me to the right
direction.

SELECT make_accessor_functions(
'dbi:ODBC:Northwind::dbi_link.data_source,
'sa'::text,
NULL::text,
'---
AutoCommit: 1
RaiseError: 1
'::dbi_link.yaml,
NULL::dbi_link.yaml,
NULL::text,
NULL, ::text,
'Northwind'::text
);

I have a ODBC connection named "Northwind".

The error message I'm always receiving (for the MS-SQL sample) is:

ERROR: error from Perl function: error from Perl function: duplicate key
violates unique constraint "dbi_connection_data_source_key" at line 94.
at line 35.
SQL state: XX000

It looks like you're trying to connect the same data_source and
user_name twice. Check whether the existing one works :)

Are the documents unclear on the idea that you only run
make_accessor_functions() once per remote (data_source,user_name)
pair per local database?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Hi David!

Thanks for the fast reply. So you mean that I might already have created
a connection but am still trying to create the same one again?

Check whether the existing one works :)

So how can I test it?

What is the correct way of accessing this data source in a select statement?

Concerning the documents, I wouldn't say that they are unclear about
make_accessor_functions(). The problem is, that in the dbi-link
documents (quote: "Do the following, with the appropriate parameters.
"Appropriate parameters" come from the perldoc of the appropriate
DBD...") and in the dbi-odbc documents I can't find any information
about the correct use of the parameters for the ODBC-connection. That's
why I tried to create the connection so many times.

Regards,

Pit

David Fetter wrote:

On Tue, Oct 30, 2007 at 01:12:41PM +0100, Pit M. wrote:

Hi David!

Thanks for the fast reply. So you mean that I might already have
created a connection but am still trying to create the same one
again?

Yes.

Check whether the existing one works :)

So how can I test it?

Fire up psql and do:

SELECT
data_source_id,
local_schema,
data_source
FROM
dbi_link.dbi_conection;

This gives you schemas you have created successfully. You can then
check what's in schema foo by doing:

\dv foo.*

What is the correct way of accessing this data source in a select
statement?

Concerning the documents, I wouldn't say that they are unclear about
make_accessor_functions(). The problem is, that in the dbi-link
documents (quote: "Do the following, with the appropriate
parameters. "Appropriate parameters" come from the perldoc of the
appropriate DBD...") and in the dbi-odbc documents I can't find any
information about the correct use of the parameters for the
ODBC-connection. That's why I tried to create the connection so many
times.

Ah, I see. I don't have an example of dbd-odbc just yet, but you
should be able to write a very short perl program which connects to
your data source.

http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_1.html

Once you've verified that that's working, you can set up a new
connection. The parameters you pass into $dbh coincide, not
coincidentally, with the first inputs to make_accessor_functions :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Hi David,

I'm sorry to bother you again, but I still couldn't get it to work. Like
you suggested, I checked for successfully installed schemas. There was
one, which I deleted again because I don't know which of my countless
tries to create it was the successful one. I think the following code
should be alright, however when executing it, I receive the following
error message:
"ERROR: error from Perl function: error from Perl function:
DBD::ODBC::db column_info failed: [Microsoft][ODBC SQL Server Driver]Die
Verbindung ist mit Ergebnissen von einem anderen hstmt belegt
(SQL-HY000)(DBD: odbc_columns/SQLColumns err=-1) at line 66. at line 53.
SQL state: XX000"

"Die Verbindung ist mit Ergebnissen von einem anderen hstmt belegt" is
German and means "The connection is occupied/reserved with results from
another hstmt". I'm desperately trying to solve it, but have no idea
where to start.

SELECT dbi_link.make_accessor_functions(
'dbi:ODBC:test'::dbi_link.data_source,
'sa'::text,
''::text,
'---
AutoCommit: 1
RaiseError: 1
'::dbi_link.yaml,
NULL::dbi_link.yaml,
NULL::text,
NULL::text,
'test'::text
);

Hope you can help me one more time. Thanks.

Regards,

Pit

On Mon, Nov 05, 2007 at 05:02:03PM +0100, Pit M. wrote:

Hi David,

I'm sorry to bother you again, but I still couldn't get it to work.
Like you suggested, I checked for successfully installed schemas.
There was one, which I deleted again because I don't know which of
my countless tries to create it was the successful one.

Any one that got created successfully in a transaction is the
successful one.

Please start over by doing the following from within psql.

BEGIN;
DROP SCHEMA test CASCADE;
DROP SCHEMA dbi_link CASCADE;
\i dbi_link.sql
COMMIT;

If you see COMMIT after that, you have successfully loaded the
DBI-Link software into that database. If you don't, let me know what
you did get.

After that, do the following, editing the file and repeating until you
get a COMMIT at the end.

BEGIN;
\i test.sql
COMMIT;

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate