[sqlite] DbFunctions.TruncateTime
Hi Ryan, I get your point. :) It seems the I was misunderstanding this help mailing list. I thought it's also support for 'System.Data.SQLite'. In the way 'System.Data.SQLite' is an ADO.NET provider for SQLite and also give support for entity framework. that because I was asking if it support 'DbFunctions' like 'TruncateTime'. I'm asking because I want to know if I simpley miss the a way or a SQLite ADO.Net class to do this. I hope I make it a little more clear why I'm asking in this mailing list. Regards Steffen
[sqlite] DbFunctions.TruncateTime
> > how can I trunc time in EntityFramework? > > I tried it this way: > > model.Datas >.GroupBy(d => > DbFunctions.TruncateTime(d.TimeStamp)) >.Select(d => d.Key.Value) >.ToArray(); > > But get this error: > "SQLite error (1): no such function: TruncateTime" > > How else can I use the "date(timestring) function in EntityFramework? > No solution for this? :( Regards Steffen
[sqlite] DbFunctions.TruncateTime
Hi, how can I trunc time in EntityFramework? I tried it this way: model.Datas .GroupBy(d => DbFunctions.TruncateTime(d.TimeStamp)) .Select(d => d.Key.Value) .ToArray(); But get this error: "SQLite error (1): no such function: TruncateTime" How else can I use the "date(timestring) function in EntityFramework? Regards Steffen
[sqlite] Open DB from stream to use System.IO.Packaging.Package
Hi, is there a proper way to open a SQLiteConnection from a Stream object? What I want is to use the System.IO.Packaging.Package class to build a custom file format. Inside this a SQLite DB should be one System.IO.Packaging.PackagePart. I don't want to copy the DB part to temp directory every time I want to access it. Best Regards Steffen Mangold
[sqlite] System.Data.SQLite version 1.0.98.0 released
> > System.Data.SQLite version 1.0.98.0 (with SQLite 3.8.11.1) is now available > on the System.Data.SQLite website: > Great news, thanks Joe!! Regards Steffen
[sqlite] ATTACH DATABASE statement speed
> > is there any target date when the preRelease branch gets over to a actual > release? > > Is a really hard show stopper for our development at the moment. We checked > everything for compatibility before merge your current trunk to Visual > Studio 2015 and we forget about the SQLite design tool. :( > Sorry! Wrong thread...
[sqlite] System.Data.SQLite 1.0.98.0 release
Hi, is there any target date when the preRelease branch gets over to a actual release? Is a really hard show stopper for our development at the moment. We checked everything for compatibility before merge your current trunk to Visual Studio 2015 and we forget about the SQLite design tool. :( Regards Steffen Mangold ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ATTACH DATABASE statement speed
Hi, is there any target date when the preRelease branch gets over to a actual release? Is a really hard show stopper for our development at the moment. We checked everything for compatibility before merge your current trunk to Visual Studio 2015 and we forget about the SQLite design tool. :( Regards Steffen Mangold
[sqlite] System.Data.SQLite preRelease branch
Steffen Mangold wrote: > > Until final release I want to download the preRelease [e670692d90] > created for 18 days. > Joe Mistachkin wrote: > > Normally, the "preRelease" branches are just a staging area for changes > needed during the release process. > > If packages are available for a pre-release build, > there will be a "Pre-Release Download" link to the far left on the web site > navigation panel. > > Anyhow, the 1.0.98.0 release should be out sometime this week. > Thanks for make it clear. I hope for a fast release. :) Can't wait to take up programming again, with Visual Studio 2015. -- Steffen Mangold
[sqlite] System.Data.SQLite preRelease branch
Hi, on the "News" site I see the update with support of Visual Studio 2015 is scheduled for some day in near future. Until final release I want to download the preRelease [e670692d90] created for 18 days. When I try to download the "sqlite-netFx46-setup-bundle-x86-2015-1.0.98.0.exe" from https://system.data.sqlite.org/index.html/doc/preRelease/www/downloads.wiki I get an error "Not Found, Not logged in". How can I download the preRelease? Best Regards Steffen Mangold
Re: [sqlite] Different User different data
Hi Simon, Thanks for you quick response! I found the same reson. I now move my DB from "c:\program files\..." to "e:\..." (this is a separate data partition). The effect is now that with my DB tool (with and without admin right) I see the same data. Fine so long! :) But my Windows Service (under LOCAL SERICE right) still see other data. :( I don't know how to fix this. Regards Steffen Mangold --- This has nothing to do with SQLite sadly, but still easy to fix. It's the WIndows UAC which is doing this to you, as it should for data that it tries to protect. I am guessing you have the DB in a protected location, such as somewhere within c:\Program Files\ or in c:\Windows\ or such... or in the actual same folder as the exe that you use. The UAC will then copy your file (at some initial point) to a substituted "Safe" folder and only edit that file, cleverly avoidining any system changes to the original by anyone, UNLESS of course, you start up Admin mode, in which case Windows no longer subjects your file to the protection, and allows you to edit/access it normally - BUT, this is now an entirely different file with entirely different content than the one in the "Safe" location. The "Safe" location should be somewhere inside c:\Users\YourUser\Appdata\Roaming\... etc (Just search for it from one of these base folders). Your "other" version of the file will be here. Now as to fixing it, well, that is easy too - just move the file to a non-protected place. My suggestion is of course the Appdata path itself, which windows will allow you to change most anything in without asking user permissions. My Documents is another option (but end-users usually fiddle there, so if this is a end-user type program, best not). You should never keep any config files or any files accessed by your program in the same folder as the exe or indeed any of the mentioned protected folders. (Standard Windows path-names exist for all users for all these special folders, easily accessible from the shell, just google it). Good luck! On 2013/07/03 23:41, Steffen Mangold wrote: > Hi, > > I have some strainge behavior with a SQLIte DB. > If I open the database with my DB tool with administrator right I see other > data as when I open the DB with my tool in non admin mode. > > Is this normal? And if yes can I deactivate this? > > To be clear: > I start my DB tool normal (local user account) and open my DB > (c:\my.db3) I see only data entry A. > > If I open my DB tool with admin rights and open the same DB. > I only see data entry B. > > > Regards > > Steffen Mangold > ___ 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] Different User different data
Hi, I have some strainge behavior with a SQLIte DB. If I open the database with my DB tool with administrator right I see other data as when I open the DB with my tool in non admin mode. Is this normal? And if yes can I deactivate this? To be clear: I start my DB tool normal (local user account) and open my DB (c:\my.db3) I see only data entry A. If I open my DB tool with admin rights and open the same DB. I only see data entry B. Regards Steffen Mangold ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] count infact passed rows of OFFSET select
Hi, I want to know if there is a possibility to count the infact passed row if I make an OFFSET SELECT. For example: table1 --- Row1 Row2 Row3 Row4 Row5 Select * from table1 Order by rowID DESC Lilit 2 Offset 10; Here the Result is null, but I want to detect that I passed 5 existing rows. regards Steffen Mangold ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Status analyze with Sqlite
Hi Clemens, first thank you for your comments. > > It works for what you've asked. Perhaps you should not have kept your actual > requirements a secret. > Sorry for being unclear! :( Hope my English is understandable (I'm from Germany). > > So you want to group only consecutive events with identical statuses, where > "consecutive" is defined on the ordering of the timestamps? > > Then why do you write them? (And why are there duplicates in your last > example?) > You are right I only want consecutive events of same status. Here more a clear example. This is the table: TimeStamp | Status - 2012-07-24 22:23:00 | status1 2012-07-24 22:23:05 | status1 2012-07-24 22:23:10 | status2 2012-07-24 22:23:16 | status2 2012-07-24 22:23:21 | status1 2012-07-24 22:23:26 | status1 2012-07-24 22:23:32 | status2 2012-07-24 22:23:37 | status3 2012-07-24 22:23:42 | status3 2012-07-24 22:23:47 | status3 This is what I want: Begin | End | Status --- 2012-07-24 22:23:00 | 2012-07-24 22:23:05 | status1 2012-07-24 22:23:10 | 2012-07-24 22:23:16 | status2 2012-07-24 22:23:21 | 2012-07-24 22:23:26 | status1 2012-07-24 22:23:37 | 2012-07-24 22:23:47 | status3 > > Well, try this: > > SELECT TimeStamp AS Begin, >(SELECT MAX(TimeStamp) > FROM Data AS same > WHERE same.Status = ou.Status > AND same.TimeStamp >= ou.TimeStamp > AND same.TimeStamp < (COALESCE((SELECT MIN(TimeStamp) > FROM Data AS next > WHERE next.TimeStamp > ou.TimeStamp > AND next.Status <> ou.Status), > '')) >) AS End, >Status > FROM Data AS ou > WHERE Status IS NOT (SELECT Status > FROM (SELECT Status, > MAX(prev.TimeStamp) >FROM Data AS prev > WHERE prev.TimeStamp < ou.TimeStamp)) > Thank you I try this. > > (And it might be easier and faster to just query the events ordered by > timestamp, and aggregate statuses by hand in your code.) > Hm ok I make some test. I think you can be right that in-code aggregation is faster that subquerys. Regards, Steffen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Status analyze with Sqlite
> ID| TimeStamp | Status > > 0 | 2012-07-24 22:23:00 | status1 > 1 | 2012-07-24 22:23:05 | status1 > 2 | 2012-07-24 22:23:10 | status2 > 3 | 2012-07-24 22:23:16 | status2 > 4 | 2012-07-24 22:23:21 | status2 > 5 | 2012-07-24 22:23:26 | status2 > 6 | 2012-07-24 22:23:32 | status2 > 7 | 2012-07-24 22:23:37 | status3 > 8 | 2012-07-24 22:23:42 | status3 > 9 | 2012-07-24 22:23:47 | status3 > > What I want as result is > ID| Begin | End | Status > --- > 0 | 2012-07-24 22:23:00 | 2012-07-24 22:23:05 | status1 > 1 | 2012-07-24 22:23:10 | 2012-07-24 22:23:32 | status2 > 2 | 2012-07-24 22:23:37 | 2012-07-24 22:23:47 | status3 > > > Hmmm, the ID in the result bears virtually no relation to the ID in the data. > Is that intentional? > > Anyhow, some of what you want could come from > > select min(TimeStamp) as Begin, max(TimeStamp) as End, Status from Data group > by Status order by Status > Hi Gerry, thank you but this won't work if the table look like this: ID | TimeStamp | Status 0 | 2012-07-24 22:23:00 | status1 1 | 2012-07-24 22:23:05 | status1 2 | 2012-07-24 22:23:10 | status2 3 | 2012-07-24 22:23:16 | status2 4 | 2012-07-24 22:23:21 | status1 5 | 2012-07-24 22:23:26 | status1 6 | 2012-07-24 22:23:32 | status2 7 | 2012-07-24 22:23:37 | status3 8 | 2012-07-24 22:23:42 | status3 9 | 2012-07-24 22:23:47 | status3 Than you get: ID | Begin | End | Status --- 0 | 2012-07-24 22:23:00 | 2012-07-24 22:23:26 | status1 1 | 2012-07-24 22:23:10 | 2012-07-24 22:23:32 | status2 2 | 2012-07-24 22:23:37 | 2012-07-24 22:23:47 | status3 But it should be: ID | Begin | End | Status --- 0 | 2012-07-24 22:23:00 | 2012-07-24 22:23:05 | status1 0 | 2012-07-24 22:23:10 | 2012-07-24 22:23:16 | status2 1 | 2012-07-24 22:23:21 | 2012-07-24 22:23:26 | status1 2 | 2012-07-24 22:23:37 | 2012-07-24 22:23:47 | status3 You are right the ID column is not relevant. You can ignore them. regards Steffen Mangold ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Status analyze with Sqlite
HI sqlite community, I have a problem I get stucked, maybe someone can help me. :( My issue: For instance if we have 10 rows with following data ID | TimeStamp | Status 0 | 2012-07-24 22:23:00 | status1 1 | 2012-07-24 22:23:05 | status1 2 | 2012-07-24 22:23:10 | status2 3 | 2012-07-24 22:23:16 | status2 4 | 2012-07-24 22:23:21 | status2 5 | 2012-07-24 22:23:26 | status2 6 | 2012-07-24 22:23:32 | status2 7 | 2012-07-24 22:23:37 | status3 8 | 2012-07-24 22:23:42 | status3 9 | 2012-07-24 22:23:47 | status3 What I want as result is ID | Begin | End | Status --- 0 | 2012-07-24 22:23:00 | 2012-07-24 22:23:05 | status1 1 | 2012-07-24 22:23:10 | 2012-07-24 22:23:32 | status2 2 | 2012-07-24 22:23:37 | 2012-07-24 22:23:47 | status3 What I have so far is SELECT ou. ID AS ID, ou.Status AS Status, ou.TimeStamp AS Begin, (SELECT MAX(TimeStamp) FROM Data WHERE TimeStamp >= ou.TimeStamp AND Status = ou.Status AND TimeStamp < '2010-02-24 00:00:00') AS End FROM Data ou But don't work :( Can you please help me? Regards Steffen Mangold ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite for Visual Studio 2012
Joe Mistachkin wrote: > > Yes. The code is currently on trunk. Please see: > > http://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki Thank you. :) Regards Steffen Mangold ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] System.Data.SQLite for Visual Studio 2012
Hi, is there a new ADO.NET setup version for Visual Studio 2012 planed? regards steffen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Update with nested selected
Hi sqlite user, I try to update a table field with a selected value from same tabel. Is this possible? What I try yet is: update TableA Set FieldA = (select FieldB from TableA where IDField = updateData.IDField AND TimeStamp = date(updateData.TimeStamp, '-1 day')); I want to update the FieldA with FieldB one day earlyer, where IDField is the same. I know that " updateData." will not work, its only a placeholder, because I dont know how to tell sqlite to compare with update row. Can you help me? :( Regards Steffen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "DEFAULT BOOLEAN NOT NULL" not working with entityframework
> > Have you checked your table afterwords to ensure you don't have any nulls in > IsReplaced? > > select count(IsReplaced) from mytable where IsReplaced is null; > > I tested and the alter table does fill with default values for me. At least > from the sqlite shell. > > Does this work for you? Are you doing the alter table via your program or > via the shell? > > 3.7.9 > > sqlite> create table t(a integer); > sqlite> insert into t values(1); > sqlite> insert into t values(2); > sqlite> alter table t add column b boolean not null default 0; select * > sqlite> from t; > 1|0 > 2|0 > Hi Michael, i alter it with a tool names "SQLITE Meastro" don't know with shell version the used. So what you say it that "alter table t add column b boolean not null default 0;" normaly replace all null fields with default value? Then I think its comes from my tools. But I always thought that all existing field still stays NULL and sqlite only returns default value for these. Steffen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "DEFAULT BOOLEAN NOT NULL" not working with entity framework
> > I think your problem is with the ado provider. Perhaps it makes the > assumption that every value in a BOOLEAN column must be a BOOLEAN. This is > not true under SQLite: you can have any value in a BOOLEAN column, even TEXT. > Hi Simon, Yes correct because if you alter a table with a new BOOLEAN column with a default value, all existing row still have NULL as value. I think in a correct way the ado provider has to return the default (in my case 0 = false) value if field is NULL and not throw these error. I think this is a bug. Do you with me? Regards Steffen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "DEFAULT BOOLEAN NOT NULL" not working with entity framework
> > Hi guys, > > i have a little problem with BOOLEAN data column. > > I have an existing table filled with data and want to add a new column like > "IsReplaced BOOLEAN NOT NULL DEFAULT 0" > All worked fine but if I now try to read a data row I gat an exception from > the sqlite ado provider that he can not map NULL to Boolean. > Why is the default value is not working correct with Boolean column? (and yes > I know that BOOLEAN in Sqlite is saved as integer, for that I use "DEFAUL 0") > Sorry mail was not finished. I forgot: Thanks for your help. Regards Steffen :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "DEFAULT BOOLEAN NOT NULL" not working with entity framework
Hi guys, i have a little problem with BOOLEAN data column. I have an existing table filled with data and want to add a new column like "IsReplaced BOOLEAN NOT NULL DEFAULT 0" All worked fine but if I now try to read a data row I gat an exception from the sqlite ado provider that he can not map NULL to Boolean. Why is the default value is not working correct with Boolean column? (and yes I know that BOOLEAN in Sqlite is saved as integer, for that I use "DEFAUL 0") ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3 command shell dump possible bug
Hi Simon, thanks for your help. All worked now :) I only lost some data at the end of the table, not so bad. Thank you :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3 command shell dump possible bug
> > You should be able to, yes. Just type in "END;" (without quotes, but with > semicolon). > Ok thank you i will try :) (in a few hours because DB is so big. :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3 command shell dump possible bug
> > If you have a BEGIN command in your script, then you should also have END or > COMMIT at the end (the two are synonyms). > can i do this by shell command after ".read" if my SQL script has miss that? Steffen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3 command shell dump possible bug
WHAT THE ! I now delete the malform message and the rollback command from the *.sql file and run ".read". Sqlite shell runs complete and the shell ask me for new command "> " (DB file seems to have the right size. I'm happy now and enter command ".exit" and bam Db file has 0kb?!?!? What have i done wrong? Any Commit command or something? :( :( ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3 command shell dump possible bug
Ok maybe i found it in the sql file is written (file end): [...] INSERT INTO "InverterData" VALUES(2478,'2012-02-28 15:00:00',1435.73,429173.78,170.28,170.75,169.38,397.56,397.38,396.69,NULL,210976,31,NULL,NULL,1,304,NULL,NULL,NULL,694,NULL,NULL,NULL); / ERROR: (11) database disk image is malformed */ / ERROR: (11) database disk image is malformed */ CREATE TRIGGER SensorData_InsertUpdate [...] ROLLBACK; -- due to errors So sqlite shell can not understand "/ ERROR: (11) database disk image is malformed */" I think. and make a rollback? Do you think that's it? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3 command shell dump possible bug
> > Is it very long ? Can you read it with a dump utility or a text editor > (don't try it with a word processor) and see the SQL commands in it ? > Yes 14 GB. 4 Table, roundabout 200.000.000 inserts. I opened it with a textviewer for large files. Sql seams well formed and readable till the end. I now make a complet integrity_check. Only four error types: On tree page xxx cell xx: invalid page number On tree page xxx cell xx: child page depth differs On page xxx at right child: child page depth differs On page xxx at right child: invalid page number xxx But this error round about 100 times. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3 command shell dump possible bug
> > Are you saying it creates a database file but doesn't put anything into it > (zero filesize) or that it doesn't even create a blank file ? > With dump its write the complete DB File new but nearly at the end (new DD file size compared to the malformed) Sqlite shell breaks and set the file size of the new DB to 0kb. No error is written. :( ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3 command shell dump possible bug
> > Ok, with .dumb i now created a "db.sql" file successfully. > but I don't get the read command!? How create a new DB file with that command? > With "sqlite> .read db.sql" it does much reading but no file is created. > Ok I get it, must attach a DB first. now sqlite writes the data to the DB, hopes this helps. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3 command shell dump possible bug
> > There's no magic tool for repairing damaged database files. But by using the > .dump command (if necessary on each individual table and view) then creating > a new database file and using the .read command you can often rescue some or > all of the data in the original > > database. > Ok, with .dumb i now created a "db.sql" file successfully. but I don't get the read command!? How create a new DB file with that command? With "sqlite> .read db.sql" it does much reading but no file is created. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3 command shell dump possible bug
> > First, get all the other databases done, so you're worried only about the one > which doesn't work. > > Then do the .dump part for that database, putting the output into a file on > disk, which should leave you with a huge file of SQL commands which should > rebuild it. > > It's likely that the .dump stage will fail because your original database is > corrupt. That's your problem. > > If it doesn't fail, split it up into parts, and rebuild your database using > '.read' by reading the parts in one at a time. One of those parts should > cause an error message or a crash. That's your problem. > > Either way, you should be able to narrow down the possible scope for the > crash. Thank you simon i try this, I read in some forums that .dumb is the best way to repair "malformed" DBs. Do you have an other way? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite3 command shell dump possible bug
Hi guys, i have a problem with the sqlite2.exe under windows. Ok, I have here 20 corrupted DBs and want to repair they all. I do this with CMD and the command ".dump | sqlite3 rebuild.db3 | sqlite3 rebuild.temp" This works perfect for all DBs except one. The DB where it is not working has a size of 15GB. During processing I can see the "rebuild.temp" is going bigger and bigger. But at the end the hole file is set empty!! It has a size of 0kb after sqlite3.exe is finished?!?! All DBs are same format, not compressed, no decryption and no password. Is this a bug? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible Timestamp Where cluase bug
> > SQLite does not have a separate "date/time" datatype. It uses either strings > (preferably in ISO8601 format) or numbers (seconds since 1970 or Julian day > number). > > Your WHERE clause is comparing strings, not dates. If you using ISO8601 > dates in your database file, as you do in the query, it will probably work, > though. > Ah ok i dont know this. So I must know the exact datetime string format with where pushed in to make a valid string compare when I do selects, right? So SQLITE stores my timestamp I the way it was pushed in. I toughed It has a fixed format to store it. Thank you. Steffen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible Timestamp Where cluase bug
> > Plus...what's the "T" supposed to do? Perhaps I'm ignorant of the magic you > expect. > The 'T' devide the date from the time. Looking here http://www.sqlite.org/lang_datefunc.html It is the default ISO-8601 datetime format. > > I'm confused as to why you would expect any match at all. And indeed, when I > run your queries against a test set I get nothing back at all for both > queries. > Simple want all events between begin and end of a day. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Possible Timestamp Where cluase bug
Hi SQLITE community, I think i found a strange bug. Lets say we have a table in this form: Id (long) | Timestamp (DateTime) - 12 | 17.01.2012 16:15:00 12 | 17.01.2012 17:15:00 Now we make a query where the data should involved: SELECT Id, TimeStamp FROM tabelA WHERE Timestamp >= '2012-01-17T00:00:00' AND Timestamp <= '2012-01-17T23:59:59' AND ID = 12 ORDER BY Timestamp DESC LIMIT 250 OFFSET 0 Result is 'nothing' means no rows are returned. But if we change the minimum timestamp to 1 day earlier, like: SELECT Id, TimeStamp FROM tabelA WHERE Timestamp >= '2012-01-16T00:00:00' AND Timestamp <= '2012-01-17T23:59:59' AND ID = 12 ORDER BY Timestamp DESC LIMIT 250 OFFSET 0 The result is the 2 rows written above. ??? I don't get it ??? Why this is happen, is it really a bug? Regards Steffen Mangold ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Generated SQL from Skip and Take (EntityFramework)
Hi, i would like to push an old question again to the users. In original it has written to the old forum bei "peter77" on 10-17-2008. But now I have the same problem. Here the question: "The Skip(n) method is not optimally transformed into SQL. Consider the following LINQ expression: (from e in _context.EMPLOYEE orderby e.NAME select e.NAME).Skip().Take(99); This generates the following SQL: SELECT [Var_8_1].[NAME] AS [NAME] WHERE NOT (EXISTS (SELECT [Var_8_3].[NAME] AS [NAME] FROM ( SELECT [Extent1].[NAME] AS [NAME] FROM [EMPLOYEE] AS [Extent1] ORDER BY [Extent1].[NAME] ASC LIMIT ) AS [Var_8_3] WHERE ([Var_8_1].[NAME] = [Var_8_3].[NAME]) OR (([Var_8_1].[NAME] IS NULL) AND ([Var_8_3].[NAME] IS NULL ORDER BY [Var_8_1].[NAME] ASC LIMIT 99 This is very slow if the employee table has a large number of recods, even if the NAME column is indexed. A much more optimal (and shorter) SQL would be: SELECT name FROM employee ORDER BY name ASC LIMIT 99 OFFSET Of course this only works if the LINQ expression has a Take(m) clause specified besides Skin(n). If Take(m) is not specified you could work around by inserting a fake "LIMIT" clause, eg.: SELECT name FROM employee ORDER BY name ASC LIMIT (SELECT count(*) FROM employee) OFFSET or just a huge number in the LIMIT clause: SELECT name FROM employee ORDER BY name ASC LIMIT 1000 OFFSET " Regards Steffen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Runfile script over existing Database
Now i have the problem that the sqlite3.exe has a problem with "ä, ö, ü" in Database filename. :( It makes a new db called " D�sseldorf " for example and fails then :( Steffen Mangold -- In your words: 1. start "cmd.exe" 2. go to directory with sqlite3.exe 3. type test.sql | sqlite3 test.db3 (Return) 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 Steffen Mangold [steffen.mang...@balticsd.de] Sent: Tuesday, November 29, 2011 1:41 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Runfile script over existing Database Thanks for your fast answere and hi michael, > > type myfile.sql | sqlite3 test.db > Pleae don't laught, but I don't get it to run... :( Let me tell what im doing: 1. start "cmd.exe" 2. go to directory with sqlite3.exe 3. > sqlite3.exe (Return) 4. test.sql | "test.db3" The result is only: sqlite> test.sql | "Saalburg 1.BA (2).db3" ...> Steffen Mangold myfile.sql example between the lines: create table t(a int); insert into t values(1); select * from t; You could also do echo .read myfile.sql | sqlite3 test.db 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 Steffen Mangold [steffen.mang...@balticsd.de] Sent: Tuesday, November 29, 2011 12:46 PM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Runfile script over existing Database Hi all, i have a question. I have a script with edit a database file (insert and alter some tables). Now I want to run this script agains an existing database file, with the commandline shell. How can I do this in a batch file (windows) I don't understand the ".read" command. Regards Steffen Mangold ___ 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 ___ 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] Runfile script over existing Database
Thank you that works :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Runfile script over existing Database
Thanks for your fast answere and hi michael, > > type myfile.sql | sqlite3 test.db > Pleae don't laught, but I don't get it to run... :( Let me tell what im doing: 1. start "cmd.exe" 2. go to directory with sqlite3.exe 3. > sqlite3.exe (Return) 4. test.sql | "test.db3" The result is only: sqlite> test.sql | "Saalburg 1.BA (2).db3" ...> Steffen Mangold myfile.sql example between the lines: create table t(a int); insert into t values(1); select * from t; You could also do echo .read myfile.sql | sqlite3 test.db 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 Steffen Mangold [steffen.mang...@balticsd.de] Sent: Tuesday, November 29, 2011 12:46 PM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Runfile script over existing Database Hi all, i have a question. I have a script with edit a database file (insert and alter some tables). Now I want to run this script agains an existing database file, with the commandline shell. How can I do this in a batch file (windows) I don't understand the ".read" command. Regards Steffen Mangold ___ 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
[sqlite] Runfile script over existing Database
Hi all, i have a question. I have a script with edit a database file (insert and alter some tables). Now I want to run this script agains an existing database file, with the commandline shell. How can I do this in a batch file (windows) I don't understand the ".read" command. Regards Steffen Mangold ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Time comparisen and CASE WHEN
Now I fixed it. CREATE TRIGGER tableA _InsertUpdate AFTER INSERT ON tableA begin update tableB set [LowestTime] = CASE WHEN ( [LowestTime] IS NULL ) OR ([LowestTime]> TIME(NEW.TimeStamp)) THEN TIME(NEW.[TimeStamp]) ELSE [LowestTime] END end; ... THEN TIME(NEW.[TimeStamp]) - was the key. If I format a field with "type" 'time' my sqlite db tool only show me the time, even if I put in a hole datetime. But I seems that sqlite also wrote the hole datetime in the field! THANK YOU IGOR! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Time comparisen and CASE WHEN
Ok here the complete example (sorry if I wasn’t clear before): 1. I had a table where I insert some data with a datetime and a value CREATE TABLE tableA ( [TimeStamp] datetime, [Value] varchar ); 2. Now I have a second table where I want save the lowest time insert in tableA CREATE TABLE tableB ( [LowestTime] time, ); 3. To store the time I wrote a trigger for tableA CREATE TRIGGER tableA _InsertUpdate AFTER INSERT ON tableA begin update tableB set [LowestTime] = CASE WHEN ( [LowestTime] IS NULL ) OR ([LowestTime] > TIME(NEW.TimeStamp)) THEN NEW.[TimeStamp] ELSE [LowestTime] END end; 4. Now I make 2 inserts in tableA (update trigger works because I created a dummy row to work) INSERT into tableA ( [TimeStamp], [Value] ) VALUES ( ‘2011-01-01 01:00:00’, ‘Dump’ ); INSERT into tableA ( [TimeStamp], [Value] ) VALUES ( ‘2011-01-01 02:00:00’, ‘Dump’ ); 5. [LowestTime] should now be ‘01:00:00’ BUT it is ’02:00:00’??? Hopes is more clear now. Steffen Mangold ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Time comparisen and CASE WHEN
> > sqlite> select time( '2011-01-29 08:00:00' ); > 08:00:00 > Oh sorry, i looked wrong. I insert this way: INSERT INTO [filed1] VALUES '2011-01-01 08:00:00' And because of the init of: CREATE TABLE tabel1 ( [field1] time, ); SQLite writes only the time to the database. But this fails: [field1] < TIME(NEW.TimeStamp) And I don't know why and if I do this: TIME( [field1] ) < TIME( NEW.TimeStamp ) It returns always exact the opposite of what I aspect. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Time comparisen and CASE WHEN
Hi Igor,
>
> Yes. You can use any expression. AND and OR are operators, just like + or =
>
Ok, thank you good to know.
>
> SQLite doesn't have a dedicated "time" type. There are many ways to store
> time values - e.g. as a string '12:34', or as a number of seconds from
> midnight.
> How exactly do you put your time values into the field?
>
I create the table in this way:
CREATE TABLE tabel1 (
[field1] time,
);
I insert data in this way (for example):
INSERT INTO [filed1] VALUES TIME('29-01-2011 08:00:00')
>
> TIME() produces a string of the form '12:34:56' (hours:minutes:seconds).
> What's in NEW.TimeStamp? What's in field1?
>
NEW.TimeStamp is a complete datetime. But I only want to compare the time part
in my trigger.
--
Steffen Mangold
___
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] Time comparisen and CASE WHEN
Hi there, i have two little questions. First one, is this valid syntax for a CASE WHEN? CASE WHEN ( [field1] IS NOT NULL ) AND ( ( [field1] < 1 ) OR ( [field1] > 0 ) ) In special I mean "can I use AND, OR in CASE WHEN". Second question, I get really strange results when I try to compare time values. For example: Given is a table with a field [field1] data type 'time'. Now I make a compare in a trigger like this. [field1] < TIME(NEW.TimeStamp) But this is not working :( Thanks for your help Steffen Mangold ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DateTimeOffset in SQLite
No solution? :( Joe can you help perhaps? You help me so much with my other problem with the Entity Framework. ___ 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] DateTimeOffset in SQLite
Pavel wrotes: > > I don't know C#, but quick look at Microsoft's documentation shows that you > can get Ticks() from TimeSpan, save it into DB and then when you get Int64 > from DB you can create TimeSpan from it. > Seems not to work entity framework cannot convert "long" to the type "TimeSpan". I know I can make a "long" field and extend the entity class with a "TimeSpan" property that convert it. But I want to find a "entity framework way" to do this. Here the EDMX generator error: Member Mapping specified is not valid. The type 'Edm.DateTimeOffset[Nullable=False,DefaultValue=,Precision=]' of element 'Type ' of member 'TestProperty' in type 'TestEntity' is not compatible with 'Typ' of member 'SQLite.integer[Nullable=False,DefaultValue=]' in type 'TestEntity'. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DateTimeOffset in SQLite
Igor Tandetnik wrote: >What's DateTimeOffset? Offset from what to what? What exactly are you trying >to achieve? >See if this helps: http://www.sqlite.org/lang_datefunc.html In C# (.Net) it is the type Timespan. I want to save a timespan in the DB and get an TimeSpan object in .Net out of the DB. -- Steffen Mangold ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] DateTimeOffset in SQLite
Hi all, how to use DateTimeOffset with Sqlite, if it is possible? Regards Steffen Mangold ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to compare time stamp
Akash Agrawal wrotes: > > I have table in which i have column of Date contain both *date and time*when > compare the value in my c++ program it is not giving me correct result . can > you help to solve my problem. > Hi, I had a similar problem. How accurate is your time? I had such a problem with comparing milliseconds. Let me search what exact was the problem, I come back here. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TransactionScope ON CONFLICT
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 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 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
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
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
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
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
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
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
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
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
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
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
[sqlite] TransactionScope ON CONFLICT
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
