MS SQL Server

I just noticed the file system/database/mmsql.php

Has anyone used Opencart with MS SQL Server?

I was hoping when I saw the file that it would be possible to use SQL Server which I would do in preference to MySQL because it is streets ahead and would make for much better back-office and reporting possibilities.

I know that I can use MyODBC but there are disadvantages to not being native.

When asked if I could put my first ecommerce site together, I selected a linux/apache server. In hindsight, I would have used a Microsoft/IIS server which now has good support for php.

mystifier wrote:I was hoping when I saw the file that it would be possible to use SQL Server which I would do in preference to MySQL because it is streets ahead and would make for much better back-office and reporting possibilities.

I know that I can use MyODBC but there are disadvantages to not being native.

When asked if I could put my first ecommerce site together, I selected a linux/apache server. In hindsight, I would have used a Microsoft/IIS server which now has good support for php.

well I guess that's all relative. They've tried to improve the performance of php on iis, but it's still a ways behind apache. Not to mention all the other quirks that microsoft/iss/php has. I really wish microsoft would just give up on the idea of webhosting so I would never have clients trying to use it and having to deal with all the crazy stupid idiosyncrasies.

As far as the db connecting to ms sql I really doubt you would have much luck with that. Daniel did write it so that you could substitute sql engines in the db class, but given that all the sql is written directly in the model files I would be willing to bet that there is quite a bit of it that won't work with mssql, and you would have to go through and convert all of it wherever the problems poped up in any model file. There is no active record type system to convert it between db's.

In my case, the most important component is the 'Back-Office' that allows data to flow straight from the website into, for example, accounts, and straight from, for example suppliers.

The shopping-cart is only piece of the jigsaw. The tools for SQL Server are far stronger than those for MySQL.

Dear friends,

Few months ago, my company converted a Opencart Store v1.4.9.1 from MySQL to SQL Server.

It was a hard work, because that was our first time, and the Opencart project was written specially to work with MySQL. If you try to use "system\database\mmsql.php", you'll get many errors, because the differences between MySQL and SQL Server queries aren't be treated in source code.

The good news is that in current versions, the delimiters were removed from tables and columns (``) in MySQL queries, and with the db naming convention based on unix standard, there will be no problems with them.

In our case, we had to eliminate the delimiters from all queries, because they are different for each database. In SQL Server they are brackets [].

The OpenCart Store is very simple in its design and coding, no major problems in doing this conversion.

We created the file "system\database\sqlserv.php" from "system\database\mmsql.php" based on php_sqlsrv.dll DLL to work with SQL Server 2005/2008 and data in UTF-8. The older version "php_mssql.dll" is very limited in several aspects.

We did use the free tool "SSMA" from Microsoft, to convert and migrate the database from MySQL to SQL Server.

We created some very complex regular expressions to "change" the format and the order of specific mysql queries to run in SQL Server.

I would like to contribute with three recommendations for Opencart Development Team, in order to facilitate migration of the store to other databases:

1) Writing SQL in ANSI SQL standard. Give special attention to queries envolving GROUP BY, SUBQUERIES and DISTINCT.

2) Creating two constants for delimiters, like DB_PREFIX. Example:

DB_DELIMITER_LEFT = '[';
DB_DELIMITER_RIGHT = ']';

3) Pagination is a problem among several databases. Creating an internal PHP function to add the piece of SQL for pagination. Unfortunately, Microsoft has develop a strange naming convention for this purpose (Eg. ROW_NUMBER OVER ( ORDER BY ). Because of this, we need to pick up the piece of ORDER BY, and put it in the columns list in the beginning of the query, and, in the end, envolving the entire query in a subquery. So complicated.

Fortunately, we document everything, and we still have the script with the steps of migration, if it is of interest to you.

My email is guilherme@suporteon.com.

We have a good experience in converting applications among different databases or languages, because of our experience acquired in developing "GENERATOR" a framework developed 5 years ago which takes one or more databases (Access, SQL Server, MySQL, Oracle or PostgreSQL) and creates an entire web application based on metadata and data, with many features like Table View, Pagination, Search, Export, Import, Advanced Form Features and Validations, Insert, Edit, Delete Data, Report Generator, and much more. This tool allow the user, in one click, to compile the generated application from a database to another without loss of functionality, or, from a programming language to another (PHP, ASP, JSP). Unfortunately, the project died last year.

I hope it was relevant.

Best Regards,

Guilherme Cruz

SQL Server Express is a free edition of SQL Server ideal for developing and powering desktop, web and small server applications. Download SQL Server 2008 R2 Express or try SQL Server Code Name “Denali” CTP3, which includes SQL Server Express LocalDB, a new lightweight deployment option for Express.
some of the features
Quickly build and deploy your database solution.
Build websites and web applications.
Create small scale line-of-business applications.
Redistribute with ISV applications.
Learn database development and management.
Download the free PowerPivot for Excel and create compelling analysis and reports.

SQL Server also includes an assortment of add-on services. While these are not essential for the operation of the database system, they provide value added services on top of the core database management system. These services either run as a part of some SQL Server component or out-of-process as Windows Service and presents their own API to control and interact with them.

SQLServer also requires a license. If you use Express Edition, you can't use it commercially without a license. MySQL, you don't need a license.

I've been a DBA for the last 14 years and I can't see a reason why you would move to MSSQL. You can report without going anywhere near Microsoft. You can use any number of open source projects to report, including live feeds of the data to Open Office.

For reference, I am an Oracle DBA first and foremost, but even I couldn't find an angle to recommend Oracle for use with OpenCart. MySQL is capable of way over and above the demands that OpenCart will put on it.

If you are talking about Microsoft Reportings Services, why, oh, why would you limit yourself to this and costing yourselves thousands in licensing. Pay that thousands of pounds instead to the OpenCart devolpment project and see how it expands.

Is it possible to use oracle database for opencart Version 1.5.1.3 instead of using mysql because i hav to use oracle db in one my my projects instead of mysql..

Anyhelp is of great value..

We converted the OpenCart 1.4.9.1 from MySQL to SQL Server using complex regular expressions, which covered approximately 90% of the queries in this release.

This version of OpenCart did not help us at all, some of the original queries were very badly written, everything was difficult.

In the current version of OpenCart it is much easier, because there is no field delimiters "``" in the queries, which is good.

To do a good job you need to know several sql syntax differences among databases.

We believe that there is a security risk to use regular expressions for this purpose in a production environment, because any minimum error can open your store to SQL injection.

It is easy to make mistakes, given the complexity of the structure of an SQL Query. Everything must be done carefully.

Because of many requests we've received since posting this forum, we provide the original files of this migration.

Download this ZIP: http://www.suporteon.com/downloads/migr ... sqlsrv.zip.

There are 3 files inside it:

1) guide-to-migrate-opencart-v-1.4.9.1-from-mysql-to-sqlsrv.txt (Intructions for migration).

