Actually, I'm using the Tcl interface, so I'm using whatever the Tcl
interface uses.
I've now verified that within the transaction I can take a snapshot, put
it back using the new method, and retrieve a new snapshot, and that these
snapshots are identical. But when I close the transaction using the new
method I get this foreign key error.
More information:
* I'm running sqlite3 3.7.7.1, but I've tried it with 3.7.14 and gotten
the same results.
* I'm running on MS Windows.
With the old code (the code that runs without FK errors), here's the
routine that does the inserts. Note that when the values were extracted
from the database, they were retrieved using the SQL "quote()" function,
so that they are well-formed string literals. The "values" argument
contains a flat list of column values, as returned by the [$db eval {...}]
command.
proc InsertGrabValues {db table ncols values} {
# FIRST, build the query.
while {[llength $values] > 0} {
set row [lrange $values 0 $ncols-1]
set values [lrange $values $ncols end]
set sql "INSERT INTO $table VALUES([join $row ,]);"
$db eval $sql
}
}
The new code is more complicated. It creates a Tcl array, b(), and
assigns column values to b(0), b(1), etc. In this case, the values are
retrieved from the database *without* the SQL "quote()" function.
proc InsertGrabValues {db table ncols values} {
# FIRST, create the query string.
set vars [list]
for {set i 0} {$i < $ncols} {incr i} {
lappend vars "\$b($i)"
}
set sql "INSERT INTO $table VALUES([join $vars ,]);"
set len [llength $values]
set i 0
foreach val $values {
set b($i) $val
incr i
if {$i == $ncols} {
set i 0
$db eval $sql
}
}
}
A snapshot consists of multiple tables and their values lists; the code
that calls InsertGrabValues opens a transaction, deletes the rows from
each listed table, then inserts rows into each table using
InsertGrabValues, and then commits the changes.
Will
--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."
On 10/1/12 2:05 PM, "Black, Michael (IS)" <michael.bla...@ngc.com> wrote:
>You don't show any code but it sounds like you're using volatile
>variables.
>
>Are you using SQLITE_STATIC instead of SQLITE_TRANSIENT?
>http://www.sqlite.org/c3ref/bind_blob.html
>
>
>Michael D. Black
>Senior Scientist
>Advanced Analytics Directorate
>Advanced GEOINT Solutions Operating Unit
>Northrop Grumman Information Systems
>
>________________________________________
>From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
>on behalf of Duquette, William H (318K) [william.h.duque...@jpl.nasa.gov]
>Sent: Monday, October 01, 2012 3:32 PM
>To: Discussion of SQLite Database
>Subject: EXT :[sqlite] Problem with Foreign Key constraints
>
>Howdy!
>
>I have some code that does the following:
>
>1. Takes a snapshot of some number of database tables, e.g., saves the
>data from those tables as a text string.
>2. Later, clears the tables and restores their content from the snapshot.
>
>The snapshot is restored by creating a new INSERT statement for each row,
>with the literal column values in it, and evaluating each of these
>statements in sequence.
>
>The tables contain foreign key constraints with "DEFERRABLE INITIALLY
>DEFERRED" specified; thus, I execute all of these INSERTs within a
>transaction so that I won't get spurious constraint failures.
>
>This has been working, but it's slow, so I'm trying to rework the
>algorithm to use queries with variable references. Then I update the
>variables once for each row, and call the same query over and over again.
> This is much faster...but at the end of the transaction I'm getting a
>foreign key constraint failure. So far as I can tell, all the data is as
>it should be; and the only difference, so far as I can tell, is that I'm
>now using variables rather than literals.
>
>Any ideas?
>
>Will
>
>
>--
>Will Duquette -- william.h.duque...@jpl.nasa.gov
>Athena Development Lead -- Jet Propulsion Laboratory
>"It's amazing what you can do with the right tools."
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
