Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

Last error i would see is i have made an error with the assembly creation.
Is it possible for you joe, to send me your assemblies direct per email?
So I can exclude this error.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

Steffen Mangold wrote:

>
> The important thing is that there is only one SaveChanges call (which 
> attempts to commit pending changes to the underlying database).
>

Yes, i do so.

>
> My test case does attempt to add rows that conflict with data already present 
> in the sample database.
>

Ok, just like in my test.

>
> I assume that is done in a separate transaction block?
>

Yes, a different context instance without transaction block, just like you 
would do it normally. (context closed after insert)

>
> Also, did you try adding the manual opening of the connection in the previous 
> message I sent?  As follows:
>
> using (TransactionScope transaction = new TransactionScope()) {
>   context.Connection.Open(); // try adding this line.

Yes, i tried but no success, same result like before for me.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Joe Mistachkin]

Steffen Mangold wrote:
>
> So you add all the 15 rows inside one transactionscope and with one
> context.SaveChanges() call?
>

Yes.

>
> Perhaps it makes a difference if the contains violation is inside the
> 15 rows you try to add (row 10 is incompatible with row 2 for example)
>

The important thing is that there is only one SaveChanges call (which
attempts to commit pending changes to the underlying database).

>
> If it is a contains violation with data already in DB.
>

My test case does attempt to add rows that conflict with data already
present in the sample database.

>
> For my test I add a single row to the DB just before I doing my
> transaction.
>

I assume that is done in a separate transaction block?

Also, did you try adding the manual opening of the connection in the
previous message I sent?  As follows:

using (TransactionScope transaction = new TransactionScope())
{
context.Connection.Open(); // try adding this line.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

Joe Mistachkin wrote:

>
> Are you sure the application is loading the new DLLs and not some stale DLLs 
> leftover from before?
>

Yes, i look with Visual Studio in the "Loading Modules" window. Path and 
version are correct (1.7.5) before I had installed only some 1.6 runtimes.
Hopes I do all right with compilation (thank again for your step-by-step help)

>
> Yes, my test case works properly (the first time).  The second time, all the 
> rows have already been added and the test case fails, which is still the 
> expected behavior.
>

Ok, now its getting really strange.

>
> The C# code for the test case is here (in the EFTransactionTest method):
>
> http://system.data.sqlite.org/index.html/finfo?name=testlinq/Program.cs
>
> One thing you may notice about the test case is that I try to add a total of
> 15 rows to
> the database.  The first five rows are added, the second five rows are NOT 
> added (because they would violate the PRIMARY KEY constraint), and the final 
> five rows are also added.
>
> The test case verifies that all 10 rows that should be added are in fact 
> added.  It also verifies that the appropriate exception is raised for the 
> PRIMARY KEY constraint violation.

So you add all the 15 rows inside one transactionscope and with one 
context.SaveChanges() call?
Perhaps it makes a difference if the contains violation is inside the 15 rows 
you try to add (row 10 is incompatible with row 2 for example) or
If it is a contains violation with data already in DB. For my test I add a 
single row to the DB just before I doing my transaction.

--
Joe Mistachkin

___
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

Re: [sqlite] TransactionScope ON CONFLICT

[Joe Mistachkin]

Steffen Mangold wrote:
>
> I try this now but with no success.
>

Are you sure the application is loading the new DLLs and not some stale DLLs
leftover
from before?

>
> Did it work at your test case?
>

Yes, my test case works properly (the first time).  The second time, all the
rows have
already been added and the test case fails, which is still the expected
behavior.

>
> If yes, can you send me your testcode, so I can look for difference to my?
>

The C# code for the test case is here (in the EFTransactionTest method):

http://system.data.sqlite.org/index.html/finfo?name=testlinq/Program.cs

One thing you may notice about the test case is that I try to add a total of
15 rows to
the database.  The first five rows are added, the second five rows are NOT
added (because
they would violate the PRIMARY KEY constraint), and the final five rows are
also added.

The test case verifies that all 10 rows that should be added are in fact
added.  It also
verifies that the appropriate exception is raised for the PRIMARY KEY
constraint violation.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

Igor Tandetnik wrotes:

>I'm not sure I understand this statement. What kind of "influence" do you want 
>to exert?

To work like expected.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

Joe Mistachkin wrote:

>The test case I added for this issue is remarkably similar to your code, 
>except it uses a different schema (the Northwind sample database) and does not 
>re-throw the exception in the catch block (it simply writes it to the console 
>instead).
>The one
>modification I would make to your code is explicitly opening the connection 
>just inside the TransactionScope using block (like my test case does).  
>Without that, the .NET Framework may try to open more than one connection to 
>the underlying database, which could >cause some problems (it did for me).  
>For example, try this
>change:

I try this now but with no success. Did it work at your test case? If yes, can 
you send me your testcode, so I can look for difference to my?


>Also, keep in mind that only the rows of data that are not causing any error 
>will be added to the database.  Any rows that fail constraints will not be 
>added.

Yes this is ok, I don't want to destroy the Db. ;)
 
I just want the result like in the SQLite doku, like:

Dataset 1 (Success in DB)
Dataset 2 (Success in DB)
Dataset 3 (Failure not in DB)
Dataset 4 (Success in DB)
Dataset 5 (Success in DB)

But for now I only get:

Dataset 1 (Success in DB)
Dataset 2 (Success in DB)
Dataset 3 (Failure not in DB)
Dataset 4 (never happens)
Dataset 5 (never happens)

Steffen Mangold
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Igor Tandetnik]

