Thread: Problems with PostgreSQL DBI-Link / DBD-ODBC
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
I'm using following components under Windows XP
postgresql-8.2.5-1
pgadmin3-1.8.0
DBI-1.59
dbi-link-2.0.0
DBD-ODBC-1.05
Thanks in advance.
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 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.
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 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.
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