The only way to solve this issue seems to be to use a varchar2 column 
for attribute_value in horde_datatree_attributes.



I'll commit the binding changes nonetheless.

No, I tried that too as a hotfix. Integrating this cleanly into the 
query build process would be a nightmare by the way.

This bit got sort of buried in the ticket:
selsky implied that this could be a solution. That useful at all?

I have the code sitting on my box to make all DataTree attribute 
queries using bindings. But this still doesn't fix the issues on 
Oracle, I get the same error message.



I'm having a hard time to find out which column is causing this error. 
I suspected the bitand() operation on the attribute_value column, but 
even wrapping the column in a to_number() function didn't fix it.



Any ideas, anyone?

by sending patches. outside of this ticket, since this is *way* off 
the original problem by now.

I didn't think you were opposed.  I just wanted to document the 
reasons for using bind variables.



I understand it will be a bunch of tricky work and I'm willing ot 
help.  Just let me know the best way to do that.

I don't think I ever said, and certainly didn't mean to say, that I 
was opposed to using bind variables. It's certainly a good idea. It's 
just going to take a lot of work, and some planning to avoid API 
breakage in places like Horde_SQL.

Some notes from my DBA on bind variables:



i do notice one specific problem with the queries.  the application is 
submitting literal sql with hardcoded variables to the server, instead 
of using bind variables.  we have seen this cause problems in the 
past. memory allocated to parsing sql statements is being saturated 
with non-reusable code.



to show you what i mean, the following sql has hardcoded values for 
it's variables (26, 126, 1028, 11206).  this sql must be parsed by the 
server to generate an execution path, then this parsed query is 
remembered in cache for the next time it's executed.   however, the 
next time it's executed the variables change, so the server cannot 
re-use the execution path that it figured out, and must re-parse a new 
query and cache that parsed query also.



[...]



this statement is prepared once, then called repeatedly, replacing all 
the :bind_vars with different values each time.  the query text 
doesn't have any hardcoded variables so the server can reuse this 
parsed code.



[...]



we have seen significant performance improvements from taking these 
steps, and i'd encourage you to ask the vendor to make these changes.

Sorry, I meant Horde_SQL::buildClause(). The counterpart could be 
Horde_SQL::bindClause().

Where is buildTagClause() defined?  I don't see it in Horde CVS...

Some databases don't suppor them, but there's emulation for that in 
DB. Mostly our code was written before that emulation existed, and no 
one ever did a whole rewrite for it.



An alternate solution is perhaps a bindTagClause() method (like 
buildTagClause()), that takes an additional list of parameters by 
reference, and adds $lhs and/or $rhs to it as many times as necessary.

That's what I'm saying.  :-)



Are there a reason we don't use bind parameters in general?  Do some 
databases not support it?

If you're saying that that way you can avoid having to use bind 
parameters, then yes, that helps, and I think that way makes sense.

http://www.techonthenet.com/oracle/functions/to_clob.htm



to_clob() can convert a VARCHAR2 to a CLOB so that the datatypes match.



Does that help?

Okay, yeah, that's icky. What does to_clob() do/solve?

I'm getting a little lost in the code trying to do this without 
messing up other stuff.  For example, when buildClause() does 
something like this:



return sprintf('bitand(%s,%d) = %d', $lhs, (int)$rhs, (int)$rhs);



What is the right way to parameterize that?



return sprintf('bitand(%s,?) = ?', $lhs);



How will the caller know that it needs to pass that parameter twice?



Would it be easier to just add to_clob() around the values (for oracle 
only) to convert the strings to CLOBs?

Add an option to return a placeholder for $value instead of putting 
$value directly into the clause.

buildAttributeQuery() calls _buildAttributeQuery() which calls 
Horde_SQL::buildClause().  That last function is called from a lot of 
places so I'm a little wary of changing the stuff that it returns.



Suggestions?

probably something like list($query, $params) = buildAttributeQuery(...)?

We need to change the following on line 973:



         $rows = $this->_db->getAssoc($query);



to



         $rows = $this->_db->getAssoc($query, false, $params);



And pass the arguments to the query in the $params array instead of 
hard-coding them in the query itself.



buildAttributeQuery() needs to be changed to pass back a query and a 
param list.  What's the best way to do that?

I unfortunately don't have an Oracle instance to test with at the 
moment (despite spending a good part of the last few days attempting 
just that). Can you find a variation of the query that works? The 
error doesn't sound/look to me like it needs to be done with prepare, 
but I could be wrong.

I'm trying to use Kronolith 2.0.2 and when I go to add a new entry, I 
get the following error in horde.log:



Feb 25 03:09:39 HORDE [error] [kronolith] DB Error: unknown error: 
SELECT c.datatree_id, c.datatree_name FROM horde_datatree c LEFT JOIN 
horde_datatree_attributes a1 ON a1.datatree_id = c.datatree_id WHERE 
c.group_uid = 'horde.shares.kronolith' AND ((a1.attribute_name = 
'owner' AND a1.attribute_value = 'user@localhost') OR 
(a1.attribute_name = 'perm_users' AND a1.attribute_key = 
'user@localhost' AND bitand(a1.attribute_value,2) = 2) OR 
(a1.attribute_name = 'perm_creator' AND bitand(a1.attribute_value,2) = 
2) OR (a1.attribute_name = 'perm_default' AND 
bitand(a1.attribute_value,2) = 2))   GROUP BY c.datatree_id, 
c.datatree_name, c.datatree_order ORDER BY c.datatree_order, 
c.datatree_name, c.datatree_id [nativecode=ORA-00932: inconsistent 
datatypes: expected - got CLOB] [on line 975 of 
"/etc/httpd/htdocs/horde/lib/Horde/DataTree/sql.php"]



This sounds like a variation of http://bugs.horde.org/ticket/?id=443