Re: [sqlite] UPDATE statement without FROM clause

Is there some absolute requirement that it all be done in SQL? Depending on the number of "items", it'd probably be faster in a loop in code. Even in MSSQL Server using TSQL, you're better off using a cursor for that sort of thing. I only use UPDATE FROM when I need a join to formulate the

[sqlite] Dotnets library System.Data.SQLite.Core doesn't convert DateTimes correcly

How are you storing the dates in the DB? Is the WHERE clause using the same format? Given SQLite's "duck" typing, I don't believe there is any built in definition of a date time type. MSSQL, for instance, has a built in "datetime" type, and when you build a WHERE clause with, e.g. a string,

[sqlite] Pascal (almost) style BLOBs

An ADO.NET DataTable *can* have type information for table columns, if you add a parameter to the WriteXml method to have it write the schema along with the data. Obviously, *their* data must be typed from their source, or be inferred, for this to work. See if your provider can do this for

[sqlite] System.Data.SQLite version 1.0.100.0 released

It's *all* 1s and 0s. > On Apr 15, 2016, at 12:46, Tim Uy wrote: > > that is a lot of 1s and 0s. > >> On Fri, Apr 15, 2016 at 10:42 AM, Joe Mistachkin >> wrote: >> >> >> System.Data.SQLite version 1.0.100.0 (with SQLite 3.12.1) is now available >> on the System.Data.SQLite website: >> >>

Re: [sqlite] How about a proper forum rather than an e-mail list

On Tue, Oct 18, 2011 at 8:04 AM, Igor Tandetnik wrote: > Frank Missel wrote: >> I think that the sqlite-users e-mail list has enough traffic to warrant a >> proper forum. > > For what it's worth, I'm using GMane (http://gmane.org/), which is a mailing >

Re: [sqlite] Database schema has changed?

> Mmmm. Looks like there's no elegant way to do it. I looked into this a couple > of years ago when designing the setup. So: > > 1) Leave things as they are. Downside is the unexplained error every few > months and it's a slightly clumsy method. Upside is if the schema changes > there's no

Re: [sqlite] how to compare time stamp

>        • TEXT as ISO8601 strings ("-MM-DD HH:MM:SS.SSS"). I personally have had the best luck with this storage mechanism. I'm lazy, and my platforms all understand this format readily, and it has the advantage of being human readable as well.

Re: [sqlite] saving pragma states in database

You could always create a table that stores the pragma values in which you're interested, then have code that checks on start up to set those pragmas to those values. On Tue, Aug 23, 2011 at 1:24 PM, Erik Lechak wrote: > Hello all, > > Is there a way to save pragma states to

Re: [sqlite] EXT :Re: Last record in db

On Mon, Aug 22, 2011 at 10:08 AM, Black, Michael (IS) wrote: > Brad got it: >> select * from t1 where rowid = max( rowid ) ; >> Error: misuse of aggregate function max() > sqlite> select * from t1 where rowid = (select max(rowid) from t1); > 3|three > > Why is max(rowid)

Re: [sqlite] Last record in db

What happens when you do: select * from t1 where rowid = (select max( rowid ) from t1); or select * from t1 where rowid in (select max( rowid ) from t1); On Mon, Aug 22, 2011 at 10:01 AM, Cousin Stanley wrote: > > Black, Michael (IS) wrote: > >> select * from table

Re: [sqlite] How to get PRIMARY KEY of newly INSERT INTO record

> Cool! That worked. The VB6 wrapper has a LastInsertAutoID that I never paid > attention / saw before. Be sure that it's doing what you think it is. If it's a general purpose wrapper, then it might not. If it's a SQLite specific wrapper, it might, but even if it's wired up correctly, you have

Re: [sqlite] How to get PRIMARY KEY of newly INSERT INTO record

>      'Create the SQL command. >      strSQLCommand = "INSERT INTO " + strDBTable + " VALUES (null" + > strUpdate + ");" http://www.sqlite.org/lang_corefunc.html Use last_insert_rowid() as a second statement in your query: 'Create the SQL command. strSQLCommand = "INSERT INTO " + strDBTable +

Re: [sqlite] set a Trigger on select

> Among the whole paramitration there are some "parameters" which are > actually reads to specific hardware addresses. If these values are to be read directly from the hardware, why involve the database at all? Why not simply have your application code read them directly, instead of trying to

Re: [sqlite] Is UPDATE with JOIN supported?

> I am trying to do an UPDATE of one table based on the aggregate > results of the different table. How can I do it in SQlite please? What have you tried that didn't work? /bs ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] drop table question ?

>> >> For my own edification, why the "order by 1" clause? >> > >> > To sort them in ascending order of table name, which might make >> > old-fashioned capers like visual scrutiny a little easier. >> >> OK then, why would one not use the column name? > > It does. No, I meant why not use the

Re: [sqlite] drop table question ?

On Tue, Feb 3, 2009 at 10:28 AM, Jay A. Kreibich <j...@kreibi.ch> wrote: > On Tue, Feb 03, 2009 at 08:37:10AM -0500, Brad Stiles scratched on the wall: >> >> For my own edification, why the "order by 1" clause? >> > >> > To sort them in ascendi

Re: [sqlite] drop table question ?

>> For my own edification, why the "order by 1" clause? > > To sort them in ascending order of table name, which might make > old-fashioned capers like visual scrutiny a little easier. OK then, why would one not use the column name? /bs ___

Re: [sqlite] drop table question ?

> sqlite> select 'drop table ' || name || ';' from sqlite_master where > type = 'table' and name glob 'X[0-9][0-9][0-9][0-9]' order by 1; For my own edification, why the "order by 1" clause? /bs ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] How many tables can a database hold?