Steffen Mangold  wrote:
> Hm... but the exception i get is an SQLite constraint exception. This means 
> that
> The error occurs at the moment where SQLite provider try to write data to the 
> DB.

Which is inside SaveChanges call.

> So I cannot have influence to this loop that you mean.

I'm not sure I understand this statement. What kind of "influence" do you want 
to exert?
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

Hm... but the exception i get is an SQLite constraint exception. This means that
The error occurs at the moment where SQLite provider try to write data to the 
DB.
So I cannot have influence to this loop that you mean.


Igor Tandetnik wrote:

>Inside this call, a loop runs, with one INSERT statement executed for each 
>prior AddObject call.
>And here's where you commit the transaction, regardless of whether or not it 
>completed successfully.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Joe Mistachkin]

Steffen Mangold wrote:
>
> No i think i can exclude this as the problem.
> Because my code goes like this:
>

The test case I added for this issue is remarkably similar to your code,
except it
uses a different schema (the Northwind sample database) and does not
re-throw the
exception in the catch block (it simply writes it to the console instead).
The one
modification I would make to your code is explicitly opening the connection
just
inside the TransactionScope using block (like my test case does).  Without
that,
the .NET Framework may try to open more than one connection to the
underlying
database, which could cause some problems (it did for me).  For example, try
this
change:

using (TransactionScope transaction = new TransactionScope())   //begin
transaction
{
context.Connection.Open();

Also, keep in mind that only the rows of data that are not causing any error
will be
added to the database.  Any rows that fail constraints will not be added.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Igor Tandetnik]

Steffen Mangold  wrote:
> No i think i can exclude this as the problem.
> Because my code goes like this:
>
> try
>{
> context.SaveChanges(); // save changes with transaction !exception raised here

Inside this call, a loop runs, with one INSERT statement executed for each 
prior AddObject call.

> }
>  catch (Exception)
>{
>   //throw;
>}
>finally
>   {
>transaction.Complete(); // end transaction
>context.AcceptAllChanges();

And here's where you commit the transaction, regardless of whether or not it 
completed successfully.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

No i think i can exclude this as the problem.
Because my code goes like this:

using (dataDBEntities context = new dataDBEntities())   
//create context
{
using (TransactionScope transaction = new TransactionScope())   //begin 
transaction
{
foreach (object data in objectIWantToAdd)   
// add all object to context
{
context.AddObject("DataObjects", data); // !no 
exceptions raised here
}

try
{
context.SaveChanges();  // save 
changes with transaction !exception raised here
}
catch (Exception)
{
 //throw;
}
finally
{
transaction.Complete(); 
// end transaction
context.AcceptAllChanges();
}
}
}

So there is no loop to break by an exception. :(


Igor Tandetnik wrotes:

> Is this perhaps because your loop is terminated by an exception, and never 
> gets around to actually insert the remaining rows?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Igor Tandetnik]

Steffen Mangold  wrote:
> Ok I'm tested it now in a simple test environment.
> 
> Conditions:
> 1 table with two columns "TimeStamp" (PK) and "SensorID" (simple value).
> 
> First I add a row with "TimeStamp" "2011-01-01 01:05:00" (Success)
> Then I doing a Transaction with 10 "TimeStamps" from "2011-01-01 01:00:00" to 
> "2011-01-01 01:10:00". (Failure)
> 
> An Exception show in Debug Output Window
> "SQLite error (19): abort at 21 in [INSERT INTO [SensorData]([SensorID], 
> [TimeStamp])
> VALUES (@p0, @p1);]: columns TimeStamp are not unique"
> 
> In the data base are now 6 rows, that mean all after the failing insert are 
> not executed be the transaction.

