Hi,
From: "Rob Dixon" <[EMAIL PROTECTED]>
Since this is a Perl forum it is helpful to use the /full syntax/ of any
SQL
that your Perl is using. (I assume you actually have some Perl behind all
this?) And in particular you should avoid abbreviating non-standard SQL,
so
that at least we have s chance of looking it up.
I have tried:
use strict;
use DBI;
my $dbh = DBI->connect("dbi:SQLite:test.db");
$dbh->do("create table test(
id integer primary key autoincrement,
text text unique
)");
my $sth = $dbh->prepare("insert into test values(null, ?)");
$sth->execute("a");
If I execute the last line for more times it gives an error (normally)
saying that "text" column is not unique.
Also this sentence
The problem is that the primary key field which has autoincrement option
skips the ID of each record which is not inserted because it is already
in
the database, and I don't want that.
is long and badly expressed.
I'll try again:
For not giving that error, I have tried using "insert or ignore".
Before executing this, the content of the table "test" was:
| ID | TEXT |
| 1 | a |
my $sth = $dbh->prepare("insert or ignore into test values(null, ?)");
$sth->execute("a");
After doing this, it works ok because it doesn't give errors, but the next
record won't have the id=2 as it should, but it will have the ID=3 or even
more if I execute the line above for more times.
(And this is what I don't want).
So what I think you're saying is that you have an SQLite database table
whihc
has a field with the attribute
INTEGER PRIMARY KEY AUTOINCREMENT
(By the way, I think the autoincrement is misleading, if this answer is
correct.)
Why autoincrement is misleading?
and you have found that
INSERT ON CONFLICT IGNORE
works quickly enough, but some records with duplicate keys are left
unchanged
when you want to replace them.
insert on conflict ignore doesn't work in SQLite.
insert or ignore is used instead.
The short answer to that is that I think you want
INSERT ON CONFLICT REPLACE
and everything will be fine. But I am still unsure of your question, so
please
come back and correct me.
insert on conflict replace doesn't work either.
But insert or replace works.
However, this also create a bad effect, because if the ID for the text "a"
is 1, if I do an
insert or replace into test values(null, "a");
it will replace everything, and it will set the ID for that record to 2 and
it will keep increasing if I execute the command above for more times.
I found that if I use RaiseError => 0 and PrintError => 0 when establishing
the database connection, the program doesn't print the error if I enclose
the SQL statements in eval { ... };
However, sometimes, not always, it appears an error window with a bad error
of the perl interpreter, so something's wrong and I prefer not using eval{}.
SQLite is pretty limited, so I decided to test every time if the current
record is already in the database or not before trying to insert it. Better
slower and safe...
Octavian
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
