#303 [Clyde] Initialisation fails after install

[zoggo]

This error below is thrown on first attempt to use
tikipro. The database server is Firebird 1.5 and the
problem is that the SQL generated includes this clause:

ORDER BY tl."layout"=? DESC

Firebird does not accept parameters in ORDER BY
statements. In fact, parameters are meaningless in an
order by statement, aren't they?

[ - Clyde v3-BETA 1 - ] [ Sun Apr 17 11:49:26 2005 ]
#### USER AGENT: Mozilla/5.0 (X11; U; Linux i686;
en-US; rv:1.7.6) Gecko/20050311 Firefox/1.0.1
#### ACCT: ID: 2 - Login: admin - e-mail: admin@localhost
#### URL: http://localhost/tikitest/wiki/
#### REFERRER:
http://localhost/tikitest/install/install.php
#### HOST: localhost
#### IP: 192.168.0.2
#### SQL: SELECT tl.*, tlm.*, tmm."module_rsrc" FROM
"tiki_layouts" tl, "tiki_layouts_modules" tlm,
"tiki_module_map" tmm WHERE
tl."module_id"=tlm."module_id" AND tl."user_id"=? AND
tmm."module_id"=tlm."module_id" AND (tl."layout"=? OR
tl."layout"=? ) ORDER BY tl."layout"=? DESC, "ord" ASC
#### ADODB: Errno: -104 Message: Dynamic SQL Error
SQL error code = -104 Token unknown - line 2, char 156 =

[Christian Fowler]

Logged In: YES
user_id=947814

hrm, this is odd. I do not believe this code has not changed
in over a year, and Firebird has been working happily since
then. send a note to lsces at users.sourceforge.net - lester
might have a quick answer for you.

Open your kernel/config_inc.php and uncomment $gDebug and
paste the results here of the actual query being executed.

BTW, Conditions in the ORDER BY can be quite useful

[zoggo]

Logged In: YES
user_id=1253220

Firebird can never have worked with a parameterised order by
in the query statement. The feature is not supported. I
suspect the logic that generates this query is new which is
why the error hasn't shown up before. (And this is a new
install - it is quite possible that the query is not
generated for an upgraded system.)

Anyway, I fixed my immediate problem with the patch below.
It fixes my problem, so feel free to mark this bug-report
closed if you wish.

Left file: K:\code\php\tikipro\kernel\TikiSystem.php
Right file: K:\srv\www\frs_web\tikitest\kernel\TikiSystem.php
1186c1186,1187
< $whereClause .= " (tl.`layout`=? OR tl.`layout`=? )
ORDER BY tl.`layout`=? DESC, ";
---
> // $whereClause .= " (tl.`layout`=? OR tl.`layout`=? )
ORDER BY tl.`layout`=? DESC, ";
> $whereClause .= " (tl.`layout`=? OR tl.`layout`=? )
ORDER BY tl.`layout` DESC, ";
1189c1190
< array_push($bindVars, $pLayout);
---
> // array_push($bindVars, $pLayout);

[Lester Caine]

Logged In: YES
user_id=175009

This is a known problem!
I have a modified copy of the 'offending' query which is how
I get round the problem. Beacuse of the way Firebird builds
query results this is something that is not going to be
fixed soon. Especially since it's not in the SQL standard :(
So I just remove that element from the order by
I've been ignoring the problem as there are other things to
complete.

[Christian Fowler]

Logged In: YES
user_id=947814

Ahh.... I didn't know lester had not checked that change in.
Removing the order by will cause some trouble with custom
home pages layouts and I believe layouts for packages. I
will see what I can do about doing changing things around a bit.

[Lester Caine]

Logged In: YES
user_id=175009

What I should do is make this a conditional change when the
firebird driver is being used, and make a note somewhere
that the logic is wrong.
( Zoggo did you have any trouble with my 'create database'
modifications? I'm not sure they are totally compatible as yet )

[Lester Caine]

Logged In: YES
user_id=175009

What I should do is make this a conditional change when the
firebird driver is being used, and make a note somewhere
that the logic is wrong.
( Zoggo did you have any trouble with my 'create database'
modifications? I'm not sure they are totally compatible as yet )

[Christian Fowler]

Logged In: YES
user_id=947814

While we are on this topic, and have some activity, there
are two other firebird issues I am aware of:

./quota/LibertyQuota.php:156: if(
!$this->mDb->mDb->Affected_Rows() ) {
and
./wiki/TikiPage.php:590: if(
$this->mDb->mDb->Affected_Rows() ) {

Last I read in the ADODB docs, Affected_Rows() was not
supported by the FireBird driver. is this still the case lester?

[Lester Caine]

Logged In: YES
user_id=175009

It is surfaced in PHP5 only - PHP4 never had a lot of the
nice interbase/firebird stuff, which is why Ard spent a lot
of time extending the driver in PHP5.
I'm hoping to get some time to run a current build of
tikipro soon, so I'll have a look at adding it to ADOdb -
and check for any other niggles that have crept in :)

[Lester Caine]

Logged In: YES
user_id=175009

The actual reported bug has been cleared, but at the
'expence' of slightly different operation between Firebird
and PostgreSQL. The ORDER BY tl."layout"=? is intended to
group selected layouts before default layouts. At present
Firebird does not carry out that ordering, grouping the two
types together.
I have not created a situation where this difference can be
displayed, so am not sure of the final impact.
It will only be addressed if it actually becomes a problem
in use.