View Issue Details

IDProjectCategoryView StatusLast Update
0011776mantisbtdb mssqlpublic2014-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.

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:23

reporter  

Database_api.path (2,136 bytes)
genius_p

genius_p

2010-06-17 07:26

reporter   ~0025878

Last edited: 2010-06-17 08:02

View 2 revisions

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

2010-09-24 10:40

reporter  

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)
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 cant 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. Its PHP 5.3.x, MS SQL 2000 and IIS6 based. Now Im 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 Microsofts 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 cant 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, thats 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

developer   ~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 cant 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 :-)
Thats 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

developer   ~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 dont 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

Ive tested dregads 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 @dregads 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 Im 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

Ive 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, Im 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, Id advise running Mantis with Mysql Only to avoid issues.

Thanks
Paul

Related Changesets

MantisBT: master 29f85ad2

2012-01-21 14:59:25

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
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 14:59:25

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
mod - admin/check.php Diff File
mod - admin/install.php Diff File
mod - core/database_api.php Diff File

Issue History

Date Modified Username Field Change
2010-04-09 10:27 tomkraw1 New Issue
2010-05-05 09:13 tomkraw1 Note Added: 0025404
2010-06-17 07:23 genius_p File Added: Database_api.path
2010-06-17 07:26 genius_p Note Added: 0025878
2010-06-17 08:02 genius_p Note Edited: 0025878 View Revisions
2010-07-20 09:27 tomkraw1 Note Added: 0026103
2010-09-24 10:40 tomkraw1 File Added: install.php.patch
2011-01-05 06:20 tomkraw1 Note Added: 0027807
2011-01-10 17:32 ted Note Added: 0027888
2011-07-20 03:21 genius_p Note Added: 0029208
2011-07-20 03:48 tomkraw1 Note Added: 0029211
2012-01-02 11:35 dregad Relationship added has duplicate 0011497
2012-01-02 11:39 dregad Relationship added has duplicate 0013676
2012-01-02 11:49 dregad Relationship added related to 0012081
2012-01-02 11:49 dregad Relationship added related to 0012082
2012-01-02 12:27 dregad Note Added: 0030765
2012-01-02 12:27 dregad Status new => acknowledged
2012-01-02 12:27 dregad Summary Support for Sql Server Native => Support for Sql Server Native driver (sqlsrv)
2012-01-02 12:27 dregad Description Updated View Revisions
2012-01-02 19:20 dregad Note Added: 0030766
2012-01-02 19:21 dregad Assigned To => dregad
2012-01-02 19:21 dregad Target Version => 1.2.9
2012-01-03 03:33 atrol Relationship added related to 0013409
2012-01-03 03:36 atrol Note Added: 0030771
2012-01-03 05:24 dregad Note Added: 0030773
2012-01-03 05:24 dregad Relationship added related to 0013713
2012-01-04 06:01 rombert Note Added: 0030787
2012-01-04 06:36 dregad Note Added: 0030788
2012-01-04 06:37 ted Note Added: 0030789
2012-01-04 14:07 tomkraw1 Note Added: 0030796
2012-01-05 04:22 dregad Note Added: 0030799
2012-01-05 04:37 rombert Note Added: 0030800
2012-01-06 23:33 dhx Note Added: 0030837
2012-01-08 09:16 tomkraw1 Note Added: 0030859
2012-01-08 10:45 tomkraw1 Note Added: 0030861
2012-01-08 11:36 dregad Relationship added related to 0011524
2012-01-09 10:44 dregad Note Added: 0030880
2012-01-09 13:20 tomkraw1 Note Added: 0030884
2012-01-14 10:30 tomkraw1 Note Added: 0030908
2012-01-15 14:42 tomkraw1 Note Added: 0030910
2012-01-21 18:03 dregad Note Added: 0030985
2012-01-21 18:03 dregad Status acknowledged => resolved
2012-01-21 18:03 dregad Fixed in Version => 1.2.9
2012-01-21 18:03 dregad Resolution open => fixed
2012-01-21 19:00 dregad Changeset attached => MantisBT master 29f85ad2
2012-01-21 19:00 dregad Changeset attached => MantisBT master-1.2.x 88274c93
2012-03-03 21:45 vboctor Status resolved => closed
2013-04-05 17:57 grangeway Status closed => acknowledged
2013-04-05 17:57 grangeway Note Added: 0036366
2013-04-05 18:18 grangeway Relationship added related to 0015721
2013-04-06 03:42 dregad Status acknowledged => closed
2013-04-06 07:23 grangeway Status closed => acknowledged
2013-04-06 09:24 dregad Tag Attached: 2.0.x check
2013-04-06 09:24 dregad Status acknowledged => closed
2014-05-16 15:00 grangeway Note Added: 0040348
2014-09-23 18:05 grangeway Tag Detached: 2.0.x check