Zend Framework

Support DB Link identifier as part of table name

Details

Description

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

SELECT TABLE_NAME.* FROM SCHEMA.TABLE_NAME@LINK

Using Zend_Db_Table_Abstract, if I use something like

protected $_schema = 'SCHEMA';
protected $_name = 'TABLE_NAME@LINK';

the generated identifiers in SELECT statements appears as

SELECT "TABLE_NAME@LINK".* FROM "SCHEMA"."TABLE_NAME@LINK" ...

when it should be

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.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Ralph Schindler added a comment -

does this not work?

protected $_name = 'TABLE_NAME"@"LINK'
Show
Ralph Schindler added a comment - does this not work? 
protected $_name = 'TABLE_NAME"@"LINK'
Hide
Permalink
Phil Brown added a comment -

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

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.

Show
Phil Brown added a comment - Sorry Ralph, it doesn't. That property generates queries like 
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.
Hide
Permalink
Mickael Perraud added a comment - - edited

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

CREATE TABLE "ZFTEST"."FOO@BAR" ("ID" NUMBER(10) NOT NULL, PRIMARY KEY("ID"));

If you access to this table from a link BAZ:

SELECT "ID" FROM "ZFTEST"."FOO@BAR"@BAZ

The link must be differentiate from the table name.

Show
Mickael Perraud added a comment - - edited The problem is that you can create a table with @ inside the name (at least with Oracle), this code functions: 
CREATE TABLE "ZFTEST"."FOO@BAR" ("ID" NUMBER(10) NOT NULL, PRIMARY KEY("ID"));
If you access to this table from a link BAZ: 
SELECT "ID" FROM "ZFTEST"."FOO@BAR"@BAZ
The link must be differentiate from the table name.
Hide
Permalink
Mickael Perraud added a comment -

Perhaps you can use:

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

or

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.

Show
Mickael Perraud added a comment - Perhaps you can use: 
class My_Table extends Zend_Db_Table_Abstract
{
    public function init()
    {
        $this->_name = new Zend_Db_Expr('TABLE_NAME@LINK');
    }
}
or 
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.
Hide
Permalink
Phil Brown added a comment -

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

Show
Phil Brown added a comment - 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).
Hide
Permalink
Mickael Perraud added a comment -

After a test this function for me:

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:

SELECT t.* FROM "TABLE_NAME"@"LINK" t

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

Show
Mickael Perraud added a comment - After a test this function for me: 
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: 
SELECT t.* FROM "TABLE_NAME"@"LINK" t
I must define the column and the primary key by hand.
Hide
Permalink
Mickael Perraud added a comment -

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.

Show
Mickael Perraud added a comment - 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.

People

Vote (0)
Watch (1)

Dates

  • Created:
    Updated:
    Resolved:
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Zend Framework. Try JIRA - bug tracking software for your team.