[sqlite] any way to find out how many current connections to db?

[Chase]

is there any way (in c/c++) to find out how many current connections
there are to a database that i am connected to?

i'd like to do some housekeeping on the database every so often, but
only if there's no one else connected to it at the time.

any ideas?

thanks.

- chase


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] any way to find out how many current connections to db?

[Chase]

is there any way (in c/c++) to find out how many current connections 
there are to a database that i am connected to?


i'd like to do some housekeeping on the database every so often, but 
only if there's no one else connected to it at the time.


any ideas?

thanks.

- chase




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how to create C functions and refer to them in sql

[Chase]

okay, wait sorry wrong question.

here's the deal.  i want this trigger to fire -- and insert valid guids 
into a table -- even outside the context of my app.


using sqlite3_create_function(), i can create a sort of temporary 
function that only works from with my app (or other running instances 
of my app), but i want it to fire even if someone opens the database 
file in some other 3rd-party editor and inserts/updates/deletes the 
table with the trigger.


well... it DOES fire, but when it does, it won't know what newuuid() means.

so i guess my question should be how to i **INSTALL/EMBED** my 
custom function **into** the database file.


- chase






On August 6, 2007, Eugene Wee wrote:


You are probably looking for sqlite3_create_function:
http://www.sqlite.org/capi3ref.html#sqlite3_create_function

Regards,
Eugene Wee

Chase wrote:


i need a trigger to create and insert a new guid into a table, but 
apparently there is no built-in function for creating guids in sqlite.


i can create the guid in C using uuid_generate() and then uuid_unparse() 
to get it into a string format.


but how can i call that c code from a trigger?

my understanding was that sqlite allows for user functions written in C 
and used from within your sql code, but i forget where i saw that and 
i'm so far unable to find in the docs where it shows how this is done.


anyone done this before?

- chase


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] SQLITE_BUSY database is locked when db is on network drive...

[Chase]

running sqlite 3.4.1 on mac os x 10.4

i've set up a file share on another mac running 10.4 and placed a small 
db file on the share and chmod'ed it to 777 (full access).


i go to the development mac and log into the other mac as the user who 
owns that db file.


the path on my dev mac to that shared db is now:  
/Volumes/SharedFolder/smalldb.db


the test app (which works perfectly with the same exact small db 
sitting on its local drive) is launched and a connection is made to the 
db sitting on the other mac.


it connects fine.  no errors.  but then i try to create a temp table 
(which, like i said, works if the db is local) it fails immediately 
with SQLITE_BUSY database is locked.


NO ONE else is accessing this database file, so how is that possible?


what am i doing wrong?

- chase



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] SQLITE_BUSY database is locked when db is on network drive...

[Chase]

running sqlite 3.4.1 on mac os x 10.4

i've set up a file share on another mac running 10.4 and placed a small 
db file on the share and chmod'ed it to 777 (full access).


i go to the development mac and log into the other mac as the user who 
owns that db file.


the path on my dev mac to that shared db is now:  
/Volumes/SharedFolder/smalldb.db


the test app (which works perfectly with the same exact small db 
sitting on its local drive) is launched and a connection is made to the 
db sitting on the other mac.


it connects fine.  no errors.  but then i try to create a temp table 
(which, like i said, works if the db is local) it fails immediately 
with SQLITE_BUSY database is locked.


NO ONE else is accessing this database file, so how is that possible?


what am i doing wrong?

- chase



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] database is locked SQLITE_BUSY when db is on network drive...

[Chase]

running sqlite 3.4.1 on mac os x 10.4

i've set up a file share on another mac running 10.4 and placed a small 
db file on the share and chmod'ed it to 777 (full access).


i go to the development mac and log into the other mac as the user who 
owns that db file.


the path on my dev mac to that shared db is now:  
/Volumes/SharedFolder/smalldb.db


the test app (which works perfectly with the same exact small db 
sitting on its local drive) is launched and a connection is made to the 
db sitting on the other mac.


it connects fine.  no errors.  but then i try to create a temp table 
(which, like i said, works if the db is local) it fails immediately 
with SQLITE_BUSY database is locked.


NO ONE else is accessing this database file, so how is that possible?


what am i doing wrong?

- chase



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] how do i declare and use variables in sqlite?

[Chase]

select 542 as x;

that part works, but then when i try to access it, i get no column x...

