dhtmlxConnector not working for MSSQL?

Hello,

I can’t seem to get the MSSQL connector working for a simple grid. It fails with a 500 status code returned. Would someone please tell me why?

Here’s the grid webpage code:

[code]

Here’s the ‘testconnector.php’ file called by the load method of the grid webpage:

[code]<?php
require_once(“/dhtmlxSuite/dhtmlxConnector/codebase/grid_connector.php”); //<- works
require_once(“/dhtmlxSuite/dhtmlxConnector/codebase/db_mssql.php”); //<- works

$res=mssql_connect("MySQLServer\MySQLServerInstance","myDomain\myLogIn","myPassword",false); //<-- fails here with 500 status code returned.
   
mssql_select_db("myDatabase");
$grid = new GridConnector($res,"MsSQL");
$grid->render_table("myTable","EmpID","EmpID,Email");

?>[/code]

Many thanks.

If it fails during db connection, than problem is not related to the connectors itself

• check that php.ini enables mssql extension
• check that sql server really can be accessed with such credentials as in your command

Thank you, Stanislav, I’ll do that. Your help is most appreciated.

Stanislav,

I suggested your recommended course of action to our network administrator. He replied:

I tried his suggestion, using this Microsoft documentation http://msdn.microsoft.com/en-us/library/cc296161(v=sql.90).aspx as a reference:

[code]$serverName = “(myDBServer\myDBServerInstance)”;

$connectionInfo = array( “UID” => “myDomain\myID”, “PWD” => “myPWD”, “Database”=>“myDatabase”);

$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn )
{
echo “Connection established.\n”;
}
else
{
echo “Connection could not be established.\n”;
}[/code]

The result I got was “Connection could not be established.”

A few questions for you, please:

1. Can sqlsrv_connect be used in the Dhtmlx environment successfully as a substitute for mssql_connect and mssql_select_db?

2. Is my network administrator’s assessment correct about recompiling PHP, or is there an easier way to configure PHP.ini to load mssql?

As always, thank you.

(1) Yep, you can use the attached php file

require_once("/dhtmlxSuite/dhtmlxConnector/codebase/db_sqlsrv.php"); ... $grid = new GridConnector($res,"SQLSrv");

(2) It may be so. The mssql driver was replaced with sqlsrv in latest php builds, so it may require rebuilding php to get mssql enabled
db_sqlsrv.zip (1.06 KB)

Thank you, Stanislav. I will download the file and try it out very soon.

Hello, Stanislav,

I think it is almost working.

Here is my PHP connector code:

[code]<?php

//require_once("/dhtmlxSuite/dhtmlxConnector/codebase/db_mssql.php");  
require_once("/dhtmlxSuite/dhtmlxConnector/codebase/db_sqlsrv.php"); //<- replaces db_mssql
require_once("/dhtmlxSuite/dhtmlxConnector/codebase/grid_connector.php"); 

$serverName = "CH-SQL-6\Pentamation_rpt";
$uid=file_get_contents("C:\inetpub\wwwroot_DEV\uid.txt");
$pwd=file_get_contents("C:\inetpub\wwwroot_DEV\pwd.txt");

$connectionInfo = array( "UID"=>$uid, "PWD"=>$pwd, "Database"=>"COF_finplus_Dev", "LoginTimeout"=>15); // <- SS authentication
$res = sqlsrv_connect( $serverName, $connectionInfo); // <- SS authentication

if( $res )
{
    echo "Connection established.\n";
}
else
{
    echo "Connection could not be established.\n";
    die( print_r( sqlsrv_errors(), true));
}

$grid = new GridConnector($res,"SQLSrv");
$grid->render_table("proj_title","code","lvl,title"); // <- displays XML via dhtmlxError.catchError

/* Close the connection. */
sqlsrv_close( $res);
echo “Connection closed.\n”;

?>[/code]

[code]And here is my HTML code:

I am getting back data from the database, but it doesn’t appear inside the grid. Instead, it is displayed through a popup generated by the dhtmlxError.catchError function. It looks like the XML response packet:

<?xml version='1.0' encoding='utf-8' ?> <rows> <row id='1008A695'> <cell><![CDATA[8]]></cell> <cell><![CDATA[Miscellaneous projects]]></cell> </row> ...

Please explain to me, why is the grid not populating with the data, please? Why is the data coming through the error routine?

As always, thank you.

Stanislav,

The code was also producing an error message, ‘invalid XML’. Upon inspecting the data in the SQL table, I discovered there were some character values in the column I was using as the identity field which caused the XML to ‘break’ and become poorly-formed. (DHMTLX carries the identitiy field value as an XML attribute, rather than placing the value in a CDATA section.)

I then used a different table column as the identity field, and the grid loaded - but I only saw 8 rows, instead of the 100 in the table which I’d expected. It turns out the new identity field only has 8 unique values, so I assume there is something similar to a SELECT DISTINCT going on.

If I have a table which has a non-unique identity field, and I want all the rows to load into the grid, what should I code for that condition, please?

Stanislav,

Never mind, please. I tried using “” for the identity column, and it seemed to work quite well.

Thank you!

hi stanislav,

thanks a lot for the db_sqlsrv driver, it’s the one what i need right now