JQGrid and Microsoft SQL Server

Question

Hello all,

I am trying to get this awesome JQuery plugin working with SQL Server. I have setup PDO as I use it elsewhere (with MS Access) - I think I have it setup with sql server (2008) now. Here is how I connect:

define('DB_DSN',"odbc:Driver={SQL Server};Server=MyInstance;Database=table1;");
define('DB_USER', ''); 
define('DB_PASSWORD', '');

// include the jqGrid Class
require_once "php/jqGrid.php";
// include the PDO driver class
require_once "php/jqGridPdo.php";
// Connection to the server
$conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD);
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");
// Create the jqGrid instance
$grid = new jqGridRender($conn);
$tsql = 'SELECT * FROM Trt';
// Write the SQL Query
$grid->SelectCommand = $tsql;
// set the ouput format to json
$grid->dataType = 'json';
// Let the grid create the model
$grid->setColModel();
// Set the url from where we obtain the data
$grid->setUrl('report-creator.php');

// Set grid caption using the option caption
$grid->setGridOptions(array(
    "caption"=>"Report",
    "rowNum"=>10,
    "sortname"=>"OrderID",
    "hoverrows"=>true,
    "rowList"=>array(10,20,50),
    ));

$grid->toolbarfilter = true;
$grid->setFilterOptions(array("stringResult"=>true));
$grid->renderGrid('#grid','#pager',true, null, null, true,true);
$conn = null;

I get this error:

    Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[IM001]: Driver
 does not support this function: driver doesn't support meta data' in  
C:\wamp\www\webs\tgd\edh\php\jqGridPdo.php(1) : eval()'d code:1 Stack trace: #0 
C:\wamp\www\webs\tgd\edh\php\jqGridPdo.php(1) : eval()'d code(1): PDOStatement-
    >getColumnMeta(0) #1 C:\wamp\www\webs\tgd\edh\php\jqGrid.php(1) : eval()'d code(7):  
jqGridDB->getColumnMeta(0, Object(PDOStatement)) #2 C:\wamp\www\webs\tgd\Front- End\report-
creator.php(31): jqGridRender->setColModel() #3 C:\wamp\www\webs\tgd\Front- End\view-
report.php(123): require_once('C:\wamp\www\web...') #4 {main} thrown in  
C:\wamp\www\webs\tgd\edh\php\jqGridPdo.php(1) : eval()'d code on line 1

I appreciate any help - I just can't understand the problem. I have narrowed it down to this function: $grid->SelectCommand - if I remove this no error occurs but I can not do without this as this is where my t-sql to query the database goes.

I am able to query SQL server mysql using PDQ->Query('SELECT * FROM table1').

Thanks all

Please note: I have edited table name, file paths for privacy. :)

Answer 1

Looks like jqGrid uses getColumnMeta function to enumerate columns, but this function may be unsupported by several drivers - in this case, your MSSQL driver.

See examples in the link above to test if getColumnMeta works.

Answer 2

I work for Trirand (the company that developers jqGrid - ASP.NET team) and unfortunately cannot help with PHP much, but I can suggest posting the very same question in our own forums as well:

http://www.trirand.net/forum/

there you will find a PHP jqGrid forum and you will get a timely response from our PHP team. Meanwhile I will write them an email and ask them to help here as well, but just in case posting there will guarantee you an answer.

Cheers, Rumen Stankov Trirand Inc

Answer 3

I couldn't solve this so I switched over to another class which was written by the JQGrid guys that makes use of the PHP driver from Microsoft. This solved my problem, hope it helps someone.