Re: [sqlite] Rowid After Sorting

[Mahalakshmi.m]

Dennis Cote wrote:
>Then you should add an index on the Name column and use that to process 
>your queries in Name order more quickly.
>create index on MyTable(Name);

Thanks a lot Dennis. My process is more fast by means indexing.

Dennis Cote wrote:
>If you really insist on reordering your table, you must copy the data 
>somewhere else, empty the table, and reinsert the data in the order you 
>want the rowid to present. Note, this will not work if you plan on 
>inserting or deleting data after this initial insert. 

Ya. Right now each time when I insert or delete I will drop the tbl and
insert once again like: create temp table t as select Id, Name from t order
by Name; I think no other way than this will work out.

Mahalakshmi.m wrote:
> So, to find the index of a name, which is in sorted order, I need the
>Rowid to be changed as shown in case 2 rather than in case 1.

Dennis Cote wrote:
>Why do you want to find the index of a Name?
>Tables in SQL databases are not arrays. You don't use an index to 
>retrieve the data. Tables are more like unordered sets of data.

Bcoz in my Application as input - I will give the starting letter say 'c'
Then as output - I need the rowid of the name that is starting with 'c' if
no name starts with that character then the rowid of the name which is next
should be provided along with the name.

For eg, I will create one temp tblb where I will store the name in sorted
order itsef s follows:
Rowid   id  name
1   4   aaa
2   2   bb
3   1   eee
4   3   zzz

Input for my application is - c
Required output is  - rowid -3 and name - eee

I will use the following querry:
Select rowid,name from tbl where name >= 'c'; 
Think this gives a clear idea abt my requirement.


Thanks & Regards,
Mahalakshmi.M








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

[sqlite] finding rows within a certain time interval of another row

[P Kishor]

  Given a table t (name, date)

  I want to find all rows within, say, 6 months of a specific row, say
name = 'foo'. So, I am doing

  SELECT name
  FROM t
  WHERE
(SELECT julianday(date) FROM t WHERE name = 'foo') -
julianday(date) < 180 OR
  julianday(date) - (SELECT julianday(date) FROM t WHERE name = 'foo') < 180

Somehow the above doesn't seem very efficient what with two
sub-selects and all. Suggestions please.

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

Re: [sqlite] Multithreads access the same database resultingin database is locked error

[Xuanvinh Vu]

I didnt realize that it could cause livelocks. Thank you for bringing that to 
my attention. It still doesnt explain why the database stays locked. I could 
just ignore the error and wait to update the database the next time the thread 
runs again. What I want to know is why once I got the exception that the 
database is locked it stays locked and I cant update the databases with other 
threads.  
_
Watch “Cause Effect,” a show about real people making a real difference.  Learn 
more.
http://im.live.com/Messenger/IM/MTV/?source=text_watchcause
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] resetting a column back to its default value

[Dennis Cote]

Alex Katebi wrote:
> 
> I was woundering how I can update a column in my table back to its default
> value.
> 
> For example:
> 
> create table t1 (value integer default 55, name text);
> insert into t1(name) values('hello');
> update t1 set value=default;  /* for illustration only */
> 

This will update every row in the table, not just the one with the name 
'hello'. Is that what you want?

> How can I achive the desired behavior short of doing "update t1 set
> value=55"
> 

I don't think this can be done using an update unless you hard code the 
value as you have shown above.

The default value is only used when a row is inserted into the table and 
a value isn't provided for that column, so you will have to arrange for 
that by deleting the row and then inserting it again.

delete from t1 where name = 'hello';
insert into t1(name) values('hello');

An insert or replace also works since internally it does a delete and 
then an insert when doing a replacement, but it requires that name be a 
primary key, and even then inserting a null value does not insert the 
default value, you must actually supply only non default values.

create table t1 (value integer default 55, name text primary key);
insert into t1(name) values('hello');
insert into t1(name) values('test');
insert into t1 values(66, 'other');
update t1 set value = 77 where name = 'test';

insert or replace into t1 values(null, 'hello');  --doesn't work
insert or replace into t1(name) values('hello');  --works


HTH
Dennis Cote

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

Re: [sqlite] Problems Compiling Sqlite

[Dennis Cote]

Jus GoodFun wrote:
> 
> Thanks for the info but before I embark on a huge coding excercise
> could you give me a vague clue as to how much work would be involved
> to get up and running?
> 

Well that depends upon how similar uC/OS is to one of the supported OS. 
If it is quite similar you can probably copy large chunks of code. Even 
if it is not, you may be able to start with a copy of one of them and 
modify it as need to get the the same functionality using uC/OS.

> Is there any documentation which will help me in implementing ur
> suggested changes?
> 

The comments in the SQLite source are generally very clear and 
descriptive. You can learn a great deal by simply reading through some 
of them.

