Re: [sqlite] [OT] mySQL ORDER BY clause in Views

Thanks to all for the input. To clarify, yes the ORDER BY clause is in the view definition and the SELECT statement that addresses the view does not have an ORDER BY clause. I think the penny has finally dropped for me on this issue. The key was the description by a responder that views are not

Re: [sqlite] [OT] mySQL ORDER BY clause in Views

tents of sqlite-users digest..." > > > Today's Topics: > >1. [OT] mySQL ORDER BY clause in Views (Peter Haworth) >2. Re: [OT] mySQL ORDER BY clause in Views (Simon Slavin) > > > ---------- > > M

[sqlite] [OT] mySQL ORDER BY clause in Views

I am in the process of converting an SQLite database to mySQL. The SQLIte db includes several views with ORDER BY clauses that have always returned qualifying rows in the correct order. I am discovering that in mySQL issuing a SELECT statement against these same views works fine in terms of the

[sqlite] sqlite-users Digest, Vol 93, Issue 4

Thanks for the info. Could I get round the single value limitation with something like: IN ((SELECT 'abc','def' FROM myTable)) Also, in the LIKE example, looks like I don't need single quote delimiters around the pattern? > > Message: 4 > Date: Thu, 3 Sep 2015 12:54:46 -0400 > From: Richard

[sqlite] Variables in statements

I use a high level language to write my db applications (Livecode). It permits the use of replacement opertaors in sql statements, e.g. "SELECT * FROM myTable WHERE myKey=:1". I guess that's a standard way of doing things in SQLite. I'm having some issues with this and not sure whether it's a

[sqlite] sqlite3 .dump

Couple of questions regarding the above command. Why does the above command include a PRAGMA foreign_keys=OFF command? Wouldn't it make more sense for the CREATE commands to include IF NOT EXISTS? Pete lcSQL Software Home of lcStackBrowser

[sqlite] sqlite3 and Unicode

Thanks for all the replies. I'm still confused on this. It sounds like maybe UTF8 is the answer on OSX but UTF-16LE on Windows. Whatever the answer to that, it seems there isn't a way to control the encoding within sqlite3 so I guess I won't worry about it. Pete lcSQL Software

[sqlite] sqlite3 and Unicode

When using the .dump command with .output to a filename, what encoding does sqlite3 for the file? Same as the database encoding? Is it possible to change whatever encoding is used? Similarly, when using the .import command, does sqlite3 assume the input file is the same encoding as the database?

[sqlite] UPDATE Problem

Thanks to all for explaining my confusion. The printf solution seems like the best way to handle this since I don't need to worry about how many decimal places are in the number.

[sqlite] UPDATE Problem

I have a table, Transactions, with a column , Value, of type NUMERIC. The Value column is supposed to have 2 decimal places in all rows but some have only one. To correct this I issued the following command: UPDATE Transactions SET Value=Value||0 WHERE substr(Value,-2,1)='.' No errors on

[sqlite] [ANN] SQLiteAdmin V1.3.6

t onto the SQLiteAdmin application window. Peter Haworth lcSQL Software www.lcsql.com

Re: [sqlite] Encoding question

On Mon, Feb 2, 2015 at 9:00 AM, wrote: > From: RSmith > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Encoding question > Message-ID: <54cebb71.8060...@rsweb.co.za> > Content-Type: text/plain; charset=windows-1252; format=flowed > > In

[sqlite] Encoding question

I'm new to the unicode world so this question may not make sense. The "PRAGMA encoding" statement tells me the encoding of a database. Can I rely on all data in the database having that encoding? For example, if the encoding is UTF8 and a row is inserted containing UTF16 encoded data, will it

Re: [sqlite] Detecting multiple CHECK failures

On Thu, Oct 9, 2014 at 9:00 AM, wrote: > Message: 6 > Date: Wed, 8 Oct 2014 14:40:57 -0500 > From: Nico Williams > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Detecting multiple CHECK

Re: [sqlite] Detecting multiple CHECK failures

