Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2010
    Location
    Western USA
    Posts
    17

    Unanswered: Cross-Database References

    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

    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

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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.

  3. #3
    Join Date
    Oct 2010
    Location
    Western USA
    Posts
    17
    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

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by caracadon View Post
    If I were to do this in SQL Server I would need to full qualify the database.owner.table
    PostgreSQL is not SQL Server

    The question is how does one fully qualify the path to the actual table?
    See the example I posted.

  5. #5
    Join Date
    Oct 2010
    Location
    Western USA
    Posts
    17
    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.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by caracadon View Post
    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.

    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.

  7. #7
    Join Date
    Oct 2010
    Location
    Western USA
    Posts
    17
    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

    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

    -- 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;

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by caracadon View Post
    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.


    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.

    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..

  9. #9
    Join Date
    Oct 2010
    Location
    Western USA
    Posts
    17
    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?
    Last edited by caracadon; 10-25-10 at 20:04.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by caracadon View Post
    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.

  11. #11
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    dblink may help.

    I don't believe it supports joins across database boundaries, though.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •