PHP Datagrid and SQL Express

Talk about working with different database types

Moderators: ne_moj, zewa, vadimako, ne_moj, zewa, vadimako

sberivu
Newbie
Newbie
Posts: 1
Joined: Feb 20th, '09, 13:02

PHP Datagrid and SQL Express

Postby sberivu » Feb 20th, '09, 13:13

Hello,

I tried to run PHP DataGrid Pro with SQL Express 2005 but I always get this error below saying that the sql-string is not correct.

search sql (total: 0) select id, reportby from tblbdr WHERE 1=1 ORDER BY id ASC AND RowNumber > 0 AND RowNumber < 10)
Errors:
1) 0
[nativecode=102 - Incorrect syntax near '0'.]


If I parse this SQL-string into SQL Express it always complains that it does not recognize 'RowNumber'.
Below you can see a part of the php-page.

## *** creating variables that we need for database connection
$DB_USER='sa'; /* usually like this: prefix_name */
$DB_PASS=''; /* must be already encrypted (recommended) */
$DB_HOST='SBEWS1264\SQLEXPRESS'; /* usually localhost */
$DB_NAME='dbBarcoDamageReporting'; /* usually like this: prefix_dbName */
//
ob_start();
$db_conn = DB::factory('mssql'); /* don't forget to change on appropriate db type */
$result_conn = $db_conn->connect(DB::parseDSN('mssql://'.$DB_USER.':'.$DB_PASS.'@'.$DB_HOST.'/'.$DB_NAME));


How can this be solved ?

Regards,

Rik Vulsteke

administrator
Site Admin
Site Admin
Posts: 5981
Joined: Jan 7th, '09, 23:18
Contact:

Re: PHP Datagrid and SQL Express

Postby administrator » Feb 21st, '09, 15:37

