[sqlite] Sqllite As server
Dear All, We have an existing Sqlite application which till now was fine running on single machine. Now we need flexibility of client server model. Is this possible with Sqlite ? . Or alternatively is it possible to use Sqlite db file from shared drives. -- With Best Regards, Vishal Kashyap. http://www.vishal.net.in
[sqlite] Need advice/understanding on indices
Thanks for any responses to the following: Imagine a sqlite db with the core table of about 1-2 million rows, total size=about 4 gigs. Want to show a page or so at a time on a web based app. == Table structure == The core table is something like CREATE TABLE Main(ID TEXT,Date INTEGER, Time INTEGER,Subject TEXT, SENDER TEXT, Other Fields that don't matter) which tends to like LEFT JOINING with CREATE TABLE Recips(ID TEXT,Address TEXT, Otherfields that don't matter) and CREATE TABLE Events(ID TEXT, Event TEXT, Otherfields that don't matter) . queries the user needs to make queries on one or more of these fields, sortable by just about any of these fields. = initial indices = This is what I thought was "reasonable" CREATE INDEX idx_core ON Main(ID,Date, Time, Subject, Sender); CREATE INDEX idx_Recipients ON Recips(ID,Address); CREATE INDEX idx_Events ON Events(ID,Event); I found that for many queries (example order by subject), the query was very slow. 20 minutes or so. === I then tried == Adding explicit individual indices (without touching the original ones) CREATE Index idx_date on main(date) CREATE Index idx_subject on main(subject) and found everything speed = So my question is = When is it appropriate to use compound indices? When single indices? Keep in mind here that the specific number of WHERE clauses and ORDER clauses needs to be flexible. It seems from the docs I've seen that compound indices only help you if you have a FROM or ORDER in the exact order of the compound indices (leaving off ones at the end being ok)?
Re: [sqlite] Auto Increment?
> Quoting Dennis Cote ([EMAIL PROTECTED]): > > Doesn't this mean that SQLite only supports 2^63 rows with autoincrement? > > That means you can insert one row per millisecond for 29 million years. Well actually, not quite. The website states that the database size is limited to 2^41 bytes. http://www.sqlite.org/faq.html You need 8 bytes to store the autoincrement field, and let's say you store another 8 bytes of information. So now you've only got 2^37 records minus database overheads, but I don't know how much that is. So you only get about 4 years at 1 record per millisecond! The bigger problem with regard to the initial request is that when records are deleted, the autoincrement field just keeps going up (for 29 million years for argument sake). So really, if you **have** to have 32 bits of Unique ID, then maybe you need to find some other method to manage it. But, as all the prior discussion indicates, it depends on how many updates and for how long. Do the maths, find out how many bits you need. If you're not going to generate more than 2^32 records over the life of the application, then use the inbuilt AUTOINCREMENT field, and don't worry about it. Regards, Carl.
Re: [sqlite] Auto Increment?
Quoting Dennis Cote ([EMAIL PROTECTED]): > Doesn't this mean that SQLite only supports 2^63 rows with autoincrement? That means you can insert one row per millisecond for 29 million years. -- Paul Tomblin <[EMAIL PROTECTED]> http://xcski.com/blogs/pt/ In any business, the customer is always right, except when he calls technical support.
Re: [sqlite] Disk IO error on AIX
[EMAIL PROTECTED] wrote: Robert Tortajada <[EMAIL PROTECTED]> wrote: The bad return from fsync is -1 so I am not sure that will be helpfull. However, couldn't we just disable DIRSYNC since that seems to be the issue? Yeah. Just disable DIRSYNC. This will slightly increase the risk of database corruption following a power failure (the risk is that the journal files name will be lost and the file itself will be moved into /lost+found). But how often does that happen, really? -- D. Richard Hipp <[EMAIL PROTECTED]> That did work, thanks for the help. I also noticed that the sqlite3-ruby interface needs a shared library and libsqlite3.a does not work. However, I copied the libsqlite3.so.0 and renamed it and was able to get that to work as well. Thanks again for the help. Bob T
Re: [sqlite] Auto Increment?
[EMAIL PROTECTED] wrote: The rowid does *not* wrap if you specify AUTOINCREMENT. Once the maximum rowid is used, all subsequent insert attempts return SQLITE_FULL. The regression test suite contains a test for this. Different rules apply if you do not use AUTOINCREMENT. There is a #define that may interest the original poster. If you compile with -DSQLITE_32BIT_ROWID=1, then rowids are limited to 32 bits. This feature is not particularly well tested (actually, I'm not sure it is tested at all) but it might work. It is worth a try, I suppose. Doesn't this mean that SQLite only supports 2^63 rows with autoincrement? If I create a table with a with an autoincrement rowid, and then insert a NULL id it assigns a rowid of 1. And if I try to initialize the rowid to a large negative number (I used 2^63 +1 to avoid the largest negative value) in order to use the other 2^63 rowids it lets me insert that one row, but then automatically assigns the next row an id of 1. sqlite> create table t2(i integer primary key autoincrement, d); sqlite> insert into t2 values((1<<63) + 1, 1); sqlite> insert into t2 values(NULL, 2); sqlite> select * from t2; -9223372036854775807|1 1|2 sqlite> insert into t2 values((1<<63) -1, 3); sqlite> select * from t2; -9223372036854775807|1 1|2 9223372036854775807|3 sqlite> insert into t2 values(NULL, 4); SQL error: database or disk is full sqlite> It won't autoincrement through the negative half of the address space. I don't think this is a real problem because of the immense size of the address space, but someone might. Also, does it behave the same with 32 bit values (i.e. only on half the space or 2^31 rows are usable)? This might be more of a practical problem for someone, but they could always switch to 64 (or 63) values if it was. Dennis Cote
Re: [sqlite] LIKE syntax with BLOBs
[EMAIL PROTECTED] wrote: Shane Baker <[EMAIL PROTECTED]> wrote: I have BLOBs in my schema and the data will often start with bytes of 0 value. I'm having a tough time coming up with the proper SQL syntax to select all the columns that start with 2 0's (or any zeros). I have tried: SELECT * FROM mytable WHERE myblob LIKE 0%; SELECT * FROM mytable WHERE myblob LIKE X'0%'; SELECT * FROM mytable WHERE myblob LIKE X'0'%; and a few other combinations that use double quotes. Is there a LIKE syntax that will work with BLOBs in this way? Thank you very much. SELECT * FROM mytable WHERE myblob LIKE X'0025'; -- D. Richard Hipp <[EMAIL PROTECTED]> Richard, That's very sneaky. Of course it won't be obvious to anyone looking at the SQL why it matches all kinds of strings, people are conditioned to look for the % character. This should do the same thing in a more transparent manner, but it will take longer to execute. SELECT * FROM mytable WHERE quote(myblob) LIKE 'X''00%'; After looking at it, it may not be that much more transparent either. :-) HTH Dennis Cote
Re: [sqlite] Auto Increment?
Dennis Cote <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > > > > > >Hmmm... In the later versions of sqlite with 64-bit ROWID values, doesn't it > >treat them as unsigned? It sure seems that autoincremented rowid values > >should always be positive...??? > > > > > > > No, SQLite treats them as 64 bit signed integers. The first 2^63 values > are positive, the next 2^63 values are negative. The problem I was > alluding to is, that the code that is extracting the range limited > values from SQLite into its own 32 integer values may treat those values > as signed 32 bit integers which will wrap to negative values after 2^31 > rows are inserted. if you are sorting by this id number, suddenly the > newest row has the lowest id not the largest id. SQLite itself will have > the same problem but only after 2^63 rows are inserted (and we don't > have enough time to wait for that to ever happen, its just too large a > number). > The rowid does *not* wrap if you specify AUTOINCREMENT. Once the maximum rowid is used, all subsequent insert attempts return SQLITE_FULL. The regression test suite contains a test for this. Different rules apply if you do not use AUTOINCREMENT. There is a #define that may interest the original poster. If you compile with -DSQLITE_32BIT_ROWID=1, then rowids are limited to 32 bits. This feature is not particularly well tested (actually, I'm not sure it is tested at all) but it might work. It is worth a try, I suppose. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Disk IO error on AIX
Robert Tortajada <[EMAIL PROTECTED]> wrote: > > > The bad return from fsync is -1 so I am not sure that will be helpfull. > However, couldn't we just disable DIRSYNC since that seems to be the issue? > Yeah. Just disable DIRSYNC. This will slightly increase the risk of database corruption following a power failure (the risk is that the journal files name will be lost and the file itself will be moved into /lost+found). But how often does that happen, really? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Auto Increment?
Jim C. Nasby wrote: On Tue, Jan 31, 2006 at 10:05:47AM -0700, Dennis Cote wrote: [EMAIL PROTECTED] wrote: CREATE TABLE x(i INTEGER PRIMARY KEY AUTOINCREMENT CHECK(i < (1<<32))); I suspect you'll see better performance if you hard-code the value instead of doing a bit-shift every time you insert. Jim, You are correct, at least until Richard finishes his constant subexpression folding optimizations. :-) Dennis Cote
Re: [sqlite] Disk IO error on AIX
[EMAIL PROTECTED] wrote:
Robert Tortajada <[EMAIL PROTECTED]> wrote:
The following snipit is where the error is generated.
#ifndef SQLITE_DISABLE_DIRSYNC
if( full_fsync(pFile->dirfd, pFile->fullSync, 0) ){
return SQLITE_IOERR;
OK. Good information. We are narrowing down the problem.
Now, can you please single-step through the full_fsync()
routine and figure out where it is failing. Probably the
call to fsync() is returning some kind of error code.
If fsync() is not working, please check the value of errno,
look up that value in /usr/include/errno.h and let me know
what the problem is.
--
D. Richard Hipp <[EMAIL PROTECTED]>
The bad return from fsync is -1 so I am not sure that will be helpfull.
However, couldn't we just disable DIRSYNC since that seems to be the issue?
Re: [sqlite] Decimal separator
The localization problem is a complex problem. Indeed, any big database system _should_ implement it. And yes, it can be implemented in sqlite, and it can be activated through a PRAGMA directive. But implementing it into sqlite (localization is not limited to numbers) would increase the size of the code, defeating the purpose of having a database with a very small memory footprint. Thus, probably the best solution is to have a standard internal representation (U.S. standard I think is fine, especially that Dr. Hipp is from U.S.) and a different presentation implemented through an optional extension, an extension that wouldn't be carried over by ALL sqlite users. On Tue, 31 Jan 2006, Bert Verhees wrote: Will Leshner wrote: On Jan 31, 2006, at 1:42 PM, Bert Verhees wrote: It is only the English speaking part of the world using Arabic numerals is a '.', And the Japanese speaking world :) Yuo are right, and the South American speaking Spanish also But the South American speaking Portuguese not, the Russian not. The Chinese, I could not check on my Windows. Maybe it is fifty-fifty A pragma taking care for this could help, but, for me I have written my own functions, doing the translation well, it is not an issue. Like Boguslaw, I use Delphi, I use a function like this, I am used to that, for many years, with many databases. Decimalseperator is only a variable in the System-unit (I believe), changing it does not take much of the performance. function Float2Str(f:Float):string var olddec: char; begin olddec := decimalseperator; decimalseperator := '.'; Result := FloatToStr(f); decimalseperator := olddec; end; bert end; -- REALbasic news and tips: http://rbgazette.com KidzMail & KidzBlog: http://haranbanjo.com
Re: [sqlite] Disk IO error on AIX
Robert Tortajada <[EMAIL PROTECTED]> wrote:
> >
> The following snipit is where the error is generated.
>
> #ifndef SQLITE_DISABLE_DIRSYNC
> if( full_fsync(pFile->dirfd, pFile->fullSync, 0) ){
> return SQLITE_IOERR;
>
OK. Good information. We are narrowing down the problem.
Now, can you please single-step through the full_fsync()
routine and figure out where it is failing. Probably the
call to fsync() is returning some kind of error code.
If fsync() is not working, please check the value of errno,
look up that value in /usr/include/errno.h and let me know
what the problem is.
--
D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Decimal separator
Bert Verhees <[EMAIL PROTECTED]> wrote: > It is only the English speaking part of the world using Arabic numerals > is a '.', which is a minority > The rest uses a ',' > SQLite uses "." as the radix point always. This is by design. It used to use the locale specific radix point, but that led to problems. See http://www.sqlite.org/cvstrac/tktview?tn=305 -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Decimal separator
Will Leshner wrote: On Jan 31, 2006, at 1:42 PM, Bert Verhees wrote: It is only the English speaking part of the world using Arabic numerals is a '.', And the Japanese speaking world :) Yuo are right, and the South American speaking Spanish also But the South American speaking Portuguese not, the Russian not. The Chinese, I could not check on my Windows. Maybe it is fifty-fifty A pragma taking care for this could help, but, for me I have written my own functions, doing the translation well, it is not an issue. Like Boguslaw, I use Delphi, I use a function like this, I am used to that, for many years, with many databases. Decimalseperator is only a variable in the System-unit (I believe), changing it does not take much of the performance. function Float2Str(f:Float):string var olddec: char; begin olddec := decimalseperator; decimalseperator := '.'; Result := FloatToStr(f); decimalseperator := olddec; end; bert end; -- REALbasic news and tips: http://rbgazette.com KidzMail & KidzBlog: http://haranbanjo.com
Re: [sqlite] Disk IO error on AIX
[EMAIL PROTECTED] wrote:
Robert Tortajada <[EMAIL PROTECTED]> wrote:
Hello, I am trying to get sqlite to work on AIX 5.3. It seemed to
compile fine and I can access my db file and do selects. However, any
kind of update or create will fail with the following error:
sunjin:/usr/local> ./bin/sqlite3 gg.db
SQLite version 3.3.2
Enter ".help" for instructions
sqlite> CREATE TABLE nodes (name TEXT UNIQUE, grouptype INTEGER);
SQL error: disk I/O error
It is not a file permission issue and in this case it did actually
create a zero byte gg.db file.
Can you run the program in a debugger and set breakpoints on
every line of os_unix.c that might return SQLITE_IOERR. (Use
grep -n to find them). Then tell me which of the various
I/O errors you are hitting.
--
D. Richard Hipp <[EMAIL PROTECTED]>
The following snipit is where the error is generated.
#ifndef SQLITE_DISABLE_DIRSYNC
if( full_fsync(pFile->dirfd, pFile->fullSync, 0) ){
return SQLITE_IOERR;
Re: [sqlite] Decimal separator
On Jan 31, 2006, at 1:42 PM, Bert Verhees wrote: It is only the English speaking part of the world using Arabic numerals is a '.', And the Japanese speaking world :) -- REALbasic news and tips: http://rbgazette.com KidzMail & KidzBlog: http://haranbanjo.com
Re: [sqlite] Decimal separator
Bert Verhees wrote:
Carl Jacobs wrote:
All would be fine but look at this :
create table test(
price double,
amount double default 0
);
insert into test(price) values("12,0");
amount now = 0.0
The world seems to have settled on using Arabic numerals 0, 1, 2 ... 9. I
think we should think about settling on . as the decimal separator, it
would
save a bit of confusion if we all used the same notation.
It is only the English speaking part of the world using Arabic numerals
is a '.', which is a minority
The rest uses a ','
I think we should settle there
;-)
I think a pragma would be a good thing.
I suspect that "12,0" is being stored as a string. Don't forget that
for all
intents and purposes sqlite3 is typless, so it will store your value in
whatever is the most compact form. So, if you want to, you can store a
picture of yourself in field price!
Regards,
Carl.
The problem is that I use not finished wrapper which is not using
sqlite3_bind_double but instead I must pass double/currency as text
which is created based on Windows regional decimal separator.
That way :
select cast(price as double) from test
always return 0.0
not good
I understand that using sqlite3_bind_double would help but I cannot.
Pragma for decimal separator could help in that case and sqlite3 should
automatically convert 12,0 into double and store as double (like when I
use 12.0)
So,it's not so stupid but rather a conversion hell.
Regards
Boguslaw Brandys
RE: [sqlite] Java bindings
Thank you very much! I'll try to compile it also in Linux. If it works, I'm set. If it doesn't, back to square one. On Tue, 31 Jan 2006, Tim Anderson wrote: -Original Message- From: Marian Olteanu [mailto:[EMAIL PROTECTED] Sent: 31 January 2006 05:14 To: sqlite-users@sqlite.org Subject: [sqlite] Java bindings any success. I failed to compile http://www.ch-werner.de/javasqlite and in Windows. I've compiled this for Windows. My build is here: http://www.itwriting.com/sqlite_jni_win.zip (Apologies for missing link in previous message). Note that the author also offers a binary on his site: http://www.ch-werner.de/javasqlite/ Tim Read my tech blog: http://www.itwriting.com/blog
Re: [sqlite] Decimal separator
Carl Jacobs wrote:
All would be fine but look at this :
create table test(
price double,
amount double default 0
);
insert into test(price) values("12,0");
amount now = 0.0
The world seems to have settled on using Arabic numerals 0, 1, 2 ... 9. I
think we should think about settling on . as the decimal separator, it would
save a bit of confusion if we all used the same notation.
It is only the English speaking part of the world using Arabic numerals
is a '.', which is a minority
The rest uses a ','
I think we should settle there
;-)
I think a pragma would be a good thing.
I suspect that "12,0" is being stored as a string. Don't forget that for all
intents and purposes sqlite3 is typless, so it will store your value in
whatever is the most compact form. So, if you want to, you can store a
picture of yourself in field price!
Regards,
Carl.
Re: [sqlite] Decimal separator
> > All would be fine but look at this :
> >
> > create table test(
> > price double,
> > amount double default 0
> > );
> >
> > insert into test(price) values("12,0");
> >
> > amount now = 0.0
The world seems to have settled on using Arabic numerals 0, 1, 2 ... 9. I
think we should think about settling on . as the decimal separator, it would
save a bit of confusion if we all used the same notation.
I suspect that "12,0" is being stored as a string. Don't forget that for all
intents and purposes sqlite3 is typless, so it will store your value in
whatever is the most compact form. So, if you want to, you can store a
picture of yourself in field price!
Regards,
Carl.
Re: [sqlite] Decimal separator
Quoting Bogus?aw Brandys ([EMAIL PROTECTED]):
> All would be fine but look at this :
>
> create table test(
> price double,
> amount double default 0
> );
>
>
> insert into test(price) values("12,0");
>
> amount now = 0.0
Let's see - you insert 12,0 in the column "price", and you're complaining
that the value in a different column, "amount", is the default? WTF?
> Of course it's not critical but maybe new pragma would be useful.
#pragma READ_MY_MIND
--
Paul Tomblin <[EMAIL PROTECTED]> http://xcski.com/blogs/pt/
I got told by a friend's ex-girlfriend that she could tell I was
a Linux geek from the way I *walked*.
-- Skud
Re: [sqlite] Decimal separator
Bogusław Brandys wrote:
Bogusław Brandys wrote:
Hello,
Maybe someone could explain me how to properly store float/decimal
values into sqlite 3.X database ?
I created test table:
create table test(number double);
insert into test(number) values(11);
Now it looks like:
11.0
so, '.' seems to be always decimal separator.
But under my Windows XP decimal separator is ',' and I have problem
retrieving this value using delphi.
Of course I could set DecimalSeparator to '.' , but this require to
use '.' widely in program which is not feasible.
Is there any pragma to set decimal separator it for sqlite database ?
Regards
Boguslaw Brandys
Ok.Found that when store value as text with proper decimal separator and
precision like : 12,0 all is fine so it's rather a problem of formatting
values.
Regards
Boguslaw Brandys
All would be fine but look at this :
create table test(
price double,
amount double default 0
);
insert into test(price) values("12,0");
amount now = 0.0
Of course it's not critical but maybe new pragma would be useful.
Regards
Boguslaw Brandys
Re: [sqlite] Decimal separator
- Original Message - From: "Bogusław_Brandys" <[EMAIL PROTECTED]> Hello, Maybe someone could explain me how to properly store float/decimal values into sqlite 3.X database ? I created test table: create table test(number double); insert into test(number) values(11); Now it looks like: 11.0 so, '.' seems to be always decimal separator. But under my Windows XP decimal separator is ',' and I have problem retrieving this value using delphi. Of course I could set DecimalSeparator to '.' , but this require to use '.' widely in program which is not feasible. Is there any pragma to set decimal separator it for sqlite database ? Whatever tool in Delphi you are using to display the data is probably the culprit. The numbers are stored in the database as numbers, and not as text. The decimal comes from whatever printf() function or parsing function pulled the data out and printed it on the screen. Robert
Re: [sqlite] Decimal separator
Bogusław Brandys wrote: Hello, Maybe someone could explain me how to properly store float/decimal values into sqlite 3.X database ? I created test table: create table test(number double); insert into test(number) values(11); Now it looks like: 11.0 so, '.' seems to be always decimal separator. But under my Windows XP decimal separator is ',' and I have problem retrieving this value using delphi. Of course I could set DecimalSeparator to '.' , but this require to use '.' widely in program which is not feasible. Is there any pragma to set decimal separator it for sqlite database ? Regards Boguslaw Brandys Ok.Found that when store value as text with proper decimal separator and precision like : 12,0 all is fine so it's rather a problem of formatting values. Regards Boguslaw Brandys
[sqlite] Decimal separator
Hello, Maybe someone could explain me how to properly store float/decimal values into sqlite 3.X database ? I created test table: create table test(number double); insert into test(number) values(11); Now it looks like: 11.0 so, '.' seems to be always decimal separator. But under my Windows XP decimal separator is ',' and I have problem retrieving this value using delphi. Of course I could set DecimalSeparator to '.' , but this require to use '.' widely in program which is not feasible. Is there any pragma to set decimal separator it for sqlite database ? Regards Boguslaw Brandys
Re: [sqlite] Intel compiler warnings with 3.3.3
=?iso-8859-1?Q?Miguel_Angel_Latorre_D=EDaz?= <[EMAIL PROTECTED]> wrote: > I will point out only some kind of warnings I saw (i.e. not signed vs > unsigned) using the Intel Compiler: > Thank you for sending the warnings. I patched a few of the warnings. None of the warnings was a real bug. All of these warnings are just your compiler blowing smoke. For many of the warnings in shell.c, I run a significant risk of introducing new bugs in an effort to silence these warnings. And that is a risk I am unwilling to take just to appease an overzealous a compiler. It is better to have working code that generates warnings than broken and buggy code that makes the compiler happy. So many of the warnings mentioned above will remain unfixed for the foreseeable future. > > ..\Sqlite\v3\main.c(33): > const char sqlite3_version[] = SQLITE_VERSION; > > this is only used in func.c, but it could use sqlite3_libversion. Why > publish both of them? I would leave the sqlite3_version[] static in main.c > The sqlite3_version[] global variable is part of the published API and cannot be removed without breaking backwards compatibility. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Intel compiler warnings with 3.3.3
I will point out only some kind of warnings I saw (i.e. not signed vs
unsigned) using the Intel Compiler:
os_win.c
.\Sqlite\v3\os_win.c(1482): warning #300: const variable "zeroData" requires
an initializer
static const ThreadData zeroData;
^
the patch was done in os_unix but not here.
vdbe.c
.\Sqlite\v3\vdbe.c(3818): remark #1599: declaration hides variable "rc"
(declared at line 396)
int res, rc;
^
shell.c
.\Sqlite\v3\shell.c(250): remark #1418: external definition with no prior
declaration. Can be made static.
char *modeDescr[MODE_NUM_OF] = {
^
.\Sqlite\v3\shell.c(914): remark #1599: declaration hides variable "i"
(declared at line 850)
int i;
^
.\Sqlite\v3\shell.c(1023): remark #1599: declaration hides variable "rc"
(declared at line 853)
int rc; /* Result code */
^
.\Sqlite\v3\shell.c(1026): remark #1599: declaration hides variable "i"
(declared at line 850)
int i, j; /* Loop counters */
^
.\Sqlite\v3\shell.c(1029): remark #1599: declaration hides parameter "zLine"
(declared at line 849)
char *zLine;/* A single line of input from the file */
^
.\Sqlite\v3\shell.c(1232): remark #1599: declaration hides variable "i"
(declared at line 850)
int i;
^
.\Sqlite\v3\shell.c(1292): remark #1599: declaration hides variable "i"
(declared at line 850)
int i;
^
.\Sqlite\v3\shell.c(1314): remark #1599: declaration hides variable "rc"
(declared at line 853)
int nRow, rc;
^
.\Sqlite\v3\shell.c(1346): remark #1599: declaration hides variable "i"
(declared at line 850)
int i, j;
^
.\Sqlite\v3\shell.c(1456): remark #1599: declaration hides variable "rc"
(declared at line 1447)
int rc = do_meta_command(zLine, p);
^
.\Sqlite\v3\shell.c(1621): remark #1418: external definition with no prior
declaration. Can be made static.
void main_init(struct callback_data *data) {
^
.\Sqlite\v3\shell.c(1693): warning #266: function declared implicitly. This
"no include header" was always missing (since version 2.x).
if( access(data.zDbFilename, 0)==0 ){
^
Also:
main.c
.\Sqlite\v3\main.c(32): remark #1418: external definition with no prior
declaration
const char rcsid3[] = "@(#) \044Id: SQLite version " SQLITE_VERSION " $";
^
This rcsid3 is not found/used anywhere else.
.\Sqlite\v3\main.c(33):
const char sqlite3_version[] = SQLITE_VERSION;
this is only used in func.c, but it could use sqlite3_libversion. Why
publish both of them? I would leave the sqlite3_version[] static in main.c
Re: [sqlite] Auto Increment?
On Tue, Jan 31, 2006 at 10:05:47AM -0700, Dennis Cote wrote: > [EMAIL PROTECTED] wrote: > CREATE TABLE x(i INTEGER PRIMARY KEY AUTOINCREMENT CHECK(i < (1<<32))); I suspect you'll see better performance if you hard-code the value instead of doing a bit-shift every time you insert. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] Disk IO error on AIX
Robert Tortajada <[EMAIL PROTECTED]> wrote: > Hello, I am trying to get sqlite to work on AIX 5.3. It seemed to > compile fine and I can access my db file and do selects. However, any > kind of update or create will fail with the following error: > > sunjin:/usr/local> ./bin/sqlite3 gg.db > SQLite version 3.3.2 > Enter ".help" for instructions > sqlite> CREATE TABLE nodes (name TEXT UNIQUE, grouptype INTEGER); > SQL error: disk I/O error > > It is not a file permission issue and in this case it did actually > create a zero byte gg.db file. > Can you run the program in a debugger and set breakpoints on every line of os_unix.c that might return SQLITE_IOERR. (Use grep -n to find them). Then tell me which of the various I/O errors you are hitting. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] LIKE syntax with BLOBs
Shane Baker <[EMAIL PROTECTED]> wrote: > I have BLOBs in my schema and the data will often start with bytes of 0 > value. > > I'm having a tough time coming up with the proper SQL syntax to select all > the columns that start with 2 0's (or any zeros). > > I have tried: > > SELECT * FROM mytable WHERE myblob LIKE 0%; > > SELECT * FROM mytable WHERE myblob LIKE X'0%'; > > SELECT * FROM mytable WHERE myblob LIKE X'0'%; > > and a few other combinations that use double quotes. > > Is there a LIKE syntax that will work with BLOBs in this way? Thank you > very much. > SELECT * FROM mytable WHERE myblob LIKE X'0025'; -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Disk IO error on AIX
Hello, I am trying to get sqlite to work on AIX 5.3. It seemed to compile fine and I can access my db file and do selects. However, any kind of update or create will fail with the following error: sunjin:/usr/local> ./bin/sqlite3 gg.db SQLite version 3.3.2 Enter ".help" for instructions sqlite> CREATE TABLE nodes (name TEXT UNIQUE, grouptype INTEGER); SQL error: disk I/O error It is not a file permission issue and in this case it did actually create a zero byte gg.db file. The sqlite3 file is small (86k)and I have the LIPPATH set to point to the libraries. I had a large binary (1.3MB) sqlite3 from version 3.1.3 wiich ran fine. but I needed the libraries for Ruby. Any advice, Thanks Bob T
Re: [sqlite] Auto Increment?
Dennis Cote <[EMAIL PROTECTED]> writes: > Derrell, > > If you are using SQLite 3.3.0 or newer then you can do the same thing in a > more direct manner using a CHECK constraint. > > CREATE TABLE x(i INTEGER PRIMARY KEY AUTOINCREMENT CHECK(i < (1<<32))); Hehe. I'm using 2.8.16 for most of my work, so I don't even have the AUTOINCREMENT keyword available, let alone CHECK. > Also, if you are concerned about signed vs unsigned interpretation of the 32 > bit value you may want to change the maximum to a 31 bit shift which will > restrict i to values that are always positive signed values. Hmmm... In the later versions of sqlite with 64-bit ROWID values, doesn't it treat them as unsigned? It sure seems that autoincremented rowid values should always be positive...??? Cheers, Derrell
[sqlite] LIKE syntax with BLOBs
I have BLOBs in my schema and the data will often start with bytes of 0 value. I'm having a tough time coming up with the proper SQL syntax to select all the columns that start with 2 0's (or any zeros). I have tried: SELECT * FROM mytable WHERE myblob LIKE 0%; SELECT * FROM mytable WHERE myblob LIKE X'0%'; SELECT * FROM mytable WHERE myblob LIKE X'0'%; and a few other combinations that use double quotes. Is there a LIKE syntax that will work with BLOBs in this way? Thank you very much. Shane
Re: [sqlite] How can i see if an query uses an index ?
Jon Friis wrote: Hi all I have the following table + index CREATE TABLE O_YDLRK_CK91_HIST ( ISIN TEXT NULL, BOERS_DATODATE NOT NULL, TERM_DATO DATE NOT NULL, AFDRAG_BELOEB REAL NULL, RENTE_BELOEB REAL NULL, CONSTRAINT XPKO_YDLRK_CK91_HIST PRIMARY KEY (ISIN,BOERS_DATO,TERM_DATO) ); CREATE INDEX XIE1O_YDLRK_CK91_HIST ON O_YDLRK_CK91_HIST(BOERS_DATO); with aprx. 5.000.000 rows. I do the following query against the table Select a.Boers_Dato,a.Isin,a.Term_dato,a.Afdrag_Beloeb,a.Rente_Beloeb from O_YDLRK_CK91_HIST a,( select Isin as FK,Term_dato as tm, max(Boers_Dato) as p from O_YDLRK_CK91_HIST where Isin='DK0009253064' and Boers_Dato<='2004-10-01' group by Isin,Term_dato) b where a.Isin=b.FK and a.Term_dato=b.tm and a.Boers_Dato=b.p and a.Term_dato>='2004-10-01' Order by a.Isin, a.Term_dato, a.Boers_Dato And have some problems with execution times. This takes aprx. 5 minutes to get an answer - which is longer than i have expected (compared to other databases) So how can i see if it really uses the index or not ??? Any other performance enhancement / efficient rewriting tips are appreciated - since i am a newbie to SQLite. Thanx in advance Jon Friis Jon, You can use the "explain query plan" command to display the tables and indexes that are used to implement a query. Try this: explain query plan Select a.Boers_Dato,a.Isin,a.Term_dato,a.Afdrag_Beloeb,a.Rente_Beloeb from O_YDLRK_CK91_HIST a,( select Isin as FK,Term_dato as tm, max(Boers_Dato) as p from O_YDLRK_CK91_HIST where Isin='DK0009253064' and Boers_Dato<='2004-10-01' group by Isin,Term_dato) b where a.Isin=b.FK and a.Term_dato=b.tm and a.Boers_Dato=b.p and a.Term_dato>='2004-10-01' Order by a.Isin, a.Term_dato, a.Boers_Dato HTH Dennis Cote
Re: [sqlite] Auto Increment?
[EMAIL PROTECTED] wrote: chetana bhargav <[EMAIL PROTECTED]> writes: Auto increment seems to return a unsigned long long is there any way for it to make it as 32 bit, as I am depending on this feilds to generate unique id, and i have a constraint fot the id to be 32 bit only. You'll have to add enough rows to the table to use up all id values that fit in 32 bits before you'll have a problem. You can, however, protect from wrap- around with something like this: CREATE TABLE x(i INTEGER PRIMARY KEY AUTOINCREMENT); CREATE TRIGGER x_insert_tr AFTER INSERT ON x FOR EACH ROW BEGIN SELECT CASE WHEN new.i >= (1<<32) THEN RAISE(ROLLBACK, 'The table is full.') ELSE NULL END; END; Derrell Derrell, If you are using SQLite 3.3.0 or newer then you can do the same thing in a more direct manner using a CHECK constraint. CREATE TABLE x(i INTEGER PRIMARY KEY AUTOINCREMENT CHECK(i < (1<<32))); Also, if you are concerned about signed vs unsigned interpretation of the 32 bit value you may want to change the maximum to a 31 bit shift which will restrict i to values that are always positive signed values. HTH Dennis Cote
Re: [sqlite] Sqlite powered Web Sites
On Tue, 31 Jan 2006, Clint Bailey wrote: Can anyone point me to web sites that are powered by Sqlite? I'm curious as to how they function as compared to a MySQL, or brand M$ powered site. check out the ruby on rails list - their are a few rails sites out there using sqlite. -a -- happiness is not something ready-made. it comes from your own actions. - h.h. the 14th dali lama
Re: [sqlite] Sqlite powered Web Sites
Clint Bailey said: > Can anyone point me to web sites that are powered by Sqlite? I'm curious > as to how they function as compared to a MySQL, or brand M$ powered site. http://www.ceamus.com Of course, you aren't going to see the guts of its SQLite access from this perspective. But as a regular user of my own product, I assure you that the performance is excellent. Clay -- Simple Content Management http://www.ceamus.com
Re: [sqlite] Sqlite powered Web Sites
Clint Bailey <[EMAIL PROTECTED]> wrote: > Can anyone point me to web sites that are powered by Sqlite? I'm curious > as to how they function as compared to a MySQL, or brand M$ powered site. > http://www.sqlite.org/ is built on top of CVSTrac (http://www.cvstrac.org/) which uses SQLite version 2.8. If you check IP addresses, you will also note that http://www.sqlite.org/ and http://www.cvstrac.org/ are in fact the same machine. Some other sites running on the same machine include: http://3dcanvas.tcl.tk/ http://tkhtml.tcl.tk/ All these sites are running on a single virtual machine leased from Linode (http://www.linode.com/), specifically a Linode 160 which is roughly equivalent to a standalone 200Mhz (to be clear: 0.2GHz) processor with 160MiB of RAM. The OS is debian. The SQLite website handles around 50K hits/day from around 5K unique IP addresses. Total traffic is a little over 50GiB per month. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Sqlite powered Web Sites
Цитат на писмо от Clint Bailey <[EMAIL PROTECTED]>: > Can anyone point me to web sites that are powered by > Sqlite? I'm curious > as to how they function as compared to a MySQL, or brand > M$ powered site. > > > yes - www.sqlite.org - Slon.bg ™ Симпатичният магазин за книги, DVD, игри и музика http://www.slon.bg
Re: [sqlite] Sqlite powered Web Sites
Clint Bailey <[EMAIL PROTECTED]> writes: > Can anyone point me to web sites that are powered by Sqlite? I'm curious as > to how they function as compared to a MySQL, or brand M$ powered site. How about http://sqlite.org ? Derrell
Re: [sqlite] Fwd: Does SQLite provide XML support
pavan savoy said: > #db2xml test.db test.xml > > which converts test.db database to XML format. I dont really care abt DTD, > stuff and all. I just want it to be converted. Thats it ... It really depends on what you want to find in that XML file. I already have a tool at http://www.ceamus.com/objbuilder/ which will render an XML file describing the schema of the database. It would be a pretty straight forward matter to generate an XML file with the contents as well, but I don't know about such a beast. For filthy lucre I would be more than happy to make such a tool. Clay Dowling -- Simple Content Management http://www.ceamus.com
[sqlite] Sqlite powered Web Sites
Can anyone point me to web sites that are powered by Sqlite? I'm curious as to how they function as compared to a MySQL, or brand M$ powered site.
Re: [sqlite] Auto Increment?
chetana bhargav <[EMAIL PROTECTED]> writes: > Auto increment seems to return a unsigned long long is there any way for it > to make it as 32 bit, as I am depending on this feilds to generate unique > id, and i have a constraint fot the id to be 32 bit only. You'll have to add enough rows to the table to use up all id values that fit in 32 bits before you'll have a problem. You can, however, protect from wrap- around with something like this: CREATE TABLE x(i INTEGER PRIMARY KEY AUTOINCREMENT); CREATE TRIGGER x_insert_tr AFTER INSERT ON x FOR EACH ROW BEGIN SELECT CASE WHEN new.i >= (1<<32) THEN RAISE(ROLLBACK, 'The table is full.') ELSE NULL END; END; Derrell
[sqlite] Fwd: Does SQLite provide XML support
-- Forwarded message -- From: pavan savoy <[EMAIL PROTECTED]> Date: Jan 31, 2006 7:17 PM Subject: Does SQLite provide XML support To: [EMAIL PROTECTED] Hi, I searched through your FAQ's mailing list, but the answer nor the question is not this simple. Hence I just want to know is there a tool where I can just say #db2xml test.db test.xml which converts test.db database to XML format. I dont really care abt DTD, stuff and all. I just want it to be converted. Thats it ... Thank you
Re: [sqlite] Database Locked Error
I've tried to copy/paste and simplify the code.
Please assume that the sql query on line #19 is correct, its actually
longer so i truncated it. getDB() returns _db.
I checked the value of _db after the sqlite3_open() fn call its a valid
handle.
I'm trying to understand fuser and lsof commands and will get back to
you.
Thanks for you help. If you have any other suggestions then please do
let me know
Thanks
Ritesh
On Tue, 2006-01-31 at 18:02, Christian Smith wrote:
> On Tue, 31 Jan 2006, Ritesh Kapoor wrote:
>
> >Regarding the configuration of NFS -
> >I have two machines with NFS on them.
> >- if i run the app on machine 1 it works properly
> >- now when i run the app on machine 2 it works properly
> >
> >But if I login to machine 2 from machine 1 and then run the app I get
> >the 'database is locked' error message.
>
>
> I assume this is using an NFS file from machine 2 from machine 1?
>
>
> >
> >The sequence of statements are -
> >
> >1 if((fp = fopen(fullFileName.c_str(),"w+")))
> >2 {
> >3 fclose(fp);
> >4 }
> >5 else
> >6 {
> >7 printf("Error creating file");
> >8 return;
> >9 }
>
>
> This bit is unnecessary. SQLite will create a file if it does not exist.
>
>
> >10 int res = sqlite3_open(fullFileName.c_str(), &_db);
> >11 if( res != SQLITE_OK )
> >12 {
> >13 printf("Error opending DB");
> >14 sqlite3_close(_db);
>
>
> _db will not be a valid handle if sqlite3_open failed.
>
>
> >15 return;
> >16 }
> >17
> >18 sqlite3_trace(getDB(),logSqlQuery,);
>
>
> getDB()? Does this return _db?
>
>
> >19 string query = "create table XYZ";
> >20 res = sqlite3_exec(_db, query.c_str(), NULL , 0, );
>
>
> This example won't work as "create table XYZ" isn't a valid complete SQL
> statement.
>
>
> >21
> >22 if(res == 5)
> >23
> >
> >
> >After line #20 the value of 'res' is 5.
> >
> >I've even tried to run the app after removing line #18 which contains
> >the sqlite3_trace() fn call. But that dosen't seem to be the cause.
> >
> >I've gone through the mail archive of this list and saw that this
> >problem did appear for others as well but there the solution was to
> >check if a previously executing sql statement was still not finished
> >with its job and a another sql statement was being executed.
> >
> >Over here the first sql statement's execution returns this error.
>
>
> Could be that the incomplete statement is causing problems.
>
>
> >
> >Another assumption that I made was that the host machine locks the file
> >after I open a sqlite connection (line #10). So I should wait for it to
> >release this lock. I ran the app using GDB and after line #10 I paused
> >the app for some time hoping that the lock would go away but the problem
> >persists even after pausing for 10, 20 and 30 minutes in seperate runs.
> >
> >
> >If NFS is the problem then what configurations do I need to look at and
> >change to make my app work properly?
>
>
> Locking is handled by rpc.lockd(8). It should be started by default.
>
>
> >
> >What if the problem is with the host machine locking the file or some
> >other process running on my machine or the host machine which locks the
> >file? Is this possible?
>
>
> Possible. Check your on both machines if the file is in use.
>
>
> >
> >If yes then is there a way to find out which process is doing this
> >through the entries in /proc directory?
> >
>
>
> If on Linux, try using fuser(1) to see if other processes have the file
> open, and lsof(8) to see if the file has locks associated with it.
>
>
> >
> >Thanks,
> >Ritesh
> >
> >
> >
> >On Mon, 2006-01-30 at 19:31, [EMAIL PROTECTED] wrote:
> >> Ritesh Kapoor <[EMAIL PROTECTED]> wrote:
> >> > Yes.
> >> > My machine has NFS and the machines I log onto also have NFS. But if
> >> > this is the problem then why dosen't it appear on my machine when I run
> >> > the app.
> >>
> >> Perhaps you are using a local filesystem when you run on
> >> your machine. Or perhaps NFS is configured properly on
> >> your machine but not on the other machines.
> >>
> >>
> >> > Is there a workaround for this? without having to change the file system
> >> > from NFS.
> >> >
> >>
> >> Yes. Configure your NFS so that file locking works correctly.
> >>
> >> --
> >> D. Richard Hipp <[EMAIL PROTECTED]>
> >>
> >
> >
Re: [sqlite] Database Locked Error
On Tue, 31 Jan 2006, Ritesh Kapoor wrote:
>Regarding the configuration of NFS -
>I have two machines with NFS on them.
>- if i run the app on machine 1 it works properly
>- now when i run the app on machine 2 it works properly
>
>But if I login to machine 2 from machine 1 and then run the app I get
>the 'database is locked' error message.
I assume this is using an NFS file from machine 2 from machine 1?
>
>The sequence of statements are -
>
>1 if((fp = fopen(fullFileName.c_str(),"w+")))
>2 {
>3 fclose(fp);
>4 }
>5 else
>6 {
>7 printf("Error creating file");
>8 return;
>9 }
This bit is unnecessary. SQLite will create a file if it does not exist.
>10 int res = sqlite3_open(fullFileName.c_str(), &_db);
>11 if( res != SQLITE_OK )
>12 {
>13 printf("Error opending DB");
>14 sqlite3_close(_db);
_db will not be a valid handle if sqlite3_open failed.
>15 return;
>16 }
>17
>18 sqlite3_trace(getDB(),logSqlQuery,);
getDB()? Does this return _db?
>19 string query = "create table XYZ";
>20 res = sqlite3_exec(_db, query.c_str(), NULL , 0, );
This example won't work as "create table XYZ" isn't a valid complete SQL
statement.
>21
>22 if(res == 5)
>23
>
>
>After line #20 the value of 'res' is 5.
>
>I've even tried to run the app after removing line #18 which contains
>the sqlite3_trace() fn call. But that dosen't seem to be the cause.
>
>I've gone through the mail archive of this list and saw that this
>problem did appear for others as well but there the solution was to
>check if a previously executing sql statement was still not finished
>with its job and a another sql statement was being executed.
>
>Over here the first sql statement's execution returns this error.
Could be that the incomplete statement is causing problems.
>
>Another assumption that I made was that the host machine locks the file
>after I open a sqlite connection (line #10). So I should wait for it to
>release this lock. I ran the app using GDB and after line #10 I paused
>the app for some time hoping that the lock would go away but the problem
>persists even after pausing for 10, 20 and 30 minutes in seperate runs.
>
>
>If NFS is the problem then what configurations do I need to look at and
>change to make my app work properly?
Locking is handled by rpc.lockd(8). It should be started by default.
>
>What if the problem is with the host machine locking the file or some
>other process running on my machine or the host machine which locks the
>file? Is this possible?
Possible. Check your on both machines if the file is in use.
>
>If yes then is there a way to find out which process is doing this
>through the entries in /proc directory?
>
If on Linux, try using fuser(1) to see if other processes have the file
open, and lsof(8) to see if the file has locks associated with it.
>
>Thanks,
>Ritesh
>
>
>
>On Mon, 2006-01-30 at 19:31, [EMAIL PROTECTED] wrote:
>> Ritesh Kapoor <[EMAIL PROTECTED]> wrote:
>> > Yes.
>> > My machine has NFS and the machines I log onto also have NFS. But if
>> > this is the problem then why dosen't it appear on my machine when I run
>> > the app.
>>
>> Perhaps you are using a local filesystem when you run on
>> your machine. Or perhaps NFS is configured properly on
>> your machine but not on the other machines.
>>
>>
>> > Is there a workaround for this? without having to change the file system
>> > from NFS.
>> >
>>
>> Yes. Configure your NFS so that file locking works correctly.
>>
>> --
>> D. Richard Hipp <[EMAIL PROTECTED]>
>>
>
>
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
RE: [sqlite] Java bindings
> -Original Message- > From: Marian Olteanu [mailto:[EMAIL PROTECTED] > Sent: 31 January 2006 05:14 > To: sqlite-users@sqlite.org > Subject: [sqlite] Java bindings > any success. I failed to compile > http://www.ch-werner.de/javasqlite and > in Windows. I've compiled this for Windows. My build is here: http://www.itwriting.com/sqlite_jni_win.zip (Apologies for missing link in previous message). Note that the author also offers a binary on his site: http://www.ch-werner.de/javasqlite/ Tim Read my tech blog: http://www.itwriting.com/blog
RE: [sqlite] Java bindings
> -Original Message- > From: Marian Olteanu [mailto:[EMAIL PROTECTED] > Sent: 31 January 2006 05:14 > To: sqlite-users@sqlite.org > Subject: [sqlite] Java bindings > any success. I failed to compile > http://www.ch-werner.de/javasqlite and > in Windows. I've compiled this for Windows. My build is here:
Re: [sqlite] READ UNCOMMITTED isolation?
On Mon, 30 Jan 2006, Gerhard Häring wrote: >[EMAIL PROTECTED] wrote: >> "Dan Petitt" <[EMAIL PROTECTED]> wrote: >> >>>I think (looking at the source) that it's a pragma, but I don't know when >>>you set it, once when DB is opened, on each write or on each read. >>> >>>You are the third to ask (including me), maybe Richard or someone else can >>>through some light on it for us. >> >> READ UNCOMMITTED only works if you enable the shared cache >> feature and have two or more database connections sharing the >> same page and schema cache (meaning that they are both running >> in the same thread). Documentation is forthcoming. > >I don't understand the point of this feature. In fact I don't know why I >would want more than one database connection per thread at all. When >would I need that? I think the point is to use a single server thread to service multiple database connections from other threads, via a queue or some such. Have a look at server.c in the latest source for a sample implementation. > >-- Gerhard > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Database Locked Error
Please ommit line #18 from the sequence of statements
and then reply.
Thanks
Ritesh
On Tue, 2006-01-31 at 16:42, Ritesh Kapoor wrote:
> Regarding the configuration of NFS -
> I have two machines with NFS on them.
> - if i run the app on machine 1 it works properly
> - now when i run the app on machine 2 it works properly
>
> But if I login to machine 2 from machine 1 and then run the app I get
> the 'database is locked' error message.
>
> The sequence of statements are -
>
> 1 if((fp = fopen(fullFileName.c_str(),"w+")))
> 2 {
> 3 fclose(fp);
> 4 }
> 5 else
> 6 {
> 7 printf("Error creating file");
> 8 return;
> 9 }
> 10 int res = sqlite3_open(fullFileName.c_str(), &_db);
> 11 if( res != SQLITE_OK )
> 12 {
> 13 printf("Error opending DB");
> 14 sqlite3_close(_db);
> 15 return;
> 16 }
> 17
> 18 sqlite3_trace(getDB(),logSqlQuery,);
> 19 string query = "create table XYZ";
> 20 res = sqlite3_exec(_db, query.c_str(), NULL , 0, );
> 21
> 22 if(res == 5)
> 23
>
>
> After line #20 the value of 'res' is 5.
>
> I've even tried to run the app after removing line #18 which contains
> the sqlite3_trace() fn call. But that dosen't seem to be the cause.
>
> I've gone through the mail archive of this list and saw that this
> problem did appear for others as well but there the solution was to
> check if a previously executing sql statement was still not finished
> with its job and a another sql statement was being executed.
>
> Over here the first sql statement's execution returns this error.
>
> Another assumption that I made was that the host machine locks the file
> after I open a sqlite connection (line #10). So I should wait for it to
> release this lock. I ran the app using GDB and after line #10 I paused
> the app for some time hoping that the lock would go away but the problem
> persists even after pausing for 10, 20 and 30 minutes in seperate runs.
>
>
> If NFS is the problem then what configurations do I need to look at and
> change to make my app work properly?
>
> What if the problem is with the host machine locking the file or some
> other process running on my machine or the host machine which locks the
> file? Is this possible?
>
> If yes then is there a way to find out which process is doing this
> through the entries in /proc directory?
>
>
> Thanks,
> Ritesh
>
>
>
> On Mon, 2006-01-30 at 19:31, [EMAIL PROTECTED] wrote:
> > Ritesh Kapoor <[EMAIL PROTECTED]> wrote:
> > > Yes.
> > > My machine has NFS and the machines I log onto also have NFS. But if
> > > this is the problem then why dosen't it appear on my machine when I run
> > > the app.
> >
> > Perhaps you are using a local filesystem when you run on
> > your machine. Or perhaps NFS is configured properly on
> > your machine but not on the other machines.
> >
> >
> > > Is there a workaround for this? without having to change the file system
> > > from NFS.
> > >
> >
> > Yes. Configure your NFS so that file locking works correctly.
> >
> > --
> > D. Richard Hipp <[EMAIL PROTECTED]>
> >
Re: [sqlite] Database Locked Error
Regarding the configuration of NFS -
I have two machines with NFS on them.
- if i run the app on machine 1 it works properly
- now when i run the app on machine 2 it works properly
But if I login to machine 2 from machine 1 and then run the app I get
the 'database is locked' error message.
The sequence of statements are -
1 if((fp = fopen(fullFileName.c_str(),"w+")))
2 {
3 fclose(fp);
4 }
5 else
6 {
7 printf("Error creating file");
8 return;
9 }
10 int res = sqlite3_open(fullFileName.c_str(), &_db);
11 if( res != SQLITE_OK )
12 {
13 printf("Error opending DB");
14 sqlite3_close(_db);
15 return;
16 }
17
18 sqlite3_trace(getDB(),logSqlQuery,);
19 string query = "create table XYZ";
20 res = sqlite3_exec(_db, query.c_str(), NULL , 0, );
21
22 if(res == 5)
23
After line #20 the value of 'res' is 5.
I've even tried to run the app after removing line #18 which contains
the sqlite3_trace() fn call. But that dosen't seem to be the cause.
I've gone through the mail archive of this list and saw that this
problem did appear for others as well but there the solution was to
check if a previously executing sql statement was still not finished
with its job and a another sql statement was being executed.
Over here the first sql statement's execution returns this error.
Another assumption that I made was that the host machine locks the file
after I open a sqlite connection (line #10). So I should wait for it to
release this lock. I ran the app using GDB and after line #10 I paused
the app for some time hoping that the lock would go away but the problem
persists even after pausing for 10, 20 and 30 minutes in seperate runs.
If NFS is the problem then what configurations do I need to look at and
change to make my app work properly?
What if the problem is with the host machine locking the file or some
other process running on my machine or the host machine which locks the
file? Is this possible?
If yes then is there a way to find out which process is doing this
through the entries in /proc directory?
Thanks,
Ritesh
On Mon, 2006-01-30 at 19:31, [EMAIL PROTECTED] wrote:
> Ritesh Kapoor <[EMAIL PROTECTED]> wrote:
> > Yes.
> > My machine has NFS and the machines I log onto also have NFS. But if
> > this is the problem then why dosen't it appear on my machine when I run
> > the app.
>
> Perhaps you are using a local filesystem when you run on
> your machine. Or perhaps NFS is configured properly on
> your machine but not on the other machines.
>
>
> > Is there a workaround for this? without having to change the file system
> > from NFS.
> >
>
> Yes. Configure your NFS so that file locking works correctly.
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
[sqlite] Auto Increment?
Auto increment seems to return a unsigned long long is there any way for it to make it as 32 bit, as I am depending on this feilds to generate unique id, and i have a constraint fot the id to be 32 bit only. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] PRAGMA case_sensitive_like
Hi I am writing a wrapper around version 3.2.8 taken from the starkit , I have implemented and unit tested execution of queries and commands. I am now providing methods for the various PRAGMAs. I have implemented and successfully unit tested PRAGMAs such as auto_vacuum and count_changes. I basically execute the string 'PRAGMA count_changes ;' , 'PRAGMA auto_vacuum ;' etc as an sql query, i.e. prepare it , step it and look for any result that may exist. When I test the result for PRAGMA case_sensitive_like; I am expecting a result of either '0' or '1'. But there is no result being returned for this particular PRAGMA. I have confirmed this independantly by using SQLiteBrowser on another database. Is this the expected behaviour? Do others see this? Related to this behaviour. If I now reset the prepared statement and perform a step I get an SQLITE_ERROR. I query immediately for the error msg the text returned states: 'not an error'. Any suggestions? TIA Aidan