Hi Simon, Actually, I do get the CHECK constraint name returned to me in the error message otherwise, as you say, it would be impossible to find out what failed. I use a translation table in my application to reformat the SQLite error message to a more suitable format to present to my users based

[sqlite] Detecting multiple CHECK failures

I'm a great believer in using CHECK constraints to do as much validation as possible within the database rather than code it in my application. However, I think I'm right in saying that as soon as a CHECK constraint fails, an error is returned to my application so no other CHECK constraints are

Re: [sqlite] Adding WHERE to query --> database disk image is malformed

> > > SELECT * FROM itemTable WHERE +key = 'profileName' ; > > Note the + sign - this suppresses the use of index. > -- > Igor Tandetnik > That's really interesting - I've never seen that use of the "+" sign mentioned in the docs. Pete lcSQL Software Home of lcStackBrowser

[sqlite] [ANN] SQLiteAdmin V 1.3.5 Released

SqliteAdmin V 1.3.5 is now available for download at www.lcsql.com/sqliteadmin.html. This version includes support for several recent language additions such as CREATE TABLE WITHOUT ROWID and the CREATE INDEX WHERE clause. Several other enhancements are included and the full release notes can be

[sqlite] PRAGMA foreign_key_list

The documentation for the above command on the SQLite web site is silent about the output of the above PRAGMA. I can guess at some of it but does anyone have a full description of it? Thanks, Pete lcSQL Software Home of lcStackBrowser

[sqlite] Foreign Key errors

It seems that foreign key errors on columns where the foreign key definition has a constraint name don't include the constraint name in the error message. This is using sqlite version 3.8.3.1. Is this under the control of a compile switch or PRAGMA or am I stuck with the way it is? Pete

[sqlite] When to open/close connections

There's a discussion on another forum I'm on about whether it's good practice to open an SQLite database as part of program initialization and close it when the program terminates, or whether the connection should be opened and closed around each transaction. I've always used the first approach

[sqlite] Still trying to track down loadable extensions

I'm still hunting for loadable extensions. The SQLite web site makes reference to extensions being part of the source code in the contrib folder but when I browser around there, I can only see 2 files dlmalloc and sqlcon. I know nothing about git so can someone please point me in the right

Re: [sqlite] Loadable extensions

ype: text/plain; charset=us-ascii > > > On 17 Feb 2014, at 6:33pm, Peter Haworth <p...@lcsql.com> wrote: > > > Is there a list of available loadable extensions for functions, virtual > > tables, etc? > > I've seen some extensions lists on the web but n

[sqlite] Loadable extensions

Is there a list of available loadable extensions for functions, virtual tables, etc? Pete lcSQL Software Home of lcStackBrowser and SQLiteAdmin ___

Re: [sqlite] RFE: Rename Column

> > > > You're both right. Igor's statement pretty-much /is/ the reason one > cannot rename a column. One would need to write a parser and changer for > SQL statements that could identify and change column names in many > statements with all sorts of weird possibilities for formatting. > > Two

[sqlite] 3.8.1

I've seen emntion of version 3.8.1 of sqlite - is there a document somwhere that describes the changes? Thanks, Pete lcSQL Software ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)

I have a tool that will do this for you and just about any other schema changte you can think of while preservbing the data and integrity of your database. Runs on WIndows and OSX and I could produce a Linux version if necessary. Check out SQLiteAdmin at www.lcsql.com Pete On Thu, Sep 19, 2013

Re: [sqlite] UPDATE question

I do need to maintain the uniqueness of those two columns. I suppose I could drop the UNIQUE constraint an check for uniqueness in my code but I'm a great believer in having sqlite do as much as possible for me. The "Blue" vs "blue" issue is addressed by COLLATE NOCASE (at least I think it is -

Re: [sqlite] UPDATE Question

Thanks Marc and all who responded. Marc's suggestion seems to come the closest to what I was looking for. I did try one other thing which I thought might solve the problem. Instead of defining the Name/Sequence as UNIQUE, I set up a UNIQUE index for those columns and defined both of them to be

[sqlite] UPDATE question

