Re: [sqlite] Column headers of result

[Simon Slavin]

On 5 Jul 2009, at 2:35am, BareFeet wrote:

> Hi Simon,


Just a reminder that you're posting to a list, not to me personally.

>> What are you trying to do ?  Find all the columns in a TABLE or find
>> all the columns in an arbitrary SELECT ?  If it's the former use
>> PRAGMA table_info for the table.
>
> Thanks, yes, I am familiar with pragma table_info to return the column
> headers of a table or view. But I need the same functionality for the
> result of an arbitrary sequence of SQL commands.

But an arbitary sequence of SQL commands doesn't have a list of  
columns.  It might involve two different unrelated tables, for  
example.  Or more than one SELECT commands.  Or no SELECT commands.   
Or INSERT commands that change more than one table.  Or one INSERT  
command that results in changes to two different tables.  I'm sorry  
but I still don't understand which columns you expect your  
functionality to produce given that you specify your sequence of  
commands must be arbitrary.

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

Re: [sqlite] Column headers of result

[BareFeet]

Hi Simon,

>> In any case, it's not possible to convert an ad hoc query (ie not  
>> known beforehand) into one that returns no rows.
>
> Sure it is.  Just add 'WHERE 1=2' to it if there's no WHERE clause,  
> or 'AND 1=2' if there is.

Increasing potential complexity of the "ad hoc" SQL command would  
require increasingly complex parsing to know what to append. eg we'd  
have to check for "begin/commit" and "union" and an existing "limit"  
etc. It is not practical.

>>> It's difficult in the command-line tool, but easy using function
>>> calls
>>> since there's a function call specially intended for it:
>>>
>>> 
>>
>> It would be great to see a pragma or built in SQL function that
>> returned the column headers of a given query.
>
> There is one: the one you just quoted my giving the URL for.

Sorry, let me clarify. I mean that I would like to see a pragma or  
function that can be used in an SQL expression (not a C function), so  
it can be used from the command line.

> If you want the smallest fastest possible query that will return at  
> least one row, do not use 'ORDER BY' and do use 'LIMIT 1'.

As mentioned in my other email, this doesn't lend itself to ad hoc (ie  
unknown beforehand) SQL commands.

> What are you trying to do ?  Find all the columns in a TABLE or find  
> all the columns in an arbitrary SELECT ?  If it's the former use  
> PRAGMA table_info for the table.

Thanks, yes, I am familiar with pragma table_info to return the column  
headers of a table or view. But I need the same functionality for the  
result of an arbitrary sequence of SQL commands.

> If the latter, replacing your 'WHERE' and 'ORDER BY' clauses with  
> LIMIT 1 will give you the right result if there's any data at all.

That will only work if it's a simple select, but not if it contains  
begin/commit, some inserts before the select, an existing limit etc.

Thanks for the thoughts,
Tom
BareFeet

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

Re: [sqlite] Column headers of result

[BareFeet]

Hi Simon,

 How can I get just the column headers without all the result rows?
>>>
>>> Turn headers on, then perform a search which gives no results.
>>
>> Unfortunately, the sqlite3 command line tool does not show the  
>> headers
>> when there are no result rows. In any case, it's nit possible to
>> convert an ad hoc query (ie not known beforehand) into one that
>> returns no rows.
>
> Add "limit 0" to the end of the select statement

That will work if it's just a simple select statement without "union"  
or an existing "limit" etc. But if it is a compound select or a series  
of commands (eg inserts then a select), then appending "limit" to the  
end will fail.

>>> It's difficult in the command-line tool, but easy using function  
>>> calls
>>> since there's a function call specially intended for it:
>>>
>>> 
>>
>> It would be great to see a pragma or built in SQL function that
>> returned the column headers of a given query.
>
> This is only a limitation in the shell (or using sqlite3_exec); column
> headers can be queried from prepared statements as already pointed
> out.

Yes, thanks, noted. My query/wish is to get that info from the command  
line.

Thanks,
Tom
BareFeet

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

Re: [sqlite] How to refer to a multiple-column primary key (PK) as 1 column (field) name

[John Machin]