Check SetSqlByDbType() - it must return ROW_NUMBER() OVER (ORDE....

so you need to get in SQL something like this

SELECT Description, Date
FROM (SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row >= 11 AND Row <= 20

helio_cesar10
Newbie
Newbie
Posts: 4
Joined: Apr 6th, '09, 14:21
Contact:

Re: PHP Datagrid and SQL Express

Postby helio_cesar10 » Apr 17th, '09, 15:13

HELLO TURMA Is it a bug in my DataGrid DATAGRID.CLASS.PHP ON LINE IN 6197 I do not know What I do HELP ME PLEASE HELP ME

administrator
Site Admin
Site Admin
Posts: 5981
Joined: Jan 7th, '09, 23:18
Contact:

Re: PHP Datagrid and SQL Express

Postby administrator » Jun 8th, '09, 14:38

There is a bug in version 5.0.6 that will be fixed in next versions.
Currently you can fix it manually by replacing next 4 methods on datagrid.class.php with original:


Code: Select all

    //--------------------------------------------------------------------------
    // Set SQL limit by DB type
    //--------------------------------------------------------------------------
    protected function SetSqlLimitByDbType($limit_start="", $limit_size=""){
        // get full recordset if export_all defined as true
        if($this->GetVariable('export') == true && $this->export_all == true) return "";
           
        $this->SetSqlLimit();
        if($limit_start == "") $limit_start = $this->limitStart;
        if($limit_size == "") $limit_size = $this->limitSize;
        $limit_string = "";
        switch($this->dbHandler->phptype){
            case "oci8":    // Oracle               
                $limit_string = "AND (rownum > ".$limit_start." AND rownum <= ".intval($limit_start + $limit_size).") ";
                break;         
            case "mssql":   // MSSQL           
                $limit_string = "AND (RowNumber > ".$limit_start." AND RowNumber < ".intval($limit_start + $limit_size).") ";
                break;
            case "pgsql":   // PostgreSql               
                $limit_string = "OFFSET ".$limit_start." LIMIT ".$limit_size." ";
                break;                 
            case "ibase":   // iBase/Firebird
            case "firebird":
                $limit_string = "FIRST ".$limit_size." SKIP ".$limit_start." ";
                break;
            case "mysql":   // MySQL and others
            default:
                $limit_string = "LIMIT ".$limit_start.", ".$limit_size." ";
                break;           
        }
        return $limit_string;
    }   

    //--------------------------------------------------------------------------
    // Set real escape string by DB type
    //--------------------------------------------------------------------------
    protected function SetRealEscapeStringByDbType($field_value = ""){
        if(!$this->allowRealEscape) return $field_value;
        switch($this->dbHandler->phptype){
            case "mysql":   // mysql
                return mysql_real_escape_string($field_value);  break;   
            case "pgsql":   // PostgreSql               
                return pg_escape_string($field_value);  break;   
                break;
            default:
                return $field_value;  break;   
        }       
    }

    //--------------------------------------------------------------------------
    // Set SQL by DB type
    //--------------------------------------------------------------------------
    protected function SetSqlByDbType($sql="", $order_by="", $limit=""){
        $sql_string = "";
        preg_match_all("/\d+/",$limit,$matches);
       
        switch($this->dbHandler->phptype){
            case "oci8":    // oracle               
                if($limit != ""){
                    $limit_start = $matches[0][0];
                    $limit_size = $matches[0][1]-$limit_start;
                    $sql_string = $this->dbHandler->modifyLimitQuery($sql." ".$order_by, $limit_start, $limit_size);
                    if($this->debug) echo "<table><tr><td><b>Oracle sql: </b>".$sql_string."</td></tr></table><br>";
                }else{
                    $sql_string = $sql." ".$order_by;
                }
                break;         
            case "mssql":   // mssql
                $select_top = isset($matches[0][1]) ? $matches[0][1] : "";
                $over_order_by = (isset($this->primaryKey) && $this->primaryKey != "") ? $this->primaryKey : trim(str_ireplace(array("ORDER BY", "ASC", "DESC"), "", $order_by));
                if($select_top != "" && $over_order_by != ""){
                    $from_index = strpos($this->StrToLower($sql), "from ");
                    $prefix = substr($sql, 0, $from_index);
                    $suffix = substr($sql, $from_index);
                    $sql_string = $prefix.", SELECT TOP ".$select_top." ROW_NUMBER() OVER (ORDER BY ".$over_order_by.") AS RowNumber ".$suffix;
                    $sql_string .= " ".$limit." ".$order_by;                   
                }
                break;         
            case "ibase":    // interbase/firebird       
            case "firebird":
                $sql_string = str_replace("SELECT ", "SELECT ".$limit." ", $sql)." ".$order_by;
                break;
            case "mysql":   // mysql and others
            default:
                $sql_string = $sql." ".$order_by." ".$limit;
                break;           
        }
        return $sql_string;       
    }

    //--------------------------------------------------------------------------
    // Get LCASE function name by DB type
    //--------------------------------------------------------------------------
    protected function GetLcaseFooByDbType(){
        $lcase_name = "";
        switch($this->dbHandler->phptype){
            case "oci8":     // oracle               
                $lcase_name = "lower";  break;         
            case "mssql":    // mssql
                $lcase_name = "LCASE";  break;
            case "pgsql":    // pgsql
                $lcase_name = "lower";  break;               
            case "ibase":    // interbase/firebird
            case "firebird": //
                $lcase_name = "lower";  break;
            case "mysql":    // mysql and others
            default:
                $lcase_name = "LCASE";  break;           
        }
        return $lcase_name;               
    }   

exodus
Newbie
Newbie
Posts: 7
Joined: Sep 5th, '09, 16:27

Re: PHP Datagrid and SQL Express

Postby exodus » Sep 5th, '09, 16:37

Greetings!

My firm has recently purchased the PHP Grid script. It works great with MySQL and the grids generated were brilliant.

However I'm experiencing trouble when I try to do the same thing with MS SQL Server DB!

Here is my environment:

Apache 2.2
PHP 5.2.10
MS SQL Server 2000

After turning on the debug and messaging mode, I can get to see the following in my php page:

The query that is being generated:
SELECT TOP 10 Queue.QueueID, Queue.StartTime, Queue.EndTime, Queue.Extra1, SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY QueueID) AS RowNumber FROM Queue WHERE 1=1 AND (RowNumber > 0 AND RowNumber < 10) ORDER BY QueueID DESC


The error message returned by the server:
1) SELECT TOP 10 Queue.QueueID, Queue.StartTime, Queue.EndTime, Queue.Extra1, SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY QueueID) AS RowNumber FROM Queue WHERE 1=1 AND (RowNumber > 0 AND RowNumber < 10) ORDER BY QueueID DESC
[nativecode=195 - 'ROW_NUMBER' is not a recognized function name.]

2) SELECT TOP 10 Queue.QueueID, Queue.StartTime, Queue.EndTime, Queue.Extra1, SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY QueueID) AS RowNumber FROM Queue WHERE 1=1 AND (RowNumber > 0 AND RowNumber < 10) ORDER BY QueueID DESC
[nativecode=195 - 'ROW_NUMBER' is not a recognized function name.]

3) SELECT TOP 10 Queue.QueueID, Queue.StartTime, Queue.EndTime, Queue.Extra1, SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY QueueID) AS RowNumber FROM Queue WHERE 1=1 AND (RowNumber > 0 AND RowNumber < 10) ORDER BY QueueID DESC
[nativecode=195 - 'ROW_NUMBER' is not a recognized function name.]


After a little googling I found out that the ROW_NUMBER() function is available only as a part of SQL Server 2005 and later. I've tried setting the paging as false and I still get the same error message.

How do I work around this? Am I doing something wrong or would you have to make a change in the script to handle such paging features in older versions of MS SQL Server?

Here is my code for reference:

Code: Select all

<?php
    ################################################################################   
    ## +---------------------------------------------------------------------------+
    ## | 1. Creating & Calling:                                                    |
    ## +---------------------------------------------------------------------------+
    ##  *** only relative (virtual) path (to the current document)
      define ("DATAGRID_DIR", "");                     /* Ex.: "datagrid/" */
      define ("PEAR_DIR", "pear/");                    /* Ex.: "datagrid/pear/" */
   
      require_once(DATAGRID_DIR.'datagrid.class.php');
      require_once(PEAR_DIR.'PEAR.php');
      require_once(PEAR_DIR.'DB.php');
   
    ##  *** creating variables that we need for database connection
      $DB_USER='sa';           
      $DB_PASS='';
      $DB_HOST='xxx.xxx.xxx.xxx';       
      $DB_NAME='DBNAME';   
   
    ob_start();
      $db_conn = DB::factory('mssql');  /* don't forget to change on appropriate db type */
      $result_conn = $db_conn->connect(DB::parseDSN('mssql://'.$DB_USER.':'.$DB_PASS.'@'.$DB_HOST.'/'.$DB_NAME));
      if(DB::isError($result_conn)){ die($result_conn->getDebugInfo()); }
   
      $sql=" SELECT TOP 10" 
       ." Queue.QueueID, "
       ." Queue.StartTime, "
       ." Queue.EndTime, "
       ." Queue.Extra1"   
       ."FROM Queue";
       
    ##  *** set needed options
      $debug_mode = true;
      $messaging = true;
      $unique_prefix = "q_"; 
      $dgrid = new DataGrid($debug_mode, $messaging, $unique_prefix, DATAGRID_DIR);

      $default_order_field = "QueueID";
      $default_order_type = "DESC";
      $dgrid->DataSource($db_conn, $sql, $default_order_field, $default_order_type);

      $ajax_option = true;
    $dgrid->AllowAjax($ajax_option);
 
     $paging_option = false;
     $rows_numeration = false;
     $numeration_sign = "N #";
     $dropdown_paging = false;
     $dgrid->AllowPaging($paging_option, $rows_numeration, $numeration_sign, $dropdown_paging);

       $dgrid->SetAutoColumnsInViewMode(true); 

        $dgrid->Bind();       
        ob_end_flush();
?>


Any help in this regards will be greatly appreciated.

exodus
Newbie
Newbie
Posts: 7
Joined: Sep 5th, '09, 16:27

Re: PHP Datagrid and SQL Express

Postby exodus » Sep 5th, '09, 19:53

In continuation of my efforts to solve the problem in the post and just to be sure that the problem was with SQL Server 2000, I installed SQL Server 2008 Express and tried it. I keep getting errors.

This time, this is the query that was run by the grid:
SELECT Queue.QueueID, Queue.StartTime, Queue.EndTime, Queue.Extra1 , SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY QueueID) AS RowNumber FROM MDCallOutbound.dbo.Queue WHERE 1=1 AND (RowNumber > 0 AND RowNumber < 10) ORDER BY QueueID ASC


And I got this error:
1) SELECT Queue.QueueID, Queue.StartTime, Queue.EndTime, Queue.Extra1 , SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY QueueID) AS RowNumber FROM MDCallOutbound.dbo.Queue WHERE 1=1 AND (RowNumber > 0 AND RowNumber < 10) ORDER BY QueueID ASC
[nativecode=156 - Incorrect syntax near the keyword 'SELECT'.]

2) SELECT Queue.QueueID, Queue.StartTime, Queue.EndTime, Queue.Extra1 , SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY QueueID) AS RowNumber FROM MDCallOutbound.dbo.Queue WHERE 1=1 AND (RowNumber > 0 AND RowNumber < 10) ORDER BY QueueID ASC
[nativecode=156 - Incorrect syntax near the keyword 'SELECT'.]

3) SELECT Queue.QueueID, Queue.StartTime, Queue.EndTime, Queue.Extra1 , SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY QueueID) AS RowNumber FROM MDCallOutbound.dbo.Queue WHERE 1=1 AND (RowNumber > 0 AND RowNumber < 10) ORDER BY QueueID ASC
[nativecode=156 - Incorrect syntax near the keyword 'SELECT'.]


