MSSQL Support?

[Mesmeric]

Hi All,

Haven't tested your Portal yet, i have been using PHPBB3Portal for a while and relatively happy with it apart from some of the bugs which by the looks of things you guys have fixed!

My issue now is that i need to run PHPBB3 on a MSSQL backend, as you will be aware PHPBB3 Portal does not work with MSSQL. Was wondering if this version does or not?

If not would you be able to point me in the direction of the files that contain MySQL code so i can manually convert this code into MSSQL compatible. Or if one of you guys are a bit of a SQL wizard maybe you want to add in the support?

Many Thanks in advance

Mesmeric

[Kevin]

We do support MSSQL, as all phpBB3 supported database types.
Would be nice if you could give us a feedback after installation, cause we have a lack of MSSQL testing environments.

[Mesmeric]

Hi Kevin,

Thanks for the speedy response.

I have just tried going for an install and i get 'Sorry, unsupported DBMS found'. I searched for that error in the code of portal.php and can see that it checks which DBMS system you are using and MSSQL is there. At present i am using a SQL2005 instance. Gimme 30mins or so and i will knock up a 2000 box to test with that.

Thanks again!

Mesmeric

[Kevin]

Thanks for the feedback , Mesmeric!

So we have to get this fixed, but I'm at work right now - no rush!

Have a look in the install_portal /schemas directory - there is the _mssql_schema.sql file.

[Mesmeric]

Hi Kevin,

Unfortunatley it comes up with the same error in SQL2K as in 2K5. I have taken a look at the schema, and it looked ok, so i imprted the code into SQL2005 Management Studio and the script executed OK.

So that kind of stumps me at the minute, I will keep having a bash at it and see if i can get round things and let you know how i get on, however if you have any good ideas or suggestions they would certainly be appreciated.

Many Thanks

Mesmeric

[nickvergessen]

hey,
please follow this two steps:

open install_portal/install.php
find:

Code: Select all

		default:
			trigger_error('Sorry, unsupported DBMS found.');
		break;

replace with:

Code: Select all

		default:
			trigger_error('Sorry, unsupported DBMS found: ' . $db->sql_layer);
		break;

Afterwards run the install again and post the resulting error message.


than do this change
same file, find:

Code: Select all

		case 'mssql':
			$db_schema = 'mssql';
			$delimiter = 'GO';
			$comments = 'remove_comments';
		break;

replace with

Code: Select all

		case 'mssql':
		case 'mssql_odbc':
			$db_schema = 'mssql';
			$delimiter = 'GO';
			$comments = 'remove_comments';
		break;

than it should hopefully work, if not we'll be able to fix it

[Mesmeric]

Hi,

Thanks for the info, The install went through and i got the successful screen. Then went to portal index and got this.........

Code: Select all

General Error
SQL ERROR [ mssql_odbc ]

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'FROM'. [37000]

SQL

SELECT user_id, username, user_colour, user_birthday FROM phpbb_users WHERE user_birthday <> '' AND user_type IN (0, 3) ORDER BY SUBSTRING(user_birthday FROM 4 FOR 2) ASC, SUBSTRING(user_birthday FROM 1 FOR 2) ASC, username_clean ASC

BACKTRACE

FILE: includes/db/mssql_odbc.php
LINE: 149
CALL: dbal->sql_error()

FILE: portal/block/birthday_list.php
LINE: 30
CALL: dbal_mssql_odbc->sql_query()

FILE: portal.php
LINE: 151
CALL: include('portal/block/birthday_list.php')

But its not just a problem with the birthday list SQL code as i managed to get into the ACP and disable the Birthday list block, however then i got a similar error for the friends block so I turned off then random member and its now working fine. I think the SQL code needs playing with in those blocks.

Thanks again for your help,

Mesmeric

[Kevin]

Thanks again for the info, Mesmeric!

Good to know that the installer does his job now.
We'll fix the other errors as soon as possible.

Let me resume, you receive DB errors in the:
- Bithday list
- Random member
- Friends block

correct?

[Mesmeric]

Hi Kevin,

Yes thats correct, I went through last night and turned all blocks on and off to see if any of the others cause errors too and everything else seems fine.

So the problem blocks are .....

Birthday List
Random Member
Friends

If i get chance at all tonight i will take a look at the code and see if i can fix the problems.

Will keep you updated on progress.

Mesmeric

[nickvergessen]

Please try for the friends block
portal/blocks/friends.php
find

Code: Select all