On 5/07/2009 5:49 AM, James Scott wrote:
> I have the following:
> 
> CREATE TABLE [Sections] (
>   [Department] varchar NOT NULL COLLATE NOCASE,
>   [Course] varchar NOT NULL COLLATE NOCASE,
>   [Section] varchar NOT NULL COLLATE NOCASE,
>   [Class_Time] timestamp,
>   [I_Id] varchar COLLATE NOCASE,
>   [Room] varchar COLLATE NOCASE,
>   CONSTRAINT [sqlite_autoindex_Sections_1] PRIMARY KEY ([Department],
> [Course], [Section]));
> 
> CREATE INDEX [PK_Sections] ON [Sections] ([Department], [Course], [Section]);

Ummm, after those two statements, you have TWO indexes on your 3 fields.

sqlite> .header on
sqlite> select * from sqlite_master where type = 'index';
type|name|tbl_name|rootpage|sql
index|sqlite_autoindex_Sections_1|Sections|3|
index|PK_Sections|Sections|4|CREATE INDEX [PK_Sections] ON [Sections] 
([Department], [Course], [Section])
sqlite>

What are you trying to achieve?

> In the programming language, I need to refer to the primary key as 1 field.

And "the programming language" (why the mystery? which language?) 
doesn't support concatenation of strings? Or better, e.g. Python's tuple 
  pk = (department, course, section) which can be used as a dictionary 
key or a set element or a sort key or ... and can be easily picked apart 
to recover the parts: department, course, section = pk

> Does Sqlite allow a 'calculated field', such as concatenation of the 3
> columns in the PK? 

Of course. SQL has allowed it since the year dot.
http://www.sqlite.org/syntaxdiagrams.html#result-column
"expr" => expression ... do what you want.

HTH,
John

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

Re: [sqlite] Re bind Statement

[Simon Slavin]

On 5 Jul 2009, at 12:50am, Indiff3rence wrote:

> I'm new to SQLite programming. I need to insert a great number of  
> entries to
> a DB, but my implementation is slow.
> I've done a function that prepares a statement and returns it, like  
> this:
>
> const char *zSql = "INSERT INTO codewords( id, degree, seed, buffer )
> VALUES( ?, ?, ?, ? )";
> sqlite3_prepare( db, zSql, -1, , 0 );
> return pStmt;

Forget all the fancy ways to speed up individual INSERT commands.  You  
could probably just use sqlite_exec.  Your speed problem is probably  
that you're not specifying that they're all the same transaction.  Do  
a 'BEGIN TRANSACTION' before the first one and a 'COMMIT' after the  
last one.

If you do want to use _prepare, try to use the _v2 versions of it and  
check to see that you're using _finalize correctly.

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

Re: [sqlite] Column headers of result

[Simon Slavin]

On 5 Jul 2009, at 12:31am, BareFeet wrote:

> Hi Simon,
>
>>> How can I get just the column headers without all the result rows?
>>
>> Turn headers on, then perform a search which gives no results.
>
> Unfortunately, the sqlite3 command line tool does not show the headers
> when there are no result rows.

Whoops.  You're right.  If the query returns no rows, there are no  
columns, so there are no column headers.

> In any case, it's nit possible to
> convert an ad hoc query (ie not known beforehand) into one that
> returns no rows.

Sure it is.  Just add 'WHERE 1=2' to it if there's no WHERE clause, or  
'AND 1=2' if there is.

>> It's difficult in the command-line tool, but easy using function  
>> calls
>> since there's a function call specially intended for it:
>>
>> 
>
> It would be great to see a pragma or built in SQL function that
> returned the column headers of a given query.

There is one: the one you just quoted my giving the URL for.  If you  
want the smallest fastest possible query that will return at least one  
row, do not use 'ORDER BY' and do use 'LIMIT 1'.  But if no rows are  
returned even that will fail.

What are you trying to do ?  Find all the columns in a TABLE or find  
all the columns in an arbitrary SELECT ?  If it's the former use  
PRAGMA table_info for the table.  If the latter, replacing your  
'WHERE' and 'ORDER BY' clauses with LIMIT 1 will give you the right  
result if there's any data at all.

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

Re: [sqlite] Column headers of result

[Simon Davies]

