View Issue Details

WikiRelated ChangesetsJump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0011776mantisbtdb mssqlpublic2010-04-09 10:272014-09-23 18:05
Reportertomkraw1 Assigned Todregad  
PrioritynormalSeverityfeatureReproducibilityN/A
Status closedResolutionfixed 
PlatformWindowsOSWindowsOS Version2003
Product Version1.2.0 
Target Version1.2.9Fixed in Version1.2.9 
Summary0011776: Support for Sql Server Native driver (sqlsrv)
Description

Since PHP 5.3 has been released, as per PHP documentation the mssql driver is no longer available on Windows [1]. Microsoft has published a new SQL Server Native Driver [2].

This driver is supported by ADOdb so it should be possible to use this driver in Mantis BT.

[1] http://www.php.net/manual/en/intro.mssql.php
[2] http://msdn.microsoft.com/en-us/sqlserver/ff657782.aspx

TagsNo tags attached.
Attached Files
Database_api.path (2,136 bytes)    
--- C:/Users/Genius.COMPENG/Desktop/database_api.php	�� ��� 23 14:28:34 2010
+++ //iserv/e$/Mantis/web/core/database_api.php	�� �� 17 10:57:12 2010
@@ -161,6 +161,9 @@
 		case 'odbc_mssql':
 			$t_support = function_exists( 'odbc_connect' );
 			break;
+		case 'mssqlnative':
+			$t_support = function_exists( 'sqlsrv_connect' );
+			break;
 		default:
 			$t_support = false;
 	}
@@ -211,6 +214,7 @@
 	switch( $t_db_type ) {
 		case 'mssql':
 		case 'odbc_mssql':
+		case 'mssqlnative':
 			return true;
 	}
 
@@ -304,7 +308,7 @@
 
 	static $s_check_params;
 	if( $s_check_params === null ) {
-		$s_check_params = ( db_is_pgsql() || config_get_global( 'db_type' ) == 'odbc_mssql' );
+		$s_check_params = ( db_is_pgsql() || config_get_global( 'db_type' ) == 'odbc_mssql' || config_get_global( 'db_type' ) == 'mssqlnative');
 	}
 
 	if( ON == $g_db_log_queries ) {
@@ -331,6 +335,13 @@
 			if( $arr_parms[$i] === false ) {
 				$arr_parms[$i] = 0;
 			}
+			if( config_get_global( 'db_type' ) == 'mssqlnative' && is_bool($arr_parms[$i]))
+			{
+				if($arr_parms[$i])
+					$arr_parms[$i] =1;
+				else
+					$arr_parms[$i] =0;
+			}
 		}
 	}
 
@@ -443,7 +454,7 @@
 	}
 
 	# mysql obeys FETCH_MODE_BOTH, hence ->fields works, other drivers do not support this
-	if( $g_db_type == 'mysql' || $g_db_type == 'odbc_mssql' ) {
+	if( $g_db_type == 'mysql' || $g_db_type == 'odbc_mssql'  || $g_db_type == 'mssqlnative' ) {
 		$t_array = $p_result->fields;
 		$p_result->MoveNext();
 		return $t_array;
@@ -537,6 +548,11 @@
 		$result = db_query_bound( $query );
 		return db_result( $result );
 	}
+	if( db_is_mssql()){
+		$query = "SELECT IDENT_CURRENT('".$p_table . "')";
+		$result = db_query_bound( $query );
+		return db_result( $result);
+		}
 	return $g_db->Insert_ID();
 }
 
