PhpNuke on MSSQL - working! (so far...)

Nuke Cadet Joined: Jan 23, 2007 Posts: 6

I don't know if anyone have posted some kind of manual about mssql, but here's the why I've done it:

(Tested on Microsoft SQL Server 2000 Enterprise Edition.)

Part 1: Creating the database.

As you might know, the included .sql file doesn't work with MSSQL. To override this, I've:
a. installed MySQL and imported the .sql file.
b. installed MySQL ODBC Driver.
c. imported the databases from the MySQL server to the MSSQL server using the ODBC driver.
(d. Here something went wrong - The Auto Inc. fields weren't set as such so I had to manually set them the old way - one table at a time. you might wanna check the default value of the different fields while you're at it.)

Part 2: db\mssql.php.

It seems that the MSSQL layer has some issues that the phpbb team didn't handle. most of them are simple lower vs. upper case, but they also didn't know that the now() function should be replaced with getdate() in mssql. So to function sql_query() in mssql.php i've added at the beginning:

--- EDIT: Please see fix in newer post below. ---

Part 3: Reserved Words (bad programing=hard work)

There are many words that shouldn't have been used for programing as far as I see it, but it a bit too late now. Anyway, these are the reserved words (for mssql anyway) that I've found (i've added a 'n_' prefix to them in order to fix them, but it's a lot of work):

date *
position (btw, in the table "blocks", it was already called bposition...)
view
action *
time *
timeStamp
hour
year
month
var
count

* - be careful when renaming those, since they are also used for legit functions such as $time() and "<action=" in html.

4. Part 4: MSSQL - Null bug?

The last issue I came across was that if field of a query was a null, for some reason sometimes it was returned as " " (a space) or (0) (a zero). so in some cases, I had to change the code from -

if (<somefield> == '') {}

and such, to something like-

if (trim(<somefield>) == '') {}

--------------------------------

Anyway, this is what I've found so far. basic checks looks ok, but I guess there's still stuff to fix. I'll try to keep you updated.

Nuke Cadet Joined: Jan 23, 2007 Posts: 6

well... It seems I spoke way too soon.

There's a BIG problem with the INSERT INTO queries which doesn't specify the "fields" into which the values are inserted [i.e. "insert into SomeTable values (list of values)" VS. "insert into SomeTable (list of fields) values (list of values)"]. The prblem is caused by the Auto number fields which while in MySQL could have been assigned a NULL which was ignored, this NULL triggers an error in MSSQL.
(google for SET IDENTITY_INSERT tablename ON/OFF.)

SO, since I don't wanna go the "lets rewrite every insert into query" way, I'm writing a function which I'll add to the data layer that will add the names of the relevant fields to the problematic query (and will remove the invalid nulls).

Again, I'll be back.

Site Admin Joined: Aug 17, 2003 Posts: 12403

Sounds like a lot of work to make compliant. Best of luck on this

Nuke Cadet Joined: Jan 23, 2007 Posts: 6

just a quick update.

I've erased most of the changes I've made to the sql_query() function, leaving only:

Code:

$query=str_ireplace("now()", "getdate()" ,$query);
$query=str_ireplace("\'", '\'\'' ,$query); //changing \' -> ''

and changed

Code:

if( preg_match('#^SELECT(.*?)(LIMIT ([0-9]+)[, ]*([0-9]+)*)?$#s', $query, $limits) )

to

Code:

if( preg_match('#^SELECT(.*?)(LIMIT ([0-9]+)[, ]*([0-9]+)*)?$#si', trim($query), $limits) )

(Edited [01-02-07] adding case insensitive, and trim.)

and then, after the line:

Code:

