Cross-Database References

caracadon · #1 (**permalink**) 10-25-10, 14:15

I am building an Add In for Excel and am using ADO 2.8 from Excel 2007 & VBA 6.0 to connect and return a recordset - pretty simple stuff.

Really new to PostgreSQL but use SQL Server daily. Anyway, connection is made and recordset object instanced all is well. The problem comes with the SQL. A simple example below.

Code:

select f_table_catalog from postgis.public.geometry_columns

Really any SQL used returns the error below

Quote:

Run-Time error '-2147467259(80004005)'
ERROR: cross-database references are not implemented:
"postgis.public.geometry_columns"
LINE 1: select f_table_catalog from postgis.public.geometry_columns

With a caret under the field name. The query works fine using pgAdminIII query tool.

Not sure what the error is trying to tell me. Any ideas?

TIA

shammat · #2 (**permalink**) 10-25-10, 14:33

You are specifying the database (postgis) as part of the table name which is not supported by Postgres.

You should use:

Code:

select f_table_catalog from public.geometry_columns

You don't even need the schema prefix (public) in there either.

caracadon · #3 (**permalink**) 10-25-10, 15:23

I now get relation does not exist error. ARGH!

If I were to do this in SQL Server I would need to full qualify the database.owner.table

The question is how does one fully qualify the path to the actual table? The hierarchy is as follows

Code:

PostgreSQL 8.4 (localhost:5432)
   postgis
      Schemas
         Public
            Tables
               geometry_columns

shammat · #4 (**permalink**) 10-25-10, 16:04

Quote:

Originally Posted by caracadon

If I were to do this in SQL Server I would need to full qualify the database.owner.table

PostgreSQL is not SQL Server

Quote:

The question is how does one fully qualify the path to the actual table?

See the example I posted.

caracadon · #5 (**permalink**) 10-25-10, 16:48

Yes, I am aware. Just thought they might be similar thats all. But if I were to need to JOIN tables from different db's might I need to to fully qualify?

At any rate, the example you posted throws an error as well.

shammat · #6 (**permalink**) 10-25-10, 16:55

Quote:

Originally Posted by caracadon

But if I were to need to JOIN tables from different db's might I need to to fully qualify?

You cannot join tables from different databases. If you need to simulate SQL Server's database, use schemas instead.

Quote:

At any rate, the example you posted throws an error as well.

Then you have a spelling error or something like that.

- Post the full table definition as CREATE TABLE statements
- Post the exact query you are running (use copy & paste)
- Post the exact error message (use copy & paste)

Steps 2) and 3) are quite easy to do from a psql command window.

caracadon · #7 (**permalink**) 10-25-10, 17:39

From the pgAdminIII query tool - all three approaches work without error.

Code:

select * from postgis.public.geometry_columns;
select * from public.geometry_columns;
select * from geometry_columns;

The problem is when accessing the connection remotely using ADO. I know this is not an ADO site but I am not understanding why it works in the query tool and not remotely.

Code:

'   quick and dirty test
'   user, pass is a variable passed to in the arg list of the procedure
    szStr = "Provider=PostgreSQL OLE DB Provider;Data Source=localhost"
    sql = "select * from geometry_columns"
    Set conn = New ADODB.Connection
    conn.Open szStr, user, pass
    
    Set rs = New ADODB.Recordset
    
    rs.Open sql, conn  '    error

The error verbatim

Quote:

ERROR: relation "geometry_manuals" does not exist
LINE 1: select * from geometry_columns

This is also true if I run it as public.geometry_columns

CREATE

Quote:

-- Table: geometry_columns

-- DROP TABLE geometry_columns;

CREATE TABLE geometry_columns
(
f_table_catalog character varying(256) NOT NULL,
f_table_schema character varying(256) NOT NULL,
f_table_name character varying(256) NOT NULL,
f_geometry_column character varying(256) NOT NULL,
coord_dimension integer NOT NULL,
srid integer NOT NULL,
"type" character varying(30) NOT NULL,
CONSTRAINT geometry_columns_pk PRIMARY KEY (f_table_catalog, f_table_schema, f_table_name, f_geometry_column)
)
WITH (
OIDS=TRUE
);
ALTER TABLE geometry_columns OWNER TO postgres;

shammat · #8 (**permalink**) 10-25-10, 17:58

Quote:

Originally Posted by caracadon

The problem is when accessing the connection remotely using ADO. I know this is not an ADO site but I am not understanding why it works in the query tool and not remotely.

Either ADO is messing with your query, or you are connecting to the wrong database.

Quote:

szStr = "Provider=PostgreSQL OLE DB Provider;Data Source=localhost"

I don't know what kind of programming language that is, but with e.g. when connecting using Java the database needs to be specified as well - I believe this is also true for C/C++ connections using libpq. I'm surprised you don't need this with that ADO thing.

Quote:

select * from postgis.public.geometry_columns;

Btw: this is only working from within pgAdmin because you are connectd to the postgis database. If you are not connected to that database, the specification of the database will not work and throw an error (and when connected to it, it doesn't make sense). So you should drop that SQL Server habit..

caracadon · #9 (**permalink**) 10-25-10, 18:10

Found it. When I copied the connection string the last part got snipped.

Code:

'    snipped
szStr = "Provider=PostgreSQL OLE DB Provider;Data Source=localhost"

'    correct
szStr = "Provider=PostgreSQL OLE DB Provider;Data Source=localhost;location=postgis"

BTW. The language is VB - language of the gods

Thanks shammat

I am curious though: why wouldn't postgis.public.geometry_columns be the fully qualified database.owner.table?

shammat · #10 (**permalink**) 10-26-10, 04:15

Quote:

Originally Posted by caracadon

why wouldn't postgis.public.geometry_columns be the fully qualified database.owner.table?

Because Postgres does not allow cross-database queries so the above is only valid when connected to the postgis database.
But as that is the current database anyway there is no need to specify it.

loquin · #11 (**permalink**) 10-26-10, 15:05

dblink may help.

I don't believe it supports joins across database boundaries, though.

Internet Services

Web Development

Digital Marketing

Consumer Tech