[sqlite] Returning a boolean
How does one return a boolean value from a custom function, so that expressions like 'select * from table where foo()' operate as expected? There's no sqlite_result function for boolean...which I understand is actually not a native type. I tried returning 'True' and 'False' but that seemed to wind up being typed as text. Dan Winslow ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug with NULL in NOT IN
Well, NULL is not a value, technically, it is the lack of a value. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wilson, Ron P Sent: Wednesday, June 25, 2008 11:12 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] bug with NULL in NOT IN Why should the second query return zero rows? Clearly ids 1 and 2 don't exist in b. I'm not defending sqlite per se, just asking, logically speaking, why would those other databases return zero rows for that query? On a related note, what if NULL exists in both tables? Sqlite doesn't return that row for the first query: SQLite version 3.5.9 Enter ".help" for instructions sqlite> create table a(id integer); sqlite> insert into a values(1); sqlite> insert into a values(2); sqlite> insert into a values(3); sqlite> insert into a values(NULL); sqlite> create table b(id integer); sqlite> insert into b values(NULL); sqlite> insert into b values(3); sqlite> insert into b values(4); sqlite> insert into b values(5); sqlite> select * from a where id in (select id from b); 3 sqlite> Sqlite deliberately ignores all NULL values in a select. This explains the result, but logically doesn't follow because in fact NULL does exist in both tables. Interesting. RW Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Wednesday, June 25, 2008 11:50 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] bug with NULL in NOT IN On Jun 25, 2008, at 11:33 AM, [EMAIL PROTECTED] wrote: > Hello, > > with the following schema and contents: > > BEGIN TRANSACTION; > CREATE TABLE a(id INTEGER); > INSERT INTO a VALUES(1); > INSERT INTO a VALUES(2); > INSERT INTO a VALUES(3); > CREATE TABLE b(id INTEGER); > INSERT INTO b VALUES(NULL); > INSERT INTO b VALUES(3); > INSERT INTO b VALUES(4); > INSERT INTO b VALUES(5); > COMMIT; > > mysql, postgres, sqlite and mssql agree on: > > SELECT * FROM a WHERE id IN (SELECT id FROM b); > > yielding one row with id=3. > > However, on the query: > > SELECT * FROM a WHERE id NOT IN (SELECT id FROM b); > > mysql, postgres and mssql correctly return zero rows. SQLite however > returns two rows, for id=1 and id=2. > > http://www.sqlite.org/nulls.html doesn't list it, so perhaps this has > never come up before. No, this has never come up before. The behavior of SQLite is as I intended it to be. NULLs are deliberately and willfully filtered out of the subquery to the right of NOT IN. Are you saying that this is incorrect? Other than the fact that three other database engines do it differently, do you have any evidence that this really is incorrect? NULL behavior in SQL is highly unintuitive. In fact, as far as I can tell it is arbitrary. Can you or anybody else point to text in any SQL spec that would suggest that SQLites behavior in this case is wrong? 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] DB file name from sqlite3 *
Is there a way in a user program to get the db file name that has been associated with a particular sqlite3* ? Dan Winslow, GamePlan 402-991-5875 x219 [EMAIL PROTECTED] Third Nerd from the left,Technology Defenestration Office ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Accessing to database informations
My apologies for the bad editing, fixed copy below : Column names and types can be found with the following calls : sqlite3_column_name(ppStmt,x); sqlite3_column_decltype(ppStmt,x); sqlite3_column_type(ppStmt,x); Where ppStmt is the structure you get back from the prepare/step functions. Listing the tble names I am not sure about, I would assume there is some sort of 'select * from master' type query. Take a look at the code for the sqlite3.exe console executable, I bet it is in there somewhere. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Accessing to database informations
Column names and types can be found with the following calls : sqlite3_column_name(ppStmt,x); sqlite3_column_decltype(ppStmt,x); sqlite3_column_type(ppStmt,x); Where ppStmt is the structure you get back from the prepare/step functions. Listing the tble names I am not sure about, I would assume there is some sort of 'select * from master' type query. Take a look at the code for the sqlite2.exe console executable, I bet it is in there somewhere. DType=AnsiString(sqlite3_column_decltype(ppStmt,x)).UpperCase();-Ori ginal Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of c.panel Sent: Tuesday, June 24, 2008 3:04 PM To: sqlite-users@sqlite.org Subject: [sqlite] Accessing to database informations Hello, I'm a new user of SQLite, (and SQL too). I'm trying to find how can I access to tables names, column names and types, from a database, using C API. I found this from a result set but what about resident tables ?. Perhaps must I use a SELECT statement for this ? But If I do, does SQLLite generate a file from database (so time consuming...) or Is there a method I missed ? thanks for your answers. -- View this message in context: http://www.nabble.com/Accessing-to-database-informations-tp18098876p1809 8876.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Virtual table sample code
Is there any example source code available that demonstrates a working virtual table implementation? Thanks Dan Winslow, GamePlan 402-991-5875 x219 [EMAIL PROTECTED] Third Nerd from the left,Technology Defenestration Office ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Availablility of current row information in a select
Hi folks- Given a user-defined function of Foo(), and a query such as select * from sometable where foo() Is it possible from within the body of Foo() to access data from the current row of the database that is being examined? For instance, if I have a text field in the db record named 'MetaText' that contains a list of name/value pairs as one large string, is it possible for the code in Foo ( c++ in this case ) to be able to access the field data from MetaText? I know I could do select * from sometable where Foo(MetaText) and the data would be available as a parameter, but it would be nicer if I had some kind of visibility into the current row being processed. Thanks Dan Winslow, GamePlan 402-991-5875 x219 [EMAIL PROTECTED] Third Nerd from the left,Technology Defenestration Office ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Rowid After Sorting
Why do you need your rowid to be changed? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mahalakshmi.m Sent: Friday, March 14, 2008 1:03 AM To: sqlite-users@sqlite.org Subject: [sqlite] Rowid After Sorting Hi, I am working in 3.3.6 and my table looks like. Id - Integer Primary Key Name- Text Id Name 1 zzz 2 xxx 3 bbb 4 aaa SELECT rowid,Id,Name FROM MyTable ORDER BY Name; Rowid Id Name 4 4 aaa 3 3 bbb 2 2 xxx 1 1 zzz But I need my rowid to be chaged as follows. Rowid Id Name 1 4 aaa 2 3 bbb 3 2 xxx 4 1 zzz I tried with Views but its rowid is not changed. But by creating one new table like "create table Temp as select Name from Mytable order by Name;" gives the desired result as above. Its taking more time for this. So I there any other way I can do the same without creating table because in My table I am having many fields and each time I will create and drop the table for each fields. Can anyone please help to solve this. Thanks & Regards, Mahalakshmi.M ___ 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] Is there direct (non SQL) table access?
Well, I'm not sure what you are trying to do that you can't do with SQL.
I'll assume that you really want to iterate over all rows of ( a single
table? ) and conditionally update a field? If so, wouldn't a
"update tablename set [Classification]='Retired' where [Age]>65" do what
you need?
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of danjenkins
Sent: Sunday, March 16, 2008 12:19 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Is there direct (non SQL) table access?
Is it possible to access a table without a SQL query? I've been
searching and haven't found a way.
I'd like to do something like this:
sqlite3 *pDB = NULL; // database pointer
FILE* fp = fopen("people.db","a+");
sqlite3_open("people.db",);
// Pseudo code that I need help with follows...
while(!pDB->Eof){
if(pDB->FieldValues["Age"] > 65;
pDB->Edit();
pDB->FieldValues["Classification"] = "Retired";
pDB->Post();
}
pDB->Next();
}
fclose(fp);
sqlite3_close(pDB);
I'd really appreciate a couple of tips so I can get back on track with
my project.
Thanks a million.
Dan
--
View this message in context:
http://www.nabble.com/Is-there-direct-%28non-SQL%29-table-access--tp1607
5825p16075825.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question re using SQLite in basic client/serversituation
Not sure on the locking issue, but some of it might have to do with whether the database file is being provided via a NFS or a Windows Network Filesystem. Sometimes file-level locking is not real robust on networked file systems. If you are using some kind of ODBC/ADO provider then the details for that would be what counts. Not sure what cocoa is, but it sounds fairly modern so you are probably able to go through an ODBC connection or provider of some sort, in which case youwould just configure the SQLITE connectivity layer you are using. As far as SQLITE itself goes, there is no database server to 'log into', all you are doing is providing a file name ( the file for which has to be visible to the client program ) to the connect function. If you want a more usual client/server situation, youd have to use ODBC or ADO, else you'd have to write it yourself. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeff Brown Sent: Friday, March 07, 2008 4:06 AM To: sqlite-users@sqlite.org Subject: [sqlite] Newbie question re using SQLite in basic client/serversituation Hi all I'm looking at switching the database that I use for my XCode/Cocoa application from MySQL to SQLite. (I'm getting tired of all the incompatibilities with new versions of MySQL). But I need to be able to use it as a server with at most 10 clients that occasionally use the system. I noticed in the documentation that it said the following "so, the file locking logic of many network filesystems implementation contains bugs (on both Unix and windows). If file locking does not work like it should, it might be possible for two or more client programs to modify the same part of the same database at the same time, resulting in database corruption." >From users' experience, is SQLite likely to be safe with so few clients on a local network. Also how do I login to the SQLite database from a client app? Cheers Jeff Get the name you always wanted with the new y7mail email address. www.yahoo7.com.au/y7mail ___ 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] Performance
I believe you can speed up the inserts by batching things in a transaction. The prevents a journalling operation after each insert. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of James Kimble Sent: Thursday, March 06, 2008 1:30 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Performance > > >That's sounds like good advice. I'll do that. >> >> Working with flash in this way is going to be a challenge. With >> limited number of writes in a lifetime (this device needs to last >> approx 20 >> years...) I will have to make some major design decisions around how >> I handle the writes. > > >> How important is the persisent data? Is it kept for audit, >> statistical analysis, what? Basically, can you afford to lose it, or >> at least a subset of it? If so , then I'd say maintain the data in an >> in-memory database, and write out the data to disk (using safe >> synchronous writes) at whatever intervals you desire. >> I say use safe synchronous writes, as recovery may be an issue if you >> don't write safely. Not what you need on an embedded system where >> user interaction may be required. >> Christian Most of my data is not persistent. I am thinking of keeping everything RAM based with a write out only done opportunistically. Values need to be retained for logging purposes but configuration settings are very stable so they can be saved only when changed. I have another question and I think I know the answer but just to be sure... I created a 40 column table with 10,000 rows as a test database for a reader and a writer process to bang on (performance proof). The table is as so: sqlite3 test.db 'create table PerfTest1 (name varchar(20), value1 int, value2 int, value3 int, value4 int, value5 int, value6 int, value7 int, value8 int, value9 int, value10 int, value11 int, value12 int, value13 int, value14 int, value15 int, value16 int, value17 int, value18 int, value19 int, value20 int, value21 int, value22 int, value23 int, value24 int, value25 int, value26 int, value27 int, value28 int, value29 int, value30 int, value31 int, value32 int, value33 int, value34 int, value35 int, value36 int, value37 int, value38 int, value39 int)' The data is repetitive junk. Just: "key1", 1, 2, ,3 . "key2", 1, 2, 3 What's driving me mad is that when I do a select from the command line like so: sqlite3 test.db `select name from PerfTest1 where name like "key1%"' The rows returned stop at "key199". No select will return a row past that point. However if I do a simple: sqlite3 test.db 'select name from PerfTest1' and just let it go it prints all 1 rows!! Is this due to the type of query prepartion done from the command line interface? Maybe limits the size of something? That doesn't make a lot of sense either though because if I query the specific row I want it returns nothing. sqlite3 test.db 'select name from PerfTest1 where name = "key1000"' Returns 0 rows. Any idea what's going on there?? It also took about 15 minutes for the .import command to insert the 10,000 rows into the table from a text file. That's was a little scarey. (Sorry for the length. Probably should have started another thread) ___ 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
