On Thu, 22 Apr 2004, Lloyd thomas wrote:
>Thanks Christian,
> Although that makes sense I can find nowhere on the
>net which explains how to dump the data within a transaction.
>Do you have an example.
It's just like any other select, except bounded by begin and commit
statements, using seperate sqlite_exec calls before and after the dump:
/* Begin transaction, for data snapshot */
sqlite_exec( db, "BEGIN TRANSACTION;", ... );
/* Get data from various tables */
sqlite_exec( db, "SELECT ....", ... );
sqlite_exec( db, "SELECT ....", ... );
/* Transaction finished, though there is nothing to actually commit */
sqlite_exec( db, "COMMIT TRANSACTION;", ... );
Of course, if you're dumping a single table or view, or using a single
select statement across multiple tables, then the begin/commit is not
necassary.
>
>Lloyd
>----- Original Message -----
>From: "Christian Smith" <[EMAIL PROTECTED]>
>To: "Lloyd thomas" <[EMAIL PROTECTED]>
>Cc: <[EMAIL PROTECTED]>
>Sent: Thursday, April 22, 2004 4:21 PM
>Subject: Re: [sqlite] Backing up data by date
>
>
>> On Wed, 21 Apr 2004, Lloyd thomas wrote:
>>
>> >Forgive my ignorance, I have yet to use a transaction and therefore can
>you
>> >give me an example.
>>
>> Transactions are simply SQL statements bounded by a begin/commit pair, or
>> begin/rollback if the transaction is to be aborted:
>>
>> begin transaction;
>> [do something]
>> commit transaction;
>>
>> If [do something] is a single select, then you don't need the
>> begin/commit. You need the begin/commit if you have multiple selects, to
>> prevent the database changing underneath you in between select statements.
>>
>> You can play with transactions simply from the sqlite command line tool.
>>
>> Any decent SQL guide or web page should explain transactions.
>>
>> >
>> >Lloyd
>> >----- Original Message -----
>> >From: "Christian Smith" <[EMAIL PROTECTED]>
>> >To: "Lloyd thomas" <[EMAIL PROTECTED]>
>> >Cc: <[EMAIL PROTECTED]>
>> >Sent: Wednesday, April 21, 2004 3:27 PM
>> >Subject: Re: [sqlite] Backing up data by date
>> >
>> >
>> >> On Tue, 20 Apr 2004, Lloyd thomas wrote:
>> >>
>> >> >Which is the best way to backup rows which meet a certain date
>criteria?
>> >> >ie WHERE data is <= '2003-11-20'.
>> >> >Would I need to select and save the data to a temporary table and then
>> >DUMP the temp table.
>> >> >
>> >>
>> >> Just begin a transaction to get a snapshot of the database, then dump
>the
>> >> data within the transaction. No temporary table needed. Will lock the
>> >> database, but it is the only way to ensure a consistent view unless you
>> >> are saving data from a single table or view, in which case you won't
>need
>> >> the transaction.
>> >>
>> >> Christian
>> >>
>> >> --
>> >> /"\
>> >> \ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
>> >> X - AGAINST MS ATTACHMENTS
>> >> / \
>> >>
>> >> ---------------------------------------------------------------------
>> >> To unsubscribe, e-mail: [EMAIL PROTECTED]
>> >> For additional commands, e-mail: [EMAIL PROTECTED]
>> >>
>> >>
>> >
>> >
>> >
>> >---------------------------------------------------------------------
>> >To unsubscribe, e-mail: [EMAIL PROTECTED]
>> >For additional commands, e-mail: [EMAIL PROTECTED]
>> >
>>
>> --
>> /"\
>> \ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
>> X - AGAINST MS ATTACHMENTS
>> / \
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [EMAIL PROTECTED]
>> For additional commands, e-mail: [EMAIL PROTECTED]
>>
>>
>
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
