#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    4
    Rep Power
    0

    Unhappy Firebird:show error after modify datatable script


    I'm interesting use DataTable from Alan Jardine sites. and of course it use PHP-MySQL in their serverside script.

    i have finished my task by using combination between jQuery,DataTable,MySQL, and PHP. For now i try to use Firebird to change MySQL.

    but after make some change inside dataTable serverside script i meet the problem like:

    Code:
    Dynamic SQL Error SQL error code = -104 Unexpected end of command - line 1, column 19
    i'm newbie in firebird, so i have some difficulty to identify where the error come from.
    This my complete script:

    Code:
    <?php
    define("DBNAME","xx.xxx.xx.xxx:D:\DATABASE\OCS DATA.FDB"); // data base name
    define("DBUSER","USER"); // user name
    define("DBPASS","USER"); // password
    
    // DB connection
    $dbh = ibase_connect(DBNAME,DBUSER,DBPASS) or die(_ERROR15.": ".ibase_errmsg());
    
    /* Paging */
            $sLimit = "";
            if ( isset( $_POST['iDisplayStart'] ) )
            {
                    $sLimit = " FIRST ".$_POST['iDisplayStart']." SKIP ".$_POST['iDisplayLength'];
            }
     /* Ordering */
            $sOrder ="";
            $sOrder = " ORDER BY LINE_NAME ";
    
            /* Filtering*/
            $sWhere = "";
            if (postVar('sSearch') !="" )
            {
                     $sWhere = " WHERE (LINE_NAME LIKE '%".$_POST['sSearch']."%' OR
                                    MODEL_ONLY LIKE '%".$_POST['sSearch']."%' OR ".
                                   " VER_ONLY LIKE '%".$_POST['sSearch']."%' OR ".
                                   " LOT_SIZE LIKE '%".$_POST['sSearch']."%' OR ".
                                   " START_SERIAL LIKE '%".$_POST['sSearch']."%' OR ".
                                   " SERIAL_NO_LOW LIKE '%".$_POST['sSearch']."%' OR ".
                                   " SERIAL_NO_UP LIKE '%".$_POST['sSearch']."%' OR ".
                                   " PROD_NO LIKE '%".$_POST['sSearch']."%' OR ".
                                   " PROD_DATE LIKE '%".$_POST['sSearch']."%') ";
            }
    $sQuery = "SELECT LINE_NAME, MODEL_ONLY, VER_ONLY, PROD_NO, 
                                      LOT_SIZE, START_SERIAL, SERIAL_NO_LOW, SERIAL_NO_UP, PROD_DATE 
                               FROM DOC_TO".$sWhere.$sOrder.$sLimit.";";
     $rResult = ibase_query( $sQuery) or _doError(_ERROR30 . ' (<small>' . htmlspecialchars($sql) . '</small>): ' . ibase_errmsg() ); 
    
            $sQuery = "SELECT FOUND_ROWS()";
            $rResultFilterTotal = ibase_query( $sQuery) or _doError(_ERROR30 . ' (<small>' . htmlspecialchars($sql) . '</small>): ' . ibase_errmsg() );  
    
            $aResultFilterTotal = ibase_fetch_assoc($rResultFilterTotal);
            $iFilteredTotal = $aResultFilterTotal[0];
    
            $sQuery = "SELECT COUNT(LINE_NAME) FROM DOC_TO;";
    
            $rResultTotal = ibase_query( $sQuery) or _doError(_ERROR30 . ' (<small>' . htmlspecialchars($sql) . '</small>): ' . ibase_errmsg() );  
            $aResultTotal = ibase_fetch_assoc($rResultTotal);
            $iTotal = $aResultTotal[0];
    
    $sOutput = '{';
            $sOutput .= '"sEcho": '.intval($_POST['sEcho']).',';     
            $sOutput .= '"iTotalRecords": '.$iTotal.', ';
            $sOutput .= '"iTotalDisplayRecords": '.$iTotal.', ';
            $sOutput .= '"aaData": [ ';
            while ( $aRow = ibase_fetch_array( $rResult ) )
            {
                    $sOutput .= "[";
                    $sOutput .= '"'.addslashes($aRow['LINE_NAME']).'",';
                    $sOutput .= '"'.addslashes($aRow['MODEL_ONLY']).'",';
                    $sOutput .= '"'.addslashes($aRow['VER_ONLY']).'",';
                    $sOutput .= '"'.addslashes($aRow['PROD_NO']).'",';
                    $sOutput .= '"'.addslashes($aRow['LOT_SIZE']).'",';
                    $sOutput .= '"'.addslashes($aRow['START_SERIAL']).'",';
                    $sOutput .= '"'.addslashes($aRow['SERIAL_NO_LOW']).'",';
                    $sOutput .= '"'.addslashes($aRow['SERIAL_NO_UP']).'",';
                    $sOutput .= '"'.addslashes($aRow['PROD_DATE']).'"';
                    $sOutput .= "],";
            }
            $sOutput = substr_replace( $sOutput, "", -1 );
            $sOutput .= '] }';
    
            echo $sOutput;
    function fnColumnToField( $i )
            {
                    if ( $i == 0 )
                            return "LINE_NAME";
                    else if ( $i == 1 )
                            return "MODEL_ONLY";
                    else if ( $i == 2 )
                            return "VER_ONLY";
                    else if ( $i == 3 )
                            return "PROD_NO";
                    else if ( $i == 4 )
                            return "LOT_SIZE";
                    else if ( $i == 5 )
                            return "START_SERIAL";
                    else if ( $i == 6 )
                            return "SERIAL_NO_LOW";
                    else if ( $i == 7 )
                            return "SERIAL_NO_UP";
                    else if ( $i == 8 )
                            return "PROD_DATE";
            }
    <?
    i change every MySQL's query become firebird's query.The error above come from:

    Code:
    $rResultFilterTotal = ..........//from this line
    can you help me find any mistake from my script?
    could you show me the similar function of FOUND_ROWS() in firebird? because i think the error come from that?
    Any advance will be appreciate.thanks.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    4
    Rep Power
    0
    i have found the answer from AndreKR's guidance:

    Code:
    $sLimit = "";
            if ( isset( $_POST['iDisplayStart'] ) )
            {
                    settype($iDisplayStart,'integer');
                    $iDisplayStart = $_POST['iDisplayStart'];
                    $iDisplayLength = $_POST['iDisplayLength'];
                    $sLimit = sprintf(" FIRST ".$iDisplayStart." SKIP ".$iDisplayLength);
            }
    and change all $_POST:

    Code:
    '%".$_POST['sSearch_0']."%'
    into
    ''%".$_POST['sSearch_0']."%''   //this to prevent injection For
    databases Like Oracle, DB2, MS SQL, Firebird

    and change :

    Code:
    $sQuery = "SELECT COUNT(*) FROM(......)
    into
    $sQuery = "SELECT COUNT(*) AS DCOUNT FROM DOC_TO".$sWhere.$sLimit.";"; //delete $sOrder
    
    $aResultFilterTotal = ibase_fetch_assoc($rResultFilterTotal);
    $iFilteredTotal = $aResultFilterTotal['DCOUNT'];  //change from ......[0]
    and add at the last php page:

    Code:
    ibase_free_result($aResultTotal);
    ?>

IMN logo majestic logo threadwatch logo seochat tools logo