[freetds] Please help with my script

Stuart Coupe stuart.coupe at easysoft.com
Fri May 23 12:01:27 EDT 2003

Try removing the "while (...) { print .... } loop."
This is fetching to the end of the results set, so when you 
come to the insert part there are no remaining rows in
your results set.


On Fri, 2003-05-23 at 16:26, Scott Antonivich wrote:
> Hello,
> I am trying (unsuccessfully) to extract data from a mssql7 database to a
> mysql database. The database is over 10,000 records and each record contains
> a OLE .jpg blob.
> Basically, I am having problems importing the data. My script is below...
> #!/usr/bin/perl
>  use DBI;
>  $ENV{'SYBASE'} = '/usr/';
>  $dbh = DBI->connect('dbi:Sybase:server=XXX', 'XXX', 'XXX')
>       or die 'connect';
> $dbh->do("use mlsnhsql");
> $sth = $dbh->prepare('select * from idxmls1') or die 'prepare';
> $sth->execute or die 'execute';
> while (@data = $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] $data[54]\n"; # the first few fields
> }
> $database="XXX";
> $user="XXX";
> $password="XXX";
> $dbh2 = DBI->connect("DBI:mysql:$database", $user, $password);
> while (@data = $sth->fetchrow_array){
> 	$statement = "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 ($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])";
> 	$sth2 = $dbh2->prepare($statement)
> 	    or die "Can't prepare $statement: $dbh->errstr\n";
> 	$sth2->execute || die 'execute2';
> }
> $sth->finish;
> $dbh->disconnect;
> When I execute the script...it *seems* to be working....however, no data
> gets imported and no error messages. Any ideas on what I could be doing
> wrong? Anything you see that can be done to improove this script?
> Thanks
> Scott
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

Easysoft Limited:     web: http://www.easysoft.com     tel: +44 (0)1937 860000     fax: +44 (0)1937 860001

This email from Easysoft Limited is intended only for the personal use of the recipient(s) named above.  The email and any files transmitted with it are confidential to the intended recipient(s) and may be legally privileged or contain proprietary and private information.  If you are not an intended recipient, you may not review, copy or distribute this message.  If received in error, please notify the sender and delete the message from your system.

Any views or opinions expressed in this email and any files transmitted with it are those of the author only and may not necessarily reflect the views of Easysoft and do not create any legally binding rights or obligations whatsoever. Unless otherwise pre-agreed by exchange of hard copy documents signed by duly authorised representatives, contracts may not be concluded on behalf of Easysoft by email.

Please note that neither Easysoft nor the sender accepts any responsibility for any viruses and it is your responsibility to scan the email and the attachments (if any).  All email received and sent by Easysoft may be monitored to protect the business interests of Easysoft.

More information about the FreeTDS mailing list