Re: [fw-db] [Postgresql] AUTO_QUOTE_IDENTIFIERS issue

Guys, thank you for your suggestions but don't look for SQL errors where they don't exist. The fact is the query works for MySQL and for PostgreSQL only when I manually add the escaping character " ... So, the point is I just want to know why I don't have escaping characters in queries generated by ZF whereas I use AUTO_QUOTE_IDENTIFIERS set to true.

Maybe a bug within Zend_Db ?!?!

--
Thomas VEQUAUD   http://thomas.vequaud.free.fr/
Ingénieur Systèmes pour le compte d' ITS GROUP
Bénévole et secouriste à la Croix-Rouge Française

On Mon, Dec 22, 2008 at 11:57 PM, thunder <ravenvelvet@gmail.com> wrote:

Hi!

On Fri, Dec 19, 2008 at 10:08 AM, Thomas VEQUAUD <thomas.vequaud@gmail.com> wrote:

Hi there!

I was running a MySQL database but from now I need more stuff... So I
decided to install a PostgreSQL database on my development
environment. I added a [pgsql] section in my config.ini file...
Anyway, when I try to launch the index of my website, I get a SQL
syntax error with the following query :
SELECT *
FROM "user" AS "us"
INNER JOIN "is" ON us.user_id = is.user_id
INNER JOIN "role" AS "ro" ON is.role_id = ro.role_id
WHERE (ro.role_type <> 2 AND ro.role_type <> 1)
ORDER BY "user_date" DESC

This query is generated by my model class 'User' with the following method:
public function         getLastRegistered()
{
       $select = $this->select();
       $select->setIntegrityCheck(false)
                  ->from(array('us' => 'user'))
                  ->join('is', 'us.user_id = is.user_id')
                  ->join(array('ro' => 'role'), 'is.role_id = ro.role_id')
                  ->where('ro.role_type <> '.ROLE_TYPE_INACTIVE.' AND ro.role_type
<> '.ROLE_TYPE_BANNED)
                  ->order('user_date DESC')
                  ->limit(0, 10);
       return ($this->fetchAll($select)->toArray());
}

Do you actually have a table called 'is' ?   In that php code above, you've specified an alias for user (us) and for role (ro), but not for 'is'. I would be highly surprised if you do actually have a table called 'is' since 'is' is a reserved word in most RDBMS (a la    SELECT *  FROM foo WHERE bar IS NULL  etc)

Try specifying the full table name (or give it a different alias) and see what happens...