The documentation at http://www.sqlite.org/c3ref/vfs.html describes the 
virtual file system object used to interface with an operating system. 
This page http://www.sqlite.org/34to35.html describes the purpose and 
use of the OS interface layer when it was introduced to SQLite. Item 
2.1.6 is a checklist for creating a new vfs. It also describes the mutex 
interface that you may need to implement to use uC/OS mutex objects.

The SQLite Wiki at http://www.sqlite.org/cvstrac/wiki has a few pages 
that will help. http://www.sqlite.org/cvstrac/wiki?p=SqliteVfs is a 
skeleton vfs file that you will need for a new vfs implementation.

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

Re: [sqlite] Trigger Steps

[drh]

"Noah Hart" <[EMAIL PROTECTED]> wrote:
> The documentation for Triggers defines a trigger step as follows:
> 
> trigger-step ::=  update-statement | insert-statement |
> delete-statement | select-statement
> 
> What would be an example of the select-statement?
> 

SELECT statements are useful for the side-effects of functions
that the SELECT statement might call.  For example:

  CREATE TRIGGER r1 AFTER UPDATE ON t1 BEGIN
SELECT raise(ABORT, 'x is too large') WHERE x>5;
  END;

You might also have application-defined functions that cause
side effects that you want to issue:

  CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
SELECT notify_other_process(new.x);
  END;

--
D. Richard Hipp <[EMAIL PROTECTED]>

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

Re: [sqlite] Trigger Steps

[Igor Tandetnik]

Noah Hart <[EMAIL PROTECTED]> wrote:
> The documentation for Triggers defines a trigger step as follows:
>
> trigger-step ::= update-statement | insert-statement |
> delete-statement | select-statement
>
> What would be an example of the select-statement?

Just your plain old SELECT statement. Useful for calling custom 
functions that may have side-effects, and also for calling a special 
function RAISE (which is particularly useful in a trigger).

Igor Tandetnik 



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

[sqlite] Trigger Steps

[Noah Hart]

The documentation for Triggers defines a trigger step as follows:

trigger-step ::=update-statement | insert-statement |
delete-statement | select-statement

What would be an example of the select-statement?

Regards,

Noah Hart



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


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

Re: [sqlite] Problems Compiling Sqlite

[drh]

Jus GoodFun <[EMAIL PROTECTED]> wrote:
>
>   I tried the DSQLITE_MUTEX_NOOP and then a rebuild but still 
> got the following compile errors, I'm sure I'm missing something
> simple...

Try compiling with -DSQLITE_THREADSAFE=0

--
D. Richard Hipp <[EMAIL PROTECTED]>

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

Re: [sqlite] Problems Compiling Sqlite

[Jus GoodFun]

Hi Dennis,
   
  Thanks for the info but before I embark on a huge coding excercise could you 
give me a vague clue as to how much work would be involved to get up and 
running? 
   
  Is there any documentation which will help me in implementing ur suggested 
changes?
   
  Sqlite would be perfect for my application but unfortuanatly I don't have a 
huge amount of time to work on it and end up with more problems.
   
  I tried the DSQLITE_MUTEX_NOOP and then a rebuild but still got the following 
compile errors, I'm sure I'm missing something simple...
   
  Thanks again
   
  Justin.
   
   
  'Building file: ../sqlite3.c'
  'Invoking: GNU C Compiler'
  m68k-elf-gcc -m5206e -O2 -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_MUTEX_NOOP 
-gdwarf-2 -I"D:\Apps\Nburn\include" -I"D:\Apps\Nburn\MOD5282\include" 
-I"D:\Apps\Nburn\gcc-m68k\m68k-elf\include" 
-I"D:\Apps\Nburn\gcc-m68k\lib\gcc\m68k-elf\3.4.2\include" 
-IE:/CDev/Embedded/Netburner/5282/Workspace/iControlv1/include -Wall -c 
-fmessage-length=0 -MMD -MP -MF"sqlite3.d" -MT"sqlite3.d" -DMOD5282 
"../sqlite3.c" -o"sqlite3.o"
  ../sqlite3.c:12911: error: parse error before "pthread_mutex_t"
  ../sqlite3.c:12911: warning: no semicolon at end of struct or union
  ../sqlite3.c:12914: error: parse error before "owner"
   
  
Dennis Cote <[EMAIL PROTECTED]> wrote:
  Jus GoodFun wrote:
> 
> I develop in 'C' within the Windows version of the Eclipse
> Environement producing embedded systems focused on the Motorola
> ColdFire Processors specifically the MCF5282.
> 
> The target operating system is The uC/OS which is a preemptive
> multitasking RTOS.
> 

You will probably need to start with the preprocessed source and 
implement your own OS support files. The SQLite core supports unix, 
windows, and OS2. You will have to write your own support modules for 
uC/OS.

You can get the preprocessed source files at 
http://www.sqlite.org/sqlite-source-3_5_7.zip You should probably start 
by looking at the source files os.h and mutex.h