@@ -677,6 +693,7 @@
 
 	switch( $t_db_type ) {
 		case 'mssql':
+		case 'mssqlnative':
 		case 'odbc_mssql':
 		case 'ado_mssql':
 			if( ini_get( 'magic_quotes_sybase' ) ) {
@@ -728,6 +745,7 @@
 
 	switch( $t_db_type ) {
 		case 'mssql':
+		case 'mssqlnative':
 		case 'odbc_mssql':
 		case 'ado_mssql':
 			$content = unpack( "H*hex", $p_string );
Database_api.path (2,136 bytes)   
install.php.patch (720 bytes)    
*** Z:/Web/virtual/bugtracker/admin/install_org.php	Tue Sep 14 14:40:10 2010
--- Z:/Web/virtual/bugtracker/admin/install.php	Thu Sep 23 15:52:33 2010
***************
*** 421,426 ****
--- 421,432 ----
  			echo '<option value="mssql">Microsoft SQL Server (experimental)</option>';
  		}
  
+ 		if( $f_db_type == 'mssqlnative' ) {
+ 			echo '<option value="mssqlnative" selected="selected">Microsoft SQL Server Native Driver (experimental)</option>';
+ 		} else {
+ 			echo '<option value="mssqlnative">Microsoft SQL Server Native Driver (experimental)</option>';
+ 		}
+ 
  		if( $f_db_type == 'pgsql' ) {
  			echo '<option value="pgsql" selected="selected">PGSQL (experimental)</option>';
  		} else {
install.php.patch (720 bytes)   

Relationships

related to 0012081 closeddregad call function config_set error 
related to 0012082 closeddregad call function token_create error 
related to 0013713 closeddregad Upgrade ADOdb library to latest version 
related to 0011524 closeddregad 1.2.0rc2 with MS SQL not installable 
related to 0015721 closedgrangeway Functionality to consider porting to master-2.0.x 
has duplicate 0011497 closeddregad Connection issue with SQL Server (PHP 5.3) 
has duplicate 0013676 closeddregad unable to configure ms sql server 
related to 0013409 closeddregad Plugin database error with config_table and the value field as an text 

Activities

tomkraw1

tomkraw1

2010-05-05 09:13

reporter   ~0025404

In ADOdb SQL Server Native Driver uses key "mssqlnative". I think in database_api.php should be added case commands wih key "mssqlnative". It should work like "mssql".

SQL Server Native Driver uses "sqlsrv_" function names prefix. In order to check if driver is installed we can check existence of function "sqlsrv_connect".
genius_p

genius_p

2010-06-17 07:26

reporter   ~0025878

Last edited: 2010-06-17 08:02

For support "mssqlnative" apply attached path to core/database_api.php.
For Developers. Please check code and commit.

PS. Aplly path from issues 0012081, 0012082.
tomkraw1

tomkraw1

2010-07-20 09:27

reporter   ~0026103

I confirm it works. Thanks!
tomkraw1

tomkraw1

2011-01-05 06:20

reporter   ~0027807

Unfortunately I have to say that changes suggested by genius_p are not enough in my environment (IIS 6, PHP 5.3.2 and Sql Server 2000 (MSDE). I was able to install Mantis 1.2.3 but can't log in. I saw some sql error.
ted

ted

2011-01-10 17:32

reporter   ~0027888

I just set up an environment with PHP 5.3.5, SQL Server Native ODBC driver and SQL Server 2005 backend. After doing a workaround for 0012674 it currently seems to work...
genius_p

genius_p

2011-07-20 03:21

reporter   ~0029208

tomkraw1:
SQL Native Client work ONLY SQL Server 2005 or greater
tomkraw1

tomkraw1

2011-07-20 03:48

reporter   ~0029211

Belive me SQL Server Native driver works with MS SQL 2000 Standard. Last year I released an application in my company. It's PHP 5.3.x, MS SQL 2000 and IIS6 based. Now I'm going to upgrade my MS SQL.
dregad

dregad

2012-01-02 12:27

developer   ~0030765

Updated bug description with references to deprecation of mssql and Microsoft's new sqlsrv drivers.
dregad

dregad

2012-01-02 19:20

developer   ~0030766

Reminder sent to: rombert

I have implemented the patches proposed by genius_p and tomkraw1 with a few minor changes. Many thanks for your contribution guys !

https://github.com/dregad/mantisbt/tree/fix-11776-mssqlnative

However, since I do not have access to any SQL server installation, I can't test it. Robert, could you please have a look ?
atrol

atrol

2012-01-03 03:36

developer   ~0030771

Maybe we should upgrade ADOdb to a newer version (5.14 atm)

from ADOdb README
5.13 18 Aug 2011

The mssqlnative driver has been substantially improved, and although is still beta, is much more stable.
dregad

dregad

2012-01-03 05:24

developer   ~0030773

atrol, that's an excellent suggestion. I opened 0013713 to follow up on that.

I actually started doing this back in October to fix an issue I had with Oracle. Asked some questions on the mailing list [1] which never got answered; due to lack of time on my end I left things as they were and never followed up. Guess I should bump the thread.

[1] http://thread.gmane.org/gmane.comp.bug-tracking.mantis.devel/3652/focus=3658
rombert

rombert

2012-01-04 06:01

reporter   ~0030787

(In reply to comment 0011776:0030766)

I have implemented the patches proposed by genius_p and tomkraw1 with a few
minor changes. Many thanks for your contribution guys !

https://github.com/dregad/mantisbt/tree/fix-11776-mssqlnative

However, since I do not have access to any SQL server installation, I can't test
it. Robert, could you please have a look ?

I do not have access to an SQL server installation either. I did merge one or two pull requests, but was based on my human SQL parser :-) .

Perhaps we should get one. I know that SQL Server express is free for development purposes ( http://www.microsoft.com/sqlserver/en/us/editions/express.aspx ) and Window Server 2k8 has a trial version of 180 days ( http://www.microsoft.com/en-us/server-cloud/windows-server/2008-r2-trial.aspx ) .

That does not imply that I have the time to do that though.
dregad

dregad

2012-01-04 06:36

developer   ~0030788

Thanks for the reply rombert.

I do not have access to an SQL server installation either
Thought you did for some reason...

based on my human SQL parser :-)
That's what I use too... Which version do you have ? ;-)

That does not imply that I have the time to do that though.
Point taken - same here actually.

@genius_p and @tomkraw1 - would you guys have be able to test the patch and provide feedback ? I can then push the change to the core.
ted

ted

2012-01-04 06:37

reporter   ~0030789

I guess I could provide access to a licenced SQL Server 2008 (or 2005 if you prefer), if this would help.
tomkraw1

tomkraw1

2012-01-04 14:07

reporter   ~0030796

Yes, I can test the patch. I have an access to working installation of MS SQL 2000.
dregad

dregad

2012-01-05 04:22

developer   ~0030799

@tomkraw1 - thanks, let me know the results.

@ted, thanks for the offer; not sure how we can make use of it though.
rombert

rombert

2012-01-05 04:37

reporter   ~0030800

(In reply to comment 0011776:0030789)

I guess I could provide access to a licenced SQL Server 2008 (or 2005 if you
prefer), if this would help.

We could try and perform a few tests. There are two possibilities here:

  1. You provide us public access to the database with a username and password
  2. You install MantisBT yourself an connect it to MS SQL and allow us to access it

I guess the second option is more secure, but involves a bit more work for you.
dhx

dhx

2012-01-06 23:33

reporter   ~0030837

Can someone please try installing a modified version of MantisBT using the latest ADOdb 5.14 library?

To do this,

  1. Extract the latest version of MantisBT to a new directory.
  2. Delete the entire library/adodb directory
  3. Extract the latest version of ADOdb from http://sourceforge.net/projects/adodb/files/latest/download to a new library/adodb directory
  4. Continue with the normal method used to install MantisBT, using the native MSSQL driver option of ADOdb for database access

I don't have MSSQL and cannot test to see whether the newest vanilla version of ADOdb allows you install and use MantisBT correctly.
tomkraw1

tomkraw1

2012-01-08 09:16

reporter   ~0030859

I've tested dregad's git version.

During the installation in the step: "Schema AlterColumnSQL ( mantis_user_pref_table )"

I had an error:

BAD
ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay INT
ALTER TABLE mantis_user_pref_table ADD CONSTRAINT DFmantis_user_pref_table__redirect_delay4395 DEFAULT 0 FOR redirect_delay
ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay INT NOT NULL
SQLState: 42000 Error Code: 5074 Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]The object 'DF__mantis_usredir2180FB33' is dependent on column 'redirect_delay'. SQLState: 42000 Error Code: 4922 Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]ALTER TABLE ALTER COLUMN redirect_delay failed because one or more objects access this column.

I downloaded ADODB v5.14. Now in the same step I have an error:

BAD

ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay INT DEFAULT 0 NOT NULL
SQLState: 42000 Error Code: 156 Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'DEFAULT'.

One more thing. Could you also fix the issue 0011806 ?
tomkraw1

tomkraw1

2012-01-08 10:45

reporter   ~0030861

Hi!

In the issue 0011524 I added a patch to the ADODB v5.14 (datadict-mssqlnative.inc.patch) that works with @dregad's changes.

I also posted the patch on the ADODB phorum (http://phplens.com/lens/lensforum/msgs.php?id=18422).

I successively installed Mantis on my Wamp with mssql native driver and sql servr 2000 (MSDE).

It would be great if someone else checked it.
dregad

dregad

2012-01-09 10:44

developer   ~0030880

@tomkraw1 - to summarize what you did, for the record:

  1. download and install https://github.com/dregad/mantisbt/tree/fix-11776-mssqlnative
  2. download and install ADODb 5.14
  3. apply the patch you provided in 0011524
  4. tested successful install of MantisBT using mssqlnative (sqlsrv) driver

One more thing. Could you also fix the issue 0011806
Done
tomkraw1

tomkraw1

2012-01-09 13:20

reporter   ~0030884

@dregad - yes. This is what I did.

Unfortumately, after a few minutes of configuring Mantis I realized that it is not enough.

My patch (datadict-mssqlnative.inc.patch) in the "DropDefaultValue" method contains sql query Sql Server 2000 compatibile but incompatibile with sql 2005 and 2008.

Additionaly some update queries throw an error but at the moment I'm not sure how to fix it. ;-(
tomkraw1

tomkraw1

2012-01-14 10:30

reporter   ~0030908

@dregad - I think you can commit changes and close this issue. The problem I have after instalation is adodb related.
tomkraw1

tomkraw1

2012-01-15 14:42

reporter   ~0030910

I've just installed Mantis on the SQL Server 2005 Express. So, the problem I mensioned concerns only the SQL Server 2000 and older.
dregad

dregad

2012-01-21 18:03

developer   ~0030985

I have pushed the commit with a couple minor modifications.

Let me know if you experience any issues.
grangeway

grangeway

2013-04-05 17:57

reporter   ~0036366

Marking as 'acknowledged' not resolved/closed to track that change gets ported to master-2.0.x branch
grangeway

grangeway

2014-05-16 15:00

reporter   ~0040348

MantisBT currently supports Mysql and has support for other database engines.

The support for other databases is known to be problematic.

Having implemented the current database layer into Mantis 10 years ago, I'm currently working on replacing the current layer.

If you are interested in using Mantis with non-mysql databases - for example, Oracle, PGSQL or MSSQL, and would be willing to help out testing the new database layer, please drop me an email at paul@mantisforge.org

In the meantime, I'd advise running Mantis with Mysql Only to avoid issues.

Thanks
Paul

Related Changesets

MantisBT: master 29f85ad2

2012-01-21 09:59

dregad


Details Diff		 Add support for SQL Server Native driver

Since PHP 5.3 has been released, as per PHP documentation the mssql
driver is no longer available on Windows. Microsoft has published a
new SQL Server Native Driver.

This commits adds support for this new driver (sqlsrv) to MantisBT
as mssqlnative db type.

Thanks to genius_p and tomkraw1 for the original patch and testing.

Fixes 0011776		 Affected Issues
0011776
mod - admin/check/check_database_inc.php Diff File
mod - admin/install.php Diff File
mod - core/database_api.php Diff File

MantisBT: master-1.2.x 88274c93

2012-01-21 09:59

dregad


Details Diff		 Add support for SQL Server Native driver

Since PHP 5.3 has been released, as per PHP documentation the mssql
driver is no longer available on Windows. Microsoft has published a
new SQL Server Native Driver.

This commits adds support for this new driver (sqlsrv) to MantisBT
as mssqlnative db type.

Thanks to genius_p and tomkraw1 for the original patch and testing.

Fixes 0011776		 Affected Issues
0011776
mod - admin/check.php Diff File
mod - admin/install.php Diff File
mod - core/database_api.php Diff File