Here is the actual code I use to pull the data from the ODBC database to my mysql database. Unfortunately, it is in perl but perhaps you see what I need to do in order to make the conversion from HEX to BINARY.
Thanks!
#!/usr/bin/perl
set up environment, include modules
use DBI;
$ENV{'SYBASE'} = '/usr/';
#create first database handle: the remote MSSQL DB
$dbh = DBI->connect('dbi:Sybase:server=xxx', 'xxx', 'xxx') or die 'connect';
$dbh->do("use xxx");
#create second database hanlde: the local MySQL DB
$database="xxx";
$user="xxx";
$password="xxx";
$dbh2 = DBI->connect("DBI:mysql:$database", $user, $password)
|| die 'connect2';
start the query
$sth = $dbh->prepare('select * from xxx') or die 'prepare';
$sth->execute or die 'execute';
####
#while (@ = $sth->fetchrow_array) {
print "$data[0] $data[1] $data[2] $data[3] $data[4] $data[5] $data[6] $data[7] $data[8] $data[9] $data[10] $data[11] $data[12] $data[13] $data[14] $data[15] $data[16] $data[17] $data[18] $data[19] $data[20] $data[21] $data[22] $data[23] $data[24] $data[25] $data[26] $data[27] $data[28] $data[29] $data[30] $data[31] $data[32] $data[33] $data[34] $data[35] $data[36] $data[37] $data[38] $data[39] $data[40] $data[41] $data[42] $data[43] $data[44] $data[45] $data[46] $data[47] $data[48] $data[49] $data[50] $data[51] $data[52] $data[53]\n"; # the first few fields
#}
###
$sth2 = $dbh2->prepare(
"INSERT into residential
(Id, bath_level, bath3_4_level, baths, baths3_4,
bd1_size, bd2_size, bd3_size, bd4_size, bedrooms,
book, color, den_size, dr_size, elem_school,
flood_zone, found_size, fr_size, frontage, glaag,
glaaga, glaagb, high_school, kt_size, lav_level,
lavs, list_price, lister_agent_name,
lister_office_name, lot, lot_size_acres, lr_size,
map, midl_school, nof_cars, ot_size, page,
parking, remark1, rooms, school_district, seasonal,
status, surveyed, tax_years, taxes, town,
water_body, water_frontage, year_built, mls_zone,
mod_date_time, p_mod_date_time, features, medium_photo)
VALUES
(? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,?)");
while (@ = $sth->fetchrow_array){
print "\t inserting ID:" . $data[0] . "\n";
$rv2 =
$sth2->execute($data[0], $data[1], $data[2], $data[3],
$data[4], $data[5], $data[6], $data[7], $data[8],
$data[9], $data[10], $data[11], $data[12], $data[13],
$data[14], $data[15], $data[16], $data[17], $data[18],
$data[19], $data[20], $data[21], $data[22], $data[23],
$data[24], $data[25], $data[26], $data[27],
$data[28], $data[29], $data[30], $data[31], $data[32],
$data[33], $data[34], $data[35], $data[36], $data[37],
$data[38], $data[39], $data[40], $data[41], $data[42],
$data[43], $data[44], $data[45], $data[46], $data[47],
$data[48], $data[49], $data[50], $data[51], $data[52],
$data[53], $data[54]);
print $sth2->errstr if $sth2->err;
print qq{Affected rows: $rv2} unless $sth2->err;
}
$sth->finish;
$dbh2->disconnect;
$dbh->disconnect;
