Re: [sqlite] C# How to getorun autoincrement value
On 2012-03-08 11:07, Joe D wrote: On 2012-03-06 15:20, Pawl wrote: Hi, I don't know how to make this EASY thing (for me side) I found only this, but they said it is solverd, but it isn;t http://stackoverflow.com/questions/3828098/solved-how-to-return-autoincrement-value-in-insert-query-in-sqlite I don;t know what I get exception Not supported when I tried to setup ParameterDirection another then input! I add this "sqa.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;" before. But without any result! Try this. SQLiteCommand command = new SQLiteCommand("INSERT INTO TABLE (FIELD1, FIELD2) VALUES (:FIELD1, :FIELD2); SELECT LAST_INSERT_ROWID();"); ... append parameters ... int nextValue = command.ExecuteScalar(); Or if you're looking for a specific column and not the ROWID, try this: SQLiteCommand command = new SQLiteCommand("INSERT INTO TABLE (FIELD1, FIELD2) VALUES (:FIELD1, :FIELD2); SELECT FIELD1 FROM TABLE WHERE ROWID = LAST_INSERT_ROWID();"); ... append parameters ... int nextValue = command.ExecuteScalar(); Never tried that, but it looks like it should work... Joe D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C# How to getorun autoincrement value
On 2012-03-06 15:20, Pawl wrote: Hi, I don't know how to make this EASY thing (for me side) I found only this, but they said it is solverd, but it isn;t http://stackoverflow.com/questions/3828098/solved-how-to-return-autoincrement-value-in-insert-query-in-sqlite I don;t know what I get exception Not supported when I tried to setup ParameterDirection another then input! I add this "sqa.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;" before. But without any result! Try this. SQLiteCommand command = new SQLiteCommand("INSERT INTO TABLE (FIELD1, FIELD2) VALUES (:FIELD1, :FIELD2); SELECT LAST_INSERT_ROWID();"); ... append parameters ... int nextValue = command.ExecuteScalar(); Joe D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Do I need to migrate to MySQL?
On 2011-06-05 12:26, Dagdamor wrote: > If you need a non-transactional (atomic)... > If you need transactions (although in most of the web cases you don't need > them)... Non-transactional is by definition not atomic. With the single exception of something that is strictly read-only, I have never, ever, seen any database application that did not need transactions. Ever. There's more to transactions than just grouping together multiple SQL statements. If you do any INSERTs, UPDATEs, or DELETEs at all, you need transactions, even if there are no indexes involved, even if the individual statements don't affect each other. Even a simple insert can involve multiple operations like requesting more disk space from the OS, rearranging pointers in the database file on the disk, etc. What happens if there's a power failure while it's in the middle of that insert? With transactions, it gets rolled back when things start up again. Without, you could wind up with a corrupted database. Joe D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bulk insert performance
On 2011-05-29 06:14, Kees Nuyt wrote: > On Sun, 29 May 2011 10:35:51 +1000, "Greg Keogh" > wrote: > >> I'm utterly gobsmacked by the poor performance of the >> inserts without a transaction around them. > > It's a FAQ, > http://www.sqlite.org/faq.html#q19 > explains the reasons quite clearly. Also see the section named "Tips on Optimizing Your Queries" in the SQLite.Net help files. The advice there applies to any .NET data provider, not just SQLite.Net. Joe D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite.net with Linq to create database
On 2011-05-24 18:37, Ruth Ivimey-Cook wrote: > So far the create database thing is my bugbear. It doesn't seem hard or > bad practice to implement "create database" as a no-op (or better, an > expunge of the current file so that the app sees an empty database). > > I guess a workaround could be to modify the wrapper to search/replace > create database out of existence... that really does seem like a hack! According to this: http://msdn.microsoft.com/en-us/library/bb399375%28v=VS.90%29.aspx you need to create a class for your database that inherits from DataContext. CreateDatabase is a DataContext method. So it looks to me like you need to override that method in your derived database class. This is just from poking around on MSDN. I am no doubt terribly wrong, and will now be mocked. Look at the testlinq program and see how it connects. Maybe that will give you some pointers. Joe D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite.net with Linq to create database
On 2011-05-24 09:28, Ruth Ivimey-Cook wrote: > Folks, > > Has anyone successfully used system.data.linq and system.data.sqlite > together with the linq CreateDatabase function? > > When I try, I get an exception "syntax error near "DATABASE"". > > If I try to query database existence using Linq's "DatabaseExists" I get > a NotImplemented exception in the sqlite ChangeDatabase function - which > doesn't make sense to me. I have never in my life messed with Linq, but I did poke around a bit getting the testlinq program to work. Does it work for you? You'll probably have to fix the connection strings in the App.Config for it. Joe D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite INT vs INTEGER
On 2011-05-19 01:16, Xavier Naval wrote: > You can take a look at http://sqlite.phxsoftware.com/forums/t/31.aspx > where you can find the DataType mappings for the .NET provider. > > Be careful also with > > DOUBLE -> DbType.Double > FLOAT -> DbType.Double > REAL -> DbType.Single Ah, so it is by design then. OK, I can live with that. Joe D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] System.Data.SQLite INT vs INTEGER
I have an app that creates a table of file names and sizes. The app itself works fine. I wanted to look at the table just to make sure that it was doing what it should, so I used something to do a simple SELECT * and bind the results to a DataGrid. When I did, I got an error "Value was either too large or too small for an Int32". I tracked it down to the column being created as type INT instead of INTEGER. SQLite treats these as the same, but System.Data.SQLite treats INT as Int32 and INTEGER as Int64. Changing the app so that the table was created as type INTEGER made the error go away. I traced it down to the SQLiteConvert._typeNames array. The entry for INT defines it as Int32. I changed it to Int64 and changed the app back to INT for the column. This also made the error go away. The error also did not occur if there were no files with sizes in excess of Int32.MaxValue. I'm not sure if this is worthy of a bug fix or not. It could cause issues if you were loading a table created somewhere else with a column defined as INT instead if INTEGER. Or not. Joe D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users