Forum: open-discussion

Monitor Forum | Start New Thread Start New Thread
Oracle special caracter [ reply ]
By: paulo moraes on 2011-09-13 18:31
Hi All,
In table oracle I have for example:
When dbi-link call information from oracle his show

Have a support for LATIN1 caracter?

problem with remote_select [ reply ]
By: Bill Eaton on 2011-06-30 00:49
I've been Googling like crazy to get to this point. I have installed dbi_link into a sample database.

I'd like to do a simple query to a SQL Server database. remote_select is a good choice (I think) because it doesn't try to write anything into a local table or synchronize anything.

Problem is I get
ERROR: error from Perl function "remote_select": Must issue a query! at line 18.
SQL state: XX000

My query (from pgadmin3) looks like

select foo from dbi_link.remote_select('dbi:sybase:server=myserver'::dbi_link.data_source,
AutoCommit: 1
RaiseError: 1
'select foo from mytable where foo = 3'::text)
as (foo text);

I have no idea what the YAML is supposed to look like. I copied it straight from one of the example files.

Hopefully, I just have a minor syntax error

Thanks in advance,

PgSQL to MSSQL [FreeTDS] : error column_info [ reply ]
By: Laurent BAEY on 2011-02-21 10:44
I properly configures a connection from PotsgreSQL to MSSQL Server with FreeTDS -> unixODBC -> DBD:ODBC but I ran into the following error while running make_accessor_functions()

DBD::ODBC::db column_info failed: [FreeTDS][SQL Server]Invalid cursor state (SQL-24000) at line 66.

I found that this driver can't make the call to sp_columns (->column_info) while the sp_tables (->table_info) call isn't finished.

Therefore, one need to get the thing done in two steps :