$sql = $db->sql_build_query('SELECT_DISTINCT', array(

replace with

Code: Select all

$sql = $db->sql_build_query('SELECT', array(

[Mesmeric]

Hi Nick,

Thanks for the suggestion, however i changed the code in the friends block and recieved the below error......

Code: Select all

General Error
SQL ERROR [ mssql_odbc ]

[Microsoft][ODBC SQL Server Driver][SQL Server]Column "phpbb_users.username_clean" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. [37000]

SQL

SELECT TOP 8 u.user_id, u.username, u.user_colour, u.user_allow_viewonline, MAX(s.session_time) as online_time, MIN(s.session_viewonline) AS viewonline FROM phpbb_users u, phpbb_zebra z LEFT JOIN phpbb_sessions s ON (s.session_user_id = z.zebra_id) WHERE z.user_id = 2 AND z.friend = 1 AND u.user_id = z.zebra_id GROUP BY z.zebra_id, u.user_id, u.username, u.user_allow_viewonline, u.user_colour ORDER BY u.username_clean ASC

BACKTRACE

FILE: includes/db/mssql_odbc.php
LINE: 149
CALL: dbal->sql_error()

FILE: includes/db/mssql_odbc.php
LINE: 201
CALL: dbal_mssql_odbc->sql_query()

FILE: includes/db/dbal.php
LINE: 157
CALL: dbal_mssql_odbc->_sql_query_limit()

FILE: portal/block/friends.php
LINE: 49
CALL: dbal->sql_query_limit()

FILE: portal.php
LINE: 206
CALL: include('portal/block/friends.php')

I will keep having a play around but if you have any more ideas please let me know.

Mesmeric

[Kevin]

Hi Mesmeric,

could you please post every single error message, for each block?

Thank you!

[Mesmeric]

Hi,

Friends Block Fixed ( kind of )

I changed the 'SELECT' back to 'SELECT_DISTINCT'

Then changed

Code: Select all

'ORDER_BY'	=> 'u.username_clean ASC',

To

Code: Select all

'ORDER_BY'	=> 'u.username ASC',

This fixed the Friends block, the problem was that the SELECT_DISTINCT statement did not include the 'username_clean' column so could not use it in the query. Instead of adding it to the query I ordered by 'u.username' as this displays the name in the format that the user signed up with so in my case 'Mesmeric' instead of 'mesmeric'. So hopefully that one is OK now.

Birthday Block

The Error.....

Code: Select all

General Error
SQL ERROR [ mssql_odbc ]

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'FROM'. [37000]

SQL

SELECT user_id, username, user_colour, user_birthday FROM phpbb_users WHERE user_birthday <> '' AND user_type IN (0, 3) ORDER BY SUBSTRING(user_birthday FROM 4 FOR 2) ASC, SUBSTRING(user_birthday FROM 1 FOR 2) ASC, username_clean ASC

BACKTRACE

FILE: includes/db/mssql_odbc.php
LINE: 149
CALL: dbal->sql_error()

FILE: portal/block/birthday_list.php
LINE: 30
CALL: dbal_mssql_odbc->sql_query()

FILE: portal.php
LINE: 151
CALL: include('portal/block/birthday_list.php')

I was unsure what was actually wrong with the syntax here, I have been revising for another MS exam recently so mind a bit mashed but what i did was change this line......

Code: Select all

AND user_type IN (" . USER_NORMAL . ', ' . USER_FOUNDER . ') ORDER BY SUBSTRING(user_birthday FROM 4 FOR 2) ASC, SUBSTRING(user_birthday FROM 1 FOR 2) ASC, username_clean ASC';

To...

Code: Select all

AND user_type IN (" . USER_NORMAL . ', ' . USER_FOUNDER . ') ORDER BY user_birthday ASC';

This works now too, not sure what the extra code is actually doing

Random Member Block

The Error.....

Code: Select all

General Error
SQL ERROR [ mssql_odbc ]

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'LIMIT'. [37000]

SQL

SELECT * FROM phpbb_users WHERE user_type <> 2 AND user_inactive_time = 0 ORDER BY RAND() LIMIT 0,1

BACKTRACE

FILE: includes/db/mssql_odbc.php
LINE: 149
CALL: dbal->sql_error()

FILE: portal/block/random_member.php
LINE: 29
CALL: dbal_mssql_odbc->sql_query()

FILE: portal.php
LINE: 201
CALL: include('portal/block/random_member.php')

So all looked fine in the code however, I changed this line...

Code: Select all

ORDER BY RAND() 
	LIMIT 0,1';

To.....

Code: Select all

ORDER BY NEWID()';

And this now works too. I don't think the 'RAND ()' works in MSSQL, i know it does in MySQL.

Hope this helps, please let me know if there is anything you want me to try different.

Mesmeric

[nickvergessen]

Seems like i found the fix for the random
http://www.parsed.org/tip/347/