w = last_insert_rowid()
where table_name = 't2';
end;
Now when you need the last inserted row for a particular table you can
get it by querying the last_inserted_rows table.
select last_inserted_row from last_inserted_rows
where table_name = :table_n
[EMAIL PROTECTED] wrote:
>
> I would like to know how to decode a Date when I read a table. The same
> question for Time.
>
See http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions for details.
HTH
Dennis Cote
___
sqlite-users
s.
You can change the synchronous pragma to off to get most of the speed
benefits of operation without a journal, but the file will still be
created.
Execute "pragam synchronous = off" to disable file syncs, and when done,
execute "pragma synchronous = full" to return
print();
rc=sqlite3_step(Statement);
if (rc!=SQLITE_DONE)
{
// SQLITE_CONSTRAINT error
abort = 1;
// uncomment this line to report errors
error_print();
// comment this line to ignore errors
break;
}
sqlite3_r
you have an existing database you can drop the old index and create
the new index without losing any data.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
ep c or step d. The advantage
of this is that it prevents some other process form executing a delete
between your steps a and c for example. If the file existed when your
process A did its existence check as step a, but didn't exist when it
got to step c, there would be problems (not the problems you are se
flash card with the database from the
device.
I would suggest using a modified SQLite only as a last resort.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Bharath Booshan L wrote:
>
> I will give overview of what is happening
>
> App A - Writer process
> ---
> * Open SQLIte Connection
> * BEGIN IMMEDIATE TRASACTION
> * Insert/Update some 1000 rows in Table A,B,C
> * COMMIT
> * Close SQLite connection
>
re isn't enough information here to make any sense of what you have
written, or what you are asking.
> This is how they are used
>
The items above don't appear anywhere in the SQL you posted.
You will have to be more specific in your question if you want to get a
ded a unique index to this column which will
raise a constraint error if you try to insert a row that has the same
value in that column as some other row.
If you don't really require that the Tag column data be unique for each
row, then change your index to o normal index without t
wrong thing. Try this instead:
select * from (select user.id as id from user ) where id=1 ;
If you want the subselect result to have a name then you can do this:
select * from
(select user.id as id from user ) as sub where sub.id=1;
HTH
Dennis Cote
___
can always use this optimization, and will in fact
never pull data from the table itself for a query that uses the index.
The table (along with the index) will only be accessed when rows are
inserted, updated, or deleted.
Dennis Cote
___
sqlite-users mailing li
short, as usual, it depends. :-)
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
nversion can
also be eliminated to give:
select code, sum(
(select l2.timestamp from Log l2
where l2.timestamp > l1.timestamp
order by timestamp limit 1
)
- timestamp)
from Log l1
group by code;
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
will be inserted into table tbl1.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
would be
the item to match on.
In your case, if the combination of Material and Name is a primary key,
then it should be quite fast. If it's not, then making them a primary
key, or adding an index on those columns will help.
HTH
Dennis Cote
___
sqlite
problems.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
ent tells the function to save the SQL so
it can be recompiled later if need be.
What kind of problems are you having? Can you be more specific.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman
e record
exists, its Qnt value will be incremented and inserted as a replacement
record.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
of the list view. The list view can give all
> information about viewing area(top position, bottom position).
See http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor for additional
info.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
it is a parameter inthe query that you can bind some string value to later.
>
> nError from the prepare is SQLITE_OK but from the bind its SQLITE_RANGE
>
This is because you don't have any parameters to be bound.
HTH
Dennis Cote
___
sqli
ld try trimming those
values instead.
Select name from PerfTest1 where trim(name) = 'key5000';
> This works:
>
> Select name from PerfTest1 where name like '%key5000%';
>
That is because this query matches names that have trailing and/or
leadi
= SQLITE_OK) {
//process error
}
>
> however when i do:
>
> select * from Images;
>
To dump the blob data in a human readable format you could use the hex()
SQL function. It will display each byte of the blob as two ASCII
characters that correspond to the hexadecimal val
> prepare statement being successful or nothing happens
> at all when clearly i did and clearly the status was
> ok too since i didnt get an error message there.
>
I'm not sure why the finalize would fail, unless perhaps your prepare
also failed, and you didn't have a valid statemen
:\MP3\Albums\Abba - Definitive
Collection\');
insert into Folders values(3,2,1,'C:\MP3\Albums\Abba - Definitive
Collection\cd1\');
insert into Folders values(4,2,1,'C:\MP3\Albums\Abba - Definitive
Collection\cd2\');
SELECT folderid FROM Folders WHERE path LIKE 'C:\MP3\Albums\' || '%';
DELETE FR
utoinc.html for full
details.
This keyword may be the solution to the OPs ocncerns as well.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
ngs back here.
Also I noticed that you are using different case (zSQL vs zSql) in the
calls to sqlite3_mprintf() and sqlite3_exec(). I assume that is another
typo. It really would be best if you could post the actual code you were
running to avoid such issues.
HT
running as a different user than the command line shell program, and
that user doesn't have write permission to the file or directory which
are needed to create the journal file and to modify the database file. I
would have expected an error message return from sqlite3_exec(
(select word from other_table where word = :word)
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
length(trim(name));
It should display any names that have leading or trailing spaces with a
colon on each end so the spaces become visible.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
tegrity
checks on the database in order to try to determine when the corruption
occurred in case it happens again. This process would log the success or
failure of each check. This may greatly reduce the number of operations
you have to consider prior to a failure if the corruption happens again.
ite3 test.db
SQLite version 3.5.6
Enter ".help" for instructions
sqlite> create table t(a,b);
sqlite> .quit
C:\Documents and Settings\DennisC>
If you don't specify a filename on the command line sqlite3 uses an in
memory database which is lost when you
ssing indexes are missing,
> parameter_index and parameter_name don't provide any indication?
>
No there isn't because the indexes aren't missing. The correct number of
variables are associated with the query. Some of them may not be bound
to other values, and will therefore ha
Michael Schlenker wrote:
>
> n is an integer primary key..., is LIMIT better than a where condition in
> that case?
>
No. Looking at the explain output it looks like six of one half dozen of
the other.
Dennis Cote
___
sqlite-users
n do it any faster than SQLite is doing for the
subquery. There is probably no benefit unless you want to reuse the temp
table for several meta table queries.
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
ly the last value every 250 ms, then you
should have no trouble. If you are trying to redraw a scrolling graph of
the last 400 samples every 250 ms you may have issues to be concerned about.
Dennis Cote
___
sqlite-users mailing list
sqlite-users@s
uid_sets table. This may be simpler if you reuse the sets often.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
in such a
> case, please tell so).
Your questions aren't too stupid, just a little vague and without enough
detailed context info for anyone to offer much real assistance.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
n the database, as noted in the documentation.
> Therefore, auto-vacuuming must be turned on
> before any tables are created. It is not possible to enable or
> disable auto-vacuum after a table has been created.
HTH
Dennis Cote
___
sqlite-users mailing l
group talker listener
-- -- -- --
1 1 11 12
1 2 15 47
2 1 11 22
2 2 37 15
3 1 22 11
3 2
he same
directory for you to make any changes.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
This should produce the following table with a unique unambiguous id for
each communication.
id group talker listener
11 11 12
21 11 22
31 22 11
41 22 12
51 34 11
61 34 12
71 34 22
8
ave your program start an exclusive transaction
before the copy,and roll it back after the copy.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
is not possible to enable or
> disable auto-vacuum after a table has been created.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
ite.
>
Not quite. First you have to compile the functions into a dll library,
then use the command you showed to load that library.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Rael Bauer wrote:
>> According to the web page, 3.5.3.
>
> Which web page is that?
>
Probably http://www.sqlite.org/changes.html
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/
rough to the callback method using
the context argument above. Then you can call the instance methods for
that particular object.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
rds in Table. Is there
> any function available in SQLite similar to this.
>
See http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor for a
discussion of paging results that is fast and does not require a
row_number for the entries.
HTH
Dennis Cote
__
match your update condition?
select count(*) from your_table where Address=7 and Port=1
If that gives a zero result you have your answer.
You might also want to show the create statement you used for the table
you are trying to update.
HTH
Dennis Cote
Thufir wrote:
>
> But isn't recursion, for better or worse, part of the SQL:2003 standard?
>
It's an optional part of the SQL:1999 standard that is not widely
implemented.
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite
; - " || Surname AS Nominative
FROM Students
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[EMAIL PROTECTED] wrote:
>
> How can I debug this issu ?
>
You will have to show some of the code you are having problems with
before anyone here can help you with this.
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite
leaves_index on LEAVES (leafID)",
> 0, 0, );
>
>
>
> the OPEN worked fine, so why is CREATE INDEX returning this error ?
> -
>
I suspect the error is due to the fact the index already exists. It only
needs to be created once,
ry will work:
select exists (select * from words where word = :word);
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
rning+c%2b%2b+sqlite3
This exact issue was discussed.
You can prepare the query once, and execute it 50K times in a
transaction to quickly enter all your words.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
RDER BY
im.balance_due DESC,
im.invoice_date,
im.total DESC,
im.customer_name
Where you can easily see that the only columns you are selecting are the
from the im table (invoice_master). The columns from the the_
[EMAIL PROTECTED] wrote:
> Is there a way to export/dump SQLite data into INSERT statements which
> also have column names?
>
No. You would have to build a custom version and modify the source of
the dump command in shell.c.
HTH
De
bzip, should work fine.
Note the encoding scheme must not split a file into multiple lines (i.e
the encoded format can't have any embedded linefeeds), since the .import
command would split the file into multiple records.
HTH
Dennis Cote
___
sqlit
o execute the query.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
to use in that you need to make more
function calls to insert the blob, but they allow the application to use
less memory, and they can handle data that is larger than what will fit
in memory (i.e. it is the only way to insert a 100G file on a machine
with only 2G of memory).
HTH
Denni
the index on mytable, since it can get the required rowid value
directly from the index. Also, since you already have the rowid for the
row in the update case, the update will not need to reference the index
to locate the row, it will go directly t
M+floating+point=gmane.comp.db.sqlite.general
It seems ARM has some unusual handling of endianness that may be causing
your problems.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
These values are harder to use, but can save
considerable space and work quite well if you have lots of data stored
at fairly regular intervals.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
materials processing.
I'm not sure how useful it would be for XML unless you were trying to
store a parse tree of an XML document in your database.
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman
Samuel Neff wrote:
> As I understand it, recursion in SQL
> is referring to self-referencing tables that create a parent/child
> relationship.
Actually the WITH RECURSIVE clause in SQL:1999 handles multiple mutually
recursive tables as well as self referential tables.
De
this. It
uses a WITH RECURSIVE clause as a prefix to a SELECT. It hasn't been
widely implemented, but I believe that IBM's DB2 supports this mechanism.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/
; If not, does anyone have any brilliant ideas?
>
See
http://article.gmane.org/gmane.comp.db.sqlite.general/17286/match=managing+trees+database
for a method I use to manage trees in SQLite that works very well.
HTH
Dennis Cote
___
sqlite-users mailing
ine shell program (and probably all other files as well).
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
t is
already the most widely deployed database (unqualified), and I think it
is almost certain that it will become the most widely deployed database
at some some point in time (if it isn't already).
That said, I agree with Toby that you must be careful about your
assumptions and arguments
ll I know, but if it is not, then it substantially increases the number
of non-SQLite database deployments.
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Fin Springs wrote:
>
> I have been using:
>
> SELECT NULL FROM sqlite_master WHERE type='table' AND lower(name)=?
>
> to determine whether a table exists and looking at the number of rows
> returned (I'm using sqlite3_get_table through an API). I get one row
> back if the table exists and no
existence check.
This query returns a boolean result and stops as soon as it has
determined the result
select exists (select * from contacts where contacts_phone_tel glob ?);
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://
t then
simply replace that identifier with a literal string containing the word
to be inserted.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
NTEGER,
> [server_start_time] INTEGER,
> [server_uptime] INTEGER,
> [server_uptime_diff] INTEGER,
> [server_is_connected] INTEGER)
>
It would help to add an index on server_timestamp in the
timestamp_master table.
create index server_timestamp_idx on timestamp_master(server_timestamp);
Kalyani Phadke wrote:
>
> Any suggestions?
>
Please stop hijacking message threads.
http://en.wikipedia.org/wiki/Thread_hijacking
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman
e row with a new one that has the count incremented if
the word already exists. It will insert the word with a count of 1 if not.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
s. Always assume your table is scrambled to an arbitrary row order
before each query since SQL works with sets of rows, not ordered tables
like a spreadsheet.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Ken wrote:
> select * from foo
> where rowid =
> (select rowid from foo
> where parent_id = 1
> and child_id = 2
> group by parent_id, child_id
> having revision = max(revision))
>
> sqlite> explain query plan
>...> select * from foo
>...> where
ueries are sub-optimal? They will both use
the index to find the correct revision number, and then use the index to
find the matching row.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
matched field b or field c in
table1, and the sum of all the field a values from table1 where the
row's id matched field b or field c.
Note the values in the first row are not valid because that row gets
overwritten on each replacement.
If this is correct, it seems to me there should be a
Try "user#" instead of user# and you will be set.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> I have not updated the patch, however. It *should* be pretty
> straightforward -- looking at it again, it doesn't actually seem to do
> any VDBE code itself, so who knows?
>
Stephen,
FYI, your ticket was fixed on Tuesday by checkin 4782 in case
many possible causes.
Since others are able to run this library, it is most likely something
specific to your machine.
You might want to run a memory test such as http://www.memtest.org/ to
rule out hardware issues.
After that start going through some of those links
usy and just skim
the postings. :-)
I'm glad you didn't give up on your idea.
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
databases created in SQLite using standard
quoting would be more easily portable to any of these other databases
if the need arises.
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
pparent huge overhead of
> the inefficiency that Steve raised about using updatable views ;-)
>
Likewise, I think this is a very good optimization idea that should be
implemented as soon as possible. I see he has created a ticket to get it
on the radar
N) searches in the index to find the row
to delete or update, and only need to do a very fast isnull check on
that one row.
Because all three cases use the same index, all the pages needed should
be in the page cache after the first scan, so they should not require
any additional I/O, and he
y to change? It seems a
> common need.
>
Tom,
Standard SQL doesn't allow any SQL-Schema statements (basically any
create or drop statements) in a trigger body. I doubt this will ever change.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@s
bility of course. This would allow users to use only the SQL
standard quoting rules, and still get proper error messages if they make
a typographical error.
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
atch itself will probably have to be modified, since
SQLite recently underwent significant changes to its code generation
routines.
As with all patches, it will be reviewed and accepted much faster if it
passes the test suite.
HTH
Dennis Cote
___
sqlite-us
on is only available in versions of SQLite since 3.5.5.
Note the trim() function has been around for a long time. It will trim
both trailing and leading spaces. If you don't need to preserve leading
spaces, then it should be sufficient to clean up your data.
You can check your version with a simp
select id from "New Id" where "table name" = 'Orders
Refunds' )
, ( select ID from Paying where Label = new.Paying )
where new.Paying not null
;
insert into [Orders Refunds Amount]
(
If you do that you need to ensure
the customer name values are unique.
insert into phones (tel, id_customer)
values ('1234567', (select id from customer where name = 'John Doe'));
insert into phones (tel, id_customer)
values ('9876543', (select id from customer where name = 'John Doe
n aggregate functions. It will be no faster
than what you have now.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
less on windows
> and unix.
>
Richard,
The OP said they were measuring an excess of lock calls. That would
imply that SQLite is locking files it has already locked. Is that
possible with the POSIX APIs?
Dennis Cote
___
sqlite-users mailing list
sq
omewhere
along the line.
Aren't standards wonderful, especially when everyone has their own. :-)
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
insert into customer select null, tel, name from temp_customer;
drop table temp_customer;
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
names
and field data for each result row, and then calls the callback function.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
sqlite> CREATE VIEW UserView3 AS
...> SELECT UserNumber, UserType, UserName, UserStatus, Password,
PasswordHint
...> FROM UserTable
...> LEFT JOIN UserTypeTable using(UserTypeKey)
...> LEFT JOIN UserStatusTable using(UserStatusKey);
SQL error: cannot join using colum
The argv array contains pointers to the string representation of each
filed in the row, there is one string for each of the argc fields. If a
field is null then its string pointer (in argv) will be NULL).
HTH
Dennis Cote
-
To
301 - 400 of 1177 matches
Mail list logo