> Can someone tell me how many tables a given database can hold. Try here: http://www.sqlite.org/limits.html > I'm looking at an initial design of an application that could have a table > of data for each city in a state. This could be possibly more than a > thousand tables. Is the data stored

Re: [sqlite] Deleting duplicate records

> CREATE TABLE dup_killer (member_id INTEGER, date DATE); INSERT INTO > dup_killer (member_id, date) SELECT * FROM talks GROUP BY member_id, > date HAVING count(*)>1; > > But, now that I have the copies in the dup_killer table, I have not > been able to discover an efficient way to go back to the

Re: [sqlite] nested transactions

> This will be a point release: 3.6.8. There are no > incompatibilities. An important aspect of our social contract is > that SQLite continues to be compatible moving forward. There are > hundreds of millions of SQLite3 databases in the world, and we do > not want to abandon them. Software

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

> That, unfortunately, leads directly to the follow-up question of > "can BIGINT PRIMARY KEY AUTOINCREMENT" be made to work the same as > INTEGER PRIMARY KEY AUTOINCREMENT". I believe the answer is yes, but > I wouldn't bet my life on it. If I knew anything at all about SQLite, I'd probably say

Re: [sqlite] Audit

> I'd like ideas / recommendations on implementing and auditing to track delta > changes to tables. Here's one possibility for auditing: http://www.sqlite.org/cvstrac/wiki?p=UndoRedo Another possibility is to have audit tables are identical copies of the tables being tracked, except for some

Re: [sqlite] Audit

> I'd like ideas / recommendations on implementing and auditing to track delta > changes to tables. Here's one possibility for auditing. http://www.sqlite.org/cvstrac/wiki?p=UndoRedo Another possibility is to have audit tables are identical copies of the tables being tracked, except for some

Re: [sqlite] Struggling with datetime("now") > MAX(dtEndDate) query - Please Help

