[development] Microsoft SQL Support added
Gildas Cotomale
gildas.cotomale at gmail.com
Sun Sep 10 03:26:54 UTC 2006
2006/9/6, Peter Heinrich <drupal at heinrichp.de>:
> I've done a quick hack to support MS-SQL-Server, since my company needed
> that functionality.
Nice idea :) And good job.
> Since only minor changes to the system were necessary I believe that
> supporting this database would not take so much.
Minor changes ? Not sure because you change many sql queries and should not.
> I've done that for version 4.7.2, you can get the patch here:
>
> http://www.heinrichp.de/drupal-4.7.2-mssql.patch
>
I put a look on your job and i can't understand some parts... Some
examples in database.mssql.in:
1. db_connect
Are you sure it works fine for you ? You took the the pgsql template
instead of mysql one. But when i refer to PHP mssql_* functions they
are closier to mysql_* than pg_* :/ And this is true for
mssql_connect()...
2. db_query_temporary
I don't have an Ms SQL Server in order to test. But, as far i can
remember, SQL Server knows "CREATE TEMPORARY"...
3. db_query
Why do you rewrite the LIMIT clause where ? This kind of things
shouldn't be directly stand here because db_query_range() role is...
:)
4. etc.
> This is not tested very much but it works for me quite fine.
> Tested with:
>
> Database System:
> Microsoft Server 2003 Standard
> Microsoft SQL Server 2005
>
> Webserver:
> Debian Sarge
> PHP-Sybase driver
>
Here is a similar work done some times ago. May it helps.
Regards.
-------------- next part --------------
<?php
// $Id $
/**
* @file
* Database interface code for MicrosoftSQL database servers.
*/
/**
* @ingroup database
* @{
*/
/**
* Initialise a database connection.
*
* Note that mysqli does not support persistent connections.
*/
function db_connect($url) {
// Check if MsSQL support is present in PHP
if (!function_exists('mssql_connect')) {
drupal_maintenance_theme();
drupal_set_title('PHP MsSQL support not enabled');
print theme('maintenance_page', '<p>We were unable to use the Ms SQL Server database because the MsSQL extension for PHP is not installed. Check your <code>PHP.ini</code> to see how you can enable it.</p>
<p>For more help, see the <a href="http://drupal.org/node/258">Installation and upgrading handbook</a>. If you are unsure what these terms mean you should probably contact your hosting provider.</p>');
exit;
}
$url = parse_url($url);
// Decode url-encoded information in the db connection string
$url['user'] = urldecode($url['user']);
$url['pass'] = urldecode($url['pass']);
$url['host'] = urldecode($url['host']);
$url['path'] = urldecode($url['path']);
// unusual port
if (isset($url['port'])) {
$url['host'] .= ':'. $url['port'];
}
$track_errors_previous = ini_get('track_errors');
ini_set('track_errors', 1);
// TRUE ...
$connection = @mssql_connect($url['host'], $url['user'], $url['pass'], TRUE);
if (!$connection) {
drupal_maintenance_theme();
drupal_set_title('Unable to connect to database');
print theme('maintenance_page', '<p>This either means that the database information in your <code>settings.php</code> file is incorrect or we can\'t contact the MicrosoftSQL database server. This could mean your hosting provider\'s database server is down.</p>
<p>The MicrosoftSQL error was: '. theme('placeholder', decode_entities($php_errormsg)) .'</p>
<p>Currently, the username is '. theme('placeholder', $url['user']) .', and the database server is '. theme('placeholder', $url['host']) .'.</p>
<ul>
<li>Are you sure you have the correct username and password?</li>
<li>Are you sure that you have typed the correct hostname?</li>
<li>Are you sure that the database server is running?</li>
</ul>
<p>For more help, see the <a href="http://drupal.org/node/258">Installation and upgrading handbook</a>. If you are unsure what these terms mean you should probably contact your hosting provider.</p>');
ini_set('track_errors', $track_errors_previous);
exit;
}
if (!mssql_select_db(substr($url['path'], 1, strlen($url['path'])))) {
drupal_maintenance_theme();
drupal_set_title('Unable to select the database');
print theme('maintenance_page', '<p>This either means that the database information in your <code>settings.php</code> file is incorrect or we can\'t contact the MicrosoftSQL database server. This could mean your hosting provider\'s database server is down.</p>
<p>The Microsoft SQL Server error was: '. theme('placeholder', decode_entities($php_errormsg)) .'</p>
<p>Currently, the database is '. theme('placeholder', substr($url['path'], 1)) .'.</p>
<ul>
<li>Are you sure you have the correct database name?</li>
</ul>
<p>For more help, see the <a href="http://drupal.org/node/258">Installation and upgrading handbook</a>. If you are unsure what these terms mean you should probably contact your hosting provider.</p>');
ini_set('track_errors', $track_errors_previous);
exit;
}
// Restore error tracking setting
ini_set('track_errors', $track_errors_previous);
// allow large texts for mssql
ini_set("mssql.textlimit",16000000);
ini_set("mssql.textsize",16000000);
mssql_query("SET TEXTSIZE 524287;");
return $connection;
}
/**
* Helper function for db_query().
*/
function _db_query($query, $debug = 0) {
global $active_db, $last_result, $queries;
if (variable_get('dev_query', 0)) {
list($usec, $sec) = explode(' ', microtime());
$timer = (float)$usec + (float)$sec;
}
// LENGTH is called DATALENGTH in Micsosoft SQL
if(strpos($query,"LENGTH")){
$query = preg_replace('/(.+)LENGTH(.+)/i', '${1}DATALENGTH$2', $query);
}
$result = mssql_query($query, $db_active);
if (variable_get('dev_query', 0)) {
$bt = debug_backtrace();
$query = $bt[2]['function'] . "\n" . $query;
list($usec, $sec) = explode(' ', microtime());
$stop = (float)$usec + (float)$sec;
$diff = $stop - $timer;
$queries[] = array($query, $diff);
}
if ($debug) {
print '<p>query: '. $query .'<br />error:'. mssql_get_last_message($active_db) .'</p>';
}
$mssql_error = db_error();
if (!$mssql_error) {
return $result;
} else {
triger_error(checkplain("$mssql_error\nquery: $query"), E_USER_WARNING);
return FALSE;
}
}
/**
* Fetch one result row from the previous query as an object.
*
* @param $result
* A database query result resource, as returned from db_query().
* @return
* An object representing the next row of the result. The attributes of this
* object are the table fields selected by the query.
*/
function db_fetch_object($result) {
if ($result) {
return mssql_fetch_object($result);
}
}
/**
* Fetch one result row from the previous query as an array.
*
* @param $result
* A database query result resource, as returned from db_query().
* @return
* An associative array representing the next row of the result. The keys of
* this object are the names of the table fields selected by the query, and
* the values are the field values for this result row.
*/
function db_fetch_array($result) {
if ($result) {
return mssql_fetch_assoc($result); // mssql_fetch_array($result, MSSQL_ASSOC);
}
}
/**
* Determine how many result rows were found by the preceding query.
*
* @param $result
* A database query result resource, as returned from db_query().
* @return
* The number of result rows.
*/
function db_num_rows($result) {
if ($result) {
return mysqli_num_rows($result);
}
}
/**
* Return an individual result field from the previous query.
*
* Only use this function if exactly one field is being selected; otherwise,
* use db_fetch_object() or db_fetch_array().
*
* @param $result
* A database query result resource, as returned from db_query().
* @param $row
* The index of the row whose result is needed.
* @return
* The resulting field.
*/
function db_result($result, $row = 0) {
if ($result && mssql_num_rows($result) > $row) {
return mssql_result($result, $row, 0);
}
}
/**
* Determine whether the previous query caused an error.
*/
function db_error() {
global $active_db;
return mssql_result(mssql_query('SELECT @@ERROR', $db_active), 0, 0); // $mssql_error = msql_fetch_row(mssql_query('SELECT @@ERROR', $db_active)); return $mssql_error[0];
}
/**
* Return a new unique ID in the given sequence.
*
* For compatibility reasons, Drupal does not use auto-numbered fields in its
* database tables. Instead, this function is used to return a new unique ID
* of the type requested. If necessary, a new sequence with the given name
* will be created.
*/
function db_next_id($name) {
$name = db_prefix_tables($name);
_db_query('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRANSACTION;');
$id = db_result(db_query("SELECT id FROM {sequences} WHERE name = '%s'", $name)) + 1;
if (db_num_rows(db_query("SELECT id FROM {sequences} WHERE name = '%s'", $name)) == 0) {
db_query("INSERT INTO {sequences} VALUES ('%s', %d)", $name,1);
$id = 1;
}
else {
db_query("UPDATE {sequences} SET id=%d WHERE name= '%s'", $id, $name);
}
_db_query('COMMIT');
return $id;
}
/**
* Determine the number of rows changed by the preceding query.
*/
function db_affected_rows() {
global $active_db;
if (!function_exists('mssql_rows_affected')) {
return msql_result(mssql_query('SELECT @@ROWCOUNT', $db_active), 0, 0);
} else {
return mssql_rows_affected($db_active); // PHP > 4.0.4
}
}
/**
* Runs a limited-range query in the active database.
*
* Use this as a substitute for db_query() when a subset of the query is to be
* returned.
* User-supplied arguments to the query should be passed in as separate parameters
* so that they can be properly escaped to avoid SQL injection attacks.
*
* Note that if you need to know how many results were returned, you should do
* a SELECT COUNT(*) on the temporary table afterwards. db_num_rows() and
* db_affected_rows() do not give consistent result across different database
* types in this case.
*
* @param $query
* A string containing an SQL query.
* @param ...
* A variable number of arguments which are substituted into the query
* using printf() syntax. The query arguments can be enclosed in one
* array instead.
* Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
* in '') and %%.
*
* NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
* and TRUE values to decimal 1.
*
* @param $from
* The first result row to return.
* @param $count
* The maximum number of result rows to return.
* @return
* A database query result resource, or FALSE if the query was not executed
* correctly.
*/
function db_query_range($query) {
$args = func_get_args();
$count = array_pop($args);
$from = array_pop($args);
array_shift($args);
$query = db_prefix_tables($query);
if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
$args = $args[0];
}
_db_query_callback($args, TRUE);
$query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
$from = (int) $from;
$limit = $from + (int)$count;
if ($from) { // not zero
$limit .= ' COUNT(*) AS hopeitsnotusedalias '; // this stuff is for the record ofset numbering
$restriction = " WHERE hopeitsnotusedalias > $from "; // but we're looking for records from that point
if (strstr($query, 'WHERE ') { // there is WHERE clause
preg_replace('WHERE', $restriction .' AND ', $query);
} elseif (strstr($query, 'GROUP BY ') { // there's no WHERE, but an agregation and maybe a HAVING filter
preg_replace('GROUP BY', $restriction .' GROUP BY', $query);
} elseif (strstr($query, 'ORDER BY ') { // there's neither WHERE nor GROUP BY ... HAVING; but maybe an ORDERing ?
preg_replace('ORDER BY', $restriction .' ORDER BY', $query);
} else { // it was really a simple query :-)
$query .= $restriction;
}
} else { // default
// nop
} // the job is done: we can execute the query now!
$query = preg_replace('/^SELECT/i', 'SELECT TOP '. $limit, $query); // Ms SQL server's TOP function returns the N first records...
return _db_query($query);
}
/**
* Runs a SELECT query and stores its results in a temporary table.
*
* Use this as a substitute for db_query() when the results need to stored
* in a temporary table. Temporary tables exist for the duration of the page
* request.
* User-supplied arguments to the query should be passed in as separate parameters
* so that they can be properly escaped to avoid SQL injection attacks.
*
* Note that if you need to know how many results were returned, you should do
* a SELECT COUNT(*) on the temporary table afterwards. db_num_rows() and
* db_affected_rows() do not give consistent result across different database
* types in this case.
*
* @param $query
* A string containing a normal SELECT SQL query.
* @param ...
* A variable number of arguments which are substituted into the query
* using printf() syntax. The query arguments can be enclosed in one
* array instead.
* Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
* in '') and %%.
*
* NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
* and TRUE values to decimal 1.
*
* @param $table
* The name of the temporary table to select into. This name will not be
* prefixed as there is no risk of collision.
* @return
* A database query result resource, or FALSE if the query was not executed
* correctly.
*/
function db_query_temporary($query) {
$args = func_get_args();
$tablename = "#" . array_pop($args);
array_shift($args);
_db_query_callback($args, TRUE);
$query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
return _db_query($query);
$query = preg_replace('/^SELECT/i', 'SELECT', db_prefix_tables($query));
if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
$args = $args[0];
}
_db_query_callback($args, TRUE);
$query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
$query = preg_replace('/SELECT/i', 'CREATE LOCAL TEMPORARY TABLE '. db_escape_table($tablename) .' AS SELECT ', db_prefix_tables($query));
return _db_query($query);
}
/**
* Returns a properly formatted Binary Large Object value.
*
* We encode binary data with base64 so we can store the
* result in a text field in the MS-SQL DB.
*
* @param $data
* Data to encode.
* @return
* Encoded data.
*/
function db_encode_blob($data) {
return "'".base64_encode($data)."'";
}
/**
* Returns text from a Binary Large OBject value.
*
* @param $data
* Data to decode.
* @return
* Decoded data.
*/
function db_decode_blob($data) {
return base64_decode($data);
}
/**
* Prepare user input for use in a database query, preventing SQL injection attacks.
*/
function db_escape_string($text) {
return get_magic_quote_gpc() ? addslashes($text) : $text; // according to PHP manual, when sql_escape_string is not available for the database, one should use addslashes() or str_replace(). but the escaping may have been done if MagicQuotes are on...
}
/**
* Lock a table.
* This function automatically starts a transaction.
*/
function db_lock_table($table) {
_db_query('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;');
_db_query('BEGIN TRANSACTION;');
}
/**
* Unlock all locked tables.
* This function automatically commits a transaction.
*/
function db_unlock_tables() {
_db_query('COMMIT');
}
/**
* Check if a table exists.
*/
function db_table_exists($table) {
return db_num_rows(db_query("SELECT table_name FROM information_schama.tables WHERE table_name = '{". db_escape_tables($tables) ."}'")); // As far i can remember, SQL Server knows SQL2 meta-data. So i prefer use them and avoid direct quering on 'sysobjets' or 'syscolumns'...
}
/**
* @} End of "ingroup database".
*/
More information about the development
mailing list