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
[sqlite] Can you perform an ATTACH-type operation just using DB handles ?
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 ? Regards, MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