Is this perhaps because your loop is terminated by an exception, and never gets 
around to actually insert the remaining rows?
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

Ok I'm tested it now in a simple test environment.

Conditions: 
1 table with two columns "TimeStamp" (PK) and "SensorID" (simple value).

First I add a row with "TimeStamp" "2011-01-01 01:05:00" (Success)
Then I doing a Transaction with 10 "TimeStamps" from "2011-01-01 01:00:00" to 
"2011-01-01 01:10:00". (Failure)

An Exception show in Debug Output Window 
"SQLite error (19): abort at 21 in [INSERT INTO [SensorData]([SensorID], 
[TimeStamp])
 VALUES (@p0, @p1);]: columns TimeStamp are not unique"

In the data base are now 6 rows, that mean all after the failing insert are not 
executed be the transaction.


So I think if I'm doing all right with checking out your fix, it is not working 
at all. :(


Steffen Mangold wrote:

>Sorry, I make my test with wrong conditions it looks like It not works correct 
>at all.
>Please give me a sec to do some more tests and ignore my last message *shame*.
>I come back here after testing. 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

Ok im not clear if im doning it right. I follow your instructions and get a 
compliable version.
But how to get your branch? What I'm doing now is to download the zip under 
"Other links: Zip archive" under
http://system.data.sqlite.org/index.html/info/42af4d17a5 and copy it over the 
version from this fossil rep.
Is this correct?

Joe Mistachkin wrote:

>1. Download the Fossil binary for your platform (e.g. Windows):

>   http://www.fossil-scm.org/download/fossil-w32-20110901182519.zip

>2. Extract the ZIP file to some directory along your PATH.

>3. Open a "Command Prompt" window.

>4. Create a directory to hold the source tree, for example:

>   mkdir C:\dev\sqlite\dotnet

>5. Change to the directory created in step #4, for example:

chdir /D C:\dev\sqlite\dotnet

>6. Execute the following command to clone the repository:

>   fossil clone http://system.data.sqlite.org/ dotnet.fossil

>7. Execute the following command to open the repository:

>   fossil open dotnet.fossil

>8. Execute the following command to change to the build directory:

>   chdir Setup

>9. Execute the following command to build the managed project(s):

>   build.bat ReleaseManagedOnly Win32

>10. Execute the following command to build the native project(s) for the x86 
>processor architecture:

>   build.bat ReleaseNativeOnly Win32

>11. Now, all the binaries should be in the following directory:

>   C:\dev\sqlite\dotnet\bin\2010\Release\bin

>12. Copy the "SQLite.Interop.dll", "System.Data.SQLite.dll", and 
>"System.Data.SQLite.Linq.dll" files into your application directory.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

Sorry, I make my test with wrong conditions it looks like It not works correct 
at all.
Please give me a sec to do some more tests and ignore my last message *shame*.
I come back here after testing.

Steffen Mangold wrote:

>Nice job! :) It works now like expected. I have tested it with use of this 
>patch http://system.data.sqlite.org/index.html/info/42af4d17a5 .
>I also wrote a comment to the ticket [ccfa69fc32]. Thank you, great job.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

Nice job! :) It works now like expected. I have tested it with
use of this patch http://system.data.sqlite.org/index.html/info/42af4d17a5 .
I also wrote a comment to the ticket [ccfa69fc32]. Thank you, great job.

Steffen

Joe Mistachkin wrote:

>I believe that I've found and fixed an issue in the SQLiteConnection class 
>that could be responsible for the errant behavior you are seeing.

>The check-in is here (on the "bug-ccfa69fc32" branch):

>http://system.data.sqlite.org/index.html/ci/42af4d17a5

