CODEWITHSUNDEEP
vb.netpostgresql
JimB
JimB

Reputation: 251

Why am I getting a "cross-database references are not implemented"?

very simple update to a postgresql database, and it's not working. The sql select statement is fine, and returns the right values.

It's when i get to the update, it throws the error: 

 {"ERROR [0A000] ERROR: cross-database references are not implemented: "openerp.public.product_template"; Error while executing the query"}.

I'm using vb.net and postgresql 9.2.

All I want it to do is change the name field to match whats in the description.

log:
LOG 0   duration: 34.000 ms  statement: SELECT * FROM product_template where import_date = '08/22/2013'
LOG 0   duration: 11.000 ms  statement: select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, d.adsrc from (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.oid = 20496) inner join pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum
LOG 0   duration: 12.000 ms  parse _PLAN000000001D2CFB60: SELECT * FROM product_template where import_date = '08/22/2013'
LOG 0   duration: 11.000 ms  statement: select ta.attname, ia.attnum, ic.relname, n.nspname, tc.relname from pg_catalog.pg_attribute ta, pg_catalog.pg_attribute ia, pg_catalog.pg_class tc, pg_catalog.pg_index i, pg_catalog.pg_namespace n, pg_catalog.pg_class ic where tc.oid = 20496 AND tc.oid = i.indrelid AND n.oid = tc.relnamespace AND i.indisprimary = 't' AND ia.attrelid = i.indexrelid AND ta.attrelid = i.indrelid AND ta.attnum = i.indkey[ia.attnum-1] AND (NOT ta.attisdropped) AND (NOT ia.attisdropped) AND ic.oid = i.indexrelid order by ia.attnum
LOG 0   duration: 0.000 ms  statement: select current_schema()
LOG 0   duration: 1.000 ms  statement: select c.relhasrules, c.relkind, c.relhasoids from pg_catalog.pg_namespace u, pg_catalog.pg_class c where u.oid = c.relnamespace and c.relname = 'product_template' and u.nspname = 'public'
LOG 0   duration: 1.000 ms  statement: select c.relhasrules, c.relkind, c.relhasoids from pg_catalog.pg_namespace u, pg_catalog.pg_class c where u.oid = c.relnamespace and c.relname = 'product_template' and u.nspname = 'public'
ERROR   0A000   cross-database references are not implemented: "openerp.public.product_template"

The code:

Private Sub btnChgNameToDescr_Click(sender As Object, e As EventArgs) Handles btnChgNameToDescr.Click

    Dim objConn As New System.Data.Odbc.OdbcConnection
    Dim objCmd As New System.Data.Odbc.OdbcCommand
    Dim dtAdapter As New System.Data.Odbc.OdbcDataAdapter
    Dim ds As New DataSet

    Me.Cursor = System.Windows.Forms.Cursors.WaitCursor

    Dim strConnString As String
    Dim strSQL As String
    Dim iRecCount As Integer

    Me.Cursor = System.Windows.Forms.Cursors.WaitCursor

    If objConn.State = ConnectionState.Open Then
        'do nothing
    Else
        strConnString = "Dsn=PostgreSQL35W;database=OpenERP;server=localhost;port=5432;uid=openpg;pwd=openpgpwd"
        objConn.ConnectionString = strConnString
        objConn.Open()
    End If


    If Me.txtImportDate.Text = "" Then
        MsgBox("Import Date field cannot be blank.")
        Exit Sub
    End If

    Dim str_import_date As String = Me.txtImportDate.Text


    strSQL = "SELECT * FROM product_template where import_date = " & "'" & str_import_date & "'"

    dtAdapter.SelectCommand = objCmd

    With objCmd
        .Connection = objConn
        .CommandText = strSQL
        .CommandType = CommandType.Text
        .ExecuteNonQuery()

        dtAdapter.Fill(ds, "product_template")

        iRecCount = ds.Tables("product_template").Rows.Count

    End With

    If iRecCount = 0 Then
        MsgBox("No records found.")
        Me.Cursor = System.Windows.Forms.Cursors.Default
        Exit Sub
    End If


    Dim cb As New Odbc.OdbcCommandBuilder(dtAdapter)


    'change the name field to item_description
    With ds
        For i As Integer = 0 To .Tables("product_template").Rows.Count - 1

            'this works, returns a string
            Dim str_default_code As String = (.Tables(0).Rows(i).Item("name").ToString)
            'this works
            Dim str_item_description As String = (.Tables(0).Rows(i).Item("description").ToString)

            .Tables("product_template").Rows(i).Item("name") = str_item_description
            'setting the variable doesn't work either - Dim str_item_description As String = "BH LITE BRT"

            'this throws the error
            dtAdapter.Update(ds, "product_template")

        Next
    End With

    Me.Cursor = System.Windows.Forms.Cursors.Default
End Sub

Upvotes: 20

Views: 51482

Answers (5)

kartik
kartik

Reputation: 2135

Noticed same error with Redshift when doing truncate table with schema name prefixed.

truncate schema_name.table_name;

While Copy from S3 load was done without schema name. In summary, Truncate table shall have same pattern as was used in Copy command to avoid this error in Redshift.

https://docs.aws.amazon.com/redshift/latest/dg/r_TRUNCATE.html

Upvotes: 0

HagaiA
HagaiA

Reputation: 373

I had the same issue using DataGrip (version 2020.3).

In my case it happened because I changed the connection settings, but kept using the same console window.

I found 2 ways to solve this:

  1. Change the session of the console (right click in the console --> switch session --> new session)
  2. Open a new console window

Upvotes: 1

CRT
CRT

Reputation: 510

Was working on something today and got this -- ended up it was because my db name had capitals in it, and the command didn't -- prodCRT.public.tablename vs. prodcrt.public.tablename

As soon as I renamed the schema to be lowercase all my commands that worked previously worked again.

NOTE: the original database was called just 'prod' - so things broke when I called it prodCRT

Upvotes: 0

Reveille
Reveille

Reputation: 4629

If you are encountering this error in DBeaver, setting the target database to active may resolve it:

enter image description here

This is related to multi-database support

Upvotes: 10

Chris Travers
Chris Travers

Reputation: 26464

Look for errors in your postgresql log to see what's actually getting sent to the db. I don't know how to fix your code because I don't know that platform very well. Also you need to move to parameterized queries as your current approach will be subject to sql injection issues.

However, your error means that you have an extra namespace. The normal namespace is schema.table.column or schema.table depending on the context. If you try to specify a table as schema.table.column it will read this as database.schema.table and throw this error. Similarly if you have an extra dot, you could accidently specify database.schema.table.column (which would also throw the error).

this is part of a reason why stringified SQL is really a bad idea, but doesn't really scratch the surface of the issue.

Upvotes: 6

Related Questions