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

Database
closed
Reini Urban
5
2014-08-26
2006-11-17
C.J.
No

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])

Discussion

  • Reini Urban
    Reini Urban
    2006-11-19

    Logged In: YES
    user_id=13755
    Originator: NO

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

     
  • C.J.
    C.J.
    2006-11-20

    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
    Reini Urban
    2006-11-20

    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.

     
  • Reini Urban
    Reini Urban
    2006-12-02

    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