a where
clause with a drop table and then a create table (using the create
statement stored in the sqlite_master table). This is done because it is
much faster than scanning and deleting all records one at a time. The
drop/create should cause a schema change.
HTH
Dennis Cote
for any string that can't be converted to an integer.
HTH
Dennis Cote
ple' AS name
...> UNION
...> SELECT 'banana' AS name
...> UNION
...> SELECT 'orange' AS name
...> UNION
...> SELECT 'Banana' AS name
...> )
...> ORDER BY name COLLATE NOCASE;
Apple
apple
Banana
banana
orange
sqlite>
HTH
Dennis Cote
(void*,int,char**, char**);
I would suggest that you switch to the new
sqlite3_prepare()/sqlite3_step(), sqlite3_finalize() API set to execute
your queries. Then you can use the sqlite3_column_text16() API to get
your results encoded as UTF-16.
HTH
Dennis Cote
eve data from a UTF-16 encoded
database, SQLite will convert the data to UTF-8 and return the converted
string.
If you are using the obsolete sqlite3_get_table() API I believe it will
always return the data encoded in UTF-8.
HTH
Dennis Cote
the best interface
I have seen so far.
Conceptually I don't have a problem with the PATTERN column, it actually
seems rather ingenious. I'm just having a little difficulty see how it
is going to be implemented using the proposed virtual table API.
Thanks for the clarification.
Dennis Cote
is
supposed to work?
Dennis Cote
statement, or if it makes any sense. Those functions are left
to sqlite3_prepare, and you. :-)
HTH
Dennis Cote
or rolls back and releases the lock. If C1 committed, then C2
will get 5, if C1 rolled back, then C2 will get 4.
HTH
Dennis Cote
which you can't use directly.
HTH
Dennis Cote
.
Dennis Cote
waste your time. :-)
In general terms, you have asked very general questions. For future reference,
you will be more likely to get a suitable response if you ask more specific
questions (like your second posting).
HTH
Dennis Cote
to execute the query.
HTH
Dennis Cote
rom the
four tables. Finally, checks if the date of the extended record is
between the specified dates. This query does not sort the final results.
It should locate the same number of records as the first query, but will
be much slower since it starts at the wrong end.
HTH
Dennis Cote
rg/cvstrac/tktnew.
Dennis Cote
Nuno Lucas wrote:
..., but it should work the same.
Famous last words. :-)
r ways to join the tables to get the same result,
and some might execute faster than this. You will have to try them out
with some real data.
HTH
Dennis Cote
simpler.
The client first wanted to store that data in an XML file,
but I was able to convince him to use a DB.
Good, you probably saved yourself a lot of work, and them a lot of
grief. :-)
HTH
Dennis Cote
into memory, so memory should not
be an issue.
HTH
Dennis Cote
object_id from field_data
where field_num in (3, 5, 6, 8, 9, 10, 15)
and field_text like (select '%' || ? || '%');
HTH
Dennis Cote
rop table temp_table;
commit;
You will also need to recreate any indexes on the table after it is dropped.
HTH
Dennis Cote
tes the % characters with your string. Now you
don't need to massage the string in the calling code. Six of one, half
dozen of the other.
HTH
Dennis Cote
entire database as
standard SQL statements that you can execute to recreate the database
using some other database engine.
HTH
Dennis Cote
nsactions per second. With a batch size of 1000 you might get 1
transaction per second, but 1000 inserts per second. Its easy to do, so
its probably worth a try.
HTH
Dennis Cote
.
Any hints from anyone
T
Thom,
Try this table definition instead.
CREATE TABLE xyz
(
str TEXT,
parent INTEGER,
PRIMARY KEY(parent, str)
);
This will create an index to greatly speed the lookups of the existing
rows when you do your inserts.
HTH
Dennis Cote
m christensen wrote:
I don't know if it does or not.
I intended to do both, but...
I can not get any variant I tried of --select sqlite_version(); -- to
execute.
It always complained about the statement in one way or another.
It does work with the current shell. I'm not sure when this
Jay Sprenkle wrote:
On 4/6/06, Dennis Cote <[EMAIL PROTECTED]> wrote:
Jay,
No, he has the open call correct. He has a local pointer, he passes the
address of that pointer to sqlite3_open() and it allocates the sqlite3
structure and sets his pointer to point to it.
No need to
places as increased accuracy.
This is very similar to the use of leading zeros on integer numbers. As
integers, 001 and 1 are the same thing. If you need to distinguish
between the two, you should be using a text column.
Dennis Cote
;
>> sqlite3 db;
>>
>>
Jay,
No, he has the open call correct. He has a local pointer, he passes the
address of that pointer to sqlite3_open() and it allocates the sqlite3
structure and sets his pointer to point to it.
No need to change this. Derrell has identified his problem.
Dennis Cote
files can be viewed at
http://www.sqlite.org/cvstrac/dir?d=sqlite/src.
HTH
Dennis Cote
ot sure how you actually execute the query.
One other possibility, you may have a wrapper that is statically linked
to an older library and the SQLite.so dynamic library may not be used.
Finally, shouldn't the library name be sqlite3.so or libsqlite3.so?
Dennis Cote
Francisco,
You might want to add a coalesce() call so the f1 value is only changed
if there is a corresponding value in table2.
update table1
set f1 = coalesce((select f1 from table2 where table2.f0 = table1.f0), f1)
Dennis Cote
the jargon!
http://sqlthis.blogspot.com | Tsql and More...
Francisco,
Try this instead.
update table1
set f1 = (select f1 from table2 where table2.f0 = table1.f0)
HTH
Dennis Cote
f
the new database file, so your tables end up allocated to sequential
pages in the file.
Dennis Cote
which you can
view here
http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/btree.c=1.324.
Basically it is a tree of blocks with similar rowid values and pointers
to other blocks with larger and smaller rowid values.
HTH
Dennis Cote
references for the life of me...
SQLite has only supported the count(distinct(expression)) since version
3.2.6.
I suspect that you have restored to an earlier version. You can check
the library version by executing
select sqlite_version();
HTH
Dennis Cote
/16175/match=fifo
HTH
Dennis Cote
ncing for you. I
would suggest http://www.codeproject.com/database/CppSQLite.asp as a
good place to start.
HTH
Dennis Cote
opy allows,
you might want to talk to Richard Hipp about some paid support. He can
surely let you know if it is possible, and may be able to do it for you
for a reasonable fee.
HTH
Dennis Cote
pendText(zSelect, ")'", 0);
+ }
+}
+sqlite3_reset(pTableInfo);
zSelect = appendText(zSelect, " || ' VALUES(' || ", 0);
rc = sqlite3_step(pTableInfo);
while( rc==SQLITE_ROW ){
Dennis Cote
, they
would appear on each output line. Note, that dump_callback() already
iterates through all the column names in the table to build the select
statement used to dump the data, so the column names are readily available.
HTH
Dennis Cote
lets us know more precisely what you are trying to do and what
isn't working. Then I'm sure someone will be able to help you. If you
won't invest some time to try to get the problem resolved, no one else
will either.
Dennis Cote
P.S. "couse" is not an English word. I think you
urn a
new SCHEMA_CHANGING_TOO_QUICKLY error if it is exceeded. This sounds
like a kludge to me, but it can prevent indeterminate execution time.
I don't think there are any valid reasons for not including the wrapper
functionality in the SQLite library in this post.
Dennis Cote
be implemented
using the existing APIs so there would be no danger of the old APIs
being abandoned over time.
Just my two cents.
Dennis Cote
sense attaching one of the sqlite_gibberish files if
its size is zero bytes.
Dennis Cote
(both good programs that
are easy to install and simple to use), could you try zipping up one of
those sqlite_gibberish files and attaching it to ticket #1698 (use the
link above and then click on "Attach" near the upper right corner of the
page).
Please let us know what you find.
Dennis Cote
On 3/30/06, Dennis Cote <[EMAIL PROTECTED]> wrote:
>
>
> Another approach is to remove your primary key. If you don't need it to
> enforce uniqueness constraints on your data then you could eliminate the
> primary key, and change the EntryId column into an integer
Again this will lead to a
larger database file, and you will still need to reorder the results,
but it should be about as fast as possible.
HTH
Dennis Cote
y what you need to do in your own application.
It seemed like a good place to start to me.
HTH
Dennis Cote
the entire database to a new file without converting everything into SQL
text. This might be faster. You can look at the code in vacuum.c
HTH
Dennis Cote
contents.
After copying the file, you can attach to the new file and you will have
a complete copy of the original database.
HTH
Dennis Cote
e concrete examples.
HTH
Dennis Cote
instantly with MSN Messenger! Download today it's
FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
Google is your friend. Try searching for "SQLite JDBC". Among other
things it will lead you to
http://www.ch-werner.de/javasqlite/overview-summary.html
HTH
Dennis Cote
this a try, you might be surprised at how flexible it is.
Just because something works doesn't mean that it can't be improved.
Dennis Cote
be freshly assigned. You will
also need to drop the temp table when you are done with it.
create temp table temp_table as select * from my_table where ;
select * from temp_table where rowid % N = 0;
drop table temp_table;
HTH
Dennis Cote
ned into one query that gets the desired rows.
select * from mytable where Name >= (
select Name from mytable where Name < 'Sprenkle' order by Name
desc limit 1 offset 50)
order by Name limit 101;
This query works as expected in SQLite so it should be a work around for
yo
modern
CPUs).
HTH
Dennis Cote
1.4) of sqlite installed that is being located
first.
HTH
Dennis Cote
union select * from day7.tab4
union select * from day8.tab4
union select * from day9.tab4
union select * from day10.tab4;
Now at the command prompt you can open your master database and execute
the merge with the following command.
sqlite master.db '.read merge.sql'
HTH
Dennis Cote
(
SELECT ... FROM ... WHERE ...
}
and that syntax is not allowed so it generates a syntax error.
HTH
Dennis Cote
[EMAIL PROTECTED] wrote:
Dennis Cote <[EMAIL PROTECTED]> wrote:
It is worth noting that SQLite's behavior is not completely consistent.
If the column is declared to be an 'integer primary key', then SQLite
will insert a "default" value even when the user explicitly suppli
alue.
sqlite> insert into t(b) values(NULL);
sqlite> insert into t(b) values(1);
I don't think any such change should be made, since it will probably
break many applications, but it worth noting.
Dennis Cote
FROM GR_ADDRESS t1
...> WHERE t1.ID > 1
...> UNION ALL
...> SELECT t1.ID as ID
...> FROM PERSON t1)
...> t1 ORDER BY t1.ID DESC;
ID
--
3
2
You might want to file a bug report ticket. You may also be interested
in the discussion of a similar problem under ticket 1688.
HTH
Dennis Cote
f work (and I/O) that
must be done to add a record. Removing indexes won't speed up queries, and
it might greatly slow them down if you delete an index that is being used to
accelerate your query.
HTH
Dennis Cote
the first time it is executed, and save the prepared statemet in a
cache. The next time it is executed, the previously prepared statement is
pulled from the cache and re-executed.
HTH
Dennis Cote
stmt=>
step
rc => MISUSE -- Library used incorrectly
msg=> not an error
prepare DROP TABLE IF EXISTS bla; -- first try
rc => OK -- Successful result
msg => not an error
stmt=> 003DA008
step
DONE
finalize
rc => OK -- Successful
them there? Can you email me a sample file?
--
D. Richard Hipp <[EMAIL PROTECTED]>
Richard,
How will he get your questions if you have taken him off the mailing list?
Dennis Cote
join this table with the tree to check if I should add the
expand control to a tree item.
HTH
Dennis Cote
BLOB data you want to insert using sqlite_exec as a literal hex string.
It is generally easier to use the prepare/step/finalize functions and
the sqlite_bind_blob and sqlite_column_blob/sqlite_column_bytes
functions to deal with binary data.
HTH
Dennis Cote
s the fact that a integer primary key column
doesn't generate an autoindex at all. You will have to infer the
existence of this index this from the primary key flag on the column,
and the lack of an autoindex on that column only.
HTH
Dennis Cote
the path to node
select * from tree
where (select path || id || '/' from tree where id = :node_id)
like path || id || '/%'
order by path;
-- find all nodes on level 3 or 4
select * from tree
where path like '/%/%/' or path like '/%/%/%/';
HTH
Dennis Cote
e the debug symbols.
strip -g .libs/libsqlite3.a
Now when you do
make install
make will use libtool to install the stripped version which is only
about 437K on my machine.
HTH
Dennis Cote
it should return an empty string. It shouldn't return a NULL pointer,
that means the result is not taken directly from a table column.
HTH
Dennis Cote
table_info(table_name);
HTH
Dennis Cote
you
can event get more sophisticated and add a timestamp field to your log,
or add other fields to track important internal state information.
HTH
Dennis Cote
SQLite.
Casting (i.e. cast(a as real)) won't work since it returns the same 0.0
value. Therefore the user will not be able to use any of the numeric
functions (i.e. sum(), avg(), min() etc.) to get correct (or at least
the expected) results using this column.
Dennis Cote
that Richard
note between avg() and sum()/count(). This would not change the behavior
for columns declared are real, or columns that are untyped.
Do any of you have, or know of, an application that would be adversely
affected if this change is made to SQLite?
Dennis Cote
Oops...
That should, of course, be *inconsistency*.
On 2/8/06, Dennis Cote <[EMAIL PROTECTED]> wrote:
>
> [EMAIL PROTECTED] wrote:
>
> >
> >Did you try sum(a)/count(a) instead of avg(a).
> >
> > SQLite version 3.3.3
> > Enter ".help" f
to eliminate. It's probably one of the
reasons the standard says the average of an integer column should be an
integer (or words to that effect).
Dennis Cote
;if
exists/if not exists" features introduced in version 3.3.0.
HTH
Dennis Cote
t. It will save me lots of time setting up other programs (and
hopefully get info on programs I don't have access to like Oracle).
TIA
Dennis Cote
_master;
t
sqlite> drop table if exists t;
sqlite> select name from sqlite_master;
sqlite>
This feature was first implemented in version 3.3.0, so you will need to
upgrade from version 3.2.8.
HTH
Dennis Cote
0 ; i < 100 ; i++ )
{
ps.setValue(0,i);
ps.execute(callback_handler);
}
Marian,
see http://www.sqlite.org/capi3.html in particular section 2.2,
Executing SQL statements.
HTH
Dennis Cote
using sqlite3_finalize()
end the transaction using sqlite3_exec()
}
HTH
Dennis Cote
d always switch to 64 (or 63) values
if it was.
Dennis Cote
ng in a more transparent manner,
but it will take longer to execute.
SELECT * FROM mytable WHERE quote(myblob) LIKE 'X''00%';
After looking at it, it may not be that much more transparent either. :-)
HTH
Dennis Cote
Jim C. Nasby wrote:
On Tue, Jan 31, 2006 at 10:05:47AM -0700, Dennis Cote wrote:
[EMAIL PROTECTED] wrote:
CREATE TABLE x(i INTEGER PRIMARY KEY AUTOINCREMENT CHECK(i < (1<<32)));
I suspect you'll see better performance if you hard-code the value
instead of doing a bit-shift e
a.Term_dato>='2004-10-01'
Order by a.Isin, a.Term_dato, a.Boers_Dato
HTH
Dennis Cote
INTEGER PRIMARY KEY AUTOINCREMENT CHECK(i < (1<<32)));
Also, if you are concerned about signed vs unsigned interpretation of
the 32 bit value you may want to change the maximum to a 31 bit shift
which will restrict i to values that are always positive signed values.
HTH
Dennis Cote
>commit transaction;
>drop table tt;
HTH
Dennis Cote
part of the ordering, and since SQLite
only uses one index per table scan it can't use the explicit index. Then
it determines that it needs to do a sort to order by Artikel. My change
removes the implicit index on Nummer, so then it finds and uses the
explicit index to scan the table in the correct order.
HTH
Dennis Cote
NULL);
sqlite> select * from t;
a f1 f2
-- --
1 1 2006-01-24
sqlite>
HTH
Dennis Cote
type() API to determine the type
of the values returned from a query. Other APIs like
sqlite3_column_decltype() and pragma table_info are returning other
information, not the type of the result value.
HTH
Dennis Cote
to be
displayed, but that is only done once for each output row in the
histogram, not twice for each row in the table).
HTH
Dennis Cote
nt of zero. The histogram output only has rows where the count was 1
or more. This may or may not be what you want.
HTH
Dennis Cote
to define a
custom function.
HTH
Dennis Cote
s text there are no NULL values. The closest thing
you get is an empty string (i.e when two separator characters occur back
to back).
In summary try this sequence commands instead.
create table table_name
.separator ;
.import datafile.txt table_name
To verify the values that were imported use:
.mode line
select * from table_name;
HTH
Dennis Cote
Igor Tandetnik wrote:
Dennis Cote wrote:
But I have to wonder why you suggest creating the temp table at all
when this does the same thing:
delete from source_table
where id in
(select id from source_table where some_condition = true);
Or even
delete from source_table
where
in
(select id from source_table where some_condition = true);
Dennis Cote
string?
Actually it would be best if you could provide the actual schema of the
table, including any indexes and triggers that are defined as well.
Dennis Cote
901 - 1000 of 1177 matches
Mail list logo