If you compile the amalgamation with SQLITE_MUTEX_APPDEF defined, then 
the builtin mutex code for all the supported OS's are disabled, and you 
will have to supply suitable functions for SQLite to call. If you don't 
need mutual exclusion (i.e. you are writing multithreaded code), then 
you can define SQLITE_MUTEX_NOOP to included a set of macros that 
effectively remove all mutex calls from SQLite.

HTH
Dennis Cote

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


   
-
Be a better friend, newshound, and know-it-all with Yahoo! Mobile.  Try it now.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multithreads access the same database resultingin database is locked error

[Igor Tandetnik]

Xuanvinh Vu <[EMAIL PROTECTED]> wrote:
> I did check and I have all the BEGINs and COMMITs. I understand that
> by default the sqlite busy signal is given right away so what I do is
> I wait till the next interval elapses to try to update the DB again
> by that time whatever the transaction that causes the DB to block me
> before should have been completed.

Wouldn't you have a livelock situation? If you have a transaction that 
executes every 10 seconds and another that executes every 5 minutes, 
then it's possible that every time 5-minute transaction tries to run a 
10-second transaction happens to run also. There is an even number of 
10-sec intervals in 5 minutes. You need some sort of back-off scheme to 
de-synchronize the two threads that started to run in lockstep.

http://en.wikipedia.org/wiki/Deadlock#Livelock

Igor Tandetnik 



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

Re: [sqlite] Multithreads access the same database resulting in database is locked error

[Ken]

I don't see any error handling in your code for SQLITE_BUSY...

Also, Depending upon the odbc driver your using,  you might want to change the 
begin to a "begin immediate" causing the lock to be taken out at the begin vs 
later on...

Xuanvinh Vu <[EMAIL PROTECTED]> wrote: 

I did check and I have all the BEGINs and COMMITs. I understand that by default 
the sqlite busy signal is given right away so what I do is I wait till the next 
interval elapses to try to update the DB again by that time whatever the 
transaction that causes the DB to block me before should have been completed. 
These transaction takes between 0.03sec to 1 sec. My fastest thread update at 
every 10 seconds. Here is part of the code.
 
public void UpdateOneHourTable(System.Data.Odbc.OdbcConnection conn, float[] 
counterValues, int startRow)
{

string CommandText = "BEGIN;";
System.Data.Odbc.OdbcCommand command = new 
System.Data.Odbc.OdbcCommand(CommandText, conn);
command.ExecuteNonQuery();
 
int rowId = startRow;
for (int x = 0; x < counterValues.Length; x++)
{

CommandText = "UPDATE OneHour SET DateTime=datetime('now','localtime'), Value=" 
+ counterValues[x].ToString() + " WHERE RowId=" + rowId.ToString() + ";";
command.CommandText = CommandText;
command.ExecuteNonQuery();
rowId++;
}
 
command.CommandText = "COMMIT;";
command.ExecuteNonQuery();
}
 
 

public void UpdateOneDayTable(System.Data.Odbc.OdbcConnection conn)
{

string CommandText = "BEGIN;";
System.Data.Odbc.OdbcCommand command = new 
System.Data.Odbc.OdbcCommand(CommandText, conn);
command.ExecuteNonQuery();
 
//Delete any entries that are over one day old.
CommandText = "DELETE FROM OneDay WHERE DateTime < 
datetime('now','localtime','-24 hours') OR DateTime > 
datetime('now','localtime') OR Value < 0;";
command.CommandText = CommandText;
command.ExecuteNonQuery();
 
//Get the average values in the past 5mins from each individual counter in 
OneHour table and put it in the OneDay table.
CommandText = "INSERT INTO OneDay SELECT CounterId, 
datetime('now','localtime'), avg(Value) FROM OneHour WHERE DateTime >= 
datetime('now','localtime','-5 minutes') AND Value >= 0 GROUP BY CounterId;";
command.CommandText = CommandText;
 
command.ExecuteNonQuery();
command.CommandText = "COMMIT;";
command.ExecuteNonQuery();
}
_
In a rush?  Get real-time answers with Windows Live Messenger.
http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_Refresh_realtime_042008
___
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] [C] Getting results doesn't work properly

[Igor Tandetnik]

"Severin Müller" <[EMAIL PROTECTED]> wrote:
> Hi Folks
>
> I have the following C funtion:
>
> void get_acc(char *src,int ac,char **av)
> {
>char *buf = "SELECT accmask FROM testtable;");
> sqlite3 *db;
> struct sqlite3_stmt *oStmt;
> int rc;
> if(sqlite3_prepare_v2(db,buf,strlen(buf),,NULL)==SQLITE_OK)

You are using uninitialized db variable. You need to open the database 
connection first with sqlite3_open.

