MSSQL and userspice

[sqluser]

Hi,
transfer the websire to a new windows system, with php7 and Microsoft SQL server.
Is there already a configuration for UserSpice41 that can be used on this enviroment.
mysqli_connect must be changed into sqlsrv_connect.

Could you make it transparant for the database version.
We like your functionality for management and user, usergroups control.

[mudmin]

I'd have to install something similar to test it out. That's actually the reason UserSpice uses pdo instead of mysqli. In THEORY PDO is designed to be a layer between all the queries and whatever database format is being used. I feel like there's a little extra config with sql server that might not be in the init.php.

Since I don't have that environment handy, if you would test this, I think it would work.

Go into classes/DB.php and replace your DB class with this one:

http://pastebin.com/ZVR6T1hw

Try it again. If that doesn't work, try going into line 31 and removing everything except for the ;

I'd REALLY appreciate you reporting back and letting me know if any of this works for you.

[cmo]

I was also hoping to see results and how it might work with mssql. But, since he didn't, I did. I replaced the DB class with your pastebin code, setup a db in mssql and let 'er rip. Here is the current result.

Warning: PDO::__construct(): php_network_getaddresses: getaddrinfo failed: No such host is known. in C:\webs\ustest\install\step3.php on line 74
Call Stack
# Time Memory Function Location
1 0.0312 268160 {main}( ) ...\step3.php:0
2 0.0312 279024 __construct ( ) ...\step3.php:74
Database connection unsuccessful! Please try again.

[cmo]

Ok.. you've got a mysqli calls littered through at least step3. There is no way to create a MSSQL version of userspice using this. I did replace the connection code above and at least get a connection to the mssql db.
mysql connection:

<pre>

Code:

$dsn = "mysql:host=$dbh;dbname=$dbn;charset=utf8";
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$pdo = new PDO($dsn, $dbu, $dbp, $opt) or die('could not connect');

</pre>


mssql connection:

make $dbh in this form: 'hostname, 1433'

<pre>

Code:

$dsn = "sqlsrv:server=$dbh;Database = $dbn";
$dsn = "sqlsrv:server=$dbh;Database = $dbn";
$pdo = new PDO($dsn, $dbu, $dbp) or die('could not connect');
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$pdo->setAttribute( PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC );

</pre>

[mudmin]

It looks to me like your db hostname is wrong by that error. Are you sure your host, db, username, and password are all correct during install? UserSpice works on pretty much any sql based DB so that shouldn't be a problem. The only other questions are:

What version of PHP are you running?
Can you tell me a little bit about your server config?

We'll get you up and running. You shouldn't have to modify any of the code to get going.

Ok. I just saw your second most. Hmm. I was 99{3bc1fe685386cc4c3ab89a3f76566d8931e181ad17f08aed9ad73b30bf28114d} sure that userspice worked as is with mssql, but I don't have it around to try it.

[mudmin]

You may have to update a driver on your server....
http://php.net/manual/en/ref.pdo-dblib.php

[ChalitaDK]

Hi Mudmin,

I gave your copy paste a try. I can't seem to get past the connection test on the install. Keeps giving me a connection unsuccessful. 

Please help!

Thanks!