> SELECT * > FROM MyTableWithDates > WHERE datetime("now") > MAX(dtEndDate) What is it that you are actually trying to do with this query? As formulated (even if it were syntactically correct, which I don't think it is), you are either going to get every row in the table, or no rows at all.

Re: [sqlite] Determine number of records in table

> > select max(rowid) from sometable; > > Looks good and is instantaneous. Thank you very much. And will only work if you never delete any rows from the table. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Problem selecting the empty string in a column

> I am *sure* that I am overlooking the obvious... > > I have a need to identify blank columns in my tables, and have been unable > to find a suitable query, eg > > SELECT * from table WHERE column = ''; > > What am I doing wrong here? It depends on what "blank" means? Does it mean an empty

Re: [sqlite] Terrible performance for one of our tables

>> Out of interest why must it completely READ each entire row? Is it >> because '*' has been used? > > The database reads a page at a time. A page is, by default, 1024 > bytes. A single page might contain multiple rows, or a single large > row might be spread across multiple pages. > When

Re: [sqlite] OLE DB provider for SQLite

> The problem with performance is not caused directly by SQLite provider, but > by different > way of reading data from managed (ADO.NET) providers by Analysis Services. Ah. "Analysis Services". 'Nuff said. Missed that the first time around. :) ___

Re: [sqlite] OLE DB provider for SQLite

> PS Managed (ADO.NET) providers do not satisfy requirements for > performance reasons. That's a pretty blanket statement. I've found Robert Simpson's ADO.NET provider to be very performant. He has a benchmarking suite he wrote to compare various DBs. It might still be available for download.

Re: [sqlite] offical full list of valid data types?

>I found that the ado.net provider for sqlite support some types that do > not really match the substrings here > http://sqlite.phxsoftware.com/forums/t/31.aspx That wrapper does some mapping between a type that's declared in the table definition, and the types used in .NET programs. So, if

Re: [sqlite] Unhappy with performance

>> Are you able to benchmark it using an actual PC's local hard drive? >> Just for comparison. To be fair, you'd have to use the same build of >> sqlite, or at one that was built the same way. > > That would be quite an effort. Just a thought. Since the build for your device is likely to be

Re: [sqlite] Unhappy with performance

> > What happens when you run the update inside a transaction? > I tried it like this: > > time sqlite3 kfzdb 'begin ; update kfz set musttrans=5 ; end' > No significant change in runtime either. Are you able to benchmark it using an actual PC's local hard drive? Just for comparison. To be

Re: [sqlite] Unhappy with performance

> The problem is with bulk-updating: > > > # time sqlite3 kfzdb 'update kfz set musttrans=3' What happens when you run the update inside a transaction? ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Update question

> My stab at an SQL statement to pull this change off is as follows: > UPDATE > Events SET Return = date(d, n + ' days') WHERE pkIndex IN (SELECT > pkIndex, Nights AS n, Departure AS d FROM Events WHERE Return = '' AND Nights > != '') Why do you think you need the sub-select? Assuming that they

Re: [sqlite] howto setup SQLite with Powershell ?

>> access ... dll from PowerShell ... PowerShell docs > > the procedure given there is to register the dll with installutil. The use of the word "register" implies to me that they might be assuming a COM dll or .NET com visible assembly. I don't believe the SQLITE3.DLL qualifies as either. :)

Re: [sqlite] howto setup SQLite with Powershell ?

> The original problem is given in the topic. > The System.Data.SQLite provider (which I currently can't get running) is > hopefully only one solution. You mentioned the System.Data.SQLite provider in every message you posted, and presented it in such a way that I interpreted you to have asked

Re: [sqlite] howto setup SQLite with Powershell ?

> btw: if I rerun InstallUtil I now get a message: > No public installers with the RunInstallerAttribute.Yes attribute could be > found in the ...\System.Data.SQLite.dll assembly. You should really be asking these questions on the forum/list dedicated to the support of the product you are

Re: [sqlite] Correct SQL name resolution on AS clauses in a SELECT?

D. Richard Hipp <[EMAIL PROTECTED]> wrote: > CREATE TABLE t1(a INTEGER, b INTEGER); > INSERT INTO t1 VALUES(1,2); > INSERT INTO t1 VALUES(9,8); MSSQL Server 2000 > SELECT a AS b, b AS a FROM t1 ORDER BY a; b a --- --- 1 2 9 8 >

Re: [sqlite] Currency Issues

> jonwood wrote: > I've done very little programming related to currency (dollars, > etc.) and I'm wondering if I need to worry about rounding errors. > Since SQLite doesn't appear to have a currency type, I had planned > on using REAL instead. But I have a lot of reports to print out and > I

Re: [sqlite] What is quicker?

> Is this how you expect the RTree tables to be used in a case like the OP > is interested in? > > create table City ( > id integer primary key, > nametext, > lat real, > longreal, > class integer > ); > > create

Re: [sqlite] Improvment suggestion for "BOOLEAN"-type fields

> Taking into consideration a declared close relativity between SQLite and > TCL, I would to suggest an improvement in boolean-type fields treatment. > In my opinion, field of that type should be treated equally, when it does > contain a values: "f", "false", 0, "no" - and, respectively: "t",

Re: [sqlite] Why doesn't "where =" work for text field

[EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >> select name, length(name), hex(name) >> from PerfTest1 >> where name like '%key5000%'; > Returns: 'key5000'|9|276B65793530303027 Does the value of the field really contain the single quotation marks, as opposed to being just a delimiter? That

Re: [sqlite] Generating new rowid algo

> > I wanted to know the algorithm used by sqlite to generate the new > > rowid. Assume there can be N distinct rowid's possible, now insert N > > records, followed by random deletion of some records. Now what rowid > > will be assigned to a new row that is added? >

Re: [sqlite] Binding values for IN ?

> I also had some rather complex piece of SQL from "The Art of SQL", which > took a string and dissected it in place with subqueries and a join to a > pivot table, but wondered if there was an easier way to do it? If you already have that information in an array in your application code, it

RE: [sqlite] Date Problems

> Can somebody give any explain to this please. > sqlite> select date("2006-03-31"); > 2006-03-31 > --> correct > > sqlite> select date("2006-03-31", "-1 month"); > 2006-03-03 > --> not correct > > > Can anyone confirm? Any suggestions / workarounds greatfully > received! I've seen other

RE: [sqlite] BOOLEAN DEFAULT VALUE

> I got a BOOLEAN field. It's defined: NOT NULL. > > when a new record is inserted, how to put a Boolean Default Value > as FALSE? By reading the documentation. http://www.sqlite.org/lang_createtable.html http://www.sqlite.org/lang_createtrigger.html Brad

Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

> Could someone please post the results of these queries on Oracle, > DB2 or SQL Server? On MS SQL Server 2000, your queries result in the following: a c --- --- 2-998 1 3 (2 row(s) affected) foo ---

Re: [sqlite] Disk caching impacts performance.

> There is a physical constraint here. If you want to verify that your > data is safely written to non-volatile storage you have to live with the > latency. If that is unimportant to you you can relax the ACID > requirements and get faster writes, but when you do that there is no > crying

Re: [sqlite] Aggregate function as column

> SELECT number, name, (SELECT COUNT(*) FROM pet WHERE employee.number = > pet.number) AS pets FROM employee That seems like it should work, provided the tables have the definitions implied by the query. What error are you getting? Or you could try something like this completely

Re: [sqlite] improving performance on SELECT

> I'm trying to improve SELECT queries on a db I created. > > Here's the part I think is relevant: > > SELECT fid, man_fsetid, pmfeature.allele, pmfeature.strand FROM > featureSet, pmfeature WHERE man_fsetid IN () AND > pmfeature.fsetid = featureSet.fsetid ORDER BY fid > > That list usually

Re: [sqlite] SQL-92 Syntax Question

> SELECTT1.COLUMN, T2.COLUMN > FROM TABLE1 T1, TABLE2 T2 > WHERE T1.COLUMN *= T2.COLUMN Sorry about the fist one. New web mail client... Anyway, if I understand what you're saying, I believe what you want is an OUTER JOIN. SELECTT1.COLUMN, T2.COLUMN FROM TABLE2 T2

Re: [sqlite] SQL-92 Syntax Question

> The SELECT I have is: > > SELECTT1.COLUMN, T2.COLUMN > FROM TABLE1 T1, TABLE2 T2 > WHERE T1.COLUMN *= T2.COLUMN > > In SQL Server, the *= indicates a forced inner join which would cause > a record to be generated regardless if it existed in the T1 table or > not. If I

Re: [sqlite] Creating index takes over an hour

> > > cat your.db > /dev/null > > > > > Using Windows XP. :-0 > > Anyone know a simple Windows command line equivalent of the cat > to dev null command above to put a file into OS cache? Well, 'type your.db > nul' will do the same thing, though whether or not it will remain in the cache

Re: [sqlite] Math functions

> On 4/4/07, Nathan Biggs <[EMAIL PROTECTED]> wrote: > Does anyone know if there is a floor function in sqlite, or of a way to > implement it. > Well, the suggestions about extending SQLite are probably quicker, but if a SQL solution is required for portability, you can try something like this:

Re: [sqlite] Index creation on huge table will never finish.

Gerry Snyder <[EMAIL PROTECTED]> wrote: > Chris Jones wrote: > Hi all, > I have a very simple schema. I need to assign a unique identifier > to a large collection of strings, each at most 80-bytes, although > typically shorter. Would it help to hash the strings, then save them in the DB,

Re: Re: [sqlite] Use "computed" fields to get the value of other fields.

> SELECT TotalInvoice, (SELECT SUM(PaymentValue) FROM Payments > WHERE Payments.IDInvoice = IDInvoice) AS TotalPaid, TotalPaid = TotalInvoice > AS FullyPaid FROM Invoices; > > Here, I select: > - TotalInvoice the total amount of the invoice > - TotalPaid the total amount paid till now > -