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:
[hidden email]]
> Gesendet: Donnerstag, 28. Dezember 2006 20:05
> An:
[hidden email]
> 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
>>
>>
>>
>
>
>
Locked
