Mediateca ELIS

Environment

You are operating on a LAMP server with Apache + PHP, but your DB is a SQL Server instance. Usually you can use mssql_get_last_message to retrieve error messages, but when a trigger fails you’ll get only a generic error message: the transaction ended in the trigger. Here will explain a workaround to catch specific error messages from triggers.

What you need

You need an IIS server connected to SQL Server and a MySQL server to storage your messages. You have to enable error display option on your IIS server.

How it works

When an error occurs, Apache send to IIS the SQL instruction that has generated the error. IIS execute again this SQL instruction and return the error in html format. PHP function clean html tags, storage new errors and return custom messages.

ASP page on IIS server

First of all you need an ASP page that have to take SQL instructions through a POST param. In the code below we suppose that the param is called data:

http://www.iis.org/db.asp

<%
SQLAnagraphic = Request.Form("data")

strConnection = "Provider=SQLOLEDB.1;Password=YOURPWD;User ID=YOURUSER;Initial Catalog=YOURDB;Data Source=DBSERVER"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnection

set rs=Server.CreateObject("ADODB.recordset")
rs.Open SQLAnagraphic, conn
%>

MySQL support table

Probably you will prefer not displaying all error messages as they come from SQL Server/ASP for security reasons and also because some of them could be hard to understand for end users, particulary for constraint violation. Moreover you should be able to monitor error messages you display. For these reasons we use a MySQL support table to log all messages you return to the application. Here there is the code:

CREATE TABLE `mssql` (
`id` int(11) NOT NULL auto_increment,
`query` varchar(1000) NOT NULL COMMENT 'SQL instruction',
`raw` varchar(1000) NOT NULL COMMENT 'raw message from the ASP page',
`msg` varchar(300) default NULL COMMENT 'final message you will present to the end user',
`db` varchar(50) default NULL COMMENT 'Original DB where the error occured',
`table` varchar(50) default NULL COMMENT 'Original table in which the error occured',
`field` varchar(50) default NULL COMMENT 'Name of the field involved in this error (usually is reported only for constraints violation)',
`constraint` varchar(50) default NULL COMMENT 'name of the constraint violated',
`type` varchar(50) default NULL COMMENT 'error type (e.g. constraint, unique, ecc...)',
`application` varchar(50) default NULL COMMENT 'optional name of the application required error message service',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='log MSSQL table' AUTO_INCREMENT=1;

PHP page on Apache server

At the end PHP functions have to send sql instructions to db.asp page. This page contains 3 functions:

• get_mssql_error to send HTTP request to the ASP page
• log_mssql_error to write on MySQL support table
• userfriendly_error_msg to change raw messages into custom application messages

Two elements of interest looking deeper in the code are:

1. You have to write your message trasformation in userfriendly_error_msg function
2. userfriendly_error_msg returns $param array: if $params['msgToLog'] is true then log_mssql_error function will be called and the message will be logged otherwise you will lost that information. This possibility allow you to select which messages are really interesting for you.

Here we have the code:

<?

/**
* This function send a POST HTTP request useing cURL library.
*
* @param string $sqlquery = sql instruction
* @param string $application = you can specify something here to identify what
* is the application is asking to retrive the sql error.
* It could be useful if this function is used by different
* applications
* @param bool $raw_error_message = if true this function return raw error message
* and userfriendly_error_msg function won't be called
* @return string
*/
function get_mssql_error($sqlquery, $application = '', $raw_error_message = false){
// Open curl sessione
$session = curl_init('http://db-log.elis.org/db.asp');

$data = urlencode($sqlquery);

curl_setopt($session, CURLOPT_HEADER, false);
curl_setopt($session, CURLOPT_RETURNTRANSFER, true);
curl_setopt($session, CURLOPT_POST, true);
curl_setopt($session, CURLOPT_POSTFIELDS, "data=".$data);

$res = curl_exec($session);
curl_close($session);

if ($raw_error_message) return "$res - $sqlquery";
$params = array ();
$msg = userfriendly_error_msg($res, $params);
$params['application'] = $application;
$params['raw'] = $res;
$params['query'] = $sqlquery;

if (isset($params['msgToLog']) && $params['msgToLog']) {
unset ($params['msgToLog']);
log_mssql_error ($params);
}
return $msg;
}

function log_mssql_error ($params) {
$link = mysql_connect('www.mssql.org', 'username', 'riversino');
if (!$link) return;

foreach ($params as $key => $param) {
if ($param == '') unset ($params[$key]);
else $params[$key] = "'" . mysql_real_escape_string(utf8_decode($param), $link) . "'";
}
$query = 'INSERT INTO drupal.mssql (`' . implode ('`,`',array_keys($params)) . '`) VALUES (' . implode (',',$params) .')';
mysql_query($query, $link);
mysql_close($link);
}

function userfriendly_error_msg ($errormsg, &$params = false) {
$init_ctrlstr = '';
$end_ctrlstr = '';
$table = '';
$db = '';
$field = '';
$constraint = '';
$type = '';
$msgToLog = false;

$errormsg = str_replace("\n", "", $errormsg);
preg_match_all("|$init_ctrlstr(.+)$end_ctrlstr|U", $errormsg, $text_found);
$errormsg = '';

foreach ($text_found[1] as $selectLongest)
if (strlen ($selectLongest) > strlen ($errormsg)) $errormsg = $selectLongest;

preg_match_all("|[ ,.]+'(.+)'[ ,.]+|U", $errormsg, $variables);

// attribuisce il significato alle variabili e classifica il tipo di errore
if (strpos($errormsg, 'CREATE RULE') !== false) {
$table = $variables[1][0];
$db = $variables[1][1];
$field = $variables[1][2];
$type = 'constraint';
}

if (strpos($errormsg, 'UNIQUE KEY') !== false) {
$constraint = $variables[1][0];
$table = $variables[1][1];
$type = 'unique';
}

if (strpos($errormsg, 'PRIMARY KEY') !== false) {
$constraint = $variables[1][0];
$table = $variables[1][1];
$type = 'primarykey';
}

if (strpos($errormsg, 'chiave duplicata') !== false) {
$constraint = $variables[1][1];
$table = $variables[1][0];
$type = 'primarykey';
}

if (strpos($errormsg, 'I dati di tipo string o binary verrebbero troncati') !== false) {
$type = 'toolong';
}

switch ($type) {
case 'constraint':
/* some code */
break;
case 'toolong':
$msg = "Data are too long";
break;
default:
$msgToLog = false;
$msg = $errormsg;
}
$params['msg'] = $msg;
$params['db'] = $db;
$params['table'] = $table;
$params['field'] = $field;
$params['constraint'] = $constraint;
$params['type'] = $type;
$params['msgToLog'] = $msgToLog;

return $msg;
}

?>