> {
> while(sqlite3_step(oStmt)==SQLITE_ROW)
> {
> sqlite3_step(oStmt);

You call sqlite3_step twice on every iteration. Which means you only 
look at every other row.

> txt = (char*)sqlite3_column_text(oStmt,0);

txt is never declared.

> sqlite3_reset(oStmt);
> sqlite3_finalize(oStmt);
> sqlite3_close(db);

You finalize the statement and close the databse after reading just one 
row. Don't you want to enumerate all rows?

Igor Tandetnik 



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

Re: [sqlite] [C] Getting results doesn't work properly

[Clark Christensen]

Well, I'm no C programmer, so I might be full of crap, but it looks like you're 
closing the db connection inside your while block (after you get the first 
row's data).  Not sure about the exact usage for reset() and finalize(), but 
they don't seem proper inside a loop like yours.  Last, it looks like you skip 
every odd row by calling step(oStmt) in the while condition, and then again 
inside the block.

 -Clark

- Original Message 
From: Severin Müller <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, March 25, 2008 2:14:33 PM
Subject: [sqlite] [C] Getting results doesn't work properly

Hi Folks

I have the following C funtion:

void get_acc(char *src,int ac,char **av)
{
   char *buf = "SELECT accmask FROM testtable;");
sqlite3 *db;
struct sqlite3_stmt *oStmt;
int rc;
if(sqlite3_prepare_v2(db,buf,strlen(buf),,NULL)==SQLITE_OK)
{
while(sqlite3_step(oStmt)==SQLITE_ROW)
{
sqlite3_step(oStmt);
txt = (char*)sqlite3_column_text(oStmt,0);
sqlite3_reset(oStmt);
sqlite3_finalize(oStmt);
sqlite3_close(db);
printf("Result: %s\n",txt);
}
}
 }

If i call the function, only one row is returned, but there are definately more 
(i check with SELECT COUNT(*)). Did i oversee something? Or what's wrong here?

Thanks again for your help.

Kind regards

Severin
-- 



Pt! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger
___
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] Multithreads access the same database resulting in database is locked error

[Xuanvinh Vu]

I did check and I have all the BEGINs and COMMITs. I understand that by default 
the sqlite busy signal is given right away so what I do is I wait till the next 
interval elapses to try to update the DB again by that time whatever the 
transaction that causes the DB to block me before should have been completed. 
These transaction takes between 0.03sec to 1 sec. My fastest thread update at 
every 10 seconds. Here is part of the code.
 
public void UpdateOneHourTable(System.Data.Odbc.OdbcConnection conn, float[] 
counterValues, int startRow)
{

string CommandText = "BEGIN;";
System.Data.Odbc.OdbcCommand command = new 
System.Data.Odbc.OdbcCommand(CommandText, conn);
command.ExecuteNonQuery();
 
int rowId = startRow;
for (int x = 0; x < counterValues.Length; x++)
{

CommandText = "UPDATE OneHour SET DateTime=datetime('now','localtime'), Value=" 
+ counterValues[x].ToString() + " WHERE RowId=" + rowId.ToString() + ";";
command.CommandText = CommandText;
command.ExecuteNonQuery();
rowId++;
}
 
command.CommandText = "COMMIT;";
command.ExecuteNonQuery();
}
 
 

public void UpdateOneDayTable(System.Data.Odbc.OdbcConnection conn)
{

string CommandText = "BEGIN;";
System.Data.Odbc.OdbcCommand command = new 
System.Data.Odbc.OdbcCommand(CommandText, conn);
command.ExecuteNonQuery();
 
//Delete any entries that are over one day old.
CommandText = "DELETE FROM OneDay WHERE DateTime < 
datetime('now','localtime','-24 hours') OR DateTime > 
datetime('now','localtime') OR Value < 0;";
command.CommandText = CommandText;
command.ExecuteNonQuery();
 
//Get the average values in the past 5mins from each individual counter in 
OneHour table and put it in the OneDay table.
CommandText = "INSERT INTO OneDay SELECT CounterId, 
datetime('now','localtime'), avg(Value) FROM OneHour WHERE DateTime >= 
datetime('now','localtime','-5 minutes') AND Value >= 0 GROUP BY CounterId;";
command.CommandText = CommandText;
 
command.ExecuteNonQuery();
command.CommandText = "COMMIT;";
command.ExecuteNonQuery();
}
_
In a rush?  Get real-time answers with Windows Live Messenger.
http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_Refresh_realtime_042008
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [C] Getting results doesn't work properly

[Severin Müller]

Hi Folks

I have the following C funtion:

void get_acc(char *src,int ac,char **av)
{
   char *buf = "SELECT accmask FROM testtable;");
sqlite3 *db;
struct sqlite3_stmt *oStmt;
int rc; 
if(sqlite3_prepare_v2(db,buf,strlen(buf),,NULL)==SQLITE_OK)
{
while(sqlite3_step(oStmt)==SQLITE_ROW)
{
sqlite3_step(oStmt);
txt = (char*)sqlite3_column_text(oStmt,0);
sqlite3_reset(oStmt);
sqlite3_finalize(oStmt);
sqlite3_close(db);
printf("Result: %s\n",txt);
}
}
 }

If i call the function, only one row is returned, but there are definately more 
(i check with SELECT COUNT(*)). Did i oversee something? Or what's wrong here?

