Linux + perl access to MS SQL Server

Source: Internet
Author: User
Tags create index odbc mssql mssql server prepare create database perl script

Perl typically uses DBI to access the database, so after the system has FreeTDS installed, you can use DBI to access the MSSQL database via FreeTDS, for example:

The code is as follows Copy Code

Using DBI;
My $cs = "Driver={freetds}; Server= host; port=1433;database= database; Uid=sa; pwd= password; tds_version=7.1;charset=gb2312 ";
My $dbh = Dbi->connect ("DBI:ODBC: $cs") or Die $@;


Because I do not use Windows, in order to study QQ group database, need to migrate data from MSSQL to MySQL, specially for QQ Group database installed a Windows Server 2008 and SQL Server 2008r2, but in a few days the assessment expires, The study of MySQL's Workbench has the ability to migrate data from MS SQL Server, but it is too cumbersome for the QQ group to have such huge data and the data for the sub-database, so write a generic Perl script to migrate the database from MSSQL to MySQL. Combined with bash, it's convenient to transfer hundreds of tables from more than 20 libraries to the following Perl code:

The code is as follows Copy Code

#!/usr/bin/perl
Use strict;
Use warnings;
Use DBI;


Die "Usage:qq dbn" if @ARGV!= 1;
My $db = $ARGV [0];

Print "Connectin to databases $db ... n";
My $cs = "Driver={freetds}; Server=mssql server; Port=1433;database= $db; Uid=sa; Pwd=mssql password; tds_version=7.1;charset=gb2312 ";

Sub Db_connect
{
My $src = Dbi->connect ("DBI:ODBC: $cs") or Die $@;
My $target = Dbi->connect ("Dbi:mysql:host=mysql server", "MySQL User name", "MySQL password") or Die $@;
Return ($SRC, $target);
}
My ($src, $target) = Db_connect;

Print "Reading table SCHEMAS....N";

My $q _tables = $src->prepare ("Select name from sysobjects WHERE xtype = ' U ' and name!= ' dtproperties ';"); #获取所有表名
My $q _key_usage = $src->prepare ("Select table_name, COLUMN_NAME from INFORMATION_SCHEMA. Key_column_usage; "); #获取表的主键
$q _tables->execute;
My @tables = ();
My%keys = ();
Push @tables, @_ while @_ = $q _tables->fetchrow_array;

$q _tables->finish;

$q _key_usage->execute ();
$keys {$_[0]} = $_[1] While @_ = $q _key_usage->fetchrow_array;
$q _key_usage->finish;


#获取表的索引信息
My $q _index = $src->prepare (QQ (
    SELECT t.name, C.name
  & nbsp From Sys.index_columns I
    INNER JOIN sys.tables T on t.object_id = i.object_id
    ; INNER JOIN sys.columns C on c.column_id = i.column_id and i.object_id = c.object_id;
));
$q _index->execute;
My%table_indices = ();
while (my @row = $q _index->fetchrow_array)
{
    my ($table, $column) = @row;
 &nbs p;  my $columns = $table _indices{$table};
    $columns = $table _indices{$table} = [] if not $columns;
    push @ $columns, $column;
}
$q _index->finish;

#在目标MySQL上创建对应的数据库
$target->do ("drop database IF EXISTS ' $db") or die "Cannot drop old DATABASE $dbn";
$target->do ("CREATE DATABASE" $db ' DEFAULT CHARSET = UTF8 COLLATE utf8_general_ci; ") or die" Cannot CREATE database $db n ";
$target->disconnect;
$SRC->disconnect;


My $total _start = time;
For my $table (@tables)
{
my $pid = fork;
Unless ($pid)
{
($SRC, $target) = Db_connect;
my $start = time;
$SRC->do ("Use $db;");
#获取表结构, to generate the DDL used for MySQL
My $q _schema = $src->prepare ("Select column_name, Is_nullable, Data_type, character_maximum_length from Information_ SCHEMA. COLUMNS WHERE table_name =? Order by ordinal_position; ");
$target->do ("Use ' $db ';");
$target->do ("SET NAMES utf8;");
My $key _column = $keys {$table};
My $ddl = "CREATE TABLE ' $table ' (n";
$q _schema->execute ($table);
My @fields = ();
while (my @row = $q _schema->fetchrow_array)
{
My ($column, $nullable, $datatype, $length) = @row;
My $field = "' $column ' $datatype";
$field. = "($length)" If $length;
$field. = "PRIMARY KEY" if $key _column eq $column;
Push @fields, $field;
}
$ddl. = Join (", n", @fields);
$ddl. = "N) ENGINE = Myisam;nn";
$target->do ($DDL) or die "Cannot create table $tablen";
#创建索引
My $indices = $table _indices{$table};
if ($indices)
{
for (@ $indices)
{
$target->do ("CREATE index ' $_ ' on ' $table ' (' $_ '); n") or die "Cannot create index on $db. $table $.$_n";
}
}
#转移数据
My @placeholders = map {'? '} @fields;
My $insert _sql = "Insert delayed into $table VALUES (". Join ', ', @placeholders). "); n";
My $insert = $target->prepare ($insert _sql);
My $select = $src->prepare ("select * from $table;");
$select->execute;
$select->{' longreadlen '} = 1000;
$select->{' longtruncok '} = 1;
$target->do ("SET autocommit = 0;");
$target->do ("START TRANSACTION;");
my $rows = 0;
while (my @row = $select->fetchrow_array)
{
$insert->execute (@row);
$rows + +;
}
$target->do ("COMMIT;");
#结束, output task information
My $elapsed = time-$start;
Print "Child process $$ a for table $db. $table done, $rows Records, $elapsed SECONDS.N";
Exit (0);
}
}
Print "Waiting for child processesn";
#等待所有子进程结束
while (Wait ()!=-1) {}
My $total _elapsed = time-$total _start;
Print "All tasks from $db finished, $total _elapsed SECONDS.N";


This script fork a subprocess and a corresponding database connection based on each table, so make sure that the maximum number of connections that the target MySQL database configures can withstand before doing this migration.
And then execute it under bash.

The code is as follows Copy Code

For x in {1..11};d O./qq.pl quninfo$x; Done
For x in {1..11};d O./qq.pl groupdata$x; Done

Instead, the script creates the same structure and configures the index on the MySQL side based on the table structure of MSSQL.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.