--- instead of ---
while(my $table = $sth->fetchrow_hashref) {
next if exists $dup_tabs{ $table->{TABLE_NAME} };
++$dup_tabs{ $table->{TABLE_NAME} };
--- put this ---
my @tables = ();
my $i = 0;
while(my $t = $sth->fetchrow_hashref) {
next if exists $dup_tabs{ $t->{TABLE_NAME} };
# if you need to filter on "table" objects, uncomment the following line
#next if $t->{TABLE_TYPE} ne 'TABLE';
warn "Adding table to shadow : $t->{TABLE_NAME} de type $t->{TABLE_TYPE}" if $_SHARED{debug};
$tables[$i] = $t;
++$dup_tabs{ $->{TABLE_NAME} };
warn "Got $#tables tables in schema" if $_SHARED{debug};
for $i (0..$#tables) {
--- end of snippet ---

This solved my issue, I hope it can help others.


error connecting to YAML [ reply ]
By: p raman on 2007-04-06 20:30
I dropped the dbi_link on my db and recreated it. Then when I tried to ceate new dbi_link to a MySQL db I get the following error.

ERROR: value for domain yaml violates check constraint "yaml_check"
SQL state: 23514

Error load .sql with odbc driver [ reply ]
By: trentasis trentasis on 2010-07-31 11:16

I have configured dbi-link with mssqlserver and is working correctly, but now I'm Trying to configure with an ODBC connection (database is Intersystems Cache), and when I load sql commands to configure dbi-link scripts returns:

psql:/tmp/file.sql:25: ERROR: error from Perl function "make_accessor_functions": error from Perl function "create_accessor_methods": DBD::ODBC::st fetchrow_hashref failed: (DBD: st_fetch/SQLFetch err=-1) at line 80. at line 56.

I like to know if it is compatible with dbi-link and how can I solve this error


No data dbi-link sql server 2000 [ reply ]
By: trentasis trentasis on 2010-07-28 13:11

I have configured dbi-link on a Debian Lenny, It seems that all is installed correctly
1.- create database
2.- createlang
3.- load dbi-link
4.- Load mssql script

But he problem is that appears new schema and mapped tables but all tables hasn't any data. also appears all tables with name_Shadow and the fields are duplicated with new_field, old_field and also a a field named iud_action
I have tried with mysql and mssql with same problems.
I have chcecked with a perl script and works correctly, but no with dbi-link.
What Can I do?

Also I have a question it is possible to use with ODBC, I have to connect to other database, I have an ODBC working, but I can't find how to configure this with dbi-link

I have searched information about to configure dbi-link with mssql (SQL Server 2000) and ODBC but I can't find more information.


windows install [ reply ]
By: Sebastian Quevedo on 2010-06-22 21:40
can be installed on windows dbilink..?

Error on connecting to MySQL [ reply ]
By: --- --- on 2010-04-26 10:35
hi, i've installed dbi-link and created a mysql connection.
Now i have my schema under postgre, tables and views, everything seems fine, buf if i try to make any query i get:
ERROR: errore dalla funzione Perl "remote_select": errore dalla funzione Perl "cache_connection": DBI connect('lta;host=VServer-Zawardo','root',...) failed: Unknown MySQL server host 'VServer-Zawardo' (11001) at line 137 at line 13.

obviously the server name is correctly resolved and was used to make the connection (so the mysql server is 100% connectable), i've tried to substitute the name with the ip, but same result.
Any idea?

Oracle Link Fails After Restarting Psql [ reply ]
By: Timothy Myer on 2009-06-04 01:21
I have been trying out DBI-Link and have been able to get to the point where I can run remote select and insert statements from my PostgreSQL 8.3 installation to an Oracle 11g installation, both running on Win2K. Unfortunately I seem to have encountered a slight problem. The remote link no longer works after I restart psql. These are the steps I am using to reproduce the issue:
1. As the superuser, create a new database, schema and role.
2. Run createlang for plperlu on the new database as the superuser.
3. Run the dbi_link.sql for the new database as the superuser.
4. In psql, connect to the new database as superuser.
5. Run UPDATE pg_catalog.pg_settings and dbi_link.make_accessor_functions()
6. Now when I "\dt" in psql, I can see the three dbi_link tables and the _shadow table from my remote connection.
7. If I run 'SELECT * FROM oracle_link."BOOK";' I see the results from the remote query.
8. Then I quit psql (with "\q") and reconnect as the same user.
9. Now, when I "\dt", I get a message that there are no relations to display.
10. When I run the query 'SELECT * FROM oracle_link."BOOK";' I get the following error message about a missing *.dll (which is in fact not missing):
ERROR: error from Perl function "remote_select": error from Perl function "cache_connection": install_driver(Oracle) failed: Can't load '$PERL_HOME/lib/auto/DBD/Oracle/Oracle.dll' for module DBD::Oracle: load_file:The specified module could not be found at $PERL_HOME/lib/ line 226. at line 3
Compilation failed in require at line 3.
Perhaps a required shared library or dll isn't installed where expected at line 137 at line 13.

I read a few posts that this message might appear if there is a permissions-related issue, but I do not see how this could be permissions-related as I am the superuser and was able to connect remotely in the previous psql session.
I am just not sure what might be going on here, how I could troubleshoot this and would appreciate any help.


Execution du script dbi_link.sql [ reply ]
By: Alain PERSONNAT on 2009-09-02 10:20

When I try to execute the script dbi_link.sql I have the following message :

psql:dbi_link.sql:86: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
psql:dbi_link.sql:86: connection to server was lost

Thank your for your help.


Aggregate Functions [ reply ]
By: Michael Olschimke on 2009-08-07 19:12

I wonder how dbi_link exactly works regarding the use of aggregate functions.

If i execute the following statement on my pg database:

select count(*), sum(costs), avg(price) from external_table
inner join customers on (external_table.customer_id = customers.customer_id)
group by customers.customer_id

will the aggregate executed on the external RDBMS or will dbilink fetch all data from the external source and execute the aggregate functions in pg?

Thank you very much.


dblink to SQL 2005 Server from postgres 8.3 [ reply ]
By: Flex Padawan on 2009-05-31 04:47
I'm having troubles getting this code to work. I keep getting
ERROR: could not establish connection
DETAIL: server closed the connection unexpectedly...

Is there something wrong with my syntax?

SELECT * FROM dblink('host= dbname=testDB port=1433 user=testUser password=password1234', 'SELECT description FROM Hardware') RETURNS (result TEXT);

Thanks in advance!

From Oracle Date to PostgreSQL Timestamp [ reply ]
By: Andrea Agosti on 2009-05-11 10:33
Hi all, I need some hints on how I can get date and time information from Oracle Date type. I run the script and I have the schema in PostgreSQL, with Date type converted in Timestamp (I've modified the script for that), but I cannot retrieve hour and minute information because it's necessary to explicitly require them through the function to_char(). And that is the problem, how can I modify the script for use the required function?

I hope to be clear and sorry for my poor english. Thanks

   Older Messages
Powered By FusionForge