[sqlite] New subscription to KL Chin's Contacts
KL Chin invites you to have access to his/her "Contacts" folder. Click the Accept button on this message to begin sharing this folder. If there is no Accept button on this form, then you need to install the Net Folders component which comes with Outlook 98 or newer versions of Office. To do so, go to the Control Panel, select Add/Remove Programs, then choose Microsoft Outlook or Microsoft Office. From there add the Net Folders component. We suggest that you make a printout of this message for future reference. Permission: You have been granted "Reviewer" access to the folder.As a Reviewer, you may only read items. How it Works: A copy of KL Chin's folder will be made for you in the folder selected below. Initial setup may take some time as your system is updated with KL Chin's. If at any time in the future you no longer wish to receive updates to this folder, simply hit the "Cancel Membership" button on the Sharing property page of the folder. A copy of the folder will remain on your machine until you delete it, but no further updates will be sent to you.
[sqlite] Does promoting a SHARED lock to RESERVED honor the busy timeout setting?
We're upgrading an app from 2.8.11 to 3.0.7 and are seeing fairly frequent "database is locked" errors that would be explained if this were not the case. It seems to be happening when two threads start a transaction; one does its first INSERT ok but the second one gets a SQLITE_BUSY error when it tries to INSERT. Our busy timeout is set high (several minutes) so it's pretty obvious that the timeout handler doesn't seem to be kicking in. What I would like is for the second thread to block until the first one commits i.e. 2.8.11 behaviour. I don't know the SQLite code so well but given that the busy timeout handler is invoked at the pager level, maybe this is why (i.e. because no pages are being changed, the handler is not being called).
Re: [sqlite] Question about trigger
Sure I can provide the schema: create table AICC_objectives_status ( session_id nvarchar(12)not null, j_id varchar(255)null, j_status nvarchar(13)null, j_score nvarchar(20)null ) ; create table AICC_student_data ( session_id nvarchar(12)not null, attempt_number int null, triesint null, status nvarchar(15)null, scorenvarchar(20)null, time nvarchar(12)null ) ; create table AICC_student_preferences ( session_id nvarchar(12)not null, audioint null, language nvarchar(255)null, lesson_type nvarchar(10)null, speedint null, text int null, text_color nvarchar(30)null, text_locationnvarchar(20)null, text_sizenvarchar(20)null, videonvarchar(15)null, constraint PK_AICC_STUDENT_PREFERENCES primary key (session_id) ) ; create table AICC_student_preferences_1 ( session_id nvarchar(12)not null, extensionint not null, window nvarchar(20)null, constraint PK_AICC_STUDENT_PREFERENCES_1 primary key (session_id, extension) ) ; create table AICC_core ( session_id nvarchar(12)not null, userid nvarchar(15)not null, student_id nvarchar(15)not null, courseid nvarchar(15)null, lesson_statusnvarchar(3) null, lesson_location varchar(255)null, score1 nvarchar(20)null, time1nvarchar(13)null, lesson_mode nvarchar(40)null, core_lesson textnull, totalnAttempts int null, totalSeconds int null, lastAttemptDate datetimenull, highestscore nvarchar(20) default 'NA' null, final_status nvarchar(1) null, finish_date datetimenull, entrychar(1)default 'a' not null, itemID nvarchar(15)null, totalTimenvarchar(13)null, totalTimeAtLastLaunch nvarchar(13)null, testInstanceID nvarchar(12)null, license_time_seconds intdefault 0 null, license_trip_datedatetimenull, constraint PK_AICC_CORE primary key (session_id) ) ; ''~`` ( o o ) +--.oooO--(_)--Oooo.--+ We are limited, not by our abilities, but by our vision. | | | http://www.carfield.com.hk| |.oooO| |( ) Oooo.| +-\ (( )+ \_)) / (_/ On Wed, 29 Sep 2004, Christian Smith wrote: > Without the schema, it's difficult to say where the problem is. > > Check spelling and make sure session_id is a column in AICC_core. > > Christian > > PS. If you're sure AICC_core has session_id, but don't want to publish > your schema for any reason, try reproducing the problem with an > example schema you can publish. > > > On Wed, 29 Sep 2004, Carfield Yim wrote: > > >Have create a trigger like this: > > > >create trigger AICC_core_del delete on AICC_core > >begin > > delete from AICC_objectives_status where session_id=old.session_id; > > delete from AICC_comments where session_id=old.session_id; > > delete from AICC_interactions where session_id=old.session_id; > > delete from AICC_student_data where session_id=old.session_id; > > delete from AICC_student_preferences where session_id=old.session_id; > > delete from AICC_student_preferences_1 where
[sqlite] Remote access to sqlite databases
Hi, I'm writing a server that will give remote access to sqlite databases. I plan to give a C library that will offer the same APIs as sqlite but will access the database through the server. I will write a MFC GUI application to run sql commands and reports on remote sqlite servers. This will enable users to compile code written for sqlite and gain remote access with zero work (just recompiling). I was wondering if anybody knows on such existing projects. I will be happy to hear any comments from the forum on possible design issues, required features or any other tips that can help me on my work. The project will be published as "public domain". I will use SOAP (gsoap) as the transport layer (which is good for passing firewalls etc). Thanks in advance, Avner
Re: [sqlite] Error compiling example
On Wed, 29 Sep 2004, Max wrote: You need to link against the SQLite library: $ gcc pippo.c -o pippo -lsqlite Nothing the result is the same .. you need to link against the sqlite version 3 library (you've probably succesfully linked against a version 2 library on your system). try something like gcc pippo.c -o pippo -L/full/path/to/directory/with/sqlite3lib/ -lsqlite afterwards run something like ldd pippo this should show that your binary points to the sqlite3 lib. because of the error i'm guessing you have the sqlite 2 lib in a standard place and the sqlite 3 lib in a non-standard place. this give you an additional problem: at runtime your binary will load the wrong lib. you have two fixes (a) once ~ > export LD_RUN_PATH=/full/path/to/directory/with/sqlite3lib/ ~ > gcc pippo.c -o pippo -L/full/path/to/directory/with/sqlite3lib/ -lsqlite everytime ~ > ./pippo (b) once ~ > gcc pippo.c -o pippo -L/full/path/to/directory/with/sqlite3lib/ -lsqlite everytime ~ > export LD_LIBRARY_PATH=/full/path/to/directory/with/sqlite3lib/ ~ > ./pippo do a man ld.so if this does not make sense. cheers. -a -- === | EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | A flower falls, even though we love it; | and a weed grows, even though we do not love it. | --Dogen ===
Re: [sqlite] Error compiling example
> > You need to link against the SQLite library: > > $ gcc pippo.c -o pippo -lsqlite > > Nothing the result is the same .. Your library is probably named sqlite3.so or sqlite-3.0.7.so or something like that. You have to give gcc the right name. Also, you have to put the library is a directory where the linker can find it, and you may need to run ldconfig after you install the library. These things apply to any shared library, not just to sqlite. Regards
Re: [sqlite] Error compiling example
> You need to link against the SQLite library: > $ gcc pippo.c -o pippo -lsqlite Nothing the result is the same .. Ciao Max -- Secondo alcuni autorevoli testi di tecnica Aeronautica, il calabrone non può volare, a causa della forma e del peso del proprio corpo in rapporto alla superficie alare. Ma il calabrone non lo sa e perciò continua a volare. Igor Sikorsky
Re: [sqlite] Error compiling example
On Wed, 29 Sep 2004, Max wrote: >I'm trying to compile this little example from www.sqlite.org > [snip] > >I compile this program : gcc pippo.c -o pippo > >But the result is : > >/tmp/ccH7jM5Y.o: In function `main': >/tmp/ccH7jM5Y.o(.text+0xd5): undefined reference to `sqlite3_open' >/tmp/ccH7jM5Y.o(.text+0xf2): undefined reference to `sqlite3_errmsg' >/tmp/ccH7jM5Y.o(.text+0x117): undefined reference to `sqlite3_close' >/tmp/ccH7jM5Y.o(.text+0x14c): undefined reference to `sqlite3_exec' >/tmp/ccH7jM5Y.o(.text+0x180): undefined reference to `sqlite3_close' >collect2: ld returned 1 exit status > >Why? You need to link against the SQLite library: $ gcc pippo.c -o pippo -lsqlite Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Ruby - SQLite 3.0.7
On Wed, 29 Sep 2004, Federico Granata wrote: Hi I'm looking for a sqlite 3.0.7 wrapper for ruby (nothin found with wiki). sqlite 2.x series is at http://sqlite-ruby.rubyforge.org/ i think 3.x is in the work by jamis buck, if not i'll proably attack in a month or so. -a -- === | EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | A flower falls, even though we love it; | and a weed grows, even though we do not love it. | --Dogen ===
[sqlite] How to use parameterized queries from the shell?
I don't see that the query language has provisions for doing parameterized queries from e.g. the shell. Did I miss something? -- My GPG public key is at http://ronware.org/ fingerprint: 8130 734C 69A3 6542 0853 CB42 3ECF 9259 AD29 415D
Re: [sqlite] Question about trigger
Without the schema, it's difficult to say where the problem is. Check spelling and make sure session_id is a column in AICC_core. Christian PS. If you're sure AICC_core has session_id, but don't want to publish your schema for any reason, try reproducing the problem with an example schema you can publish. On Wed, 29 Sep 2004, Carfield Yim wrote: >Have create a trigger like this: > >create trigger AICC_core_del delete on AICC_core >begin > delete from AICC_objectives_status where session_id=old.session_id; > delete from AICC_comments where session_id=old.session_id; > delete from AICC_interactions where session_id=old.session_id; > delete from AICC_student_data where session_id=old.session_id; > delete from AICC_student_preferences where session_id=old.session_id; > delete from AICC_student_preferences_1 where session_id=old.session_id; > delete from AICC_core_detail where session_id=old.session_id; >end >; > >But get the following exception: > >08:03:46.046 create trigger AICC_core_del delete on AICC_core begin > delete from AICC_objectives_status where session_id=old.session_id >08:03:47.047 SQLite.Exception: near "session_id": syntax error >08:03:47.047 delete from AICC_comments where session_id=old.session_id >08:03:47.047 SQLite.Exception: no such column: old.session_id >08:03:47.047 delete from AICC_interactions where session_id=old.session_id >08:03:47.047 SQLite.Exception: no such column: old.session_id >08:03:47.047 delete from AICC_student_data where session_id=old.session_id >08:03:47.047 SQLite.Exception: no such column: old.session_id >08:03:47.047 delete from AICC_student_preferences where >session_id=old.session_id >08:03:47.047 SQLite.Exception: no such column: old.session_id >08:03:47.047 delete from AICC_student_preferences_1 where >session_id=old.session_id >08:03:47.047 SQLite.Exception: no such column: old.session_id >08:03:47.047 delete from AICC_core_detail where session_id=old.session_id >08:03:47.047 SQLite.Exception: no such column: old.session_id >08:03:47.047 end >08:03:47.047 SQLite.Exception: cannot commit - no transaction is active > >Anyone know how to solve the manual tell that the old is a valid >reference in delete event... but see it is not? > -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
[sqlite] Error compiling example
I'm trying to compile this little example from www.sqlite.org
#include
#include
#include
#include "sqlite3.h"
static int callback(void *NotUsed, int argc, char **argv, char
**azColName)
{
int i;
for( i = 0; i < argc; i++)
{
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char **argv){
sqlite3 *db;
char *zErrMsg = 0;
int rc;
if( argc != 3 )
{
fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
exit(1);
}
rc = sqlite3_open(argv[1], &db);
if( rc )
{
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
rc = sqlite3_exec(db, argv[2], callback, 0, &zErrMsg);
if( rc != SQLITE_OK )
{
fprintf(stderr, "SQL error: %s\n", zErrMsg);
}
sqlite3_close(db);
return 0;
}
I compile this program : gcc pippo.c -o pippo
But the result is :
/tmp/ccH7jM5Y.o: In function `main':
/tmp/ccH7jM5Y.o(.text+0xd5): undefined reference to `sqlite3_open'
/tmp/ccH7jM5Y.o(.text+0xf2): undefined reference to `sqlite3_errmsg'
/tmp/ccH7jM5Y.o(.text+0x117): undefined reference to `sqlite3_close'
/tmp/ccH7jM5Y.o(.text+0x14c): undefined reference to `sqlite3_exec'
/tmp/ccH7jM5Y.o(.text+0x180): undefined reference to `sqlite3_close'
collect2: ld returned 1 exit status
Why?
Sorry for the question but I'm new to sqlite
Ciao Max
--
Secondo alcuni autorevoli testi di tecnica Aeronautica,
il calabrone non può volare, a causa della forma e del
peso del proprio corpo in rapporto alla superficie alare.
Ma il calabrone non lo sa e perciò continua a volare.
Igor Sikorsky
Re: [sqlite] Re: FOREIGN:[sqlite] Receiving notification of table updates
On Wed, 29 Sep 2004, Kazuho Oku wrote: >From: "Christian Smith" <[EMAIL PROTECTED]> >> On Tue, 28 Sep 2004, Kazuho Oku wrote: > >> >Unfortunately, my apache module only performs a single SELECT clause of >> >which WHERE clause can be indexed. >> >What I am wondering is the way to stop calling SQLite each time the module >> >processes an HTTP request (eliminate the FLOCK -> READ -> FUNLOCK done by >> >SQLite). >> >> Is this an actual bottleneck? Against internet latencies, this is likely >> not to be that big a win. >> >> It will not be significantly slower than any other filesystem operation, >> such as checking for an indicator file, especially if you then remove the >> indicator file (synchronous disk operations!) > >It's not the latency that is the problem. >The problem is the performance decrease caused by the module I am >developing. > >When apache (without my module) sends a static file, the disk-related system >calls that it issues are: stat(), open(), mmap(), munmap(), write(), once >for each. >If my module did not cache database information, then it would issue flock() >twice, and multiple seek()s and read()s. >Although I have not benchmarked, my understanding is that this would be of >(at least) some performance penalty. There is going to be some performance penalty, but if it's only 1% of your request time, you'll have complicated your design for very little performance gain. Always perform benchmarks to back up your assumptions. I'm not saying your assumptions are necessarily wrong, but just that you should back up your claims before complicating your design to work round a bottleneck which may not be there. AFAIK, flock() is a kernel operation, and seek() and read() should not touch the disk if the data blocks required are already cached, and so all should be relatively cheap operations (against disk IO and network latencies.) As I said, anything in the filesystem you use to communicate cache updates will require synchronous IO on the writer (create the file) and the reader (delete the file once cache is updated.) Synchronous IO will likely kill performance more than cached reads and seeks. >This is the reason why I think caching is necessary. > >> Does the cache have to be in sync with the current database all the time. >> ie. Would it be satisfactory to simply check the database once per second, >> and only update the cache then? This would certainly reduce any SQLite >> overhead significantly. > >Since apache is a multiprocess server, I do not think rereading the database >periodically is a good idea. >Typical usage would be to update the database through a CGI and then access >through the updated database from a different HTTP connection. So I cannot >make the interval between rereads long. How often is the reader being hit? Approximately? How often does the writer write? Is the reader also a CGI binary? If so, the fork/exec will likely be a big performance drain. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
RE: [sqlite] Is there...?
http://sqlitebrowser.sourceforge.net/ Edgardo Rossetto wrote: > Anything like a visual manager for sqlite dbs? > > or just the command line tool? > > Regards, > Edgardo reid
[sqlite] Question about trigger
Have create a trigger like this: create trigger AICC_core_del delete on AICC_core begin delete from AICC_objectives_status where session_id=old.session_id; delete from AICC_comments where session_id=old.session_id; delete from AICC_interactions where session_id=old.session_id; delete from AICC_student_data where session_id=old.session_id; delete from AICC_student_preferences where session_id=old.session_id; delete from AICC_student_preferences_1 where session_id=old.session_id; delete from AICC_core_detail where session_id=old.session_id; end ; But get the following exception: 08:03:46.046 create trigger AICC_core_del delete on AICC_core begin delete from AICC_objectives_status where session_id=old.session_id 08:03:47.047 SQLite.Exception: near "session_id": syntax error 08:03:47.047 delete from AICC_comments where session_id=old.session_id 08:03:47.047 SQLite.Exception: no such column: old.session_id 08:03:47.047 delete from AICC_interactions where session_id=old.session_id 08:03:47.047 SQLite.Exception: no such column: old.session_id 08:03:47.047 delete from AICC_student_data where session_id=old.session_id 08:03:47.047 SQLite.Exception: no such column: old.session_id 08:03:47.047 delete from AICC_student_preferences where session_id=old.session_id 08:03:47.047 SQLite.Exception: no such column: old.session_id 08:03:47.047 delete from AICC_student_preferences_1 where session_id=old.session_id 08:03:47.047 SQLite.Exception: no such column: old.session_id 08:03:47.047 delete from AICC_core_detail where session_id=old.session_id 08:03:47.047 SQLite.Exception: no such column: old.session_id 08:03:47.047 end 08:03:47.047 SQLite.Exception: cannot commit - no transaction is active Anyone know how to solve the manual tell that the old is a valid reference in delete event... but see it is not?
Re: [sqlite] Sqlite3 BLOB support
I've never had to do it before so take this with a grain of salt: >From what I've seen, storing file data in a relational database is generally looked upon as a bad idea. Files aren't exactly relational and filesystems are specialized for storing large, unrelated blocks of data, so why not use them? Sqlite 3.x doesn't have any limit on size but in most cases you should just reference a filename. Another thought- searching through a bunch of huge rows to get to your file data may cause more disk access than searching small rows containing just the filename and then referencing the file. On Wed, 29 Sep 2004 13:23:21 +0300, Serge Semashko <[EMAIL PROTECTED]> wrote: > Hello all, > > I'm sorry for a probably lame question, I'm new to sqlite and database > programming. Is it possible to store very large files in sqlite3 > database? Are there any limitations? > -- Cory Nelson http://www.int64.org
Re: [sqlite] Pre-compiling statements
Arno Engelbrecht wrote: > Does anybody know how to pre-compile a statement? Basically I want to do > mulitple inserts into the same table and want to cut down on the time to > parse each statement. > > http://www.sqlite.org/capi3ref.html#sqlite3_prepare -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Sqlite3 BLOB support
On 29 Sep 2004, at 11:23, Serge Semashko wrote: Hello all, I'm sorry for a probably lame question, I'm new to sqlite and database programming. Is it possible to store very large files in sqlite3 database? Are there any limitations? http://www.sqlite.org/faq.html#q10 Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
[sqlite] Sqlite3 BLOB support
Hello all, I'm sorry for a probably lame question, I'm new to sqlite and database programming. Is it possible to store very large files in sqlite3 database? Are there any limitations?
[sqlite] Ruby - SQLite 3.0.7
Hi I'm looking for a sqlite 3.0.7 wrapper for ruby (nothin found with wiki). Can you help me ? -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Biscotti perfetti? Metti la pasta dentro allo Sparabiscotti e...click click... biscotti pronti per essere infornati! Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=2745&d=29-9
