MS SQL Server

Spreadsheet
#1

I dedicated some time yesterday to make dhtmlxSpreadsheet function on MS SQL Server (SQLSrv). I am documenting my process here to make it work, and adding some tips.

After downloading, unzipping and copying the files; make sure the root folder has full write rights. As you are using SQL Server, chances are you are using IIS, and this is more important for you.

After unzipping you will find a few files in the root directory and an install folder. That install folder contains an entire set of documents; that most will be copied to your running folder structure. It’s important to know this; at the end you will pretty much have 2 copies of every file. When editing make sure you are using the correct location.

The install sets up 5 new tables, the definition is for MySQL, replace spreadsheet.sql with:

[code]if not exists (select * from sysobjects where name=’#__data’ and xtype=‘U’)
create table #__data (
[sheetid] varchar(255) default null,
[columnid] int default null,
[rowid] int default null,
[data] varchar(255) default null,
[style] varchar(255) default null,
[parsed] varchar(255) default null,
[calc] varchar(255) default null,
primary key ([sheetid], [columnid], [rowid])
)

if not exists (select * from sysobjects where name=’#__header’ and xtype=‘U’)
create table #__header (
[sheetid] varchar(255) default null,
[columnid] int default null,
[label] varchar(255) default null,
[width] int default null,
primary key ([sheetid], [columnid])
)

if not exists (select * from sysobjects where name=’#__sheet’ and xtype=‘U’)
create table #__sheet (
[sheetid] varchar(255) NOT NULL,
[userid] int default null,
[name] varchar(255) default null,
[key] varchar(255) default null,
cfg varchar(512) default null,
primary key ([sheetid])
)
insert into #__sheet values (‘demo_sheet’, null, null, ‘any_key’, null);

if not exists (select * from sysobjects where name=’#__user’ and xtype=‘U’)
create table #__user (
[userid] int identity(1,1),
[apikey] varchar(255) default null,
[email] varchar(255) default null,
[secret] varchar(64) default null,
[pass] varchar(64) default null,
primary key ([userid])
)

if not exists (select * from sysobjects where name=’#__triggers’ and xtype=‘U’)
create table #__triggers (
[id] int identity(1,1),
[sheetid] varchar(255) default null,
[trigger] varchar(10) default null,
source varchar(10) default null,
primary key ([id])
)
[/code]
There are a few field names that are prohibited in SQL Srvr, the brackets are necessary.

Replace all opening and closing ` with [ and ] This is painful, but we don’t have much of an option. Alternatively the back quotes can be removed, but then you will have to fix all the cases where field names like that are not allowed.

File: dhtmlx_core.js
Line: 15446
From:

var sql = "INSERT INTO `dhtmlxmenu_demo` (`itemId`, `itemParentId`, `itemOrder`, `itemText`, `itemType`, `itemEnabled`, `itemChecked`, `itemGroup`, `itemImage`, `itemImageDis`) VALUES ";

to:

var sql = "INSERT INTO [dhtmlxmenu_demo] ([itemId], [itemParentId], [itemOrder], [itemText], [itemType], [itemEnabled], [itemChecked], [itemGroup], [itemImage], [itemImageDis]) VALUES ";

File: admin_connector.php
Line: 68
From:

$res = $this->wrapper->query("UPDATE sheet SET `{$field}`='{$value}' WHERE sheetid='{$sheet}'");

To:

$res = $this->wrapper->query("UPDATE [{$this->prefix}sheet] SET [{$field}]='{$value}' WHERE sheetid='{$sheet}'");

Note: I think sheet needs a prefix?

File: api.php
Line: 129
From:

$query = "SELECT `rowid`, `columnid` FROM {$this->prefix}data WHERE `sheetid`='".$this->e($this->sheetid)."'";

To:

$query = "SELECT [rowid], [columnid] FROM {$this->prefix}data WHERE [sheetid]='".$this->e($this->sheetid)."'";

Line: 255
From:

$query = "SELECT `data`, `parsed`, `calc`, `style` FROM {$this->prefix}data WHERE {$this->where}";

To:

$query = "SELECT [data], [parsed], [calc], [style] FROM {$this->prefix}data WHERE {$this->where}";

Line: 272
From:

$query = "SELECT `data` FROM {$this->prefix}data WHERE {$this->where}";

To:

$query = "SELECT [data] FROM {$this->prefix}data WHERE {$this->where}";

Line: 278
From:

$query = "INSERT INTO `{$this->prefix}data` (`sheetid`, `rowid`, `columnid`,`data`,`calc`,`parsed`,`style`) VALU…

To:

$query = "INSERT INTO [{$this->prefix}data] ([sheetid], [rowid], [columnid],[data],[calc],[parsed],[style]) VALU…

Line: 320
From:

$query = "SELECT `data` FROM {$this->prefix}data WHERE {$this->where}";

To:

$query = "SELECT [data] FROM {$this->prefix}data WHERE {$this->where}";

Line: 357
From:

$query = "DELETE FROM {$this->prefix}triggers WHERE `source`='".$this->e($coord)."' AND `sheetid`='".$this->e($this->sheetid)."'";

To:

$query = "DELETE FROM {$this->prefix}triggers WHERE [source]='".$this->e($coord)."' AND [sheetid]='".$this->e($this->sheetid)."'";

Line: 365
From:

$query = "INSERT INTO {$this->prefix}triggers (`sheetid`, `trigger`, `source`) VALUES {$triggers}";

To:

$query = "INSERT INTO {$this->prefix}triggers ([sheetid], [trigger], [source]) VALUES {$triggers}";

Line: 410
From:

$query = "SELECT * FROM {$this->prefix}triggers WHERE `trigger`='".$this->e($coord)."' AND `sheetid`='".$this->e($this->sheetid)."'";

To:

$query = "SELECT * FROM {$this->prefix}triggers WHERE [trigger]='".$this->e($coord)."' AND [sheetid]='".$this->e($this->sheetid)."'";

Line: 535
From:

$query = "SELECT `calc` FROM {$this->prefix}data WHERE {$this->where}";

To:

$query = "SELECT [calc] FROM {$this->prefix}data WHERE {$this->where}";

Line: 549
From:

$query = "SELECT `parsed` FROM {$this->prefix}data WHERE {$this->where}";

To:

$query = "SELECT [parsed] FROM {$this->prefix}data WHERE {$this->where}";

Line: 562
From:

$query = "SELECT `style` FROM {$this->prefix}data WHERE {$this->where}";

To:

$query = "SELECT [style] FROM {$this->prefix}data WHERE {$this->where}";

Line: 581
From:
$query = “UPDATE {$this->prefix}data SET style=’”.$this->e($style)."’ WHERE {$this->where}";
To:

$query = "UPDATE {$this->prefix}data SET [style]='".$this->e($style)."' WHERE {$this->where}";

File: db_common
Line: 966
From:

$result = mysql_query("SHOW COLUMNS FROM `".$table."`");

To:

$result = mysql_query("SHOW COLUMNS FROM [".$table."]");

Line: 987
From:

if ((strpos($data,"`")!==false || is_int($data)) || (strpos($data,".")!==false))

