Update: Implemented Jeffas suggestion ;)

Due to licensing costs (etc) for interfacing to SQLServer on anything other than a M$ platform - we decided moving to MySQL was the way to go. I could use the data exporter for any tables other than those that had text or blob style columns.

Consequently here is a program that handles each data pull/insert on a table by table basis. You need to update the SQL but that should be ok.

Note:
1) This is a Windows only app.
2) As I was dealing with BLOBS I had to restart the MySQL server with set-variable=max_allowed_packet=16M to stop it bombing on the larger sets of data.
3) I have already created the tables on the MySQL server. 
# Port from SQL Server to MySQL
use strict;
use warnings 'all';
use DBI;
use DBD::mysql;

use Win32::OLE;
use constant adOpenKeySet => 1;

my $db_datasource = 'driver={SQL Server};Server=HOST; database=DB;uid=
+UID;pwd=PWD;';
my $db_connection = new Win32::OLE('ADODB.Connection');
$db_connection->Open($db_datasource);

my $dsn = "DBI:mysql:database=DB;host=HOSTNAME";
my $my_sql = DBI->connect($dsn, 'me', 'julie', {RaiseError=>1});

# notice use of RaiseError - goodbye DBI::errstr!
my $table  = 'OTHER_TABLE';
my @field  = qw(ID description file_data);

my $rs = new Win32::OLE("ADODB.Recordset");
$rs->Open("SELECT * FROM $table", $db_connection, adOpenKeySet);

# Prepare our insert
my $insert_sql = "INSERT INTO $table ("
   . join(',',@field)
   . ') VALUES ('
   . join(',',map '?',@field) . ')'
;
my $sth = $my_sql->prepare($insert_sql);

while (!$rs->EOF) 
{
    $sth->execute( map $rs->{$_}{Value}, @field );
    $rs->MoveNext();
}

$rs->close();
$db_connection->close();

$my_sql->disconnect();

[download]
This should be good enough as a basis for any port tool someone else may need to write.

Replies are listed 'Best First'.
Re: Porting from SQL Server to MySQL
by jeffa (Bishop) on Aug 11, 2003 at 15:53 UTC
    Thanks for posting this, it is quite helpful. However, it could be made more easily modifiable by abstracting out the table name and its field names. The following is an untested and incomplete "re-write" of your script. This should help create a stronger basis for this tool. ;) 
    # notice use of RaiseError - goodbye DBI::errstr!
my $my_sql = DBI->connect($dsn, 'user', '1234', {RaiseError=>1});

# replace these to fit your needs - or better, use a Getopt:: module!
my $table = 'OTHER_TABLE';
my @field = qw(ID description file_data);

$rs->Open("SELECT * FROM $table", $db_connection, adOpenKeySet);

# Prepare our insert
my $insert_sql = "INSERT INTO $TABLE ("
   . join(',',@field)
   . ') VALUES ('
   . join(',',map '?',@field) . ')'
;
my $sth = $my_sql->prepare($insert_sql);

while (!$rs->EOF)
{
    $sth->execute( map $rs->{$_}{Value}, @field );
    $rs->MoveNext();
}

    [download]
    Hope this helps. :)

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
[d/l]
Re: Porting from SQL Server to MySQL
by Anonymous Monk on Aug 11, 2003 at 14:36 UTC
[reply]
      Initial impressions are that this would not work in my problem domain as I had originally tried DBI::ODBC for accessing the SQL Server and was getting truncation errors on selecting the data (ie before even hitting MySQL).

      The solution I have provided uses ADO natively and so avoids this problem. Additionally, I can just about guarantee that the migrate module will not handle Memo fields correctly either (though of course I could be wrong ;) ).

      Your migrate tool looks good but I don't think it is ready for the whole porting from ADO land to MySQL land that I needed.

      Thanks all the same.
[reply]

Back to Cool Uses for Perl