T-SQL: Import into ODBC via PHP

t-sql_import.php

<?php
/**
 * Import a MS SQL T-SQL dump file into
 * and ODBC connection via a PHP browser
 * script.
 *
 * I have not tested the script with binary DB fields so
 * I cannot confirm whether it works or not.
 *
 * @author Simon Holywell
 * @version 8/4/2008
 */

//Up the execution limits - you may need to tweak for
//larger database imports I have only tested up to 9MB
//which took 30seconds to process with this file.
ini_set('max_execution_time', 1000);
ini_set('max_input_time', 1000);
ini_set('memory_limit', '120M');

//ODBC connection details
$server_dsn = 'dsn';
$user = 'user';
$pass = 'pass';

//path to T-SQL dump file
$dump_filename = '../dump.sql';

//drop the tables that already
//exist in the DB?
$drop_prexisting = true;

//How many queries should the script
//execute per database connection
//this is in place to stop the DB/ODBC
//connection from timing out on us.
//5 is a safe number if you have large
//database fields
$how_many_per_connection = 5;

function utf16_to_utf8($str) {
    //http://www.moddular.org/log/utf16-to-utf8
    //16th March 2006
    $c0 = ord($str[0]);
    $c1 = ord($str[1]);

    if ($c0 == 0xFE && $c1 == 0xFF) {
        $be = true;
    } else if ($c0 == 0xFF && $c1 == 0xFE) {
        $be = false;
    } else {
        return $str;
    }

    $str = substr($str, 2);
    $len = strlen($str);
    $dec = '';
    for ($i = 0; $i < $len; $i += 2) {
        $c = ($be) ? ord($str[$i]) << 8 | ord($str[$i + 1]) :
                ord($str[$i + 1]) << 8 | ord($str[$i]);
        if ($c >= 0x0001 && $c <= 0x007F) {
            $dec .= chr($c);
        } else if ($c > 0x07FF) {
            $dec .= chr(0xE0 | (($c >> 12) & 0x0F));
            $dec .= chr(0x80 | (($c >>  6) & 0x3F));
            $dec .= chr(0x80 | (($c >>  0) & 0x3F));
        } else {
            $dec .= chr(0xC0 | (($c >>  6) & 0x1F));
            $dec .= chr(0x80 | (($c >>  0) & 0x3F));
        }
    }
    return $dec;
}

function microtime_float() {
    //http://www.developertutorials.com/blog/php/php-measure-max-execution-time-script-execution-time-83/
    //Akash Mehta
    //March 15th, 2008
    list($utime, $time) = explode(" ", microtime());
    return ((float)$utime + (float)$time);
}
?>
<p>Dropping:</p>
<?php
//drop all pre-existing tables in the DB prior to importing
if($drop_prexisting)
    $table_drop_sql = '';
$link2 = odbc_connect($server_dsn, $user, $pass) or die('Unable to connect to the server: '.odbc_errormsg());
$tables = odbc_tables($link2);
while (odbc_fetch_row($tables)){
    if(odbc_result($tables,"TABLE_TYPE")=="TABLE") {
        $table_name = odbc_result($tables,"TABLE_NAME");
        print $table_name.'<br />';
        if($drop_prexisting)
            $table_drop_sql .= "DROP TABLE $table_name\n";
    }
}
odbc_close($link2);

if($drop_prexisting) {
    $link2 = odbc_connect($server_dsn, $user, $pass) or die('Unable to connect to the server: '.odbc_errormsg());
    @odbc_exec($link2, $table_drop_sql);
    odbc_close($link2);
}
?>
<p>Have patience - converting the DB file to UTF8 and removing guff&hellip;</p>
<?php
$script_start = microtime_float();

$file = file_get_contents($dump_filename);
//convert from utf16 to utf8 character encoding
$file = utf16_to_utf8($file);
//strip out errant MS line endings
$file = str_replace(array("\r","\r\n","\r\n","\n\n"), "\n", $file);
//strip more guff that ODBC/MS SQL cannot understand
//(the dbo object doesn't exist via ODBC)
$file = preg_replace("/^IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[[a-z_]+]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)$/im",'',$file);
//strip out print statements
$file = preg_replace("/^print '[a-z0-9 ]+'$/im", '', $file);
//strip SQL Server comments
$file = preg_replace('/^/[*]{6} [a-z0-9[] /:._]+ [*]{6}/$/im', '', $file);
//strip out SQL Server rubbish and any errant dbo refs
$file = str_replace(array("GO\n",'[dbo].','dbo.'), '', $file);

//lets split up the queries by stepping through the file and breaking on insert statements
$search_string = "\nINSERT [";
$file_length = strlen($file);
$iteration = 0;
$total_interations = 0;
$total_exe_time = bcsub(microtime_float(), $script_start, 4);
$queries = array();
while(true) {
    $script_start = microtime_float();
    $end_yank = strpos($file, $search_string, strlen($search_string));
    //store the query(s)
    $queries[] = substr($file, 0, $end_yank);
    //scrub the saved query(s) from the main T-SQL
    $file = substr($file, $end_yank);

    if(empty($file) or
       strlen($file) <= strlen($search_string) or
       $end_yank == 0)
        break;

    if(1 == (++$iteration / $how_many_per_connection)) {
        //process our accumulated queries
        $link = odbc_connect($server_dsn, $user, $pass) or die('Unable to connect to the server: '.odbc_errormsg());
        odbc_exec($link, implode("n", $queries)) or die('Could not process SQL: '.odbc_errormsg($link));
        odbc_close($link);
        $queries = array();
        $total_interations += $iteration;
        $script_end = microtime_float();
        $portion_time = bcsub($script_end, $script_start, 4);
        $total_exe_time += $portion_time;
        print '<p>'.$total_interations.' records processed.  Portion: '.$portion_time.' Total: '.$total_exe_time.'</p>';
        $iteration = 0;
    }
}
?>
<p>Successfully Imported:</p>
<?php
$link2 = odbc_connect($server_dsn, $user, $pass) or die('Unable to connect to the server: '.odbc_errormsg());
$tables = odbc_tables($link2);
while (odbc_fetch_row($tables)){
    if(odbc_result($tables,"TABLE_TYPE")=="TABLE")
        echo"<br>".odbc_result($tables,"TABLE_NAME");
}
odbc_close($link);
?>