#561 Can't use Lock Page with Oracle because of SQL differences

[C.J.]

The use of IF and LIMIT in SQL queries won't work with Oracle.

In config.ini I had set (amongst other things; see bug 1569424):
DATABASE_TYPE = SQL
ADMIN_USER = cj
ADMIN_PASSWD = cj
ENCRYPTED_PASSWD = false

I logged in as cj and tried to lock the RawHtmlPlugin page i.e. I called http://localhost/index.php/RawHtmlPlugin?action=lock

I got a page containing:

  Fatal Error:

  lib/WikiDB/backend/PearDB.php:1028: Error: WikiDB_backend_PearDB_oci8: fatal database error


  * DB Error: unknown error
  * (SELECT IF(linkee.pagename,1,0) as result FROM phpwiki_link, phpwiki_page linker, phpwiki_page linkee, phpwiki_nonempty WHERE linkfrom=linker.id AND linkto=linkee.id AND linker.pagename='RawHtmlPlugin' AND linkee.pagename='ModeratedPage' LIMIT 1 [nativecode=ORA-00933: SQL command not properly ended])
  *

  Fatal PhpWiki Error

  lib/WikiDB/backend/PearDB.php:1028: Error: WikiDB_backend_PearDB_oci8: fatal database error


  * DB Error: unknown error
  * (SELECT IF(linkee.pagename,1,0) as result FROM phpwiki_link, phpwiki_page linker, phpwiki_page linkee, phpwiki_nonempty WHERE linkfrom=linker.id AND linkto=linkee.id AND linker.pagename='RawHtmlPlugin' AND linkee.pagename='ModeratedPage' LIMIT 1 [nativecode=ORA-00933: SQL command not properly ended])

[Reini Urban]

Logged In: YES
user_id=13755
Originator: NO

Oops, exists_link() uses still a hardcoded "LIMIT 1". Thanks.

[C.J.]

Logged In: YES
user_id=1646527
Originator: YES

An untested hack fix is:

--- /home/cjbj/src/phpwiki-1.3.12p3/lib/WikiDB/backend/PearDB.php 2006-06-18 04:19:27.000000000 -0700
+++ lib/WikiDB/backend/PearDB.php 2006-11-20 11:27:08.000000000 -0800
@@ -576,13 +576,19 @@
list($have, $want) = array('linker', 'linkee');
$qpagename = $dbh->escapeSimple($pagename);
$qlink = $dbh->escapeSimple($link);

• $row = $dbh->GetRow("SELECT IF($want.pagename,1,0) as result"
  +
• $row = $dbh->GetRow("SELECT $want.pagename as result"
  . " FROM $link_tbl, $page_tbl linker, $page_tbl linkee, $nonempty_tbl"
  . " WHERE linkfrom=linker.id AND linkto=linkee.id"
  . " AND $have.pagename='$qpagename'"
• . " AND $want.pagename='$qlink'"
• . " LIMIT 1");
• return $row['result'];
• . " AND $want.pagename='$qlink'");
  +
• if (is_array($row)) {
• return 1;
• }
• else {
• return 0;

• }
  }

  function get_all_pages($include_empty=false, $sortby=false, $limit=false, $exclude='') {

[Reini Urban]

Logged In: YES
user_id=13755
Originator: NO

Fixed in CVS.
I did by using CASE WHEN, and not checking for a row.
Don't know yet which is more portable.

[C.J.]

Logged In: YES
user_id=1646527
Originator: YES

The CASE WHEN syntax as coded is not valid for Oracle. It gives ORA-00905: missing keyword.

Some references for CASE:
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/expressions004.htm#sthref2637
http://www.databasejournal.com/features/oracle/article.php/3344871

Also CASE won't help when no rows match the WHERE condition: you just get no data returned by the query.

[Reini Urban]

Logged In: YES
user_id=13755
Originator: NO

You are right.
It should be called as
"SELECT CASE WHEN $want.pagename='$qlink' THEN 1 ELSE 0 END as result" with pear
and SELECT CASE WHEN $want.pagename=$qlink THEN 1 ELSE 0 END" with ADODB

[Reini Urban]

Logged In: YES
user_id=13755
Originator: NO

Fixed in current CVS.

http://sourceforge.net/cvs/?group_id=6121
or http://phpwiki.sf.net/nightly/phpwiki.nightly.tar.gz