On Feb 1, 2007, at 8:19 AM, Ion Silvestru wrote:
If we have a query where we compare a column to a set of values, then
which is faster: OR or IN?
Ex: OR: (mycol = "a") OR (mycol = "b") OR (mycol = "c") OR...
IN: (mycol IN "a", "b", "c" ...)
IN is much faster - OR disables any use of index
If we have a query where we compare a column to a set of values, then
which is faster: OR or IN?
Ex: OR: (mycol = "a") OR (mycol = "b") OR (mycol = "c") OR...
IN: (mycol IN "a", "b", "c" ...)
Thanks.
-
To unsubs
I know how to use ATTACH with sqlite3.exe but I am having problems using
it in C++. I am using a wrapper and what I am trying to do is
illustrated by:
CppSQLite3DB db;
db.open("Stocks.db");
db.execDML("ATTACH 'Options.db' AS OPT;");
sSQL = "UPDATE Stocks SET bOption=1 WHERE rowid IN ";
On Wed, 2007-01-31 at 20:04 -0600, Jay Sprenkle wrote:
> On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >
> > > Please pick a pseudo algorithm of your choice and give us the ability to
> > > seed it?
> > >
> >
> > You already have this capability. Use the redefinably I/O feature
> > to
I want to lock a DB when I access it, in case of two processes write/read it
at the same time.
Forgive my poor english.
Thank you.
"Jay Sprenkle" <[EMAIL PROTECTED]> wrote:
> On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >
> > > Please pick a pseudo algorithm of your choice and give us the ability to
> > > seed it?
> > >
> >
> > You already have this capability. Use the redefinably I/O feature
> > to insert your
> I wonder what the reason was to limit the number of table joins to 32.
http://www.sqlite.org/cvstrac/chngview?cn=3622
Finding fabulous fares is fun.
Let Yahoo! FareChase search your favorite travel sites
Thanks Richard, it worked perfectly.
Roger
[EMAIL PROTECTED] wrote:
UPDATE stocks SET bOption=1 WHERE rowid IN
(SELECT stocks.rowid FROM Stocks, Options
WHERE Stocks.sStockSymbol = Options.sStockSymbol);
--
D. Richard Hipp <[EMAIL PROTECTED]>
--
Hi Jeff,
I've encountered some functions that apparently aren't supported by
SQLite
So have I, such as replacing occurrences of an inner string.
so I've created my own
I've yet to figure out/try that. Is there a library somewhere of
prebuilt functions we can add? Is there and mechanism
I should also mention that I have been able to achieve what I want
using a temp table with an autoincrement field. First I do:
insert into temp_table (id) select id from table_a order by x;
Then I use this table in the join and sort on the autoincrement field:
select
*
from
temp_table
join
On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Please pick a pseudo algorithm of your choice and give us the ability to
> seed it?
>
You already have this capability. Use the redefinably I/O feature
to insert your own random number seeder in place of the one that
comes built in.
On 1/30/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
Last night, a single user (or, at least, a single IP address)
in China that self-identified as running windows98 and
Mozilla 4.0 attempted to download sqlite-3.3.12.tar.gz
24980 times and sqlite-source-3_3_12.zip 25044 times
over about a
From: <[EMAIL PROTECTED]>
I'm not sure what "int()" does. Maybe you are looking for round().
Or perhaps cast(expr AS int) will serve your needs.
Sorry, I should have been clearer. INT just forces the result to be
an integer. So, your "cast" example is probably what I need.
Thanks for the
Hi James,
What is the sqlite concatenation operator?
Ohh, ooh, I actually know this one. Pick me, pick me... ;-)
The concatenation operator (for joining strings) is a double bar: ||
eg:
SELECT 'hi ' || 'there'
gives:
'hi there'
See:
http://www.sqlite.org/lang_expr.html
where it says, in
"Jeff Godfrey" <[EMAIL PROTECTED]> wrote:
> I'm currently converting some Access tables/views to SQLite.
> I've encountered some functions that apparently aren't
> supported by SQLite, so I've created my own (a power function
> and an "IIF" function). Also, several of my queries have a
> basic
"Jeff Godfrey" <[EMAIL PROTECTED]> wrote:
>
> Out of curiosity though, is there a reason why ... a view
> [across multiple attached databases] can't be stored permanently?
When you open a database and first try to use it, SQLite scans
the SQLITE_MASTER table and parses the schema. Views are sto
Roger Binns <[EMAIL PROTECTED]> wrote:
>
> We had an earlier discussion about calling sqlite3_interrupt from
> another thread, which wasn't possible at the time:
>
> It looks like the code is fixed for 3.3.12 (and possibly earlier). I
> just wanted to double check that it is now officially safe
I'm currently converting some Access tables/views to SQLite. I've encountered
some functions that apparently aren't supported by SQLite, so I've created my
own (a power function and an "IIF" function). Also, several of my queries have
a basic int() wrapper, that also seems to be unsupported.
From: <[EMAIL PROTECTED]>
"Jeff Godfrey" <[EMAIL PROTECTED]> wrote:
So, is it not possible to create a view across a "main" and
an "attached" database?
If I recall, you can create a TEMP VIEW across attached databases.
Thanks for the tip. Adding TEMP is all it took to get things workin
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
We had an earlier discussion about calling sqlite3_interrupt from
another thread, which wasn't possible at the time:
http://article.gmane.org/gmane.comp.db.sqlite.general/20427
It looks like the code is fixed for 3.3.12 (and possibly earlier). I
jus
I code in VB and I think I stay out of altering the C source code.
> just do a single REPLACE command with a SELECT on 2 or more subqueries on
> sub-sets of the tables (more efficient).
Will try that one.
> This stands to reason since you're only doing a single lookup per
> sub-table instead of
Roger Miskowicz <[EMAIL PROTECTED]> wrote:
> I am trying to do an update inner join, and have tried the following
> without success. Would someone please tell me the proper syntax for
> sqlite3?
>
>
> UPDATE Stocks, Stocks-- SQL error: near ",": syntax error
> INNER JOIN Options
>
--- RB Smissaert <[EMAIL PROTECTED]> wrote:
> There is one important problem though that I just discovered.
> Just found out that the maximum number of tables in a join is 32!
> So, with my base table that is only 31 to add.
Let's do some grepping...
#define BMS (sizeof(Bitmask)*8)
...
/* T
"Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote:
> Is cast documented on the sqlite website? I couldn't find it.
>
http://www.sqlite.org/lang_expr.html
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, se
I am trying to do an update inner join, and have tried the following
without success. Would someone please tell me the proper syntax for
sqlite3?
UPDATE Stocks, Stocks-- SQL error: near ",": syntax error
INNER JOIN Options
ON Stocks.sStockSymbol = Options.sStockSymbol
SET Sto
Thanks. Unfortunately my background is sybase and that's anything but
standard :-(
-Original Message-
From: Kees Nuyt [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 31, 2007 6:52 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Equivalent syntax?
On Wed, 31 Jan 2007 17:30:29 -05
Thanks, somehow I had missed it.
-Original Message-
From: Nicolas Williams [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 31, 2007 6:43 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Equivalent syntax?
On Wed, Jan 31, 2007 at 06:31:20PM -0500, Anderson, James H (IT) wrote:
> I
On Wed, 31 Jan 2007 17:30:29 -0500, you wrote:
>BTW, what is the concatenation operator?
Standard SQL: string || string
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
On Wed, 31 Jan 2007 18:31:20 -0500, you wrote:
>Is cast documented on the sqlite website? I couldn't find it.
http://www.sqlite.org/lang_expr.html
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTE
On 1/31/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote:
Is cast documented on the sqlite website? I couldn't find it.
..
http://www.sqlite.org/lang_expr.html
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Ge
On Wed, Jan 31, 2007 at 06:31:20PM -0500, Anderson, James H (IT) wrote:
> Is cast documented on the sqlite website? I couldn't find it.
http://www.sqlite.org/
Click on 'syntax', click on 'expression', arrive at:
http://www.sqlite.org/lang_expr.html
-
Thanks,
jim
NOTICE: If received in error, please destroy and notify sender. Sender does not
intend to waive confidentiality or privilege. Use of this email is prohibited
when received in error.
Is cast documented on the sqlite website? I couldn't find it.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 31, 2007 5:51 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Equivalent syntax?
"Anderson, James H \(IT\)" <[EMAIL PROTECTED]
"Shane Harrelson" <[EMAIL PROTECTED]> wrote:
>
> I have two tables, an "Objects" table with a foreign key into a second
> "Strings" table which is composed of unique values. It is a many to
> one relationship, that is, several Objects may reference the same
> String. When an Object is added, it
Experimenting :)
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 31, 2007 5:51 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Equivalent syntax?
"Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote:
> In the case shown, for example,
>
Hello all :)
Imagine the following query:
select
*
from
(
select
id
from
table_a
order by
x
) sub
join table_b on sub.id = table_b.id;
I would like the order that is defined by the results subquery "sub"
to be preserved after the result of the join. What I think I n
OK, thanks, I'll try that.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
Kishor
Sent: Wednesday, January 31, 2007 5:34 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Equivalent syntax?
On 1/31/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
wrot
"Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote:
> In the case shown, for example,
>
> convert(char(3), NULL) CDRefIndustry,
>
> It creates a char(3) column, sets it to null, and names it
> CDRefIndustry.
>
Dennis Cote's guess was mostly right then. A strict equivalent
in SQLite
Hello all :)
Imagine the following query:
select
*
from
(
select
id
from
table_a
order by
x, y, z
) sub
join table_b on sub.id = table_b.id
I would like the order that is defined by the results subquery "sub"
to be preserved after the result of the join. I do realiz
On Wed, Jan 31, 2007 at 05:23:29PM -0500, Shane Harrelson wrote:
>
> I have two tables, an "Objects" table with a foreign key into a second
> "Strings" table which is composed of unique values. It is a many to
> one relationship, that is, several Objects may reference the same
> String. When an
"Jeff Godfrey" <[EMAIL PROTECTED]> wrote:
>
> So, is it not possible to create a view across a "main" and
> an "attached" database?
>
If I recall, you can create a TEMP VIEW across attached databases.
But you can't create a persistent view because such a view would
not make sense to a processe
Peter James wrote:
On 1/30/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
Thoughts anyone? Are there less drastic measures that might
be taken to prevent this kind of abuse?
A couple of people here mentioned CAPTCHA's. This is sort of the
standard
for preventing automated abuse (intenti
On 1/31/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote:
In the case shown, for example,
convert(char(3), NULL) CDRefIndustry,
It creates a char(3) column, sets it to null, and names it
CDRefIndustry.
Since SQLite has no datatypes, char(3) doesn't mean anything to it. How about
On 1/31/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote:
What's the equivalent sqlite syntax for sybase convert function? For
example,
select distinct
date, CDId, CDName, CDTicket,
tradeId, tapsAccount, CDBook, coalesce(CDFid,'') CDFid,
CDStatus, CDTradeDate, CDExpDa
I don't see the cast function listed in the Core Functions section of
the web page...
BTW, what is the concatenation operator?
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 31, 2007 5:21 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Equiv
On 1/31/07, Dennis Cote <[EMAIL PROTECTED]> wrote:
Shane Harrelson wrote:
> On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>>
>> The official way to find the conflicting entry is to do a query.
>>
>> SELECT rowid FROM table WHERE uniquecolumn=?;
>>
>>
>
> Thank you for the reply. I a
In the case shown, for example,
convert(char(3), NULL) CDRefIndustry,
It creates a char(3) column, sets it to null, and names it
CDRefIndustry.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 31, 2007 5:14 PM
To: sqlite-users@sqlite.
Anderson, James H (IT) wrote:
What's the equivalent sqlite syntax for sybase convert function? For
example,
select distinct
date, CDId, CDName, CDTicket,
tradeId, tapsAccount, CDBook, coalesce(CDFid,'') CDFid,
CDStatus, CDTradeDate, CDExpDate, CDNotional,
CDCurr,
Hi All,
I have an open sqlite3 database (name = dbSerial), to which I've attached a 2nd
database (name = dbParent). Now, I'm trying to create a view by joining a view
from dbSerial with another view from dbParent. Attempting to create the view
generates the following error:
Error: view [tco
"Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote:
> What's the equivalent sqlite syntax for sybase convert function?
Can you describe what the convert function in sybase does? That
might help us to find the equivalent function in SQLite for you.
--
D. Richard Hipp <[EMAIL PROTECTED]>
What's the equivalent sqlite syntax for sybase convert function? For
example,
select distinct
date, CDId, CDName, CDTicket,
tradeId, tapsAccount, CDBook, coalesce(CDFid,'') CDFid,
CDStatus, CDTradeDate, CDExpDate, CDNotional,
CDCurr, CDSellBuy, CDType, CDExerType,
Dennis,
Thanks for the timely reply.
max(coalesce(col1, 0), coalesce(col2, 0))
is a lot cleaner than the
max(
case when col1 is null then 0 else col1 end,
case when col2 is null then 0 else col2 end
)
solution I came up with. Though the performance seems to
There is one important problem though that I just discovered.
Just found out that the maximum number of tables in a join is 32!
So, with my base table that is only 31 to add.
This trouble doesn't of course apply to the old UPDATE method.
So, I think after all I need the old way of doing it or what
Hi Shane,
On Wed, 31 Jan 2007 09:29:24 -0500, you wrote:
>On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>> "Shane Harrelson" <[EMAIL PROTECTED]> wrote:
>> > when i try to insert a row into a table that has a UNIQUE constraint
>> > on a column, and I get the SQLITE_CONSTRAINT result co
Shane Harrelson wrote:
On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
The official way to find the conflicting entry is to do a query.
SELECT rowid FROM table WHERE uniquecolumn=?;
Thank you for the reply. I assumed this was most likely the case, and
as I said in my original e
Clark Christensen wrote:
I've read through numerous discussions here about comparing values with null,
and how SQLite functions work with null values, and I thought I understood.
Now it seems appropriate to use the max(col1, col2) function to find the latest
of two dates (integer Unix times),
Actually make that about 5 to 6 times as fast.
RBS
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 31 January 2007 17:39
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Limit statement size?
Can confirm now that the method with INSERT OR REPLACE is faster indeed.
Can confirm now that the method with INSERT OR REPLACE is faster indeed.
My timings tell me it is about twice as fast and that is worth it as that
could be up to a few seconds.
I now wonder if there is an even faster way avoiding all the GROUP tables
and doing it all in one statement.
Thanks again
You need to revise your Java interface or maybe find another. What are
you using?
Jerome CORRENOZ wrote:
Now, I can use user-dfined types by declaring them with SQL create
tables. Fine !
But when I try to use the following code to get an object, I get a
ClassCastException:
Statement stmt
I faced the same problem recently (before I joined this newsgroup). I backed
off from SQL to C++ level,
which was very uncomfortable. It would be very handy if you implement the same
decimal-point parsing
for years too ;)
Best Regards,
Ivailo Karamanolev
On Wednesday, January 31, 2007, 6:04:14
So, it is not a bug but an unadvertised feature. :-)
What will happen most likely? That working with decimals for years will also
be added in the future, or that working with decimals for months and days
will be removed?
Rick van der Lans
-Oorspronkelijk bericht-
Van: [EMAIL PROTECTED] [
<[EMAIL PROTECTED]> wrote on 01/30/2007 03:24:51 PM:
> [EMAIL PROTECTED] wrote:
> >
> > I probably should ask my question this way: What is the *safe* method
for
> > 32 bit machines to build Sqlite 3.3x?
> >
>
> "Safe" is relative. I know of a few companies that use the technique
> I outlined ea
"info" <[EMAIL PROTECTED]> wrote:
> Hi,
>
> If I use the expression datetime('2000-01-01','1.5 months'), SQLite returns
> 2000-02-16 00:00:00. This means that it added one month plus half a month.
> Makes sense.
>
> If I use the expression datetime('2000-01-01','1.5 days'), I get 2000-01-02
> 12:
Ion Silvestru <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Is there a possibility to open a SQLite database read-only so that
> database file will not be locked? This request is for updating
> reasons, to overwrite the database file with a newer version, while
> users have opened the database only for rea
"info" <[EMAIL PROTECTED]> wrote:
> Hi,
>
> The manual states that the function julianday returns the number of days
> since noon in Greenwich on November 24, 4714 B.C. That would imply that the
> statement:
>
> Select julianday('-4714-11-24 12:00:00');
>
> Should return 0.0. But it doesn't, it
Aah, perfect. Thanks for the pointer.
-Clark
- Original Message
From: Dan Kennedy <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, January 30, 2007 10:49:34 PM
Subject: Re: [sqlite] NULL always greater?
The basic rule is that the result of any comparison involving a
NULL
> Your INSERT OR REPLACE statement is in error.
Yes, you are right. In the end it all came down to a simple mistake on my
side. Sorry if I have wasted anybody's time.
Got this all working now and will now see if it is faster than my old
method.
RBS
-Original Message-
From: Joe Wilson [ma
On Wednesday, January 31, 2007 Rick van der Lans wrote:
> The manual states that the function julianday returns the number of days
> since noon in Greenwich on November 24, 4714 B.C. That would imply that the
> statement:
> Select julianday('-4714-11-24 12:00:00');
> Should return 0.0. But it do
Hi,
If I use the expression datetime('2000-01-01','1.5 months'), SQLite returns
2000-02-16 00:00:00. This means that it added one month plus half a month.
Makes sense.
If I use the expression datetime('2000-01-01','1.5 days'), I get 2000-01-02
12:00:00. Again this makes sense: 1.5 days is equal t
Hi,
Is there a possibility to open a SQLite database read-only so that
database file will not be locked? This request is for updating
reasons, to overwrite the database file with a newer version, while
users have opened the database only for read.
Thanks in advance.
Wikipedia also disagrees with the 24. Nov of 4714 B.C. as start of
julian days...
http://en.wikipedia.org/wiki/Julian_day
(Footnote from that page..)
^ This equals November 24, 4714 BC in the proleptic Gregorian calendar.
--
info schrieb:
Hi,
The manual states that the function julianday returns the number of days
since noon in Greenwich on November 24, 4714 B.C. That would imply that the
statement:
Select julianday('-4714-11-24 12:00:00');
Should return 0.0. But it doesn't, it returns -365.0
Does this mean, that
Thanks,
jim
NOTICE: If received in error, please destroy and notify sender. Sender does not
intend to waive confidentiality or privilege. Use of this email is prohibited
when received in error.
On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
"Shane Harrelson" <[EMAIL PROTECTED]> wrote:
> when i try to insert a row into a table that has a UNIQUE constraint
> on a column, and I get the SQLITE_CONSTRAINT result code because i'm
> inserting a duplicate value, is there anyway to det
Hi,
The manual states that the function julianday returns the number of days
since noon in Greenwich on November 24, 4714 B.C. That would imply that the
statement:
Select julianday('-4714-11-24 12:00:00');
Should return 0.0. But it doesn't, it returns -365.0
Does this mean, that the manual shou
"Shane Harrelson" <[EMAIL PROTECTED]> wrote:
> when i try to insert a row into a table that has a UNIQUE constraint
> on a column, and I get the SQLITE_CONSTRAINT result code because i'm
> inserting a duplicate value, is there anyway to determine the rowid of
> the conflict?
>
> looking at the int
"Jay Sprenkle" <[EMAIL PROTECTED]> wrote:
> On 1/29/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >
> > The pseudo-random number generator (PRNG) in SQLite is becoming more
> > important so it seem good to make sure it is well seeded.
>
>
> I'd like to be able to seed the random number gener
Stocks is a table in SOC and Options is a table in Options (Opt).
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
when i try to insert a row into a table that has a UNIQUE constraint
on a column, and I get the SQLITE_CONSTRAINT result code because i'm
inserting a duplicate value, is there anyway to determine the rowid of
the conflict?
looking at the internals of the VDBE, i found that the rowid of the
confli
On 1/29/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
The pseudo-random number generator (PRNG) in SQLite is becoming more
important so it seem good to make sure it is well seeded.
I'd like to be able to seed the random number generator so I can have
repeatable
sequences of random numbers
Recent outages on www.sqlite.org are due to hardware
problems and are unrelated to the recent download abuse.
Our ISP will be swapping out servers soon (which will
result in another outage of an hour to two, of course.)
Thank you for your patience.
If you ever need access to www.sqlite.org and you
hello drh, list
I used to think that only in my country they can forbid
knives and forks because someone can misuse them, but
obviously it is true for other countries too.
Regards,
Emily
-
Спортни залагания!
bg.sportingbet.com
-
Now, I can use user-dfined types by declaring them with SQL create
tables. Fine !
But when I try to use the following code to get an object, I get a
ClassCastException:
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(
"SELECT * FROM EMP");
while (rset.next()
83 matches
Mail list logo