This looks like an issue we ran into when connecting with MS-SQL 7. Try
using two different database handles, one handles the query and the
other one to perform the inserts.
You also were missing a semi-colon after your print, but I think that
was probably an error in copying the code to the email.
I would also advise you to use DBD::ODBC instead of Win32::ODBC. Your
code will then look like:
use strict;
use DBI;
my $query_dbh = DBI->connect('dbi:ODBC:database','name','pass');
my $insert_dbh = DBI->connect('dbi:ODBC:database','name','pass');
my $sth = $query_dbh->prepare("SELECT DATE1 FROM MyTABLE");
$sth->execute();
while( my(temp) = $sth->fetchrow_array()){
print "$temp\n"; # So I can see what happens
$insert_dbh->do("INSERT INTO MyTABLE (DATE2) VALUES
(".$insert_dbh->quote($temp).")");
}
$sth->finish();
$query_dbh->disconnect();
$insert_dbh->disconnect();
exit 0;
Of course this should really be done with just one SQL statement:
UPDATE MyTable SET DATE2 = DATE1
- Johnathan
Mason, Andrew wrote:
>I have a table in a database which contains a date field. Being
>technically inept this filed was specified as of type Char. I would now
>like correct my error. My idea was to read in the date for each line of
>the table, then insert this value back into a new field.
>
>My code...
>
>#! d:/perl/bin/perl.exe
>use Win32::ODBC;
>$db = new Win32::ODBC("MyDSN");
>$db->Sql("SELECT DATE1 FROM MyTABLE");
>while ($db->FetchRow())
> {
> $temp = $db->Data;
> print "$temp\n" # So I can see what happens
> $db->Sql("INSERT INTO MyTABLE (DATE2) VALUES ('$temp')");
> }
>$db->Close();
>exit 0;
>
>### End ###
>
>When I run this it prints the Date from the first line and then stops.
>Nothing has been entered into the table.
>I realise now that I was being very naive. Can anybody suggest how I
>should do this?
>
>TIA,
>
>&rw
>
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
