Re: [sqlite] philosophy behind public domain?
Chad Whitacre wrote: I am interested in the reasoning behind SQLite's dedication to the public domain vis-a-vis other copyright/licensing options (GPL, BSD, etc.) Is there any documentation available on this decision? It comes down to goals. If your goal is to give other people code to use, then BSDL or public domain would be the way to go. If your goal is to get other people to give you code, then GPL would be a better approach. Also keep in mind that many users of SQLite are using it on server-side apps that aren't distributed to others and wouldn't be affected by the GPL distribution restrictions anyway. -- http://www.velocityvector.com/ | http://glmiller.blogspot.com/ http://www.classic-games.com/ | Linux is UNIX for Windows users. BSD is UNIX for UNIX users.
Re: [sqlite] how to list table making a view
Noel Frankinet wrote: Hello, Is there a better way to list all table making a view than parsing SQL. Is there an API ? You may be able to do this using the 'explain' statement for example, I have a view called 'myview'. Wrapping this into an sql select statement and proceeding this with explain e.g. > explain select null from myview gives a result set with the following:- addropcodep1p2p3 0ColumnName00null 1ColumnName10TEXT 2Integer00 3OpenRead1228mytable1 4VerifyCookie03016 5Integer00 6OpenRead217mytable2 7Rewind113 8Rewind212 9String00 10Callback10 11Next29 12Next18 13Close10 14Close20 15NullCallback10 16Halt00 All you need to do now is search through the result set for any opcode that is OpenRead, and the table name is in the p3 (last) column. As you can see, myview consists of mytable1 and mytable2 I would guess that this method could be expanded so that by looking for the the OpenRead and OpenWrite opcodes, the tables accessed used could be ascertained for any select, insert or update statement as well. I am not particularly versed with the opcodes though, so there may be something I have missed here, so maybe one of the experts on the list could indicate if this is a worthwhile approach. I was hoping that it might be possible to do something like "select p3 from (explain select null from myview) where opcode = 'OpenRead'", but unfortunatley that approach does not work :( Same question for the table schema, Don't quite understand what you require here. Regards Lawrence I do parse the SQL but its rather fragile. I'm still in 2.8 Thank you.
Re: [sqlite] short_column_name(s)
On May 26, 2005, at 12:58 PM, Hans Bieshaar wrote: Enter ".help" for instructions sqlite> .mode columns sqlite> .header on sqlite> pragma short_column_names; short_column_names Ok. I am an idiot. The 's' was just being truncated. Sorry for the noise.
Re: [sqlite] short_column_name(s)
On May 26, 2005, at 12:58 PM, Hans Bieshaar wrote: Enter ".help" for instructions sqlite> .mode columns sqlite> .header on sqlite> pragma short_column_names; short_column_names Interesting. Then this may be a bug in my wrapper. Thanks for doing the sanity check.
[sqlite] short_column_name(s)
I don't know if I'd call this a bug, exactly, but if you do PRAGMA short_column_names to get the short_column_names setting, the result you get back has a column named "short_column_name". In other words, the column name lacks an 's' at the end.
Re: [sqlite] qualified names in WHERE clause
All of the databases I've used required the columns in the order by clause also be present in the result set. It may not be universally true though On 5/26/05, Cronos <[EMAIL PROTECTED]> wrote: > It seems to me that MySQL and PostgreSQL are exhibitting some dubious > guessing behaviour as to which column it refers to, or perhaps they are > making some requirement of the order by to contain a column that is in the > resultset ??? If name were only in test11 then what would MySQL and > PostgreSQL do ? >
Re: [sqlite] database disk image is malformed
On Thu, 26 May 2005, Drew, Stephen wrote: >Hello, > >Assuming there is no external interference, how could one cause this >error to occur through embedded use of SQLite? And why, following a >restart of my application, does it not happen again immediately? It >seems most odd... > >Any clues would be most appreciated. This is SQLite 2.8.15. Without details, it's difficult to say. What platform you running on? Is the file on a network drive? Have you checked your application against buffer overruns and stray pointers? > >Regards, >Steve > -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
RE: [sqlite] qualified names in WHERE clause
It seems to me that MySQL and PostgreSQL are exhibitting some dubious guessing behaviour as to which column it refers to, or perhaps they are making some requirement of the order by to contain a column that is in the resultset ??? If name were only in test11 then what would MySQL and PostgreSQL do ? -Original Message- From: Will Leshner [mailto:[EMAIL PROTECTED] Sent: 26 May 2005 15:18 To: Forum SQLite Subject: [sqlite] qualified names in WHERE clause I guess I never really noticed this before (since I only use SQLite, of course :) ). But consider a query like this: SELECT test2.* FROM test2,test11 WHERE test2.id=test11.id ORDER BY name If the 'name' column happens to be a column in both test2 and test11, then SQLite will return an error. You need to qualify 'name' with 'test2.' to make the query acceptable. Apparently MySQL and PostgreSQL are able to recognize that 'name' refers to the 'name' in the result set and are able to disambiguate it.
Re: [sqlite] prepared statement and database schema changed problem
mhm, the problem is i have to store the "Query strings" somewhere, so that i'm able to prepare them again not nice, but OK. thanks for your answer. Dennis Cote wrote: Your call to sqlite3_step() is returning an SQLITE_ERROR result. The SQLITE_SCHEMA error code will be returned by the sqlite3_finalize() call (or a call to sqlite3_errcode()) after the error is reported. When you receive the SQLITE_SCHEMA error, you must finalize and then re-prepare your SQL statement before you can retry executing it. You can't simply re-execute it by calling sqlite3_step() again. HTH Dennis Cote
RE: [sqlite] qualified names in WHERE clause
You may be the person I've encountered who is able to perceive Someone Else's Problem. :) -Tom > -Original Message- > From: Will Leshner [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 26, 2005 10:58 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] qualified names in WHERE clause > > > On May 26, 2005, at 7:49 AM, Thomas Briggs wrote: > > > It's been our > > experience that the only truly reliable way to avoid this problem > > is to > > be explicit. > > > > I agree, and that's what I've always done up until now because it > never occurred to me that the SQL engine would be able to figure it > out. This "problem" was brought to me by somebody else. > > -- > REALbasic database options: http://sqlabs.net > REALbasic news and tips: http://rbgazette.com > KidzMail & KidzLog: http://haranbanjo.com > >
Re: [sqlite] qualified names in WHERE clause
On May 26, 2005, at 7:49 AM, Thomas Briggs wrote: It's been our experience that the only truly reliable way to avoid this problem is to be explicit. I agree, and that's what I've always done up until now because it never occurred to me that the SQL engine would be able to figure it out. This "problem" was brought to me by somebody else. -- REALbasic database options: http://sqlabs.net REALbasic news and tips: http://rbgazette.com KidzMail & KidzLog: http://haranbanjo.com
Re: [sqlite] qualified names in WHERE clause
On May 26, 2005, at 7:23 AM, Gregory Letellier wrote: try SELECT Test2.* FROM test2 inner join test11 ON test2.id=test11.id ORDER By Name; Thanks. I know there are ways to get the query to work. I think the problem is when people are migrating over from another database engine and they already have thousands of queries written. It's cool when those queries can work out of the box, so to speak. On the other hand, SQL is implemented slightly differently in all SQL engines, and so I imagine that some differences are simply unavoidable.
Re: [sqlite] prepared statement and database schema changed problem
Peter Hermsdorf wrote:
hi,
i'm using a DB base class which prepares some sql statements in it's
constructor. a derived class creates additional tables in the same DB
which "invalidates" the prepared statements in the base class (because
of the schema change).
After browsing the mailinglist i found an older discussion on a
similar topic where the conclusion was "execute the statement again
and it will work".
I'm using the code below to execute the prepared statements, but have
the problem to detect the "schema changed" error code.
the Output i get using the code below is:
26.05.05 11:34:56|sqlite3_step: Code 1 (database schema has changed)
so the errorcode is not SQLITE_SCHEMA (=16), but SQLITE_ERROR. But the
output of sqlite3_errmsg seems to be correct in this case (but the
return code of sqlite3_errcode is still 1 and not 16).
thanks for any advice!
bye, peter
bool continueTrying = true;
while(continueTrying)
{
rc = sqlite3_step(stmt);
switch(rc)
{
case SQLITE_BUSY:
DPrint(INFO, "SQLITE_BUSY: sleeping fow a while (step)");
usleep(10);
break;
case SQLITE_DONE:
case SQLITE_ROW:
continueTrying = false; // We're done
break;
case SQLITE_ERROR:
DPrint(ERROR,"sqlite3_step: Code %d (%s)",
sqlite3_errcode(db), sqlite3_errmsg(db));
continueTrying = false;
break;
case SQLITE_SCHEMA:
DPrint(WARN,"DB Schema changed. Trying again.");
break;
default:
handleError(rc);
continueTrying = false;
break;
}
}
Peter,
Your call to sqlite3_step() is returning an SQLITE_ERROR result. The
SQLITE_SCHEMA error code will be returned by the sqlite3_finalize() call
(or a call to sqlite3_errcode()) after the error is reported.
When you receive the SQLITE_SCHEMA error, you must finalize and then
re-prepare your SQL statement before you can retry executing it. You
can't simply re-execute it by calling sqlite3_step() again.
HTH
Dennis Cote
Re: [sqlite] qualified names in WHERE clause
try SELECT Test2.* FROM test2 inner join test11 ON test2.id=test11.id ORDER By Name; Will Leshner a écrit : I guess I never really noticed this before (since I only use SQLite, of course :) ). But consider a query like this: SELECT test2.* FROM test2,test11 WHERE test2.id=test11.id ORDER BY name If the 'name' column happens to be a column in both test2 and test11, then SQLite will return an error. You need to qualify 'name' with 'test2.' to make the query acceptable. Apparently MySQL and PostgreSQL are able to recognize that 'name' refers to the 'name' in the result set and are able to disambiguate it.
[sqlite] database disk image is malformed
Hello, Assuming there is no external interference, how could one cause this error to occur through embedded use of SQLite? And why, following a restart of my application, does it not happen again immediately? It seems most odd... Any clues would be most appreciated. This is SQLite 2.8.15. Regards, Steve
[sqlite] Using pragma user_version
Hi, In the sqlite wiki below it describes the use of pragma user_version, but doesn't really say how to use it. I'm evaluating the latest sqlite.exe. Say on initial creation of me DB I set user_version to 1. then product with DB schema 1 gets released. Then at some point in the future I want to insert some data into tables only if user_version ==1. then update user_version to 2. I don't think the sqlite syntax supports? IF (pragma.user_version == 1) BEGIN INSERT into x END Do I have to query user_version from code instead, through sqlite API? Thanks, damian http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq Q) On sqlite3_Open(), when the file with given filename doesn't exists, SQLite creates new database. But usually, the application needs to do some initialization work to be able to use this new database (create tables etc.) So, what is the most natural way to determine, that Open() created new database instead of opening the existing one? A) Use some system API funtion like IsFileExists(DB_filename) before calling sqlite_open(DB_filename) A) or, use the "user_version" pragma. Right after calling open(), do "pragma user_version". If it returns "0", then assume that this is a new file -- create your tables and do "pragma user_version=1" to mark that you've created your tables in this db. The next time you do "pragma user_version", it will return 1, signifying that you've previously set up this db. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.322 / Virus Database: 266.11.17 - Release Date: 5/25/2005
