So here is the issue. Inserting a single row into a SQLite2 database doesn't work using perl on a Windows system.
I have a data in a MS SQL Server database I need to process on a Linux box (and getting ODBC to work isn't an option at this time) so my cheat is to pull the data and put it in the SQLite db mount the share and access the data.
This was part of a new process so I wanted to test it using a single row. I'm sure I have something missing here but it works when I run this and inserting multiple rows.
Here is the code.
Which can't be the actual code you're using. Please copy and paste your actual code rather than retyping it.
use DBI;
my $dbh1 = DBI->connect( "dbi:ODBC:my.dsn", "user", "password" ); my $dbh2 = DBI->connect( "dbi:SQLite2:dbname=reports.db", "", "" );
my $sth1; my $sth2;
$reportsdb->do( "DELETE FROM table1" );
Nowhere have you defined $reportsdb; this shouldn't run.
$sth1 = $dbh1->prepare( "INSERT INTO table1 " . " ( field1, field2, field3, field4 ) " . " VALUES ( ?, ?, ?, ? )" );
You're preparing an insert statement for the ODBC database, not the SQLite database.
$sth2 = $dbh2->prepare( "SELECT field1, field2, field3, field4 FROM table1 WHERE field1 = 'XXX'" );
$sth2->execute();
And you're attempting to fetch records from the SQLite database, which you (unsuccessfully) tried to empty earlier.
while ( my @report = $sth2->fetchrow_array() ) {
# Just to see what is being selected print join(' : ', @report) . "\n"; $sth1->execute( $report[0], $report[1], $report[2], $report[3] ); # Changed this as part of the testing. Works for mutiple rows # $sth1->execute( @report );
What do you mean by "multiple rows" here?
}
$dbh1->disconnect; $dbh2->disconnect;
After running this I looked at the reports.db with notepad, less, and strings. No data, just the table definition.
Of course, since you never executed an insert statement on it.
Is there something I'm missing? The concern is there could be just a single row in the MS SQL Server database.
I think you've misfocused and come up with a red herring; the problem doesn't appear to have anything to do with the number of rows fetched.
