or
when you called sqlite_reset (see the section Goofy Interface Alert).
Nonetheless, you are saying you are getting an SQLITE_DONE when the disk
is full.
What does the version 3.3.4 sqlite shell give you when you do an explain
on a commit statement?
sqlite3 test.db
.explain
ler that this op-code (Halt) executed correctly. This op-code is only
one step in the execution of the statement.
I'm not saying you haven't found a problem with respect to full disks,
but this code is not the culprit. You will need to keep
That
will acquire a write lock and unnecessarily block all other readers as
well. You only need to hold a read lock to prevent any other process
from acquiring a write lock.
Dennis Cote
-
To unsubscribe, send email
es from doing any writes).
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
ach to
:memory: you get a new completely independent memory database.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
o get more sophisticated adding a timer that
will also do a commit and begin say one second after the last log entry
if you need to. You will get a speedup by nearly a factor of 100, and if
you have a power failure or OS crash you will only lose the uncommitted
records, not your entire database.
HTH
D
completely safe.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
7;);
sqlite> insert into t(b) values('three');
sqlite> pragma count_changes= 1;
sqlite> delete from t where b like 't%';
2
sqlite> select * from t;
1|one
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
w records onto the ends of the tables. There might
be a small speedup by pre-sorting the strings to be inserted within a
transaction so that they are added to the unique index in order, but
that may be offset by cost of doin
record per transaction?
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
lect/Insert" version
(which DRH agreed would most likely be the fastest "approved"
mechanism), with the "Insert+Ignore/Insert+Select" version proposed by
Dennis Cote, while being simplest, was the slowest.
Windows Embedded
(ms) (%) (ms) (%)
V0 2718
Igor Tandetnik wrote:
IF EXISTS clause is supported with SQLite v3.3.0 and up.
Igor,
Right you are, version 3.3.0.
I was mistakenly looking at the entry for IF EXISTS on create and drop
of triggers and views, that didn't happen until 3.3.8. Sorry if I caused
any confusion.
Dennis
t syntax.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
le process accessing the database. This page
explains the nature of your error in more detail
http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
y the lemon parser generator (also included in SQLite). You
may be able to use that directly, or model your parser on it. If you
build your own, be aware that the language does change from time to time.
HTH
Dennis Cote
---
Joe Wilson wrote:
--- Dennis Cote <[EMAIL PROTECTED]> wrote:
On 2/3/07, Tom Shaw <[EMAIL PROTECTED]> wrote:
SQLSTATE[HY000]: General error: 1 SQL logic error or missing database
and
SQLSTATE[HY000]: General error: 8 attempt to write a readonly database
Tom what wra
f, but your wrapper. It may have restrictions that sqlite
does not.
Dennis Cote
execute the inserts.
I thought he may have been using the sqlite3_exec function which includes
the overhead of compiling the SQL code for each execution. In that case, I
suspect that changing to the prepared statements should provide more benefit
than the VDBE stack hack by eliminating this overhead.
Dennis Cote
);
}
// execution
void myInsertFunction( const char* str)
{
do_stmt(bgn);
sqlite3_bind_text(inString, 1, str, -1, SQLITE_STATIC);
do_stmt(inString);
sqlite3_bind_text(inObject, 1, str, -1, SQLITE_STATIC);
do_stmt(inObject);
do_stmt(cmt);
}
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
ue to be the primary key) then it will be used to quickly locate any
pre-existing record during the first insert. After that, all the needed
pages will be cached and the subquery in the second insert should
execute very quickly.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Table AS SELECT * FROM myOtherTable;
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
re than science.
So, what works and what doesn't? Let me know.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
factor>
::=
[ ]
::=
|
::=
|
::=
::=
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
h the
database so that the standard collations rules can be used in triggers etc.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
X1, X2, .
. . , Xn, the collating sequence
is effectively determined by considering X1 and X2, then combining this
result with X3, and so on.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
s and let you know how
they say this should be parsed.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
gate the NULL collation values that
result from using different Implicit collations in a dyadic function.
These rules may seem a little convoluted, but they are standardized, and
clearly define the behavior.
I think it's important for SQLite to try to maintain as much SQL
standard compatibilit
bOption=1 WHERE rowid IN "
"(SELECT Stocks.rowid FROM Stocks, Options "
"WHERE Stocks.sStockSymbol = Options.sStockSymbol); ";
eliminates two unnecessary calls to the string operator+= function. I
also think it looks better. :-)
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
ld translate to SQLite by simply
removing the convert(...) clauses.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
o what you
want to do.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
ly wan the max of the two columns you can use coalesc to
convert nulls into zeros for the max function.
max(coalesce(col1, 0), coalesce(col2, 0))
This will give a result of zero if both columns are null.
HTH
Dennis Cote
--
number is small
relative to all the other clients, but you might be surprised.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
itely looks like a bug to me.
Nico
Nicolas,
You need to re-read the documentation for LIMIT and OFFSET at
http://www.sqlite.org/lang_select.html
In particular this sentence should clarify what is happening: "The
opt
2) How long is this pointer valid? Till the next call of a sqlite3
function, till a next query etc.?
Hartwig,
re 1: No, you don't need to free the memory.
re 2: It is valid until you step to the next record with sqlite3_step,
or reset the query with sqlite3_reset or sqlite3_fi
large this will be a lot faster.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
wer failure. The second sets the encoding to its default value,
UTF-8, and shouldn't be needed. The second last (vdbe_trace) is also
setting the value to its default. Furthermore this pragma can only be
turned on in a special debug build of the sqlite dll. Increasing the
cache and page size wi
jose isaias cabrera wrote:
can unique id numbers have numbers missing?
Jose,
Yes, of course. The database will store whatever values you put in its
columns. A unique column does not mean it its entries must be
sequential, just that there can not be any duplicates.
HTH
Dennis Cote
/pragma.html#schema
It gives the requested information for each column in a table.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
d item when the user
moves an item. This is somewhat like the Dewey decimal system used by
libraries to order books on shelves. Using a separate table would also
allow multiple users to each have their own preferred sort order
maintained by your application.
HTH
Dennis Cote
much work you go through
with each release, but suspect that a release after each crash or
corruption bug is fixed, or new feature addition, would not be too onerous.
Dennis Cote
-
To unsubscribe, send email
statistics about your indexes which sqlite uses to select the
optimal index when it prepares a query.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-with-tcl directive. Th
tclConfig.sh is a shell script that will provide additional information
to locate other parts of your TCL installation (like the tcl.h header file).
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
read most of the documentation from this
script file directly, or if you have TCL installed you can probably
expand this file into the desired HTML.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Year. ;-)
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
nd line. Note,
you must quote the command so it gets passed to sqlite as a single string.
sqlite3 newdatabase.db ".read schemafile.txt"
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
2')
becomes
'2006-12-22' - '2002-12-22'
which is converted to
2006 - 2002
without any warning or error and produces the correct result 4
Other than that it uses the reverse logic of my case statement. Get the
difference of the years and subtract 1 or 0 depend
ops, I needed another set of brackets to ensure correct order of
operations, and I had the comparison wrong (need <= not >=).
This works:
select
case
when date('2002-12-22', '+' ||
(strftime('%Y', 'now') - strftime('%Y
select
case
when date(dob, '+' ||
strftime('%Y', 'now') - strftime('%Y', dob) ||
' years') >= date('now')
then strftime('%Y', 'now') - strftime('%Y'
ere is no power function in standard SQL.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
://www.tcl.tk/community/tcl2004/Papers/D.RichardHipp/drh.html
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
arty tools can do).
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
mail_ma wrote:
> I set the maillist is every one to send me ,so many mail is not good,
> I want get a archive,what URL i can cancel it ?
> please help me ???
>
>
>
See the mailing list instructions at the top of
http://www.sqlite.org/support.htm
to tell a 3rd party tool which
extensions it should load when it opens a database.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
connection can batch its writes based on time as
I suggested before.
If you really need multiple writers you should probably look at a
different database engine like PostgreSQL.
Dennis Cote
-
To unsubscribe, send email to
Rashmi Hiremath wrote:
Can anyone send me the code of C++ API for
SQLite3.3.8.
Check out http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers in
particular the section on C++.
I would suggest CppSQLite as it provides full access to sqlite with a
very thin interface.
HTH
Dennis Cote
ion
set transaction open to false
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
order to define their tables and
indexes without redundant data being stored.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
/tktnew.
SQLite should be able to execute these queries without even accessing
the Posts table itself since all the required data is available in the
index in the correct order.
Dennis Cote
-
To unsubscribe, send email
;);
seq name unique
-- --
0 sqlite_autoindex_t_1 1
sqlite> pragma index_info('sqlite_autoindex_t_1');
seqno cid name
-- -- --
0 1 b
HTH
Dennis Cote
-
@sqlite.org/msg19148.html
I believe Igor's suggestion is optimal for SQLite. It may be faster to
add a limit clause to the subquery for other database engines.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
this index */
};
#endif
Be advised, that after I do this I get a similar unresolved external
error for _sqlite3_libversion. I'm trying to see what might be causing
this, but the generated sqlite3.lib file looks OK on first inspection.
HTH
Dennis Cote
discovered with Borland IMPLIB. The fix I found is
described under ticket 1291 at http://www.sqlite.org/cvstrac/tktview?tn=1291
For some reason implib doesn't generate the correct symbols when
converting directly from the dll file.
e fast since it
reads the file sequentially from start to finish with no seeking,
whereas SQLite will seek back and forth as it reads database pages into
memory.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
l database.
Normalization like this works just as well for non blob data.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
ed J#, but that is not required to
the best of my knowledge.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RB Smissaert wrote:
Thanks for the tip. It sounds promising and I will try that out.
Oops. I forgot to add the link
http://www.borland.com/us/products/turbo/index.html to speedup locating it.
Good Luck.
Dennis Cote
ler you can use SQLite
directly, or through one of the C++ wrappers. I suspect that this will
produce an application that is about as fast a you will get for moving
data from Interbase to SQLite.
HTH
Dennis Cote
-
<< 16) +
(substr(ip, 9, 1) << 8) +
substr(ip, 11, 3)
when substr(ip, 11, 1) = '.' then -- 2 digit third quad
(substr(ip, 1, 3) << 24) +
(substr(ip, 5, 3) << 16) +
(substr(ip, 9, 2) << 8) +
substr(ip, 12, 3)
else -- 3 digit third quad
(substr(ip, 1, 3) << 24) +
(substr(ip, 5, 3) << 16) +
(substr(ip, 9, 3) << 8) +
substr(ip, 13, 3)
end
end
end
from t;
Boy was I pissed the first time I typed that in and all I got was "SQL
error: near "when": syntax error". ;-)
FYI, this expression produces a VDBE program with 895 instructions.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
that julian
day numbers can express dates back to 4700 BC, but Excel can't.
The testing needed to fix the dates for excel's bug only applies to
dates in the first two month of the year 1900, so it can probably be
safely skipped
nday('now') - julianday('1899-12-30')
end as excel_date;
If all your dates are after 1900-02-28 then you can safely skip the test
and use only the later base date.
select julianday('now') - julianday('1899-12-30') as excel_date;
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
...> ;
39054
sqlite>
I can' t account for the other off by one error though. You could, of
course, just add 2 instead of 1 to get the right date from excel.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
teger) || '-' ||
substr('00' || (cast(20061204 / 100 as integer) % 100), -2,
2) || '-' ||
substr('00' || (20061204 % 100), -2, 2)
) - julianday('1900-01-01')
s any measurable speed difference.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[EMAIL PROTECTED] wrote:
Thanks , will try that.
What is as excel_date?
Is this a variable or is this jus plain SQL against SQLite?
Bart,
excel_date is just a normal SQL alias name for the complicated
expression. The string 'execl_date' will be returned as the name of this
result column.
ger) % 100) || '-' ||
(datefield % 100)
) - julianday('1900-01-01')
as integer
) as excel_date
from mytable;
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
as integer
) as excel_date
from mytable;
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
sion command to detect if your database has been initialized
(search for previous posts).
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
is right, you should store your password in a table. If you are
concerned about the password being saved in the database, then you could
use a temporary table, or even attach a :memory: database to hold the
table that contains the password. In any case the saved password is
available to your encrypt and decrypt functions.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
your inserts in a transaction.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
open a
database to see if the user_version has the expected value. If not you
know you have some other kind of file, if the file existed before you
did the sqlite3_open, or a newly created empty database file that needs
to be
return rc;
}
This way you are sure you have all the functions registered with each
connection you use. Now you can safely execute any SQL statement. If it
causes the trigger to fire, it will have the correct user defined
functio
equal values of
the bar are scattered around with different value of p.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
||
case
when strftime('%H:%M', '12:00', 'localtime') < '12:00'
then strftime('-%H:%M', '00:00', 'utc')
else strftime('+%H:%M', '00:00', 'utc')
--
To unsubscribe, send email to [EMAIL PROTECTED]
-
See this thread
http://www.mail-archive.com/sqlite-users@sqlite.org/msg14937.html for
some example programs that
give the
same results.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
he sqlite
commands file, which will write its output to the file testfile.txt in
the current directory (which will be C:\test\ReadCodes).
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
the depths to inserts
spaces to produce an indented report from the tree.
Alternatively, you could create a user defined function that simply
counts the number of / separator characters in the path string to
determine the depth of a node. This would almost certainly be fast
triggers.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
sqlite.org/pragma.html.
David,
You should create a documentation bug ticket so these things will get fixed.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
strings in your database you can do this:
select * from t where date < '2006-07-04 18:00:10';
If you store the julian day numbers in your database you can do this:
select * from t where date < julianday('2006-07-04 18:00:1
This will open (or create) the database file and then execute all the
SQL statements in the file mysql.txt.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
s
In particular the function julianday(datestring) will return a julian
day number for a suitably formatted date string. The supported date
formats are from ISO-8601, or -MM-DD, so you will have to use substr
and concatenation (||) to rearrange
;
select id from t where subblob(data, 100, 4) = X'DEADBEEF';
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
eed to pay the cost of the
additional storage overhead.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
rom a lot of potential customers
is very persuasive to a commercial enterprise.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
a table has any records.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
never use conditions on your queries.
If your query returns a subset of the rows in a table this carefully
maintained count of all the rows in the table is useless.
Dennis Cote.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
te to matlab formats.
It also opens and closes the database file for each query which can be
expensive.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
to
prepare and execute a second count query unless the result set is very
large. With large results the execution time of the count query
dominates, and the overhead time to prepare the query becomes
insignificant. It really doesn't take that long to prepare a count quer
701 - 800 of 1282 matches
Mail list logo