Thanks again for your help.

Kind regards

Severin
-- 



Pt! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multithreads access the same database resulting in database is locked error

[Igor Tandetnik]

Xuanvinh Vu <[EMAIL PROTECTED]> wrote:
> Here is what the error looks like...
>
> [1436] 25 Mar 16:24:24  OnEveryHour: Failure occurred while updating
> the OneMonth table. Exception: System.Data.Odbc.OdbcException: ERROR
> [HY000] database is locked (5)
> What does the error code 5 stands for?

SQLITE_BUSY "The database file is locked"

> Also I started getting the
> errors when I started using transactions with my queries. I have 4
> threads which create their own connections to the DB. One thread
> access the DB every 10 seconds, another every 5 minutes, another
> every hour and another every day. Even if the database is locked
> because of one thread shouldnt it starts working again once the
> database is done with that transaction?

Yes, if you wait for the lock to clear. See sqlite3_busy_timeout. The 
default timeout is zero meaning you get SQLITE_BUSY right away.

Or, you can just retry the failed request manually after waiting for 
some time.

> I'm doing all this from
> inside a windows service. I found that once I started seeing the
> errors it does not eventually correct itself like I'm expecting it
> to, but I have to restart the service for it to work.

Check your code, make sure all your BEGIN's are balanced by COMMIT's and 
you don't accidentally leave a transaction open. Also check that you 
close all cursors you use to read the results of the SELECT statements 
(e.g. you should call Close on OdbcDataReader once you are done with the 
resultset).

Igor Tandetnik 



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

[sqlite] Multithreads access the same database resulting in database is locked error

[Xuanvinh Vu]

Here is what the error looks like...
 
[1436] 25 Mar 16:24:24  OnEveryHour: Failure occurred while updating the 
OneMonth table. Exception: System.Data.Odbc.OdbcException: ERROR [HY000] 
database is locked (5) 
What does the error code 5 stands for? Also I started getting the errors when I 
started using transactions with my queries. I have 4 threads which create their 
own connections to the DB. One thread access the DB every 10 seconds, another 
every 5 minutes, another every hour and another every day. Even if the database 
is locked because of one thread shouldnt it starts working again once the 
database is done with that transaction? I'm doing all this from inside a 
windows service. I found that once I started seeing the errors it does not 
eventually correct itself like I'm expecting it to, but I have to restart the 
service for it to work.
 
-Vin
_
Windows Live Hotmail is giving away Zunes.
http://www.windowslive-hotmail.com/ZuneADay/?locale=en-US=TXT_TAGLM_Mobile_Zune_V3
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECTing calls < last two weeks?

[Gilles Ganault]

On Tue, 25 Mar 2008 11:09:55 -0400, "Derrell Lipman"
<[EMAIL PROTECTED]>
wrote:
>http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

Thanks guys. For those interested, here's a way to do it:

$sql = "SELECT * FROM calls ";
$sql .= "WHERE (julianday('now') - julianday(calls_date)) < 15";

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

Re: [sqlite] resetting a column back to its default value

[P Kishor]

On 3/25/08, Alex Katebi <[EMAIL PROTECTED]> wrote:
> Hi,
>
>  I was woundering how I can update a column in my table back to its default
>  value.
>
>  For example:
>
>  create table t1 (value integer default 55, name text);
>  insert into t1(name) values('hello');
>  update t1 set value=default;  /* for illustration only */

you do realize that the above update will try to update the value of
the column 'value' to the value of a mysterious column called
'default'. Still, going on...

>
>  How can I achive the desired behavior short of doing "update t1 set
>  value=55"
>

insert into t1(name) values('hello');

automatically sets the value of the column called 'value' to 55, so
your question is answered by exactly what you are doing.


>  Thanks,
>  -Alex


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

[sqlite] resetting a column back to its default value

[Alex Katebi]

Hi,

I was woundering how I can update a column in my table back to its default
value.

For example:

create table t1 (value integer default 55, name text);
insert into t1(name) values('hello');
update t1 set value=default;  /* for illustration only */

How can I achive the desired behavior short of doing "update t1 set
value=55"

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

Re: [sqlite] how can I find source of sqlite V 2.x

[Dennis Cote]

Russell wrote:
> 
> My project had to use Qt 3.3.8, which supports sqlite V2 only. How
> can I get the source of V2? 
> 

You can use the CVS repository as described at the bottom of the 
download page http://www.sqlite.org/download.html

Or you can modify the links shown on the download page. For example the 
preprocessed source for version 2.8.17 can be had by modifying the link 
for the current version http://www.sqlite.org/sqlite-source-3_5_7.zip to 
be http://www.sqlite.org/sqlite-source-2_8_17.zip. Enter that in your 
browser and it will start the download.

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

Re: [sqlite] Issue with LEFT JOIN, index, and literal in ON clause

[Dennis Cote]

