Firebird:show error after modify datatable script

Dev Shed Forums Sponsor:

November 19th, 2010, 09:42 PM

klox

Registered User

Join Date: Nov 2010

Posts: 4

Time spent in forums: 3 h 10 m 25 sec
Reputation Power: 0

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.

November 23rd, 2010, 10:51 PM

klox

Registered User

Join Date: Nov 2010

Posts: 4

Time spent in forums: 3 h 10 m 25 sec
Reputation 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);
?>

Firebird:show error after modify datatable script

Developer Shed Advertisers and Affiliates