[sqlite] Creating custom function for recursive queries

[Marcel Strittmatter]

Hi

I implemented a custom function that returns a comma separated list of  
primary keys as a string by making recursive queries. This works well  
if I don't use subqueries. But I like to use subqueries like this

SELECT * FROM users WHERE id IN (SELECT parents('relations', 3));

Below some example data I used to test my custom function...

Is it possible to implement a custom function where the result can be  
used in a subquery like above?

Regards
Marcel


Here a dump of an example database:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO users VALUES(1,'Marge');
INSERT INTO users VALUES(2,'Homer');
INSERT INTO users VALUES(3,'Lisa');
INSERT INTO users VALUES(4,'Bart');
INSERT INTO users VALUES(5,'Maggie');
CREATE TABLE relations (parent INTEGER, child INTEGER);
INSERT INTO relations VALUES(1,3);
INSERT INTO relations VALUES(1,4);
INSERT INTO relations VALUES(1,5);
INSERT INTO relations VALUES(2,3);
INSERT INTO relations VALUES(2,4);

And here the output of my custom function (parents), which is similar  
to the builtin function group_concat():

sqlite SELECT name,parents('relations', id) FROM users WHERE id = 3;
nameparents('relations', id)
--  
Lisa1,2

But the following query returns nothing

SELECT name FROM users WHERE id IN (SELECT parents('relations', id)  
FROM users WHERE id = 3);

because the result of the custom function is a string (1,2)...

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problems compiling SQLite 3.3.4 on Mac OS X

[Marcel Strittmatter]

Hi

On my PowerMac G4 with Tiger 10.4.5 (Build 8H14), it works.
You can download an archive with finished configure and make steps  
from the following address:


http://www.dinoware.com/mailinglists/sqlite-3.3.4.tar.gz

You can look at the configure.log and make.log files to check for  
differences on your system.


Marcel

On 06.03.2006, at 21:11, [EMAIL PROTECTED] wrote:


Tito Ciuro [EMAIL PROTECTED] writes:


Hello,

SQLite 3.3.4
Mac OS X Tiger 10.4.5

After ./configure and make SQLite 3.3.4, I see that some files have
disappeared:

os_test.c
os_test.h
os_unix.h
os_win.h

This results in a few undefined symbols:

sqlite3OsClose
sqlite3FileSize
sqlite3OsLock
...

Any ideas?


First guess would be that 'configure' isn't detecting that OS X is  
Unix-like.
I suspect you might get a hint of what's going on if you carefully  
inspect the
output from 'configure' to see what supported OS it's detecting (if  
any).


Derrell

Re: [sqlite] Training opportunity: The Inner Workings Of SQLite

[Marcel Strittmatter]

http://conferences.oreillynet.com/oscon2005/



http://conferences.oreillynet.com/os2005/ is the correct url, isn't it?

Regards,
Marcel

Re: [sqlite] Concatenate NULL strings

[Marcel Strittmatter]

I tried using the | operator, but it seems that concatenating a string 
and a NULL results in a NULL value..
SELECT a | b FROM test WHERE ID=1;
- NULL
The operator for concat is ||, | is for bitwise operation, isn't it?
So I tried using coalesce (and ifnull), but it converts the string to 
a integer...
SELECT colasce(a, '') | coalesce(b, '') WHERE ID=1;
- 356
Try:
SELECT colaesce(a, '')||coalesce(b, '') WHERE ID = 1;
Marcel

Re: [sqlite] HEllo All

[Marcel Strittmatter]

I amVery much Beginner to SQLITE, i have very simple query:- How 
to password protect the db file.
There is no simple method to just password protect the database. If you 
want to protect the database you should consider encrypting it.

You can buy the encryption extension from hwaci 
(http://www.hwaci.com/sw/sqlite/prosupport.html) to fully encrypt the 
database
or you can use tools like http://www.voidbrain.com/sqlitesec.html 
(which does the encryption very similar to the official encryption 
extension)
or you can search for other encryption support - google

A good starting point is also the mailing list archive of this list..., 
e.g. http://www.mail-archive.com/sqlite-users%40sqlite.org/

Marcel

Re: [sqlite] Bind Blob in Version 2 Api

[Marcel Strittmatter]

Is there an similiar function to version 2 API to sqlite3_bind_blob 
function?
Version 2 doesn't handle blobs. You must store blobs as encoded 
strings. Look at the file src/enocde.c to see how to encode strings.

Marcel

Re: [sqlite] How to do NULL Handling in SELECT Statement?

[Marcel Strittmatter]

what is the correct way to query for NULL-values? I use  SQLite 
version 3.2.0
try the following:
... WHERE field IS NULL;
or
... WHERE field IS NOT NULL;
Marcel

[sqlite] database table is locked

[Marcel Strittmatter]

Hi all
When I try to insert data into a table, sqlite3 responses with 
SQLITE_ERROR and the error message: database table is locked. I 
searched already for unfinalized statements but couln't find any. The 
insert statement is not executed while a query is active...

The problem exists only on Windows (local storage), on Linux everything 
(except known nfs problems) works well (same code).

My question: Is there a easy way to test if a table is locked? Are 
there other possibilities to help debug such a problem (I already tried 
sqlit3_trace, but this doesn't help much because I don't see if a query 
is finalized or not).

Marcel

Re: [sqlite] database table is locked

[Marcel Strittmatter]

SQLITE_BUSY - another process has the whole database locked
SQLITE_LOCKED - one sqlite3_step() is trying to read (or write)
   the same table that another sqlite3_step() is
   writing (or reading) using the same DB handle.
It sounds as if Mr. Strittmatter is getting the second error.
The cause of SQLITE_LOCKED is usually because you failed to
sqlite3_finalize() the previous statement.
Yes. That's right. I found the missing sqlite3_finalize() command. But 
why did it work on Linux and failed on  Windows? Maybe because I work 
with nfs on Linux? Whatever... my problem is solved. Thanks for the 
help!

Marcel