Linux + perl access to MS SQL Server

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.