2) opencart-database-sqlserver.bak (Opencart 1.4.9.1 Database converted to SQL Server "Backup").

3) sqlsrv.php (Opencart version of PHP SQLSRV Driver).

This was not a perfect job, it was just an experiment.

Good Luck!

3 months ago we converted the OpenCart v1.5.4.1 to work with SQL Server 2005/2008/2012 in versions for both drivers "mssql" and "sqlsrv".

Everything seems to be working well, both in the Back-End as the Front-End.

But we know that to make available the OpenCart SQL Server, it would be necessary to perform many tests in the entire application.

We have reached to build an extension for OpenCart v1.5.4.1, by changing only two core files, "/index.php" and "/admin/index.php", and some files in the folder "/install", which will be completely removed after installation. The other files are kept in a separate folder "override" because this extension is based on another one we created (OpenCart Override -> http://www.opencart.com/index.php?route ... on_id=8664).

Unfortunately our company was forced to drive many of its resources to other more immediate projects, and truly, we are missing resources here.

We intend to convert the OpenCart v1.5.5.1 and all future versions of OpenCart.

We provide an online version of OpenCart SQL Server based on mssql driver, for anyone interested in testing it.

We are keeping in touch with the developers of Propel to evaluate the possibility of converting all SQL queries from OpenCart to Propel instead of "translating them" directly in the source code as we have done.

Below are two links for pictures of the installation of OpenCart SQL Server:

http://www.suporteon.com/sites/opencart ... -step2.png

http://www.suporteon.com/sites/opencart ... -step3.png

We have created an OpenCart v1.5.4.1 store that is connecting to a remote SQL Server 2012 database through the driver "mssql", right here: http://www.suporteon.com/sites/opencart ... 1541_mssql

created the file "system\database\sqlserv.php" from "system\database\mmsql.php" based on php_sqlsrv.dll DLL to work with SQL Server 2005/2008

<?php
final class SQLSRV {
private $link;

public function __construct($hostname, $username, $password, $database) {
/*
* Argument 2 passed to sqlsrv_connect() must be an array, string given
*/
$connectionInfo = array (
"UID" => $username,
"PWD" => $password,
"Database" => $database
);

if (!$this->link = sqlsrv_connect($hostname, $connectionInfo)) {
exit('Error: Could not make a database connection using ' . $username . '@' . $hostname);
}

/*
if (!mssql_select_db($database, $this->link)) {
exit('Error: Could not connect to database ' . $database);
}
*/

sqlsrv_query("SET NAMES 'utf8'", $this->link);
sqlsrv_query("SET CHARACTER SET utf8", $this->link);
}

public function query($sql) {
$resource = sqlsrv_query($sql, $this->link);

if ($resource) {
if (is_resource($resource)) {
$i = 0;

$data = array();

while ($result = sqlsrv_fetch_array($resource, SQLSRV_FETCH_ASSOC)) {
$data[$i] = $result;

$i++;
}

sqlsrv_free_stmt($resource);

$query = new stdClass();
$query->row = isset($data[0]) ? $data[0] : array();
$query->rows = $data;
$query->num_rows = $i;

unset($data);

return $query;
} else {
return true;
}
} else {
//trigger_error('Error: ' . mssql_get_last_message($this->link) . '<br />' . $sql);
trigger_error('Error: <br />' . $sql);
exit();
}
}

public function escape($value) {
$unpacked = unpack('H*hex', $value);

return '0x' . $unpacked['hex'];
}

public function countAffected() {
return sqlsrv_rows_affected($this->link);
}

public function getLastId() {
$last_id = false;

$resource = sqlsrv_query("SELECT @@identity AS id", $this->link);

if ($row = sqlsrv_fetch($resource)) {
$last_id = trim($row[0]);
}

sqlsrv_free_stmt($resource);

return $last_id;
}

public function __destruct() {
sqlsrv_close($this->link);
}
}
?>