I have a table with the following (simplified) structure PKeyINTEGER PRIMARY KEY NameTEXT Sequence INTEGER The Name/Sequence pair of columns is defined as UNIQUE I need to insert a new row into a point between two sequence numbers. For example, if the existing rows are: Name Sequence

Re: [sqlite] pragma table_info(database.table) not supported

I found the code in the two attached files (are they allowed on this list?) on the web a while back. The claim was that it created an information schema database from an sqlite db. I know nothing about Lua but I managed to get as far as creating the schema. Unfortunately, the url I got it from

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

Seems like there are several ways to skin this cat. The problem I have with CHECK is the generic error message it produces. If a table has several columns with CHECK statements, how does my application know which one failed? I favor the trigger approach because I can define a meaningful error

Re: [sqlite] sqlite-users Digest, Vol 63, Issue 2

Hi Igor, Foreign keys are about maintaining the integrity of your database not performance so using a foreign key and creating an index are two different subjects. The foreign key will prevent an application from creating entries in the table that don't have a matching entry in the parent end of

Re: [sqlite] Is there a way to create a foreign key in existing table?

And remembering that you will lose any indexes or triggers defined for the table by doing this. There are plenty of third party tools out there that will take care of adding/removing constraints to existing tables and a whole lot of other functions that aren't available in SQLite's DDL, while

Re: [sqlite] INSERT OR REPLACE

Thanks SImon. Pretty much what I expected, just checking if I was missing something obvious. Pete lcSQL Software On Sun, Dec 16, 2012 at 9:00 AM, wrote: > I think you 'should not use'. You can do an UPDATE and see whether it > updated

[sqlite] INSERT OR REPLACE

If I understand the docs correctly, the REPLACE part of an INSERT OR REPLACE command deletes a row if a UNIQUE constraint occurs and inserts a new row in its place. On the face of it, that seems like it will invalidate any foreign key definitions that use the primary key of the INSERT OR REPLACE

Re: [sqlite] Subject: Re: Simple SQLite-based spreadsheet?

Hi Gille, As others have mentioned, there are several third party products out there that will do something similar to what you want. My SQLiteAdmin tool has a grid view of data in a table. You can't edit directly in the table but below the table is an area that you could think of as something

Re: [sqlite] sqlite-users Digest, Vol 60, Issue 3

The method in the faq is somewhat simplistic. For example, if you have indexes or triggers associated with the table, you'll lose them. It may or may not be a big deal to reinstate them manually but be prepared to have to do that. Or you could use one of the many third party tools that will do

Re: [sqlite] Why can't SQLite drop columns?

Pete lcSQL Software You're probably already aware of this but there are third party tools available that will do this for you, plus many other schema maintenance functions that aren't provided in sqlite. One such is my SQLiteAdmin program, available at www.lcsql.com. On

Re: [sqlite] Alter table constraint question

Hi Andrea, There are several commercial products that will provide this capability along with many other schema maintenance functions not available in sqlite itself. I have one such available, SQLiteAdmin, at www.lcsql.com. Pete lcSQL Software On Sun, Sep 9, 2012 at 9:00

Re: [sqlite] How do you load a ".csv" and skip the first line?

Take a look at my SQLiteAdmin tool. It will import csv files with or without headers, export them that way too, plus many features to help create and maintain your schema and browse/edit your data. Avaialble at www.lcsql.com. Pete lcSQL Software On Sun, Sep 2, 2012 at

Re: [sqlite] Selecting from a GROUP Select

t; > > Content-Type: text/plain; charset=ISO-8859-1 > > I guess you need something like > > SELECT * from TableA > WHERE z in ( > SELECT z FROM TableA GROUP BY z HAVING count(*)=2 > ); > > > Pavel > > > On Fri, Jun 22, 2012 at 6:43 PM, Peter Haworth

[sqlite] Selecting from a GROUP Select

I have a situation where I need to select entries based on the count of the number of entries in a GROUP = 2, and also that the value of a specific column must be different for each row in the group. I then need to select all the individual rows from the qualifying groups. Getting the groups with