>Would it be possible for you to update your local System.Data.SQLite and see 
>if this fix corrects the behavior you are seeing?

>The fix is located on the "bug-ccfa69fc32" branch in Fossil.  If you need 
>information on checking out or building the System.Data.SQLite source code, 
>please let me know and I will send complete step-by-step instructions.

--
Joe Mistachkin

___
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

Re: [sqlite] TransactionScope ON CONFLICT (oops, missed a step)...

[Joe Mistachkin]

Oops, forgot to update to the bug-ccfa69fc32 branch, inserted new step #8
below to correct this.

Steffen Mangold wrote:
> 
> It would really help me if you send me your step by step instructions.
> I had some experience with SVN, but it will help for building.
> 
 
1. Download the Fossil binary for your platform (e.g. Windows):

http://www.fossil-scm.org/download/fossil-w32-20110901182519.zip

2. Extract the ZIP file to some directory along your PATH.

3. Open a "Command Prompt" window.

4. Create a directory to hold the source tree, for example:

mkdir C:\dev\sqlite\dotnet

5. Change to the directory created in step #4, for example:

chdir /D C:\dev\sqlite\dotnet

6. Execute the following command to clone the repository:

fossil clone http://system.data.sqlite.org/ dotnet.fossil

7. Execute the following command to open the repository:

fossil open dotnet.fossil

8. Execute the following command to access the bug-ccfa69fc32 branch:

fossil update bug-ccfa69fc32

9. Execute the following command to change to the build directory:

chdir Setup

10. Execute the following command to build the managed project(s):

build.bat ReleaseManagedOnly Win32

12. Execute the following command to build the native project(s)
for the x86 processor architecture:

build.bat ReleaseNativeOnly Win32

13. Now, all the binaries should be in the following directory:

C:\dev\sqlite\dotnet\bin\2010\Release\bin

14. Copy the "SQLite.Interop.dll", "System.Data.SQLite.dll", and
"System.Data.SQLite.Linq.dll" files into your application directory.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Joe Mistachkin]

Steffen Mangold wrote:
> 
> It would really help me if you send me your step by step instructions.
> I had some experience with SVN, but it will help for building.
> 
 
1. Download the Fossil binary for your platform (e.g. Windows):

http://www.fossil-scm.org/download/fossil-w32-20110901182519.zip

2. Extract the ZIP file to some directory along your PATH.

3. Open a "Command Prompt" window.

4. Create a directory to hold the source tree, for example:

mkdir C:\dev\sqlite\dotnet

5. Change to the directory created in step #4, for example:

chdir /D C:\dev\sqlite\dotnet

6. Execute the following command to clone the repository:

fossil clone http://system.data.sqlite.org/ dotnet.fossil

7. Execute the following command to open the repository:

fossil open dotnet.fossil

8. Execute the following command to change to the build directory:

chdir Setup

9. Execute the following command to build the managed project(s):

build.bat ReleaseManagedOnly Win32

10. Execute the following command to build the native project(s)
for the x86 processor architecture:

build.bat ReleaseNativeOnly Win32

11. Now, all the binaries should be in the following directory:

C:\dev\sqlite\dotnet\bin\2010\Release\bin

12. Copy the "SQLite.Interop.dll", "System.Data.SQLite.dll", and
"System.Data.SQLite.Linq.dll" files into your application directory.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

hi joe,

wow thanks for your fast help. Tomorrow I will try your patch.
It would really help me if you send me your step by step instructions.
I had some experience with SVN, but it will help for building.

Thank you!!

Steffen Mangold


Joe Mistachkin wrote:

>I believe that I've found and fixed an issue in the SQLiteConnection
>class that could be responsible for the errant behavior you are seeing.

>The check-in is here (on the "bug-ccfa69fc32" branch):

>http://system.data.sqlite.org/index.html/ci/42af4d17a5

>Would it be possible for you to update your local System.Data.SQLite
>and see if this fix corrects the behavior you are seeing?

>The fix is located on the "bug-ccfa69fc32" branch in Fossil.  If you
>need information on checking out or building the System.Data.SQLite
>source code, please let me know and I will send complete step-by-step
>instructions.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Joe Mistachkin]

