Re: [sqlite] Single row insert failure
Eric Bohlman wrote: Which can't be the actual code you're using. Please copy and paste your actual code rather than retyping it. They get a little AR around here when I show actual names of things. It was copied buy I must have lost track of whre is was at while obfuscating it. ... Bloody Hell. I just ran it again this AM and it worked! I did it several times and different ways yesterday and kept getting the same results. Today with selection criteria for one row (tuple) I get one stored in the SQLite db. The server it is running on was pretty heavily loaded yesterday. Could have been some Windows file-system issues. And of course as soon as I exposed my problem to the world it goes away. Mr. Murphy has it right! Thanks for taking the time to look it over (and getting me to take a clue-stick to my own head. ;-) Rod -- --- [This E-mail scanned for viruses by Declude Virus]
Re: [sqlite] Single row insert failure
Roderick A. Anderson wrote:
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.
[sqlite] Single row insert failure
New subscriber butI did check the archives first and did not find
reference to this (or used the totally wrong search terms). There was
one thread to something similar but it didn't pan out.
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.
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" );
$sth1 = $dbh1->prepare(
"INSERT INTO table1 " .
"( field1, field2, field3, field4 ) " .
" VALUES ( ?, ?, ?, ? )" );
$sth2 = $dbh2->prepare( "SELECT field1, field2, field3, field4 FROM
table1 WHERE field1 = 'XXX'" );
$sth2->execute();
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 );
}
$dbh1->disconnect;
$dbh2->disconnect;
After running this I looked at the reports.db with notepad, less, and
strings. No data, just the table definition.
Is there something I'm missing? The concern is there could be just a
single row in the MS SQL Server database.
TIA,
Rod
--
---
[This E-mail scanned for viruses by Declude Virus]
