Adapted for mssql

[jude.hanlon]

I've adapted phpjobscheduler to run on a windows server 2003/mssql set-up. I know this is an unusual set-up with php so unlikely to be *widely* needed, but in case anyone does, here's a line by line log of what I've changed to get it working. Please note there may be some functions I haven't tried yet, so this may be incomplete. Also, I didn't spend too much time working out how to auto-create primary key/autoincrement columns in the DB - I let the script create the basic table, then changed the column settings through Enterprise Manager.

cheers!
jude

here's the list/log (nb some of the line numbers may be one or two out, but they'll be close)

eg_email_site_stats_to_boss.php - no changes
firepjs.php - no changes
index.php - no changes
phpjobscheduler.php - no changes
pjs.css - no changes

pjsfiles/

add-modify.html - no changes

add-modify.php
65: > $result = mssql_query($query) or die(mssql_get_last_message());
removed 66

config.inc.php - no changes
constants.inc.php - no changes

delete.php
31: > $result = mssql_query($query) or die(mssql_get_last_message());
32: > removed "IF..." as this is served by "or die.." above

error-logs.html - no changes
error-logs.php - no changes
footer.html - no changes
functions.js - no changes

functions.php
51: if(mysql_num_rows(mysql_query("SHOW TABLES LIKE '".LOGS_TABLE."'"))==0)
> if(mssql_num_rows(mssql_query("SELECT * FROM sysobjects WHERE xtype='U' AND name LIKE '".LOGS_TABLE."'"))==0)
55: id int(11) NOT NULL
> id varchar(50) NOT NULL

61: $result=mysql_query($q_create_table);
> $result=mssql_query($q_create_table) or die (mssql_get_last_message());
63: if(mysql_num_rows(mysql_query("SHOW TABLES LIKE '".PJS_TABLE."'"))==0)
> if(mssql_num_rows(mssql_query("SELECT * FROM sysobjects WHERE xtype='U' AND name LIKE '".PJS_TABLE."'"))==0)
75: $result=mysql_query($main_table);
> $result=mssql_query($main_table);
77-80:
$result=mysql_query("select scriptpath from ".PJS_TABLE);
if (mysql_field_len($result, 0)<255) $result=mysql_query("ALTER TABLE ".PJS_TABLE." CHANGE scriptpath scriptpath VARCHAR(255)");
$result=mysql_query("SHOW COLUMNS FROM ".PJS_TABLE." LIKE 'run_only_once' ");
if (!mysql_num_rows($result)) $result=mysql_query("ALTER TABLE ".PJS_TABLE." ADD run_only_once tinyint(1) NOT NULL DEFAULT '0'");
>>
$result=mssql_query("select scriptpath from ".PJS_TABLE);
if (mssql_field_length($result, 0)<255) $result=mssql_query("ALTER TABLE ".PJS_TABLE." CHANGE scriptpath scriptpath VARCHAR(255)");
$result=mssql_query("SELECT 1 FROM sysobjects o, syscolumns c WHERE o.id=c.id AND o.name LIKE '".PJS_TABLE."' AND c.name LIKE 'run_only_once' ");
if (!mssql_num_rows($result)) $result=mssql_query("ALTER TABLE ".PJS_TABLE." ADD run_only_once tinyint(1) NOT NULL DEFAULT '0'");

118-120: @$db_link = mysql_connect(DBHOST, DBUSER, DBPASS);
if ($db_link) @mysql_select_db(DBNAME);
if (mysql_error())
>>
@$db_link = mssql_pconnect(DBHOST, DBUSER, DBPASS);
if ($db_link) @mssql_select_db(DBNAME) or exit(mssql_get_last_message());


122: if (SHOW_MYSQL_ERRORS) echo "MySQL error: ". mysql_error(). " MySQL error no: ".mysql_errno();
> removed