Steffen Mangold wrote:
> 
> I have a question to the System.Data.SQLite in action with the .Net
> TransactionScope.  In the SQLite documentation is written that the
> default behavior of a transaction in case of an error is "ABORT".
> In documentation is also writen that "prior SQL statements within
> the same transaction are preserved and the transaction remains
> active".
> 

I believe that I've found and fixed an issue in the SQLiteConnection
class that could be responsible for the errant behavior you are seeing.

The check-in is here (on the "bug-ccfa69fc32" branch):

http://system.data.sqlite.org/index.html/ci/42af4d17a5

Would it be possible for you to update your local System.Data.SQLite
and see if this fix corrects the behavior you are seeing?

The fix is located on the "bug-ccfa69fc32" branch in Fossil.  If you
need information on checking out or building the System.Data.SQLite
source code, please let me know and I will send complete step-by-step
instructions.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Pavel Ivanov]

>> In this example I added 10 objects to the context and I know that object #5 
>> raises a PrimaryKey (or unique) Exception.>> What I read from the SQLite 
>> documentation I understand that object #6 till object #10 will be also add 
>> to the database.>> But this never happens.>> I assume you mean that you do 
>> eventually do a COMMIT.  So what's happening is that ABORT not only aborts 
>> the current operation but also aborts all other operations until you do 
>> either a COMMIT or a ROLLBACK.  Which is interesting.
I'm not familiar with implementations of AddObject() and SaveChanges()
methods. But if it executes inserts one-by-one and in case of any
error it just throws an exception then inserts #6 through #10 just
don't have a chance to be executed. So I would check documentation for
dataGroupDBEntities in case if it has different policies of processing
errors in such combined operations.

And btw, this behavior doesn't have anything to do with SQLite's
documentation and default behavior in C interface.


Pavel


On Sat, Sep 10, 2011 at 11:43 AM, Simon Slavin  wrote:
>
> On 10 Sep 2011, at 4:22pm, Steffen Mangold wrote:
>
>> http://www.sqlite.org/lang_transaction.html
>> " Such transactions usually persist until the next COMMIT or ROLLBACK 
>> command.
>> But a transaction will also ROLLBACK if the database is closed or if an 
>> error occurs and the ROLLBACK conflict resolution algorithm is specified.
>> See the documentation on the ON CONFLICT clause for additional information 
>> about the ROLLBACK conflict resolution algorithm."
>
> Okay, so that one depends on what you've specified for ON CONFLICT.  Fair 
> enough.
>
>
>
>> http://www.sqlite.org/lang_conflict.html
>> " ABORT -  When an applicable constraint violation occurs, the ABORT 
>> resolution algorithm aborts the current SQL statement with an
>> SQLITE_CONSTRAIT error and backs out any changes made by the current SQL 
>> statement;
>> but changes caused by prior SQL statements within the same transaction are 
>> preserved and the transaction remains active. This is the default behavior 
>> and the behavior proscribed the SQL standard."
>
> Ah.  I misunderstood.  ABORT is not an error.  So your problem from your 
> earlier post is this:
>
>> In this example I added 10 objects to the context and I know that object #5 
>> raises a PrimaryKey (or unique) Exception.
>> What I read from the SQLite documentation I understand that object #6 till 
>> object #10 will be also add to the database.
>> But this never happens.
>
> I assume you mean that you do eventually do a COMMIT.  So what's happening is 
> that ABORT not only aborts the current operation but also aborts all other 
> operations until you do either a COMMIT or a ROLLBACK.  Which is interesting.
>
> Simon.
> ___
> 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

Re: [sqlite] TransactionScope ON CONFLICT

[Simon Slavin]

On 10 Sep 2011, at 4:22pm, Steffen Mangold wrote:

> http://www.sqlite.org/lang_transaction.html
> " Such transactions usually persist until the next COMMIT or ROLLBACK 
> command. 
> But a transaction will also ROLLBACK if the database is closed or if an error 
> occurs and the ROLLBACK conflict resolution algorithm is specified. 
> See the documentation on the ON CONFLICT clause for additional information 
> about the ROLLBACK conflict resolution algorithm."

Okay, so that one depends on what you've specified for ON CONFLICT.  Fair 
enough.



> http://www.sqlite.org/lang_conflict.html
> " ABORT -  When an applicable constraint violation occurs, the ABORT 
> resolution algorithm aborts the current SQL statement with an 
> SQLITE_CONSTRAIT error and backs out any changes made by the current SQL 
> statement;
> but changes caused by prior SQL statements within the same transaction are 
> preserved and the transaction remains active. This is the default behavior 
> and the behavior proscribed the SQL standard."