else if( preg_match('#^INSERT #i', $query) )
{

adding this:

Code:

if (insert_has_fields($query)) {
//remove null fields?
}
else {
$nskip = insert_null_values_count($query);
$nlimit = insert_values_count($query)-insert_null_values_count($query);
$query= inject_insert_fields($query,get_fields(get_table($query),$nskip,$nlimit));
}

I'll post the new function soon.

Nuke Cadet Joined: Jan 23, 2007 Posts: 6

This is the first (and hopefully the last) version of the function I've added that are used by the MS-SQL layer.

(I've added them to db.php, but you can save them to a new file and use include or some thing like that...)

Code:

And a quick note: the function only handles nulls at the beginning of the table, so make sure you keep the auto-numbered fields first.

Nuke Cadet Joined: Jan 23, 2007 Posts: 6

well, first of all I had a bug the insert_remove_null_values() function, so it has been edited.

And for the new problems:
1. dates.

well, MSSQL stores dates at its own will/format, so when ever you see -

Code:

ereg ("([0-9]{4})-([0-9]{1,2})-([0-9]{1,2}) ([0-9]{1,2}):([0-9]{1,2}):([0-9]{1,2})", $VAR1, $VAR2);

You have to change it to:

Code:

ereg ("([0-9]{4})-([0-9]{1,2})-([0-9]{1,2}) ([0-9]{1,2}):([0-9]{1,2}):([0-9]{1,2})", date("Y-m-d H:i:s",strtotime($VAR1)), $VAR2);

(which to my opinion, this is how it should have been like anyway.)

if I'm not mistaken, this line appears 39 times in various files (with $time/$mydate/$ratingtimestamp/$editorialtimestamp as "$VAR1").

2. more on dates.

In modules\download\index.php, when querying a date, insted of using:

Code:

SELECT <whatever> from <sometable> WHERE n_date like '%<somedatevar>%'

it should be

Code:

SELECT <whatever> from <sometable> WHERE CONVERT(varchar(10), n_date, 121) like '%<somedatevar>%'

this will for the date to be converted to a string in a "YYYY-MM-DD" format and trim the time from it (since I used varchar(10)).

3. stupid extra space can cuz you serious headaches.

In the MSSQL.php file, I have added a trim() so change:

Code:

if( preg_match('#^SELECT(.*?)(LIMIT ([0-9]+)[, ]*([0-9]+)*)?$#si', $query, $limits) )

to:

Code:

if( preg_match('#^SELECT(.*?)(LIMIT ([0-9]+)[, ]*([0-9]+)*)?$#si', trim($query), $limits) )

(an extra space at the end of the query will prevent the LIMIT fix from working.)

well, that's all (for this week anyway...)

Nuke Cadet Joined: Feb 05, 2007 Posts: 1

I can't seem to get it working with the mssql-odbc connection. I get this message on the admin.php page: "There seems to be a problem with the mssql-odbc server, sorry for the inconvenience." Here's my config:

$dbhost = "localhost";
$dbuname = "nuke_user"; #my sql 2005 user
$dbpass = "nuke_pwd";
$dbname = "my_nuke"; #the db name AND the System ODBC name
$prefix = "my_nuke"; #no clue if this is correct
$user_prefix = "nuke_user"; #no clue if this is correct
$dbtype = "mssql-odbc";
$sitekey = "SdFk*jkjbkuuz-dm98769.4b67DS+e4";
$gfx_chk = 0;
$subscription_url = "";
$admin_file = "admin";
$tipath = "images/topics/";
$nuke_editor = 1;
$display_errors = true;

Am I completely missing something here? I'm running this on Windows XP and Apache 2.2. I'm new to both PHP and Apache, so I apologize for the (probably) simple question.

Also, if the mssql-odbc DB layer is the problem, can you post or send me your working version? Thanks.

Nuke Cadet Joined: Jan 23, 2007 Posts: 6

I haven't tried using MSSQL via ODBC, but directly so I use:

$dbhost = "localhost";
$dbuname = "MyUser";
$dbpass = "MyPass";
$dbname = "nuke";
$prefix = "nuke";
$user_prefix = "nuke";
$dbtype = "mssql";
$sitekey = "MySiteKey";
$gfx_chk = 0;
$subscription_url = "";
$admin_file = "admin";
$tipath = "images/topics/";
$nuke_editor = 1;
$display_errors = false;