On Mon, Feb 2, 2015 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote:
From: RSmith rsm...@rsweb.co.za
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 short, the
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 still
On Thu, Oct 9, 2014 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote:
Message: 6
Date: Wed, 8 Oct 2014 14:40:57 -0500
From: Nico Williams n...@cryptonector.com
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Subject: 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
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
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 http://www.lcsql.com
Home of lcStackBrowser
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 http://www.lcsql.com
Home of lcStackBrowser http://www.lcsql.com/lcstackbrowser.html
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
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
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
, 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 none of them struck me as
worth bookmarking.
The SQLite site includes a page of contributions, which contains some
Is there a list of available loadable extensions for functions, virtual
tables, etc?
Pete
lcSQL Software http://www.lcsql.com
Home of lcStackBrowser http://www.lcsql.com/lcstackbrowser.html and
SQLiteAdmin http://www.lcsql.com/sqliteadmin.html
___
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
I've seen emntion of version 3.8.1 of sqlite - is there a document somwhere
that describes the changes?
Thanks,
Pete
lcSQL Software http://www.lcsql.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
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
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 - I
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
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
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
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
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
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
Thanks SImon. Pretty much what I expected, just checking if I was missing
something obvious.
Pete
lcSQL Software http://www.lcsql.com
On Sun, Dec 16, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote:
I think you 'should not use'. You can do an UPDATE and see whether it
updated
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
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
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
Pete
lcSQL Software http://www.lcsql.com
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
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 http://www.lcsql.com
On Sun, Sep 9, 2012 at 9:00
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 http://www.lcsql.com
On Sun, Sep 2, 2012 at
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 p...@lcsql.com wrote:
I have a situation where I need to select entries based on the count of
the
number of entries in a GROUP
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
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 http://www.lcsql.com
___
sqlite-users mailing list
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
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
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
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 http://www.lcsql.com
On Wed, May 23, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote:
Message: 15
Date: Wed, 23 May 2012 08:11:17 -0400
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
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
-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 p...@lcsql.com wrote:
A 30-day demo of the application is available for download
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
herehttp://www.lcsql.com/products.html. To
mark the launch of SQLiteAdmin, I am making it available for $9.99 to
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
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 condition THEN calculation ELSE calculation ?
END AS
CalcA, sum(CalcA) AS CalcATotal
I get an error no such column referring to CalcA when
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
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
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
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
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
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
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
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
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
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 $ sign
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
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
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 Customers
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
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.
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
58 matches
Mail list logo