Connection to MSSQL / .NET

[esper]

Do you think it would be possible to establish connection between Processwire (MySQL) and MSSQL? I mean large-scale web portal running on ProcessWire, which would communicate with intranet systems in .NET, all would use LDAP authentication.. Maybe with help of ODBC? Thanks for any insight!!!

[cstevensjr]

Please start with this link and there are numerous others out there.  I linked to a few related threads in that posting.

Google under ProcessWire and MSSL, ProcessWire and IIS or ProcessWire and Windows.   Good luck and welcome to the ProcessWire Forum.

[esper]

Thank you very much for your reply! However those posts refer mostly to setting up Processwire on Windows server. I need to know, if it is possible to communicate between MySQL of Processwire and MSSQL of intranet .NET systems, using LDAP authentication with several thousands users, and getting data from one another. Does the posts mean, that if all systems run on the same server, it would make the connection possible, even if they are different technologies?

Maybe I ask the wrong way... I use ProcessWire over two years now and did some programming in PHP as well, however I have no experience with Windows servers, .NET and connecting to other systems in general. I don´t suppose Processwire could be safely set up with MSSQL instead of MySQL?

[cstevensjr]

 

8 hours ago, esper said:

Maybe I ask the wrong way... I use ProcessWire over two years now and did some programming in PHP as well, however I have no experience with Windows servers, .NET and connecting to other systems in general. I don´t suppose Processwire could be safely set up with MSSQL instead of MySQL?

I don't believe it has been explored for maybe the following reasons:

(1) I would look first at the stated requirements for ProcessWire

https://processwire.com/about/requirements/

It should be noted that it says that MySQL is required (or any MySQL compatible database i.e. MariaDB)

(2) Furthermore, I don't believe anyone has explicitly said that it technically can't be done, however you first need a good understanding of the differences between MySQL and SQL Server (MSSQL).  Then you would need to account for these differences in your code.

http://www.databasejournal.com/features/mysql/mapping-data-types-between-mysql-and-sql-server.html

http://www.databasejournal.com/features/mysql/comparing-sql-server-and-mysql-functions.html

(3) Additional vital information regarding Microsoft Infrastructure (Database technology), in case you want to pursue getting this to work

https://www.microsoft.com/en-us/download/details.aspx?id=20098

http://www.iis.net/learn/application-frameworks/install-and-configure-php-on-iis/install-the-sql-server-driver-for-php

(4) Additional information regarding Microsoft Infrastructure (Web Server technology) which will always be applicable when working with Windows solutions

http://www.iis.net/

There is much information (provided before) about using Microsoft technologies (specifically IIS working with MySQL) to enable working with ProcessWire.  They work without changing coding standards and I believe many people are using these solutions successfully.

BTW, .Net is a Microsoft technology and framework

 

[Doug G]

php supports mssql connections, easier from a windows php server than from linux but available for both.  You can certainly access mssql databases from php though.  Take a look here for starters:  http://php.net/manual/en/ref.pdo-dblib.php

Be aware there are sql differences between mssql server an mysql server, so you may run across data conversion issues between the two database servers.  For example:  http://stackoverflow.com/questions/30739204/how-to-convert-mssql-to-mysql-date-in-php

[esper]

cstevensjr and Doug G, thank you both very much for response and useful links! Now I know where to start studying. Thanks again and have a nice day!  

[FrancisChung]

Not sure if it's too late but just couple of things I thought of

1) certain fields like timestamps are usually different across languages and certainly have different meaning between programming languages and databases.

2) Probably best to stick to UTF-8 or some sort of encoding ALL of the technology stack agrees with (i.e. PHP, .NET, MySQL, SQL Server)

3) To call SQL Server from PHP
http://php.net/manual/en/function.sqlsrv-connect.php

To call .NET code from PHP

http://php.net/manual/en/book.com.php

4) A quick way of testing this is to install one of the basic example sites and then migrate the MySQL DB into SQL Server and change DB Connections in PW to point to the MySQL instance?
You could probably find the UnitTests they have (on github?) for PW and run that against this example site?

You could also see how the DB is created and if it's a SQL script, perhaps run that on a SQL Server and see what happens?