[sqlite] Counting entries returned from a compound SELECT.

Hoping someone can provide a way to return a count of the number of entries returned by a compound SELECT statement, specifically "SELECT …. EXCEPT SELECT….". Thanks, Pete lcSQL Software ___ sqlite-users mailing list

[sqlite] FTS4 Questions

I'm testing out the capabilites of fts4 virtual tables. First question concerns the snippet() function. Unless I'm doing something wrong, it appears snippet() only returns one snippet of text containing the requested string from each row in the table irrespective of how many instances of the

[sqlite] [ANN] SQLiteAdmin Version 1.3.0 Released

I'm pleased to announce the availability of version 1.3.0 of SQLiteAdmin, an administration and data maintenance tool for SQLite databases. This update is free for existing customers. Version 1.3.0 includes the following enhancements: - Support for the creation and browsing of Full Text Search

Re: [sqlite] What does "The use of the amalgamation is recommended for all applications." mean, precisely?

Just picking a random post to make my reply to. I truly wish I could get access to an external sqlite library to load at runtime. I use e devlopement language that has the sqlite library built into it so I am at the mercy of the language provider as to what is provided. Right now, they are

Re: [sqlite] sqlite-users Digest, Vol 53, Issue 22

Thanks Donald. I have a utility that imports csv files to sqlite so just trying to get a handle on what I need to deal with! Pete lcSQL Software On Wed, May 23, 2012 at 9:00 AM, wrote: > Message: 15 > Date: Wed, 23 May 2012 08:11:17

Re: [sqlite] csv test cases (was Details On New Features)

Donald, I have a question about #9 of your test cases. According to RFC 4180, #9 is an invalid record. The RFC states "If fields are not enclosed with double quotes, then double quotes may not appear inside the fields." However, I imported your test cases into Open Office, Excel, and Numbers

Re: [sqlite] sqlite-users Digest, Vol 53, Issue 8

Peter, I think you're the perfect candidate for using one of the many sqlite gui admin tools out there. There are lots of them and they all do a good job of getting you off the ground when you are a newcomer to sqlite, but I have to shamelessly promote my own tool, SQLiteAdmin. I think it would

Re: [sqlite] [ANN] SQLiteAdmin Released

: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Subject: Re: [sqlite] [ANN] SQLiteAdmin Released > Message-ID: <4c1c8972-0f24-4f6f-b017-36a7da66d...@bigfraud.org> > Content-Type: text/plain; charset=us-ascii > > > On 7 May 2012, at 4:54am, Peter Haworth &l

[sqlite] [ANN] SQLiteAdmin Released

I'm pleased to announce the general availability of SQLiteAdmin, an SQLite database administration tool for Mac and Windows. A 30-day demo of the application is available for download here. To mark the launch of SQLiteAdmin, I am making it available for $9.99

[sqlite] STandard Deviation

Does Sqlite have a STDEV function? Don;t see it listed under the core or aggregate functions. Thanks, Pete Haworth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 23, Issue 15

Thank you Kees. While not achieving exactly what I was thinking of (the total is in an extra row at the end of the selected rows rather than a column in each row), this will work for me. Pete Haworth http://www.mollysrevenge.com http://www.sonicbids.com/MollysRevenge

Re: [sqlite] sqlite-users Digest, Vol 23, Issue 15

Interesting you should classify my data need as a waste without knowing anything about my application. What if I want to calculate a percentage that the first column is of the total - would it still be a waste to calculate the total? As far as I'm concerned , the more data manipulation

Re: [sqlite] sqlite-users Digest, Vol 23, Issue 15

ain; charset=ISO-8859-1 > > On Sat, Nov 14, 2009 at 1:58 PM, Peter Haworth > <p...@mollysrevenge.com> wrote: >> I'm trying to get a SELECT statement in the following general form >> to work: >> >> SELECT CASE WHEN THEN ELSE ? >> END AS >> Cal

[sqlite] More on Referring to columns named with AS