Daniel Ring wrote:
> The following behavior when using LEFT OUTER JOIN with an indexed column
> and literals in the ON clause is, I'm pretty sure, wrong.  SQLite seems
> to convert the join to an INNER JOIN in some cases.
> 
> The capture is from SQLite 3.4.2, but I get the same results with 3.5.7.
>  I also copied the raw SQL at the end for your copy-and-pasting pleasure.
> 
> SQLite version 3.4.2
> Enter ".help" for instructions
> sqlite>
> sqlite> CREATE TABLE "aaa" (
>...>   'id' integer NOT NULL PRIMARY KEY,
>...>   'o_id' integer UNSIGNED NOT NULL,
>...>   'o_type' integer NOT NULL
>...> );
> sqlite>
> sqlite> CREATE TABLE 'bbb' (
>...>   'id' integer NOT NULL PRIMARY KEY
>...> );
> sqlite>
> sqlite> CREATE TABLE 'ccc' (
>...>   'id' integer NOT NULL PRIMARY KEY
>...> );
> sqlite>
> sqlite> INSERT INTO aaa ('id', 'o_id', 'o_type') VALUES (1, 3, 1);
> sqlite> INSERT INTO aaa ('id', 'o_id', 'o_type') VALUES (2, 4, 2);
> sqlite>
> sqlite> INSERT INTO bbb ('id') VALUES (3);
> sqlite> INSERT INTO ccc ('id') VALUES (4);
> sqlite>
> sqlite> SELECT aaa.id, bbb.id, ccc.id
>...>   FROM aaa
>...> LEFT OUTER JOIN bbb
>...>   ON (aaa.o_id=bbb.id AND aaa.o_type=1)
>...> LEFT OUTER JOIN ccc
>...>   ON (aaa.o_id=ccc.id AND aaa.o_type=2);
> 1|3|
> 2||4
> sqlite>
> 
> This is correct, but add an index and...
> 
> sqlite> CREATE INDEX 'aaa_o_type' ON 'aaa' ('o_type');
> sqlite>
> sqlite> SELECT aaa.id, bbb.id, ccc.id
>...>   FROM aaa
>...> LEFT OUTER JOIN bbb
>...>   ON (aaa.o_id=bbb.id AND aaa.o_type=1)
>...> LEFT OUTER JOIN ccc
>...>   ON (aaa.o_id=ccc.id AND aaa.o_type=2);
> 1|3|
> sqlite>
> 
> I expect the second SELECT to produce the same results as the first
> (presumably faster).
> 

This is a bug and you should create a ticket at 
http://www.sqlite.org/cvstrac/tktnew so that it gets addressed. Adding 
an index should never change the result of a query.

As for a workaround; What about simply dropping the index? In other 
words are you sure the correct version without the index is too slow?

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

[sqlite] Test insert4-2.4.1 in insert4.test

[Noah Hart]

I don't have TCL, so I'm trying to validate sqlite by running some of
the tests by hand.

In insert4.test, there is a sub-test 

do_test insert4-2.4.1 {
  execsql {
DELETE FROM t3;
INSERT INTO t3 SELECT DISTINCT * FROM t2;
SELECT * FROM t3;
  }
} {9 1 1 9}


I can run this test by hand, using the following commands 
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> CREATE TABLE t2(x int, y int);
sqlite> CREATE TABLE t3(a int, b int);
sqlite> INSERT INTO t2 VALUES(9,1);
sqlite> INSERT INTO t2 SELECT y, x FROM t2;
sqlite> SELECT * FROM t2;
9|1
1|9
sqlite>
sqlite> INSERT INTO t3 SELECT DISTINCT * FROM t2;
sqlite> SELECT * FROM t3;
1|9
9|1

Notice the difference in the ordering of the rows.

When I run the same INSERT without the DISTINCT keyword, I get the
expected results

sqlite> DELETE FROM T3;
sqlite> INSERT INTO t3 SELECT * FROM t2;
sqlite> SELECT * FROM t3;
9|1
1|9


So, I have 2 questions:
1) Does this test work when running from tcl?
2) If so, what is different?

Regards,

Noah



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


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

Re: [sqlite] Problems Compiling Sqlite

[Dennis Cote]

Jus GoodFun wrote:
> 
> I develop in 'C' within the Windows version of the Eclipse
> Environement producing embedded systems focused on the Motorola
> ColdFire Processors specifically the MCF5282.
> 
> The target operating system is The uC/OS which is a preemptive
> multitasking RTOS.
> 

You will probably need to start with the preprocessed source and 
implement your own OS support files. The SQLite core supports unix, 
windows, and OS2. You will have to write your own support modules for 
uC/OS.

You can get the preprocessed source files at 
http://www.sqlite.org/sqlite-source-3_5_7.zip You should probably start 
by looking at the source files os.h and mutex.h

If you compile the amalgamation with SQLITE_MUTEX_APPDEF defined, then 
the builtin mutex code for all the supported OS's are disabled, and you 
will have to supply suitable functions for SQLite to call. If you don't 
need mutual exclusion (i.e. you are writing multithreaded code), then 
you can define SQLITE_MUTEX_NOOP to included a set of macros that 
effectively remove all mutex calls from SQLite.

