To improve efficiency you could add where 1=2 to avoid returning any
rows. Should just check validity.
On 5/7/2014 8:19 AM, Stephan Beal wrote:
On Wed, May 7, 2014 at 4:57 PM, Simon Slavin slav...@bigfraud.org wrote:
somehow ? Perhaps the ROWID field of a table might have its own
I believe a check constraint with an appropriate typeof comparison has
been suggested for this usage.
On 3/6/2013 6:29 AM, Ryan Johnson wrote:
I would agree that no warning is needed for for columns that don't
state any affinity, or for a non-affinity FK that refers to some PK
with affinity.
http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers
Check out Google for more.
On 12/12/2012 8:54 AM, Tilsley, Jerry M. wrote:
All,
Might be a silly question, but does anyone know if any JDBC drivers exist for
SQLite?
Thanks,
Jerry
Adding the warning to the explain plan output should work well.
...
And yet the coding mistake in the SQL query was very subtle. It makes me
wonder if we shouldn't somehow come up with a warning mechanism in SQLite
to give developers a heads-up on error-prone constructs, such as using ==
This analysis is a good candidate for inclusion in a FAQ or similar
document.
On 9/5/2012 7:28 PM, Keith Medcalf wrote:
sqlite create table alpha (frequency, term);
sqlite create table beta (term, frequency);
sqlite create index betaterm on beta(term);
sqlite .explain
sqlite explain query plan
Are there significant improvements in speed for existing SQL?
How does the compiled size compare with SQLite3?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
We added blob import on an old version of the shell, 3.5.9. Using a
simple HexToByte function.
To function: static int do_meta_command(char *zLine, struct
callback_data *p){
Added:unsigned char * blobBuffer = NULL;
In the loop // * Bind cached values to prepared statement.
Joshua,
It doesn't exist in the standard version. We added it in-house to aid
development and testing. The code I posted was the changes we made to
the 3.5.9 shell.c in addition to adding an existing hex to byte function
from our libraries.
If you can compile a new shell the existing
If you are not wrapping the inserts in an explicit transaction, try that.
On 5/2/2012 9:04 AM, Nigel Verity wrote:
Hi
I am writing an application which requires approximately 50,000 items to be
imported from a text file into a table. Each item is a single string of 8
characters, and the
It is possible using an alias would force better behavior:
selsect theDate
from (select transfer_date as theDate from transfer_history
where regn_no='039540' and transfer_date= '2012-05-01') order by
theDate
___
sqlite-users mailing list
It will definitely run. Pelles is new to me. Our application uses
SQLite 3.5.9 on WinCE 4.2 through 6.5 using C++. I don't recall that we
had to change any source, but we did modify the shell for better input
handling, but there were probably some compiler flags we needed to set.
Been
Don't know about those devices but we use the MC55A and related device
without issue with C++ and I'm pretty sure they support C#.
http://www.motorola.com/Business/US-EN/Business+Product+and+Services/Mobile+Computers/Handheld+Computers/MC55A0
On 2/9/2012 9:24 AM, Richard Hipp wrote:
On Thu,
it on the scan gun?
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jim Morris
Sent: Thursday, February 09, 2012 12:22 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5
It will definitely
Yes, works great!
On 11/11/2011 4:24 PM, Matt Young wrote:
Embedded Sqlite3 questions:
I want to load and prepare multiple statements, keep them prepared and when
I want to use one of them, I will reset, bind and step.
Can pre-prepare multiple independent statements, then run them one at a
On 10/6/2011 10:43 PM, Ivan Shmakov wrote:
Jim Morris writes:
The recent thread may relate: [sqlite] Is there an efficient way to
insert unique rows (UNIQUE(a, b, c)) into an fts3 virtual table?
INSERT INTO fts3_table (a,b,c)
SELECT 'an A','a B','a C'
WHERE NOT EXISTS
The recent thread may relate: [sqlite] Is there an efficient way to
insert unique rows (UNIQUE(a, b, c)) into an fts3 virtual table?
INSERT INTO fts3_table (a,b,c)
SELECT 'an A','a B','a C'
WHERE NOT EXISTS
(SELECT DISTINCT a,b,c
FROM fts3_table
WHERE a='an A' AND b='a B' AND c='a C');
The
This should do it.
select region, count(*) from hosts group by region
union all
select 'Total, count(*) from hosts
;
On 10/3/2011 11:49 AM, James Kang wrote:
select region, count(*) from hosts group by region
___
sqlite-users mailing list
That should be select 'Total', count(*) from hosts
On 10/3/2011 11:52 AM, Jim Morris wrote:
This should do it.
select region, count(*) from hosts group by region
union all
select 'Total, count(*) from hosts
;
On 10/3/2011 11:49 AM, James Kang wrote:
select region, count(*) from hosts group
Your where clause WHERE ItemsME.IDR ... is only satisfied if there is
an associated ItemsME record so the left outer join is pointless. Just
use the inner join. Normally the left outer join would include all of
ItemsME_Properties, that probably explains the table scan.
I'd guess it was for escaping strings used to build SQL statements by
concatenation rather than using prepared statements and binding.
On 8/18/2011 7:12 AM, Simon Slavin wrote:
...
The SQLite3 object API for PHP includes a function SQLite3::escapeString .
The documentation for it doesn't
You can't replace multiple rows in a single insert/update/delete statement.
You might consider copying the duplicates to a temp table, delete them
from the old then use a select on the temp table to generate the new
rows for the old table. The select portion would be something like
select
A union variation that is more amenable to generic selects:
select a,b,c,1 as tablename from real where id=123 and not exists (Select 1
from over where over.id = real.id)
union
select a,b,c,2 as tablename from over where id=123
___
sqlite-users
On 7/15/2011 1:26 AM, Mattia wrote:
- deleting the old database (data.db) and renaming new.db with the
correct name (new.db becomes data.db).
After this step, as a test, reopen the connection to new.db and ensure
that the data is there.
Are you sure a commit is done before closing
The between operator is order dependent. This variation might work:
SELECT d from T_d
inner join (select min(pos) as xMin, max(pos) as yMax FROM T_x WHERE txt
= '1990' OR txt='1991') as xcriteria on xPos between xMin and xMax
inner join (select min(pos) as yMin, max(pos) as yMax FROM T_y WHERE
Or do an update and if no records are modified then do an insert.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
string literals are enclose in single quotes not double quotes
select (select v from t1 where n='a') wrong,* from a1;
On 6/28/2011 11:42 AM, thilo wrote:
select (select v from t1 where n=a) wrong,* from a1;
___
sqlite-users mailing list
Did you try to time a simpler select:
SELECT min(n1, n2) as new1, max(n1,n2) as new2 FROM table1 group by new1, new2
having count(*) 1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
You might also consider a trigger to calculate the distance once on
insert/update. They you could use an index.
On 6/14/2011 4:53 AM, Simon Slavin wrote:
On 14 Jun 2011, at 12:11pm, Martin.Engelschalk wrote:
make sure you have an index on category and distance, like
create index MyIndex
I've only used the xerial driver recently and haven't checked for the
error code on exception. It does come with source, so you may be able
to walk into the code to see how the value is set/retrieved.
___
sqlite-users mailing list
This line seems erroneous: SQLiteCommand sqlCmd(%conn); Isn't the
percent the modulus operator?
Shouldn't it be: SQLiteCommand sqlCmd(conn);
On 6/1/2011 5:06 PM, Don Ireland wrote:
I'm hoping someone can help me with this.
Using Visual Studio C++, the following code DOES create the DB file.
If you just need them in descending order, i.e. not an arbitrary order,
then order by rec desc will work.
On 5/20/2011 7:23 AM, jose isaias cabrera wrote:
Martin Engelschalk on Friday, May 20, 2011 10:21 AM wrote...
Hi,
to order, you have to use order by. In that case, however, it gets
You must use the alias if specified:
select ar.* from aa ar, ab ab1, ab ab2;
rather than
select aa.* from aa ar, ab ab1, ab ab2;
On 5/19/2011 10:33 AM, Matthew Jones wrote:
select aa.* from aa ar, ab ab1, ab ab2;
___
sqlite-users mailing list
Yes, transaction are designed to work with multiple statements.
Begin
Statement 1
Statement 2
...
Statement N
Commit
On 5/17/2011 7:58 AM, StyveA wrote:
Hello again,
I've got an other question about prepare :
Is it possible to make a BEGIN transaction, then prepare many statements
(insert,
I've just started using Xerial also for a small project and have not had
any problems so far. I haven't used any other JDBC wrappers so have no
comparison info.
___
sqlite-users mailing list
sqlite-users@sqlite.org
Are you sure these leaks aren't yours? Although I don't know the Sqlite
internals some of the data values don't seem to be related to Sqlite, like:
c:/DEV/Platform/
PolicyDataPack.i
You can try to use the allocation number to narrow down the code
location. Don't remember the specifics though.
Did you try doubling the apostrophes?
*Goin'' Down the Road Feelin'' Bad*
On 4/17/2011 6:16 PM, Simon Slavin wrote:
On 17 Apr 2011, at 11:54pm, Alan Holbrook wrote:
I'm using SQLite with VBE2008. I've defined a table with a number of text
fields in it. If the information I want to write
Did you want to use correlated sub queries?
Something like:
SELECT c1,c2,
(select sum(t2.c3) FROM t2 WHERE t2.key2=t1.key1) as mySum,
(select count(t3.c4) FROM t3 where t3.key3=t1.key1) as myCount
FROM t1
;
On 4/7/2011 5:31 PM, Pete wrote:
I am trying to use GROUP BY to summarise information
A simple restatement should work:
delete
from xxx
where entry_id in (select
t1.entry_id
from
xxx t1
where not
t1.entry_id in(select
t2.entry_id
from
xxx t2
where
t1.patient_id = t2.patient_id
order by
t2.start_date desc limit 1))
On 3/24/2011 12:00 PM, Bart Smissaert wrote:
delete
from
xxx t1
Make sure your separator is the ,
http://www.sqlite.org/sqlite.html
On 3/10/2011 12:28 PM, jcilibe...@comcast.net wrote:
Hello,
Unbelievably active user group!
I have been unable to import a CSV text file from MS Access to sqlite:
1. Created a small table (3 fields and 1 record) in Access
.separator ,
.import myPath/myCSVfile myTable
We use a import file with these commands.
On 3/10/2011 2:32 PM, jcilibe...@comcast.net wrote:
Thanks, but doesn't seem to work:
I used command
- Original Message -
From: Gerry Snydermesmerizer...@gmail.com
To: General Discussion of
A correlated sub-query might work for you.
SELECT
[Analyzers].[AnalyzerID]
, [Analyzers].[Name] AS [Analyzer]
, [Analysis].[AnalysisID]
, [Analysis].[ScanID]
, [Analysis].[Timestamp]
, [Analysis].[EndTime]
, (SELECT COUNT(*) AS NumDefects
On the MC55 and MC70 we use with Sqlite 3.5.9:
PRAGMA temp_store = MEMORY
PRAGMA journal_mode = PERSIST
PRAGMA journal_size_limit = 50
On 2/16/2011 5:24 AM, Black, Michael (IS) wrote:
Try this benchmark program and see what numbers you get. You need to compare
to other machines with the
I tihnk the firefox plug-in Sqlite Manager does.
On 2/11/2011 2:30 PM, Simon Slavin wrote:
On 11 Feb 2011, at 7:19pm, prad wrote:
does the sqlite3 interface have completion of sql key words?
in postgresql you can type SEL and press tab to complete.
is there such a thing for sqlite3?
sqlite3
If you don't care about the order then use and instead of trigger to
force the ordering then you will get the collisions you expect.
On 2/9/2011 10:12 AM, Black, Michael (IS) wrote:
I have a need to create a unique bi-directional relationship.
You can think of it as pairings of people who eat
Could a backup or virus scanning software be locking the database?
On 2/8/2011 10:12 AM, Nathan Biggs wrote:
I haven't tried that, but if we stop the application then restart it,
everything works again. That is until we get another Disk I/O error
which happens the next day.
I'm wondering if
Only apples
SELECT distinct customerid
FROM Customers c1
WHERE Type = 'Apple' AND not exists (select 1 from customers c2 where
c2.customerid=c1.customerid and not Type = 'Apple')
;
Apples and Bananas
SELECT distinct customerid
FROM Customers c1
WHERE Type = 'Apple' AND exists (select 1 from
I'd probably move the analyze out of the loop.
Since your joining on props.id a better index pind might be
create index pind on props (id, pnam)
The name of column id in table props would be clearer as obj_id since it
is not the id of the property but the id of the record in the obj table.
On
If not already done creating a page of additional collations on the wiki
would make sense and minimize work all around.
I just mailed you an extension for SQLite offering the collation you need.
___
sqlite-users mailing list
sqlite-users@sqlite.org
Did you try a compound index?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
A union of a left and right joins should do it.
On 12/7/2010 4:50 AM, Simon Slavin wrote:
On 7 Dec 2010, at 12:37pm, Pavel Ivanov wrote:
I Writed A FULL OUTER JOIN Query In Sqlite But I Got A Message Like Not
Supported. What Should I Do To Do This
You should re-think once more: do you really
Why are you looking for a duplicate with col4 instead of the unique key,
col2, col3 that caused the collision?
On 12/1/2010 7:29 AM, Hemant Shah wrote:
Folks,
My C program creates a in-memory database. It creates a table and a unique
index on two columns. If the insert fails due to unique
If you have another thread running that deletes or modifies the table,
then move the commit to after the select for duplicate to ensure
transactional integrity.
On 12/1/2010 8:10 AM, Black, Michael (IS) wrote:
The problem is probably in the bind calls that you are not showing.
If you care to
I still think you should use the same columns for searching for the
duplicate that cause the collision. Using col4 seem problematic. Can
you change the code to use col2 and col3?
On 12/1/2010 8:24 AM, Hemant Shah wrote:
This is a single thread/process. No other thread or process is
.value from bar b3 WHERE b3.key = b1.key
except
-- Values common to both foo key and bar key
select f2.value from foo f2 inner join bar b2 on b2.value = f2.value
WHERE b2.key = b1.key AND f2.key= f1.key
);
On 11/19/2010 6:40 PM, Jim Morris wrote:
This should return a the equivalent keys in the two
This should return a the equivalent keys in the two maps. The basic
idea is to compare the values in each key in foo(left outer join foo)
with the values for each key in bar where there are any matching
values(left outer join bar) and only select those with a complete match(
inner join). Not
Can you better explain your intent?
Why are you grouping? This is normally for creating sums, averages,
counts etc.
Do you have a small sample of input vs output desired?
On 11/10/2010 11:11 AM, James wrote:
I've been fighting with this for a couple days now. I've been
searching like mad,
There is no logic way to show you intended result. You need some sort
of data that can be used as a filter.
If you want to filter by color why not add color to the item and use a
WHERE clause? Or maybe style?
___
sqlite-users mailing list
If you would explain why/how the position value is significant that
might help.
I fixed your pseudo SQL to run in SQLite Manager and I don't understand
from the
sample data what your trying to do. There is only one image per item.
Do you have multiple images per item and only want to return the
Not much help but this removes the multiplication:
SELECT B1.B + B2.B + B3.B + B4.B FROM
(SELECT 0 AS B UNION SELECT 1 AS B) AS B1,
(SELECT 0 AS B UNION SELECT 2 AS B) AS B2,
(SELECT 0 AS B UNION SELECT 4 AS B) AS B3,
(SELECT 0 AS B UNION SELECT 8 AS B) AS B4
On 10/15/2010 3:00 PM, Max Vlasov
If there is an index on (name, position) the a where like below might
use it.
A1.name=A2.name and A2.position between( A1.position - 10, A1.position + 10 )
On 8/20/2010 3:54 PM, Peng Yu wrote:
Hi,
I have the following code to search for neighboring positions
(distance=10). But it is slow
Did you try something like(pseudo code):
select * from A inner join B on A.name = B.name AND ( B.left
between(A.left,A.right) OR B.right between(A.left,A.right) )
On 8/13/2010 8:07 AM, Peng Yu wrote:
Hi,
Suppose that I have a table A, each row represents a interval. For
example, the first
What you are trying to do is unclear to me. It seems that table1
doesn't have enough data to unambiguously identify the rows.
On 7/23/2010 8:03 AM, peterwinson1 wrote:
Thanks Eric and Alan for your help. I tried to apply your code to my problem
and it works to a limited extent because the
is can I do this just using SQL?
Jim Morris-4 wrote:
What you are trying to do is unclear to me. It seems that table1
doesn't have enough data to unambiguously identify the rows.
On 7/23/2010 8:03 AM, peterwinson1 wrote:
Thanks Eric and Alan for your help. I tried to apply your
You also need to watch for multiple command separated via ';'
On 7/15/2010 11:36 AM, JT Olds wrote:
I considered that also, but I wasn't sure about whether or not that
guaranteed no disk writes (maybe some sort of function call might be
made there). That also restricts things like the usage of
You need to use the project properties to set the file as a C file and
to not use precompiled headers
On 7/13/2010 5:47 PM, GHCS Software wrote:
What do I need to do to get sqlite3.c to compile in a MFC C++ project
(Visual C++)? If I just add it to the project, I end up getting a
compile
Maybe this?
SELECTwhatever column, min(Count(type_id),n)
FROM foods
GROUP BYwhatever column
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Are you thinking of limit?
On 7/2/2010 9:58 AM, Simon Slavin wrote:
On 2 Jul 2010, at 5:15pm, Peng Yu wrote:
I want to select an entry that appears
=n times and only show n times if it appears more than n times. I
think that group by might help.
There's no simple format which
Try creating a script file something like(Psuedo code):
.separator ,
CREATE TEMP TABLE dummy (value);
.import file1.csv dummy
INSERT INTO TEST (filename,number) (SELECT 'file1.csv', value FROM dummy;
delete from dummy;
.import file2.csv dummy
INSERT INTO TEST (filename,number) (SELECT 'file2.csv',
You also haven't specified a primary key, i.e. on id
On 6/28/2010 11:24 PM, J. Rios wrote:
I have the next table
table1( id INTEGER, name VARCHAR, id2 INTEGER, name2 VARCHAR, year INTEGER
);
I have created the next indexes : index1( name ), index2( id2 ), index3(
name2 );
I don't understand the driver for this but have you considered creating
a function that would be called as part of the insert or trigger that
would have greater access to application/sqlite internal info that might
be used to create a transaction id.
http://www.sqlite.org/draft/wal.html
'a' should be 'as' in the text located in the same directory or folder
a the original database file
Should be located in the same directory or folder as the original
database file
___
sqlite-users mailing list
Is is possible the character encoding is different?
On 4/30/2010 6:59 AM, Adam DeVita wrote:
Is it possible there is a null, tab, newline or other invisible character?
Try
select timeStamp, '' || resourceType || 'xx' From MyTable where
resourceType like 'PSM' LIMIT 10;
On Fri, Apr
Did you try something like:
SELECT id + 1 FROM foo WHERE id= 100 AND id 200 and id = MAX (id);
On 02/22/2010 7:02 AM, ArtemGr wrote:
Simon Slavinslav...@... writes:
Just out of interest, and I know that theoretically this is not an optimal
statement, but have you compared
this with
73 matches
Mail list logo
