Re: [sqlite] Can you perform an ATTACH-type operation just using DB handles ?
John Belli <[EMAIL PROTECTED]> writes:
> GIVEN: db1 is the open handle of the first database, and db2 is the
> open handle of the second database
>
> WANTED: attach database open as db1 to db2 to do an atomic update
> * * * WARNING: untested code ahead! * * *
> char **table;
> sqlite3_get_table(db1, "PRAGMA database_list", , NULL, NULL,
> NULL);
> char *tmp = sqlite3_mprintf("ATTACH %q AS a", table[5]);
> sqlite3_free_table(table);
> sqlite3_exec(db2, tmp, NULL, NULL, NULL);
> sqlite3_free(tmp);
>...do your stuff here...
> sqlite3_exec(db2, "DETACH a", NULL, NULL, NULL);
>
> There should be a bunch of error checking in there. This is left as an
> exercise for the reader.
>
> This will not work if db1 is a memory db. In that case, you would have
> to do it the other way around. If both are memory dbs, then you're out
> of luck.
>
> JAB
Thanks - but it looks implementation-dependent, so better not use
in production code since it would be 'fragile' wrt SQLite changes.
Will accept the small risk of loss of failure due to power-off
between separate COMMITs
BEGIN; ... UPDATE db1
BEGIN; ... UPDATE db2
... all well so far ...
COMMIT db1;
... still OK ... hope no power fail here !
COMMIT db2;
... made it ...
Regards,
MikeW
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can you perform an ATTACH-type operation just using DB handles ?
On Fri, 17 Oct 2008 07:53:52 + (UTC), MikeW
<[EMAIL PROTECTED]> wrote:
>John Belli <[EMAIL PROTECTED]> writes:
>>
>> On Thu, 16 Oct 2008 17:54:52 + (UTC), MikeW
>> <[EMAIL PROTECTED]> wrote:
>>
>> >My code has two pre-opened DB connection handles, and would like to
>> >perform an SQL operation over the two databases.
>> >The DB filenames are not available at this point, hence an ATTACH is
>> >not feasible.
>> >Is there any way to achieve this by applying an ATTACH at the handle level ?
>>
>> I don't think so, but check into "PRAGMA database_list" (See
>> http://www.sqlite.org/pragma.html ).
>>
>> JAB
>
>Thanks, that might be useful in some situations but not in this one.
>
>The use case is a very specific one in which a database may contain one-off
>overrides for a second, normally independent (but already opened) database.
>I can obviously implement this by reading rows from the first one
>and updating the second, but if I could briefly refer to both DBs at
>the same time, it would be just one 'atomic' SQL statement -
>much simpler and more robust.
>
>Still, it seems surprising that there isn't a (SQLite-specific) handle-level
>operation to do this attach, maybe I should put in a feature request.
>(Or even have a look in the SQLite code to see if it's an easy mod ...)
GIVEN: db1 is the open handle of the first database, and db2 is the
open handle of the second database
WANTED: attach database open as db1 to db2 to do an atomic update
* * * WARNING: untested code ahead! * * *
char **table;
sqlite3_get_table(db1, "PRAGMA database_list", , NULL, NULL,
NULL);
char *tmp = sqlite3_mprintf("ATTACH %q AS a", table[5]);
sqlite3_free_table(table);
sqlite3_exec(db2, tmp, NULL, NULL, NULL);
sqlite3_free(tmp);
...do your stuff here...
sqlite3_exec(db2, "DETACH a", NULL, NULL, NULL);
There should be a bunch of error checking in there. This is left as an
exercise for the reader.
This will not work if db1 is a memory db. In that case, you would have
to do it the other way around. If both are memory dbs, then you're out
of luck.
JAB
--
John A. Belli
Software Engineer
Refrigerated Transport Electronics, Inc.
http://www.rtelectronics.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can you perform an ATTACH-type operation just using DB handles ?
John Belli <[EMAIL PROTECTED]> writes: > > On Thu, 16 Oct 2008 17:54:52 + (UTC), MikeW > <[EMAIL PROTECTED]> wrote: > > >My code has two pre-opened DB connection handles, and would like to > >perform an SQL operation over the two databases. > >The DB filenames are not available at this point, hence an ATTACH is > >not feasible. > >Is there any way to achieve this by applying an ATTACH at the handle level ? > > I don't think so, but check into "PRAGMA database_list" (See > http://www.sqlite.org/pragma.html ). > > JAB Thanks, that might be useful in some situations but not in this one. The use case is a very specific one in which a database may contain one-off overrides for a second, normally independent (but already opened) database. I can obviously implement this by reading rows from the first one and updating the second, but if I could briefly refer to both DBs at the same time, it would be just one 'atomic' SQL statement - much simpler and more robust. Still, it seems surprising that there isn't a (SQLite-specific) handle-level operation to do this attach, maybe I should put in a feature request. (Or even have a look in the SQLite code to see if it's an easy mod ...) MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can you perform an ATTACH-type operation just using DB handles ?
On Thu, 16 Oct 2008 17:54:52 + (UTC), MikeW <[EMAIL PROTECTED]> wrote: >My code has two pre-opened DB connection handles, and would like to >perform an SQL operation over the two databases. >The DB filenames are not available at this point, hence an ATTACH is >not feasible. >Is there any way to achieve this by applying an ATTACH at the handle level ? I don't think so, but check into "PRAGMA database_list" (See http://www.sqlite.org/pragma.html ). JAB -- John A. Belli Software Engineer Refrigerated Transport Electronics, Inc. http://www.rtelectronics.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