2009/7/5 BareFeet :
>>>
> Hi Simon,
>
>>> How can I get just the column headers without all the result rows?
>>
>> Turn headers on, then perform a search which gives no results.
>
> Unfortunately, the sqlite3 command line tool does not show the headers
> when there are no result rows. In any case, it's nit possible to
> convert an ad hoc query (ie not known beforehand) into one that
> returns no rows.

Add "limit 0" to the end of the select statement

>
>> It's difficult in the command-line tool, but easy using function calls
>> since there's a function call specially intended for it:
>>
>> 
>
> It would be great to see a pragma or built in SQL function that
> returned the column headers of a given query.

This is only a limitation in the shell (or using sqlite3_exec); column
headers can be queried from prepared statements as already pointed
out.

>
>>
> Thanks,
> Tom
> BareFeet

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

[sqlite] Re bind Statement

[Indiff3rence]

Hello,

I'm new to SQLite programming. I need to insert a great number of entries to
a DB, but my implementation is slow.
I've done a function that prepares a statement and returns it, like this:

const char *zSql = "INSERT INTO codewords( id, degree, seed, buffer )
VALUES( ?, ?, ?, ? )"; 
sqlite3_prepare( db, zSql, -1, , 0 );
return pStmt;

Then I have another function that takes that statement. This function will
be called several times with different values to bind to the statement, so I
can insert different values:

sqlite3_reset( pStmt );
sqlite3_clear_bindings( pStmt );

sqlite3_bind_int( pStmt, 1, *id );
sqlite3_bind_int( pStmt, 2, *degree );
sqlite3_bind_int( pStmt, 3, *seed );
sqlite3_bind_blob( pStmt, 4, buffer, size, SQLITE_STATIC );

sqlite3_step( pStmt );

My question is: why is necessary to reset the statement before I can rebind
it?
It should work only if I clear the old bind, no? 

Thanks,

Pedro Saraiva
-- 
View this message in context: 
http://www.nabble.com/Rebind-Statement-tp24339026p24339026.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

Re: [sqlite] Column headers of result

[BareFeet]

>>
Hi Simon,

>> How can I get just the column headers without all the result rows?
>
> Turn headers on, then perform a search which gives no results.

Unfortunately, the sqlite3 command line tool does not show the headers  
when there are no result rows. In any case, it's nit possible to  
convert an ad hoc query (ie not known beforehand) into one that  
returns no rows.

> It's difficult in the command-line tool, but easy using function calls
> since there's a function call specially intended for it:
>
> 

It would be great to see a pragma or built in SQL function that  
returned the column headers of a given query.

>
Thanks,
Tom
BareFeet
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to refer to a multiple-column primary key (PK) as 1 column (field) name

[Simon Slavin]

On 4 Jul 2009, at 8:49pm, James Scott wrote:

> CREATE INDEX [PK_Sections] ON [Sections] ([Department], [Course],
> [Section]);
>
> In the programming language, I need to refer to the primary key as 1  
> field.
> Does Sqlite allow a 'calculated field', such as concatenation of the 3
> columns in the PK?  Or can I create a View to combine the 3?

SELECT department||course||section FROM sections

or

SELECT department||course||section FROM sections ORDER BY  
department,course,section

whichever suits you best.  You can use '||' to concatenate anywhere an  
expression is allowed.

As usual, it's not your job to work out which index is best: SQLite  
does it for you.  And because of the way SQLite works it will never  
actually look at the table to find those values: it needs to use the  
index anyway and will realise it can take the values directly from the  
index without having to do the extra lookup to get them from the table.

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

[sqlite] How to refer to a multiple-column primary key (PK) as 1 column (field) name

[James Scott]

I have the following:

CREATE TABLE [Sections] (
  [Department] varchar NOT NULL COLLATE NOCASE,
  [Course] varchar NOT NULL COLLATE NOCASE,
  [Section] varchar NOT NULL COLLATE NOCASE,
  [Class_Time] timestamp,
  [I_Id] varchar COLLATE NOCASE,
  [Room] varchar COLLATE NOCASE,
  CONSTRAINT [sqlite_autoindex_Sections_1] PRIMARY KEY ([Department],
[Course], [Section]));

CREATE INDEX [PK_Sections] ON [Sections] ([Department], [Course],
[Section]);

In the programming language, I need to refer to the primary key as 1 field.
Does Sqlite allow a 'calculated field', such as concatenation of the 3
columns in the PK?  Or can I create a View to combine the 3?

Any help is greatly appreciated.

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

Re: [sqlite] problem with SQLITE_BUSY

[Dan]

On Jul 4, 2009, at 8:12 PM, Wenton Thomas wrote:

> I use prepare statements, and I  am sure I  finalize all  of  them.

You may be mistaken. sqlite3_next_stmt() will return 0 if all
statements really are finalized.

   assert( sqlite3_next_stmt(db, 0)==0 );

Dan.

>
>
>
>
>
> 
> From: Igor Tandetnik 
> To: sqlite-users@sqlite.org
> Sent: Saturday, July 4, 2009 8:44:52 PM
> Subject: Re: [sqlite] problem  with SQLITE_BUSY
>
> Wenton Thomas wrote:
>> Now in my system I used sqlite  to manage  2  database file A.db and
>> B.db,  and each has a connection handle cA, cB. My operation perform
>> like this:
>>
>>
>> sqlite3_exec( select records from cA)
>> sqlite3_exec("begin transaction");
>> insert all records  into cB;
>> sqlite3_exec("commit transaction");
>>
>> All  return value is normal.,but when  I  execute
>> rc = sqlite3_close(),
>> return value rc always be SQLITE_BUSY.
>
> How precisely do you implement "insert all records" part? Do you use
> prepared statements, by any chance? If so, you need to finalize all  
> such
> statements before you can close the connection.
>
> Igor Tandetnik
>
>
>
> ___
> 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] problem with SQLITE_BUSY

[Wenton Thomas]

I use prepare statements, and I  am sure I  finalize all  of  them.





From: Igor Tandetnik 
To: sqlite-users@sqlite.org
Sent: Saturday, July 4, 2009 8:44:52 PM
Subject: Re: [sqlite] problem  with SQLITE_BUSY

Wenton Thomas wrote:
> Now in my system I used sqlite  to manage  2  database file A.db and
> B.db,  and each has a connection handle cA, cB. My operation perform
> like this:
>
>
> sqlite3_exec( select records from cA)
> sqlite3_exec("begin transaction");
> insert all records  into cB;
> sqlite3_exec("commit transaction");
>
> All  return value is normal.,but when  I  execute
> rc = sqlite3_close(),
> return value rc always be SQLITE_BUSY.

How precisely do you implement "insert all records" part? Do you use 
prepared statements, by any chance? If so, you need to finalize all such 
statements before you can close the connection.

Igor Tandetnik 



___
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] problem with SQLITE_BUSY

[Igor Tandetnik]

Wenton Thomas wrote:
> Now in my system I used sqlite  to manage  2  database file A.db and
> B.db,  and each has a connection handle cA, cB. My operation perform
> like this:
>
>
> sqlite3_exec( select records from cA)
> sqlite3_exec("begin transaction");
> insert all records  into cB;
> sqlite3_exec("commit transaction");
>
> All  return value is normal.,but when  I  execute
> rc = sqlite3_close(),
> return value rc always be SQLITE_BUSY.

How precisely do you implement "insert all records" part? Do you use 
prepared statements, by any chance? If so, you need to finalize all such 
statements before you can close the connection.

Igor Tandetnik 



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

Re: [sqlite] problem with SQLITE_BUSY

[Wenton Thomas]

The two database file belongs to different modules.
A module  gets  records  from another  through   interfaces, not  accesses  
other module's database file  directly.

The following  statements  

sqlite3_exec( select records from cA)
sqlite3_exec("begin transaction");
insert all records  into cB;
sqlite3_exec("commit transaction");


are  actually 
get_record_func();  
sqlite3_exec("begin transaction");
insert all records  into cB;
sqlite3_exec("commit transaction");

where   get_record_func() is  a interface of  module which has database file 
A.db.
The functon  get_record_func() execute  
"sqlite3_exec( select records from cA)".



From: "freshie2004-sql...@yahoo.com.au" 
To: General Discussion of SQLite Database 
Sent: Saturday, July 4, 2009 5:57:41 PM
Subject: Re: [sqlite] problem  with SQLITE_BUSY

What about using only one connection and the ATTACH statement:

http://www.sqlite.org/lang_attach.html

Also, see the select-stmt form of the INSERT statement:

http://www.sqlite.org/lang_insert.html

Something like...

sqlite3_open database B

ATTACH DATABASE A.db AS dbA

BEGIN


INSERT INTO main.mytable(col1,...colN) SELECT col1,...colN FROM dbA.myothertable

COMMIT

DETACH dbA


sqlite3_close B.db

Cheers!




From: Wenton Thomas 
To: sqlite-users@sqlite.org
Sent: Saturday, 4 July, 2009 7:31:55 PM
Subject: [sqlite] problem  with SQLITE_BUSY

Now in my system I used sqlite  to manage  2  database file A.db and B.db,  and 
each has a connection handle cA, cB.
My operation perform like this:


sqlite3_exec( select records from cA)
sqlite3_exec("begin transaction");
insert all records  into cB;
sqlite3_exec("commit transaction");

All  return value is normal.,but when  I  execute
rc = sqlite3_close(), 
return value rc always be SQLITE_BUSY.

Could anyone help me?

Does the  two database connection disturb each other?
I means, if  there exist a  reading lock on cA, can I write cB?


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



  

Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
___
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] SQLite counters by "key" and "tempkey" extensions

[Alexey Pechnikov]

Hello!

There is simple example for "tempkey" extension (Public Domain license):

create table colors(name text);
insert into colors values ('Red');
insert into colors values ('Green');
insert into colors values ('Blue');

select tempkey_install(); -- temp table "tempkeys" wiil be created
select tempkey_increment('','',1) as counter, name from colors;
1|Red
2|Green
3|Blue

select tempkey_get('',''); -- current values for key name '' in key group name 
''
3

--select tempkey_delete('',''); -- delete key name '' in key group name ''
select tempkey_uninstall(); -- delete all keys

The "key" extension is similar but is operate with non-temp table "keys".

Test script for module "key":
select 'ERROR' where key_install() is not null; --Create "keys" table.
select 'ERROR' where key_exists('common','nds%')!=0; --Check is param exists
select 'ERROR' where key_get('common','nds%') is not null; --Get _non-existing_ 
value _without_ default_value
select 'ERROR' where key_get('common','nds%',18)!='18'; --Get _non-existing_ 
value _with_ default_value
select 'ERROR' where key_set('common','nds%',10)!='10'; --Set value
select 'ERROR' where key_get('common','nds%')!='10'; --Get _existing_ value
select 'ERROR' where key_add('common','nds%',18) is not null; --Add _existing_ 
key
select 'ERROR' where key_replace('common','nds%',20)!='20'; --Replace 
_existing_ key
select 'ERROR' where key_delete('common','nds%')!=1; --Delete existing key
select 'ERROR' where key_delete('common','nds%') is not null; --Delete 
non-existing key
select 'ERROR' where key_delete('common') is not null; --Delete all keys in unit
select 'ERROR' where key_replace('common','nds%',20) is not null; --Replace 
_non-existing_ key
select 'ERROR' where key_add('common','nds%',18)!='18'; --Add _non-existing_ key
select 'ERROR' where key_increment('common','num',10)!='10'; --Increment 
_non-existing_ value
select 'ERROR' where key_increment('common','num',10)!='20'; --Increment 
_existing_ value
select 'ERROR' where key_prepend('common','string','prepend-')!='prepend-'; 
--Prepend value
select 'ERROR' where 
key_append('common','string','-append')!='prepend--append'; --Append value
select 'ERROR' where key_flush('common','string')!=1; --Flush existing key
select 'ERROR' where key_delete('common','string') is not null; --Flush 
non-existing key
select 'ERROR' where key_uninstall() is not null; -- Drop all keys from "keys" 
table.


SQLite build with some extra modules you can get from my debian repository:
deb http://mobigroup.ru/debian/ lenny main contrib non-free
deb-src http://mobigroup.ru/debian/ lenny main contrib non-free

Install repository keyring as
sudo aptitude install debian-mobigroup-keyring

Also you can get sources directly from
http://mobigroup.ru/debian/pool/main/s/sqlite3/
The patch file for current SQLite release is
http://mobigroup.ru/debian/pool/main/s/sqlite3/sqlite3_3.6.16-2.diff.gz

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problem with SQLITE_BUSY

[freshie2004-sqlite]

What about using only one connection and the ATTACH statement:

http://www.sqlite.org/lang_attach.html

Also, see the select-stmt form of the INSERT statement:

http://www.sqlite.org/lang_insert.html

Something like...

sqlite3_open database B

ATTACH DATABASE A.db AS dbA

BEGIN


INSERT INTO main.mytable(col1,...colN) SELECT col1,...colN FROM dbA.myothertable

COMMIT

DETACH dbA


sqlite3_close B.db

Cheers!




From: Wenton Thomas 
To: sqlite-users@sqlite.org
Sent: Saturday, 4 July, 2009 7:31:55 PM
Subject: [sqlite] problem  with SQLITE_BUSY

Now in my system I used sqlite  to manage  2  database file A.db and B.db,  and 
each has a connection handle cA, cB.
My operation perform like this:


sqlite3_exec( select records from cA)
sqlite3_exec("begin transaction");
insert all records  into cB;
sqlite3_exec("commit transaction");

All  return value is normal.,but when  I  execute
rc = sqlite3_close(), 
return value rc always be SQLITE_BUSY.

Could anyone help me?

Does the  two database connection disturb each other?
I means, if  there exist a  reading lock on cA, can I write cB?


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



  

Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite-undo: loadable extension to give undo/redo functionality

[freshie2004-sqlite]

Hi All,

As part of a project I am toying with writing I
needed undo/redo functionality, so have ended up writing a loadable
extension for sqlite which implements undo/redo functionality entirely
within the database using custom functions. Kind-of a C implementation
of http://www.sqlite.org/cvstrac/wiki?p=UndoRedo.

Only tested on Linux, so far.

Anyhoo... have fun if you are interested.

http://sourceforge.net/projects/sqlite-undo/

Cheers!


  

Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] problem with SQLITE_BUSY

[Wenton Thomas]

Now in my system I used sqlite  to manage  2  database file A.db and B.db,  and 
each has a connection handle cA, cB.
My operation perform like this:


sqlite3_exec( select records from cA)
sqlite3_exec("begin transaction");
insert all records  into cB;
sqlite3_exec("commit transaction");

All  return value is normal.,but when  I  execute 
rc = sqlite3_close(), 
return value rc always be SQLITE_BUSY.

Could anyone help me?

Does the  two database connection disturb each other?
I means, if  there exist a  reading lock on cA, can I write cB?


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

Re: [sqlite] referential integrity and INSERT OR REPLACE

[Kees Nuyt]

On Fri, 03 Jul 2009 14:38:43 -0700, James Gregurich
 wrote:

>
>nuts. that makes INSERT OR REPLACE worthless if you have tables  
>dependent on one another.
>
>
>Is there any way to manually get a list of records for which there  
>would be a conflict if a given record was inserted?

BEGIN;
INSERT the row, and observe any UNIQUE constraint failures.

If there isn't one, the INSERT succeeded and you're done.

If it failed, proceed to :
DELETE FROM yourtable 
WHERE PK  = yournewPKvalue
  OR CK1 = yournewCK1value
  OR CK2 = yournewCK2value
   ;
and repeat the INSERT.

COMMIT;

Legend:
PK  = primary key column
CKn = candidate key column #n
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can a COMMIT attempt result in SQLITE_BUSY ?

[Marcus Grimm]

Thank's Igor,

uhh... I should have read this more carefully.
I understand now that the commit may in fact
need to wait for some other shared locks...

sorry for the noise

Marcus

> Marcus Grimm wrote:
>> I'm wondering how it can happen that after a successfull
>> "BEGIN TRANSACTION;" statement and some inserts or update
>> statements, that the final COMMIT failes with a busy error code ?
>>
>> Shouldn't that be impossible because there can be only one running
>> transaction ?
>
> There may be multiple connections to the same database, each of which
> could start a transaction. For more details, see
>
> http://sqlite.org/lockingv3.html
> http://sqlite.org/lang_transaction.html
>
>> Everthing was working perfect until the user started a maintenance
>> tool that opens a single connection to the same sqlite database
>> while the server application was still running on the same hardware.
>> Now it happends that the server was not able to get a
>> COMMIT statement through
>
> You cannot commit any changes while another connection is reading from
> the database.
>
> Igor Tandetnik
>
>
>
> ___
> 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