After searching around the web, it seems I can't expect the SELECT syntax in my earlier post to work but that repeating the aliased column logic as part of the sum function will work so I tried that and it does indeed total things up for me. But, it now only gives me one row in the result

Re: [sqlite] Output in currency format

Thanks for all the comments on this. Didn't realise there were so many things to worry about when dealing with currency! The system I'm developing is only dealing with US dollars right now but I would hope it might make it's way into other countries at some point. Even with dollars, I

Re: [sqlite] Output in currency format

Thanks for all the advice on this. Just to be clear, I wasn't referring to the accuracy of calculations when I compared the sqlite date/time formatting capabilites to the lack of similar functionality for currency, just the fact that there is a precedent for sqlite providing output

Re: [sqlite] sqlite3 for Mac OSX 10.5

Thanks for all the info. I believe the problem lies within Revolution since I'm pretty sure it includes its own private library of the sqlite code. I've reported it to them and hopefully they will fix it. I understand the reasons for applications having their own copy of the code like

Re: [sqlite] Output in currency format

Seems like I should handle the formatting in my application. Not sure I agree that sqlite is not the place to do output formatting - it provides lots of date and time formatting features so at least in that area, output formatting is available. Thanks also for the info re accuracy/REAL

Re: [sqlite] sqlite3 for Mac OSX 10.5

Yes, the analyzer is the only precompiled binary I see on the sqlite download page The group_concat function works fine in the Firefox SQLite Manager extension on the same Mac where it fails within sqlite3. Also, the development software I'm using (Revolution) also rejects the

[sqlite] sqlite3 for Mac OSX 10.5

sqlite3 is rejecting a SELECT statement that includes the group_concat function saying it's an unknown function, yet the same SELECT statement works fine in the Firefox SQLite Manager extension. The version of sqlite3 on my Mac is 3.4.0 but it looks like the latest version is 3.6.x. Could

[sqlite] Output in currency format

I have a column defined with a type of FLOAT, which I believe is treated as REAL by SQLite. When selecting that column, I would like it to be returned with a leading "$" sign and always have a decimal point and two numbers after the decimal point. I can use concatenation to get the "$"

Re: [sqlite] sqlite-users Digest, Vol 23, Issue 10

Thank you Igor, that's exactly what I need. Pete Haworth On Nov 10, 2009, at 4:00 AM, sqlite-users-requ...@sqlite.org wrote: > You are looking for group_concat (http://sqlite.org/ > lang_aggfunc.html): > > select KeyA, DataA, group_concat(DataB) > from TABLEA join TABLEB on

[sqlite] Multiple joined values in one row

Given the following tables: TABLEA KeyA, DataA TableBKey TABLEB KeyB DataB .. and a JOIN on TABLEA.TableBKey=TableB.KeyB IS there a SELECT statement that returns TABLEA.KeyA, TABLEA.Data,AllDataB, where AllDataB consists of all the values of TableB.DataB strung together? For example, for

[sqlite] Another SELECT/JOIN Question.

Trying to implement the following situation involving 4 tables Customers is the "master table" in that the results should end up with one row for each primary key value in it. I need to sum the values of a column in the Sales table, which has a column that joins to the primary key of

Re: [sqlite] Conditional JOIN

Jay, First, yes I screwed up on the table data examples. The 3/SPECIAL TAbleA values should have shown 2 3/STANDARD TableB entries. My brain is hurting too! Anyway, the main thing is that your latest suggestion works perfectly so thanks for your help, I appreciate it. Pete On Oct

Re: [sqlite] sqlite-users Digest, Vol 22, Issue 74

Thanks for this. I tried the CASE solution in preference to the coalesce solution since I'm more familiar with CASE than coalesce. Your statement that there must be matching TableB entries with PriceTable STANDARD for all values of TableA.ProdID is correct. It almost works but not quite.

[sqlite] Conditional JOIN

Looking for a way to implement the following situation I need to select entries form TableA and TableB. The join needs to happen using two separate fields. One of this fields can be used in the normal way but the other filed needs some special logic The normal join field is ProdID and the