[sqlite] Sqlite3_exec_stmt
I love sqlite3_exec, but I also like prepared statements. Here's a function
I wrote to make it easier to execute prepared statement. I have only tested
with SELECT and INSERT.
Hope someone finds it useful.
-Eric
#include "sqliteInt.h"
#include "os.h"
#include
/*
** Execute SQL code. Return one of the SQLITE_ success/failure
** codes. Also write an error message into memory obtained from
** malloc() and make *pzErrMsg point to that message.
**
** If the SQL is a query, then for each row in the query result
** the xCallback() function is called. pArg becomes the first
** argument to xCallback(). If xCallback=NULL then no callback
** is invoked, even for queries.
*/
int sqlite3_exec_stmt(
sqlite3_stmt *pStmt, /* The SQL to be executed */
sqlite3_callback xCallback, /* Invoke this callback routine */
void *pArg, /* First argument to xCallback() */
char **pzErrMsg /* Write error messages here */
){
int rc = SQLITE_OK;
sqlite3 *db = 0;
char **azCols = 0;
int nRetry = 0;
int nChange = 0;
int nCallback;
if( pStmt ==0 ) return SQLITE_OK;
db = sqlite3_db_handle(pStmt);
while( (rc==SQLITE_OK || (rc==SQLITE_SCHEMA && (++nRetry)<2))){
int nCol;
char **azVals = 0;
db->nChange += nChange;
nCallback = 0;
nCol = sqlite3_column_count(pStmt);
azCols = sqliteMalloc(2*nCol*sizeof(const char *) + 1);
if( azCols==0 ){
goto exec_out;
}
while( 1 ){
int i;
rc = sqlite3_step(pStmt);
/* Invoke the callback function if required */
if( xCallback && (SQLITE_ROW==rc ||
(SQLITE_DONE==rc && !nCallback && db->flags&SQLITE_NullCallback))
){
if( 0==nCallback ){
for(i=0; ipVdbe==0 ){
nChange = db->nChange;
}
break;
}
}
sqliteFree(azCols);
azCols = 0;
}
exec_out:
if( azCols ) sqliteFree(azCols);
rc = sqlite3ApiExit(0, rc);
if( rc!=SQLITE_OK && rc==sqlite3_errcode(db) && pzErrMsg ){
*pzErrMsg = sqlite3_malloc(1+strlen(sqlite3_errmsg(db)));
if( *pzErrMsg ){
strcpy(*pzErrMsg, sqlite3_errmsg(db));
}
}else if( pzErrMsg ){
*pzErrMsg = 0;
}
sqlite3_reset(pStmt);
assert( (rc&db->errMask)==rc );
return rc;
}
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Problem installing TCL bindings
I'm having a similar problem to this earlier email. I'm a novice linux user, not easily able to rebuild the sources. I have downloaded precompiled tclsqlite-3.5.3.so* from www.sqlite.org as described in the online doc. I start tclsh8.4 on fedora core 6, and try to load the tclsqlite library as described in the documentation. I get an error message as listed below, and I don't know how to proceed. I have /usr/share/tcl8.4/ but no /usr/share/tcl8.4/sqlite3 on this system. What is sqlite3StrICmp? Any help would be appreciated. Thanks, P. tclsh % load ./tclsqlite-3.5.3.so Sqlite3 couldn't load file "./tclsqlite-3.5.3.so": ./tclsqlite-3.5.3.so: undefined symbol: sqlite3StrICmp % if it helps, listing usr/share/ gives: ls /usr/share/tcl8.4/ total 176 -rw-r--r-- 1 root root 20911 Jul 20 2006 auto.tcl drwxr-xr-x 2 root root 4096 Jun 22 11:30 encoding/ -rw-r--r-- 1 root root 9030 Jul 20 2006 history.tcl drwxr-xr-x 2 root root 4096 Jun 22 11:30 http1.0/ drwxr-xr-x 2 root root 4096 Jun 22 11:30 http2.5/ -rw-r--r-- 1 root root 22845 Jul 20 2006 init.tcl -rw-r--r-- 1 root root 2856 Jul 20 2006 ldAix -rw-r--r-- 1 root root 6802 Jul 20 2006 ldAout.tcl drwxr-xr-x 2 root root 4096 Jun 22 11:30 msgcat1.3/ drwxr-xr-x 2 root root 4096 Jun 22 11:30 opt0.4/ -rw-r--r-- 1 root root 23894 Jul 20 2006 package.tcl -rw-r--r-- 1 root root 882 Jul 20 2006 parray.tcl -rw-r--r-- 1 root root 27659 Jul 20 2006 safe.tcl -rw-r--r-- 1 root root 4864 Jul 20 2006 tclAppInit.c -rw-r--r-- 1 root root 6097 Jul 20 2006 tclIndex drwxr-xr-x 2 root root 4096 Jun 22 11:30 tcltest2.2/ -rw-r--r-- 1 root root 4335 Jul 20 2006 word.tcl END %% Miguel Bazdresch wrote: > > On 4/7/06, Miguel Bazdresch <[EMAIL PROTECTED]> wrote: > >> 2. For some reason, the libraries needed for tcl interaction >> (libtclsqlite3.so) are not installed by 'make install' > > I finally determined they *are* installed, to /usr/lib/tcl8.4/sqlite3. > A mention of this somewhere on the website or README would be nice. > >> 3. The quickstart.html page is wrong. It states one needs to do: >> >>load /usr/lib/tclsqlite3.so Sqlite3 >> >> when actually one needs to do: >> >> load /usr/lib/libtclsqlite3.so Sqlite3 > > It actually needs to say > > load /usr/lib/tcl8.4/sqlite3/libtclsqlite3.so Sqlite3 > > It took me a few hours to figure this out. Please consider improving > the documentation so the installation directory is clearer and the > instructions more precise. > > -- > Miguel Bazdresch > > -- View this message in context: http://www.nabble.com/Problem-installing-TCL-bindings-tf1409622.html#a14225808 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: indexing BLOBs
P Kishor wrote: On 12/7/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote: P Kishor <[EMAIL PROTECTED]> wrote: folks, I have never worked with BLOBs, but am now going to. Feel a bit nervous. Here is a question -- what if I have a rather large image and I want only a certain part of it? Let me give an example -- say, I want to store an image of the entire US, but when I want to see only Hawaii, I ask SQLite to fetch the portion of the image with a certain bounding box, say, the rect of Hawaii. SQLite of course knows nothing about images. As far as it's concerned, you are storing an array of bytes - it couldn't care less if it contains image data or not. So you cannot directly ask it to retrieve a portion of the image. well, I used image as an example because I was interested in image. I guess, the same logic could be applied to any other BLOB. Recent versions of SQLite allow retrieving contiguous segments of the BLOB, given an offest and length. If you store your image as an uncompressed bitmap, then each scanline is stored as a contiguous segment. Given a rectangle, you can retrieve each scanline of the portion you are interested in, then compose them in memory into the final image. Ahhh so this is the path to pursue. Good to know that. Now to find someone who has actually done that. :-) Having said that, SQL database is probably a wrong tool for image manipulation. You are correct. But I don't want to manipulate the image with SQLite (or any SQL db). I just want to store and retrieve it efficiently. Storing images as files certainly precludes being able to find just *that* particular rect and extract it out of a file. One would have to experiment with many image tile size, so on, so forth. Thinking of an image not as an image but as an array of bytes intuitively seems to be the right direction to be thinking in. Many thanks, Puneet. Look at Google maps for inspiration. If you did not want to store an images as many tiles you would only have one image and would keep it in one file, not a DB. You could keep spacial refs in your DB and use the parameters to extract fragments from an image stored in a simple x-y format like a BMP. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] PATCH: compound query column naming and resolving (Ticket #2822)
The attached sqlite 3.5.3 patch addresses several different compound
query column naming and resolving issues in ORDER BY and the SELECT
expression list mentioned in this ticket:
http://www.sqlite.org/cvstrac/tktview?tn=2822
(The exception being it does not support expressions in the ORDER BY
clause of compound SELECT statements. That functionality remains the
same as in version 3.5.3.)
I believe it makes compound query behavior more compatible with other
popular databases. It is mostly backwards compatible with the previous
syntax and only 2 tests performed by "make test" had to be altered.
It seems to work, although it's quite possible that I missed something.
At least this patch serves as a basis of syntax discussion.
If you want to test it, just put sqlite-3.5.3.tar.gz and the patch
file in the same directory and run these commands:
tar xzvf sqlite-3.5.3.tar.gz
cd sqlite-3.5.3
patch -p0 < ../union-alias-20071207.patch.txt
./configure
make
./sqlite3
Please report any issues to the mailing list.
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
Index: src/select.c
===
RCS file: /sqlite/sqlite/src/select.c,v
retrieving revision 1.363
diff -u -3 -p -r1.363 select.c
--- src/select.c23 Nov 2007 13:42:52 - 1.363
+++ src/select.c8 Dec 2007 03:49:30 -
@@ -1118,8 +1118,19 @@ Table *sqlite3ResultSetOfSelect(Parse *p
/* For columns of the from A.B use B as the name */
zName = sqlite3MPrintf(db, "%T", &pR->token);
}else if( p->span.z && p->span.z[0] ){
- /* Use the original text of the column expression as its name */
- zName = sqlite3MPrintf(db, "%T", &p->span);
+ Token t = p->span;
+ if( p->op==TK_COLUMN ){
+/* Get rid of all dotted prefixes, if any */
+int n;
+for(n = t.n-1; n>=0; n--){
+ if( t.z[n]=='.' ){
+t.n -= n+1;
+t.z += n+1;
+break;
+ }
+}
+ }
+ zName = sqlite3MPrintf(db, "%T", &t);
}else{
/* If all else fails, make up a name */
zName = sqlite3MPrintf(db, "column%d", i+1);
@@ -1469,12 +1480,23 @@ static int matchOrderbyToColumn(
if( !mustComplete ) continue;
iCol--;
}
-if( iCol<0 && (zLabel = sqlite3NameFromToken(db, &pE->token))!=0 ){
+if( iCol<0 && (
+(zLabel = sqlite3NameFromToken(db, &pE->token))!=0
+|| ((pE->op==TK_STRING || pE->op==TK_DOT || pE->op==TK_ID)
+ && (zLabel = sqlite3NameFromToken(db, &pE->span))!=0) )){
for(j=0, pItem=pEList->a; jnExpr; j++, pItem++){
char *zName;
int isMatch;
if( pItem->zName ){
zName = sqlite3DbStrDup(db, pItem->zName);
+}else if( pItem->pExpr->op==TK_DOT && pE->op==TK_ID ){
+ Expr *pRight = pItem->pExpr->pRight;
+ if( pRight->op==TK_DOT ){
+pRight = pRight->pRight;
+ }
+ zName = sqlite3NameFromToken(db, &pRight->token);
+}else if( pItem->pExpr->op==TK_DOT && pE->op==TK_DOT ){
+ zName = sqlite3NameFromToken(db, &pItem->pExpr->span);
}else{
zName = sqlite3NameFromToken(db, &pItem->pExpr->token);
}
@@ -1829,7 +1851,7 @@ static int multiSelect(
if( eDest==SRT_Callback ){
Select *pFirst = p;
while( pFirst->pPrior ) pFirst = pFirst->pPrior;
- generateColumnNames(pParse, 0, pFirst->pEList);
+ generateColumnNames(pParse, pFirst->pSrc, pFirst->pEList);
}
iBreak = sqlite3VdbeMakeLabel(v);
iCont = sqlite3VdbeMakeLabel(v);
@@ -1907,7 +1929,7 @@ static int multiSelect(
if( eDest==SRT_Callback ){
Select *pFirst = p;
while( pFirst->pPrior ) pFirst = pFirst->pPrior;
-generateColumnNames(pParse, 0, pFirst->pEList);
+generateColumnNames(pParse, pFirst->pSrc, pFirst->pEList);
}
iBreak = sqlite3VdbeMakeLabel(v);
iCont = sqlite3VdbeMakeLabel(v);
Index: test/select1.test
===
RCS file: /sqlite/sqlite/test/select1.test,v
retrieving revision 1.54
diff -u -3 -p -r1.54 select1.test
--- test/select1.test 23 Jul 2007 22:51:15 - 1.54
+++ test/select1.test 8 Dec 2007 03:49:31 -
@@ -559,6 +559,66 @@ do_test select1-6.23 {
}
} {b d}
+# Ticket #2822
+do_test select1-6.30 {
+ execsql {
+ CREATE TABLE x1(a, b, c);
+ INSERT INTO x1 VALUES(6, 4,
Re: [sqlite] Re: Any advantages of "varchar()" over "text"?
On 12/7/07, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote: > On Fri, Dec 07, 2007 at 05:49:56PM -0500, Igor Tandetnik wrote: > > >So I would to ask: are there any benefits from using VARCHAR() and > > >not TEXT? > > There is no difference whatsoever to SQLite. It maintains the types for > > compatibility with other DMBS only > Just from curiosity: perhaps could you tell, does it make any differences > (other than just coercing a limit) in case of other database systems (like > f.e. PostgreSQL, or other known to you)? > > If so - is it significant difference (in data access speed, or any other...)? It varies wildly depending on the database and how its storage was designed. In PostgreSQL, there is no difference; VARCHAR is just TEXT with a constraint on length. However, its storage subsystem is such that any data of variable length is stored the same way, up to a maximum of 1GB, and all variable-length data types are built on that. Firebird and Interbase have a 32KB limit on VARCHAR size (the limit you use may be lower depending on encoding selection), but it's stored essentially the same way as other database fields. It does not have a TEXT type, so your next option is BLOB (with a subtype label of text), which is stored separately from other data. It's not entirely transparent due to the way it interacts with Firebird's transactional architecture, so there are some caveats to using it. That's beside obvious disadvantages of it being a separate type, and therefore not as easy to use string manipulation functions with. And so on... In general, you probably wion't find any significant difference between a TEXT type (if it exists) and a VARCHAR type. As another reply mentioned, the major historical difference was between CHAR and VARCHAR, since the former can be optimized based on a fixed-length architecture. You're less likely to see that now, since storage and processing capabilities have changed such that it's better to spend more CPU time in an effort to make the on-disk data as compact as possible, since storage is so slow to access. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: indexing BLOBs
P Kishor <[EMAIL PROTECTED]> wrote: You are correct. But I don't want to manipulate the image with SQLite (or any SQL db). I just want to store and retrieve it efficiently. Storing images as files certainly precludes being able to find just *that* particular rect and extract it out of a file. What do you believe you can do to a BLOB in a database that you can't do to a file on disk? You most certainly can read a portion of a file at a given offset. One would have to experiment with many image tile size, so on, so forth. Thinking of an image not as an image but as an array of bytes intuitively seems to be the right direction to be thinking in. And what exactly do you think a file is? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Any advantages of "varchar()" over "text"?
On Fri, Dec 07, 2007 at 06:05:35PM -0600, P Kishor wrote: > don't know about other db, but with the ones I have worked, there is > no such thing as unlimited text width field. Oracle maxes out VARCHAR > at 4000 or 8000 (for NVARCHAR).. Of course, I realize, that "unlimited" is just theory. Perhaps I should write: "with no formal limit set" rather. So, I was just wondering, whether (or not) there is a noticeable difference when operating on - let's say - VARCHAR(255), rather than on just TEXT. Never made such measurements by myself. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: indexing BLOBs
On 12/7/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > P Kishor <[EMAIL PROTECTED]> wrote: > > folks, I have never worked with BLOBs, but am now going to. Feel a > > bit nervous. > > > > Here is a question -- what if I have a rather large image and I want > > only a certain part of it? Let me give an example -- say, I want to > > store an image of the entire US, but when I want to see only Hawaii, I > > ask SQLite to fetch the portion of the image with a certain bounding > > box, say, the rect of Hawaii. > > SQLite of course knows nothing about images. As far as it's concerned, > you are storing an array of bytes - it couldn't care less if it contains > image data or not. So you cannot directly ask it to retrieve a portion > of the image. > well, I used image as an example because I was interested in image. I guess, the same logic could be applied to any other BLOB. > Recent versions of SQLite allow retrieving contiguous segments of the > BLOB, given an offest and length. If you store your image as an > uncompressed bitmap, then each scanline is stored as a contiguous > segment. Given a rectangle, you can retrieve each scanline of the > portion you are interested in, then compose them in memory into the > final image. Ahhh so this is the path to pursue. Good to know that. Now to find someone who has actually done that. :-) > > Having said that, SQL database is probably a wrong tool for image > manipulation. > You are correct. But I don't want to manipulate the image with SQLite (or any SQL db). I just want to store and retrieve it efficiently. Storing images as files certainly precludes being able to find just *that* particular rect and extract it out of a file. One would have to experiment with many image tile size, so on, so forth. Thinking of an image not as an image but as an array of bytes intuitively seems to be the right direction to be thinking in. Many thanks, Puneet. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Any advantages of "varchar()" over "text"?
On 12/7/07, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote: > On Fri, Dec 07, 2007 at 05:43:49PM -0600, P Kishor wrote: > > > I believe it used to... fixed width (CHAR) was quicker than VARCHAR > > while the latter was more space efficient, obviously. > > But he didn't compare variable, but limited width (VARCHAR()) - with variable > unlimited width fields (TEXT)? > -- don't know about other db, but with the ones I have worked, there is no such thing as unlimited text width field. Oracle maxes out VARCHAR at 4000 or 8000 (for NVARCHAR).. I guess you could muck around with CLOBs, but as far as I remember, they are a royal pain in the behind. Don't remember about SQL Server. Never worked with DB2. If it matters, Access maxes out at 64k for its memo fields. Puneet. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: indexing BLOBs
P Kishor <[EMAIL PROTECTED]> wrote: folks, I have never worked with BLOBs, but am now going to. Feel a bit nervous. Here is a question -- what if I have a rather large image and I want only a certain part of it? Let me give an example -- say, I want to store an image of the entire US, but when I want to see only Hawaii, I ask SQLite to fetch the portion of the image with a certain bounding box, say, the rect of Hawaii. SQLite of course knows nothing about images. As far as it's concerned, you are storing an array of bytes - it couldn't care less if it contains image data or not. So you cannot directly ask it to retrieve a portion of the image. Recent versions of SQLite allow retrieving contiguous segments of the BLOB, given an offest and length. If you store your image as an uncompressed bitmap, then each scanline is stored as a contiguous segment. Given a rectangle, you can retrieve each scanline of the portion you are interested in, then compose them in memory into the final image. Having said that, SQL database is probably a wrong tool for image manipulation. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Any advantages of "varchar()" over "text"?
On Fri, Dec 07, 2007 at 05:43:49PM -0600, P Kishor wrote: > I believe it used to... fixed width (CHAR) was quicker than VARCHAR > while the latter was more space efficient, obviously. But he didn't compare variable, but limited width (VARCHAR()) - with variable unlimited width fields (TEXT)? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] indexing BLOBs
folks, I have never worked with BLOBs, but am now going to. Feel a bit nervous. The way I understand it, our favorite db breaks a BLOB into its predetermined chunks (4096 bytes or whatever) and figures out how and where to store them. We just ask it to put in the BLOB or take out the BLOB, and SQLite just does the job. I also understand that it is advisable to store the BLOBs in their own table with an id field linking them to the other attributes instead of lumping them with other attributes. Here is a question -- what if I have a rather large image and I want only a certain part of it? Let me give an example -- say, I want to store an image of the entire US, but when I want to see only Hawaii, I ask SQLite to fetch the portion of the image with a certain bounding box, say, the rect of Hawaii. How would one go about implementing such a capability? (and, no, please don't tell me to break up the image of the US into 50 images of the States... that is not what I am asking). Obviously, this capability would be useful in storing very large images, and fetching portions of them. Many thanks, Puneet. Oh yes, if any one of you has done extensive work with BLOBs, and has been sweet enough to write a how-to, please point me to it. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Any advantages of "varchar()" over "text"?
On 12/7/07, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote: > On Fri, Dec 07, 2007 at 05:49:56PM -0500, Igor Tandetnik wrote: > > > >So I would to ask: are there any benefits from using VARCHAR() and > > >not TEXT? > > > > There is no difference whatsoever to SQLite. It maintains the types for > > compatibility with other DMBS only > > Just from curiosity: perhaps could you tell, does it make any differences > (other than just coercing a limit) in case of other database systems (like > f.e. PostgreSQL, or other known to you)? > > If so - is it significant difference (in data access speed, or any other...)? > -- I believe it used to... fixed width (CHAR) was quicker than VARCHAR while the latter was more space efficient, obviously. One of my Oracle-ish friends was telling me that now it really doesn't matter as they are all very fast, and he just makes everything VARCHAR. Some db, perhaps under some installation conditions, will fail the operation if you try to insert a bigger string in a CHAR column. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Any advantages of "varchar()" over "text"?
-Original Message- From: P Kishor [mailto:[EMAIL PROTECTED] Sent: Friday, December 07, 2007 5:50 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Any advantages of "varchar()" over "text"? I may be wrong, but my understanding is that other than INTEGER PRIMARY KEY, SQLite doesn't give a rat's batuti about what you define a column as... for example, ... it does matter what datatype you use, just SQLite is very forgiving. But read the section on "column affinity" in the datatypes page for the importance of selecting the right types and aliases for the pre-defined types. http://sqlite.org/datatype3.html HTH, Sam - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Any advantages of "varchar()" over "text"?
On Fri, Dec 07, 2007 at 04:50:19PM -0600, P Kishor wrote: > I may be wrong, but my understanding is that other than INTEGER > PRIMARY KEY, SQLite doesn't give a rat's batuti about what you define Perhaps it's related to "closed relations" between SQLite and Tcl. Well, it's even more comfortable then. Less things to take care of. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Any advantages of "varchar()" over "text"?
On Fri, Dec 07, 2007 at 05:49:56PM -0500, Igor Tandetnik wrote: > >So I would to ask: are there any benefits from using VARCHAR() and > >not TEXT? > > There is no difference whatsoever to SQLite. It maintains the types for > compatibility with other DMBS only Just from curiosity: perhaps could you tell, does it make any differences (other than just coercing a limit) in case of other database systems (like f.e. PostgreSQL, or other known to you)? If so - is it significant difference (in data access speed, or any other...)? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Any advantages of "varchar()" over "text"?
On 12/7/07, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote: > We can define in, using SQLite, both "fixed max. width" VARCHAR() fields (I > know, in practice it can be crossed over), as well as "no limit" TEXT fields. > > So I would to ask: are there any benefits from using VARCHAR() and not TEXT? > For example faster(?) data access - or just anything, that makes establishing > such limit on the field length reasonable? I may be wrong, but my understanding is that other than INTEGER PRIMARY KEY, SQLite doesn't give a rat's batuti about what you define a column as... for example, CREATE TABLE foo (a ELEPHANT, b ZEBRA(32)) should be a perfectly valid statement. It does provide INTEGER, REAL, DATE, TEXT, and BLOB as five types, but it is a free for all in their. You might want to define columns more specifically with a view to making your schema more portable. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Any advantages of "varchar()" over "text"?
Zbigniew Baniewski wrote: We can define in, using SQLite, both "fixed max. width" VARCHAR() fields (I know, in practice it can be crossed over), as well as "no limit" TEXT fields. So I would to ask: are there any benefits from using VARCHAR() and not TEXT? There is no difference whatsoever to SQLite. It maintains the types for compatibility with other DMBS only: internally, there is only one string type and it supports strings of unlimited length. The length specified in VARCHAR() spec is ignored. For more details, see http://sqlite.org/datatype3.html Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Any advantages of "varchar()" over "text"?
We can define in, using SQLite, both "fixed max. width" VARCHAR() fields (I know, in practice it can be crossed over), as well as "no limit" TEXT fields. So I would to ask: are there any benefits from using VARCHAR() and not TEXT? For example faster(?) data access - or just anything, that makes establishing such limit on the field length reasonable? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Unix Epoch Time Support
Mark Riehl wrote: > All - We're in the process of porting an application that used MySQL > to an embedded platform running SQLite. Two of the built-in functions > we used frequently were the functions to create and convert from Unix > epochs (UNIX_TIMESTAMP(), FROM_UNIXTIME). > > Are there any plans to add this functionality to SQLite? This functionality already exists. Look up datetime processing in the Wiki or checking out my blog post: http://www.perturb.org/display/entry/629/ -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Unix Epoch Time Support
All - We're in the process of porting an application that used MySQL to an embedded platform running SQLite. Two of the built-in functions we used frequently were the functions to create and convert from Unix epochs (UNIX_TIMESTAMP(), FROM_UNIXTIME). Are there any plans to add this functionality to SQLite? Thanks, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Performance tuning using PRAGMA, other methods
I believe the optimizations that would help the most is the time spent on "commits." Thanks, Brett -Original Message- From: Scott Krig [mailto:[EMAIL PROTECTED] Sent: Thursday, November 29, 2007 10:57 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods BTW, several PRAGMAS actually increase performance in my embedded app case - maybe 15-30% depending upon transaction activity and the way I structure transaction commits. Specific PRAGMAS that helped include: // // Synchronous OFF (0) // rc = sqlite3_exec(*dbHandle, "PRAGMA synchronous = 0;", 0, 0, 0); // // Keep temporary storage in MEMORY (2) instead of a file // rc = sqlite3_exec(*dbHandle, "PRAGMA temp_store = 2;", 0, 0, 0); // // Allow reads from uncommitted memory containing DB tables/records // rc = sqlite3_exec(*dbHandle, "PRAGMA read_uncommitted = 1;", 0, 0, 0); // // Exclusive access to DB to avoid lock/unlock for each transaction // rc = sqlite3_exec(*dbHandle, "PRAGMA locking_mode = exclusive;", 0, 0, 0); -Original Message- From: Scott Krig [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 20, 2007 2:03 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods There are apparently no folks with the experience to answer the questions as given? -Original Message- From: Tom Briggs [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 20, 2007 1:41 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods re: Q1 - Maybe (hopefully) someone will prove me wrong, but I think that your question is too broad to be answerable (unless you're actually attempting to assemble a collection of optimal values in all possible situations for all existing pragmas... ;shrug) re: Q2 - At the risk of sounding crass, tuning queries is, has been and always will be the best way to optimize the performance of any database. I've done a lot of tuning of SQLite and a half dozen other databases, and query design is always what has the most impact. Pragmas, #defines, API usage, etc. are always a distant second in the race for performance gains. -T > -Original Message- > From: Scott Krig [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 20, 2007 4:24 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods > > To the point, the questions are: > > > Q1)) PRAGMA: Does anyone have experience and good results optimizing > sqlite performance using PRAGMA's? If so, which ones, how were they > used, and what was the performance increase? > > Q2)) Other techniques: Any success stories on sqlite optimization > methods of any type would be appreciated. > > > > > > -Original Message- > From: Tom Briggs [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 20, 2007 10:40 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods > > >Which pragmas will be most effective (and what values you > should use > for each) depends on what you're trying to do with the database. > Synchronous is important if you're writing frequently, for > example, but > won't matter much in a read-only setting. Appropriate values for the > page_size and cache_size pragmas vary depending on whether > the database > is write-mostly or read-mostly and also depending on whether > you want to > optimize for reading or writing. > >So in short, the answer is, it depends. Depends on what you're > trying to tune for, that is. > >-T > > > -Original Message- > > From: Scott Krig [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, November 20, 2007 1:13 PM > > To: sqlite-users@sqlite.org > > Subject: [sqlite] Performance tuning using PRAGMA, other methods > > > > > > What are the 'biggest bang for the buck' sqlite optimization > > techniques > > to apply to a working system to tune performance? > > > > Q1)) PRAGMA: Does anyone have experience and good results optimizing > > sqlite performance using PRAGMA's? If so, which ones, how were they > > used, and what was the performance increase? > > Q2)) Other techniques: Any success stories on sqlite optimization > > methods of any type would be appreciated. > > > > Thanks. > > > > > > Scott > > -=- > > > > > > > > Here is a list of the PRAGMA examples from the sqlite documentation: > > > > > > > > PRAGMA auto_vacuum; > > PRAGMA auto_vacuum = 0 | none | 1 | full | 2 | incremental; > > > > PRAGMA cache_size; > > PRAGMA cache_size = Number-of-pages; > > > > PRAGMA case_sensitive_like; > > PRAGMA case_sensitive_like = 0 | 1; > > > > PRAGMA count_changes; > > PRAGMA count_changes = 0 | 1; > > > > PRAGMA default_cache_size; > > PRAGMA default_cache_size = Number-of-pages; > > > > PRAGMA default_synchronous; > > > > PRAGMA empty_result_callba
Re: [sqlite] sqlite:Deletion in Joins method
Sreedhar.a wrote: Hi Dennis, I have created 2 tables for PlayList as u suggested as Follows. "CREATE TABLE MUSIC (MusicId INTEGER PRIMARY KEY NOT NULL,Album_Id INTEGER,Artist_Id INTEGER,Bgm_Id INTEGER, Track Text);" MusicId Album_Id Artist_Id Bgm_Id Track 1111 T1.mp3 2112 T2.mp3 3113 T3.mp3 4221 S1.mp3 5222 S2.mp3 6221 S3.mp3 "CREATE TABLE PLAYLIST(PlayListId INTEGER PRIMARY KEY NOT NULL, PlayListName Text);" PlayListId PlayListName 1PlayList1 2PlayList2 "CREATE TABLE TRACKS(PlayListId INTEGER, MusicId INTEGER);" PlayListId MusicId 11 24 12 26 13 16 My Doubt is: If i want to list the MUSIC.Track for Playlist1.With the below statement i could able to get only the First result. ie, T1.mp3 "SELECT Track from MUSIC where MUSIC.Id=(SELECT MusicId FROM TRACKS WHERE TRACKS.PlayListId = (SELECT Id FROM PLAYLIST WHERE PlayListName ='Maha'));" But my desired result is as follows. T1.mp3 T2.mp3 T3.mp3 S3.mp3 Can u please correct where i am wrong. You need to join the playlist, tracks, and music tables to do this. Select Track from PLAYLIST join TRACKS using PlayListId join MUSIC using MusicId where PlayListName = 'Maha'; Also, I realized after I posted my last message that using last_insert_rowid is probably not a good idea. It will only return the correct rowid when the insert or ignore actually does an insert. If the row already exists in the table, it will return the wrong rowid. You should stick with the original selects, unless you know that you are inserting a new row. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