HTH
Dennis Cote

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

Re: [sqlite] SQL error: SQL logic error or missing database

[P Kishor]

On 3/25/08, Prabowo Murti <[EMAIL PROTECTED]> wrote:
> BanuPrakash G wrote:
>  > Hi,
>  >
>  >  I have used the latest verision of sqlite i.e. sqlite-3.5.7
>  > Amalgamation release
>  >  for PPC Arch.
>  >
>  >  I was able to cross compile it successfull and when i use the db to
>  > create tables
>  >  I see on the SQLITE shell the ERROR message as "SQL error: SQL logic
>  > error or missing database".
>  >
>  >  i.e.
>  >
>  >  ./sqlite3
>  > SQLite version 3.5.7
>  > Enter ".help" for instructions
>  > sqlite> CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));
>  > SQL error: SQL logic error or missing database
>  >
>  >
>  >
>  >  Can anybody point me out where exactly I am wrong ?
>  >
>  >
>  > Thanks
>  > /BP
>  >
>  >
>  >
>  > ___
>  > sqlite-users mailing list
>  > sqlite-users@sqlite.org
>  > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>  >
>  >
>
> Hi
>  You have to create a database first. Create a table before a database is
>  like build a window without house :)
>  I am using sqlite3 on Ubuntu.
>
>  $ sqlite3 mydb.db3
>
>  I am new in sqlite. Cmiiw.
>


Nope, not true at all. On my computer --

[10:29 AM] ~$ sqlite3
SQLite version 3.5.6
Enter ".help" for instructions
sqlite> CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));
sqlite> .s
CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));
sqlite> .q
[10:55 AM] ~$

Not specifying a db just creates the db in memory. Of course, the db
vanishes when you quit out of sqlite3.

The above maybe a 3.5.7 bug, or something to do with the OP's sqlite3 build.

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

Re: [sqlite] SQL error: SQL logic error or missing database

[Prabowo Murti]

BanuPrakash G wrote:
> Hi,
>
>  I have used the latest verision of sqlite i.e. sqlite-3.5.7
> Amalgamation release 
>  for PPC Arch.
>
>  I was able to cross compile it successfull and when i use the db to
> create tables  
>  I see on the SQLITE shell the ERROR message as "SQL error: SQL logic
> error or missing database".
>
>  i.e.
>
>  ./sqlite3
> SQLite version 3.5.7
> Enter ".help" for instructions
> sqlite> CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));
> SQL error: SQL logic error or missing database
>
>
>
>  Can anybody point me out where exactly I am wrong ?
>
>
> Thanks
> /BP
>
>   
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
Hi
You have to create a database first. Create a table before a database is 
like build a window without house :)
I am using sqlite3 on Ubuntu.

$ sqlite3 mydb.db3

I am new in sqlite. Cmiiw.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL error: SQL logic error or missing database

[Ken]

Try  ./sqlite3   

BanuPrakash G <[EMAIL PROTECTED]> wrote: Hi,

 I have used the latest verision of sqlite i.e. sqlite-3.5.7
Amalgamation release 
 for PPC Arch.

 I was able to cross compile it successfull and when i use the db to
create tables  
 I see on the SQLITE shell the ERROR message as "SQL error: SQL logic
error or missing database".

 i.e.

 ./sqlite3
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));
SQL error: SQL logic error or missing database



 Can anybody point me out where exactly I am wrong ?


Thanks
/BP

  

___
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] SQL error: SQL logic error or missing database

[BanuPrakash G]

Hi,

 I have used the latest verision of sqlite i.e. sqlite-3.5.7
Amalgamation release 
 for PPC Arch.

 I was able to cross compile it successfull and when i use the db to
create tables  
 I see on the SQLITE shell the ERROR message as "SQL error: SQL logic
error or missing database".

 i.e.

 ./sqlite3
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));
SQL error: SQL logic error or missing database



 Can anybody point me out where exactly I am wrong ?


Thanks
/BP

  

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

Re: [sqlite] how to reply?

[Dennis Cote]

L B wrote:
> Sorry for this post, but I can't find a way to reply to a post. It seems that 
> I can only reply via email to the person who answered me.
>   How to reply and see my answer on this site?
> 

Your email client must be using the From header rather than the Reply-To 
header field when you select reply. Each message posted by the list has 
the Reply-To header field set to the email address of the mailing list.

What email client program are you using?

Some of the headers for your message that I am replying to were:

Date: Fri, 21 Mar 2008 09:13:44 -0700 (PDT)
From: L B <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
MIME-Version: 1.0
Message-ID: <[EMAIL PROTECTED]>
X-Content-Filtered-By: Mailman/MimeDel 2.1.9
Subject: [sqlite] how to reply?
X-BeenThere: sqlite-users@sqlite.org
X-Mailman-Version: 2.1.9
Precedence: list
Reply-To: General Discussion of SQLite Database 
List-Id: General Discussion of SQLite Database 
List-Unsubscribe: 
,

