Issues

ZF-7668: Support DB Link identifier as part of table name

Issue Type: Bug Created: 2009-08-23T20:11:48.000+0000 Last Updated: 2009-11-20T05:52:53.000+0000 Status: Resolved Fix version(s): - 1.9.6 (24/Nov/09)

Reporter: Phil Brown (philbrown) Assignee: Mickael Perraud (mikaelkael) Tags: - Zend_Db_Adapter_Oracle

  • Zend_Db_Table

Related issues: Attachments:

Description

Oracle supports DB Links in order to query remote schemas via the following syntax

<pre class="highlight">
SELECT TABLE_NAME.* FROM SCHEMA.TABLE_NAME@LINK

Using Zend_Db_Table_Abstract, if I use something like

<pre class="highlight">
protected $_schema = 'SCHEMA';
protected $_name = 'TABLE_NAME@LINK';

the generated identifiers in SELECT statements appears as

<pre class="highlight">
SELECT "TABLE_NAME@LINK".* FROM "SCHEMA"."TABLE_NAME@LINK" ...

when it should be

<pre class="highlight">
SELECT "TABLE_NAME".* FROM "SCHEMA"."TABLE_NAME"@"LINK" ...

The issue is that the @ should not be quoted in the FROM clause nor should the link be included in the column prefix.

P.S. We're stuck at ZF 1.7.4 due to RHEL PHP version however a quick glance at 1.9.1 does not seem to indicate that this issue is resolved.

Comments

Posted by Ralph Schindler (ralph) on 2009-08-26T08:51:09.000+0000

does this not work?

<pre class="highlight">
protected $_name = 'TABLE_NAME"@"LINK'

Posted by Phil Brown (philbrown) on 2009-08-26T16:21:43.000+0000

Sorry Ralph, it doesn't. That property generates queries like

<pre class="highlight">
SELECT "TABLE_NAME""@""LINK".* FROM "SCHEMA"."TABLE_NAME""@""LINK" ...

notice the extra quotes?

The problem isn't just quoting however. The link name should only appear in the FROM clause. See the last code example in my bug report above for the correct syntax.

Something tells me this will be difficult to implement into Zend_Db_Table_Abstract without affecting other, non-Oracle use cases. A solution is to create a synonym for the linked entity however this requires specific access to the schema in question and reduces portability.

Posted by Mickael Perraud (mikaelkael) on 2009-09-17T13:02:46.000+0000

The problem is that you can create a table with @ inside the name (at least with Oracle), this code functions:

<pre class="highlight">
CREATE TABLE "ZFTEST"."FOO@BAR" ("ID" NUMBER(10) NOT NULL, PRIMARY KEY("ID"));

If you access to this table from a link BAZ:

<pre class="highlight">
SELECT "ID" FROM "ZFTEST"."FOO@BAR"@BAZ

The link must be differentiate from the table name.

Posted by Mickael Perraud (mikaelkael) on 2009-09-23T04:29:18.000+0000

Perhaps you can use:

<pre class="highlight">
class My_Table extends Zend_Db_Table_Abstract
{
    public function init()
    {
        $this->_name = new Zend_Db_Expr('TABLE_NAME@LINK');
    }
}

or

<pre class="highlight">
class My_Table extends Zend_Db_Table_Abstract
{
    public function init()
    {
        $this->_name = new Zend_Db_Expr('"TABLE_NAME"@"LINK"');
    }
}

I didn't try but it should work.

Posted by Phil Brown (philbrown) on 2009-09-29T20:15:03.000+0000

No, that still won't work as the link identifier must only appear in the FROM clause.

As Mickael pointed out above, the link would have to be separate from the table name (ie, new property).

Posted by Mickael Perraud (mikaelkael) on 2009-09-30T04:10:07.000+0000

After a test this function for me:

<pre class="highlight">
class My_Table extends Zend_Db_Table_Abstract
{
    public function init()
    {
        $this->_name = new Zend_Db_Expr('"TABLE_NAME"@"LINK"');
        $this->_cols = array('ID', 'MY_VAL', 'MY_COMMENTS');
        $this->_primary = array('ID');
    }
}
$myTable = new My_Table();
var_dump($myTable->fetchAll()->toArray());

The generated SQL is:

<pre class="highlight">
SELECT t.* FROM "TABLE_NAME"@"LINK" t

I must define the column and the primary key by hand.

Posted by Mickael Perraud (mikaelkael) on 2009-11-20T05:52:53.000+0000

Except using the proposed solution, you can't do it.

Even if we add the support of the link in Zend_Db_Table_Abstract, you don't have access to the remote server to retrieve the description of the table.

Have you found an issue?

See the Overview section for more details.

Copyright

© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.

Contacts