select x;

set x = 542;
var x = 542;
@set x = 542;
@var x = 542;
set @x = 542;
var @x = 542;
$set x = 542;
$var x = 542;
set $x = 542;
var $x = 542;


none of these seem to work and i can't find the documentation on this 
subject.


can someone shed some light on this for me?

thanks.

- chase



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] sqlite3_update_hook does not get called when other app updates shared db

[Chase]

When i call sqlite3_update_hook() from App A and point it to my 
callback, i am only notified when app A updates the database.


When app B (another instance of the same app as A) updates the 
database, app A's callback is never called, and vice versa.


I thought the whole point of this function was to get callbacks on any 
**outside** tinkering with a shared database file.


Can someone please shed some light on this.

Thank you.

- Chase




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] CREATE INDEX that is case insensitive?

[Chase]

Right now, when i do a select in sqlite that is supposed to be in 
alphabetical order, i get:


DC
Da
De
Do



instead of:

Da
DC
De
Do


The LIKE operator doesn't seems to be helping me here either.  It 
searches the text case-insensitively, but it still outputs it in the 
wrong order.  Keep in mind that I'm aware that the former is 
NUMERICALLY/TECHNICALLY in order, but I need it in human-understood 
alphabetical order (we'll call it).


We ultimately will be creating an index for this column anyway, so 
let's just jump ahead and talk about creating an INDEX which would spit 
out:


Da
DC
De
Do

If, however, it has nothing to do with the index and instead we should 
deal with this in the SELECT, that's fine.  In that case, tell me what 
that SELECT statement would look like.


Thanks.

- Chase








-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] CREATE INDEX that is case insensitive?

[Chase]

ok.  here's a SELECT that works...

SELECT foo FROM bar WHERE foo LIKE 'D%' ORDER BY upper(foo);


but, how could that upper(foo) part be used with the CREATE INDEX syntax?

neither of the following attemps worked (syntax errors):

CREATE INDEX barfooindex ON bar upper(foo);

or

CREATE INDEX barfooindex ON bar(foo) ORDER BY upper(foo);

at this point, i guess i'm just wanting to avoid the extra run-time 
overhead of running the UPPER() function on every foo returned from a 
select.  i may just add an extra column to the table that's the 
upper-case version of title and index that.  same net result, but it 
would add, of course, to the size of the db.


like:

foo UPPERFOO
TestTEST



any ideas?

- chase










On July 30, 2007, Chase wrote:



Right now, when i do a select in sqlite that is supposed to be in 
alphabetical order, i get:


DC
Da
De
Do



instead of:

Da
DC
De
Do


The LIKE operator doesn't seems to be helping me here either.  It 
searches the text case-insensitively, but it still outputs it in the 
wrong order.  Keep in mind that I'm aware that the former is 
NUMERICALLY/TECHNICALLY in order, but I need it in human-understood 
alphabetical order (we'll call it).


We ultimately will be creating an index for this column anyway, so 
let's just jump ahead and talk about creating an INDEX which would spit 
out:


Da
DC
De
Do

If, however, it has nothing to do with the index and instead we should 
deal with this in the SELECT, that's fine.  In that case, tell me what 
that SELECT statement would look like.


Thanks.

- Chase








-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] how do i generate a uniqueidentifier ?

[Chase]

how do i generate a uniqueidentifier ?

- chase



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how do i generate a uniqueidentifier ?

[Chase]

sorry.  should have been more specific.  i'm talking about guids (or uuids).

here's the create table line:

CREATE TABLE foobar (id uniqueidentifier, foo text, bar text, PRIMARY 
KEY (id));


that works great, but i have not been able so far to generate a fresh 
guid to insert into the table.


in ms-sql, you'd use newid(), for example:

insert into foobar values (newid(), Aaa, Bbb);

and then you'd get something like:

select * from foobar;

   idfoo  bar
   ___

   {0109--0010-8000-00AA006D2EA4}AaaBbb


so how is this done in sqlite3?

- chase




On July 27, 2007, Mark Richards wrote:


Chase wrote:


how do i generate a uniqueidentifier ?

Define a column as follows:
{fieldname} INTEGER NOT NULL PRIMARY KEY

eg:

   CREATE TABLE hardware_types (record_key INTEGER NOT NULL PRIMARY 
KEY,hardware_key INTEGER default 0);


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-