How to connect OpenEMR to SQL Server database

system · July 10, 2014, 9:44am

cvadnala wrote on Thursday, July 10, 2014:

I downloaded the OpenEMR application and installed in my machine. Currently configured to MySql database.

But, I want to work OpEMR application with SQL Server database.

How to connect OpenEMR to SQL Server database.

system · July 10, 2014, 12:54pm

yehster wrote on Thursday, July 10, 2014:

To switch OpenEMR to work with SQL Server would be very difficult.

Although OpenEMR uses ADODB as an abstraction layer to the database, which does have SQL Server drivers, there are a number of MySQL specific features in use, such as Autoincrement.

Here is the code where OpenEMR chooses which database driver to use.

github.com

openemr/openemr/blob/master/library/sql.inc#L33


require_once(dirname(__FILE__) . "/sqlconf.php");
require_once(dirname(__FILE__) . "/../vendor/adodb/adodb-php/adodb.inc.php");
require_once(dirname(__FILE__) . "/../vendor/adodb/adodb-php/drivers/adodb-mysqli.inc.php");
require_once(dirname(__FILE__) . "/log.inc");
require_once(dirname(__FILE__) . "/ADODB_mysqli_log.php");


if (!defined('ADODB_FETCH_ASSOC')) {
define('ADODB_FETCH_ASSOC', 2);
}


$database = NewADOConnection("mysqli_log"); // Use the subclassed driver which logs execute events
// Below optionFlags flag is telling the mysql connection to ensure local_infile setting,
// which is needed to import data in the Administration->Other->External Data Loads feature.
// (Note the MYSQLI_READ_DEFAULT_GROUP is just to keep the current setting hard-coded in adodb)
$database->optionFlags = array(array(MYSQLI_READ_DEFAULT_GROUP,0), array(MYSQLI_OPT_LOCAL_INFILE,1));
// Set mysql to use ssl, if applicable.
// Can support basic encryption by including just the mysql-ca pem (this is mandatory for ssl)
// Can also support client based certificate if also include mysql-cert and mysql-key (this is optional for ssl)
if (file_exists($GLOBALS['OE_SITE_DIR'] . "/documents/certificates/mysql-ca")) {
if (defined('MYSQLI_CLIENT_SSL')) {
    $database->clientFlags = MYSQLI_CLIENT_SSL;

Changing this to be SQL server instead of MySQL is where you would start, but there are a lot more things you would have to do in order to get it to work, even partially.

Modifying the .sql that creates the empty schema to be compatible with SQL Server would be a next big step.

github.com

openemr/openemr/blob/master/sql/database.sql

--
-- Database: `openemr`
--

-----------------------------------------------------------

--
-- Table structure for table `addresses`
--

DROP TABLE IF EXISTS `addresses`;
CREATE TABLE `addresses` (
  `id` int(11) NOT NULL default '0',
  `line1` varchar(255) default NULL,
  `line2` varchar(255) default NULL,
  `city` varchar(255) default NULL,
  `state` varchar(35) default NULL,
  `zip` varchar(10) default NULL,
  `plus_four` varchar(4) default NULL,
  `country` varchar(255) default NULL,

This file has been truncated. show original

You might be able to overcome that problem by exporting a working database from MySQL and importing it into SQL Server and using that as a baseline.

I don’t know your motivation for wanting to use SQL Server instead of MySQL, so you are going to have to weigh the difficulty in making the changes with the advantages of SQL Server. The issues I mention here are but a few of many other problems you are likely to encounter.

system · March 22, 2015, 1:17pm

omarchirine wrote on Sunday, March 22, 2015:

hi,t’am trying to connect to database openemr but an error occuors:Erreur : SQLSTATE[HY000] [1049] Base ‘openemr’ inconnue

system · March 22, 2015, 5:22pm

fsgl wrote on Sunday, March 22, 2015:

What is your operating system?

Which download of OpenEMR are you installing?

Are you using another database, not MySQL?

It is difficult to know why you are not connecting to the database without more information.

dhammapal_gawai · May 27, 2021, 12:59pm

My client wants to use MSSQL with OpenEMR. We dont have any other option for backend. Please help. Request to provide reference and feedback.

luisuriarte · May 27, 2021, 4:47pm

OpenEMR works with MySQL.
You can download Xampp for windows and install OpenEMR there

https://www.apachefriends.org/index.html