It looks like the nativecode has changed for the different SQL Server Versions.

Here is my code again for reference:

Code: Select all

<?php
      define ("DATAGRID_DIR", "");                     /* Ex.: "datagrid/" */
      define ("PEAR_DIR", "pear/");                    /* Ex.: "datagrid/pear/" */
   
      require_once(DATAGRID_DIR.'datagrid.class.php');
      require_once(PEAR_DIR.'PEAR.php');
      require_once(PEAR_DIR.'DB.php');
   
      $DB_USER='sa';           
      $DB_PASS='';           
      $DB_HOST='xxx.xxx.xxx.xxx;       
      $DB_NAME='DBNAME';   
   
    ob_start();
      $db_conn = DB::factory('mssql');  /* don't forget to change on appropriate db type */
      $result_conn = $db_conn->connect(DB::parseDSN('mssql://'.$DB_USER.':'.$DB_PASS.'@'.$DB_HOST.'/'.$DB_NAME));
      if(DB::isError($result_conn)){ die($result_conn->getDebugInfo()); }
   
    ##  *** put a primary key on the first place
      $sql="SELECT" 
       ." Queue.QueueID,"
       ." Queue.StartTime,"
       ." Queue.EndTime,"
       ." Queue.Extra1"   
       ." FROM MDCallOutbound.dbo.Queue";
       
    ##  *** set needed options
      $debug_mode = true;
      $messaging = true;
      $unique_prefix = "q_"; 
      $dgrid = new DataGrid($debug_mode, $messaging, $unique_prefix, DATAGRID_DIR);

      ##  *** set data source with needed options
      $default_order_field = "QueueID";
      $default_order_type = "ASC";
      $dgrid->DataSource($db_conn, $sql, $default_order_field, $default_order_type);

      $ajax_option = true;
    $dgrid->AllowAjax($ajax_option);
 
        $dgrid->SetAutoColumnsInViewMode(true); 
   
 
        $dgrid->Bind();       
        ob_end_flush();
 
?>


A live example of connecting to a SQL Server would be a great help. Again am I doing something wrong?

administrator
Site Admin
Site Admin
Posts: 5981
Joined: Jan 7th, '09, 23:18
Contact:

Re: PHP Datagrid and SQL Express

Postby administrator » Sep 6th, '09, 14:31

Please try to run in your query analyzer this SQL SELECT with the next format:

SELECT * FROM
( SELECT
ROW_NUMBER() OVER (ORDER BY sort_key ASC) AS row_number,
COLUMNS
FROM tablename
) AS foo
WHERE row_number <= 10

We need to understand which format it understand, before we're going to change something in DataGrid

exodus
Newbie
Newbie
Posts: 7
Joined: Sep 5th, '09, 16:27

Re: PHP Datagrid and SQL Express

Postby exodus » Sep 8th, '09, 04:27

Greetings administrator!

Thank you for your response.

I tried running your query in both SQL Server 2000 and SQL Server 2008 Express Edition and here are the results:

SQL Server 2000:
Msg 195, Level 15, State 10, Line 3
'ROW_NUMBER' is not a recognized function name.


SQL Server 2008: the query ran just fine.
row_number Extra1
1 PAGER
2 SMS
3 PAGER


So I take it that my coding was correct and I did not make any mistakes...???

I appreciate the time and effort spent on answering my queries. It feels nice to take steps to resolve a problem. I'm hoping we can resolve the problem soon.

administrator
Site Admin
Site Admin
Posts: 5981
Joined: Jan 7th, '09, 23:18
Contact:

20 Re: PHP Datagrid and SQL Express 2e

Postby administrator » Sep 8th, '09, 16:56

Ok, thanks for your help. It was an error in SQL. We'll review DataGrid to fix it.
Generally, the syntax of MSSQL query must be:

SELECT * FROM
(
SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY QueueID DESC) AS RowNumber,
Queue.QueueID,
Queue.StartTime,
Queue.EndTime,
Queue.Extra1
FROM Queue
) as foo
WHERE 1=1 AND (RowNumber > 0 AND RowNumber < 10)
ORDER BY QueueID DESC

exodus
Newbie
Newbie
Posts: 7
Joined: Sep 5th, '09, 16:27

Re: PHP Datagrid and SQL Express

Postby exodus » Sep 8th, '09, 18:38

Sure thing admin. Please do let me know when you have fixed the grid.


Return to “Databases: different types implementation”