List-Archive: 
List-Post: 
List-Help: 
List-Subscribe: 
,

Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Sender: [EMAIL PROTECTED]
Errors-To: [EMAIL PROTECTED]


As you can see the Replay-To header is set correctly.

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

Re: [sqlite] SELECTing calls < last two weeks?

[Griggs, Donald]

Regarding:

In MySQL, I used the following to only get calls received in the last
two weeks:

WHERE calls_date > DATE_SUB(CURRENT_DATE, INTERVAL 15 DAY)

Is there an equivalent in SQLite, or should I perform this test in PHP?
===

Hi Giles,

The sqlite date/time functions are documented at:
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

 



This email and any attachments have been scanned for known viruses using 
multiple scanners. We believe that this email and any attachments are virus 
free, however the recipient must take full responsibility for virus checking. 
This email message is intended for the named recipient only. It may be 
privileged and/or confidential. If you are not the named recipient of this 
email please notify us immediately and do not copy it or use it for any 
purpose, nor disclose its contents to any other person.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECTing calls < last two weeks?

[Derrell Lipman]

On Tue, Mar 25, 2008 at 11:02 AM, Gilles Ganault <[EMAIL PROTECTED]> wrote:
> Hello
>
>  In MySQL, I used the following to only get calls received in the last
>  two weeks:
>
> WHERE calls_date > DATE_SUB(CURRENT_DATE, INTERVAL 15 DAY)
>
>  Is there an equivalent in SQLite, or should I perform this test in
>  PHP?

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] paged query in sqlite

[John Stanton]

Do a SELECT, read 100 rows, then read another 100.  Your program 
controls it.

Shailesh Madhukar Birari wrote:
> Does Sqlite support Paged query? what I mean is on doing select* it should
> return me first, say 100, rows and then subsequent calls should return me
> successive rows.
> Is this supported in sqlite:? If yes, what are the interfaces?
> If not, is there an easy way to add this functionality using existing
> interfaces?
> 
> regards.
> Shailesh
> ___
> 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] SELECTing calls < last two weeks?

[Gilles Ganault]

Hello

In MySQL, I used the following to only get calls received in the last
two weeks:

WHERE calls_date > DATE_SUB(CURRENT_DATE, INTERVAL 15 DAY)

Is there an equivalent in SQLite, or should I perform this test in
PHP?

Thank you.

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

Re: [sqlite] Rowid After Sorting

[Dennis Cote]

Mahalakshmi.m wrote:
> 
> I wish to perform all operations in my code with sorted order of the Name
> field.
> 

Then you should add an index on the Name column and use that to process 
your queries in Name order more quickly.

create index on MyTable(Name);

If you really insist on reordering your table, you must copy the data 
somewhere else, empty the table, and reinsert the data in the order you 
want the rowid to present. Note, this will not work if you plan on 
inserting or deleting data after this initial insert. Adding new rows 
will place then at the end and assign a large rowid, regardless of the 
Name.

create temp table t as select Id, Name from MyTable;
delete from MyTable;
insert into MyTable select Id, Name from t order by Name;

> So, to find the index of a name, which is in sorted order, I need the Rowid
> to be changed as shown in case 2 rather than in case 1.
> 

Why do you want to find the index of a Name?

Tables in SQL databases are not arrays. You don't use an index to 
retrieve the data. Tables are more like unordered sets of data.

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

Re: [sqlite] paged query in sqlite

[Emilio Platzer]

another aproach is to use OFFSET and LIMIT in your select queries:

select * from table limit 100 offset 1

then

select * from table limit 100 offset 101

then

select * from table limit 100 offset 201

etc.

Shailesh Madhukar Birari escribió:
> Does Sqlite support Paged query? what I mean is on doing select* it should
> return me first, say 100, rows and then subsequent calls should return me
> successive rows.
> Is this supported in sqlite:? If yes, what are the interfaces?
> If not, is there an easy way to add this functionality using existing
> interfaces?
> 
> regards.
> Shailesh
> ___
> 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] paged query in sqlite

[Igor Tandetnik]

"Shailesh Madhukar Birari"
<[EMAIL PROTECTED]> wrote in
message
news:[EMAIL PROTECTED]
> Does Sqlite support Paged query? what I mean is on doing select* it
> should return me first, say 100, rows and then subsequent calls
> should return me successive rows.

Do a select for all rows. Call sqlite3_step 100 times to get the first 
100 rows. Continue calling it to get the rest of the rows.

See also http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor

Igor Tandetnik 



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

[sqlite] paged query in sqlite

[Shailesh Madhukar Birari]

Does Sqlite support Paged query? what I mean is on doing select* it should
return me first, say 100, rows and then subsequent calls should return me
successive rows.
Is this supported in sqlite:? If yes, what are the interfaces?
If not, is there an easy way to add this functionality using existing
interfaces?

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