Ok, thanks for the clarification. When I saw the ":name" syntax I
assumed you were using bound parameters. That's the meaning of the
":name" syntax. But in your case, you have columns that literally
contain colon characters.
It is not uncommon for database interfaces to use the ":name" syntax to
indicate named parameters. It's not a good idea to name your columns
starting with colons, even though it is possible to do so by using
delimited identifiers. I know you said you can't change it at this
time, but you will find this is a habit that causes trouble, not only
with PHP interfaces.
Zend_Db uses the PDO library internally. PDO interprets the ":name"
syntax to mean named bound parameters. As far as I know, one cannot
turn off this behavior. I looked in the PDO online documentation, and
in ext/pdo/pdo_sql_parser.re in the PHP 5 source.
So I don't know of any way you can use SQL identifiers that contain
colon characters when using PDO, or libraries that utilize PDO such as
Zend_Db.
If you are using MySQL, you can try using the experimental MySQLi
database adapter that is in the Zend Framework incubator. This adapter
uses the mysqli functions, not the PDO functions. This adapter
currently does not seem to support bound parameters, neither the ":name"
syntax nor the "?" syntax. So it won't be looking for the ":name"
syntax in the SQL string you pass to it.
Regards,
Bill Karwin
Aycko Maerzke wrote:
Hi Gavin.
This is exactly what i mean. I can't change the fieldnames, because other
application (not PHP) use this DB too. So i have to find a solution without
renaming the fieldnames.
Escaping with \ doesn't work and results in an exception too.
-> Invalid parameter number: no parameters were bound
I hope ZF will include a functionality to solve problems like this.
Temporarly i have to use my own db class to solve this problem or does there
any other solution exists?
Best regards ... Aycko
-----Ursprüngliche Nachricht-----
Von: Gavin Vess [mailto:[EMAIL PROTECTED]
Gesendet: Donnerstag, 28. Dezember 2006 20:05
An: [email protected]
Betreff: Re: [fw-general] Problem with Zend_DB an PDO
Bill,
I think Aycko was speaking about a different problem than you described.
Aycko> The fieldname is ':field:name' without table prefix.
In fact what Aycko described is possible, but not with Zend_Db, for the
reasons I gave earlier. I also believe the framework should not impose
conventions on DB schemas used by ZF developers.
For example, the SQL below does work with MySQL
CREATE TABLE `test` (
`id` tinyint(4) NOT NULL auto_increment,
`:stuff:more` smallint(6) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
INSERT INTO `test` (`id`, `:stuff:more`) VALUES (1, 0);
SELECT `:stuff:more` FROM `test`;
Gavin> This general problem is recognized, and a solution has been
proposed:
Gavin> http://framework.zend.com/wiki/x/RB8
Gavin> See the section titled "Mapping Identifiers between DB and PHP".
Cheers,
Gavin
Bill Karwin wrote:
Aycko Maerzke wrote:
I tried an query like this:
$db->query("SELECT `:tablename:filedname` FROM `table`");
You shouldn't use parameters for table and column names. Parameters
are used in SQL only to supply values, not identifiers or any other
part of syntax.
Also, the identifier delimiter syntax you used above won't work even
if you execute it in the MySQL command-line client. You need to do
the delimiters on _each_ of the table name and the column name.
For example, the following doesn't work because it looks for a column
called foo.bar in the table foo.
SELECT `foo.bar` FROM `foo`
You need to delimit each identifier separately to make it clear that
they are two separate identifiers:
SELECT `foo`.`bar` FROM `foo`
If you need to make the table referenced in the select-list depend on
the table queried in the FROM clause, you should use a correlation name:
SELECT f.`bar` FROM `foo` AS f
Regards,
Bill Karwin
