vb.net 为什么我收到“未实现跨数据库引用”？

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

提问by JimB

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

对 postgresql 数据库的非常简单的更新，但它不起作用。sql select 语句很好，并返回正确的值。

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.

我正在使用 vb.net 和 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

回答by Chris Travers

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.

在 postgresql 日志中查找错误以查看实际发送到数据库的内容。我不知道如何修复您的代码，因为我不太了解该平台。您还需要转向参数化查询，因为您当前的方法将受到 sql 注入问题的影响。

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

但是，您的错误意味着您有一个额外的命名空间。正常的命名空间是schema.table.column或schema.table取决于上下文。如果您尝试指定一个表，schema.table.column它会将其读取为database.schema.table并抛出此错误。同样，如果您有一个额外的点，您可能会意外指定database.schema.table.column（这也会引发错误）。

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

这是字符串化 SQL 确实是一个坏主意的部分原因，但并没有真正触及问题的表面。

回答by Reveille

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

如果您在 DBeaver 中遇到此错误，将目标数据库设置为活动可能会解决它：

This is related to multi-database support

这与多数据库支持有关