131: if ($db_link) $result = mysql_close($db_link);
> if ($db_link) $result = mssql_close($db_link);
146: $result=mysql_query($i_query);
> $result=mssql_query($i_query);
153: $result = mysql_query($query);
> $result = mssql_query($query) or die (mssql_get_last_message());
154: if (!$result) js_msg("There has been an error: ".mysql_error() );
> removed if/else statement
157: if (mysql_num_rows($result)) // check has got some
> if (mssql_num_rows($result)) // check has got some
162: while ($i < mysql_num_rows($result))
> while ($i < mssql_num_rows($result))
164-170:
$id=mysql_result($result,$i, 'id');
$scriptpath=mysql_result($result,$i, 'scriptpath');
$name=mysql_result($result,$i, 'name');
$time_interval=mysql_result($result,$i, 'time_interval');
$fire_time=mysql_result($result,$i, 'fire_time');
$time_last_fired=mysql_result($result,$i, 'time_last_fired');
$run_only_once_txt= (mysql_result($result,$i, 'run_only_once'))? "<i><font color=\"#ff0000\"> Will run just once</font></i>":"";
>>
$id=mssql_result($result,$i, 'id');
$scriptpath=mssql_result($result,$i, 'scriptpath');
$name=mssql_result($result,$i, 'name');
$time_interval=mssql_result($result,$i, 'time_interval');
$fire_time=mssql_result($result,$i, 'fire_time');
$time_last_fired=mssql_result($result,$i, 'time_last_fired');
$run_only_once_txt= (mssql_result($result,$i, 'run_only_once'))? "<i><font color=\"#ff0000\"> Will run just once</font></i>":"";
215: $query="select * from ".LOGS_TABLE." ORDER BY id DESC LIMIT $qstart, $num";
> $query="select TOP $num * from ".LOGS_TABLE." WHERE id not in (select top $qstart id FROM ".LOGS_TABLE." ORDER BY id) ORDER BY id DESC";
226: $result = mysql_query($query);
> $result = mssql_query($query) or die (mssql_get_last_message());
227: if (!$result) js_msg("There has been an error: ".mysql_error() );
> removed if/else statement
230: if (mysql_num_rows($result)) // check has got some
> if (mssql_num_rows($result)) // check has got some
235: while ($i < mssql_num_rows($result))
237-241:
$id=mysql_result($result,$i, 'id');
$script=mysql_result($result,$i, 'script');
$output= mysql_result($result,$i, 'output') ;
$output_decoded=html_entity_decode($output);
$execution_time= mysql_result($result,$i,'execution_time');
>>
$id=mssql_result($result,$i, 'id');
$script=mssql_result($result,$i, 'script');
$output= mssql_result($result,$i, 'output') ;
$output_decoded=html_entity_decode($output);
$execution_time= mssql_result($result,$i,'execution_time');

header.html - no changes
index.php - no changes
main.html - no changes

modify.php
29: $result = mysql_query($query);
> $result = mssql_query($query) or die (mssql_get_last_message());
30: if (!$result) js_msg("There has been an error: ".mysql_error() );
> removed
31: else $row = mysql_fetch_array($result);
> $row = mssql_fetch_array($result);

phpjobscheduler.php
31: $result = mysql_query($query);
> $result = mssql_query($query);
33: if (mysql_num_rows($result)) // check has got some
> if (mssql_num_rows($result)) // check has got some
36-43:
while ($i < mysql_num_rows($result))
{
$id=mysql_result($result,$i, 'id');
$scriptpath=mysql_result($result,$i, 'scriptpath');
$time_interval=mysql_result($result,$i, 'time_interval');
$fire_time=mysql_result($result,$i, 'fire_time');
$time_last_fired=mysql_result($result,$i, 'time_last_fired');
$run_only_once=mysql_result($result,$i, 'run_only_once');
>>
while ($i < mssql_num_rows($result))
{
$id=mssql_result($result,$i, 'id');
$scriptpath=mssql_result($result,$i, 'scriptpath');
$time_interval=mssql_result($result,$i, 'time_interval');
$fire_time=mssql_result($result,$i, 'fire_time');
$time_last_fired=mssql_result($result,$i, 'time_last_fired');
$run_only_once=mssql_result($result,$i, 'run_only_once');
52: mysql_query($query);
> mssql_query($query);

PLUS after tables were created, manually (SQL Server Enterprise) alter the PK to auto-increment

[davidgwalker]

Hi Jude, welcome to the forums.

Many thanks for your contribution, I am sure it will helps others considering phpJobScheduler for use with MSSQL.

All the best

Dave