Ah.  I misunderstood.  ABORT is not an error.  So your problem from your 
earlier post is this:

> In this example I added 10 objects to the context and I know that object #5 
> raises a PrimaryKey (or unique) Exception.
> What I read from the SQLite documentation I understand that object #6 till 
> object #10 will be also add to the database.
> But this never happens.

I assume you mean that you do eventually do a COMMIT.  So what's happening is 
that ABORT not only aborts the current operation but also aborts all other 
operations until you do either a COMMIT or a ROLLBACK.  Which is interesting.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

Hi Simon,

first nice to hear from you. :)

> Fred helps solve Helen's problem one day, Helen may solve Fred's the 
> following week.

Oh I'm not mean pro support. What you descript is what I'm searching for. Like 
a forum.

> Can you show us a pointer to this information ?

Sure,

http://www.sqlite.org/lang_transaction.html
" Such transactions usually persist until the next COMMIT or ROLLBACK command. 
But a transaction will also ROLLBACK if the database is closed or if an error 
occurs and the ROLLBACK conflict resolution algorithm is specified. 
See the documentation on the ON CONFLICT clause for additional information 
about the ROLLBACK conflict resolution algorithm."

http://www.sqlite.org/lang_conflict.html
" ABORT -  When an applicable constraint violation occurs, the ABORT resolution 
algorithm aborts the current SQL statement with an 
SQLITE_CONSTRAIT error and backs out any changes made by the current SQL 
statement;
but changes caused by prior SQL statements within the same transaction are 
preserved and the transaction remains active. This is the default behavior and 
the behavior proscribed the SQL standard."

I think "transaction remains active" make it sure and there will be not 
rollback at all.

> Transactions are a way of grouping database changes together.  
> Your description above seems to say something different.

If you trying this in a DB Sqlite tool (or command shell) you see the behave I 
described.

Greetings from germany,
Steffen 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Simon Slavin]

On 10 Sep 2011, at 3:58pm, Steffen Mangold wrote:

> I'm hoping doing all right to get technical support. :)

Perhaps not.  If you want technical support you have to pay for it.  This 
mailing list is mostly a bunch of users who have run into their own problems 
over the years.  Fred helps solve Helen's problem one day, Helen may solve 
Fred's the following week.

> In the SQLite documentation is written that the default behavior of a 
> transaction in case of an error is
> "ABORT". In documentation is also writen that "prior SQL statements within 
> the same transaction are preserved and the transaction remains active".

Can you show us a pointer to this information ?

Transactions are a way of grouping database changes together.  The idea is that 
if any of the changes within a transaction cannot be completed, none of them 
should go ahead -- just like a transaction in real life.  Your description 
above seems to say something different.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

Hi,

I'm hoping doing all right to get technical support. :)

I have a question to the System.Data.SQLite in action with the .Net 
TransactionScope.
In the SQLite documentation is written that the default behavior of a 
transaction in case of an error is
"ABORT". In documentation is also writen that "prior SQL statements within the 
same transaction are preserved and the transaction remains active".


In my case using a transaction does not show this behaves. To be little more 
clear here some example code:

using (dataGroupDBEntities context = new 
dataGroupDBEntities(this.GetDataGroupConnection(cachedSensor.Logger.DataStorage 
as FileBasedDataStorage)))
{
using (TransactionScope transaction = new 
TransactionScope())
{
foreach (SensorValuePair data in sensorData)
{
// create sensor data
SensorData newSensorData = new SensorData
{
TimeStamp = data.Timestamp,
SensorID = cachedSensor.SensorID,
};
// save to DB
context.AddObject("SensorData", newSensorData);
}
try
{
context.SaveChanges(SaveOptions.None);
}
catch (Exception)
{
throw;
}
finally
{
transaction.Complete();
context.AcceptAllChanges();
}
}
}

In this example I added 10 objects to the context and I know that object #5 
raises a PrimaryKey (or unique) Exception.
What I read from the SQLite documentation I understand that object #6 till 
object #10 will be also add to the database.
But this never happens. :( Can you help me?

Regards

Steffen Mangold
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users