[sqlite] Pre-compiling statements
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.
[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=2745d=29-9
[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?
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 __
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] 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
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 / \
[sqlite] Error compiling example
I'm trying to compile this little example from www.sqlite.org
#include stdio.h
#include stdlib.h
#include string.h
#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] 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 / \
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 ===
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] 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
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
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 ===
[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] 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 session_id=old.session_id;
