Re: [sqlite] Efficiency Question - Value Or Liability forIndexingof This Table?
That search is currently in process. Lee _ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jay Sprenkle Sent: Friday, March 14, 2008 4:36 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Efficiency Question - Value Or Liability forIndexingof This Table? The intelligent choice would be to begin a search for an employer who is more rational. ___ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Efficiency Question - Value Or Liability for Indexingof This Table?
DRH, To be completely honest, the first implementation of my project is completed. I'm in the fine tuning stages, given the small amount of time that I have available. But, it was easier to pose the questions about the best way to do this as if the project had not been implemented yet. I already have all 3 fields indexed because searches are performed on all 3 fields: individually and in combination, and that is my prior training. However, I was wondering if I made a mistake by indexing the fields, since the indexes and the data are identical. I am a little short on time. My employer doesn't believe stress testing, load testing, nor optimization are important because my employer has no experience with databases. I have repeatedly explained that database optimization is an art, not a science, and all databases and the applications that access them require testing and refinement. No one is listening. I was hoping to make an intelligent choice without taking the time to test it one way, then the other because I am probably not going to be given that time. I agree with everything you said. If I never get the time to test my applications both ways (indexed, not indexed), can you make a best recommendation? Lee -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Friday, March 14, 2008 3:53 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Efficiency Question - Value Or Liability for Indexingof This Table? "Lee Crain" <[EMAIL PROTECTED]> wrote: > I am debating the performance gains to be realized, if any, by indexing a > particular table in a system implementation for which I am responsible. > You are getting way ahead of yourself. Stop trying to speculate about whether or not an index or indices will be useful. Write your application first. Then test it. Measure the speed. Then add an index and test and measure again. Compare the measurements. Add a different index or combination indices. Repeat until done. Premature optimization leads to bad design. Build your application first, then measure it to see where optimization is needed. -- 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] Efficiency Question - Value Or Liability for Indexingof This Table?
Cool! That's a great feature! Indexed the table will be. Thanks for all of your responses, Lee ___ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Friday, March 14, 2008 2:57 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Efficiency Question - Value Or Liability for Indexingof This Table? Lee Crain wrote: > > The reason I was wondering about indexing is that the indexes contain > exactly the same data as is in the table and this seemed like an > unnecessary duplication of data. > Actually this can be beneficial at least speed wise. SQLite has an optimization that will return data directly from the index where possible. This eliminates the extra work of using the rowid in the index entry to locate the row in the table (an O(log N) operation) so that it can retrieve the unindexed column data. If all your data is duplicated in the index, it can always use this optimization, and will in fact never pull data from the table itself for a query that uses the index. The table (along with the index) will only be accessed when rows are inserted, updated, or deleted. Dennis Cote ___ sqlite-users mailing 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] Efficiency Question - Value Or Liability for Indexingof This Table?
Understood. I will be writing our software to search this table by values, so it sounds like indexing to improve read access is desirable. The reason I was wondering about indexing is that the indexes contain exactly the same data as is in the table and this seemed like an unnecessary duplication of data. However, if your remarks are accurate, our application would still see read access efficiencies by indexing the data fields. Lee P.S. I've sent my original email to the SQLite forum several times because our mail server is showing that it was rejected. Obviously, this is not true. I apologize for the extra sends. _ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David McDivitt Sent: Friday, March 14, 2008 1:07 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Efficiency Question - Value Or Liability for Indexingof This Table? This depends on the way you plan to access the table. Indexing reduces the effect of table scans, or where clauses. If you will not be fetching records by finding a value in a given field, do not add that field to any supplemental indexes. Indexes slow the database down when writing, since each index must be maintained. I am debating the performance gains to be realized, if any, by indexing a particular table in a system implementation for which I am responsible. __ This is the table creation command: CREATE TABLE CC ( [catIndex] [integer] NOT NULL, [contIndex] [integer] NOT NULL, [new] [integer] NOT NULL DEFAULT(0)); Pretty simple: a table containing 3 fields, all integers. __ If I were to index this table, I would implement this index creation command: CREATE UNIQUE INDEX CC_CatContNewIndex on CC ([catIndex] ASC, [contIndex] ASC, [new] ASC); Pretty simple: an index containing 3 fields, all integers, sorted ascending order. __ The question: Could improvements in search and access performance be realized by indexing this table? Or, might having an index actually decrease table write and/or read performance? Thanks, Lee ___ ___ 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] Efficiency Question - Value Or Liability for Indexing of This Table?
Thanks. There are a lot of records in this table. It is written to infrequently, read often. Based on your comments, I'd have to guess that it would be best to index it to achieve better read performance. Thanks, Lee P.S. I've sent my original email to the SQLite forum several times because our mail server is showing that it was rejected. Obviously, this is not true. I apologize for the extra sends. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Friday, March 14, 2008 1:03 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Efficiency Question - Value Or Liability for Indexing of This Table? Lee Crain wrote: > > Could improvements in search and access performance be realized by > indexing this table? Yes, if your searches are of the type that can use the index. > Or, might having an index actually decrease table > write and/or read performance? Adding indexes will always decrease write performance since both the table and the index must be updated. The real question is whether the costs of the index in space and write performance pay off in enough increased read performance in the common search cases to make it worth incurring those extra costs. In short, as usual, it depends. :-) HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Efficiency Question - Value Or Liability for Indexing of This Table?
I am debating the performance gains to be realized, if any, by indexing a particular table in a system implementation for which I am responsible. __ This is the table creation command: CREATE TABLE CC ( [catIndex] [integer] NOT NULL, [contIndex] [integer] NOT NULL, [new] [integer] NOT NULL DEFAULT(0)); Pretty simple: a table containing 3 fields, all integers. __ If I were to index this table, I would implement this index creation command: CREATE UNIQUE INDEX CC_CatContNewIndex on CC ([catIndex] ASC, [contIndex] ASC, [new] ASC); Pretty simple: an index containing 3 fields, all integers, sorted ascending order. __ The question: Could improvements in search and access performance be realized by indexing this table? Or, might having an index actually decrease table write and/or read performance? Thanks, Lee ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Efficiency Question - Value Or Liability for Indexing of This Table?
I am debating the performance gains to be realized, if any, by indexing a particular table in a system implementation for which I am responsible. __ This is the table creation command: CREATE TABLE CC ( [catIndex] [integer] NOT NULL, [contIndex] [integer] NOT NULL, [new] [integer] NOT NULL DEFAULT(0)); Pretty simple: a table containing 3 fields, all integers. __ If I were to index this table, I would implement this index creation command: CREATE UNIQUE INDEX CC_CatContNewIndex on CC ([catIndex] ASC, [contIndex] ASC, [new] ASC); Pretty simple: an index containing 3 fields, all integers, sorted ascending order. __ The question: Could improvements in search and access performance be realized by indexing this table? Or, might having an index actually decrease table write and/or read performance? Thanks, Lee ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Efficiency Question - Value Or Liability for Indexing of This Table?
I am debating the performance gains to be realized, if any, by indexing a particular table in a system implementation for which I am responsible. __ This is the table creation command: CREATE TABLE CC ( [catIndex] [integer] NOT NULL, [contIndex] [integer] NOT NULL, [new] [integer] NOT NULL DEFAULT(0)); Pretty simple: a table containing 3 fields, all integers. __ If I were to index this table, I would implement this index creation command: CREATE UNIQUE INDEX CC_CatContNewIndex on CC ([catIndex] ASC, [contIndex] ASC, [new] ASC); Pretty simple: an index containing 3 fields, all integers, sorted ascending order. __ The question: Could improvements in search and access performance be realized by indexing this table? Or, might having an index actually decrease table write and/or read performance? Thanks, Lee ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Inconsistency of Returned Field Names?
I have noticed what appears to be an inconsistency of returned field names. Two queries accessing the same field in the same table are returning slightly different field names. I don't know if this is by design or by accident and I'm simply reporting what I see. QUERY1: --- "SELECT [categoryIndex] FROM CategoriesContent WHERE [contentIndex] = %i ORDER BY [categoryIndex] ASC;"; --> returns a field name of "categoryIndex". QUERY2: --- "SELECT DISTINCT [categoryIndex] FROM CategoriesContent WHERE [new] = 1 ORDER BY [categoryIndex] ASC;"; --> returns a field name of "[categoryIndex]". ________ I am using SQLite3 version 3.5.4. Lee Crain ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select Strict Affinity or No Affinity modes? Attention: DRH
DRH, ">> Can you explain why you think strict affinity mode >> might be beneficial to you? If somebody can provide a good >> enough rational to justify strict affinity mode, we might just put it >> in." My response to your request for an example of a benefit is that I have always been an adherent of strict datatyping as a means of trapping inadvertent software development errors. It's just one of the many aspects of my self-checking software that makes certain everything is done on purpose and not by chance. Lee Crain -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Chapman Sent: Wednesday, February 06, 2008 8:23 AM To: D. Richard Hipp Cc: sqlite-users@sqlite.org Subject: Re: [sqlite] How to select Strict Affinity or No Affinity modes? I take it that there's no way to work around this currently? Scott Scott Chapman wrote: > D. Richard Hipp wrote: > >> On Feb 2, 2008, at 7:57 PM, Scott Chapman wrote: >> >> >> >>> I've looked high and low and can't find a way to invoke the other 2 >>> affinity modes. Are they available? I'm on 3.5.4. >>> >>> >> The concept of "strict" affinity mode was briefly discussed years >> ago, but we never implemented it, having never seen any benefit >> for such a thing. Can you explain why you think strict affinity mode >> might be beneficial to you? If somebody can provide a good >> enough rational to justify strict affinity mode, we might just put it >> in. >> >> > I'm working on a Python adapter that goes on top of APSW. It will > enable you to use the column types NUMERIC, DATE, TIME, TIMESTAMP and > automatically convert these to and from Python's respective data types. > > The case I'm dealing with that is not working like I want is the case of > NUMERIC column type. In SQLite, this column type gets an affinity of > REAL. If I put in a value to the column as a string literal, say > '123.23', it's stored as a REAL even though I specified it as a string > in quotes. I want it to store it as a string. The only way I've found > to fix this is to use a column type of NUMERIC_TEXT. The presense of > "TEXT" in the column type changes the affinity to string. This is not > very elegant and I was looking for any other way to make this work > correctly. "No Affinity" would probably work, if I understand it > correctly. > > I want to avoid the use of REAL types in this case because they can lead > to rounding errors, which is the whole purpose of the NUMERIC type to > begin with, in my understanding. I also would like to be able to make > the column type just NUMERIC as that is compilant with the SQL standard. > > Strict Affinity and No Affinity are mentioned in the SQLite3 Datatypes > page. If there are no plans to implement these, please consider > removing them from the docs. > > Thanks! > Scott > > ___ > 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
RE: [sqlite] How Does NOT NULL produce NULLs?
No, I'm not performing Outer Joins. This problem occurs on an INSERT statement. A QString object's pointer to memory is ZERO unless an assignment is made. Performing: -> QString object = ""; solves the problem. Lee -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 29, 2008 12:39 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How Does NOT NULL produce NULLs? "Lee Crain" <[EMAIL PROTECTED]> wrote: > I did expect SQLite to enforce the NOT NULL portion of the SQL > creation statements, no matter what. SQLite *does* enforce NOT NULL no matter what. I think your pointers are getting turned into NULLs someplace else, perhaps somewhere in the QT layer. A NULL can get inserted for NOT NULL columns for non-matching rows of an OUTER JOIN. Are you doing OUTER JOINs? The NOT NULL applies to the table, not to query results from the table. Also, if you request a invalid column (the column number is too large or too small, or the last call to sqlite3_step() did not return SQLITe_ROW), then the SQLite interfaces will return a NULL pointer. -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How Does NOT NULL produce NULLs?
A "bug" in "my" code is possible.
We are using the QT suite and QString objects do not distinguish between
an uninitialized QString object (pointer == zero) and an empty string ("")
which I think is a flawed lack of distinction. In Lee Crain's Rules Of
Software Development Practices, NULL means nothing is known; empty means
empty. The 2 conditions are distinctly and unambiguously different.
However, I did expect SQLite to enforce the NOT NULL portion of the SQL
creation statements, no matter what. If it cannot for whatever reason,
then I will enforce it in my source code by:
if( 0 == QString.Length( ) )
{
// QString object's state is ambiguous; can be NULL or empty,
// according to QT documentation.
QString = ""; // This line solves the problem.
}
Thanks for your responses,
Lee Crain
__
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 29, 2008 12:02 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How Does NOT NULL produce NULLs?
"Lee Crain" <[EMAIL PROTECTED]> wrote:
> I've created a table with several fields, 3 of which are created using
> these SQL statements:
>
> [description] [varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
>
> [keywords][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
>
> [metadata][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE);
>
> __
>
> Subsequent data insertions of empty strings produce the following data:
>
> (null)|(null)|(null)
>
I am unable to replicate the problem. Are you sure you don't
have a bug in *your* code?
--
D. Richard Hipp <[EMAIL PROTECTED]>
--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] How Does NOT NULL produce NULLs?
Scott,
I'm not ignoring your post. I'm going to respond to DRH's post.
Thanks,
Lee
_
-Original Message-
From: Scott Hess [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 29, 2008 11:54 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How Does NOT NULL produce NULLs?
That seems unlikely, since NULL wouldn't come out as the string (null)
in any case. Most likely some higher layer is putting the literal
'(null)' in for you when you insert. Please post a set of literal
input to sqlite3 which demonstrates the problem.
.nullvalue '[null]'
create table x (
[description] [varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
[keywords][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
[metadata][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE
);
insert into x values ('', '', '');
insert into x (description) values ('x');
Outputs:
||
x||
Just what I'd expect.
create table y (
[description] [varchar](255) COLLATE NOCASE,
[keywords][varchar](255) COLLATE NOCASE,
[metadata][varchar](255) COLLATE NOCASE
);
insert into y values ('', '', '');
insert into y (description) values ('x');
select * from y;
Outputs:
||
x|[null]|[null]
Again, just what I'd expect.
This under SQLite version 3.5.4.
-scott
On Tue, Jan 29, 2008 at 10:12 AM, Lee Crain <[EMAIL PROTECTED]> wrote:
> I've created a table with several fields, 3 of which are created using
> these SQL statements:
>
> [description] [varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
>
> [keywords][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
>
> [metadata][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE);
>
> __
>
> Subsequent data insertions of empty strings produce the following data:
>
> (null)|(null)|(null)
>
> __
>
> It is important in the application this data is associated with to NOT
> have any NULL fields. To me, an empty string is not a NULL, only an
empty
> string, an important distinction.
>
> How can I prevent the insertion of NULLs into these fields and instead
> replace them with empty strings?
>
> Lee Crain
>
>
--
---
> To unsubscribe, send email to [EMAIL PROTECTED]
>
--
---
>
>
--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] How Does NOT NULL produce NULLs?
I've created a table with several fields, 3 of which are created using
these SQL statements:
[description] [varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
[keywords][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
[metadata][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE);
__
Subsequent data insertions of empty strings produce the following data:
(null)|(null)|(null)
__
It is important in the application this data is associated with to NOT
have any NULL fields. To me, an empty string is not a NULL, only an empty
string, an important distinction.
How can I prevent the insertion of NULLs into these fields and instead
replace them with empty strings?
Lee Crain
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] order by issue?
Unless I don't know SQL, the results look correct. Anyone chime in to correct me. Your query: select * from tst order by b, a desc; Is really: select * from tst order by b ASC, a desc; So, if you look at the rows from top to bottom, you'll see that for each value of 'b' in ascending order, you have the 'a' values in descending order. Lee ___ -Original Message- From: Ken [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 23, 2008 5:10 PM To: sqlite Subject: [sqlite] order by issue? I'm not sure if this a bug or if working as intended: the order by b,a seems to cause B to use asc.. Instead of the desc as written in the order by statement. create table tst (a integer, b integer ); sqlite> insert into tst values(1,1); sqlite> insert into tst values(1,2); sqlite> insert into tst values(1,3); sqlite> insert into tst values(2,3); sqlite> insert into tst values(2,2); sqlite> insert into tst values(2,1); sqlite> select * from tst order by b, a desc; a|b 2|1 1|1 2|2 1|2 2|3 1|3 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to create a database in run-time
I'm not responding to your entire email, only the question far below. On my employer's system, we use a query named "$CMD_ISDATABASECONSTRUCTED" to check for whether the database is created. The query is simple: "Analyze Categories;" If the Categories table does not yet exist, the query fails. If the Categories table already exists, the query succeeds. This solution suffices for our needs. Lee Crain __ ".my question is if there's a better solution for check if the database is already created, something like a "describe" SQL Command (in oracle) that help to know if the schema is already there on the database?" Thank you! -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite --> PostGres
I mean no offense to anyone but the conversation on this subject seems a little strange to me. SQLite is what it is. If it was like everything/anything else, we wouldn't need or want it. It is designed to fill a particular niche in the DBMS world and from my perspective, with every passing day, it seems to be filling it a little better. Concerning SQLite's extensions, they are what they are - as conceived by DRH and his assistants. They have a particular vision for this product and they are continuing to realize it with every new release. If you want another DBMS, they're out there. If you want what SQLite has to offer, it's available. To me, it's mostly a black and white subject. I can understand people requesting and suggesting new features and functionality. That's normal and appropriate. But, I can't see those that dramatically alter the functionality, complexity, size, or paradigm of the product being implemented, or ask those that ask it to be like some other DBMS. I guess I'm old school when it comes to databases. To me, they are highly efficient file data storage and retrieval mechanisms. All the other bells and whistles my employer wants go into a traditional 3-tier software structure. The middle tier is the exclusive home of the business rules. I wouldn't consider migrating those into the database or DBMS. Doing so spreads them out and makes cohesive control and support of them much more difficult. Where I work, our software is responsible for all data qualifications, data manipulations, the maintenance of referential integrity, and the control of access in a multi-threaded environment. I wouldn't even consider asking the DBMS to do those things. As a developer, I want explicit control of them and other functionalities that are not strictly database (file cabinet) related. My only unfulfilled desire is that SQLite eventually be fully ANSI SQL '92 compliant, as a minimum. From my perspective, it is getting there. Rome wasn't built in a day. My 2 cents, Lee Crain ___ -Original Message- From: alex katebi [mailto:[EMAIL PROTECTED] Sent: Friday, January 11, 2008 1:21 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQLite --> PostGres I don't think many people have your problem. I mean using Sqlite towards using PostGres. Please let's not pollute Sqlite. Thanks, Alex Katebi - Original Message From: P Kishor <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Friday, January 11, 2008 2:19:23 PM Subject: [sqlite] SQLite --> PostGres I have been writing an app using SQLite (great to develop with, and to serve with to an extent), always with the intent of one day upsizing it to PostGres (pain in the tush to develop with, but great to serve with to any extent). Much to my delight, I am finding that y'all (the SQLite developers) have made many things (for example, datatypes) similar to PostGres (yes, I know most all about how SQLite datatypes). My question -- why not take it all the way, and make SQLite almost a mini-PostGres... wait, before you chide me -- I don't mean in the "add more features" way, but in the "make syntax and datatypes as similar as possible" way. For example, why have the "INTEGER PRIMARY KEY" when it could just as easily be called "SERIAL"? One way might be to allow for aliases -- so, SERIAL would mean the same as INTEGER PRIMARY KEY, or CHAR(3) would mean the same as TEXT with a CHECK CONSTRAINT, and so on. Wouldn't that increase the already wildly popular appeal of SQLite even more so without subtracting or weighing anything down? By the way, I didn't find a BLOB kind in PostGres -- is that the same as BYTEA? If yes, that would be another candidate for such an alias. -- Puneet Kishor -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- __ __ Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Need help in sqlite3
Your request for assistance is too broad. Can you narrow it, be more specific? For instance: Do you want to test the use of a specific SQLite3 database that you've designed, test the software that you've created to access it, test the SQLite3 DBMS, or what? I know from reading forum emails from DRH that he and his co-workers have developed extensive TCL and software modules to test the SQLite3 DBMS so I think duplicating those tests would probably not be of interest to you. I designed a SQLite3 DBMS API for my employer. I had to write my own tester to test my API. If you need something like what I've implemented, I suggest that you will most likely have to write it yourself. Lee Crain -Original Message- From: ssridhar07 [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 08, 2008 2:18 AM To: sqlite-users@sqlite.org Subject: [sqlite] Need help in sqlite3 Dear All, I need help in sqlite3. i want to test the sqlite 3 database . any test tool is there to test database?? pls help me Thanks in Advance. -- View this message in context: http://www.nabble.com/Need-help-in-sqlite3-tp14685105p14685105.html Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Performance tuning using PRAGMA, other methods
Tom Brigg's response to your question 2 was an excellent response. Efficiently constructed queries will generally produce the best results. Also, indexes on fields contained in WHERE clauses will generally produce good results. Lee _ -Original Message- From: Scott Krig [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 20, 2007 3:03 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods There are apparently no folks with the experience to answer the questions as given? -Original Message- From: Tom Briggs [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 20, 2007 1:41 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods re: Q1 - Maybe (hopefully) someone will prove me wrong, but I think that your question is too broad to be answerable (unless you're actually attempting to assemble a collection of optimal values in all possible situations for all existing pragmas... ;shrug) re: Q2 - At the risk of sounding crass, tuning queries is, has been and always will be the best way to optimize the performance of any database. I've done a lot of tuning of SQLite and a half dozen other databases, and query design is always what has the most impact. Pragmas, #defines, API usage, etc. are always a distant second in the race for performance gains. -T > -Original Message- > From: Scott Krig [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 20, 2007 4:24 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods > > To the point, the questions are: > > > Q1)) PRAGMA: Does anyone have experience and good results optimizing > sqlite performance using PRAGMA's? If so, which ones, how were they > used, and what was the performance increase? > > Q2)) Other techniques: Any success stories on sqlite optimization > methods of any type would be appreciated. > > > > > > -Original Message- > From: Tom Briggs [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 20, 2007 10:40 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods > > >Which pragmas will be most effective (and what values you > should use > for each) depends on what you're trying to do with the database. > Synchronous is important if you're writing frequently, for > example, but > won't matter much in a read-only setting. Appropriate values for the > page_size and cache_size pragmas vary depending on whether > the database > is write-mostly or read-mostly and also depending on whether > you want to > optimize for reading or writing. > >So in short, the answer is, it depends. Depends on what you're > trying to tune for, that is. > >-T > > > -Original Message- > > From: Scott Krig [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, November 20, 2007 1:13 PM > > To: sqlite-users@sqlite.org > > Subject: [sqlite] Performance tuning using PRAGMA, other methods > > > > > > What are the 'biggest bang for the buck' sqlite optimization > > techniques > > to apply to a working system to tune performance? > > > > Q1)) PRAGMA: Does anyone have experience and good results optimizing > > sqlite performance using PRAGMA's? If so, which ones, how were they > > used, and what was the performance increase? > > Q2)) Other techniques: Any success stories on sqlite optimization > > methods of any type would be appreciated. > > > > Thanks. > > > > > > Scott > > -=- > > > > > > > > Here is a list of the PRAGMA examples from the sqlite documentation: > > > > > > > > PRAGMA auto_vacuum; > > PRAGMA auto_vacuum = 0 | none | 1 | full | 2 | incremental; > > > > PRAGMA cache_size; > > PRAGMA cache_size = Number-of-pages; > > > > PRAGMA case_sensitive_like; > > PRAGMA case_sensitive_like = 0 | 1; > > > > PRAGMA count_changes; > > PRAGMA count_changes = 0 | 1; > > > > PRAGMA default_cache_size; > > PRAGMA default_cache_size = Number-of-pages; > > > > PRAGMA default_synchronous; > > > > PRAGMA empty_result_callbacks; > > PRAGMA empty_result_callbacks = 0 | 1; > > > > PRAGMA encoding; > > PRAGMA encoding = "UTF-8"; > > PRAGMA encoding = "UTF-16"; > > PRAGMA encoding = "UTF-16le"; > > PRAGMA encoding = "UTF-16be"; > > > > PRAGMA full_column_names; > > PRAGMA full_column_names = 0 | 1; > > > > PRAGMA fullfsync > > PRAGMA fullfsync = 0 | 1; > > > > PRAGMA incremental_vacuum(N); > > > > PRAGMA legacy_file_format; > > PRAGMA legacy_file_format = ON | OFF > > > > PRAGMA locking_mode; > > PRAGMA locking_mode = NORMAL | EXCLUSIVE > > PRAGMA main.locking_mode=EXCLUSIVE; > > > > PRAGMA page_size; > > PRAGMA page_size = bytes; > > > > PRAGMA max_page_count; > > PRAGMA max_page_count = N; > > > > PRAGMA read_uncommitted; > > PRAGMA read_uncommitted = 0 | 1; > > > > PRAGMA short_column_names; > > PRAGMA short_column_names = 0 | 1; > >
RE: [sqlite] Converting date from d/m/yy format
John, There are an almost infinite number of applications and operations that a person can think of regarding the use of dates and times. My employer's applications on that particular server were specific and well defined. I'm not going to go into all the details of the requirement; there were many. I picked the best, low-tech solution that satisfied every existing requirement and every foreseeable requirement. It was a very successful implementation that solved all date-time related problems on that server. And, (this is a very important point) if additional, unforeseen, date-time functionality must be implemented in the future, like adding or subtracting time intervals, those solutions can and should be implemented in the server software, not the server database. In my solution, the database is the repository of data, not logic. Lee Crain -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, December 03, 2007 12:55 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Converting date from d/m/yy format I didn't mean to nitpick, but my experience has been that date and time processing is a blind spot. Over the years we have come across the most unholy kludges and nasty errors as people try to process dates without using the core theory. The Y2K situation was just one of the side effects in an ongoing litany of difficulties. Lee Crain wrote: > John, > > None of the functionalities you mentioned were requirements on the project > I worked on. > > Clumsy? My approach simplified everything on that server and is in > production worldwide, and unmodified, today. > > Lee Crain > > ___ > > > -Original Message- > From: John Stanton [mailto:[EMAIL PROTECTED] > Sent: Monday, December 03, 2007 12:18 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Converting date from d/m/yy format > > > That approach makes date processing clumsy. Distributing dates across > time zones and into different calendars is difficult. Date arithmetic > is awkward. > > When you use dates and times based on the magic astronomical epoch > everything is simplified. Time zones and calendars are handled fairly > transparently. > > > Lee Crain wrote: >> Several years ago when I worked for a Fortune 70 company, we had a > server >> whose source code and database were complicated by timestamps. I say >> complicated because there were different timestamp datatypes used for >> different fields (inherited from the data sources), the data could be >> stored in different formats, timestamp precision varied, and it was a >> problem to translate back and forth between the different > representations >> and compare them. All of this added up to the occasional exercise of > some >> obscure bugs in the server. >> >> I successfully undertook a project to fix this for all time. My solution >> was very simple: all timestamps were represented as strings in the >> following format: >> >> "MMDD:HHmmSS.nn" >> >> This format, no what the original data source or format, became the >> standard format for timestamps on this particular server. Precision was > to >> the microsecond for all data, even if represented by zeroes. >> >> This had several virtues: >>> When debugging software, all timestamps were readable when using Debug. >> Instead of looking at some binary number, the timestamp was easily human >> readable. >>> When using administrative tools to access the database, it was easy to >> examine, modify, and compare timestamps, since they were all human >> readable and in exactly the same format. >>> When comparing timestamps in the software to determine the most > current, >> a simple string comparison always produced the correct result. >> >> The only feature that might have been needed (but wasn't on this >> particular server) was the ability to add or subtract time intervals > from >> the timestamp. >> >> You may wish to consider a similar approach to managing your date and > time >> information. >> >> Lee Crain >> > -- > --- > 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] Converting date from d/m/yy format
John, None of the functionalities you mentioned were requirements on the project I worked on. Clumsy? My approach simplified everything on that server and is in production worldwide, and unmodified, today. Lee Crain ___ -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, December 03, 2007 12:18 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Converting date from d/m/yy format That approach makes date processing clumsy. Distributing dates across time zones and into different calendars is difficult. Date arithmetic is awkward. When you use dates and times based on the magic astronomical epoch everything is simplified. Time zones and calendars are handled fairly transparently. Lee Crain wrote: > Several years ago when I worked for a Fortune 70 company, we had a server > whose source code and database were complicated by timestamps. I say > complicated because there were different timestamp datatypes used for > different fields (inherited from the data sources), the data could be > stored in different formats, timestamp precision varied, and it was a > problem to translate back and forth between the different representations > and compare them. All of this added up to the occasional exercise of some > obscure bugs in the server. > > I successfully undertook a project to fix this for all time. My solution > was very simple: all timestamps were represented as strings in the > following format: > > "MMDD:HHmmSS.nn" > > This format, no what the original data source or format, became the > standard format for timestamps on this particular server. Precision was to > the microsecond for all data, even if represented by zeroes. > > This had several virtues: >> When debugging software, all timestamps were readable when using Debug. > Instead of looking at some binary number, the timestamp was easily human > readable. >> When using administrative tools to access the database, it was easy to > examine, modify, and compare timestamps, since they were all human > readable and in exactly the same format. >> When comparing timestamps in the software to determine the most current, > a simple string comparison always produced the correct result. > > The only feature that might have been needed (but wasn't on this > particular server) was the ability to add or subtract time intervals from > the timestamp. > > You may wish to consider a similar approach to managing your date and time > information. > > Lee Crain > -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Converting date from d/m/yy format
Several years ago when I worked for a Fortune 70 company, we had a server whose source code and database were complicated by timestamps. I say complicated because there were different timestamp datatypes used for different fields (inherited from the data sources), the data could be stored in different formats, timestamp precision varied, and it was a problem to translate back and forth between the different representations and compare them. All of this added up to the occasional exercise of some obscure bugs in the server. I successfully undertook a project to fix this for all time. My solution was very simple: all timestamps were represented as strings in the following format: "MMDD:HHmmSS.nn" This format, no what the original data source or format, became the standard format for timestamps on this particular server. Precision was to the microsecond for all data, even if represented by zeroes. This had several virtues: > When debugging software, all timestamps were readable when using Debug. Instead of looking at some binary number, the timestamp was easily human readable. > When using administrative tools to access the database, it was easy to examine, modify, and compare timestamps, since they were all human readable and in exactly the same format. > When comparing timestamps in the software to determine the most current, a simple string comparison always produced the correct result. The only feature that might have been needed (but wasn't on this particular server) was the ability to add or subtract time intervals from the timestamp. You may wish to consider a similar approach to managing your date and time information. Lee Crain -Original Message- From: T [mailto:[EMAIL PROTECTED] Sent: Friday, November 02, 2007 12:29 AM To: sqlite-users@sqlite.org Subject: [sqlite] Converting date from d/m/yy format Hi all, How can I convert dates from the format d/m/yy to SQL style YYY-MM-DD? I have some imported data that includes a date column in the format d/ m/yy, where: d = day as 1 or two digits m = month as 1 or two digits yy = year as two digits eg: 2/11/07 = today 2/8/68 = 2nd of August, 1968 How can I convert this in SQLite to -MM-DD? The data is from a bank, so I have no control over its production. I couldn't find any suitable built in SQLite functions, which all seem to operate in the other direction. The best I've come up with so far is: create table Raw( Date ); insert into Raw( Date ) values ( '2/11/07' ); insert into Raw( Date ) values ( '2/8/68' ); select case /* Prefix for year = 20 if year starts 0, 1 or 2, else 19 */ when cast( substr( Date, -2, 1 ) as integer ) < 3 then '20' else '19' end || substr( Date, -2, 2 ) /* Year = last two characters */ || '-' || case /* Prefix month with 0 if short */ when substr( Date, -5, 1 ) = '/' then '0' else '' end || case /* Month = from after / to 4th last character */ when substr( Date, 2, 1) = '/' then substr( Date, 3, length( Date ) - 5 ) else substr( Date, 4, length( Date ) - 6 ) end || '-' || case /* Day = from 1st to character before first / */ when substr( Date, 2, 1 ) = '/' then '0' || substr( Date, 1, 1 ) /* Prefix with 0 if short */ else substr( Date, 1, 2 ) end as Date from Raw ; which correctly gives: 2007-11-02 1968-08-02 But is there a more robust, built in method? Thanks, Tom -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Race condition -- fixed?
Guys, I read this forum regularly and I've given a lot of thought to all of these conversations about low level logic and trying to squeeze the last ounce of performance out of SQLite. That's not for me. Simplicity equates to robustness and my company needs robustness. And my time is really expensive so I need to not spend too much time tweaking my SQLite based application to get everything to work reliably and efficiently. I've wrapped all of my company's SQLite database accesses in my own API layer that encapsulates all of our applications' business rules and forces ALL transactions, no matter how lengthy or trivial, to be atomic by using a MUTEX to avoid the types of scenarios described below. This includes simple database accesses, even reads. Yes, I know it makes things slower. But, it's solid. And I won't be getting calls at 3:00 a.m. about some mysterious database problem. If my company wants something faster, they need to buy SQL Server. At $5000, it's a bargain. At my salary, I'm not. My 2 cents, Lee Crain _ -Original Message- From: Richard Klein [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 24, 2007 10:39 PM To: sqlite-users@sqlite.org Subject: [sqlite] Race condition -- fixed? As I was thinking about the locking mechanism in SQLite 3, it occurred to me that the following race condition could occur. Imagine a joint bank account with a balance of $10,000. The wife makes a withdrawal of $1,000 at ATM 'A' (serviced by process A in the bank's mainframe), while at the same time the husband makes a deposit of $1,000 at ATM 'B' (serviced by process B). The steps performed by each process are as follows: Process A - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Process B - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Both processes open the accounts database, obtain SHARED locks, and proceed at about the same pace. Process A updates her local cache with a new balance of $900, while process B updates his local cache with a new balance of $11,000. Now suppose B gets to the COMMIT first. He tries to get a PENDING lock and succeeds. He then tries to promote his PENDING lock to EXCLUSIVE, but gets a SQLITE_BUSY instead, because process A holds a SHARED lock. So, he goes to sleep, hoping that when he awakens the SHARED lock will be gone. Meanwhile, process A reaches her COMMIT, tries to get a PENDING lock, but gets a SQLITE_BUSY instead, because process B already holds a PENDING lock. Process A then releases her SHARED lock (so that process B can be promoted from PENDING to EXCLUSIVE and do his commit), and goes to sleep, hoping that when she wakes up the PENDING lock will be gone. Process B then wakes up, finds the database UNLOCKED, obtains his EXCLUSIVE lock, commits his local cache's balance of $11,000 to the database, releases his lock, and exits. Process A then wakes up, finds the database UNLOCKED, obtains an EXCLUSIVE lock, commits her local cache's balance of $9,000 to the database, releases her lock, and exits. *The database now erroneously shows a balance of $9,000.* The problem is that the moment that process B commits his local cache's balance of $11,000 to the database, he causes process A's local cache to become *stale*, i.e. inconsistent with the database. After scouring the documentation, I came across the following article: http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError which seems to describe the exact scenario I described above. According to this article, SQLite has been fixed so that if a process encounters a SQLITE_BUSY during an explicit trans- action, then the transaction will *automatically* be rolled back, and the app will receive an error code of SQLITE_IOERR *instead of* SQLITE_BUSY. I understand this to mean that whenever coding an explicit transaction, the programmer must always be prepared to receive an SQLITE_IOERR when stepping through any SQL statement, and must deal with this error by going back to the start of the transaction and starting over. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Some Questions Regarding Access To a SQLite Database By More Than One Process
Ken, Igor, I read the article you referenced. Much appreciated. http://sqlite.org/lockingv3.html I didn't want to complicate my original questions with the intricate details of the application requirements which involve not allowing any database access while certain other operations are executing. I think a MUTEX, even with its inherent performance limitations, is the best solution. Thanks for your replies, Lee Crain P.S. Ken, I'm pretty certain that a MUTEX is both an intra- and inter-process mutual exclusion object. -Original Message- From: Ken [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 24, 2007 2:22 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Re: Some Questions Regarding Access To a SQLite Database By More Than One Process If you are using seperate processes then a mutex will not help since it is local to a process. A semaphore could be used however. You can use a begin immediate around all statements that perform DML (ins/upd/sel) Then loop on the busy at the begin immediate command. This is a fairly simple thing to do. Then for selects you'll need only test the prepare/ and first step After the first step you should not get a sqlite busy. Lee Crain <[EMAIL PROTECTED]> wrote: Igor, I did say "controlled" concurrency. I'll rephrase question 3. 3) Would use of a MUTEX to avoid the dreaded "SQLite busy" condition be a good solution? Or is some other method of avoiding a busy condition recommended? Lee Crain __ -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 24, 2007 1:36 PM To: SQLite Subject: [sqlite] Re: Some Questions Regarding Access To a SQLite Database By More Than One Process Lee Crain wrote: > 1. Can multiple processes "concurrently" access the same SQLite > database? Yes. > 2. If so, can multiple processes maintain an open connection to the > database? Or must the connection be opened and closed, before and > after, > respectively, each database access? You can have multiple open connections, from the same or different processes, at any given time. You can keep a connection open as long as necessary. > 3. Would the use of a MUTEX as access protection be adequate to > successfully implement controlled "concurrency"? I'm not sure I understand this question. Mutexes are all about _not_ allowing concurrency. Igor Tandetnik -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Some Questions Regarding Access To a SQLite Database By More Than One Process
Igor, I did say "controlled" concurrency. I'll rephrase question 3. 3) Would use of a MUTEX to avoid the dreaded "SQLite busy" condition be a good solution? Or is some other method of avoiding a busy condition recommended? Lee Crain __ -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 24, 2007 1:36 PM To: SQLite Subject: [sqlite] Re: Some Questions Regarding Access To a SQLite Database By More Than One Process Lee Crain <[EMAIL PROTECTED]> wrote: > 1. Can multiple processes "concurrently" access the same SQLite > database? Yes. > 2. If so, can multiple processes maintain an open connection to the > database? Or must the connection be opened and closed, before and > after, > respectively, each database access? You can have multiple open connections, from the same or different processes, at any given time. You can keep a connection open as long as necessary. > 3. Would the use of a MUTEX as access protection be adequate to > successfully implement controlled "concurrency"? I'm not sure I understand this question. Mutexes are all about _not_ allowing concurrency. Igor Tandetnik -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Some Questions Regarding Access To a SQLite Database By More Than One Process
I loosely follow this forum so I am almost certain this subject has come up in the forum in the last few months. However, until Monday of this week, this subject had not come up in my company's application requirements so I made few mental notes on it. Now, a requirement for having 2 or more processes accessing the same SQLite database has arisen. Questions: 1. Can multiple processes "concurrently" access the same SQLite database? 2. If so, can multiple processes maintain an open connection to the database? Or must the connection be opened and closed, before and after, respectively, each database access? 3. Would the use of a MUTEX as access protection be adequate to successfully implement controlled "concurrency"? If my ideas on how to successfully implement this capability are not appropriate, please advise me. Thanks, Lee Crain - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] A Question: Use of the sqlite3_exe( ) function?
Igor, I have a question. Why is it "highly recommended" to use the function call sequence you iterate in preference to the sqlite3_exe call, since it is implemented using that sequence? Lee Crain _ -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Sunday, October 07, 2007 9:44 AM To: SQLite Subject: [sqlite] Re: Callback fonction really not flexible to use [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] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Bug: Database connection closed on different thread
Vitali, I don't think the issue is one of threads/parallel processing being evil, it's just that they are more prone to programmer oversight or error, especially on REALLY BIG real-time systems/servers accessing databases. My career experience has been that the larger and more complicated the software, the more knowledge and skill required of the development staff to produce a high quality product. Considering that possibly as many as 50% of the people I've encountered in my 22 year career in IT were not particularly well-suited for what they were doing, large system size and system complications increase the likelihood of a non-failsafe product. The treatise in this link: http://www.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf focuses on threads but the same arguments can be made for all parallel processing. And I think the author's points are at least somewhat valid; the current development paradigms do not provide good mechanisms for ensuring zero-defect code in large, complex systems where a lot has to be known by the developers about all components of the system to ensure correct synchronization, correct serialization of events, the prevention of race conditions, and the elimination of the possibility of deadlocks. To a large degree, the problems with function focused software were solved with object-oriented, data type based, properly implemented class-based software. Maybe it is time for our industry to begin to look for the next technological improvement that will make multi-threaded/parallel processing applications more robust. Lee Crain ___ -Original Message- From: Vitali Lovich [mailto:[EMAIL PROTECTED] Sent: Thursday, October 04, 2007 3:58 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Bug: Database connection closed on different thread I'm confused with your statement about threads. With multi-core multi-cpu machines becoming more prevalent on the desktop, seems to me that multi-threaded apps would provide better performance (obviously for specific problem sets). Also, don't apps that properly separate UI and background work benefit from being multi-threaded (i.e. UI remains responsive despite processing) even on single-cpu machines? Granted, writing correct multi-threaded apps may be more difficult, but I would disagree that they're evil - it just requires a higher level of programmer and better understanding of the architecture. [EMAIL PROTECTED] wrote: > patters <[EMAIL PROTECTED]> wrote: > >> Greetings, >> >> We ran into a bug that's not documented behavior: closing a connection that >> was created in a different thread (where thread A calls sqlite3_open[16], >> "hands off" the connection to thread B, and thread B calls sqlite3_close). >> >> The documentation (http://www.sqlite.org/faq.html#q6) says that any >> functions can be used with a connection as long as it's not being used by >> another thread. With SQLITE_ENABLE_MEMORY_MANAGEMENT defined, >> > > The documentation says that when SQLITE_ENABLE_MEMORY_MANAGEMENT > is defined then all operations against a database connection must > occur in the same thread in which the database connection was > originally opened. > > This constraint exists for all versions of SQLite before and > including 3.4.2. The constraint is removed in version 3.5.0. > > And as is my custom, I must also warn you that your best remedy > is to not use threads in the first place since threads are > evil and programs that use threads are almost always buggy and > slow. If you feel compelled to use threads in spite of this > warning, then upgrading to SQLite version 3.5.0 will probably > work out better for you than trying to use version 3.4.2 or > earlier. > > -- > 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] DB initial creation?
Gary, When you execute a sqlite3_open( ) function call, the database will be created if it does not already exist. Lee Crain ___ -Original Message- From: Gary G Allen [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 19, 2007 2:16 PM To: sqlite-users@sqlite.org Subject: [sqlite] DB initial creation? Thanks for the help thus far. The nice people on the list have provided me with solutions to my issues I have run into so far. We are considering using SQLite for a backend db on our device. It will be used by the CLI & to store configuration data. I do not see in the source code for any api to generate the initial db file. When running, sqlite3 , the db file is generated for you if not already available. How do I go about creating the initial instance of the db file without having to run sqlite3 application? Regards, Gary -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Callback Function Not Working In Example Code
Gary,
Your email sounds like you are executing an INSERT record command.
The callback function is only called when you execute a SELECT and data is
returned as a result of your query.
Lee Crain
___
-Original Message-
From: Gary G Allen [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 19, 2007 10:13 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Callback Function Not Working In Example Code
I appreciate the help yesterday. John Stanton's advice with the compiler
option got me going.
I have the example code from http://www.sqlite.org/quickstart.html
running. See code below.
However, the callback function is not getting executed. Everything
looks correct to me and the callback function should be getting called.
I can create an instance where sqlite3_exec is executed properly and the
entry is made in the database.
Anyone have any suggestions to what might be going on?
Here are the compiler options I am using:
%>gcc -o sql_test -lsqlite3 sql_test.c
Regards,
Gary
-
#include
#include
static int callback(void *NotUsed, int argc, char **argv, char
**azColName){
int i;
for(i=0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char **argv){
sqlite3 *db;
char *zErrMsg = 0;
int rc;
if( argc!=3 ){
fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
exit(1);
}
rc = sqlite3_open(argv[1], );
if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
rc = sqlite3_exec(db, argv[2], callback, 0, );
if( rc!=SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
sqlite3_close(db);
return 0;
}
--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] A Question About Creating and Accessing a SQLite Database in a RAM Drive
Rich, We're going to delete and rewrite ~109,369 records in 5 tables every week. Hard drives are a minimum of 10,000 times slower than RAM. I'll let you know if this process is not a lot faster than writing the records, individually, to a hard drive. Lee Crain _ -Original Message- From: Rich Shepard [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 21, 2007 11:15 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] A Question About Creating and Accessing a SQLite Database in a RAM Drive On Tue, 21 Aug 2007, Lee Crain wrote: > The approach I planned was a little different than what you proposed. That's fine, Lee. > This technique for performing database updates offline and then updating > the original database via a file copy operation has worked very well on > hard drives. I am only considering using the RAM drive to improve the > speed of the database updates. This was common in the early 1980s when drives and other hardware were slow. I've not seen a situation any time recently when this was necessary with modern hardware and fast memory. When I was capturing real-time data (lat/lon from the GPS receiver and depth from the sonar), I'd write both to memory buffers, then write to disk on a regular basis. This let me use slower hardware (compared to the data flow) while writing to disk in chunks and ensuring that no data were lost. I'm confident that you can tune your database for speed in other ways, but -- of course -- it's your choice. Good luck with it, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863 -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] A Question About Creating and Accessing a SQLite Database in a RAM Drive
Rich, The approach I planned was a little different than what you proposed. I've done some experimentation with updating a SQLite database by overwriting it with an updated copy of the database. It's a simple file overwrite, it's fast, and the database, with its new data contents, is fully useable. My plan: 1. Create a RAM drive. 1. Copy a hard drive based SQLite database from its hard drive to a RAM drive. 2. Perform updates to the SQLite database in the RAM drive (for speed). 3. Copy the RAM based SQLite database back to the hard drive and overwrite the original SQLite database. 4. Delete the RAM drive. This technique for performing database updates offline and then updating the original database via a file copy operation has worked very well on hard drives. I am only considering using the RAM drive to improve the speed of the database updates. Lee Crain -Original Message- From: Rich Shepard [mailto:[EMAIL PROTECTED] Sent: Monday, August 20, 2007 5:36 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] A Question About Creating and Accessing a SQLite Database in a RAM Drive On Mon, 20 Aug 2007, Lee Crain wrote: > Is there any reason why this would not be possible? Lee, Not at all. You can create the databse in memory as long as you have sufficient storage for it. See the web pages for syntax. If you do not specify a filename when you invoke sqlite3, it's an in-memory database on which you can perform all SQL operations. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863 -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] A Question About Creating and Accessing a SQLite Database in a RAM Drive
Is there any reason why this would not be possible? Data persistence is not required. Thanks, Lee Crain - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Need To Export A Table From a SQLite Database as a TAB Character Delimited Text File
Rich, I tried both of your suggestions. Both worked correctly. Thanks for the extra information, Lee Crain __ -Original Message- From: Rich Shepard [mailto:[EMAIL PROTECTED] Sent: Monday, August 13, 2007 5:29 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Need To Export A Table From a SQLite Database as a TAB Character Delimited Text File On Mon, 13 Aug 2007, Rich Shepard wrote: > And does it work if you specify "\t" as the spacer? Lee, Better yet, try this from the command line: .m tabs .o myfilename.txt select * from mytable .m list .o stdout The .mode command allows you to specify tabs as the values separator, just like the .separator string does. If the latter is not working, the former should. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863 -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Need To Export A Table From a SQLite Database as a TAB Character Delimited Text File
Rich, I actually keyed a TAB character, not spaces. Thanks for your response, Lee Crain ___ -Original Message- From: Rich Shepard [mailto:[EMAIL PROTECTED] Sent: Monday, August 13, 2007 4:22 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Need To Export A Table From a SQLite Database as a TAB Character Delimited Text File On Mon, 13 Aug 2007, Lee Crain wrote: > No, I'm on Windows XP. My condolences. > I am running SQLite 3.3.17. And I did enter exactly: separator ' '; > where the gap between the single quotes is an actual T A B character. > Maybe that's cheating. :^) Lee, Only yourself. The '\t' (or use double quotes to keep Gates and Balmer happy) is the standard ASCII code for a tab. The number of spaces in a tab is not universally fixed at the same width, and spaces are not equal to a tab. When you're in the sqlite command line editor, type .h to see all the help. The .mode command is also useful when dumping or exporting to a disk file. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863 -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Need To Export A Table From a SQLite Database as a TAB Character Delimited Text File
No, I'm on Windows XP. ?? I am running SQLite 3.3.17. And I did enter exactly: separator ''; where the gap between the single quotes is an actual T A B character. Maybe that's cheating. :^) Lee Crain _ -Original Message- From: Griggs, Donald [mailto:[EMAIL PROTECTED] Sent: Monday, August 13, 2007 3:37 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Need To Export A Table From a SQLite Database as a TAB Character Delimited Text File Regarding .separator "\t" Vs .separator '\t' Cool. BTW, It seems to require double quotes for me, running Windoze XP, sqlite3.exe version 3.4.0, else the separator becomes literally a backslash followed by the letter t. I'm guessing you're using *NIX and guessing it requires single quotes. -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Need To Export A Table From a SQLite Database as a TAB Character Delimited Text File
Donald,
After a small change ("" to '') for the .separator command, everything
worked very well.
Thanks for taking the time to respond,
Lee Crain
__
-Original Message-
From: Griggs, Donald [mailto:[EMAIL PROTECTED]
Sent: Monday, August 13, 2007 2:34 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Need To Export A Table From a SQLite Database as a
TAB Character Delimited Text File
Hi Lee,
Regarding: ...I need to export a table from a SQLite database as a TAB
character delimited text file.
Try the following
.separator "\t"
.output ExportFile.txt
select * from MyTable;
.output stdout
--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Need To Export A Table From a SQLite Database as a TAB Character Delimited Text File
I need to export a table from a SQLite database as a TAB character delimited text file. After reading the SQLite documentation, and specifically, the command line interface, I haven't found anything appropriate. Is there a way to do this from the command line interface? If so how? Thanks, Lee Crain - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Object Oriented Question About Adding a vector inside the callback function
The formatting on part of my response didn't turn out as I expected. I'll
try that part again:
Think of a vRecordset vector like this, as a vector of vRecord vectors:
vRecord0< Field0, Field1, Field2, FieldN >
vRecord1< Field0, Field1, Field2, FieldN >
vRecord2< Field0, Field1, Field2, FieldN >
vRecord3< Field0, Field1, Field2, FieldN >
vRecord4< Field0, Field1, Field2, FieldN >
vRecord5< Field0, Field1, Field2, FieldN >
vRecord6< Field0, Field1, Field2, FieldN >
vRecord7< Field0, Field1, Field2, FieldN >
Lee Crain
______
-Original Message-
From: Lee Crain [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 09, 2007 1:45 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Object Oriented Question About Adding a vector
inside the callback function
Stephen,
I was faced with a similar problem while writing a SQLite API wrapper for
our application programmers.
My solution was this:
> I created a Field class that is a container than can hold ONE of several
different data types.
> I created a Record class that consists of a vector of Fields and
supporting vector access services. (vector vRecord)
> I created a vector of Records as my Recordset. (vector
vRecordset)
Think of a vRecordset vector like this, as a vector of vRecord vectors:
vRecord0< Field0, Field1, Field2, FieldN >
vRecord1< Field0, Field1, Field2, FieldN >
vRecord2< Field0, Field1, Field2, FieldN >
vRecord3< Field0, Field1, Field2, FieldN >
vRecord4< Field0, Field1, Field2, FieldN >
vRecord5< Field0, Field1, Field2, FieldN >
vRecord6< Field0, Field1, Field2, FieldN >
vRecord7< Field0, Field1, Field2, FieldN >
The x axis consists of the Field containers loaded into the vRecord
vector.
The y axis consists of the vRecord vectors loaded into the vRecordset
vector.
The Recordset vector is instantiated on the stack in application code and
before the SQLite API wrapper call. Then its ADDRESS is passed as an
argument to my SQLite API wrapper class method calls. Those calls store
the pointer to the vRecordset vector in a static vRecordset vector
pointer, then execute the "sqlite3_exec()" function call which triggers
the static callback function (at global scope) to read back the data from
the SQL queries.
The callback function populates a Field class object for each field in the
received data. After all fields have been received (1 row per callback),
each of the Field class objects is loaded into a vRecord vector which is
loaded into the vRecordset vector ("pushback()" calls).
I don't see a way to make the callback function non-static. So, I didn't
try.
OO programming is type specific. That presented a problem in the static
callback function because the data coming back is not type specific. So, I
solved that problem by creating a Field container that could hold any
datatype. For each query executed, I programmed my solution to know
exactly the order of (left to right) and the expected datatypes for each
field that is returned, so that the callback function can translate the
returned data to its correct datatype before loading that data into a
Field container. That way, when the application code receives a vRecordset
back from a read operation, it doesn't have to deal with the datatypes;
they're already correctly set inside each Field container.
With the exception of the static parts of my implementation, everything is
strictly OO. The breakthrough for me was to create a Field container that
could hold any datatype. Now, I have an interface that is not bound to any
particular tables or fields, which can receive and hold the data results
from any query. Even if our underlying database changes, my SQLite API
wrapper source code will not.
I hope this helps,
Lee Crain
-Original Message-
From: Stephen Sutherland [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 09, 2007 1:06 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Object Oriented Question About Adding a vector inside
the callback function
Hi
I am using the 'quick start' C-styled code for sqlite3
http://www.sqlite.org/quickstart.html
I think I'm running into a problem trying to put it in classes to make
it somewhat object oriented. So I'm asking for help about how to make it
object-oriented - or to confirm whether what I'm doing is object oriented.
Here is the code:
[code]
//callback function
static int callback(void *NotUsed, int argc, char **argv, char
**azColName)
{
int i;
for(i=0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
};
// this callback is referenced here.
void MyClass::executeSQLStatement()
{
rc = sqlite3_exec(db, "select * from table1" , callback, 0, );
};
[/code]
However I am trying to add
RE: [sqlite] Object Oriented Question About Adding a vector inside the callback function
Stephen,
I was faced with a similar problem while writing a SQLite API wrapper for
our application programmers.
My solution was this:
> I created a Field class that is a container than can hold ONE of several
different data types.
> I created a Record class that consists of a vector of Fields and
supporting vector access services. (vector vRecord)
> I created a vector of Records as my Recordset. (vector
vRecordset)
Think of a vRecordset vector like this, as a vector of vRecord vectors:
vRecord0< Field0, Field1, Field2, FieldN >
vRecord1< Field0, Field1, Field2, FieldN >
vRecord2< Field0, Field1, Field2, FieldN >
vRecord3< Field0, Field1, Field2, FieldN >
vRecord4< Field0, Field1, Field2, FieldN >
vRecord5< Field0, Field1, Field2, FieldN >
vRecord6< Field0, Field1, Field2, FieldN >
vRecord7< Field0, Field1, Field2, FieldN >
The x axis consists of the Field containers loaded into the vRecord
vector.
The y axis consists of the vRecord vectors loaded into the vRecordset
vector.
The Recordset vector is instantiated on the stack in application code and
before the SQLite API wrapper call. Then its ADDRESS is passed as an
argument to my SQLite API wrapper class method calls. Those calls store
the pointer to the vRecordset vector in a static vRecordset vector
pointer, then execute the "sqlite3_exec()" function call which triggers
the static callback function (at global scope) to read back the data from
the SQL queries.
The callback function populates a Field class object for each field in the
received data. After all fields have been received (1 row per callback),
each of the Field class objects is loaded into a vRecord vector which is
loaded into the vRecordset vector ("pushback()" calls).
I don't see a way to make the callback function non-static. So, I didn't
try.
OO programming is type specific. That presented a problem in the static
callback function because the data coming back is not type specific. So, I
solved that problem by creating a Field container that could hold any
datatype. For each query executed, I programmed my solution to know
exactly the order of (left to right) and the expected datatypes for each
field that is returned, so that the callback function can translate the
returned data to its correct datatype before loading that data into a
Field container. That way, when the application code receives a vRecordset
back from a read operation, it doesn't have to deal with the datatypes;
they're already correctly set inside each Field container.
With the exception of the static parts of my implementation, everything is
strictly OO. The breakthrough for me was to create a Field container that
could hold any datatype. Now, I have an interface that is not bound to any
particular tables or fields, which can receive and hold the data results
from any query. Even if our underlying database changes, my SQLite API
wrapper source code will not.
I hope this helps,
Lee Crain
-Original Message-
From: Stephen Sutherland [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 09, 2007 1:06 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Object Oriented Question About Adding a vector inside
the callback function
Hi
I am using the 'quick start' C-styled code for sqlite3
http://www.sqlite.org/quickstart.html
I think I'm running into a problem trying to put it in classes to make
it somewhat object oriented. So I'm asking for help about how to make it
object-oriented - or to confirm whether what I'm doing is object oriented.
Here is the code:
[code]
//callback function
static int callback(void *NotUsed, int argc, char **argv, char
**azColName)
{
int i;
for(i=0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
};
// this callback is referenced here.
void MyClass::executeSQLStatement()
{
rc = sqlite3_exec(db, "select * from table1" , callback, 0, );
};
[/code]
However I am trying to add a vector in the callback function to store
the results. When I put the vector in it seems I am forced to do
something like this:
[code]
vector vecX;
static int callback(void *NotUsed, int argc, char **argv, char
**azColName)
{
int i;
for(i=0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
vecX.push_back(argv[3]);
printf("\n");
return 0;
};
[/code]
Now this doesn't seem object oriented ?
Nor do I understand how I would access this vector from other classes ?
And I don't know how this vector which I created can be considered part of
the class ? it seems to me to only have page scope.
Any advice on how to make my vector object oriented or accessible by
other classes ?
Thanks in Advance
Stephen
-
Pinpoint customers
RE: [sqlite] UNION?
Tom, Thanks for taking the time to document for my benefit more efficient implementations. Lee Crain __ -Original Message- From: T [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 4:08 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] UNION? Hi Lee, > This query does not work in SQLite but works in MS SQL Server: > > sqlite> SELECT items_idx, [name], active FROM Items >...> WHERE active = 'T' AND Items.items_idx IN >...> ((SELECT related_item FROM RelatedItems WHERE item = 1777) >...> UNION >...> (SELECT item FROM RelatedItems WHERE related_item = 1777)) >...> ORDER BY Items.name ASC; > SQL error: near "UNION": syntax error Of course the problem is the brackets you have around each SELECT statement, which separates them from the UNION operator. So, it works fine like this: SELECT items_idx, [name], active FROM Items WHERE active = 'T' AND Items.items_idx IN ( SELECT related_item FROM RelatedItems WHERE item = 1777 UNION SELECT item FROM RelatedItems WHERE related_item = 1777 ) ORDER BY Items.name ASC; Also, you may want to consider avoiding performing an IN on a UNION. As far as I know, SQLite doesn't optimize that, so will build the entire union before performing the IN. If you instead do the following, it should be a lot faster (if you have lots of data). But I may be wrong. SELECT items_idx, [name], active FROM Items WHERE active = 'T' AND ( items_idx IN ( SELECT [related_item] FROM RelatedItems WHERE item = 1777 ) OR items_idx IN ( SELECT item FROM RelatedItems WHERE related_item = 1777 ) ) ORDER BY [name] ASC ; or by using a JOIN: SELECT items_idx, [name], active FROM Items JOIN RelatedItems AS RI WHERE active = 'T' AND ( RI.related_item = Items.items_idx AND RI.item = 1777 OR RI.item = Items.items_idx AND RI.related_item = 1777 ) ORDER BY Items.name ASC ; Tom -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] UNION?
I agree, Jim, it "would have been nice...". When I first posted about the problem I was having, I had gone through so SQL many experiments trying to get the query to work that had I lost track of which variations I had tried on which DBMS's. I inadvertently posted one of my versions of the query as a representation of the problem that I had tried with MS SQL Server but not yet tried with SQLite. Lee Crain _ -Original Message- From: Jim Dodgen [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 7:59 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] UNION? would have been nice to see the double parentheses in your example ... I have seen MS Access as well being overly parenthesized, what does the SQL92 standard say about that? Jim Lee Crain wrote: > Richard, > > Thanks for suggesting it but no, I don't think invisible control > characters are the problem. I have copied and pasted the query from my > source code into a MS SQL Server Management Studio interface and executed > it against a SQL Server mockup of our SQLite database. It works perfectly. > > I experimented and tried some variations on the query. > > ___ > > This query does not work in SQLite but works in MS SQL Server: > > sqlite> SELECT items_idx, [name], active FROM Items >...> WHERE active = 'T' AND Items.items_idx IN >...> ((SELECT related_item FROM RelatedItems WHERE item = 1777) >...> UNION >...> (SELECT item FROM RelatedItems WHERE related_item = 1777)) >...> ORDER BY Items.name ASC; > SQL error: near "UNION": syntax error > sqlite> > > > > However, this query works in both SQLite and MS SQL Server: > > sqlite> SELECT items_idx, [name], active FROM Items >...> WHERE active = 'T' AND Items.items_idx IN >...> (SELECT related_item FROM RelatedItems WHERE item = 1777 >...> UNION >...> SELECT item FROM RelatedItems WHERE related_item = 1777) >...> ORDER BY Items.name ASC; > 1706|Arizona Character|T > 1707|Arizona Clothing and Props|T > 1660|Arizona Hair|T > 2325|Bonnie V3 Teen|T > 1425|Isabella for Stephanie 3|T > 1918|Little Darling for V3/SP|T > 106|Rose Character|T > 1778|Teresa Hair|T > sqlite> > > ________ > > Further experimentation showed that the extra pair of parentheses in the > first query (around each SELECT statement) caused the syntax error. > > Thanks for your response, > > Lee Crain > > ________ > > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 08, 2007 3:38 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] UNION? > > "Lee Crain" <[EMAIL PROTECTED]> wrote: > >> The query at the bottom of this email is failing on the word "UNION". >> > (The > >> query works correctly in MS SQL Server.) >> > > Works when I try it Do you think you might have some > invisible control characters or something in the middle of > the SQL? > > >> __ >> SELECT Items.items_idx, Items.name, Items.active FROM Items >> WHERE active = 'T' AND Items.items_idx IN >> (SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777 >> UNION >> SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777) >> ORDER BY Items.name ASC; >> >> > -- > 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] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Is SQLite Case Sensitive?
Dwight, I had come to the same conclusion. The data has been manually typed, inconsistently, over the last 4 years. I've decided that during data importation, I'm going to force all pertinent fields to lower case before they are written to the database. That should solve the problem. Thanks, Lee Crain __ -Original Message- From: Dwight Ingersoll [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 5:11 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is SQLite Case Sensitive? --- Lee Crain <[EMAIL PROTECTED]> wrote: > I am working on an application where I am importing > data for which great care has NOT been taken to > ensure uppercase and lowercase letters have been > entered appropriately. Just a suggestion: This sounds like it's a candidate for some data scrubbing and cleanup rather than trying to code for a lot of 'what if' scenarios, especially since you indicate that the data is pretty freeform. It will probably save a lot of development time and make future debugging easier if your data is in a known consistent state rather than the currently somewhat random state you implied. __ __ Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on, when. http://tv.yahoo.com/collections/222 -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] UNION?
Richard, Thanks for suggesting it but no, I don't think invisible control characters are the problem. I have copied and pasted the query from my source code into a MS SQL Server Management Studio interface and executed it against a SQL Server mockup of our SQLite database. It works perfectly. I experimented and tried some variations on the query. ___ This query does not work in SQLite but works in MS SQL Server: sqlite> SELECT items_idx, [name], active FROM Items ...> WHERE active = 'T' AND Items.items_idx IN ...> ((SELECT related_item FROM RelatedItems WHERE item = 1777) ...> UNION ...> (SELECT item FROM RelatedItems WHERE related_item = 1777)) ...> ORDER BY Items.name ASC; SQL error: near "UNION": syntax error sqlite> However, this query works in both SQLite and MS SQL Server: sqlite> SELECT items_idx, [name], active FROM Items ...> WHERE active = 'T' AND Items.items_idx IN ...> (SELECT related_item FROM RelatedItems WHERE item = 1777 ...> UNION ...> SELECT item FROM RelatedItems WHERE related_item = 1777) ...> ORDER BY Items.name ASC; 1706|Arizona Character|T 1707|Arizona Clothing and Props|T 1660|Arizona Hair|T 2325|Bonnie V3 Teen|T 1425|Isabella for Stephanie 3|T 1918|Little Darling for V3/SP|T 106|Rose Character|T 1778|Teresa Hair|T sqlite> Further experimentation showed that the extra pair of parentheses in the first query (around each SELECT statement) caused the syntax error. Thanks for your response, Lee Crain -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 3:38 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] UNION? "Lee Crain" <[EMAIL PROTECTED]> wrote: > The query at the bottom of this email is failing on the word "UNION". (The > query works correctly in MS SQL Server.) Works when I try it Do you think you might have some invisible control characters or something in the middle of the SQL? > > __ > SELECT Items.items_idx, Items.name, Items.active FROM Items > WHERE active = 'T' AND Items.items_idx IN > (SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777 > UNION > SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777) > ORDER BY Items.name ASC; > -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] UNION?
I've queried it in both the command line interface and via an sqlite3_exec() call in a C++ environment. Lee Crain ___ -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 3:30 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] UNION? There's nothing wrong with your query. It works fine against this schema in sqlite 3.4.1: CREATE TABLE Items(items_idx, name, active); CREATE TABLE RelatedItems(item, related_item); What language/tool are you using to query sqlite? --- Lee Crain <[EMAIL PROTECTED]> wrote: > The query at the bottom of this email is failing on the word "UNION". (The > query works correctly in MS SQL Server.) > > I believe this is, unfortunately correct, since the SQLite documentation > does not mention the reserved word "UNION" in the set of supported and > recognized SQL words (http://www.sqlite.org/lang.html). > > Is this correct? If so, is there another way to accomplish this query as a > single query? > > Thanks, > > Lee Crain > > __ > > > SELECT Items.items_idx, Items.name, Items.active FROM Items > > WHERE active = 'T' AND Items.items_idx IN > > (SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777 > > UNION > > SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777) > > ORDER BY Items.name ASC; __ __ Park yourself in front of a world of choices in alternative vehicles. Visit the Yahoo! Auto Green Center. http://autos.yahoo.com/green_center/ -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] UNION?
The query at the bottom of this email is failing on the word "UNION". (The query works correctly in MS SQL Server.) I believe this is, unfortunately correct, since the SQLite documentation does not mention the reserved word "UNION" in the set of supported and recognized SQL words (http://www.sqlite.org/lang.html). Is this correct? If so, is there another way to accomplish this query as a single query? Thanks, Lee Crain __ SELECT Items.items_idx, Items.name, Items.active FROM Items WHERE active = 'T' AND Items.items_idx IN (SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777 UNION SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777) ORDER BY Items.name ASC; - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] The Callback Function Interface?
Thanks for your reply, Richard. I've invested a considerable amount of time and effort in designing a very simple and robust SQLite API wrapper for our application development staff that uses the SQLite callback function. I didn't want to have to redesign it anytime soon. And thanks for your commitment to not breaking existing interfaces. Lee Crain __ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 10:25 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] The Callback Function Interface? "Lee Crain" <[EMAIL PROTECTED]> wrote: > > Is the callback function interface going to be eliminated? > No. At SQLite, we work very hard to support all of our past mistakes :-) Seriously, we work very hard to make sure that the C-language API and the on-disk file format for SQLite never change in incompatible ways. We might add new features and better ways of doing things from time to time, but old features and interfaces are always preserved to the best of our ability. The previous paragraph does NOT apply to interfaces that are declared "experimental". Nor does it apply to pragmas. Though in practice, neither of these change very often. -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] The Callback Function Interface?
John, Understood. Is the callback function interface going to be eliminated? What advantages does the new interface offer over the callback function interface? Lee Crain _ -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 8:06 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Extremely new to SQLite The callback interface is there to support legacy applications. It has been replaced by prepare/step for new work. Lee Crain wrote: > Dennis, > > Are you certain that the callback function interface has been deprecated? > >>From the link you posted: > > --- > > "2.2 Executing SQL statements >typedef int (*sqlite_callback)(void*,int,char**, char**); >int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, > char**); > The sqlite3_exec function works much as it did in SQLite version 2. Zero > or more SQL statements specified in the second parameter are compiled and > executed. Query results are returned to a callback routine." > > ------- > > I couldn't find a reference to its deprecation. > > Lee Crain > > > > > -Original Message- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 07, 2007 1:08 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Extremely new to SQLite > > Stephen Sutherland wrote: > >>use the quick start code >> http://www.sqlite.org/quickstart.html >> >> That's what I used to build all my code from >> >> > > Stephen, > > The quickstart code is very old. It uses the callback function interface > which is a depreciated API function that is maintained primarily for > backward compatibility. > > You should really look at the prepare/bind/step/column set of API > functions introduced in version 3 which is described at > http://www.sqlite.org/capi3.html > > Dennis Cote > > -- > --- > 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] Extremely new to SQLite
Thanks for a detailed response, Dennis. Under some time constraints, I just finished an important implementation and used the callback function as the means of acquiring returned data. I don't want that interface to become obsolete any time soon. Maybe I need to consider migrating to the newer, preferred interface. Lee -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 07, 2007 5:08 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Extremely new to SQLite Lee Crain wrote: > Dennis, > > Are you certain that the callback function interface has been deprecated? > > >From the link you posted: > > --- > > "2.2 Executing SQL statements >typedef int (*sqlite_callback)(void*,int,char**, char**); >int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, > char**); > The sqlite3_exec function works much as it did in SQLite version 2. Zero > or more SQL statements specified in the second parameter are compiled and > executed. Query results are returned to a callback routine." > > --- > > I couldn't find a reference to its deprecation. > > Lee, Perhaps depreciated may be too strong, but it is no longer recommended. If you continue reading in the section you quoted you will find the following: > In SQLite version 3, the sqlite3_exec routine is just a wrapper around > calls to the prepared statement interface. > >typedef struct sqlite3_stmt sqlite3_stmt; >int sqlite3_prepare <http://www.sqlite.org/capi3ref.html#sqlite3_prepare>(sqlite3*, const char*, int, sqlite3_stmt**, const char**); >int sqlite3_prepare16 <http://www.sqlite.org/capi3ref.html#sqlite3_prepare16>(sqlite3*, const void*, int, sqlite3_stmt**, const void**); >int sqlite3_finalize <http://www.sqlite.org/capi3ref.html#sqlite3_finalize>(sqlite3_stmt*); >int sqlite3_reset <http://www.sqlite.org/capi3ref.html#sqlite3_reset>(sqlite3_stmt*); > > > The sqlite3_prepare interface compiles a single SQL statement into > byte code for later execution. This interface is now the preferred way > of accessing the database. > The last sentence is the basis for my claim. I still use sqlite3_exec all the time, but only to execute statements that don't return a result set. Hence, I think it is really the callback mechanism that has been depreciated, not sqlite3_exec itself. The callback mechanism is still supported for backwards compatibility, which is why the quickstart code (along with lots of other existing sqlite client code) still works, but it should not be used for new code. HTH Dennis Cote -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Is SQLite Case Sensitive?
I am working on an application where I am importing data for which great care has NOT been taken to ensure uppercase and lowercase letters have been entered appropriately. Would a search for an 'a' return a different result than a search for an 'A'? SELECT * FROM table WHERE field1 = 'a'; Vs. SELECT * FROM table WHERE field1 = 'A'; If SQLite is case sensitive, is there an easy override for this to enforce all lowercase letters? Thanks, Lee Crain - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Extremely new to SQLite
Dennis, Are you certain that the callback function interface has been deprecated? >From the link you posted: --- "2.2 Executing SQL statements typedef int (*sqlite_callback)(void*,int,char**, char**); int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**); The sqlite3_exec function works much as it did in SQLite version 2. Zero or more SQL statements specified in the second parameter are compiled and executed. Query results are returned to a callback routine." --- I couldn't find a reference to its deprecation. Lee Crain -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 07, 2007 1:08 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Extremely new to SQLite Stephen Sutherland wrote: > use the quick start code > http://www.sqlite.org/quickstart.html > > That's what I used to build all my code from > > Stephen, The quickstart code is very old. It uses the callback function interface which is a depreciated API function that is maintained primarily for backward compatibility. You should really look at the prepare/bind/step/column set of API functions introduced in version 3 which is described at http://www.sqlite.org/capi3.html Dennis Cote -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Security Problem C/C++
Severin,
The "sqlite3_open( )" call is hard to break. Try breakpointing into the
sqlite3 source code to see what the failure is.
Lee Crain
__
-Original Message-
From: Severin Müller [mailto:[EMAIL PROTECTED]
Sent: Monday, August 06, 2007 4:42 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Security Problem C/C++
Hey
I don't even get to call the sqlite3_errmsg() function. My Program crashes
with the call sqlite3_open(filename,);
I'm been spending hours now, to figure out, what may cause that crap :) I
really have no idea...
-Original Message-
From: Clay Dowling [mailto:[EMAIL PROTECTED]
Sent: Montag, 6. August 2007 22:27
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Security Problem C/C++
Check the result code of sqlite3_open and use the error message from
sqlite3_errmsg as the message in your exception. Then you'll know what's
wrong.
Clay
Severin Müller wrote:
> Hi
>
>
>
> Im trying to use SQLite3 for my File, but Im having trouble to use the
> library properly.
>
>
>
> I Have to following Code:
>
>
>
> void Nickserv::write_nickname(std::string nick,std::string
> pass,std::string
> email,User user)
>
> {
>
> #ifdef _WIN32
>
> const char *filename = "db\\Nickserv.db";
>
> #else
>
> const char *filename = "db/Nickserv.db";
>
> #endif
>
> sqlite3 *db;
>
> sqlite3_open(filename,);
>
> }
>
>
>
> When i run the Program, I get get the following Error:
>
>
>
> Unhandled exception
so its some kind of a segmentation fault.
>
>
>
> And my Debugger is tracing the error in a file called gs_support.
>
>
>
>
>
> Now, I really cant figure out, what the Problem is. Are certain
> Architectures not supported? Im Using Win32 on a Pentium 4.
>
>
>
> Thanks for your help in Advance.
>
>
>
>
>
> Greetings
>
>
>
>
> Severin Mueller
>
> Switzerland
>
>
>
>
--
Lazarus Registration
http://www.lazarusid.com/registration.shtml
--
--
-
To unsubscribe, send email to [EMAIL PROTECTED]
--
--
-
--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] A Data Importation Question
Donald, thanks for your response.
Currently, I'm executing importation by executing a ".reading
filename.ext" command from the SQLite command line. The command file
contains these commands:
__
.separator ""<-- this is a TAB character
.import Categories.txt Categories
.import ContentFiles.txt ContentFiles
.import InstallerFiles.txt InstallerFiles
.import ItemCategories.txt ItemCategories
.import Items.txt Items
.import RelatedItems.txt RelatedItems
.import RequiredItems.txt RequiredItems
__
This works correctly. I am seeking to automate this process via software.
I was hoping there was something that had been implemented to replace the
COPY() API call.
If not, I will write a program to read the flat files one record at a time
and INSERT each record into our database.
Best regards,
Lee Crain
__
-Original Message-
From: Griggs, Donald [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 31, 2007 3:35 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] A Data Importation Question
Hi Lee,
If I answer wrongly here, I trust (and hope) someone will correct me:
Regarding: "I need a way to programmatically import data from flat
files..."
Sqlite itself is designed to be small and simple -- for some embedded
uses even the sql parser is removed. I don't think there's an api call
for such imports.
However, the full source of the command line software is available as a
model.
FWIW, I import files using batch files quite often. Note that the
.import command requies the target table name.
If you try it, you will want to preface the .import with a command of
.separator '\t'
since the default is a virgule ('|').
BTW, I don't think the .import can tolerate separator characters within
a field -- and surrounding a field with quotes will only cause the
quotes to be imported as well. Since you're using a tab as separator
you probably won't have that issue, but with comma-separated files, it
can be a problem.
--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] A Data Importation Question
My company's application has a need to regularly refresh an entire read-only database, at one week intervals. The COPY API call is no longer available. I have read the documentation on the .IMPORT command but it is a line item command interface. I need a way to programmatically import data from flat files so that we can automate this process. We would prefer not to create batch processes to do this using the SQLite command line interface, although after experimentation, I don't see that this will work (e.g. - " > sqlite3 .import Categories.txt " fails). Just in case I have missed something in the SQLite documentation, I'll ask this question: "Is there a SQLite API call that will facilitate data importation from TAB character delimited flat files?" If not, I will write the software to do it. Thanks, Lee Crain
RE: [sqlite] callback and sequence number of iteration
I suggest creating a static counter. Initialize it to zero before you call
the "sqlite3_exec( )" function and increment it once for each call to the
"callback" function.
After all records are read and the call to "sqlite3_exec( )" returns, the
counter will show the record count.
Lee Crain
_
static int iCounter;
callback( blah-blah, etc. )
{
// Extract the record
iCounter++;
}
_
-Original Message-
From: Dusan Gibarac [mailto:[EMAIL PROTECTED]
Sent: Friday, July 20, 2007 3:02 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] callback and sequence number of iteration
callback function will do something for each retrieved row and I have to
know in each iteration what is the sequence number of iteration. How can I
read or count it?
Dusan Gibarac
--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: RE: [sqlite] Problem: Can't See Tables in Database
Tom, Ritesh, After all is said and done, I have some egg on my face. The problem was ultimately a path issue, complicated for me because, in the hurry of development, I had forgotten that I had copied a database to another folder to perform a specific test. I had it duplicated one directory away from the original. I had copied the small, original, practice database that I had from my trial runs 3 weeks ago to a second place on my hard drive where my software was being developed. So, the copied database was always accessible and my software could always see its tables. The original database was in an adjacent folder; the software could not see it. So, when I made modifications to the original database through the command line interface, the changes couldn't be seen by my software. My software could only see the copied database which never changed. I apologize for consuming your time. Thank you for your assistance. Sincerely, Lee Crain
RE: [sqlite] Problem: SQLite C++ Examples in the SQLite Documentation No Longer Work; Can't See Tables in Database
Ritesh Kapoor,
Thank you for your response. I attempted to send you a response complete
with screenshots to eliminate any ambiguity but it was returned unsent
because it was too large for your mail daemon (30,000 bytes).
I took your advice and ran the query you suggested which produced this
result:
--
sqlite> .tables
t
sqlite> .schema
CREATE TABLE t( one varchar( 10 ), two smallint );
sqlite> select * from sqlite_master;
table|t|t|2|CREATE TABLE t( one varchar( 10 ), two smallint )
sqlite>
--
Also, I have taken care to make certain that the "sqlite3.exe" executable,
all databases (highlighted in red), and my import files (*.txt) are all in
the same directory to avoid path issues. Please see the screenshot below:
DELETED
--
I've created a very simple database named "DBm" with one table "t" and 2
fields, a varchar(10) and a smallint. Please see the screenshot below:
DELETED - the screenshot showed the following:
D:\DATA\SQLite>sqlite3 DBm
SQLite version 3.3.17
Enter ".help" for instructions
sqlite> .tables
t
sqlite> .schema
CREATE TABLE t( one varchar( 10 ), two smallint );
sqlite>
--
Then, using the examples in the SQLite documentation, I ran the example
code:
int main( )
{
sqlite3 *db;
char *zErrMsg = 0;
int rc;
// Test Open
rc = sqlite3_open( "DBm", & db );
if( rc )
{
assert( false );
fprintf( stderr, "Can't open database: %s\n", sqlite3_errmsg(
db );
sqlite3_close( db );
Sleep( 5000 );
return( 1 );
}
// Test SELECT
rc = sqlite3_exec( db, "SELECT * from t", callback, 0, );
if( rc!=SQLITE_OK )
{
fprintf( stderr, "SQL error: %s\n", zErrMsg );
Sleep( 15000 );
sqlite3_free( zErrMsg );
return( 1 );
}
The failure occurs on the sqlite3_exec( ) call.
--
And I receive this message (see screenshot):
DELETED
"SQL error: no such table: t"
--
What I'm attempting to do is about as vanilla an application as I can
think of.
I am open to any other suggestions as to what the problem might be. I hope
you are correct that the problem will turn out to be something very small.
Sincerely,
Lee Crain
Senior Software Engineer
DAZ 3D Productions
801-495-1777, x759
[EMAIL PROTECTED]
__
-Original Message-
From: Ritesh Kapoor [mailto:[EMAIL PROTECTED]
Sent: Monday, July 02, 2007 10:20 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; sqlite-users@sqlite.org
Subject: Re: [sqlite] Problem: SQLite C++ Examples in the SQLite
Documentation No Longer Work; Can't See Tables in Database
Importance: High
Try connecting to this database using the SQLite command line browser and
then check if the table 'tb1' exists. Most probably it dosen't.
You can check which all tables have been created using the query -
"select * from sqlite_master"
Regards,
Ritesh
> I've had to do a complete backtrack today. I went back to where I was 3
> weeks ago.
>
>
>
> I used the SQLite command line documentation to create the "ex1"
> database and enter 2 data records, exactly as the example shows.
>
>
>
> Then, I copied and pasted the SQLite C++ example into my program and
> attempted to execute it. It failed with a "table does not exist" error
> for the "tbl1" table in the "ex1" database.
>
>
>
> I've re-extracted the sqlite3 executable onto my hard drive and
> overwritten the old one. I've completely cleaned and rebuilt my project.
> Nothing has prevented this failure from recurring except for one thing:
>
> I have the original SQLite test database I created 3 weeks ago and I can
> still read it with the SQLite C++ example code and my C++ code. Why is
> this so?
>
>
>
> Is there some database metadata hidden somewhere on my hard drive that
> might be corrupted? If so, how do I resolve the problem with it?
>
>
>
> Thanks,
>
>
>
> Lee Crain
>
> Senior Software Engineer
>
> DAZ 3D Productions
>
> 801-495-1777, x759
>
> [EMAIL PROTECTED]
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Problem: Can't See Tables in Database
Tom Briggs,
Thank you for your response. I attempted to send you a response complete
with screenshots to eliminate any ambiguity but it was returned unsent
because it was too large for your mail daemon.
I have taken care to make certain that the "sqlite3.exe" executable, all
databases (highlighted in red), and my import files (*.txt) are all in the
same directory to avoid path issues. Please see the screenshot below:
DELETED
--
I've created a very simple database named "DBm" with one table "t" and 2
fields, a varchar(10) and a smallint. Please see the screenshot below:
DELETED - the screenshot showed the following:
D:\DATA\SQLite>sqlite3 DBm
SQLite version 3.3.17
Enter ".help" for instructions
sqlite> .tables
t
sqlite> .schema
CREATE TABLE t( one varchar( 10 ), two smallint );
sqlite>
--
Then, using the examples in the SQLite documentation, I ran the example
code:
int main( )
{
sqlite3 *db;
char *zErrMsg = 0;
int rc;
// Test Open
rc = sqlite3_open( "DBm", & db );
if( rc )
{
assert( false );
fprintf( stderr, "Can't open database: %s\n", sqlite3_errmsg(
db );
sqlite3_close( db );
Sleep( 5000 );
return( 1 );
}
// Test SELECT
rc = sqlite3_exec( db, "SELECT * from t", callback, 0, );
if( rc!=SQLITE_OK )
{
fprintf( stderr, "SQL error: %s\n", zErrMsg );
Sleep( 15000 );
sqlite3_free( zErrMsg );
return( 1 );
}
The failure occurs on the sqlite3_exec( ) call.
--
And I receive this message (see screenshot):
DELETED
"SQL error: no such table: t"
--
What I'm attempting to do is about as vanilla an application as I can
think of.
I am open to any other suggestions as to what the problem might be. I hope
you are correct that the problem will turn out to be something very small.
Sincerely,
Lee Crain
Senior Software Engineer
DAZ 3D Productions
801-495-1777, x759
[EMAIL PROTECTED]
[sqlite] Problem: SQLite C++ Examples in the SQLite Documentation No Longer Work; Can't See Tables in Database
I've had to do a complete backtrack today. I went back to where I was 3 weeks ago. I used the SQLite command line documentation to create the "ex1" database and enter 2 data records, exactly as the example shows. Then, I copied and pasted the SQLite C++ example into my program and attempted to execute it. It failed with a "table does not exist" error for the "tbl1" table in the "ex1" database. I've re-extracted the sqlite3 executable onto my hard drive and overwritten the old one. I've completely cleaned and rebuilt my project. Nothing has prevented this failure from recurring except for one thing: I have the original SQLite test database I created 3 weeks ago and I can still read it with the SQLite C++ example code and my C++ code. Why is this so? Is there some database metadata hidden somewhere on my hard drive that might be corrupted? If so, how do I resolve the problem with it? Thanks, Lee Crain Senior Software Engineer DAZ 3D Productions 801-495-1777, x759 [EMAIL PROTECTED]
[sqlite] Problem: Can't See Tables in Database
I have 2 databases created and populated:
> DBLee, my test database
> MiniMain, a subset copy of one of our production databases
>From the sqlite3 command prompt, I can run queries against both databases
and see the results. I can also enter the ".tables" command and see the
correct list of tables in each database.
DBLee
sqlite> .tables
tb12 tb22
sqlite>
MiniMain
sqlite> .tables
Categories InstallerFiles Items RequiredItems
ContentFilesItemCategories RelatedItems
sqlite>
_
I have written a small test program in C++ to perform proof of concepts.
The code is failing to execute any query against the MiniMain database
with the error message "SQL error: no such table: Categories". Categories
is one of 7 tables. The same failure results for all 7 tables and for all
simple queries ("Select.").
This is my source code:
int main( )
{
sqlite3 *db;
char *zErrMsg = 0;
int rc;
// Test Open
//rc = sqlite3_open( "DBLee", & db );
rc = sqlite3_open( "MiniMain", & db );
if( rc )
{
assert( false );
fprintf( stderr, "Can't open database: %s\n", sqlite3_errmsg(
db ) );
sqlite3_close( db );
Sleep( 5000 );
return( 1 );
}
// Test SELECT
//rc = sqlite3_exec( db, "SELECT * from tb12", callback, 0,
);
rc = sqlite3_exec( db, "SELECT * from Categories", callback, 0,
);
if( rc!=SQLITE_OK )
{
assert( false );
fprintf( stderr, "SQL error: %s\n", zErrMsg );
Sleep( 15000 );
sqlite3_free( zErrMsg );
return( 1 );
}
The commented out lines execute correctly. The "SELECT * FROM Categories"
line always fails.
What could be the problem?
Thanks,
Lee Crain
Senior Software Engineer
DAZ 3D Productions
801-495-1777, x759
