1

I am new to Zend Framework 2 and I am trying to hook my table up to TableGateway and do a basic select that should look like:

SELECT * FROM <tableName> WHERE QMQT# = 1

It looks like this in my table model:

$rowset = $this->tableGateway->select(array('QMQT#' => $id));

The problem seems to be the # sign in the query as I get an error back: 

SQLSTATE[42S22]: Column not found: 0 [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0206 - Column "QMQT""#" not in specified tables. (SQLPrepare[0] at ext\pdo_odbc\odbc_driver.c:206)

I can prepare and execute this query fine in plain PDO, but running it through Zend's TableGateway gets me this error. Are pound/number signs not allowed in table names in Zend? Is there a way to bypass this excessive escaping?

Thank you for any suggestions...

Edit:

I have also tried the quoteIdentifier() function to try to fix this:

$rowset = $this->tableGateway->select(array($this->tableGateway->getAdapter()->getPlatform()->quoteIdentifier('QMQT#') => $id));

But all it does is give me this error:

SQLSTATE[42000]: Syntax error or access violation: 0 [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token QMQT was not valid. Valid tokens: < > = <> <= !< !> != >= �< �> �= IN NOT. (SQLPrepare[0] at ext\pdo_odbc\odbc_driver.c:206)
Sarah Kemp
  • 2,670
  • 3
  • 21
  • 29
  • that error is not coming from the framework, it's the actual database driver, are you sure those are even valid characters for a column name? Is there any need to use none standard characters in column names? – Andrew Jul 16 '13 at 07:38
  • Yes, I get errors from the driver because the query Zend is sending is breaking apart my column name ('QMQT#' is becoming "QMQT""#"). I know it is not my driver or PDO because it works outside of Zend. The existing column names are out of my control - I do not use special characters when I have a choice. SQL supposedly allows $, #, and _ in identifiers, from what I can tell. – Sarah Kemp Jul 16 '13 at 15:01

1 Answers1

1

Have you tried using Expressions?

$rowset = $this->tableGateway->select(array(
    new Zend\Db\Sql\Expression\Expression('QMQT# as bob')
));
Andrew
  • 12,617
  • 1
  • 34
  • 48
  • No, Expressions are news to me (very welcome news!) - thank you very much! I ended up not using an alias but just doing: `$resultSet = $this->tableGateway->select(array(new Expression('QMQT# = ?', 67)));` – Sarah Kemp Jul 16 '13 at 20:35
  • 1
    Any time you need an expression which you don't want to be autmatically escaped etc you can use Expressions, they work in selecting columns, where stmts and joins :) – Andrew Jul 17 '13 at 07:56