[sqlite] Trouble with Visual Studio Express
Has anybody else had trouble debugging SQlite3 in Visual Studio Express? Here's what I have: Made a Visual Studio Express project, put the SQLite3 code in, added shell.c, put a breakpoint in sqlite3_exec, and ran the debugger (debugging argument goes through sqlite3_exec). No stop. Then I put a breakpoint on the call to sqlite3_exec in main(), ran, got the stop there, pressed Step Into (F11), and the current line jumped to comments in sqlite3.c. I'm thinking it's the VS Express can't take the amalgamated code. Before suggestions of other IDE's/compilers, did anybody else experience this? Found workarounds? Thanks, Martin mail2web.com Enhanced email for the mobile individual based on Microsoft® Exchange - http://link.mail2web.com/Personal/EnhancedEmail ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to bind a tinyint so that actually uses 1 byte?
> As long as your values are within the range of -128 to +127, your > integers will only take one byte of storage (plus common overhead). > There's an extra byte of meta data for each column value in each row due to manifest typing, so an int will take at least 2 bytes of storage per column per row. http://www.sqlite.org/fileformat.html#record_format The exceptions are the special values 0 and 1 when using pragma legacy_file_format=0; then only 1 byte is required to store these values. -- View this message in context: http://www.nabble.com/How-to-bind-a-tinyint-so-that-actually-uses-1-byte--tp20147265p20152977.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to install SQLite on a shared linux hosting
> only compiled and linked into an application. There is no server. Martin, thank you for the answer. I do not really understand "compiled", "linked into an application" and "there is no server". They are far too technical terms. But, judging from your short answer, I believe that there is no way to use it to store/retrieve data for my site. Am I right? Luigi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to install SQLite on a shared linux hosting
I have been trying to understand if and how it is it possible for me to install SQLite, but due to my little knowledge I find no way. Also, no information at all is given on the official site, apart from a general "installation is trivial: just copy the sqlite or sqlite.exe executable to the target machine and run it".[1] I have my own site hosted on a shared linux server[2][3] (of which I have no direct control) which I can access with ftp. Where exactly should I place the SQLite file, and which file should I grab. please? Then, how do I "run" it? Another tutorial[4] says: "Get a copy of the prebuilt binaries for your machine, or get a copy of the sources and compile them yourself." As I do not exactly know what compiling is, I targeted the "Precompiled Binaries for Linux"[5], but there I found 4 different things. Which of them would be the right one in order to try and use for my own php site, please? I understand that most users here are already super-experts, who do not need any advice. Is there a real (as opposed to [4]) tutorial for beginners, please? Luigi [1] http://www.sqlite.org/whentouse.html [2] http://webx18.aruba.it/ver.php [3] http://translate.google.com/translate?u=http%3A%2F%2Fassistenza.aruba.it%2Fkb%2Fidx%2F45%2F142%2Farticle%2FLinguaggi-supportati-su-Hosting-Linux.html&hl=it&ie=UTF-8&sl=it&tl=en [4] http://www.sqlite.org/quickstart.html [5] http://www.sqlite.org/download.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adding index to table makes SELECT much slower. Why?
SELECT lat, lon, depth FROM hydro WHERE depth>= 49.01 AND depth <=50.99; The SELECT may be faster still if you use this index instead: CREATE INDEX hydro_indx2 ON hydro (depth, lat, lon); as the query can find all the information in the index without hitting the pages of the main table. (At the expense of having a larger database file.) The data in the hydro table need not be presorted at population unless you need to pull out additional columns not listed in the index. Always try to create INDEXes after you populate the table (where possible) in order to have contiguous table and index pages. If you have the inclination you could also experiment with various page sizes. -- View this message in context: http://www.nabble.com/Adding-index-to-table-makes-SELECT-much-slower.-Why--tp19889143p19908299.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adding index to table makes SELECT much slower. Why?
When used with the index, the query may be producing too many random (slow) disk seeks into your 2GB+ database table. Increasing the page cache substantially may help. But if the rows of the main table were accessed in order it might also reduce the number of page seeks. Out of curiosity, without changing the page cache size, how long does this query take with the index in place? SELECT lat, lon, depth FROM hydro where rowid in ( select rowid from hydro WHERE depth>= 49.01 AND depth <= 50.99 ); If that doesn't work, you might try populating the hydro table with data sorted by depth in order to keep the data locality in check and disk seeks to a minimum. -- View this message in context: http://www.nabble.com/Adding-index-to-table-makes-SELECT-much-slower.-Why--tp19889143p19894289.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Network concurrency question
I would like to use SQLite from a network share. I would like to create a server app that would do all of the writing to the database except for certain tables, one table per client,the clients would write to their own table only. The client drops it's data/instructions into it's own table, the server app would scan all client tables for new data/instructions and then write the data to the main tables of the database. Would this work without concurrency issues? Another question I have is do I understand correctly that an SQLite database, on a network share, has no problems with many readers, the problem starts with many writers. Is this correct? Thanks, TD myhosting.com - Premium Microsoft® Windows® and Linux web and application hosting - http://link.myhosting.com/myhosting ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rtree performance problems?
see sqlite3_index_info.estimatedCost http://www.sqlite.org/cvstrac/chngview?cn=5649 > AFAICT, when you have a join where one table has a good index, the > virtual table cannot signal that it has an even better index. I could > not follow the index-selection logic well enough to have any > suggestions how to improve things. In the fts case, this breaks the > query because the MATCH operator _only_ works when used to access the > index. > > -scott -- View this message in context: http://www.nabble.com/rtree-performance-problems--tp19035092p19422728.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] On UNIQUE and PRIMARY KEY
> One occasionally sees SQLite schemas of the following form: > >CREATE TABLE meta(id LONGVARCHAR UNIQUE PRIMARY KEY, ); > > In other words, one sometimes finds a PRIMARY KEY and a UNIQUE > declaration on the same column. This works fine in SQLite, but it is > wasteful, both of disk space and of CPU time. I'm trying to reproduce the issue, but I only see a single index. What am I missing? SQLite version 3.5.3 Enter ".help" for instructions sqlite> CREATE TABLE meta(id LONGVARCHAR UNIQUE PRIMARY KEY, foo, bar); sqlite> select * from sqlite_master; table|meta|meta|2|CREATE TABLE meta(id LONGVARCHAR UNIQUE PRIMARY KEY, foo, bar) index|sqlite_autoindex_meta_1|meta|3| The database file above has 3 pages, as one would expect. SQLite version 3.6.1 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t1(id LONGVARCHAR UNIQUE, foo, bar); sqlite> create table t2(id LONGVARCHAR PRIMARY KEY, foo, bar); sqlite> create table t3(id LONGVARCHAR UNIQUE PRIMARY KEY, foo, bar); sqlite> select * from sqlite_master; table|t1|t1|2|CREATE TABLE t1(id LONGVARCHAR UNIQUE, foo, bar) index|sqlite_autoindex_t1_1|t1|3| table|t2|t2|4|CREATE TABLE t2(id LONGVARCHAR PRIMARY KEY, foo, bar) index|sqlite_autoindex_t2_1|t2|5| table|t3|t3|6|CREATE TABLE t3(id LONGVARCHAR UNIQUE PRIMARY KEY, foo, bar) index|sqlite_autoindex_t3_1|t3|7| And this database has 7 pages. -- View this message in context: http://www.nabble.com/On-UNIQUE-and-PRIMARY-KEY-tp19313570p19325957.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Understanding how SQLite works
I understand that the SQLite database resides in memory. I understand that the information in memory gets written to disk, ie saving parts that have been updated/whole database. I have read that SQLite has been known to support up 100,000 concurrent read connections and can support several terabytes of data. Now lets say a database is 10Gb in size and it is written to disk. Would not writing a 10Gb file to disk take a very long time? Now perhaps SQLite can just write the part that has changed to disk. If this is the case, then how does it know which sectors on the hard drive to update since it isn't writing the entire file to disk Can someone explain to me how all of this work? Thanks, TD mail2web.com - Microsoft® Exchange solutions from a leading provider - http://link.mail2web.com/Business/Exchange ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE operator and ATTACH databases memory usage
> Change cache sizes using separate cache_size > pragmas for each attached database. Thank you! (It would be nice if there is a hint for this behaviour in http://www.sqlite.org/lang_attach.html.) Is this correct? (At least it does not return an error) PRAGMA job01.cache_size=200 PRAGMA job02.cache_size=200 PRAGMA job03.cache_size=200 ...or should I better use PRAGMA default_cache_size when the database I created? So if the cache_size increases per attached database and my emebedded system has a limited amount of memory, the best solution to control the total amount of memory SQLite uses is to count / limit attached databases? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE operator and ATTACH databases memory usage
> Depending on what you are storing in fs_textid and what your LIKE
> pattern is, you might get much better performance (and lower memory
> usage) if you use GLOB instead of LIKE and if you explicitly code the
> pattern rather than using the wildcard "?", and if you create a new
> index:
> CREATE INDEX newidx1 ON fs_main(fs_itemtype, fs_textid);
Thank you for the optimization hints – I changed the application that it uses
all three suggestions and many queries
are now executed faster. Although, not if the search string begins with a
wildcard char-acter as described in http:
//www.sqlite.org/optoverview.html#like_opt under point 2. I assume this causes
again most of the database to be loaded
into cache as this was the case with the LIKE opera-tor.
> Are you sure that the memory is not freed? Calling free() does not
> normally return memory to the operating system so just because the
> process memory usage went up does *not* mean that the memory is still
> in use. It might just mean that the memory is being held by the
> malloc()/free() for possible reuse later. What does the
> sqlite3_memory_used() interface tell you? What about
> sqlite3_memory_highwater()?
I wrote two small example programs to illustrate the memory usage difference
between working on the main database and
working with multiple ATTACHed database. The example with just one main
database does not consume more than ~300KB
which seems to be pretty close to the specified 'cache_size'. The second
example with three attached database consumes
around 500KB per query and it looks like 'cache_size' setting is not relevant.
All memory seems to be freed correctly.
--- Example A ---
--- One database only, cache size limited to 250 pages ---
long rc = SQLITE_OK;
sqlite3_int64 mem01;
sqlite3_int64 mem02;
// Open database connection
sqlite3* m_pDB;
sqlite3_open("job01.db", &m_pDB); // application memory usage: 1.5 MB
// PRAGMA
sqlite3_stmt* pStmtPragma01(NULL);
rc = sqlite3_prepare_v2(m_pDB, "PRAGMA cache_size=250", -1, &pStmtPragma01,
NULL);
rc = sqlite3_step(pStmtPragma01);
rc = sqlite3_finalize(pStmtPragma01);
// Run queries
sqlite3_stmt* pStmtLike01(NULL);
rc = sqlite3_prepare_v2(m_pDB, "SELECT COUNT(fs_recid) FROM main.fs_main
WHERE fs_itemtype=10 AND fs_textid GLOB
'*1';", -1, &pStmtLike01, NULL);
mem01 = sqlite3_memory_used(); // 17937 ...application memory
usage: 1.8MB
rc = sqlite3_step(pStmtLike01);
mem01 = sqlite3_memory_used(); // 305725 ...application memory
usage: 2.0MB
rc = sqlite3_finalize(pStmtLike01);
mem01 = sqlite3_memory_used(); // 302855 ...application memory
usage: 2.0MB
mem02 = sqlite3_memory_highwater(1);// 305757
// Close database connection
sqlite3_close(m_pDB); // application memory usage: 1.5 MB <- all memory
is freed correctly
--- Example B ---
--- :memory: database and three attached databases, cache size limited to 250
pages ---
long rc = SQLITE_OK;
sqlite3_int64 mem01;
sqlite3_int64 mem02;
// Open database connection
sqlite3* m_pDB;
sqlite3_open(":memory:", &m_pDB); // application memory usage: 1.5 MB
// PRAGMA
sqlite3_stmt* pStmtPragma01(NULL);
rc = sqlite3_prepare_v2(m_pDB, "PRAGMA cache_size=250", -1, &pStmtPragma01,
NULL);
rc = sqlite3_step(pStmtPragma01);
rc = sqlite3_finalize(pStmtPragma01);
// ATTACH job databases
sqlite3_stmt* pStmtAttach01(NULL);
rc = sqlite3_prepare_v2(m_pDB, "ATTACH 'job01.db' AS _job01;", -1,
&pStmtAttach01, NULL);
rc = sqlite3_step(pStmtAttach01);
rc = sqlite3_finalize(pStmtAttach01);
sqlite3_stmt* pStmtAttach02(NULL);
rc = sqlite3_prepare_v2(m_pDB, "ATTACH 'job02.db' AS _job02;", -1,
&pStmtAttach02, NULL);
rc = sqlite3_step(pStmtAttach02);
rc = sqlite3_finalize(pStmtAttach02);
sqlite3_stmt* pStmtAttach03(NULL);
rc = sqlite3_prepare_v2(m_pDB, "ATTACH 'job03.db' AS _job03;", -1,
&pStmtAttach03, NULL);
rc = sqlite3_step(pStmtAttach03);
rc = sqlite3_finalize(pStmtAttach03);
// Run queries
sqlite3_stmt* pStmtLike01(NULL);
rc = sqlite3_prepare_v2(m_pDB, "SELECT COUNT(fs_recid) FROM _job01.fs_main
WHERE fs_itemtype=10 AND fs_textid
GLOB '*1';", -1, &pStmtLike01, NULL);
mem01 = sqlite3_memory_used(); // 42568 ...application memory
usage: 1.8MB
rc = sqlite3_step(pStmtLike01);
mem01 = sqlite3_memory_used(); // 5
Re: [sqlite] LIKE operator and ATTACH databases memory usage
Our e-mails crossed on the way to the list... sorry for the confusion. Thank you for your advices - i'll follow up them to make the mentioned wildcard search working. Basically, I'm looking for a way to have a fast wildcard search on a dataset that is distributed over multiple databases whose are attached to a main database. I hope I can do it with GLOB. At the moment, the question is still unanswered, why LIKE consumes multiple times 2.5MB when applied to attached dabases. Daniel Ursprüngliche Nachricht---- Von: [EMAIL PROTECTED] Datum: 15.08.2008 16:30 An: "General Discussion of SQLite Database" Betreff: Re: [sqlite] LIKE operator and ATTACH databases memory usage On Aug 15, 2008, at 8:06 AM, [EMAIL PROTECTED] wrote: > Hello > > Why does SQLite consume 2.5MB memory every time when running a > statement on a attached database with LIKE operator? > > Example 1: > SELECT fs_rec FROM fs_main WHERE fs_itemtype=? AND fs_textid > LIKE ?; // consumes <50kB RAM > > Example 2: > SELECT fs_rec FROM _job01.fs_main WHERE fs_itemtype=? AND fs_textid > LIKE ?; // consumes 2.5MB RAM (Note to mailing list readers: Daniel sent me a sample database by private email) The database is about 3MB in size and the example 2 query is probably doing something close to a full table scan. This causes most of the database to be loaded into cache. That will use about 2.5MB of RAM. The cache will flush itself automatically when you close the database connection or when the cache becomes stale. Depending on what you are storing in fs_textid and what your LIKE pattern is, you might get much better performance (and lower memory usage) if you use GLOB instead of LIKE and if you explicitly code the pattern rather than using the wildcard "?", and if you create a new index: CREATE INDEX newidx1 ON fs_main(fs_itemtype, fs_textid); See http://www.sqlite.org/optoverview.html#like_opt D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE operator and ATTACH databases memory usage
I'm going to check sqlite3_memory_used()/sqlite3_memory_highwater() next week as soon as I can. At the moment the following is clear: The application needs to run the same statement with LIKE operator for multiple attached databases. On the embedded side, the device crashes after a few statements because there is no more RAM. On the desktop pc side, all statements are successful, but process viewer shows an increased amount of memory used by the application. I would be happy if the problem is in our application, but I wasn't able to find anything yet. Why does the statement with LIKE (Example 2) consume the memory and the statement without LIKE (Example3) does not consume the memory on a ATTACH'ed database? Database schema: CREATE TABLE fs_main ( 'fs_recid' INTEGER PRIMARY KEY NOT NULL, 'fs_itemtype' INTEGER, 'fs_textid' TEXT, 'fs_flag1' INTEGER, 'fs_object' BLOB ); The size of a single record is typically 100 bytes. Please let me know if you didnt get the example database I sent directly to [EMAIL PROTECTED] Regards Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE operator and ATTACH databases memory usage
> This causes most of the database to be loaded into cache. Is there one cache per database connection or one cache per ATTACH'ed database? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE operator and ATTACH databases memory usage
I don’t dare to use the term “leak” here. It is hard so say at the moment where the memory is going to. At least, the memory is not freed when sqlite3_finalize() is called on the statement. Might it be possible, that this memory is allocated once per attached database and used for caching reasons? Attached… oh sorry, I meant: “a few databases are attached with the ATTACH command to the main in-memory database”. Please let me know if you really need the database file. I can see how the memory usage of my application increases by 2.5MB as soon as I call sqlite3_step(). Database and parameters: Records: Typically 20’000 records Values for ?: fs_itemtype is an integer between 0 and 100 Values for ?: fs_textid is a string with length between 0 and 16 characters Indexes: On fs_textid Indexes: There is another index on fs_itemtype but is not used here Environment: SQLite version: 3.5.9 (we will release our product very soon) Operation system: Windows XP and WinCE (problem exists on both platforms) Compiled: Ourselves with Visual Studio 2005 (no changes to source code) Regards Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] LIKE operator and ATTACH databases memory usage
Hello Why does SQLite consume 2.5MB memory every time when running a statement on a attached database with LIKE operator? Example 1: SELECT fs_rec FROM fs_main WHERE fs_itemtype=? AND fs_textid LIKE ?; // consumes <50kB RAM Example 2: SELECT fs_rec FROM _job01.fs_main WHERE fs_itemtype=? AND fs_textid LIKE ?; // consumes 2.5MB RAM SELECT fs_rec FROM _job02.fs_main WHERE fs_itemtype=? AND fs_textid LIKE ?; // consumes 2.5MB RAM SELECT fs_rec FROM _job03.fs_main WHERE fs_itemtype=? AND fs_textid LIKE ?; // consumes 2.5MB RAM SELECT fs_rec FROM _job04.fs_main WHERE fs_itemtype=? AND fs_textid LIKE ?; // consumes 2.5MB RAM Example 3: SELECT fs_rec FROM _job01.fs_main WHERE fs_itemtype=? AND fs_textid=?; // consumes <1KB RAM Memory is consumed immediately after the first sqlite3_step() is called. The PRAGMA cache_size is set to 2000 on the in-memory main database. Regards Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Amount of memory for caching one page with x byte page size?
Hello, to answer the following question: Amount of memory for caching one page with x byte page size? I found in the Draft 3.6.0 Doc the following information: PRAGMA page_size = bytes; Query or set the page size of the database. The page size may only be set if the database has not yet been created. The page size must be a power of two greater than or equal to 512 and less than or equal to SQLITE_MAX_PAGE_SIZE. The maximum value for SQLITE_MAX_PAGE_SIZE is 32768. PRAGMA default_cache_size = Number-of-pages; Query or change the maximum number of database disk pages that SQLite will hold in memory at once. Each page uses 1K on disk and about 1.5K in memory. ... Obviously you have an overhand per page-size to calculate the memory requirement for caching on page. Do I understand this right? kind regards Rainer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Windows CE performance
Hello As usual, there is no general rule. You have to define the best settings for your embedded environement yourself. Start here: "[sqlite] Performance tuning using PRAGMA, other methods" http://www.mail-archive.com/sqlite-users@sqlite.org/msg29343.html Try different settings to get the feeling which PRAGMAs are critical for you. Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Windows CE performance
Hello Filipe Have you already checked your PRAGMA settings four your embedded version? (see http://www.sqlite.org/pragma.html) On which operation do you have you performance decrease? Is it INSERT performance? Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COUNT() on indexed tables / primary keywith 100'000records
Hello Thank you for you quick responses. If I disable the index with + it takes around 50% more execution time. I supposed a very fast query even with many records, because COUNT( ) might do its work on the index only. I this case, I have to find another solution to get the number of records quickly. The often discussed "trigger COUNT() solution" might not be applicable here. Daniel Ursprüngliche Nachricht---- Von: [EMAIL PROTECTED] Datum: 14.07.2008 00:51 An: Betreff: Re: [sqlite] COUNT() on indexed tables / primary keywith 100'000records <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Lets say i have 120'000 records and worst case is that upto 90'000 > records match the conditions. Well, if you count almost all records, why do you expect the query to run substantially faster than the one that in fact counts all records? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COUNT() on indexed tables / primary key with 100'000records
Hello Lets say i have 120'000 records and worst case is that upto 90'000 records match the conditions. Daniel Ursprüngliche Nachricht---- Von: [EMAIL PROTECTED] Datum: 13.07.2008 17:16 An: Betreff: Re: [sqlite] COUNT() on indexed tables / primary key with 100'000records <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I have following table with around 100'000 rows / ~10MB on a embedded > device: > > CREATE TABLE 'fs_main' ( > 'fs_recid' INTEGER PRIMARY KEY NOT NULL, > 'fs_contenttype' INTEGER, > 'fs_itemtype' INTEGER, > 'fs_job' INTEGER, > 'fs_textid' TEXT,<- ~5 chars per Record > 'fs_flag1' INTEGER, > 'fs_object' BLOB <- ~100 Bytes per Record > ); > > Indexed by: > > CREATE INDEX 'index_fs_itemjobcontent' ON fs_main ( > fs_itemtype ASC, > fs_job ASC, > fs_contenttype ASC, > fs_recid ASC > ); > > I need to count different result sets and i'm doing that this way: > > SELECT COUNT(fs_recid) AS num FROM fs_main WHERE ( fs_itemtype=18 ) > AND fs_contenttype=2 AND fs_job=1 > > ...which takes around 4 sec. How many of your records satisfy this condition? If the condition selects 10% or more of all records, then not using the index may actually be faster. If you want to try suppressing the index, use "WHERE +fs_itemtype=18 ..." (note the unary plus). Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] COUNT() on indexed tables / primary key with 100'000 records
Hello I have following table with around 100'000 rows / ~10MB on a embedded device: CREATE TABLE 'fs_main' ( 'fs_recid' INTEGER PRIMARY KEY NOT NULL, 'fs_contenttype' INTEGER, 'fs_itemtype' INTEGER, 'fs_job' INTEGER, 'fs_textid' TEXT,<- ~5 chars per Record 'fs_flag1' INTEGER, 'fs_object' BLOB <- ~100 Bytes per Record ); Indexed by: CREATE INDEX 'index_fs_itemjobcontent' ON fs_main ( fs_itemtype ASC, fs_job ASC, fs_contenttype ASC, fs_recid ASC ); I need to count different result sets and i'm doing that this way: SELECT COUNT(fs_recid) AS num FROM fs_main WHERE ( fs_itemtype=18 ) AND fs_contenttype=2 AND fs_job=1 ...which takes around 4 sec. EXPLAIN QUERY PLAN tells me: TABLE fs_main WITH INDEX index_fs_itemjobcontent Why does it take so long? Is SQLite fetching the whole table even if COUNT() is applied on the primary key? Regards Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA synchronous = OFF on transaction-safe file system TFAT WinCE
Hi! We have a 14MB SQLite database on a 16MB flash disk. The journal file gets to big on some queries, which results in a SQLITE_FULL error. Any other ideas to make data storage secure? Regards Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PRAGMA synchronous = OFF on transaction-safe file system TFAT WinCE
Hello Is it safe do set PRAGMA synchronous = OFF when a transaction-safe file system is used? We are working on WinCE with TFAT (see below) - but this might be a general question. Regards Daniel TFAT: The original file allocation table (FAT) file system enabled file modification operations to be interrupted before completion. In this way, actions such as sudden power loss or sudden removal of a storage card frequently resulted in data loss and file system corruption. By making file operations transaction-safe, TFAT stabilizes the file system and ensures that the file system is not corrupted when an interruption occurs. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't create table from a trigger
>De: [EMAIL PROTECTED] >Fecha: 27/05/2008 19:56 > >It's not supposed to, according to >http://sqlite.org/lang_createtrigger.html . The syntax >only allows select, insert, update and delete statements. > >What are you trying to achieve? I need to handle tables with several million records, on realtime, from RAM. One of the fields takes few values (say, company website id), but new ids are added from time to time. I would prefer to dynamically create a set of tables when a new id shows up, for the improved locality of reference plus reduced overhead from the website id and its non-unique index. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can't create table from a trigger
Does not work: CREATE TABLE ttt ( t INTEGER PRIMARY KEY ); CREATE TRIGGER ttt_new_trigger AFTER INSERT ON ttt FOR EACH ROW BEGIN CREATE TABLE uuu ( u INTEGER PRIMARY KEY ); END; SQL error: near "CREATE": syntax error If I try the CREATE TABLE outside the trigger, it succeds. If I replace CREATE TABLE... with SELECT 1, SQLite accepts the trigger. Is this a limitation, or am I missing something? Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] blob error
Hello i'm using sqlite with turbogears, and any time i try to store a file in a blobcol of more that 1mb i get this error: DataError: String or BLOB exceeded size limit reading the paper the default value of sqlite for blobclo is 10 but why i get this error Thanks and Regards Luca ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite2 download ?
Thanks Keith, found them here: http://www.sqlite.org/sqlite-source-2_8_17.zip On Sat, 10 May 2008 07:08:44 -0700 "Keith Goodman" <[EMAIL PROTECTED]> wrote: > On Sat, May 10, 2008 at 4:38 AM, [EMAIL PROTECTED] > <[EMAIL PROTECTED]> wrote: > > where do I find the latest sqlite2 tarball ? > > If you have a cvs client: > > (from http://www.sqlite.org/download.html) > > All SQLite source code is maintained in a CVS repository that is > available for read-only access by anyone. You can interactively view > the repository contents and download individual files by visiting > > http://www.sqlite.org/cvstrac/dir?d=sqlite. > > To access the repository directly, use the following commands: > > cvs -d :pserver:[EMAIL PROTECTED]:/sqlite login > cvs -d :pserver:[EMAIL PROTECTED]:/sqlite checkout sqlite > > When the first command prompts you for a password, enter "anonymous". > > To access the SQLite version 2.8 sources, begin by getting the 3.0 > tree as described above. Then update to the "version_2" branch as > follows: > > cvs update -r version_2 > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite2 download ?
Hi All, where do I find the latest sqlite2 tarball ? Thanks for supporting, Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Memory Leak on select ?
I'm using sqlite3 on embedded system.
I've got a very very large
memory usage.
My program must loop to show some values catched from a
table using SELECT statement.
In a while memory usage grow over
phisical RAM, so my device lock.
If I run the same code on a PC after
some minutes the process use a very large amount of memory (512M+).
I
need to use some function to force memory release or I make some
mistake ?
Thanks in advance
Pierluigi
Follow sample code:
#include
#include
#include
#include
int main (int argc, char *argv[])
{
// Define variables
sqlite3
*db;
int rc,i;
char *zErrMsg = 0;
char *zSql;
char *prevStatus;
sqlite3_stmt *stmt;
struct data
{
int iPortNumber;
float
fRangeMin;
float fRangeMax;
float fOffset;
float fMinL;
float fMinLL;
float fMaxH;
float fMaxHH;
int iDelay;
int
iHyster;
int iAlarm;
char *sTimeAlarm;
};
struct data riga
[16];
// Open Database
rc = sqlite3_open("/tmp/test.db", &db);
if(
rc )
{
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg
(db));
sqlite3_close(db);
exit(-1);
}
for(rc=0; rc <
1; rc++)
{
// Init variables
i=0;
zSql =
sqlite3_mprintf("SELECT PortNumber, RangeMin, RangeMax, Offset, MinL,
MinLL, MaxH, MaxHH, Delay, Hysteresis, " \
"Alarm,
TimeAlarm FROM input_ai WHERE Address=%d and Monitor='Y';", 7);
if
(sqlite3_prepare(db, zSql, -1, &stmt, 0) != SQLITE_OK)
return(-1);
// Save at least 16 rows
while ((sqlite3_step(stmt)
==SQLITE_ROW) & (i < 16))
{
riga[i].iPortNumber =
sqlite3_column_int(stmt,0);
riga[i].fRangeMin =
sqlite3_column_double(stmt,1);
riga[i].fRangeMax =
sqlite3_column_double(stmt,2);
riga[i].fOffset =
sqlite3_column_double(stmt,3);
riga[i].fMinL =
sqlite3_column_double(stmt,4);
riga[i].fMinLL =
sqlite3_column_double(stmt,5);
riga[i].fMaxH =
sqlite3_column_double(stmt,6);
riga[i].fMaxHH =
sqlite3_column_double(stmt,7);
riga[i].iDelay =
sqlite3_column_int(stmt,8);
riga[i].iHyster = sqlite3_column_int
(stmt,9);
riga[i].iAlarm = sqlite3_column_int(stmt,10);
riga[i].sTimeAlarm = (char *) sqlite3_column_text(stmt,11);
i++;
}
sqlite3_finalize(stmt);
}
sqlite3_close(db);
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PHP 4 and SQLite3
Hello I must use SQLite functions of PHP 4, but it seems that these functions doesn't works with SQLite 3. What can I do? best regards Giovanni Rossati ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update fail without ERRORS
SOLVED!
Thanks to all and expecially to Dennis
I've found MY
MISTAKE!!! :-)
My head is safe ;-)
The error was in
my_custom_function.
Inside this function I need to query the same table
to retrieve some values needed to calculate the float value.
Inside
my_custom_function I use the prepare ,step, finalize functions, but I
forgot to use sqlite3_finalize before to exit, so the table was locked,
or better, was locked the same RECORD i need to update, because the
where statement is the same.
In this conditions the update fail, but
the strange thing is that I don't receive any error.
The result of
update statement is SQLITE_OK (this is very strange, it's normal or
it's a sqlite bug ??).
Follow I attach my code "CORRECT".
Dennis,
please can You check the correctness ?
float expander::upd_ai_val(int
Address, int Port)
{
int rc;
char sql[2048],[512];
char *zErrMsg
= 0;
char *zSql;
pkt rcvd_frm;
long int value;
time_t rawtime;
float valore;
char val[25];
time(&rawtime);
printf("Address=%d -
Port=%d\n",Address,Port);
inp_ana_inp_req(Address, Port, &rcvd_frm);
value = (rcvd_frm.arg[1]*256 + rcvd_frm.arg[2]);
valore =
my_custom_function(Address, Port, value);
//valore=value;
if
(debug > 1) {printf("Arg[1]=%x - Arg[2]=%x - Valore letto=%ld\n",
rcvd_frm.arg[1],rcvd_frm.arg[2],value);}
zSql = sqlite3_mprintf
("UPDATE inputai SET Value=%.2f, Timestamp=%d WHERE Address=%d and
PortNumber=%d;",valore , Port,Address, Port);
if (debug > 1) {printf
("upd_ai_val:%s\n",sql);}
printf("upd_ai_val:%s\n",zSql);
rc =
sqlite3_exec(db,zSql, NULL, NULL, &zErrMsg );
if( rc!=SQLITE_OK )
{
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free
(zErrMsg);
return(-1);
}
return(valore);
}
float expander::
my_custom_function(int Address, int PortNumber, int Valore)
{
char sql
[2048];
int i;
float RangeMin, RangeMax, Offset;
sqlite3_stmt
*stmt;
/* Init Variables */
RangeMin=0;
RangeMax=0;
Offset=0;
//char sA[3];
//sprintf(sA,"%s",Address);
//printf("calcola_valore:
Address=%d/%s - Porta=%d/%s\n",Address, sA, PortNumber, itos
(PortNumber));
strcpy(sql,"SELECT RangeMin, RangeMax, Offset FROM
inputai WHERE Address=");
strcat(sql,itos(Address));
strcat(sql," and
PortNumber=");
strcat(sql,itos(PortNumber));
printf("CV_SQL=%s\n",
sql);
if (sqlite3_prepare(db, sql, -1, &stmt, 0) != SQLITE_OK)
{
// printf("CV_Errore prepare\n");
return(-1);
}
if (sqlite3_step
(stmt)==SQLITE_ROW)
{
for (i=0; i< sqlite3_column_count
(stmt); i++)
{
/* Column are in the SELECT order */
if (strcasecmp(sqlite3_column_name(stmt,i),"RangeMin") == 0)
RangeMin = atof((char *)sqlite3_column_text(stmt,i));
else
if (strcasecmp(sqlite3_column_name(stmt,i),"
RangeMax") == 0)
RangeMax = atof((char *)
sqlite3_column_text(stmt,i));
else
if
(strcasecmp(sqlite3_column_name(stmt,i),"Offset") == 0)
Offset = atof((char *)sqlite3_column_text(stmt,i));
}
}
//
The follow row is that one I forgot :-)
sqlite3_finalize
(stmt);
return(Valore * (RangeMax - RangeMin)/1023 +
RangeMin + Offset);
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update fail without ERRORS
I've found what's matter, but I don't understand why ?
I've tried to
use the sqlite3_mprintf() to prepare my sql.
If my numeric fValue is
integer the SQL works fine, but if I've got a floating value the sql
doesn't update the table.
And the bad is that I can get any error
I'll try to explain me better using a part of my code.
int iLow,
iHigh;
long int liValue;
float fValue;
char *zSQL;
int Address;
int
Port;
liValue = iHigh*256 + iLow;
fValue = my_custom_function
(liValue);
// If I uncomment the follow row the code works fine
//
fValue = liValue;
zSQL = sqlite3_mprintf("UPDATE inputai SET Value=%.
2f, Timestamp=%d WHERE Address=%d and PortNumber=%d;",fValue , Port,
Address, Port);
rc = sqlite3_exec(db,zSql, NULL, NULL, &zErrMsg );
if(
rc!=SQLITE_OK )
{
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
return(-1);
}
This function doesn't works!!!
I can't update my values!!!
BUT... if remove the comment and the
program execute fValue=liValue; statement the table is updated !!!
??
What's my mistake ??
Pierluigi
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update fail without ERRORS
>The code looks OK except for the typo (i.e. sPre[2048[ should be
>
sPre[2048]).
Yes, It's a mistype on the mailinglist, the code it's ok!
> I assume that your real table isn't named "table" since that is a
keyword.
The real table name is "inputai"
> Can you open the database
file using the sqlite3 command line utility
> and execute a select
query to return or count the number of rows that
> match your update
condition?
Yes. I can open the database and I can run with success the
same command.
I copy and paste the program output (printf sql) to
sqlite3 command line utility then I press Return and the table is
updated!!!
>select count(*) from your_table where Address=7 and Port=1
> If that gives a zero result you have your answer.
I've got "1"
>You
might also want to show the create statement you used for the table
>you are trying to update.
I create the table using some code:
int
create_input_ai()
{
int rc;
char sql[512];
char *zErrMsg = 0;
printf("CREATE_INPUT_AI\n");
strcpy(sql,"CREATE TABLE inputai
(Address INTEGER,PortDescription TEXT,PortNumber INTEGER,PortType TEXT,
PortResolution INTEGER,Value REAL,UM TEXT,"\
"RangeMin REAL,RangeMax
REAL,Offset REAL,MinL REAL,MinLL REAL,MaxH REAL,MaxHH REAL,Delay
INTEGER,Hysteresis INTEGER,Alarm TEXT,Users TEXT,"\
"Action TEXT,
Monitor TEXT,TimeStamp TEXT,TimeAlarm TEXT);");
rc = sqlite3_exec(db,
sql, NULL, NULL, &zErrMsg );
if( rc!=SQLITE_OK )
{
fprintf(stderr,
"SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
return(-1);
}
return(0);
}
I don't know where I must crash my head :-(
Pierluigi Bucolo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update fail without ERRORS
The code is very long, I'll try to put here the core of my application.
I'm using a C++ Class where one function is "sqlraw" that I use to
execute a SQL statement:
CLASS DEFINITION
sqlite3 *db;
int expander::
open_db(char * pDbName)
{
int rc;
rc = sqlite3_open(pDbName, &db);
if( rc )
{
fprintf(stderr, "Can't open database: %s\n",
sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
return(0);
}
int expander::sqlraw(char *pSql)
{
int rc;
char *zErrMsg = 0;
printf("SQLRAW: SQL=%s\n",pSql);
printf("Database %d\n",db);
rc =
sqlite3_exec(db,pSql, NULL, NULL, &zErrMsg );
printf("SQLRAW: Stato=%d
- OK=%d\n",rc, SQLITE_OK);
if( rc!=SQLITE_OK )
{
fprintf(stderr,
"SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
return(-1);
}
return(0);
}
int main(int argc ,char *argv[])
{
expander expio;
char sPre[2048[;
expio.open("test.db");
strcpy(sPre,"UPDATE
table SET Value=12.3 WHERE Address=7 and Port=1");
if (expio.sqlraw
(sPre) == 0)
{
/ / Action for no error
}
else
{
// Manage error conditions
}
When I execute the code, sqlraw
function print the pSql string, and this is the same I pass.
The
Database descriptor is the same returned from open function, and status
code is OK!!!
But table value isn't updated.
I don't understand
what's matter, and because i haven't any error message I can't debug
it.
Any suggestion is VERY VERY appreciate
Pierluigi Bucolo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Update fail without ERRORS
I'm working on a program using sqlite library, but I've got an issue that I can't solve. Suddenly, my program don't update the tables I don't understand whats matter because, if I write SQL instructions using Sqlite3 client, UPDATE works fine, and I haven't any ERROR CODE. sqlite3_exec function return SQLITE_OK, but tables aren't updated. How can I debug this issu ? Pierlugi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite3 and PHP 4.4
Hello My problem is that the SQLites Functions don't recognize the DBs SQLite3, and Bambalan uses PHP 4.4 that it doesn't support PDO. I believe that the problem is resolvable because I have developed an application with Roadsend and SQLite3 with PHP 4 without problems. I also have another silly idea a terminal vonNewman machine: how I can create an executable file that also contains the data and that it is obviously updateable? Best regards Giovanni Rossati ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Version 3.5.5 Released
Thanks, that helped. [EMAIL PROTECTED] wrote: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: Got the sqlite3.exe version 3.5.5 but .explain still doesn't seem to work. Maybe it's me, could someone post a simple example of .explain working? C:\ sqlite3 SQLite version 3.5.5 Enter ".help" for instructions sqlite> create table t1(x); sqlite> .explain sqlite> EXPLAIN SELECT * FROM t1; [explain output follows the above]. -- D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Version 3.5.5 Released
Got the sqlite3.exe version 3.5.5 but .explain still doesn't seem to work. Maybe it's me, could someone post a simple example of .explain working? [EMAIL PROTECTED] wrote: Also, .schema and .explain don't seem to do anything in then sqlite3.exe version 3.5.4. I downloaded in sqlite-3_5_5.zip Mohd Radzi Ibrahim wrote: Hi, The Precompiled Binary for Windows : the command-line sqlite-3_5_5.zip still contain the 3.5.4 version regards, Radzi. - Original Message - From: <[EMAIL PROTECTED]> To: Sent: Friday, February 01, 2008 1:33 AM Subject: [sqlite] Version 3.5.5 Released SQLite version 3.5.5 is now available for download from the SQLite website: http://www.sqlite.org/ The big change from version 3.5.4 is that the internal virtual machine was reworked to use operands in registers rather than pulling operands from a stack. The virtual machine stack has now been removed. The removal of the VM stack will help prevent future stack overflow bugs and will also facilitate new optimizations in future releases. There should be no user-visible changes to the operation of SQLite in this release, except that the output of EXPLAIN looks different. In order to make this change, about 8.5% of the core SQLite code had to be reworked. We thought this might introduce instability. But we have done two weeks of intensive testing, during which time we have increased the statement test coverage to 99% and during which we have found and fixed lots of minor bugs (mostly things like leaking memory following a malloc failure). But for all of that testing, we have not detected a single bug in the new register-based VM. And for that reason, we believe the new VM, and hence version 3.5.5, is stable and ready for production use. As usual, please report any problems to this mailing list, or directly to me. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Version 3.5.5 Released
Also, .schema and .explain don't seem to do anything in then sqlite3.exe version 3.5.4. I downloaded in sqlite-3_5_5.zip Mohd Radzi Ibrahim wrote: Hi, The Precompiled Binary for Windows : the command-line sqlite-3_5_5.zip still contain the 3.5.4 version regards, Radzi. - Original Message - From: <[EMAIL PROTECTED]> To: Sent: Friday, February 01, 2008 1:33 AM Subject: [sqlite] Version 3.5.5 Released SQLite version 3.5.5 is now available for download from the SQLite website: http://www.sqlite.org/ The big change from version 3.5.4 is that the internal virtual machine was reworked to use operands in registers rather than pulling operands from a stack. The virtual machine stack has now been removed. The removal of the VM stack will help prevent future stack overflow bugs and will also facilitate new optimizations in future releases. There should be no user-visible changes to the operation of SQLite in this release, except that the output of EXPLAIN looks different. In order to make this change, about 8.5% of the core SQLite code had to be reworked. We thought this might introduce instability. But we have done two weeks of intensive testing, during which time we have increased the statement test coverage to 99% and during which we have found and fixed lots of minor bugs (mostly things like leaking memory following a malloc failure). But for all of that testing, we have not detected a single bug in the new register-based VM. And for that reason, we believe the new VM, and hence version 3.5.5, is stable and ready for production use. As usual, please report any problems to this mailing list, or directly to me. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Quick question about multithread and SQLITE_BUSY/SQLITE_LOCKED in 3.5.4
I'd like to use reader-writer lock in the client code, but at this point I'm not sure if I can determine at which point SQLite is not writing. I mean, INSERT / UPDATE are most likely need a writer lock, but I don't know if SELECT is guaranteed to be read-only in its internal operation within SQLite when I set SQLITE_THREADSAFE=0. Implementing an efficient RW lock on Windows XP is another challenge anyway. -- sword On Sat, 19 Jan 2008 22:56:43 +0100 Jens Miltner <[EMAIL PROTECTED]> wrote: > > Am 19.1.08 um 03:13 schrieb [EMAIL PROTECTED]: > > > OK I figured out SQLITE_THREADSAFE=0 for the second question... > > And it seems the answer for the first question is yes, but if you know > > a simpler way please share it with us, thanks! > > You could use a read-write mutex to serialize access to your database > connection. That way you can have multiple readers, but modifying the > database becomes an exclusive operation. This matches the sqlite > requirements. > Alternatively, you can just retry your write queries if you get > SQLITE_BUSY errors... > > > > > > > -- sword > > > > On Sat, 19 Jan 2008 09:57:10 +0900 > > "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > > >> Hello all, > >> > >> I've read http://www.sqlite.org/lockingv3.html but am still not > >> sure about > >> multithread and locking in 3.5.4. > >> > >> I have a multithread application that has a single connection to a > >> single > >> SQLite3 database. Since it's multithreaded, SQL statements are > >> thrown to > >> a single SQLite3 object concurrently. I'm using > >> http://www.sqlite.org/sqlite-amalgamation-3_5_4.zip > >> on VC8 + WindowsXP. > >> > >> Prior to this version (I was using SQLite2) I'd serialized all > >> these database access > >> using critical sections and didn't care about SQLITE_BUSY or > >> SQLITE_LOCKED > >> since they never happen. It was very simple as I didn't need to > >> implement access > >> retry for a busy case. > >> > >> However, I learned that SQLite 3.5 does mutexing by default. So I > >> removed > >> all synchronization stuff in my SQLite access code, and now it seems > >> it's not working as I intended. Unfortunately I can't reproduce it > >> in my > >> development environment and I've not yet implemented logging to see > >> if it's due to SQLITE_BUSY or SQLITE_LOCKED. I saw it's entering > >> sqlite3_mutex_enter multiple times in the debugger though, so it's > >> thread-safe > >> at least. > >> > >> My question is, > >> > >> 1. Do I still have to synchronize all SQLite access in my client > >> code not to > >> encounter SQLITE_BUSY or SQLITE_LOCKED? (Or is there any better way?) > >> > >> 2. If so, how can I turn off all these mutexes (critical sections) > >> in SQLite 3.5.4? > >> They are needless if I serialize all SQLite access in the client > >> code. > >> > >> Regards, > >> > >> -- sword > >> > >> > >> > >> - > >> To unsubscribe, send email to [EMAIL PROTECTED] > >> - > > > > > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Quick question about multithread and SQLITE_BUSY/SQLITE_LOCKED in 3.5.4
OK I figured out SQLITE_THREADSAFE=0 for the second question... And it seems the answer for the first question is yes, but if you know a simpler way please share it with us, thanks! -- sword On Sat, 19 Jan 2008 09:57:10 +0900 "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > Hello all, > > I've read http://www.sqlite.org/lockingv3.html but am still not sure about > multithread and locking in 3.5.4. > > I have a multithread application that has a single connection to a single > SQLite3 database. Since it's multithreaded, SQL statements are thrown to > a single SQLite3 object concurrently. I'm using > http://www.sqlite.org/sqlite-amalgamation-3_5_4.zip > on VC8 + WindowsXP. > > Prior to this version (I was using SQLite2) I'd serialized all these database > access > using critical sections and didn't care about SQLITE_BUSY or SQLITE_LOCKED > since they never happen. It was very simple as I didn't need to implement > access > retry for a busy case. > > However, I learned that SQLite 3.5 does mutexing by default. So I removed > all synchronization stuff in my SQLite access code, and now it seems > it's not working as I intended. Unfortunately I can't reproduce it in my > development environment and I've not yet implemented logging to see > if it's due to SQLITE_BUSY or SQLITE_LOCKED. I saw it's entering > sqlite3_mutex_enter multiple times in the debugger though, so it's thread-safe > at least. > > My question is, > > 1. Do I still have to synchronize all SQLite access in my client code not to > encounter SQLITE_BUSY or SQLITE_LOCKED? (Or is there any better way?) > > 2. If so, how can I turn off all these mutexes (critical sections) in SQLite > 3.5.4? > They are needless if I serialize all SQLite access in the client code. > > Regards, > > -- sword > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Quick question about multithread and SQLITE_BUSY/SQLITE_LOCKED in 3.5.4
Hello all, I've read http://www.sqlite.org/lockingv3.html but am still not sure about multithread and locking in 3.5.4. I have a multithread application that has a single connection to a single SQLite3 database. Since it's multithreaded, SQL statements are thrown to a single SQLite3 object concurrently. I'm using http://www.sqlite.org/sqlite-amalgamation-3_5_4.zip on VC8 + WindowsXP. Prior to this version (I was using SQLite2) I'd serialized all these database access using critical sections and didn't care about SQLITE_BUSY or SQLITE_LOCKED since they never happen. It was very simple as I didn't need to implement access retry for a busy case. However, I learned that SQLite 3.5 does mutexing by default. So I removed all synchronization stuff in my SQLite access code, and now it seems it's not working as I intended. Unfortunately I can't reproduce it in my development environment and I've not yet implemented logging to see if it's due to SQLITE_BUSY or SQLITE_LOCKED. I saw it's entering sqlite3_mutex_enter multiple times in the debugger though, so it's thread-safe at least. My question is, 1. Do I still have to synchronize all SQLite access in my client code not to encounter SQLITE_BUSY or SQLITE_LOCKED? (Or is there any better way?) 2. If so, how can I turn off all these mutexes (critical sections) in SQLite 3.5.4? They are needless if I serialize all SQLite access in the client code. Regards, -- sword ----- To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite and CE 4.1
Does sqlite supports ce 4.1? I've tried several wrappers for .net and also system.data.sqlite which supports cf 2 and native code but It doesn't work (the test testce exe crashes after two lines of output). Does sqlite natively supports CE 4.1? Thanks -- AlphaC - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Who has the sqlite3.dll and lib for version 3_3_14 for windows
Open your VS2005 command prompt, type: Lib /def:sqlite3.def it will generate proper sqlite3.lib for you. /Mike/ learning Sqlite3 wrote: > Hello, > > Who have the sqlite3.dll and sqlite3.lib of version 3_3_14 for windows. > > I created them wtih visual studio 2005. But they did not work. > > Or tell me how I can create them with visual studio 2005 porperly? > > Thanks! > > _ > Invite your mail contacts to join your friends list with Windows Live Spaces. > It's easy! > http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us > --------- To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Vista-IE7 problem
Barabbas Jiang Gmail <[EMAIL PROTECTED]> writes: > > Hi all, > > I found another strange problem of SQLite on Vista with IE7. > I have an input method with SQLite. When I opened it in IE7 for > typing Chinese, the SQLite returned nothing. However, *if IE7 is > "run as administrator," SQLite works.* > > Generally I know IE7 on Vista has a implicit security rules: > once it thinks you're doing some guilty IPC from other DLLs, > it drops all messages. > > The strange thing is, *SQLite 3.2.5 and before worked, but > 3.2.6 and later not.* I revisited this problem recently, seems IE7 solved some mysterious issue and then SQLite3 does not conflict with IE7 protected mode anymore. :) /Mike/ ----- To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Reading error outside the while
John Stanton a écrit :
John Stanton wrote:
[EMAIL PROTECTED] wrote:
John Stanton a écrit :
[EMAIL PROTECTED] wrote:
Hello,
I got an error when I try to read some data outside the while{},
inside the while{} it's ok, an idea ?
test.db have just one "table1" and a "field1" with values "one",
"two", "three".
#include
#include
int main(void)
{
sqlite3 *db;
sqlite3_stmt *pStat;
const char *dbname = "test.db";
const char *sql = "SELECT * FROM table1";
const unsigned char *my_array[3];
int i=0;;
sqlite3_open(dbname, &db);
sqlite3_prepare_v2(db, sql, -1, &pStat, 0);
while(sqlite3_step(pStat) == SQLITE_ROW)
{
my_array[i] = sqlite3_column_text(pStat, 0);
printf ("%s\n",my_array[i]); // ok
i++;
}
for (i = 0; i<3; i++);{
printf ("%s\n", my_array[i]); // error
}
sqlite3_finalize(pStat);
sqlite3_close(db);
return 0;
}
Fred.
Your program is wrongly structured. Try this layout:
sqlite3_prepare_V2
while (TRUE) {
rc = sqlite3_step
switch (rc) {
case SQLITE_ROW:
/*Get each column*/
for (count = 0; count > sqlite3_column_count; count++) {
switch (sqlite3_column_type) {
case SQLITE_TEXT:
pt = sqlite3_column_text
/*Move text into your output*/
sprintf(outval, "%s", pt); /*Or some other move.*/
break;
case SQLITE_INTEGER:
outnbr = sqlite3_column_int;
or
sprintf(outval, "%d", sqlite_column_int(..));
break;
add other types
}
}
break;
case SQLITE_DONE:
sqlite3_finalize
return from function
case SQLITE_BUSY:
/*Handle BUSY condition.*/
break;
default:
/*Handle error condition.*/
break;
} /*switch*/
} /*while*/
Now you can handle errors, busy conditions and the return of differring
types from Sqlite. When you get a pointer to a text value from
Sqlite it is the programmer's responsibility to move data from that
pointer into data in your program.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Hello John,
Thanks for reply,
I just tested with your code that seem to be more useful, but
something is wrong :
#include
#include
int main(void) {
sqlite3 *db;
sqlite3_stmt *pStat;
const gchar *dbname = "test.db";
int colcnt;
int rc;
int finished = 0;
rc = sqlite3_open(dbname, &db);
if (rc != SQLITE_OK) {
printf("error\n");
exit(1);
}
rc = sqlite3_prepare_v2(db, "SELECT * FROM TABLE1", -1, &pStat, 0);
if (rc != SQLITE_OK) {
printf("error\n");
exit(1);
}
while(!finished) {
rc = sqlite3_step(pstat);
switch (rc) {
case SQLITE_ROW:
/*Get each column*/
for (colcnt = 0; colcnt < sqlite3_column_count(pStat);
colcnt++) {
/*Handle returned data according to type.*/
switch (sqlite3_column_type(pStat, 0)) {
case SQLITE_TEXT:
printf("%s %s ", sqlite3_colimn_name(pstat, colcnt),
sqlite3_column_text(pStat,0));
break;
case SQLITE_INTEGER:
printf("%s %d ", sqlite3_column_name(pstat, colcnt),
sqlite3_column_int(pstat);
break;
/*!!!There are more types like SQLITE_NULL ...*/
} /*switch*/
}/*for*/
printf("\n");
break;
case SQLITE_BUSY:
/*Busy logic*/
break;
case SQLITE_DONE:
sqlite3_finalize(pstat);
finished = 1;
break;
default:
printf("Error\n");
break;
} /*while*/
sqlite3_close(db);
exit(0);
}
You left out quite a bit of the example. I have corrected it, but not
tested the code. This should print out a table of what you read.
Take note that Sqlite does not have fixed types, so the programmer
must be aware of that.
You also need to be aware of error conditions and busy states.
Whoops, I left out the sqlite3_step. Corrected.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-----
Works perfectly, thanks you very much.
Fred.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: Reading error outside the while
Igor Tandetnik a écrit :
[EMAIL PROTECTED] wrote:
while(sqlite3_step(pStat) != SQLITE_DONE)
{
switch (sqlite3_step(pStat)) {
You call sqlite3_step twice on every iteration, which means you are only
looking at every other row. That's probably not what you wanted.
case SQLITE_ROW:
/*Get each column*/
for (i = 0; i < sqlite3_column_count(pStat); i++)
{
switch (sqlite3_column_type(pStat,0))
{
case SQLITE_TEXT:
printf("%s ", sqlite3_column_text(pStat,0));
break;
}
break;
}
Why do you run the 'for' loop, if you unconditionally break out of it on
the very first iteration anyway?
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
ok thanks i understand my errors, is there a function to count the number of
rows ?
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Reading error outside the while
John Stanton a écrit :
[EMAIL PROTECTED] wrote:
Hello,
I got an error when I try to read some data outside the while{},
inside the while{} it's ok, an idea ?
test.db have just one "table1" and a "field1" with values "one",
"two", "three".
#include
#include
int main(void)
{
sqlite3 *db;
sqlite3_stmt *pStat;
const char *dbname = "test.db";
const char *sql = "SELECT * FROM table1";
const unsigned char *my_array[3];
int i=0;;
sqlite3_open(dbname, &db);
sqlite3_prepare_v2(db, sql, -1, &pStat, 0);
while(sqlite3_step(pStat) == SQLITE_ROW)
{
my_array[i] = sqlite3_column_text(pStat, 0);
printf ("%s\n",my_array[i]); // ok
i++;
}
for (i = 0; i<3; i++);{
printf ("%s\n", my_array[i]); // error
}
sqlite3_finalize(pStat);
sqlite3_close(db);
return 0;
}
Fred.
Your program is wrongly structured. Try this layout:
sqlite3_prepare_V2
while (TRUE) {
rc = sqlite3_step
switch (rc) {
case SQLITE_ROW:
/*Get each column*/
for (count = 0; count > sqlite3_column_count; count++) {
switch (sqlite3_column_type) {
case SQLITE_TEXT:
pt = sqlite3_column_text
/*Move text into your output*/
sprintf(outval, "%s", pt); /*Or some other move.*/
break;
case SQLITE_INTEGER:
outnbr = sqlite3_column_int;
or
sprintf(outval, "%d", sqlite_column_int(..));
break;
add other types
}
}
break;
case SQLITE_DONE:
sqlite3_finalize
return from function
case SQLITE_BUSY:
/*Handle BUSY condition.*/
break;
default:
/*Handle error condition.*/
break;
} /*switch*/
} /*while*/
Now you can handle errors, busy conditions and the return of differring
types from Sqlite. When you get a pointer to a text value from Sqlite
it is the programmer's responsibility to move data from that pointer
into data in your program.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Hello John,
Thanks for reply,
I just tested with your code that seem to be more useful, but something is
wrong :
#include
#include
int main(void)
{
sqlite3 *db;
sqlite3_stmt *pStat;
const gchar *dbname = "test.db";
int i;
sqlite3_open(dbname, &db);
sqlite3_prepare_v2(db, "SELECT * FROM TABLE1", -1, &pStat, 0);
while(sqlite3_step(pStat) != SQLITE_DONE)
{
switch (sqlite3_step(pStat)) {
case SQLITE_ROW:
/*Get each column*/
for (i = 0; i < sqlite3_column_count(pStat); i++)
{
switch (sqlite3_column_type(pStat,0))
{
case SQLITE_TEXT:
printf("%s ", sqlite3_column_text(pStat,0));
break;
}
break;
}
}
}
sqlite3_close(db);
return 0;
}
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Reading error outside the while
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: Hello,
I got an error when I try to read some data outside the while{}, inside the
while{} it's ok, an idea ?
test.db have just one "table1" and a "field1" with values "one", "two", "three".
#include
#include
int main(void)
{
sqlite3 *db;
sqlite3_stmt *pStat;
const char *dbname = "test.db";
const char *sql = "SELECT * FROM table1";
const unsigned char *my_array[3];
int i=0;;
sqlite3_open(dbname, &db);
sqlite3_prepare_v2(db, sql, -1, &pStat, 0);
while(sqlite3_step(pStat) == SQLITE_ROW)
{
my_array[i] = sqlite3_column_text(pStat, 0);
printf ("%s\n",my_array[i]); // ok
i++;
}
for (i = 0; i<3; i++);{
printf ("%s\n", my_array[i]); // error
}
sqlite3_finalize(pStat);
sqlite3_close(db);
return 0;
}
Fred.
-----
To unsubscribe, send email to [EMAIL PROTECTED]
-
Ken a écrit :
You need to make a copy of the str instead of just capturing a pointer
reference.
try:
my_array[i] = strdup(sqlite3_column_text(pStat, 0));
Its same with strdup()
-----
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Reading error outside the while
Simon Davies a écrit :
On 09/10/2007, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
Hello,
I got an error when I try to read some data outside the while{}, inside the
while{} it's ok, an idea ?
test.db have just one "table1" and a "field1" with values "one", "two", "three".
.
.
.
const unsigned char *my_array[3];
.
while(sqlite3_step(pStat) == SQLITE_ROW)
{
my_array[i] = sqlite3_column_text(pStat, 0);
printf ("%s\n",my_array[i]); // ok
i++;
}
for (i = 0; i<3; i++);{
printf ("%s\n", my_array[i]); // error
}
Hi Fred,
sqlite3_column_text is returning a pointer to a text string stored
within sqlite's private address space. If you want to access that data
after calling sqlite_step() again, then copy the data to some storage
of your own.
i.e
unsigned char* my_array[3];
while(sqlite3_step(pStat) == SQLITE_ROW)
{
my_array[i] = malloc( sqlite3_column_bytes(pStat, 0) );
memcpy( my_array[i], sqlite3_column_text(pStat, 0));
printf ("%s\n",my_array[i]);
i++;
}
for (i = 0; i<3; i++);{
printf ("%s\n", my_array[i]);
free( my_array[i] );
}
(or something like this - have not tested this code...)
Rgds,
Simon
-----
To unsubscribe, send email to [EMAIL PROTECTED]
-
Thanks for your reply Simon.
I tried that code but it same :
#include
#include
#include
#include
int main(void)
{
sqlite3 *db;
sqlite3_stmt *pStat;
const char *dbname = "test.db";
const char *sql = "SELECT * FROM table1";
char *row;
char *my_array[3];
int i=0;
sqlite3_open(dbname, &db);
sqlite3_prepare_v2(db, sql, -1, &pStat, 0);
while(sqlite3_step(pStat) == SQLITE_ROW)
{
row = (char *)sqlite3_column_text(pStat, 0);
my_array[i] = malloc( sizeof row * sizeof *my_array[i]);
memcpy (my_array[i], row, sizeof row * sizeof *my_array[i]);
printf ("%s\n",my_array[i]); // ok
i++;
}
for (i = 0; i<3; i++);{
printf ("%s\n", my_array[i]); //error
free (my_array);
}
sqlite3_finalize(pStat);
sqlite3_close(db);
return 0;
}
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Reading error outside the while
Hello,
I got an error when I try to read some data outside the while{}, inside the
while{} it's ok, an idea ?
test.db have just one "table1" and a "field1" with values "one", "two", "three".
#include
#include
int main(void)
{
sqlite3 *db;
sqlite3_stmt *pStat;
const char *dbname = "test.db";
const char *sql = "SELECT * FROM table1";
const unsigned char *my_array[3];
int i=0;;
sqlite3_open(dbname, &db);
sqlite3_prepare_v2(db, sql, -1, &pStat, 0);
while(sqlite3_step(pStat) == SQLITE_ROW)
{
my_array[i] = sqlite3_column_text(pStat, 0);
printf ("%s\n",my_array[i]); // ok
i++;
}
for (i = 0; i<3; i++);{
printf ("%s\n", my_array[i]); // error
}
sqlite3_finalize(pStat);
sqlite3_close(db);
return 0;
}
Fred.
---------
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: Callback fonction really not flexible to use
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 07 October 2007 17:39
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: Callback fonction really not flexible to use
Igor Tandetnik a écrit :
[EMAIL PROTECTED] wrote:
Here a sample (in c) of the use i would like to do with sqlite
fucntion1() call fonction2() where is sqlite3_exec()
Callback function is the function3() and i would like to add data
in an array, which is retuned to function1() after the call of
function(2).
How i can do that ? does the Callback function can return
something else than an int ?
A callback function must return 0. Any non-zero return value is an error
indicator.
However, the callback can, and usually does, have side effects. The
void* parameter you pass to sqlite3_exec is passed through to the
callback. Normally, this points to some kind of a data structure that
the callback modifies.
Having said that, be aware that sqlite3_exec is retained for backward
compatibility only. It is highly recommended for new code to use API
like sqlite3_prepare, sqlite3_step, sqlite3_finalize, sqlite3_column_*
to iterate over the resultset. In fact, sqlite3_exec itself is
implemented entirely in terms of these public API functions.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Thanks for your answer,
Is there somewhere an snipet code to read a db in C with "sqlite3_step,
sqlite3_finalize, sqlite3_column_*"
In the official doc, there is only the call to the sqlite3_exec() function.
Mike Marshall a écrit :
Here's something from some code I was working on this morning that hopefully
will help
sqlite3_stmt* pStatement;
char* acQuery = sqlite3_mprintf("SELECT feedurl FROM feeds WHERE pageurl =
'%q'",sUrl.c_str());
int nError = sqlite3_prepare_v2(m_pDB,acQuery,-1,&pStatement,NULL);
while (nError == SQLITE_OK && sqlite3_step(pStatement) == SQLITE_ROW)
{
string sFeed = (char*)sqlite3_column_text(pStatement,0);
}
sqlite3_finalize(pStatement);
sqlite3_free(acQuery);
Thanks you very much that helped me ! it seem to work fine and thats really
better than that satanas vade retro calback function.
Fred.
-----
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: Callback fonction really not flexible to use
Igor Tandetnik a écrit : [EMAIL PROTECTED] wrote: Here a sample (in c) of the use i would like to do with sqlite fucntion1() call fonction2() where is sqlite3_exec() Callback function is the function3() and i would like to add data in an array, which is retuned to function1() after the call of function(2). How i can do that ? does the Callback function can return something else than an int ? A callback function must return 0. Any non-zero return value is an error indicator. However, the callback can, and usually does, have side effects. The void* parameter you pass to sqlite3_exec is passed through to the callback. Normally, this points to some kind of a data structure that the callback modifies. Having said that, be aware that sqlite3_exec is retained for backward compatibility only. It is highly recommended for new code to use API like sqlite3_prepare, sqlite3_step, sqlite3_finalize, sqlite3_column_* to iterate over the resultset. In fact, sqlite3_exec itself is implemented entirely in terms of these public API functions. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - Thanks for your answer, Is there somewhere an snipet code to read a db in C with "sqlite3_step, sqlite3_finalize, sqlite3_column_*" In the official doc, there is only the call to the sqlite3_exec() function. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Callback fonction really not flexible to use
hello, Here a sample (in c) of the use i would like to do with sqlite fucntion1() call fonction2() where is sqlite3_exec() Callback function is the function3() and i would like to add data in an array, which is retuned to function1() after the call of function(2). How i can do that ? does the Callback function can return something else than an int ? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] 3.5.0 alpha TryEnterCriticalSection linker error
Your alternative to sqlite3_mutex_try() doesn't link on NT/2000/XP/Vista
because you still
have TryEnterCriticalSection there instead of getting the pointer to it
dynamically as Nicolas
suggested. So the current mutex_w32.c 1.4 does compile but won't link either.
http://www.sqlite.org/cvstrac/filediff?f=sqlite/src/mutex_w32.c&v1=1.3&v2=1.4
To link it you have to add
#ifndef _WIN32_WINNT
#define _WIN32_WINNT 0x0400
#endif _WIN32_WINNT
somewhere, I added it after the include guard in the amalgamation sqlite3.c of
3.5.0 alpha,
which I have done now to remove a link error in my code and it works. Hope drh
adds it in
the CVS.
I second that Win9X support should be dropped, it's as pointless as supporting
MS-DOS now.
It should have been dropped when sqlite3 was released.
-- sword
On Wed, 05 Sep 2007 10:20:37 +0200
Ralf Junker <[EMAIL PROTECTED]> wrote:
>
> >Isn't it time to drop the Win9X support from the default build?
>
> I do not believe that just because Win9x is missing a single required call
> justifies dropping support for it altogether!
>
> >I'm thinking that any optimization should be enabled for the majority of
> >users. Or if it's not really an optimization, why keeping it in the code
> >then?
>
> If possible, please keep the optimization.
>
> >An alternative is to call this function when available using
> >"GetProcAddress" (this is the case for a lot of other modern calls that
> >cannot be done right now).
>
> I second this alternative.
>
> According to http://msdn2.microsoft.com/en-us/library/ms686857.aspx,
> TryEnterCriticalSection() is available on all Windows NT sytems. Therefore an
> option to "GetProcAddress()" is checking for such OSes. The isNT() routine is
> already part of os_win.c and is used there frequently:
>
> static int isNT(void){
> if( sqlite3_os_type==0 ){
> OSVERSIONINFO sInfo;
> sInfo.dwOSVersionInfoSize = sizeof(sInfo);
> GetVersionEx(&sInfo);
> sqlite3_os_type = sInfo.dwPlatformId==VER_PLATFORM_WIN32_NT ? 2 : 1;
> }
> return sqlite3_os_type==2;
> }
>
>
> sqlite3_mutex_try() would then extend to something like this (untested!):
>
> int sqlite3_mutex_try(sqlite3_mutex *p){
> int rc;
> assert( p );
> assert( p->id==SQLITE_MUTEX_RECURSIVE || sqlite3_mutex_notheld(p) );
> if( isNT() && TryEnterCriticalSection(&p->mutex) ){
> p->owner = GetCurrentThreadId();
> p->nRef++;
> rc = SQLITE_OK;
> }else{
> rc = SQLITE_BUSY;
> }
> return rc;
> }
>
> Ralf
>
>
> -----
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] how to cout the nandflash expire
Tank you very much, I know that all of the sqlite's operates will be parsed to vdbe code, but i don't know how can i get the map of vdbe code to file operate. Ben Combee wrote: > > On 7/30/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >> >> hello, I port the sqlite3 to linux(file system is jffs2).now , I must >> cout >> the nandflash expire in sqlite3 running. > > SQLite works on top of the file system, so it has no knowledge of what > JFFS2 and MTD are doing to manage your NAND flash. You'll have to > talk directly to the JFFS2 layer on your device and find out what's > happening to the file that stored the SQLite database. > > ----- > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/how-to-cout-the-nandflash-expire-tf4168923.html#a11891733 Sent from the SQLite mailing list archive at Nabble.com. ----- To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] how to cout the nandflash expire
hello, I port the sqlite3 to linux(file system is jffs2).now , I must cout the nandflash expire in sqlite3 running. -- View this message in context: http://www.nabble.com/how-to-cout-the-nandflash-expire-tf4168923.html#a11860604 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Split a table
Ulrich Schöbel a écrit :
As this list has excellent SQL wizards, I hope someone can help
me on the problem to split a table into two.
I have something like this:
create table org (
a text,
b text,
c text,
d text
);
I need to split it into rwo tables as follows:
create tbl_a (
a_id integer not null autoincrement,
a text,
b text
);
create tbl_b (
b_id integer,
c text,
d text
);
with b_id corresponding to a_id and a_id autogenerated.
I know how to do this within a tcl script, but I need a way
in pure SQL. Is it at all possible?
Thanks for your help
Ulrich
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Hello,
Look at this example with triggers...
regards,
Yves.
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> .read test.sql
.echo ON
BEGIN TRANSACTION;
create table org (
a text,
b text,
c text,
d text
);
create table tbl_a (
a_id integer PRIMARY KEY NOT NULL,
a text,
b text
);
create table tbl_b (
b_id integer PRIMARY KEY NOT NULL,
c text,
d text
);
CREATE TRIGGER [tbl_a_after_insert] AFTER Insert ON tbl_a
BEGIN
INSERT into tbl_b (b_id, c, d)
SELECT new.a_id, org.c, org.d FROM org
WHERE org.a = new.a AND org.b = new.b;
END;
insert into org values('aaa','bbb','ccc','ddd');
insert into org values('111','222','333','444');
insert into org values('abc','def','999','888');
COMMIT;
select * from org;
aaa|bbb|ccc|ddd
111|222|333|444
abc|def|999|888
insert into tbl_a select null, a, b from org;
select * from tbl_a;
1|aaa|bbb
2|111|222
3|abc|def
select * from tbl_b;
1|ccc|ddd
2|333|444
3|999|888
.echo OFF
sqlite>
Re: [sqlite] Re: In Mem Query Performance
Hi >Hi Ken, > >Thanks a lot. >But this would require the key to be short. Will Check if this is acceptable >to all as we may not be able to port old db data if the key format is changed. > Perhaps the key can be modified only for comparation. You store the key as you want, but before compare it do a rle compression. You can store the rle compressed key in database too. Note that rle is a one-to- one transform, that is one key has only one compressed key and one compressed key has only one key. Working that way you can compare 200,1,1,2 with 199,1,2,2 (four bytes) instead ..(196 '1')..2 with ..(195 '1')..22. HTH >regards >ragha ----- To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Help with compiling 3.3.17 version for WinCE
I've download the amalgamation (single .c file) version of SQLite 3.3.17 and I'm trying to compile it using Embedded Visual C++ 3.0, but I'm getting some compiling errors such as,fatal error C1083: Cannot open include file: 'assert.h': No such file or directoryIs there any special settings I need to make to compile the amalgamation version of the code?Thanks for your help.Dave ___ Join Excite! - http://www.excite.com The most personalized portal on the Web!
Re: [sqlite] Database malformed with SQLite3.3.17 on WindowsXP
Hello drh and lists, Thank you for the information provided at the ticket page at http://www.sqlite.org/cvstrac/tktview?tn=2409 Now I successfully worked around the problem. -- tamagawa ryuji [EMAIL PROTECTED] : > "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: >> I've opened a new ticket 2409. >> >> http://www.sqlite.org/cvstrac/tktview?tn=2409,38 >> > > I am so far unable to reproduce the problem. Please send > me an example corrupt database and the binaries for > SQLiteCrush.exe. Tnx. > > You can send them to me by direct email if you want. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > ----- > To unsubscribe, send email to [EMAIL PROTECTED] > - > > ----- To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Database malformed with SQLite3.3.17 on WindowsXP
I've opened a new ticket 2409. http://www.sqlite.org/cvstrac/tktview?tn=2409,38 Also I found that it happenes from SQLite 3.3.9. -- tamagawa ryuji > Hi, Richard. Thank you for your very quick response. > >>> 1) Run a program SQLiteCrush.exe. >>>This program updates 'test.db' repeatedly. Insert data >>>to work table, copy them into items table, then delete >>>records from work. > >> Does SQLiteCrush.exe continue running in the background >> while you are doing steps 2 and 3? Or does SQLiteCrush.exe >> run to completion, then you do steps 2 and 3 separately? > > It continue running in the background. > > >> The code for SQLiteCrush.exe and a clean database would be helpful. >> Binaries for SQLiteCrush.exe and a corruption database, not so much. >> >> Perhaps you can open a new ticket and include the code and database >> as an attachment. > > I'm grad to do that. > -- > tamagawa ryuji > > - > To unsubscribe, send email to [EMAIL PROTECTED] > ----- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: Re: [sqlite] sqlite3_open() fails on WinCE due to utf8ToUnicode / unicode
It really looks like this UTF-8 codepage is not avaiable. Is there any WinCE developer that uses SQLite newer than version 3.3.9 on this list? -> Did you have similiar problems since the unicode conversion functions were changed? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Vista-IE7 problem
Hi all, I found another strange problem of SQLite on Vista with IE7. I have an input method with SQLite. When I opened it in IE7 for typing Chinese, the SQLite returned nothing. However, *if IE7 is "run as administrator," SQLite works.* Generally I know IE7 on Vista has a implicit security rules: once it thinks you're doing some guilty IPC from other DLLs, it drops all messages. The strange thing is, *SQLite 3.2.5 and before worked, but 3.2.6 and later not.* Sincerely, /Mike/
AW: Re: [sqlite] sqlite3_open() fails on WinCE due to utf8ToUnicode / unicodeToU
Hello Yes, I can rebuild the image, but i didn't find any UTF-8 code page. But it really looks like this code page is not installed, because I tried GetCPInfo(CP_ACP, &lpCPInfo) and it failed with ERROR_INVALID_PARAMETER. Can you tell me where I can enable UTF-8 support. (hope this is not to offtopic). Regards, Daniel - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite3_open() fails on WinCE due to utf8ToUnicode / unicodeToUtf8
Hello
It looks like there's a problem on WinCE operating system with the
utf8ToUnicode() / unicodeToUtf8() conversion functions. No database
can be opened by sqlite3_open() because thehes functions fail.
Please have a look what happens there (location: os_win.c :)
/*
** Convert a UTF-8 string to microsoft unicode (UTF-16?).
**
** Space to hold the returned string is obtained from
sqliteMalloc.
*/
static WCHAR *utf8ToUnicode(const char *zFilename){// ->->->
zFilename is "mydatabase.db"
int nChar;
WCHAR *zWideFilename;
nChar = MultiByteToWideChar(CP_UTF8, 0, zFilename, -1, NULL, 0);
// ->->-> returns 0 here
zWideFilename = sqliteMalloc( nChar*sizeof(zWideFilename[0]) );
// ->->-> returns null pointer
if( zWideFilename==0 ){
return 0;
// ->->-> leaves function here
}
nChar = MultiByteToWideChar(CP_UTF8, 0, zFilename, -1,
zWideFilename, nChar);
if( nChar==0 ){
sqliteFree(zWideFilename);
zWideFilename = 0;
}
return zWideFilename;
}
If I use the CP_ACP flag, everthing works ok:
nChar = MultiByteToWideChar(CP_ACP, 0, zFilename, -1, NULL, 0);
Same failure probably in unicodeToUtf8() because it uses
"WideCharToMultiByte(CP_UTF8, 0, zWideFilename, -1, 0, 0, 0, 0);"
My question:
Is this a bug or is something wrong with my WinCE-Image?
Version information
SQLite 3.3.17 (also tried 3.3.13 and failed)
WinCE 5.0
Regards
Daniel
-----
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] About a Vista problem
Hi Millan, I encountered the same problem, the record is on http://b6s.blogspot.com/2007/04/previous-version-ghost-on-vista-can-be.html and the issue was sent to http://www.sqlite.org/cvstrac/tktview?tn=2178 To my best knowledge, there's something funny in the dynamic library of file I/O, since my SQLite usage is actually a DLL, which may attach to ANY applications. Some cross-platform apps, such as Firefox/Thunderbird, met this problem; some other Win32 native apps not. However, I have no solution now... Sincerely, /Mike "b6s" Jiang/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Need Help with SQL Statement
This should do it: select * from Options o1 where rowid in ( select o2.rowid from Options o2 where o1.StockSymbol = o2.StockSymbol and o1.ExpiryDate=o2.ExpiryDate and o2.StrikePrice < o2.StockPrice limit 4); Thanks Igor, this doesn't work (no results are displayed) which may well be that I haven't implemented your solution correctly but it has certainly given me some idea about where to look and how to use LIMIT 4 in a way that generates more than just a total of 4 rows. I wouldn't be surprised if it turns out to be faster to just retrieve all records and skip over all but the first four in each group in your program. I will explore this suggestion as well as your first suggestion seems to take an inordinately long time (even to display no records :) ) Thanks, Roger - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Need Help with SQL Statement
I have a file with the columns: StockSymbol, OptionSymbol, StockPrice, StrikePrice, ExpiryDate For each StockSymbol, ExpiryDate, I would like to list just 4 of the records where the StrikePrice is lower than the StockPrice. The following is close to what I want except it gives me all the rows where the StrikePrice is less than StockPrice but I only want 4 rows (2 PUTs & 2 CALLs) for each Stock and associated ExpiryDate. SELECT StockSymbol, OptionSymbol, ExpiryDate, StrikePrice, StockPrice FROM Options WHERE nStrikePrice < StockPrice ORDER BY StockSymbol,sExpiryDate, nStrikePrice DESC; I would appreciate any suggestions on how I can generate an appropriate SQL statement. Thanks, Roger - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Vista problem on its aggressive "previous version"
Hi Klemens, Thank you, I'm trying to follow this: http://technet2.microsoft.com/WindowsVista/en/library/4ac505e6-dd8b-4ae7-80fa-b9d77cd8104d1033.mspx?mfr=true Cheers, Mike Klemens Friedl 提到: Try to deactivate the shadow copy for the directory where the sqlite db file(s) are stored (directory extended properties). - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Vista problem on its aggressive "previous version"
Kees Nuyt 提到: On Wed, 25 Apr 2007 22:09:21 +0800, you wrote: Hi all, Is it possible to prevent this problem happens with SQLite DLL itself? Please refer to http://b6s.blogspot.com/2007/04/previous-version-ghost-on-vista-can-be.html for problem description. First of all: I have no hands on experience with Vista... Perhaps you should install the software in directories outside the ones "guarded" by Vista. I think "Program Files" and "Windows" aren't safe anymore for people who want to be in control themselves. I've tried to just "copy" some directory to C:\, outside Windows and Program Files. That copied directory will still be affected by Vista's restore/backup functionality, i. e. it will have a shadow copy that confuse SQLite. Regards, /Mike/ ----- To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Vista problem on its aggressive "previous version"
Hi all, Is it possible to prevent this problem happens with SQLite DLL itself? Please refer to http://b6s.blogspot.com/2007/04/previous-version-ghost-on-vista-can-be.html for problem description. Regards, /Mike/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Japanese-Korean characters
Pavan 提到: Hi, Can we store/retrieve Japanese/korean characters in sqlite db ? Thanks, Pavan. UTF-8 (or Unicode) rules! Cheers, Mike - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Need help understanding SQLITE_ERROR[1] problem
In the following pseudo-code which works the first time through either
Function A or Function B I get a problem when either function is
executed a second time.
I get the following error:
SQLITE_ERROR[1] - cannot start a transaction within a transaction
Program
{
Open Database
randomly call Function A or Function B a bunch of times
(both functions work the first time through only but hang when called a
second time)
Close Database
}
Function A
{
BEGIN IMMEDIATE
define stmt (INSERT OR REPLACE)
set binds
stmt.execDML();
stmt.reset();
COMMIT TRANSACTION
stmt.finalize();
}
Function B
{
BEGIN IMMEDIATE
define stmt (INSERT OR REPLACE)
set binds
stmt.execDML();
stmt.reset();
COMMIT TRANSACTION
stmt.finalize();
}
I would appreciate any ideas on where to look to eliminate this error.
Roger
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] SQlite3.exe .dump doesn't do anything for me
Did you try ".dump TABLENAME" where TABLENAME is one of your seven tables? Roger Rob Richardson wrote: Greetings! I have a small database (seven tables with no more than 20 rows per table) that I want to dump, since I want to create similar databases. However, sqlite3 mydatabase.db ".dump" just gives me: BEGIN TRANSACTION; COMMIT; What would cause sqlite3 not to be able to see any tables in my database? Thank you very much. Rob Richardson - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: Fwd: [sqlite] problems reading a sqlite db
I'll try that next time. This time i found a cheat way around the problem. I used the sqlite command line program, which had no problems opening the database, and did a dump of the db. I then used the command line program to create a new db, and read in the dump file. Then i just swaped the problem db file with the one i just created. Thanks for the help, Ian -- Forwarded message -- From: Steven Danneman <[EMAIL PROTECTED]> Date: Oct 20, 2006 6:05 PM Subject: Re: [sqlite] problems reading a sqlite db To: sqlite-users@sqlite.org It's possible that the end-of-line characters were converted when you transferred the DB file from Windows to Linux. This corrupts the binary format and can happen when you transfer a file over FTP in text mode. It's worth a shot to first run the DB file through the dos2unix command then try opening it on your Linux machine. -- Best regards, Steven Danneman <[EMAIL PROTECTED]> ITTIA - Mobile and Embedded Database Solutions Download a free evaluation of ITTIA DB at: http://www.ittia.com/community/request/ittiadb [EMAIL PROTECTED] wrote: > hi all, > > I'm trying to read a sqlite database that was created as part of a trac > installation. I'm running > ubuntu, python 2.3.4 and pysqlite2 > > I can read the db fine on a window server. > > When i copy the db over to our linux box though, i'm having some problems. > > I've written a very simple test script to try and open the db. When i run it, > i get an error when i > try to execute a SELECT statement. > > pysqlite2.dbapi2.OperationalError: unsupported file format > > As far as i can tell everything on the system is the latest version of both > pysqlite and sqlite. > I'm not really 100% sure how i can verify that though. > > Can anyone offer any suggestions as to how i would troubleshoot and resolve > this? > > thanks > > Ian > > > > > > ---- > > - > To unsubscribe, send email to [EMAIL PROTECTED] > ----- - To unsubscribe, send email to [EMAIL PROTECTED] ----- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] problems reading a sqlite db
hi all, I'm trying to read a sqlite database that was created as part of a trac installation. I'm running ubuntu, python 2.3.4 and pysqlite2 I can read the db fine on a window server. When i copy the db over to our linux box though, i'm having some problems. I've written a very simple test script to try and open the db. When i run it, i get an error when i try to execute a SELECT statement. pysqlite2.dbapi2.OperationalError: unsupported file format As far as i can tell everything on the system is the latest version of both pysqlite and sqlite. I'm not really 100% sure how i can verify that though. Can anyone offer any suggestions as to how i would troubleshoot and resolve this? thanks Ian - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] i have a few Qs - sqlite3
Whoa, time out here.Try this tutorial at www.yeohhs.com/vcsqlite1.htmIt covers some basics of SQLite, and the use of SQLite with C++.Bill ??? I am in heaven.353455 453455 wrote:> You make it so easy so others can learn from you. Youll go to heaven for> that. You are so useful to those who ask for your help. Someone should make> you a saint and pray so you help them.> > > > > On 7/7/06, John Stanton <[EMAIL PROTECTED]> wrote:> >>>> Sigh Free help is a privilege, not a right. Explanations are not>> pointless, they are educational.>>>> 353455 453455 wrote:>> > I do want to learn but i dont like to waste hours and hours through>> > pointless explainations. i know what an integer is.. i dont need 3 >> pages>> > explaining it, worst thing is that the tutorials arent good at all they>> > dont>> > teach me a lot of what i want to learn.>> >>> > spoon feeding? well i cant afford books here because there are none to>> buy.>> > and shipment fees are huge so thats not an option. i just wanted some>> > advice.>> >>> > sqlite documentation seems to talk about how good sqlite WAS in those>> times>> > and how things seem to be done among with a few senseless examples >> which>> > doesnt help at all.>> >>> > by now you think im a retarded little kid for saying what i said.>> > i just wanted some advice from those who KNOW. hence i came to ask >> HERE.>> >>> >>> >>> >>> >>> >>> >>> > On 7/7/06, John Stanton <[EMAIL PROTECTED]> wrote:>> >>> >>>> >> This type of thing works - http://www.1keydata.com/sql/sql.htm l>> >>>> >> You can also look through the Sqlite documentation.>> >>>> >> An SQL driven database is not a good tool for a person who doesn't >> want>> >> to get some SQL knowledge.>> >>>> >> If you are looking for spoon feeding, seeking free information on the>> >> Internet is not the way. Enroll.>> >>>> >> I found Sqlitespy a bit flaky but useful. Sqlbrowser seems to be >> quite>> >> effective.>> >>>> >> 353455 453455 wrote:>> >> > i tried sqlitespy but its so unfinished!>> >> >>> >> > about the tutorials i cant find any sqlite good ones>> >> > should i read mysql ones?>> >> >>> >> > any texts i can read so i can learn the basics instead of having to>> >> crawl>> >> > though piles of nonsense texts? because most tutorials ive found>> doesnt>> >> > seem>> >> > quite right they TRY to explain a lot but they TEACH way too little.>> >> >>> >> >>> >> >>> >> > On 7/7/06, John Stanton <[EMAIL PROTECTED]> wrote:>> >> >>> >> >>>> >> >> Try sqlitespy or sqlbrowser. Both free to download.>> >> >>>> >> >> You will need to learn some SQL. Just find an on-line tutorial and>> >> >> learn the basics. It will on take a couple of hours.>> >> >>>> >> >> 353455 453455 wrote:>> >> >> > hi i have a few questions hope they dont bother much.>> >> >> >>> >> >> > I) how can i know how many tables and how many rows there are in>> my>> >> >> > database?>> >> >> > II) how can i perform a search on my database?>> >> >> >>> >> >> >>> >> >> > i need to do simple text search queries. and about knowing the>> >> >> amount of>> >> >> > tables and rows. i need this because i want to have a few>> >> >> statistics. so>> >> >> is>> >> >> > there any way of knowing how many tables and how many rows there>> are>> >> in>> >> >> > total via a query?>> >> >> >>> >> >> > im interested in searching on just a column of each table in this>> >> >> database>> >> >> > but i dont know any sql at all.>> >> >> > im using sqlite3.>> >> >> >>> >> >> > does anybody know of a good administration tool for sqlite3?>> because>> >> i>> >> >> only>> >> >> > found a few tools but they werent quite done. and the others ive>> >> found>> >> >> were>> >> >> > shareware so i want to know if theres any freeware good tool out>> >> >> there?.>> >> >> >>> >> >>>> >> >>>> >> >>> >>>> >>>> >>>>>>
[sqlite] hard copy docs
Hi, I am pretty much a hard copy guy too. But about a month ago I purchased a dual output video card and a second monitor (LCD). Total price $270. Now I have the docs on one screen while a work on the other. I like it much better than I thought I would. Bill
[sqlite] sqlite system table names
Hi, I want to know the names of the system tables in sqlite. I only know of the table sqlite_master. Thanks in advance. Bill
[sqlite] autonum primary key
Hi, I need help in generating a unique integer for the table's primary key. I am more familiar with MS Access that has a data type called "Autonum" that generates the integer. Do I need to find the last record to know what the next number should be? Thanks in advance, Bill
Re: [sqlite] SQLite minimum RAM requirements?
Hi, The product GoDB uses SqLite and works on a lot of PDA and phoes. And many of these pdas are also mp3 players Bill
Re: [sqlite] Can't access sqlite_master from VB6 via ODBC
Hi That is what I thought. Who should I report the bug to? Cheers, Robin Original Message: - From: [EMAIL PROTECTED] Date: Mon, 22 May 2006 08:14:12 -0400 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Can't access sqlite_master from VB6 via ODBC Robin Wilson <[EMAIL PROTECTED]> wrote: > > I have had a few problems though. At the moment I am accessing SQLite > through ODBC from Visual Basic 6. This means the students can carry on > using the ADO commands that they are familier with (from working with > Access DBs). However, when working from VB the SQL query "SELECT * FROM > sqlite_master;" does not return any records, but when I run that from > the sqlite3 command line program with the same db file it returns 1 row. > This sounds like a bug in the ODBC driver. -- D. Richard Hipp <[EMAIL PROTECTED]> mail2web - Check your email from the web at http://mail2web.com/ .
Re: [sqlite] Can't access sqlite_master from VB6 via ODBC
Hi Yes I did look at that, but I was wanting to use ODBC to access SQLite as that is what I and my students are used to. In VB you can use the same programming commands (ADO) to access any ODBC database. My students are used to this and I'd like to carry on using this if possible. I will, however, investigate the link you gave me in more detail when I am at home. I was playing with the problem a bit more last night, and found that some queries worked (for example the one specified on your example page: SELECT name FROM sqlite_master WHERE type='table' UNION ALL SELECT name FROM sqlite_temp_master WHERE type='table' ORDER BY name; - which apparantly is the same as the .tables command. However, when I try the query SELECT name FROM sqlite_master; it gives no results - even though a much more complex query with WHERE clauses gives some results. Any ideas on this? Cheers, Robin Original Message: ----- From: John Newby [EMAIL PROTECTED] Date: Mon, 22 May 2006 10:39:54 +0100 To: sqlite-users@sqlite.org, [EMAIL PROTECTED] Subject: Re: [sqlite] Can't access sqlite_master from VB6 via ODBC Hi Robin, have you looked here, there are many wrappers for SQLite http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers this one in particular mentions "any ActiveX language such Visual Basic" http://vfornazin.ipdz.com/ hope this is of help to you john On 22/05/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > Hi > > I thought it wasn't permissions. The VB interface I'm using is ActiveX > Data > Objects, which works through ODBC and the SQLite ODBC driver. Might there > be some problem with this driver? > > Robin > > Original Message: > - > From: John Stanton [EMAIL PROTECTED] > Date: Mon, 22 May 2006 09:07:17 +1000 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Can't access sqlite_master from VB6 via ODBC > > > It is not permissions, more like a VB interface problem. > > Robin Wilson wrote: > > Hi all, > > > > I've just started looking at SQLite - and think it is really impressive. > > It would be just the thing to use with my students for teaching them > > about RDBMS's (especially with some of the nice Windows UIs which are > > available for the students who can't cope with command line tools!). > > > > I have had a few problems though. At the moment I am accessing SQLite > > through ODBC from Visual Basic 6. This means the students can carry on > > using the ADO commands that they are familier with (from working with > > Access DBs). However, when working from VB the SQL query "SELECT * FROM > > sqlite_master;" does not return any records, but when I run that from > > the sqlite3 command line program with the same db file it returns 1 row. > > > > Is this some kind of permissions problem? If this happened elsewhere I > > would assume it was, but I remember reading on your website that > > permissions aren't implemented in SQLite. > > > > Does anyone have any ideas? > > > > Cheers, > > > > Robin > > > > P.S. I hope this is the right list, and that it is ok to just butt in > > etc... > > > > mail2web - Check your email from the web at > http://mail2web.com/ . > > > mail2web - Check your email from the web at http://mail2web.com/ .
Re: [sqlite] Can't access sqlite_master from VB6 via ODBC
Hi I thought it wasn't permissions. The VB interface I'm using is ActiveX Data Objects, which works through ODBC and the SQLite ODBC driver. Might there be some problem with this driver? Robin Original Message: - From: John Stanton [EMAIL PROTECTED] Date: Mon, 22 May 2006 09:07:17 +1000 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Can't access sqlite_master from VB6 via ODBC It is not permissions, more like a VB interface problem. Robin Wilson wrote: > Hi all, > > I've just started looking at SQLite - and think it is really impressive. > It would be just the thing to use with my students for teaching them > about RDBMS's (especially with some of the nice Windows UIs which are > available for the students who can't cope with command line tools!). > > I have had a few problems though. At the moment I am accessing SQLite > through ODBC from Visual Basic 6. This means the students can carry on > using the ADO commands that they are familier with (from working with > Access DBs). However, when working from VB the SQL query "SELECT * FROM > sqlite_master;" does not return any records, but when I run that from > the sqlite3 command line program with the same db file it returns 1 row. > > Is this some kind of permissions problem? If this happened elsewhere I > would assume it was, but I remember reading on your website that > permissions aren't implemented in SQLite. > > Does anyone have any ideas? > > Cheers, > > Robin > > P.S. I hope this is the right list, and that it is ok to just butt in > etc... mail2web - Check your email from the web at http://mail2web.com/ .
[sqlite] SQLite on Palm Handheld and Pocket-Pc
Hi, Does anyone have experience use SQLite on a Palm or Pocket-PC handheld? I want the speed of an indexed database. I need faster data access than I get from the Palm files on my Palm Tungsten T3. I can give more details, but the main point is, as I sai, I nee help using SQLite on hanhels. Bill
[sqlite] RE:Re: [sqlite] sqlite3_prepare() locking mode
Thank you very much for your advice. i will try to do it
---Mensaje [EMAIL PROTECTED] wrote:
>Hi All,
>Does anybody know how to use sqlite3_prepare() with read only locking table?
>if i try to insert, delete or update on a table that is afected by
sqlite3_prepare sqlite returns an SQLITE_LOCKED error.
>i can not call sqlite3_reset, becuase the modification sentence is inside
the sqlite3_prepare statement loop.
>Please see the code below (i have simplified so some errors are not taking
into consideration and the example is quite silly an unreal, but points out
quite well the problem).
> sqlite3* db;
> char *err;
> struct sqlite3_stmt* myquery;
> int res = 0;
> char *MySQL1 = "select * from TABLE1 where order > 1000";
> if (sqlite3_open("\\example.db", &db)) {
> MessageBox((CString)sqlite3_errmsg(db),_T("Error"), MB_OK);
> return;
> }
> if (sqlite3_prepare(db, MiSQL1, strlen(MySQL1), &myquery, NULL)) {
> MessageBox((CString)sqlite3_errmsg(db),_T("Error"), MB_OK);
> sqlite3_reset(myquery);
> sqlite3_close(db);
> return;
> }
> CString szDato;
> if (sqlite3_step(myquery) == SQLITE_ROW) {
> szDato = (CString)sqlite3_column_text(myquery, 3);
> if (szdato == 'HI') {
> //THIS ONE IS GOING TO FAIL BECAUSE FILE LOCKING MODE
>--> res = sqlite3_exec( db, "insert into TABLE1
values ('VALUE1', 'TEST1', 1);" , 0, 0, &err );
> }
> while((rc = sqlite3_step(miquery)) == SQLITE_ROW) {
> nCurrentRecord ;
> }
> }
> else
> MessageBox(_T("No records on DB"),_T("Warning"),
MB_OK);
> sqlite3_reset(myquery);
> sqlite3_finalize(myquery);
> sqlite3_close(db);
>Thnak you in advance
>---Mensaje original---
>
>¿Quieres montarte una película? ¡Elige el guión, Invéntate los diálogos y
mándala a tus amigos!
http://ad.doubleclick.net/clk;28265024;7829128;n?http://entretenimiento.wanadoo.es/bombaytv/index.php
>
You can't write to a table at the same time as you are reading it.
The work around is to create a copy of the result rows from the read
operation in a temporary table, and then use a second read to scan
through the temporary table while updating the original table.
create temp table temp_table1 as select * from table1 where order >
1000;
Now you can change your outer read loop to read from temp_table1
instead of table1.
char *MySQL1 = "select * from temp_table1 where order < 1000";
Inside this loop you can safely update table1 as you were before.
HTH
Dennis Cote
¿Quieres montarte una película? ¡Elige el guión, Invéntate los diálogos y
mándala a tus amigos!
http://ad.doubleclick.net/clk;28265024;7829128;n?http://entretenimiento.wanadoo.es/bombaytv/index.php
[sqlite] sqlite3_prepare() locking mode
Hi All,
Does anybody know how to use sqlite3_prepare() with read only locking table?
if i try to insert, delete or update on a table that is afected by
sqlite3_prepare sqlite returns an SQLITE_LOCKED error.
i can not call sqlite3_reset, becuase the modification sentence is inside the
sqlite3_prepare statement loop.
Please see the code below (i have simplified so some errors are not taking into
consideration and the example is quite silly an unreal, but points out quite
well the problem).
sqlite3* db;
char *err;
struct sqlite3_stmt* myquery;
int res = 0;
char *MySQL1 = "select * from TABLE1 where order > 1000";
if (sqlite3_open("\\example.db", &db)) {
MessageBox((CString)sqlite3_errmsg(db),_T("Error"), MB_OK);
return;
}
if (sqlite3_prepare(db, MiSQL1, strlen(MySQL1), &myquery, NULL)) {
MessageBox((CString)sqlite3_errmsg(db),_T("Error"), MB_OK);
sqlite3_reset(myquery);
sqlite3_close(db);
return;
}
CString szDato;
if (sqlite3_step(myquery) == SQLITE_ROW) {
szDato = (CString)sqlite3_column_text(myquery, 3);
if (szdato == 'HI') {
//THIS ONE IS GOING TO FAIL BECAUSE FILE LOCKING MODE
--> res = sqlite3_exec( db, "insert into TABLE1
values ('VALUE1', 'TEST1', 1);" , 0, 0, &err );
}
while((rc = sqlite3_step(miquery)) == SQLITE_ROW) {
nCurrentRecord ;
}
}
else
MessageBox(_T("No records on DB"),_T("Warning"), MB_OK);
sqlite3_reset(myquery);
sqlite3_finalize(myquery);
sqlite3_close(db);
Thnak you in advance
---Mensaje original---
¿Quieres montarte una película? ¡Elige el guión, Invéntate los diálogos y
mándala a tus amigos!
http://ad.doubleclick.net/clk;28265024;7829128;n?http://entretenimiento.wanadoo.es/bombaytv/index.php
[sqlite] RE:Re: [sqlite] RE:Re: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
Hi Robert, I have never used SQLite with sqlite3_prepare(), sqlite3_step(), sqlite3_reset() and sqlite3_finalize(). Do you have any code example that i can use to avoid the use of sqlite_get_table() ? Thank you, Eduardo ---Mensaje original---I don't use the sqlite_get_table() function, and don't recommend it to others to use. As I understand it, it's there for legacy application support. New programs written for SQLite should use sqlite3_prepare(), sqlite3_step(), sqlite3_reset() and sqlite3_finalize() instead. Robert - Original Message - From: To: ; Sent: Friday, March 17, 2006 7:08 AM Subject: [sqlite] RE:Re: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE Hi Robert, I was talking about 3 selects satements using the same connections. Anyway, thank you very much for your advice of using "PRAGMA cache size=8", that solved all the problems related to sqlite3_exec memory problems with a select statement, baut the memory problems are not solved at all, stilll i get a memory leak when using sqlite3_get_table, even if i use sqlite3_free_table not all the memory is freed. Someone pointed that this might be an error on the source code becuse the ARM processor architecture is quite different from the X86 processor. I have been taking a look at the table.c file but i am not that good programer, to see if anything fails, Do you see anything on the table.c that might be generating the problem?. I have attached to this email a memory graphic of sqlite3_get_table problem El día del padre está cerca... ¿Ya tienes el regalo? http://buscawanadoo.es/search?type=pref&destino=web&origen=homespot&buscar=Regalos%20Día%20del%20Padre
[sqlite] RE:Re: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
Hi Robert, I was talking about 3 selects satements using the same connections. Anyway, thank you very much for your advice of using "PRAGMA cache size=8", that solved all the problems related to sqlite3_exec memory problems with a select statement, baut the memory problems are not solved at all, stilll i get a memory leak when using sqlite3_get_table, even if i use sqlite3_free_table not all the memory is freed. Someone pointed that this might be an error on the source code becuse the ARM processor architecture is quite different from the X86 processor. I have been taking a look at the table.c file but i am not that good programer, to see if anything fails, Do you see anything on the table.c that might be generating the problem?. I have attached to this email a memory graphic of sqlite3_get_table problem ---Mensaje original Original Message - From: > I have run your program on the CE emulator (Pocket PC 2003) > and i got the same memory leak. > I have inserted 2 buttons on a MFC dialog application. > The first button executes your code and the second button > closes the application. > If you examine the memory you will discover that the program > only free the memory once you exit from the apllication, > meanwhile it reserves memory as its needed (on demand, but > see details below). > the memory behaviour of SQLite is quite strange, an example: > lets say that a select sentence reserves 1000kb of memory, > once this local process has finished memory keeps reserved > for the program (it should be freed), if another process > executes a select sentence that needs 200kb SQLite will not > reserve 200k more, it will use 200k of the previous 1000k > reserved. if a 3rd process executes a select sentence that > needs 1300k SQlite will reserve 300kb more and those 1300kb > will not be freed until the main dialog application closes > (even if the 3 process where local methods or functions). Ok this is where you lost me. 3 processes? Is your program running 3 times on the CE platform? If CE is running 3 instances of your program, then they definitely won't be sharing any memory and yes you'll definitely run out. Also if I recall correctly, CE 5.0 will not let you run multiple instances of the same program. If you're talking about 3 SELECT statements in the same program using the same connection instance, then that's another story. > The problem is that if a select sentence consume most of the > memory it will not be freed and the program will execute very > slow until you exit from the application because there will > be so little memory left for other not SQLite process that > the program might be unusable. SQLite's default cache size is about 3mb. After opening a connection, try executing "PRAGMA cache_size=8" or some really low number and tell me if its still "leaking". Robert El día del padre está cerca... ¿Ya tienes el regalo? http://buscawanadoo.es/search?type=pref&destino=web&origen=homespot&buscar=Regalos%20Día%20del%20Padre
[sqlite] RE:Re: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
Hi,
Thank you very much for your help, i have modify the line, it compiles, it
doesn´t fire any exception, but memory is not freed at all (works the same way)
Anyone has another idea?, i tought that SQLite was working since a long time
ago on Windows CE devices, it seems quite strange that no one detected this
memory problem before?.
Thank you,
Eduardo
---Mensaje original---On March 16, 2006 12:28 pm, Jose Da Silva wrote:
I forgot a line:
char x[12] = "Hello world\0";
char *ptr;
ptr = x;
while (*ptr) {printf("%c",*ptr); ptr;};
> If you figure out the right sizeof(???) value to use, then I think it
> could be submitted as a bug-fix for \src\table.c
You want to move the pointer from azResult[1] to azResult[0] but you
want to say it without doing it like this "azResult--;" on line 192,
but saying it like this looks sort of ugly although it might work:
azResult -= ( &azResult[1] - &azResult[0] );
..so if someone has a simpler solution, thanks :-)
El día del padre está cerca... ¿Ya tienes el regalo?
http://buscawanadoo.es/search?type=pref&destino=web&origen=homespot&buscar=Regalos%20Día%20del%20Padre
[sqlite] RE:Re: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
Hi Robert, I was talking about 3 selects satements using the same connections. Anyway, thank you very much for your advice of using "PRAGMA cache size=8", that solved all the problems related to sqlite3_exec memory problems with a select statement, baut the memory problems are not solved at all, stilll i get a memory leak when using sqlite3_get_table, even if i use sqlite3_free_table not all the memory is freed. Someone pointed that this might be an error on the source code becuse the ARM processor architecture is quite different from the X86 processor. I have been takin a look at the table.c file but i am not that good programer, to see if anything fails, Do you see anything on the table.c that might be generating the problem?. Thank you very much, Eduardo ---Mensaje original Original Message - From: > I have run your program on the CE emulator (Pocket PC 2003) > and i got the same memory leak. > I have inserted 2 buttons on a MFC dialog application. > The first button executes your code and the second button > closes the application. > If you examine the memory you will discover that the program > only free the memory once you exit from the apllication, > meanwhile it reserves memory as its needed (on demand, but > see details below). > the memory behaviour of SQLite is quite strange, an example: > lets say that a select sentence reserves 1000kb of memory, > once this local process has finished memory keeps reserved > for the program (it should be freed), if another process > executes a select sentence that needs 200kb SQLite will not > reserve 200k more, it will use 200k of the previous 1000k > reserved. if a 3rd process executes a select sentence that > needs 1300k SQlite will reserve 300kb more and those 1300kb > will not be freed until the main dialog application closes > (even if the 3 process where local methods or functions). Ok this is where you lost me. 3 processes? Is your program running 3 times on the CE platform? If CE is running 3 instances of your program, then they definitely won't be sharing any memory and yes you'll definitely run out. Also if I recall correctly, CE 5.0 will not let you run multiple instances of the same program. If you're talking about 3 SELECT statements in the same program using the same connection instance, then that's another story. > The problem is that if a select sentence consume most of the > memory it will not be freed and the program will execute very > slow until you exit from the application because there will > be so little memory left for other not SQLite process that > the program might be unusable. SQLite's default cache size is about 3mb. After opening a connection, try executing "PRAGMA cache_size=8" or some really low number and tell me if its still "leaking". Robert El día del padre está cerca... ¿Ya tienes el regalo? http://buscawanadoo.es/search?type=pref&destino=web&origen=homespot&buscar=Regalos%20Día%20del%20Padre
[sqlite] RE:Re: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
Hi Robert, I was talking about 3 selects satements using the same connections. Anyway, thank you very much for your advice of using "PRAGMA cache size=8", that solved all the problems related to sqlite3_exec memory problems with a select statement, baut the memory problems are not solved at all, stilll i get a memory leak when using sqlite3_get_table, even if i use sqlite3_free_table not all the memory is freed. Someone pointed that this might be an error on the source code becuse the ARM processor architecture is quite different from the X86 processor. I have been takin a look at the table.c file but i am not that good programer, to see if anything fails, Do you see anything on the table.c that might be generating the problem?. Thank you very much, Eduardo ---Mensaje original Original Message - From: > I have run your program on the CE emulator (Pocket PC 2003) > and i got the same memory leak. > I have inserted 2 buttons on a MFC dialog application. > The first button executes your code and the second button > closes the application. > If you examine the memory you will discover that the program > only free the memory once you exit from the apllication, > meanwhile it reserves memory as its needed (on demand, but > see details below). > the memory behaviour of SQLite is quite strange, an example: > lets say that a select sentence reserves 1000kb of memory, > once this local process has finished memory keeps reserved > for the program (it should be freed), if another process > executes a select sentence that needs 200kb SQLite will not > reserve 200k more, it will use 200k of the previous 1000k > reserved. if a 3rd process executes a select sentence that > needs 1300k SQlite will reserve 300kb more and those 1300kb > will not be freed until the main dialog application closes > (even if the 3 process where local methods or functions). Ok this is where you lost me. 3 processes? Is your program running 3 times on the CE platform? If CE is running 3 instances of your program, then they definitely won't be sharing any memory and yes you'll definitely run out. Also if I recall correctly, CE 5.0 will not let you run multiple instances of the same program. If you're talking about 3 SELECT statements in the same program using the same connection instance, then that's another story. > The problem is that if a select sentence consume most of the > memory it will not be freed and the program will execute very > slow until you exit from the application because there will > be so little memory left for other not SQLite process that > the program might be unusable. SQLite's default cache size is about 3mb. After opening a connection, try executing "PRAGMA cache_size=8" or some really low number and tell me if its still "leaking". Robert El día del padre está cerca... ¿Ya tienes el regalo? http://buscawanadoo.es/search?type=pref&destino=web&origen=homespot&buscar=Regalos%20Día%20del%20Padre
[sqlite] RE:Re: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
Hi,
I guess that you are right. Robert gave me the hint to change chae size using
the PRAGMA command, and that did solve all the problems i hadt with the
sqlite3_exec command, but it did not solved the problems related to
sqlite3_get_table.
I have been taking a look at table.c code and i am not a great programmer too
and i reviwed the code buti do not see anything strange that might be
generating this problem, i hope someone with more experience could solve it?.
Thank you,
Eduardo
---Mensaje original---On March 16, 2006 08:49 am, [EMAIL PROTECTED] wrote:
> Hi Again Robert,
> I have run your program on the CE emulator (Pocket PC 2003) and i got
> the same memory leak. I have inserted 2 buttons on a MFC dialog
> application.
> The first button executes your code and the second button closes the
> application. If you examine the memory you will discover that the
> program only free the memory once you exit from the apllication,
> meanwhile it reserves memory as its needed (on demand, but see
> details below). the memory behaviour of SQLite is quite strange, an
> example: lets say that a select sentence reserves 1000kb of memory,
> once this local process has finished memory keeps reserved for the
> program (it should be freed), if another process executes a select
> sentence that needs 200kb SQLite will not reserve 200k more, it will
> use 200k of the previous 1000k reserved. if a 3rd process executes a
> select sentence that needs 1300k SQlite will reserve 300kb more and
> those 1300kb will not be freed until the main dialog application
> closes (even if the 3 process where local methods or functions). The
> problem is that if a select sentence consume most of the memory it
> will not be freed and the program will execute very slow until you
> exit from the application because there will be so little memory left
> for other not SQLite process that the program might be unusable.
>
> Any ideas,
Hi again, I think the problem lies with the type of processor being used
and the way that sqlite (or other programs for that matter) are
written. I think your problem is also related to another thread:
"[sqlite] SQLITE_CORRUPT problem in Mac OS X"
The reason I suggest that is because the Mac uses a power PC I believe,
and in your case it uses an ARM processor.
The Intel, x86, and several processors tend to be able to pack bytes,
words, longs, etc, next to each other. The ARM, the power PC, Sun, and
other risc processors tend to align them on boundaries.
So while, it is easy to point at a string and then use a basic increment
function to look up-n-down a string on an x86, it tends to cause
problems with processors that pack things differently. example:
Please realize I did not test this code, so it may not run as typed.
char x[12] = "Hello world\0";
char *ptr;
while (*ptr) {printf("%c",*ptr); ptr;};
The code above should work okay on an x86 or other CISC processor, but
may have problems on some RISC processors due to alignment issues,
especially considering the fact the string was defined using [] but it
is getting accessed using a *ptr and being incremented usingor --.
With a RISC processor, you need to take alignment into consideration,
and therefore the distance may not necessarily beor --, but some
other distance.
When I looked at src\table.c I noticed that malloc uses a formula like
(sizeof(char*)*stringsize 1) which is why I suggested trying to modify
line 192 using "result -=(char*);" instead of "result--;"
I hope this makes sense, so although I guessed that -=sizeof(char*);
perhaps it should be another value inserted there, but I'm not that
great a programmer to have guessed the correct value;
Going back to table.c you have this:
--
void sqlite3_free_table(char **azResult){
if( azResult ){ <--this is pointing to azResult[1] if you look at
the previous "sqlite3_get_table()" routines.
int i, n;
azResult--; <--I think this is wrong for ARM processor.
and should be a distance of azResult[1] to azResult[0] which I think is
another value than --
if( azResult==0 ) return; <--probably pointing to wrong spot now!
n = (int)azResult[0];
for(i=1; i(the line above is accessing info correctly for an ARM but can be
coded
quicker using *azResult if an x86 type processor was used, but
unfortunately wouldn't work then)
free(azResult);
}
}
--
I hope the above makes sense, so although I though it was
-=sizeof(char*);
It probably has to be another value, hopefully someone else know.
If you figure out the right sizeof(???) value to use, then I think it
could be submitted as a bug-fix for \src\table.c
El día del padre está cerca... ¿Ya tienes el regalo?
http://buscawanadoo.es/search?type=pref&destino=web&origen=homespot&buscar=Regalos%20Día%20del%20Padre
[sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
Hi Again Robert,
I have run your program on the CE emulator (Pocket PC 2003) and i got the same
memory leak.
I have inserted 2 buttons on a MFC dialog application.
The first button executes your code and the second button closes the
application.
If you examine the memory you will discover that the program only free the
memory once you exit from the apllication, meanwhile it reserves memory as its
needed (on demand, but see details below).
the memory behaviour of SQLite is quite strange, an example:
lets say that a select sentence reserves 1000kb of memory, once this local
process has finished memory keeps reserved for the program (it should be
freed), if another process executes a select sentence that needs 200kb SQLite
will not reserve 200k more, it will use 200k of the previous 1000k reserved. if
a 3rd process executes a select sentence that needs 1300k SQlite will reserve
300kb more and those 1300kb will not be freed until the main dialog application
closes (even if the 3 process where local methods or functions).
The problem is that if a select sentence consume most of the memory it will not
be freed and the program will execute very slow until you exit from the
application because there will be so little memory left for other not SQLite
process that the program might be unusable.
Any ideas,
Thank you in advance
Eduardo
---Mensaje original---Ok, here's what I did ...
On the desktop I created a SQLite database with one table and inserted
120,000 rows into it. I then copied it over to the CE emulator.
Then I ran the following code on the CE (Pocket PC 2003 SE) emulator:
#include
#include
#include "sqlite3.h"
int WINAPI _tWinMain(HINSTANCE hInst, HINSTANCE hPrev, LPTSTR pszCmdLine,
int nCmdShow)
{
sqlite3 *pdb;
int rc;
for (int n = 0; n < 1; n )
{
rc = sqlite3_open("\\test.db3", &pdb);
if (rc) break;
rc = sqlite3_exec(pdb, "select * from testcase", 0, 0, 0);
if (rc) break;
rc = sqlite3_close(pdb);
if (rc) break;
}
return 0;
}
On the first call to sqlite3_exec(), available program memory dropped from
an initial 10.5mb to 8.45mb. However, once that was done, available memory
remained rock solid for the duration of the loop at a constant 8.45mb and no
non-zero error codes were ever returned.
Robert
El día del padre está cerca... ¿Ya tienes el regalo?
http://buscawanadoo.es/search?type=pref&destino=web&origen=homespot&buscar=Regalos%20Día%20del%20Padre
[sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
Hi Robert,
Thank you for your test. I have not test it on the emulator but in 4 different
Windows CE devices i have at work (with different Windows CE OS versions) and
it always give me the same memory leak result.
I will run your test tomorrow at work using the emulator and i will let you
know the results.
Thank you again,
Eduardo
---Mensaje original---Ok, here's what I did ...
On the desktop I created a SQLite database with one table and inserted
120,000 rows into it. I then copied it over to the CE emulator.
Then I ran the following code on the CE (Pocket PC 2003 SE) emulator:
#include
#include
#include "sqlite3.h"
int WINAPI _tWinMain(HINSTANCE hInst, HINSTANCE hPrev, LPTSTR pszCmdLine,
int nCmdShow)
{
sqlite3 *pdb;
int rc;
for (int n = 0; n < 1; n )
{
rc = sqlite3_open("\\test.db3", &pdb);
if (rc) break;
rc = sqlite3_exec(pdb, "select * from testcase", 0, 0, 0);
if (rc) break;
rc = sqlite3_close(pdb);
if (rc) break;
}
return 0;
}
On the first call to sqlite3_exec(), available program memory dropped from
an initial 10.5mb to 8.45mb. However, once that was done, available memory
remained rock solid for the duration of the loop at a constant 8.45mb and no
non-zero error codes were ever returned.
Robert
El día del padre está cerca... ¿Ya tienes el regalo?
http://buscawanadoo.es/search?type=pref&destino=web&origen=homespot&buscar=Regalos%20Día%20del%20Padre
[sqlite] RE:[sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
Hi again Robert,
I think i forgot to tell you that to detect the memory leak you must not close
the application testing program, the easiest way to detect the memory leak is:
create a simple MFC dialog project. Add 2 buttons, one for executing your
testing program an another one for close the application from it.
If you execute your testing program and you monitorize the memory usage with
the Remote performance monitor (from the tools menu at the EVC 4.0
IDE->tools->Remote Performance Monitor->Select your device->press
the sign (add)->change combo Box selection to Memory->add a graphic
line for memory load). You will notice that memory is only recovered once you
execute the button to close the application.
I hope this might help you.
Thank you,
Eduardo
---Mensaje original---
Hi Robert,
Thank you for your test. I have not test it on the emulator but in 4 di
fferent Windows CE devices i have at work (with different Windows CE OS
versions) and it always give me the same memory leak result.
I will run your test tomorrow at work using the emulator and i will l
et you know the results.
Thank you again,
Eduardo
---Mensaje original---Ok, here's what I did ...
On the desktop I created a SQLite database with one table and inserted
120,000 rows into it. I then copied it over to the CE emulator.
Then I ran the following code on the CE (Pocket PC 2003 SE) emulator:
#include
#include
#include "sqlite3.h"
int WINAPI _tWinMain(HINSTANCE hInst, HINSTANCE hPrev, LPTSTR pszCmdLin
e,
int nCmdShow)
{
sqlite3 *pdb;
int rc;
for (int n = 0; n < 1; n )
{
rc = sqlite3_open("\\test.db3", &pdb);
if (rc) break;
rc = sqlite3_exec(pdb, "select * from testcase", 0, 0, 0);
if (rc) break;
rc = sqlite3_close(pdb);
if (rc) break;
}
return 0;
}
On the first call to sqlite3_exec(), available program memory dropped f
rom
an initial 10.5mb to 8.45mb. However, once that was done, available mem
ory
remained rock solid for the duration of the loop at a constant 8.45mb a
nd no
non-zero error codes were ever returned.
Robert
El día del padre está cerca... ¿Ya tienes el regalo? http://busca
wanadoo.es/search?type=pref&destino=web&origen=homespot&buscar=
Regalos%20Día%20del%20Padre
El día del padre está cerca... ¿Ya tienes el regalo?
http://buscawanadoo.es/search?type=pref&destino=web&origen=homespot&buscar=Regalos%20Día%20del%20Padre
[sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
Hi Robert,
Thank you for your test. I have not test it on the emulator but in 4 different
Windows CE devices i have at work (with different Windows CE OS versions) and
it always give me the same memory leak result.
I will run your test tomorrow at work using the emulator and i will let you
know the results.
Thank you again,
Eduardo
---Mensaje original---Ok, here's what I did ...
On the desktop I created a SQLite database with one table and inserted
120,000 rows into it. I then copied it over to the CE emulator.
Then I ran the following code on the CE (Pocket PC 2003 SE) emulator:
#include
#include
#include "sqlite3.h"
int WINAPI _tWinMain(HINSTANCE hInst, HINSTANCE hPrev, LPTSTR pszCmdLine,
int nCmdShow)
{
sqlite3 *pdb;
int rc;
for (int n = 0; n < 1; n )
{
rc = sqlite3_open("\\test.db3", &pdb);
if (rc) break;
rc = sqlite3_exec(pdb, "select * from testcase", 0, 0, 0);
if (rc) break;
rc = sqlite3_close(pdb);
if (rc) break;
}
return 0;
}
On the first call to sqlite3_exec(), available program memory dropped from
an initial 10.5mb to 8.45mb. However, once that was done, available memory
remained rock solid for the duration of the loop at a constant 8.45mb and no
non-zero error codes were ever returned.
Robert
El día del padre está cerca... ¿Ya tienes el regalo?
http://buscawanadoo.es/search?type=pref&destino=web&origen=homespot&buscar=Regalos%20Día%20del%20Padre