To:

if ((strpos($data,"'")!==false || is_int($data)) || (strpos($data,".")!==false))

Line: 989
From:

return '`'.$data.'`';

To:

return str_replace("'", "''", $data);

Note: This is more than simple quote replace.

File: db_mysqli.php
Line: 41
From:

$result = $this->connection->query("SHOW COLUMNS FROM `".$table."`");

To:

$result = $this->connection->query("SHOW COLUMNS FROM [".$table."]");

Note: Not sure if this needed, but doesn’t hurt…

File: grid_cell_connector.php
Line: 224
From:

$res = $this->wrapper->query("SELECT `key` FROM {$this->db_prefix}sheet WHERE sheetid='".$this->e($sheet)."'");

To:

$res = $this->wrapper->query("SELECT [key] FROM {$this->db_prefix}sheet WHERE sheetid='".$this->e($sheet)."'");

Line: 261
From:

$res = $this->wrapper->query("SELECT cfg FROM {$this->db_prefix}sheet WHERE sheetid='".$this->e($sheet)."'");

To:

$res = $this->wrapper->query("SELECT [cfg] FROM {$this->db_prefix}sheet WHERE sheetid='".$this->e($sheet)."'");

Add this code:

File: index.php
Line: 137
Add:

<option value="SQLSrv"<?php if ($db_type === 'SQLSrv') echo " selected"; ?>>SQLSrv</option>

File: installer.php
Line: 98
Add:

private function sqlsrvDBConnect($db_host, $db_port, $db_user, $db_pass, $db_name) { require_once('./installer/src/codebase/php/db_sqlsrv.php'); if (!function_exists("sqlsrv_connect")) return "PHP extension for SQLSrv is not available"; $res = sqlsrv_connect($db_host, array("Database" => "$db_name", "UID" => "$db_user", "PWD" => "$db_pass")); if (!$res) return null; return $res; }

Line: 128
From:

$dump = str_replace("#__", $db_prefix, $dump);

To:

$dump = str_replace("#__", $db_name . "." . $db_prefix, $dump);

Line: 200
Add:

case 'sqlsrv': $config .= "require_once('db_sqlsrv.php');\n"; break;

File: data.php
Line: 22
Add:

case 'sqlsrv': $res = sqlsrv_connect($db_host, array("Database" => "$db_name", "UID" => "$db_user", "PWD" => "$db_pass")); break;

Copy SQL Server database connector db_sqlsrv.php (I am sure you allready have a copy of it somewhere in your install) into
./installer/src/codebase/php/

Now it is time to open the root folder with your browser and start the real install.

Hopefully this writeup helps someone. You might have other issues; please note them here.

One hint; the application includes an entire dhtmlx.js core install; I tried to mix this with the current library; not good…

Spreadsheet is a really cool application though!!! Maybe this will help some new development :wink: