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
With cross-database queries, you can seamlessly query data from any database in the cluster, regardless of which database you are connected to. Cross-database queries can eliminate data copies and simplify your data organization to support multiple business groups on the same cluster.
Description. dblink_connect() establishes a connection to a remote PostgreSQL database. The server and database to be contacted are identified through a standard libpq connection string. Optionally, a name can be assigned to the connection.
If you are encountering this error in DBeaver, setting the target database to active may resolve it:
This is related to multi-database support
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With