RE: [sqlite] [Visual Basic] How do you work with SQLite?
Several reasons. Main one is that we won't be allowed as this is a third party application clinical database. The other one is that it would cause too much slow-down of the regular clinical front-end application. This is reporting software and apart from some rare exceptions there is no writing back to Interbase. RBS -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: 10 August 2007 02:58 To: sqlite-users@sqlite.org Subject: RE: [sqlite] [Visual Basic] How do you work with SQLite? At 05:25 PM 8/9/2007, you wrote: >I use it mainly to manipulate data obtained from an Interbase database. >All the data will eventually be dumped to Excel. >I use 2 ways to move data from Interbase to SQLite. >One, via an ADO recordset after connecting to Interbase with ODBC. This >recordset will then be dumped to SQLite via the free VB wrapper from Olaf >Schmidt, dhSQLite. Very good and fast wrapper with a rich object model. >Two, via a custom Delphi dll that moves data directly from Interbase to >SQLite. This is maybe slightly faster than the first method, but not that >much difference. So, no ODBC with this method and no ADO recordset. > >Moving data from SQLite to Excel is all done with dhSQLite. This wrapper can >be downloaed from: http://www.thecommon.net/2.html >Very much recommended for anybody who works with VB or VBA. > >RBS RBS, So why can't you manipulate the data in Interbase? It's reasonably fast. Mike - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Threading issues in SQLite
Hi Dan, Thanks for your suggestion. > If you have any choice in the matter, don't use threads. Run all 5 > queries from the same thread. You can either run them sequentially, Running sequentially might trouble me as I have to update more sets of information at a cause of single event. > or sqlite3_prepare() all 5 and then round-robin calls to sqlite3_step(). I have look into this. However, could you please let me know the overhead in managing multiple connections? Does it create any impact on my application's performance? On 8/10/07 11:41 AM, "Dan Kennedy" <[EMAIL PROTECTED]> wrote: > On Fri, 2007-08-10 at 11:25 +0530, Bharath Booshan L wrote: >> Hello All, >> >> I am using SQLite in one my application and I require to retrieve around >> 4-5 sets of information simultaneously. I have opted to run the queries in >> separate threads which seems obvious choice here. >> >> However, I came to know from one of the documents in sqlite.org that single >> connection cannot be used simultaneously across threads. >> >> So I would like to hear any suggestions regarding the best wary to open & >> manage multiple connections i.e. >> >> a) Is it efficient to create new connections in separate thread each time >> when the query needs to be executed? ( Here I am worried about the time >> taken to open connection ) >> >> b) Is it efficient to create the number of connections required, in a single >> thread and use it in different threads? ( although a connection will not be >> used simultaneously and this will eliminate the effort to create different >> connections but we need to ensure that a connection is used simultaneously >> in different threads) >> >> Looking forward to your suggestions, --- Robosoft Technologies - Come home to Technology Disclaimer: This email may contain confidential material. If you were not an intended recipient, please notify the sender and delete all copies. Emails to and from our network may be logged and monitored. This email and its attachments are scanned for virus by our scanners and are believed to be safe. However, no warranty is given that this email is free of malicious content or virus. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Threading issues in SQLite
On Fri, 2007-08-10 at 11:25 +0530, Bharath Booshan L wrote: > Hello All, > > I am using SQLite in one my application and I require to retrieve around > 4-5 sets of information simultaneously. I have opted to run the queries in > separate threads which seems obvious choice here. > > However, I came to know from one of the documents in sqlite.org that single > connection cannot be used simultaneously across threads. > > So I would like to hear any suggestions regarding the best wary to open & > manage multiple connections i.e. > > a) Is it efficient to create new connections in separate thread each time > when the query needs to be executed? ( Here I am worried about the time > taken to open connection ) > > b) Is it efficient to create the number of connections required, in a single > thread and use it in different threads? ( although a connection will not be > used simultaneously and this will eliminate the effort to create different > connections but we need to ensure that a connection is used simultaneously > in different threads) > > Looking forward to your suggestions, If you have any choice in the matter, don't use threads. Run all 5 queries from the same thread. You can either run them sequentially, or sqlite3_prepare() all 5 and then round-robin calls to sqlite3_step(). Either way, things will be more efficient. For you and the application. Hey, you asked for suggestions. :) Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Threading issues in SQLite
Hello All, I am using SQLite in one my application and I require to retrieve around 4-5 sets of information simultaneously. I have opted to run the queries in separate threads which seems obvious choice here. However, I came to know from one of the documents in sqlite.org that single connection cannot be used simultaneously across threads. So I would like to hear any suggestions regarding the best wary to open & manage multiple connections i.e. a) Is it efficient to create new connections in separate thread each time when the query needs to be executed? ( Here I am worried about the time taken to open connection ) b) Is it efficient to create the number of connections required, in a single thread and use it in different threads? ( although a connection will not be used simultaneously and this will eliminate the effort to create different connections but we need to ensure that a connection is used simultaneously in different threads) Looking forward to your suggestions, Bharath Booshan L --- Robosoft Technologies - Come home to Technology Disclaimer: This email may contain confidential material. If you were not an intended recipient, please notify the sender and delete all copies. Emails to and from our network may be logged and monitored. This email and its attachments are scanned for virus by our scanners and are believed to be safe. However, no warranty is given that this email is free of malicious content or virus. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Object Oriented Question About Adding a vector inside the callback function
I think problem is in use char * as parameter in push function. For that reason I use std::string always. Look, in callback2 SQLite pass char ** - array of char*. But when your code leave callback2 SQLite might errase that array and then all char* will garbage. But when in push method you will pass const std::string & -- char* will transform into std::string and you will not lose any data. Or event you can obviosly call ctor of std::string like q->push( atoi(argv[0] ),atoi(argv[1] ),atoi(argv[2] ), std::string( argv[3] ), std::string( argv[4] ) ); and push method then will looks like this: void QuestionDB::push( int b, int c, int v, const std::string & q, const std::string & a ) { ... } P.S. Try don't use char* in C++ code. Use std::string instead. And when you need char* you can transform std::string into char* with c_str() method of std::string. Stephen Sutherland wrote: Igor I tried your solution and can't get it working . Here is my code. The STRANGE problem that I am having is that when it adds to the vector at position 1, the vector contains the object. But when it adds to vector at position 2 using push_back - for some reason the contents at vector 1 is garbage. then when it adds to vector at position 3 using push_back - for some reason position 3 is garabage ? the errors in this situation doesn't seem to follow much logic ? but it's consistent. for example if i run this code and it uses push 4 times, position 1 and 3 might have garbase while position 2 will have the same contents as position 4 ? ? this is some weird stuff i haven't seen before. I'm wondering if the problem is due to the scope of either my QuestionDBStruct or my vector . = static int callback2(void *NotUsed, int argc, char **argv, char **azColName) { int i; for(i=0; i printf("%i. %s = %s\n", i, azColName[i], argv[i] ? argv[i] : "NULL"); } QuestionDB* q = (QuestionDB*)NotUsed; q->push(c ); return 0; }; QuestionDB::QuestionDB() { }; void QuestionDB::push(int b, int c, int v, char* q, char* a) { QuestionDBStruct qbs; qbs.bible_book =1; qbs.bible_chapter =2; qbs.bible_verse=3; qbs.bible_answer ="test"; qbs.bible_question =q; printf("\n** push called \n"); vecQuestions.push_back(qbs); for(int x = 0 ; x < vecQuestions.size(); x++) printf("\nvecQuestion[%i] = %s \n", x, ((QuestionDBStruct)vecQuestions[x]).bible_question ); printf("\n***\n"); }; Igor Mironchick <[EMAIL PROTECTED]> wrote: If I understand you right then try it: static int add_value( void *st, int, char **value, char ** ) { storage_t * storage = (storage_t*) st; st->push( value[ 0 ] ); return SQLITE_OK; }; class storage_t { public: storage_t() : m_db( 0 ) { sqlite3_open( "your_database.db", &m_db ); }; virtual ~storage_t() { sqlite3_close( m_db ); }; void push( const std::string & v ) { m_buff.push_back( v ); } void read_table() { sqlite3_exec( m_db, "SELECT * FROM some_table", add_value, this, NULL ); } private: sqlite3 * m_db; std::vector< std::string > m_buff; }; This is very simple example, but it can help you I think. Stephen Sutherland wrote: Hi I am using the 'quick start' C-styled code for sqlite3 http://www.sqlite.org/quickstart.html I think I'm running into a problem trying to put it in classes to make it somewhat object oriented. So I'm asking for help about how to make it object-oriented - or to confirm whether what I'm doing is object oriented. Here is the code: [code] //callback function static int callback(void *NotUsed, int argc, char **argv, char **azColName) { int i; for(i=0; i> printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL"); } printf("\n"); return 0; }; // this callback is referenced here. void MyClass::executeSQLStatement() { rc = sqlite3_exec(db, "select * from table1" , callback, 0, &zErrMsg); }; [/code] However I am trying to add a vector in the callback function to store the results. When I put the vector in it seems I am forced to do something like this: [code] vector vecX; static int callback(void *NotUsed, int argc, char **argv, char **azColName) { int i; for(i=0; i> printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL"); } vecX.push_back(argv[3]); printf("\n"); return 0; }; [/code] Now this doesn't seem object oriented ? Nor do I understand how I would access this vector from other classes ? And I don't know how this vector which I created can be considered part of the class ? it seems to me to only have page scope. Any advice on how to make my vector object ori
Re: [sqlite] Object Oriented Question About Adding a vector inside the callback function
okay i'm trying to use preparestatement and step and finalize. I have some quick questions about this legacy.c code. First I notice that it has a while loop within a while loop. Question: when I implement this prepared statement, will I also need a while loop within a while loop ? Just double checking I noticed rc = sqlite3_step(pStmt); is the start of the inner while loop. I'm guessing the inner while loop is needed with pStmt contains multiple SQl statements right ? Thanks STev Dennis Cote <[EMAIL PROTECTED]> wrote: Stephen Sutherland wrote: > Hi > > I am using the 'quick start' C-styled code for sqlite3 > http://www.sqlite.org/quickstart.html > I think I'm running into a problem trying to put it in classes to make it > somewhat object oriented. So I'm asking for help about how to make it > object-oriented - or to confirm whether what I'm doing is object oriented. > Here is the code: > > [code] > //callback function > static int callback(void *NotUsed, int argc, char **argv, char **azColName) > { > int i; > for(i=0; i> printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL"); > } > printf("\n"); > return 0; > }; > > // this callback is referenced here. > void MyClass::executeSQLStatement() > { > rc = sqlite3_exec(db, "select * from table1" , callback, 0, &zErrMsg); > }; > > [/code] > > > However I am trying to add a vector in the callback function to store the > results. When I put the vector in it seems I am forced to do something like > this: > > > [code] > vector vecX; > > static int callback(void *NotUsed, int argc, char **argv, char **azColName) > { > int i; > for(i=0; i> printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL"); > } > vecX.push_back(argv[3]); > > printf("\n"); > return 0; > }; > [/code] > Now this doesn't seem object oriented ? > Nor do I understand how I would access this vector from other classes ? > And I don't know how this vector which I created can be considered part of > the class ? it seems to me to only have page scope. > Any advice on how to make my vector object oriented or accessible by other > classes ? > > Thanks in Advance > Stephen > > > - > Pinpoint customers who are looking for what you sell. > Stephen, You should look into using the newer prepare/step API functions instead of the callback mechanism. It will make your code clearer, and will probably execute faster as well. The new API is used to implement the current version of sqlite3_exec that uses the callback mechanism so you can look at that code to see how the new API is used. The following excerpt is from the file legacy.c in the sqlite source. It shows how sqlite uses the new API functions to build the arrays of strings it passes to the callback function. By using the new API functions directly you can avoid the overhead of converting all the database fields into string and building these arrays, only to have your callback function iterate over the string arrays and convert the values back into other types (for non string fields anyway) and then stuff them into vectors. You can extract the fields and store them directly into the vectors you want. /* ** 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( sqlite3 *db, /* The database on which the SQL executes */ const char *zSql, /* 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; const char *zLeftover; sqlite3_stmt *pStmt = 0; char **azCols = 0; int nRetry = 0; int nCallback; if( zSql==0 ) return SQLITE_OK; while( (rc==SQLITE_OK || (rc==SQLITE_SCHEMA && (++nRetry)<2)) && zSql[0] ){ int nCol; char **azVals = 0; pStmt = 0; rc = sqlite3_prepare(db, zSql, -1, &pStmt, &zLeftover); assert( rc==SQLITE_OK || pStmt==0 ); if( rc!=SQLITE_OK ){ continue; } if( !pStmt ){ /* this happens for a comment or white-space */ zSql = zLeftover; continue; } 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; i azCols[i] = (char *)sqlite3_column_name(pStmt, i); } nCallback++; } if( rc==SQLITE_ROW ){ azVals = &azCols[nCol]; for(i=0; i azVals[i] = (char *)sqlite3_column_text(pStmt, i); } } if( xCallback(p
RE: [sqlite] [Visual Basic] How do you work with SQLite?
At 05:25 PM 8/9/2007, you wrote: I use it mainly to manipulate data obtained from an Interbase database. All the data will eventually be dumped to Excel. I use 2 ways to move data from Interbase to SQLite. One, via an ADO recordset after connecting to Interbase with ODBC. This recordset will then be dumped to SQLite via the free VB wrapper from Olaf Schmidt, dhSQLite. Very good and fast wrapper with a rich object model. Two, via a custom Delphi dll that moves data directly from Interbase to SQLite. This is maybe slightly faster than the first method, but not that much difference. So, no ODBC with this method and no ADO recordset. Moving data from SQLite to Excel is all done with dhSQLite. This wrapper can be downloaed from: http://www.thecommon.net/2.html Very much recommended for anybody who works with VB or VBA. RBS RBS, So why can't you manipulate the data in Interbase? It's reasonably fast. Mike - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed incompatible change to SQLite
On 8/9/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > We propose to modify this so > that these routines work across all database > connections in the same process. > > Double plus good for us.
Re: [sqlite] Object Oriented Question About Adding a vector inside the callback function
Igor I tried your solution and can't get it working . Here is my code. The STRANGE problem that I am having is that when it adds to the vector at position 1, the vector contains the object. But when it adds to vector at position 2 using push_back - for some reason the contents at vector 1 is garbage. then when it adds to vector at position 3 using push_back - for some reason position 3 is garabage ? the errors in this situation doesn't seem to follow much logic ? but it's consistent. for example if i run this code and it uses push 4 times, position 1 and 3 might have garbase while position 2 will have the same contents as position 4 ? ? this is some weird stuff i haven't seen before. I'm wondering if the problem is due to the scope of either my QuestionDBStruct or my vector . = static int callback2(void *NotUsed, int argc, char **argv, char **azColName) { int i; for(i=0; ipush(atoi(argv[0] ),atoi(argv[1] ),atoi(argv[2] ), argv[3] , argv[4] ); return 0; }; QuestionDB::QuestionDB() { }; void QuestionDB::push(int b, int c, int v, char* q, char* a) { QuestionDBStruct qbs; qbs.bible_book =1; qbs.bible_chapter =2; qbs.bible_verse=3; qbs.bible_answer ="test"; qbs.bible_question =q; printf("\n** push called \n"); vecQuestions.push_back(qbs); for(int x = 0 ; x < vecQuestions.size(); x++) printf("\nvecQuestion[%i] = %s \n", x, ((QuestionDBStruct)vecQuestions[x]).bible_question ); printf("\n***\n"); }; Igor Mironchick <[EMAIL PROTECTED]> wrote: If I understand you right then try it: static int add_value( void *st, int, char **value, char ** ) { storage_t * storage = (storage_t*) st; st->push( value[ 0 ] ); return SQLITE_OK; }; class storage_t { public: storage_t() : m_db( 0 ) { sqlite3_open( "your_database.db", &m_db ); }; virtual ~storage_t() { sqlite3_close( m_db ); }; void push( const std::string & v ) { m_buff.push_back( v ); } void read_table() { sqlite3_exec( m_db, "SELECT * FROM some_table", add_value, this, NULL ); } private: sqlite3 * m_db; std::vector< std::string > m_buff; }; This is very simple example, but it can help you I think. Stephen Sutherland wrote: > Hi > > I am using the 'quick start' C-styled code for sqlite3 > http://www.sqlite.org/quickstart.html > I think I'm running into a problem trying to put it in classes to make it > somewhat object oriented. So I'm asking for help about how to make it > object-oriented - or to confirm whether what I'm doing is object oriented. > Here is the code: > > [code] > //callback function > static int callback(void *NotUsed, int argc, char **argv, char **azColName) > { > int i; > for(i=0; i> printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL"); > } > printf("\n"); > return 0; > }; > > // this callback is referenced here. > void MyClass::executeSQLStatement() > { > rc = sqlite3_exec(db, "select * from table1" , callback, 0, &zErrMsg); > }; > > [/code] > > > However I am trying to add a vector in the callback function to store the > results. When I put the vector in it seems I am forced to do something like > this: > > > [code] > vector vecX; > > static int callback(void *NotUsed, int argc, char **argv, char **azColName) > { > int i; > for(i=0; i> printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL"); > } > vecX.push_back(argv[3]); > > printf("\n"); > return 0; > }; > [/code] > Now this doesn't seem object oriented ? > Nor do I understand how I would access this vector from other classes ? > And I don't know how this vector which I created can be considered part of > the class ? it seems to me to only have page scope. > Any advice on how to make my vector object oriented or accessible by other > classes ? > > Thanks in Advance > Stephen > > > - > Pinpoint customers who are looking for what you sell. > -- Regards, Igor Mironchick, Intervale © #ICQ 492-597-570 - To unsubscribe, send email to [EMAIL PROTECTED] - - Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out.
Re: [sqlite] Object Oriented Question About Adding a vector inside the callback function
Thanks I guess i have to figure out how to use prepared statements instead of callbacks because i can't get callbacks working as expected. Dennis Cote <[EMAIL PROTECTED]> wrote: Stephen Sutherland wrote: > Hi > > I am using the 'quick start' C-styled code for sqlite3 > http://www.sqlite.org/quickstart.html > I think I'm running into a problem trying to put it in classes to make it > somewhat object oriented. So I'm asking for help about how to make it > object-oriented - or to confirm whether what I'm doing is object oriented. > Here is the code: > > [code] > //callback function > static int callback(void *NotUsed, int argc, char **argv, char **azColName) > { > int i; > for(i=0; i> printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL"); > } > printf("\n"); > return 0; > }; > > // this callback is referenced here. > void MyClass::executeSQLStatement() > { > rc = sqlite3_exec(db, "select * from table1" , callback, 0, &zErrMsg); > }; > > [/code] > > > However I am trying to add a vector in the callback function to store the > results. When I put the vector in it seems I am forced to do something like > this: > > > [code] > vector vecX; > > static int callback(void *NotUsed, int argc, char **argv, char **azColName) > { > int i; > for(i=0; i> printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL"); > } > vecX.push_back(argv[3]); > > printf("\n"); > return 0; > }; > [/code] > Now this doesn't seem object oriented ? > Nor do I understand how I would access this vector from other classes ? > And I don't know how this vector which I created can be considered part of > the class ? it seems to me to only have page scope. > Any advice on how to make my vector object oriented or accessible by other > classes ? > > Thanks in Advance > Stephen > > > - > Pinpoint customers who are looking for what you sell. > Stephen, You should look into using the newer prepare/step API functions instead of the callback mechanism. It will make your code clearer, and will probably execute faster as well. The new API is used to implement the current version of sqlite3_exec that uses the callback mechanism so you can look at that code to see how the new API is used. The following excerpt is from the file legacy.c in the sqlite source. It shows how sqlite uses the new API functions to build the arrays of strings it passes to the callback function. By using the new API functions directly you can avoid the overhead of converting all the database fields into string and building these arrays, only to have your callback function iterate over the string arrays and convert the values back into other types (for non string fields anyway) and then stuff them into vectors. You can extract the fields and store them directly into the vectors you want. /* ** 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( sqlite3 *db, /* The database on which the SQL executes */ const char *zSql, /* 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; const char *zLeftover; sqlite3_stmt *pStmt = 0; char **azCols = 0; int nRetry = 0; int nCallback; if( zSql==0 ) return SQLITE_OK; while( (rc==SQLITE_OK || (rc==SQLITE_SCHEMA && (++nRetry)<2)) && zSql[0] ){ int nCol; char **azVals = 0; pStmt = 0; rc = sqlite3_prepare(db, zSql, -1, &pStmt, &zLeftover); assert( rc==SQLITE_OK || pStmt==0 ); if( rc!=SQLITE_OK ){ continue; } if( !pStmt ){ /* this happens for a comment or white-space */ zSql = zLeftover; continue; } 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; i azCols[i] = (char *)sqlite3_column_name(pStmt, i); } nCallback++; } if( rc==SQLITE_ROW ){ azVals = &azCols[nCol]; for(i=0; i azVals[i] = (char *)sqlite3_column_text(pStmt, i); } } if( xCallback(pArg, nCol, azVals, azCols) ){ rc = SQLITE_ABORT; goto exec_out; } } if( rc!=SQLITE_ROW ){ rc = sqlite3_finalize(pStmt); pStmt = 0; if( rc!=SQLITE_SCHEMA ){ nRetry = 0; zSql = zLeftover; while( isspace((unsigned char)zSql[0]) ) zSql++; } break; } } sqliteFree(azCols); azCols = 0; } HTH Dennis Cote - To unsu
Re: [sqlite] Extra sqlite DB file
Ken <[EMAIL PROTECTED]> wrote: > Whilst running a transaction I noticed the following files were created for > a sqlite database. > What is the last file? How is it used? > > -rw-r--r-- 1 ixion users 6193152 2007-08-09 18:40 bat_412.db > -rw-r--r-- 1 ixion users 11304 2007-08-09 18:40 bat_412.db-journal > -rw-r--r-- 1 ixion users 134 2007-08-09 18:40 bat_412.db-mj514651E0 > The "master journal" file is used to make sure commits to transactions to two or more ATTACHed databases are atomic. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Object Oriented Question About Adding a vector inside the callback function
Stephen Sutherland wrote: Hi I am using the 'quick start' C-styled code for sqlite3 http://www.sqlite.org/quickstart.html I think I'm running into a problem trying to put it in classes to make it somewhat object oriented. So I'm asking for help about how to make it object-oriented - or to confirm whether what I'm doing is object oriented. Here is the code: [code] //callback function static int callback(void *NotUsed, int argc, char **argv, char **azColName) { int i; for(i=0; i // this callback is referenced here. void MyClass::executeSQLStatement() { rc = sqlite3_exec(db, "select * from table1" , callback, 0, &zErrMsg); }; [/code] However I am trying to add a vector in the callback function to store the results. When I put the vector in it seems I am forced to do something like this: [code] vector vecX; static int callback(void *NotUsed, int argc, char **argv, char **azColName) { int i; for(i=0; i printf("\n"); return 0; }; [/code] Now this doesn't seem object oriented ? Nor do I understand how I would access this vector from other classes ? And I don't know how this vector which I created can be considered part of the class ? it seems to me to only have page scope. Any advice on how to make my vector object oriented or accessible by other classes ? Thanks in Advance Stephen - Pinpoint customers who are looking for what you sell. Stephen, You should look into using the newer prepare/step API functions instead of the callback mechanism. It will make your code clearer, and will probably execute faster as well. The new API is used to implement the current version of sqlite3_exec that uses the callback mechanism so you can look at that code to see how the new API is used. The following excerpt is from the file legacy.c in the sqlite source. It shows how sqlite uses the new API functions to build the arrays of strings it passes to the callback function. By using the new API functions directly you can avoid the overhead of converting all the database fields into string and building these arrays, only to have your callback function iterate over the string arrays and convert the values back into other types (for non string fields anyway) and then stuff them into vectors. You can extract the fields and store them directly into the vectors you want. /* ** 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( sqlite3 *db,/* The database on which the SQL executes */ const char *zSql, /* 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; const char *zLeftover; sqlite3_stmt *pStmt = 0; char **azCols = 0; int nRetry = 0; int nCallback; if( zSql==0 ) return SQLITE_OK; while( (rc==SQLITE_OK || (rc==SQLITE_SCHEMA && (++nRetry)<2)) && zSql[0] ){ int nCol; char **azVals = 0; pStmt = 0; rc = sqlite3_prepare(db, zSql, -1, &pStmt, &zLeftover); assert( rc==SQLITE_OK || pStmt==0 ); if( rc!=SQLITE_OK ){ continue; } if( !pStmt ){ /* this happens for a comment or white-space */ zSql = zLeftover; continue; } 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; i
[sqlite] Extra sqlite DB file
Whilst running a transaction I noticed the following files were created for a sqlite database. What is the last file? How is it used? -rw-r--r-- 1 ixion users 6193152 2007-08-09 18:40 bat_412.db -rw-r--r-- 1 ixion users 11304 2007-08-09 18:40 bat_412.db-journal -rw-r--r-- 1 ixion users 134 2007-08-09 18:40 bat_412.db-mj514651E0 Thanks, Ken
RE: [sqlite] [Visual Basic] How do you work with SQLite?
I use it mainly to manipulate data obtained from an Interbase database. All the data will eventually be dumped to Excel. I use 2 ways to move data from Interbase to SQLite. One, via an ADO recordset after connecting to Interbase with ODBC. This recordset will then be dumped to SQLite via the free VB wrapper from Olaf Schmidt, dhSQLite. Very good and fast wrapper with a rich object model. Two, via a custom Delphi dll that moves data directly from Interbase to SQLite. This is maybe slightly faster than the first method, but not that much difference. So, no ODBC with this method and no ADO recordset. Moving data from SQLite to Excel is all done with dhSQLite. This wrapper can be downloaed from: http://www.thecommon.net/2.html Very much recommended for anybody who works with VB or VBA. RBS -Original Message- From: Gilles Ganault [mailto:[EMAIL PROTECTED] Sent: 09 August 2007 22:46 To: sqlite-users@sqlite.org Subject: [sqlite] [Visual Basic] How do you work with SQLite? Hello I was wondering: how do you VB developpers work with SQLite? Currently, I use a variant array that I fill with data from SQLite, and use ComponentOne's grid object to display the data through its LoadArray() method; If/once the data is updated by the user, I write the array back to SQLite. Do you use ADO et al? Other ways to connect VB and SQLite? Thank you. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] incredible slow performance of a trigger
Hi! Here is the table "stocks": CREATE TABLE [stocks] ( [stocks_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [plant] VARCHAR(0) NULL, [material] VARCHAR(0) NULL, [material_description] VARCHAR(0) NULL, [material_type] VARCHAR(0) NULL, [material_batch] VARCHAR(0) NULL, [material_stock_unrestricted] FLOAT NULL, [material_stock_restricted] FLOAT NULL, [material_stock_in_transfer] FLOAT NULL, [material_stock_qlty_insp] FLOAT NULL, [material_stock_blocked] FLOAT NULL, [material_stock_returns] FLOAT NULL, [material_stock_total] FLOAT NULL, [material_stock_units] VARCHAR(0) NULL, [material_value_unrestricted] FLOAT NULL, [material_value_restricted] FLOAT NULL, [material_value_in_transfer] FLOAT NULL, [material_value_qlty_insp] FLOAT NULL, [material_value_blocked] FLOAT NULL, [material_value_returns] FLOAT NULL, [material_value_total] FLOAT NULL, [material_value_units] VARCHAR(0) NULL, [storage_location] VARCHAR(0) NULL ) Regarding indexes, I have them defined... Regarding TEXT/VARCHAR, well, originally it was TEXT, but my front-end (Lazarus) couldn't show text fields in DBGrid, so I changed it to VARCHAR. Varchar(0) instead of Varchar because SQLite Administrator didn't accept varchar without field length:( I really don't understand what is the problem. Maybe I'm missing something obvious? If I delete the trigger and delete where clause from qry_bom_components_input, then both INSERT INTO..SELECT FROM queries (for bom_products and bom_components) work well Regards, Zlatko - Original Message - From: "Marco Antonio Abreu" <[EMAIL PROTECTED]> To: Sent: Thursday, August 09, 2007 10:50 PM Subject: Re: [sqlite] incredible slow performance of a trigger Hi Zlatko, In your commands, I did't find table "stocks" used in view "qry_stocks_sum". In any case, try to use SQLite default types, like REAL and TEXT in place of FLOAT and VARCHAR. The type "VARCHAR(0)" is not indicated for field type and length, please use some thing like "TEXT(40)". Finally, indexes help queries performances. []'s, Marco Antonio Abreu IT Quality Systems [EMAIL PROTECTED] http://www.itquality.com.br Zlatko Matic wrote: Hello. I have terrible performance when executing the following query, which inserts rows from table "products" to table "bom_products": INSERT INTO bom_products ( plant, product, product_description, product_base_qty_units, product_base_qty) SELECT DISTINCT products.plant, products.product, products.product_description, products.product_base_qty_units, products.product_base_qty FROM products ORDER BY products.plant, products.product; The query fires trigger "bom_products_tr_after_row_insert" (see below) that should populate table "bom_components" with corresponding rows for every row in table "bom_products" (bom_products and bom_components are one-to-many). CREATE TABLE [products] ( [products_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [plant] VARCHAR(0) NULL, [product] VARCHAR(0) NULL, [product_description] VARCHAR(0) NULL, [product_base_qty_units] VARCHAR(0) NULL, [product_base_qty] FLOAT NULL ) CREATE TABLE [bills_of_materials] ( [bills_of_materials_id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, [plant] VARCHAR(0) NULL, [product] VARCHAR(0) NULL, [component] VARCHAR(0) NULL, [component_description] VARCHAR(0) NULL, [component_brutto_qty] FLOAT NULL, [component_brutto_qty_units] VARCHAR(0) NULL, [product_base_qty] FLOAT NULL ) CREATE TABLE [bom_products] ( [bom_products_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [plant] VARCHAR(0) NULL, [product] VARCHAR(0) NULL, [product_description] VARCHAR(0) NULL, [product_base_qty_units] VARCHAR(0) NULL, [product_base_qty] FLOAT NULL, [product_target_qty] FLOAT NULL ) CREATE TRIGGER [bom_products_tr_after_row_insert] AFTER INSERT ON [bom_products] FOR EACH ROW BEGIN INSERT INTO bom_components( plant, product, component, component_description, component_brutto_qty, component_brutto_qty_units, product_base_qty, product_target_qty, component_stock_unrestricted, component_stock_restricted, component_stock_qlty_insp, component_stock_blocked, component_stock_in_transfer, component_stock_returns, component_stock_total) SELECT DISTINCT qry_bom_components_input.plant, qry_bom_components_input.product, qry_bom_components_input.component, qry_bom_components_input.component_description, qry_bom_components_input.component_brutto_qty, qry_bom_components_input.component_brutto_qty_units, qry_bom_components_input.product_base_qty, NEW.product_target_qty, qry_bom_components_input.component_stock_unrestricted, qry_bom_components_input.component_stock_restricted, qry_bom_components_input.component_stock_qlty_insp, qry_bom_components_input.component_stock_blocked, qry_bom_components_input.component_stock_in_transfer, qry_bom_com
[sqlite] [Visual Basic] How do you work with SQLite?
Hello I was wondering: how do you VB developpers work with SQLite? Currently, I use a variant array that I fill with data from SQLite, and use ComponentOne's grid object to display the data through its LoadArray() method; If/once the data is updated by the user, I write the array back to SQLite. Do you use ADO et al? Other ways to connect VB and SQLite? Thank you. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Proposed incompatible change to SQLite
> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 09, 2007 8:38 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Proposed incompatible change to SQLite > > We are considering a change to the SQLite API which is > technically not backwards compatible. On the other hand, > it is difficult to imagine a situation where the change > would cause anyone any serious hardship. We suspect that > most people will consider this change a vast improvement. > > The proposed changes is to these APIs: > > sqlite3_soft_heap_limit(); > sqlite3_enable_shared_cache(); > sqlite3_release_memory(); > > As currently implemented (and documented), these > routines only work across database connections in > the same thread. We propose to modify this so > that these routines work across all database > connections in the same process. > > If you think such a change will cause problems for > you, please let me know. Tnx. +1 for this change - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed incompatible change to SQLite
Does this mean that the "shared_cached" connections in two threads can access the same DB cache and database in parallel? I'm all for it, either way it should be an improvement. Thanks DRH for a great product. Ken [EMAIL PROTECTED] wrote: We are considering a change to the SQLite API which is technically not backwards compatible. On the other hand, it is difficult to imagine a situation where the change would cause anyone any serious hardship. We suspect that most people will consider this change a vast improvement. The proposed changes is to these APIs: sqlite3_soft_heap_limit(); sqlite3_enable_shared_cache(); sqlite3_release_memory(); As currently implemented (and documented), these routines only work across database connections in the same thread. We propose to modify this so that these routines work across all database connections in the same process. If you think such a change will cause problems for you, please let me know. Tnx. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Creating index takes over an hour
--- mos <[EMAIL PROTECTED]> wrote: > At 01:42 PM 8/9/2007, you wrote: > >--- mos <[EMAIL PROTECTED]> wrote: > > > Is there no way to allocate more memory to SQLite index buffers like you > > > can with MySQL and most other databases? I suspect SQLite is building the > > > index on disk which will be 100x slower than if it used RAM. The indexing > > > process has used 400MB of RAM so far and it seems not to want any more > > even > > > though there is 800mb free. I could go out and get more RAM, but if SQLite > > > doesn't use it, what's the point? > > > >see the previously mentioned pragma statements: > > > > http://www.mail-archive.com/sqlite-users%40sqlite.org/msg26838.html > > > >Don't discount the speed increase of OS file caching (pre-caching). > >It can make a very big difference. > > Hmmm, I have 2gb RAM on my computer, and the SQLite database (with just > this one table) is 10.5 gb. > It's like trying to fit an elephant into a phone booth. I can try and > squeeze in it, but I don't think it's gonna to fit. > And you're telling me, "Well, PUSH HARDER!" Fair enough. You didn't say how big your database was. If you had, say, 64 bytes per row x 15M rows, it would have fit in RAM. I suggest you use MySQL. > > Mike > > > > > > > > To build the index on a 15 million row table takes 2 hours. I guy can > > drink > > > only so much coffee. Choose the right car based on your needs. Check out Yahoo! Autos new Car Finder tool. http://autos.yahoo.com/carfinder/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] incredible slow performance of a trigger
Hi Zlatko, In your commands, I did't find table "stocks" used in view "qry_stocks_sum". In any case, try to use SQLite default types, like REAL and TEXT in place of FLOAT and VARCHAR. The type "VARCHAR(0)" is not indicated for field type and length, please use some thing like "TEXT(40)". Finally, indexes help queries performances. []'s, Marco Antonio Abreu IT Quality Systems [EMAIL PROTECTED] http://www.itquality.com.br Zlatko Matic wrote: > Hello. > I have terrible performance when executing the following query, which > inserts rows from table "products" to table "bom_products": > INSERT INTO bom_products ( > plant, > product, > product_description, > product_base_qty_units, > product_base_qty) > SELECT DISTINCT >products.plant, >products.product, >products.product_description, >products.product_base_qty_units, >products.product_base_qty > FROM >products > ORDER BY >products.plant, >products.product; > The query fires trigger "bom_products_tr_after_row_insert" (see below) > that should populate table "bom_components" with corresponding rows > for every row in table "bom_products" (bom_products and bom_components > are one-to-many). > > CREATE TABLE [products] ( > [products_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > [plant] VARCHAR(0) NULL, > [product] VARCHAR(0) NULL, > [product_description] VARCHAR(0) NULL, > [product_base_qty_units] VARCHAR(0) NULL, > [product_base_qty] FLOAT NULL > ) > > CREATE TABLE [bills_of_materials] ( > [bills_of_materials_id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, > [plant] VARCHAR(0) NULL, > [product] VARCHAR(0) NULL, > [component] VARCHAR(0) NULL, > [component_description] VARCHAR(0) NULL, > [component_brutto_qty] FLOAT NULL, > [component_brutto_qty_units] VARCHAR(0) NULL, > [product_base_qty] FLOAT NULL > ) > > CREATE TABLE [bom_products] ( > [bom_products_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > [plant] VARCHAR(0) NULL, > [product] VARCHAR(0) NULL, > [product_description] VARCHAR(0) NULL, > [product_base_qty_units] VARCHAR(0) NULL, > [product_base_qty] FLOAT NULL, > [product_target_qty] FLOAT NULL > ) > > CREATE TRIGGER [bom_products_tr_after_row_insert] > AFTER INSERT ON [bom_products] > FOR EACH ROW > BEGIN > > INSERT INTO bom_components( > plant, > product, > component, > component_description, > component_brutto_qty, > component_brutto_qty_units, > product_base_qty, > product_target_qty, > component_stock_unrestricted, > component_stock_restricted, > component_stock_qlty_insp, > component_stock_blocked, > component_stock_in_transfer, > component_stock_returns, > component_stock_total) > SELECT DISTINCT >qry_bom_components_input.plant, >qry_bom_components_input.product, >qry_bom_components_input.component, >qry_bom_components_input.component_description, >qry_bom_components_input.component_brutto_qty, >qry_bom_components_input.component_brutto_qty_units, >qry_bom_components_input.product_base_qty, >NEW.product_target_qty, >qry_bom_components_input.component_stock_unrestricted, >qry_bom_components_input.component_stock_restricted, >qry_bom_components_input.component_stock_qlty_insp, >qry_bom_components_input.component_stock_blocked, >qry_bom_components_input.component_stock_in_transfer, >qry_bom_components_input.component_stock_returns, >qry_bom_components_input.component_stock_total > FROM > qry_bom_components_input > WHERE qry_bom_components_input.product=NEW.product > ORDER BY >qry_bom_components_input.plant, > qry_bom_components_input.product, > qry_bom_components_input.component; > END > > CREATE VIEW qry_bom_components_input > AS > SELECT DISTINCT bills_of_materials.plant AS plant, >bills_of_materials.product AS product, >bills_of_materials.component AS component, >bills_of_materials.component_description AS component_description, >bills_of_materials.component_brutto_qty AS component_brutto_qty, >bills_of_materials.component_brutto_qty_units AS > component_brutto_qty_units, >bills_of_materials.product_base_qty AS product_base_qty, >bills_of_materials.product_base_qty AS product_target_qty, >qry_stocks_sum.material_stock_unrestricted AS >component_stock_unrestricted, >qry_stocks_sum.material_stock_restricted AS >component_stock_restricted, qry_stocks_sum.material_stock_qlty_insp AS >component_stock_qlty_insp, qry_stocks_sum.material_stock_blocked AS >component_stock_blocked, qry_stocks_sum.material_stock_in_transfer AS >component_stock_in_transfer, qry_stocks_sum.material_stock_returns AS >component_stock_returns, qry_stocks_sum.material_stock_total AS >component_stock_total > FROM bills_of_materials bills_of_materials LEFT JOIN qry_stocks_sum >qry_stocks_sum ON bills_of_materials.component = >qry_stocks_sum.material AND bills_of_mater
Re: [sqlite] Re: Re: Updating two tables
Op donderdag 09-08-2007 om 14:01 uur [tijdzone -0400], schreef Igor Tandetnik: > > Yes, I did forget to mention: I would like to wrap the statements into > > one transaction. > Feel free to. Nothing I said would prevent you from doing that. > > Besides, doing an insert, select and again a number > > of > > inserts somehow feels wrong. > You don't need a select. Use sqlite3_last_insert_rowid API. Thanks. I did some googling on the combination sqlite3_last_insert_rowid and transactions and found some old discussions that explained it in such a way that I think I understand how it works now. It basically boiled down to me not realizing that locks are acquired at the time of the insert statement (or earlier). Anyway, thanks for pointing me in the right direction! Best, Koen - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Object Oriented Question About Adding a vector inside the callback function
The formatting on part of my response didn't turn out as I expected. I'll try that part again: Think of a vRecordset vector like this, as a vector of vRecord vectors: vRecord0< Field0, Field1, Field2, FieldN > vRecord1< Field0, Field1, Field2, FieldN > vRecord2< Field0, Field1, Field2, FieldN > vRecord3< Field0, Field1, Field2, FieldN > vRecord4< Field0, Field1, Field2, FieldN > vRecord5< Field0, Field1, Field2, FieldN > vRecord6< Field0, Field1, Field2, FieldN > vRecord7< Field0, Field1, Field2, FieldN > Lee Crain __ -Original Message- From: Lee Crain [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 1:45 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Object Oriented Question About Adding a vector inside the callback function Stephen, I was faced with a similar problem while writing a SQLite API wrapper for our application programmers. My solution was this: > I created a Field class that is a container than can hold ONE of several different data types. > I created a Record class that consists of a vector of Fields and supporting vector access services. (vector vRecord) > I created a vector of Records as my Recordset. (vector vRecordset) Think of a vRecordset vector like this, as a vector of vRecord vectors: vRecord0< Field0, Field1, Field2, FieldN > vRecord1< Field0, Field1, Field2, FieldN > vRecord2< Field0, Field1, Field2, FieldN > vRecord3< Field0, Field1, Field2, FieldN > vRecord4< Field0, Field1, Field2, FieldN > vRecord5< Field0, Field1, Field2, FieldN > vRecord6< Field0, Field1, Field2, FieldN > vRecord7< Field0, Field1, Field2, FieldN > The x axis consists of the Field containers loaded into the vRecord vector. The y axis consists of the vRecord vectors loaded into the vRecordset vector. The Recordset vector is instantiated on the stack in application code and before the SQLite API wrapper call. Then its ADDRESS is passed as an argument to my SQLite API wrapper class method calls. Those calls store the pointer to the vRecordset vector in a static vRecordset vector pointer, then execute the "sqlite3_exec()" function call which triggers the static callback function (at global scope) to read back the data from the SQL queries. The callback function populates a Field class object for each field in the received data. After all fields have been received (1 row per callback), each of the Field class objects is loaded into a vRecord vector which is loaded into the vRecordset vector ("pushback()" calls). I don't see a way to make the callback function non-static. So, I didn't try. OO programming is type specific. That presented a problem in the static callback function because the data coming back is not type specific. So, I solved that problem by creating a Field container that could hold any datatype. For each query executed, I programmed my solution to know exactly the order of (left to right) and the expected datatypes for each field that is returned, so that the callback function can translate the returned data to its correct datatype before loading that data into a Field container. That way, when the application code receives a vRecordset back from a read operation, it doesn't have to deal with the datatypes; they're already correctly set inside each Field container. With the exception of the static parts of my implementation, everything is strictly OO. The breakthrough for me was to create a Field container that could hold any datatype. Now, I have an interface that is not bound to any particular tables or fields, which can receive and hold the data results from any query. Even if our underlying database changes, my SQLite API wrapper source code will not. I hope this helps, Lee Crain -Original Message- From: Stephen Sutherland [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 1:06 PM To: sqlite-users@sqlite.org Subject: [sqlite] Object Oriented Question About Adding a vector inside the callback function Hi I am using the 'quick start' C-styled code for sqlite3 http://www.sqlite.org/quickstart.html I think I'm running into a problem trying to put it in classes to make it somewhat object oriented. So I'm asking for help about how to make it object-oriented - or to confirm whether what I'm doing is object oriented. Here is the code: [code] //callback function static int callback(void *NotUsed, int argc, char **argv, char **azColName) { int i; for(i=0; i
RE: [sqlite] File Permission and Busy Handler Issue in SQLite
--- Kiran <[EMAIL PROTECTED]> wrote: > This make me suspect that there is some point which > I am missing or may be it is a defect in SQLite. I wouldn't necessarily call a product defective simply because it didn't behave in the manner you expected or wished it to. Considering what I've seen when other applications have encountered similar issues, I find it encouraging that the engine simply went to sleep leaving the database intact. > Now suppose my Linux system already has a DB > with "444" permission (say I copied from another > Linux machine) and tried running my application, > then I want to trap the read-only (unable to write) > scenario. This doesn't explain why you expect that the file permissions may change unexpectedly. While modifying the source code to handle the situation is a solution, the *Nix environment already provides these tools for you. Start your application in a shell script that checks the file permissions before launching your application. If you're trying to prevent others from changing the permissions on your database, then create a user strictly for the process accessing the SQLite database, and ensure that the permissions are set prohibiting other users from making modifications to the DB file. Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Object Oriented Question About Adding a vector inside the callback function
Stephen, I was faced with a similar problem while writing a SQLite API wrapper for our application programmers. My solution was this: > I created a Field class that is a container than can hold ONE of several different data types. > I created a Record class that consists of a vector of Fields and supporting vector access services. (vector vRecord) > I created a vector of Records as my Recordset. (vector vRecordset) Think of a vRecordset vector like this, as a vector of vRecord vectors: vRecord0< Field0, Field1, Field2, FieldN > vRecord1< Field0, Field1, Field2, FieldN > vRecord2< Field0, Field1, Field2, FieldN > vRecord3< Field0, Field1, Field2, FieldN > vRecord4< Field0, Field1, Field2, FieldN > vRecord5< Field0, Field1, Field2, FieldN > vRecord6< Field0, Field1, Field2, FieldN > vRecord7< Field0, Field1, Field2, FieldN > The x axis consists of the Field containers loaded into the vRecord vector. The y axis consists of the vRecord vectors loaded into the vRecordset vector. The Recordset vector is instantiated on the stack in application code and before the SQLite API wrapper call. Then its ADDRESS is passed as an argument to my SQLite API wrapper class method calls. Those calls store the pointer to the vRecordset vector in a static vRecordset vector pointer, then execute the "sqlite3_exec()" function call which triggers the static callback function (at global scope) to read back the data from the SQL queries. The callback function populates a Field class object for each field in the received data. After all fields have been received (1 row per callback), each of the Field class objects is loaded into a vRecord vector which is loaded into the vRecordset vector ("pushback()" calls). I don't see a way to make the callback function non-static. So, I didn't try. OO programming is type specific. That presented a problem in the static callback function because the data coming back is not type specific. So, I solved that problem by creating a Field container that could hold any datatype. For each query executed, I programmed my solution to know exactly the order of (left to right) and the expected datatypes for each field that is returned, so that the callback function can translate the returned data to its correct datatype before loading that data into a Field container. That way, when the application code receives a vRecordset back from a read operation, it doesn't have to deal with the datatypes; they're already correctly set inside each Field container. With the exception of the static parts of my implementation, everything is strictly OO. The breakthrough for me was to create a Field container that could hold any datatype. Now, I have an interface that is not bound to any particular tables or fields, which can receive and hold the data results from any query. Even if our underlying database changes, my SQLite API wrapper source code will not. I hope this helps, Lee Crain -Original Message- From: Stephen Sutherland [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 1:06 PM To: sqlite-users@sqlite.org Subject: [sqlite] Object Oriented Question About Adding a vector inside the callback function Hi I am using the 'quick start' C-styled code for sqlite3 http://www.sqlite.org/quickstart.html I think I'm running into a problem trying to put it in classes to make it somewhat object oriented. So I'm asking for help about how to make it object-oriented - or to confirm whether what I'm doing is object oriented. Here is the code: [code] //callback function static int callback(void *NotUsed, int argc, char **argv, char **azColName) { int i; for(i=0; i
Re: [sqlite] Object Oriented Question About Adding a vector inside the callback function
If I understand you right then try it: static int add_value( void *st, int, char **value, char ** ) { storage_t * storage = (storage_t*) st; st->push( value[ 0 ] ); return SQLITE_OK; }; class storage_t { public: storage_t() :m_db( 0 ) { sqlite3_open( "your_database.db", &m_db ); }; virtual ~storage_t() { sqlite3_close( m_db ); }; void push( const std::string & v ) { m_buff.push_back( v ); } void read_table() { sqlite3_exec( m_db, "SELECT * FROM some_table", add_value, this, NULL ); } private: sqlite3 * m_db; std::vector< std::string > m_buff; }; This is very simple example, but it can help you I think. Stephen Sutherland wrote: Hi I am using the 'quick start' C-styled code for sqlite3 http://www.sqlite.org/quickstart.html I think I'm running into a problem trying to put it in classes to make it somewhat object oriented. So I'm asking for help about how to make it object-oriented - or to confirm whether what I'm doing is object oriented. Here is the code: [code] //callback function static int callback(void *NotUsed, int argc, char **argv, char **azColName) { int i; for(i=0; i // this callback is referenced here. void MyClass::executeSQLStatement() { rc = sqlite3_exec(db, "select * from table1" , callback, 0, &zErrMsg); }; [/code] However I am trying to add a vector in the callback function to store the results. When I put the vector in it seems I am forced to do something like this: [code] vector vecX; static int callback(void *NotUsed, int argc, char **argv, char **azColName) { int i; for(i=0; i printf("\n"); return 0; }; [/code] Now this doesn't seem object oriented ? Nor do I understand how I would access this vector from other classes ? And I don't know how this vector which I created can be considered part of the class ? it seems to me to only have page scope. Any advice on how to make my vector object oriented or accessible by other classes ? Thanks in Advance Stephen - Pinpoint customers who are looking for what you sell. -- Regards, Igor Mironchick, Intervale © #ICQ 492-597-570 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Creating index takes over an hour
> Anyone know a simple Windows command line equivalent > of the cat to dev null command above to put a file > into OS cache? The command would be: type filename.db > nul but I'm pretty sure that this does not work the same way under Windows as it does in *Nix. Shape Yahoo! in your own image. Join our Network Research Panel today! http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Creating index takes over an hour
At 01:42 PM 8/9/2007, you wrote: --- mos <[EMAIL PROTECTED]> wrote: > Is there no way to allocate more memory to SQLite index buffers like you > can with MySQL and most other databases? I suspect SQLite is building the > index on disk which will be 100x slower than if it used RAM. The indexing > process has used 400MB of RAM so far and it seems not to want any more even > though there is 800mb free. I could go out and get more RAM, but if SQLite > doesn't use it, what's the point? see the previously mentioned pragma statements: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg26838.html Don't discount the speed increase of OS file caching (pre-caching). It can make a very big difference. Hmmm, I have 2gb RAM on my computer, and the SQLite database (with just this one table) is 10.5 gb. It's like trying to fit an elephant into a phone booth. I can try and squeeze in it, but I don't think it's gonna to fit. And you're telling me, "Well, PUSH HARDER!" Mike > > To build the index on a 15 million row table takes 2 hours. I guy can drink > only so much coffee. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Object Oriented Question About Adding a vector inside the callback function
Hi I am using the 'quick start' C-styled code for sqlite3 http://www.sqlite.org/quickstart.html I think I'm running into a problem trying to put it in classes to make it somewhat object oriented. So I'm asking for help about how to make it object-oriented - or to confirm whether what I'm doing is object oriented. Here is the code: [code] //callback function static int callback(void *NotUsed, int argc, char **argv, char **azColName) { int i; for(i=0; i
[sqlite] Read Only DB
Hi. How can I open DB in read-only mode? -- Regards, Igor Mironchick, Intervale © #ICQ 492-597-570 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Creating index takes over an hour
--- mos <[EMAIL PROTECTED]> wrote: > Is there no way to allocate more memory to SQLite index buffers like you > can with MySQL and most other databases? I suspect SQLite is building the > index on disk which will be 100x slower than if it used RAM. The indexing > process has used 400MB of RAM so far and it seems not to want any more even > though there is 800mb free. I could go out and get more RAM, but if SQLite > doesn't use it, what's the point? see the previously mentioned pragma statements: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg26838.html Don't discount the speed increase of OS file caching (pre-caching). It can make a very big difference. > > To build the index on a 15 million row table takes 2 hours. I guy can drink > only so much coffee. Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on, when. http://tv.yahoo.com/collections/222 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed incompatible change to SQLite
A most welcome modification. On 8/9/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > We are considering a change to the SQLite API which is > technically not backwards compatible. On the other hand, > it is difficult to imagine a situation where the change > would cause anyone any serious hardship. We suspect that > most people will consider this change a vast improvement. > > The proposed changes is to these APIs: > > sqlite3_soft_heap_limit(); > sqlite3_enable_shared_cache(); > sqlite3_release_memory(); > > As currently implemented (and documented), these > routines only work across database connections in > the same thread. We propose to modify this so > that these routines work across all database > connections in the same process. > > If you think such a change will cause problems for > you, please let me know. Tnx. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] Creating index takes over an hour
At 12:10 PM 8/9/2007, you wrote: > > > cat your.db > /dev/null > > > > > Using Windows XP. :-0 > > Anyone know a simple Windows command line equivalent of the cat > to dev null command above to put a file into OS cache? Well, 'type your.db > nul' will do the same thing, though whether or not it will remain in the cache is another question. Is there no way to allocate more memory to SQLite index buffers like you can with MySQL and most other databases? I suspect SQLite is building the index on disk which will be 100x slower than if it used RAM. The indexing process has used 400MB of RAM so far and it seems not to want any more even though there is 800mb free. I could go out and get more RAM, but if SQLite doesn't use it, what's the point? To build the index on a 15 million row table takes 2 hours. I guy can drink only so much coffee. Mike - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Updating two tables
Koen Vermeer <[EMAIL PROTECTED]> wrote: Op donderdag 09-08-2007 om 12:26 uur [tijdzone -0400], schreef Igor Tandetnik: I'm not sure I understand the problem. Can't you insert into A, retrieve the last ID into a variable in your program, then use the value of this variable for all subsequent inserts into B? Yes, I did forget to mention: I would like to wrap the statements into one transaction. Feel free to. Nothing I said would prevent you from doing that. Besides, doing an insert, select and again a number of inserts somehow feels wrong. You don't need a select. Use sqlite3_last_insert_rowid API. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Updating two tables
Op donderdag 09-08-2007 om 12:26 uur [tijdzone -0400], schreef Igor Tandetnik: > I'm not sure I understand the problem. Can't you insert into A, retrieve > the last ID into a variable in your program, then use the value of this > variable for all subsequent inserts into B? Yes, I did forget to mention: I would like to wrap the statements into one transaction. Besides, doing an insert, select and again a number of inserts somehow feels wrong. But if it is the best way, I'll try to put my feelings aside :-) Koen - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed incompatible change to SQLite
Do you also intend to use just a single file descriptor for all connections to the same database file in the same process? --- [EMAIL PROTECTED] wrote: > We are considering a change to the SQLite API which is > technically not backwards compatible. On the other hand, > it is difficult to imagine a situation where the change > would cause anyone any serious hardship. We suspect that > most people will consider this change a vast improvement. > > The proposed changes is to these APIs: > > sqlite3_soft_heap_limit(); > sqlite3_enable_shared_cache(); > sqlite3_release_memory(); > > As currently implemented (and documented), these > routines only work across database connections in > the same thread. We propose to modify this so > that these routines work across all database > connections in the same process. Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out. http://answers.yahoo.com/dir/?link=list&sid=396545469 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] New User: Creating/Modifying tables
Has anybody successfuly ported DISQLite3 from Delphi to Lazarus? - Original Message - From: "Michael Hooker" <[EMAIL PROTECTED]> To: Sent: Thursday, August 09, 2007 9:47 AM Subject: Re: [sqlite] New User: Creating/Modifying tables How do I create/alter tables with a GUI?<< Short of buying some kind of commercial pre-written db manager, you could use the DisqLite3 you already have to write something in Delphi which you already have. All you really need is an edit control into which you put your SQLite3 statements and a button to execute them, along with some more edit controls or a StringGrid to display the output, but you can make it as complicated as you like. For example, how you display the data you extract from the database is entirely up to you and you can reformat your dates however you like. It's not very much harder than using the Delphi data-aware components, though if you only have a personal edition you won't have seen these. Once you've written something useful, then you don't have to write a program every time - you just modify the one you've already written to add new functionality when/if you need it. Statements, database paths and so on can be stored as text files so they can be re-used - just like SQLiteSpy. Check out the example programs that come with DisqLite3. Ralf Junker has put a lot of work into this, so look at the examples for beginners which he recently added - he wrote these at my request when I was stuck, and I'm very grateful to him. If you need assistance with Delphi programming there is no shortage of helpful people on Forums such as Tek-Tips. Obviously, this isn't the place to deal with those issues. Michael Hooker - Original Message - From: "mos" <[EMAIL PROTECTED]> To: Sent: Thursday, August 09, 2007 6:17 AM Subject: [sqlite] New User: Creating/Modifying tables I created my first Delphi program with DISQLite3 1.40 and I used the Importer component to create the table and I imported a few of the rows from MySQL 5. The problem is the dates in the SQLite table show up as floating point, which is how they are natively stored. 1) How can I determine what the date is with SQLiteSpy? I need the dates formatted as '2006-05-11'. Do I have to write a program every time I 2) How do I create/alter tables with a GUI? I can't find that option in SQLiteSpy or SQLite3 Mgr. TIA Mike - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Creating index takes over an hour
> > > cat your.db > /dev/null > > > > > Using Windows XP. :-0 > > Anyone know a simple Windows command line equivalent of the cat > to dev null command above to put a file into OS cache? Well, 'type your.db > nul' will do the same thing, though whether or not it will remain in the cache is another question. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Creating index takes over an hour
Regarding: > > cat your.db > /dev/null > > Using Windows XP. :-0 --- >Anyone know a simple Windows command line equivalent of the cat to dev null command above to put a file into OS cache? >You could write a small C program to do this, I suppose. === I would think: copy /b your.db nul Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games. http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] incredible slow performance of a trigger
Hello. I have terrible performance when executing the following query, which inserts rows from table "products" to table "bom_products": INSERT INTO bom_products ( plant, product, product_description, product_base_qty_units, product_base_qty) SELECT DISTINCT products.plant, products.product, products.product_description, products.product_base_qty_units, products.product_base_qty FROM products ORDER BY products.plant, products.product; The query fires trigger "bom_products_tr_after_row_insert" (see below) that should populate table "bom_components" with corresponding rows for every row in table "bom_products" (bom_products and bom_components are one-to-many). CREATE TABLE [products] ( [products_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [plant] VARCHAR(0) NULL, [product] VARCHAR(0) NULL, [product_description] VARCHAR(0) NULL, [product_base_qty_units] VARCHAR(0) NULL, [product_base_qty] FLOAT NULL ) CREATE TABLE [bills_of_materials] ( [bills_of_materials_id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, [plant] VARCHAR(0) NULL, [product] VARCHAR(0) NULL, [component] VARCHAR(0) NULL, [component_description] VARCHAR(0) NULL, [component_brutto_qty] FLOAT NULL, [component_brutto_qty_units] VARCHAR(0) NULL, [product_base_qty] FLOAT NULL ) CREATE TABLE [bom_products] ( [bom_products_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [plant] VARCHAR(0) NULL, [product] VARCHAR(0) NULL, [product_description] VARCHAR(0) NULL, [product_base_qty_units] VARCHAR(0) NULL, [product_base_qty] FLOAT NULL, [product_target_qty] FLOAT NULL ) CREATE TRIGGER [bom_products_tr_after_row_insert] AFTER INSERT ON [bom_products] FOR EACH ROW BEGIN INSERT INTO bom_components( plant, product, component, component_description, component_brutto_qty, component_brutto_qty_units, product_base_qty, product_target_qty, component_stock_unrestricted, component_stock_restricted, component_stock_qlty_insp, component_stock_blocked, component_stock_in_transfer, component_stock_returns, component_stock_total) SELECT DISTINCT qry_bom_components_input.plant, qry_bom_components_input.product, qry_bom_components_input.component, qry_bom_components_input.component_description, qry_bom_components_input.component_brutto_qty, qry_bom_components_input.component_brutto_qty_units, qry_bom_components_input.product_base_qty, NEW.product_target_qty, qry_bom_components_input.component_stock_unrestricted, qry_bom_components_input.component_stock_restricted, qry_bom_components_input.component_stock_qlty_insp, qry_bom_components_input.component_stock_blocked, qry_bom_components_input.component_stock_in_transfer, qry_bom_components_input.component_stock_returns, qry_bom_components_input.component_stock_total FROM qry_bom_components_input WHERE qry_bom_components_input.product=NEW.product ORDER BY qry_bom_components_input.plant, qry_bom_components_input.product, qry_bom_components_input.component; END CREATE VIEW qry_bom_components_input AS SELECT DISTINCT bills_of_materials.plant AS plant, bills_of_materials.product AS product, bills_of_materials.component AS component, bills_of_materials.component_description AS component_description, bills_of_materials.component_brutto_qty AS component_brutto_qty, bills_of_materials.component_brutto_qty_units AS component_brutto_qty_units, bills_of_materials.product_base_qty AS product_base_qty, bills_of_materials.product_base_qty AS product_target_qty, qry_stocks_sum.material_stock_unrestricted AS component_stock_unrestricted, qry_stocks_sum.material_stock_restricted AS component_stock_restricted, qry_stocks_sum.material_stock_qlty_insp AS component_stock_qlty_insp, qry_stocks_sum.material_stock_blocked AS component_stock_blocked, qry_stocks_sum.material_stock_in_transfer AS component_stock_in_transfer, qry_stocks_sum.material_stock_returns AS component_stock_returns, qry_stocks_sum.material_stock_total AS component_stock_total FROM bills_of_materials bills_of_materials LEFT JOIN qry_stocks_sum qry_stocks_sum ON bills_of_materials.component = qry_stocks_sum.material AND bills_of_materials.plant = qry_stocks_sum.plant ORDER BY bills_of_materials.plant, bills_of_materials.product, bills_of_materials.component, bills_of_materials.component_description, bills_of_materials.component_brutto_qty, bills_of_materials.component_brutto_qty_units, bills_of_materials.product_base_qty, qry_stocks_sum.material_stock_unrestricted, qry_stocks_sum.material_stock_restricted, qry_stocks_sum.material_stock_qlty_insp, qry_stocks_sum.material_stock_blocked, qry_stocks_sum.material_stock_in_transfer, qry_stocks_sum.material_stock_returns, qry_stocks_sum.material_stock_total, bills_of_materials.product_base_qty CREATE VIEW [qry_stocks_sum] AS S
Re: [sqlite] Proposed incompatible change to SQLite
Game on!! We'd love to see this happen. [EMAIL PROTECTED] wrote: We are considering a change to the SQLite API which is technically not backwards compatible. On the other hand, it is difficult to imagine a situation where the change would cause anyone any serious hardship. We suspect that most people will consider this change a vast improvement. The proposed changes is to these APIs: sqlite3_soft_heap_limit(); sqlite3_enable_shared_cache(); sqlite3_release_memory(); As currently implemented (and documented), these routines only work across database connections in the same thread. We propose to modify this so that these routines work across all database connections in the same process. If you think such a change will cause problems for you, please let me know. Tnx. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] - - Park yourself in front of a world of choices in alternative vehicles. Visit the Yahoo! Auto Green Center.
Re: [sqlite] Proposed incompatible change to SQLite
We'll be more than happy with a change like that. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Aug 9, 2007, at 5:37 PM, [EMAIL PROTECTED] wrote: We are considering a change to the SQLite API which is technically not backwards compatible. On the other hand, it is difficult to imagine a situation where the change would cause anyone any serious hardship. We suspect that most people will consider this change a vast improvement. The proposed changes is to these APIs: sqlite3_soft_heap_limit(); sqlite3_enable_shared_cache(); sqlite3_release_memory(); As currently implemented (and documented), these routines only work across database connections in the same thread. We propose to modify this so that these routines work across all database connections in the same process. If you think such a change will cause problems for you, please let me know. Tnx. -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] In-Memory Database: Delete rows on a Table increases the memory usage.
--- "Babu, Lokesh" <[EMAIL PROTECTED]> wrote: > Does anybody knows the exact code, a little hack where can I free up the > memory, I don't want it to use it for future requests. > > Even drop table consumes memory. :-(. > > If we are doing in-memory database operation, why do we want to maintain the > free'd memory pages? On Linux using sqlite 3.4.1: -- start sqlite3 shell -- 2m VIRT, 1m RES pragma temp_store=memory; CREATE TABLE abc(a,b,c); INSERT INTO "abc" VALUES(5400,'some dumb phrase to fill stuff',-345.7); INSERT INTO "abc" VALUES(-1234,'the quick brown fox', 20394203492340.5); insert into abc select * from abc; insert into abc select * from abc; insert into abc select * from abc; insert into abc select * from abc; insert into abc select * from abc; insert into abc select * from abc; insert into abc select * from abc; insert into abc select * from abc; insert into abc select * from abc; insert into abc select * from abc; insert into abc select * from abc; insert into abc select * from abc; insert into abc select * from abc; insert into abc select * from abc; insert into abc select * from abc; insert into abc select * from abc; insert into abc select * from abc; insert into abc select * from abc; insert into abc select * from abc; insert into abc select * from abc; -- 167m VIRT, 166m RES delete from abc where a != b; -- 200m VIRT, 199m RES peak usage during delete -- 167m VIRT, 166m RES after delete vacuum; -- 18m VIRT, 1m RES Are you seeing different results? Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV. http://tv.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Updating two tables
[EMAIL PROTECTED] wrote: Suppose I create two tables: CREATE TABLE tblA ( idA PRIMARY KEY UNIQUE ); CREATE TABLE tblB ( idB PRIMARY KEY UNIQUE, A NOT NULL ); Now, I would like to add a number of rows to tblB, one row to tblA, and map each new row in tblB to the new row in tblA by setting tblB.A to the corresponding tblA.idA. The question is: How? I guess I have to do something with last_insert_rowid(), but I'm not quite sure what exactly... If I do an INSERT INTO tblA, the last_insert_rowid() allows me to find the right idA in tblA. But then after the first INSERT INTO tblB, it changes to reflect the new inserted row, so I cannot use it for the next INSERT INTO tblB, unless I use it to find the right idA in tblB. I'm not sure I understand the problem. Can't you insert into A, retrieve the last ID into a variable in your program, then use the value of this variable for all subsequent inserts into B? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] UNION?
Tom, Thanks for taking the time to document for my benefit more efficient implementations. Lee Crain __ -Original Message- From: T&B [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 4:08 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] UNION? Hi Lee, > This query does not work in SQLite but works in MS SQL Server: > > sqlite> SELECT items_idx, [name], active FROM Items >...> WHERE active = 'T' AND Items.items_idx IN >...> ((SELECT related_item FROM RelatedItems WHERE item = 1777) >...> UNION >...> (SELECT item FROM RelatedItems WHERE related_item = 1777)) >...> ORDER BY Items.name ASC; > SQL error: near "UNION": syntax error Of course the problem is the brackets you have around each SELECT statement, which separates them from the UNION operator. So, it works fine like this: SELECT items_idx, [name], active FROM Items WHERE active = 'T' AND Items.items_idx IN ( SELECT related_item FROM RelatedItems WHERE item = 1777 UNION SELECT item FROM RelatedItems WHERE related_item = 1777 ) ORDER BY Items.name ASC; Also, you may want to consider avoiding performing an IN on a UNION. As far as I know, SQLite doesn't optimize that, so will build the entire union before performing the IN. If you instead do the following, it should be a lot faster (if you have lots of data). But I may be wrong. SELECT items_idx, [name], active FROM Items WHERE active = 'T' AND ( items_idx IN ( SELECT [related_item] FROM RelatedItems WHERE item = 1777 ) OR items_idx IN ( SELECT item FROM RelatedItems WHERE related_item = 1777 ) ) ORDER BY [name] ASC ; or by using a JOIN: SELECT items_idx, [name], active FROM Items JOIN RelatedItems AS RI WHERE active = 'T' AND ( RI.related_item = Items.items_idx AND RI.item = 1777 OR RI.item = Items.items_idx AND RI.related_item = 1777 ) ORDER BY Items.name ASC ; Tom -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Creating index takes over an hour
--- mos <[EMAIL PROTECTED]> wrote: > At 10:19 AM 8/9/2007, you wrote: > >If your database file is less than the size of your RAM, > >then do this before the create index to speed it up: > > > > cat your.db > /dev/null > > Using Windows XP. :-0 Anyone know a simple Windows command line equivalent of the cat to dev null command above to put a file into OS cache? You could write a small C program to do this, I suppose. Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games. http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed incompatible change to SQLite
Definite enhancement without a downside from our perspective. [EMAIL PROTECTED] wrote: We are considering a change to the SQLite API which is technically not backwards compatible. On the other hand, it is difficult to imagine a situation where the change would cause anyone any serious hardship. We suspect that most people will consider this change a vast improvement. The proposed changes is to these APIs: sqlite3_soft_heap_limit(); sqlite3_enable_shared_cache(); sqlite3_release_memory(); As currently implemented (and documented), these routines only work across database connections in the same thread. We propose to modify this so that these routines work across all database connections in the same process. If you think such a change will cause problems for you, please let me know. Tnx. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] UNION?
I agree, Jim, it "would have been nice...". When I first posted about the problem I was having, I had gone through so SQL many experiments trying to get the query to work that had I lost track of which variations I had tried on which DBMS's. I inadvertently posted one of my versions of the query as a representation of the problem that I had tried with MS SQL Server but not yet tried with SQLite. Lee Crain _ -Original Message- From: Jim Dodgen [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 7:59 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] UNION? would have been nice to see the double parentheses in your example ... I have seen MS Access as well being overly parenthesized, what does the SQL92 standard say about that? Jim Lee Crain wrote: > Richard, > > Thanks for suggesting it but no, I don't think invisible control > characters are the problem. I have copied and pasted the query from my > source code into a MS SQL Server Management Studio interface and executed > it against a SQL Server mockup of our SQLite database. It works perfectly. > > I experimented and tried some variations on the query. > > ___ > > This query does not work in SQLite but works in MS SQL Server: > > sqlite> SELECT items_idx, [name], active FROM Items >...> WHERE active = 'T' AND Items.items_idx IN >...> ((SELECT related_item FROM RelatedItems WHERE item = 1777) >...> UNION >...> (SELECT item FROM RelatedItems WHERE related_item = 1777)) >...> ORDER BY Items.name ASC; > SQL error: near "UNION": syntax error > sqlite> > > > > However, this query works in both SQLite and MS SQL Server: > > sqlite> SELECT items_idx, [name], active FROM Items >...> WHERE active = 'T' AND Items.items_idx IN >...> (SELECT related_item FROM RelatedItems WHERE item = 1777 >...> UNION >...> SELECT item FROM RelatedItems WHERE related_item = 1777) >...> ORDER BY Items.name ASC; > 1706|Arizona Character|T > 1707|Arizona Clothing and Props|T > 1660|Arizona Hair|T > 2325|Bonnie V3 Teen|T > 1425|Isabella for Stephanie 3|T > 1918|Little Darling for V3/SP|T > 106|Rose Character|T > 1778|Teresa Hair|T > sqlite> > > > > Further experimentation showed that the extra pair of parentheses in the > first query (around each SELECT statement) caused the syntax error. > > Thanks for your response, > > Lee Crain > > > > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 08, 2007 3:38 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] UNION? > > "Lee Crain" <[EMAIL PROTECTED]> wrote: > >> The query at the bottom of this email is failing on the word "UNION". >> > (The > >> query works correctly in MS SQL Server.) >> > > Works when I try it Do you think you might have some > invisible control characters or something in the middle of > the SQL? > > >> __ >> SELECT Items.items_idx, Items.name, Items.active FROM Items >> WHERE active = 'T' AND Items.items_idx IN >> (SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777 >> UNION >> SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777) >> ORDER BY Items.name ASC; >> >> > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > > > -- --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- --- > > > > -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed incompatible change to SQLite
--- [EMAIL PROTECTED] wrote: > We are considering a change to the SQLite API which is > technically not backwards compatible. On the other hand, > it is difficult to imagine a situation where the change > would cause anyone any serious hardship. We suspect that > most people will consider this change a vast improvement. > > The proposed changes is to these APIs: > > sqlite3_soft_heap_limit(); > sqlite3_enable_shared_cache(); > sqlite3_release_memory(); > > As currently implemented (and documented), these > routines only work across database connections in > the same thread. We propose to modify this so > that these routines work across all database > connections in the same process. This would be a very welcome improvement. Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out. http://answers.yahoo.com/dir/?link=list&sid=396545469 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] In-Memory
Great! Thanks a lot - Uma [EMAIL PROTECTED] wrote: from sqlite3 import dbapi2 as sqlite connection = sqlite.connect(':memory:') cursor.execute(' . . . sql statements . . .') cursor.close() Whatever sql operations you do will be in memory. Once you exit the applcation, DB will be lost Uma Krishnan 08/08/07 10:44 PM Please respond to sqlite-users@sqlite.org To sqlite-users@sqlite.org cc Subject RE: [sqlite] In-Memory Hello, Has anyone used SQLite as an In-Memory implementation. If so would you like to share your ideas? Thanks in advance - Uma *** This e-mail and any attached documents may contain confidential or proprietary information. If you are not the intended recipient, please advise the sender immediately and delete this e-mail and all attached documents from your computer system. Any unauthorized disclosure, distribution or copying hereof is prohibited. ***
Re: [sqlite] Creating index takes over an hour
At 10:19 AM 8/9/2007, you wrote: If your database file is less than the size of your RAM, then do this before the create index to speed it up: cat your.db > /dev/null See also: PRAGMA cache_size = number-of-pages; and PRAGMA page_size = bytes; -- recommend at least 8192 http://www.sqlite.org/pragma.html Using Windows XP. :-0 Mike --- mos <[EMAIL PROTECTED]> wrote: > I'm building my first index on a large table using the "Create index > ix_Main on Table1(StringField1,StringField2,DateField,TimeField)" and so > far it has been running over an hour on a fast computer with 2gb ram. There > are 15 million rows in the table. The same index can be built in MySQL in a > minute or two. Why does it take so long? Building a website is a piece of cake. Yahoo! Small Business gives you all the tools to get online. http://smallbusiness.yahoo.com/webhosting - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Proposed incompatible change to SQLite
We are considering a change to the SQLite API which is technically not backwards compatible. On the other hand, it is difficult to imagine a situation where the change would cause anyone any serious hardship. We suspect that most people will consider this change a vast improvement. The proposed changes is to these APIs: sqlite3_soft_heap_limit(); sqlite3_enable_shared_cache(); sqlite3_release_memory(); As currently implemented (and documented), these routines only work across database connections in the same thread. We propose to modify this so that these routines work across all database connections in the same process. If you think such a change will cause problems for you, please let me know. Tnx. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Creating index takes over an hour
If your database file is less than the size of your RAM, then do this before the create index to speed it up: cat your.db > /dev/null See also: PRAGMA cache_size = number-of-pages; and PRAGMA page_size = bytes; -- recommend at least 8192 http://www.sqlite.org/pragma.html --- mos <[EMAIL PROTECTED]> wrote: > I'm building my first index on a large table using the "Create index > ix_Main on Table1(StringField1,StringField2,DateField,TimeField)" and so > far it has been running over an hour on a fast computer with 2gb ram. There > are 15 million rows in the table. The same index can be built in MySQL in a > minute or two. Why does it take so long? Building a website is a piece of cake. Yahoo! Small Business gives you all the tools to get online. http://smallbusiness.yahoo.com/webhosting - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: auto_increment - why does it do the opposite?
Regarding: " This works, probably because sqlite doesn't enforce column types..." Yes, you can create table ABC ( myCol WARM_AND_FUZZY); And this will be parsed without error. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] File Permission and Busy Handler Issue in SQLite
Kiran, I understand where you are coming from with this. With that said here are my thoughts: 1. It is not sqlite's responsibility to make sure that your db is writable. After all either you/and/or the application has made it not writeable. Sqlite has a reasonable expectation that underlying file permissions are not changed in the middle of a transaction. 2. Other DB vendors do not generally allow the user to modify the underling files nor their permissisions. An error would be generated. The fact that sqlite is throwing a busy error is your error code and allows you to test/check for permissions issues in your application busy handler code. A solution for your case would be to write and install your own busy handler that also performs a file system check of the permissions. You can then at your own discretion either terminate the transaction or reset the permissions appropriately. For unix systems: int chmod(const char *path, mode_t mode); HTH Kiran <[EMAIL PROTECTED]> wrote: All, Let me confess that the topic has changed a bit from "how to solve" to "why should this occur" scenario. Now suppose my Linux system already has a DB with "444" permission (say I copied from another Linux machine) and tried running my application, then I want to trap the read-only (unable to write) scenario. This is one of the few scenarios I can think of. Like this there might be N number of scenarios wherein we may encounter some sort of issue with DB. So my (might I say holistic view) question is if sqlite3_exec is unable to differentiate a busy_handler trigger due to permission other related issues and DB lock, won't we get ourselves into some mis-leading (ambiguity) scenario. Regards, Kiran -Original Message- From: Ken [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 8:00 PM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: RE: [sqlite] File Permission and Busy Handler Issue in SQLite What is the goal of changing permissions? Kiran wrote: The interesting part of the scenario is, when you change the file permission using chmod 000 bulk.db, there is no effect on the process of writing (with sqlite3_exec and busy handler combo) however the same does not work for the chmod 444 bulk.db. This make me suspect that there is some point which I am missing or may be it is a defect in SQLite. Anyways thanks for effort Joe -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 6:53 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] File Permission and Busy Handler Issue in SQLite It is not reasonable to change a database file to read-only in the middle of a transaction. I suppose sqlite could be changed to detect it, but why bother? By the same logic, if another process wrote random bytes in the middle of an sqlite database file, should sqlite be responsible for recovering from that as well? --- Kiran wrote: > I am currently using SQLite in my application as in Multi-process > environment. I.e. my application will contain multi-process which will > handle add/delete request to a single db - (bulk.db). I am using the > busy_handler concept (with 5ms delay) for locking issues. > > However, during testing, when 5 processes is up and running, I by chance > changed the file permission of bulk.db (chmod 444 bulk.db) to read only. Now > I see that the sqlite_exe is not able to trap the read-only mode error of > the database (while it tries to add records) and is just returning a > busy_handler and thus in loop. > > Bottom line, if you are using busy_handler and if DB file permission is > changed to read-only (manually) instead of throwing an error, SQLite will > continue triggering busy_handler. Thus not able to handle this situation. Choose the right car based on your needs. Check out Yahoo! Autos new Car Finder tool. http://autos.yahoo.com/carfinder/ - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] In-Memory Database: Delete rows on a Table increases the memory usage.
Does anybody knows the exact code, a little hack where can I free up the memory, I don't want it to use it for future requests. Even drop table consumes memory. :-(. If we are doing in-memory database operation, why do we want to maintain the free'd memory pages? I think If Mr. Hipp answers, it will be better... :-) On 8/3/07, Christian Smith <[EMAIL PROTECTED]> wrote: > > Scott Derrick uttered: > > > are you saying this is a memory leak? > > sqlite never gives back the unused memory? > > > No, libc never gives back the memory. > > It is not leaked because the malloc implementation keeps a reference to > all the free'd heap memory in tracking it for future requests. > > > > > > Christian Smith wrote: > >> Lokesh Babu uttered: > >> > >>> Hello Folks, > >>> > >>> When I perform the DELETE operation on a Table using In-Memory > Database > >>> (":memory:"), the memory usage increases. > >>> > >>> I tried using PRAGMA auto_vacuum=1; /* result - nothing works */ > >>> Even I tried using VACUUM table_name; /* this too isn't work */ > >>> > >>> if I perform DROP the table operation, memory usage works. Deleting > the > >>> rows > >>> doesn't work. > >>> > >>> Anybody please help me in resolving this. > >> > >> > >> Memory usage goes up because SQLite must temporarily store copies of > the > >> old pages that store the deleted rows, in order to allow a future > rollback > >> if required. > >> > >> Once a commit is done, the old pages are free'd, but by that time the > >> memory footprint has already increased. Not many libc implementations > >> release heap memory back to the OS once it's allocated. > >> > >> > >>> > >>> Thanks in advance, > >>> > >>> Lokee > >>> > >> > >> -- > >> /"\ > >> \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL > >> X - AGAINST MS ATTACHMENTS > >> / \ > >> > >> > >> > - > >> To unsubscribe, send email to [EMAIL PROTECTED] > >> > >> > - > >> > >> > >> > >> > > > > > > -- > /"\ > \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL > X - AGAINST MS ATTACHMENTS > / \ > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] WxDevCpp and Sqlite 3.4.1
[cut] Tnx Clay -- Massimiliano Marini - http://www.linuxtime.it/massimilianomarini/ "It's easier to invent the future than to predict it." -- Alan Kay
Re: [sqlite] Creating index takes over an hour
Hi Mike, i have noticed that things take a lot longer if they aren't surrounded by a BEGIN; do work; COMMIT; does that make a difference for you? Paul On 09/08/07, mos <[EMAIL PROTECTED]> wrote: > I'm building my first index on a large table using the "Create index > ix_Main on Table1(StringField1,StringField2,DateField,TimeField)" and so > far it has been running over an hour on a fast computer with 2gb ram. There > are 15 million rows in the table. The same index can be built in MySQL in a > minute or two. Why does it take so long? > > Mike > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: auto_increment - why does it do the opposite?
ok... That explains how autoincrement works, but it doesn't explain why the AUTO_INCREMENT keyword made it through the parser without raising an error or doing what I would expect - and work as an AUTOINCREMENT table. perhaps, it is the same reason this works: sqlite> create table abc (id not null default 0); sqlite> This works, probably because sqlite doesn't enforce column types... but it would be nice if this sort of syntax error was caught... eg, I had made the mistake of: create table abc (`column integer` not null default 0); and then wondered why " select column from abc; " didn't work ! not essential, just a nice-to-have. thanks Paul On 09/08/07, miguel manese <[EMAIL PROTECTED]> wrote: > Igor has answered this before. Roughly: > > 1. all tables has an implicit integer column named "rowid" that is > auto increment > 2. creating an integer primary key effectively "renames" rowid to that > column, so in your case below fields id and rowid are the same > > IIRC drh replied something else, and since I'm not familiar with the > internals I'm not sure if that is how it is implemented or an > equivalent black-box model of how sqlite works. But since Igor is like > the next authoritative guru here after drh himself, that explanation > should be correct. > > Cheers, > M. Manese > > On 8/9/07, Paul Harris <[EMAIL PROTECTED]> wrote: > > I dislike answering myself, but no sooner I had sent that email, I tried > > this: > > > > sqlite> create table ghi (id integer primary key autoincrement, value text); > > sqlite> insert into ghi (value) values ('justvalue'); > > sqlite> insert into ghi (id,value) values (null,'withid'); > > sqlite> select * from ghi; > > id|value > > 1|justvalue > > 2|withid > > sqlite> > > > > so, why doesn't auto_increment do either of the following more sensible > > things: > > 1) throw an error > > 2) do what "autoincrement" does > > > > ? > > > > thanks > > Paul > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Creating index takes over an hour
I'm building my first index on a large table using the "Create index ix_Main on Table1(StringField1,StringField2,DateField,TimeField)" and so far it has been running over an hour on a fast computer with 2gb ram. There are 15 million rows in the table. The same index can be built in MySQL in a minute or two. Why does it take so long? Mike - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: auto_increment - why does it do the opposite?
Igor has answered this before. Roughly: 1. all tables has an implicit integer column named "rowid" that is auto increment 2. creating an integer primary key effectively "renames" rowid to that column, so in your case below fields id and rowid are the same IIRC drh replied something else, and since I'm not familiar with the internals I'm not sure if that is how it is implemented or an equivalent black-box model of how sqlite works. But since Igor is like the next authoritative guru here after drh himself, that explanation should be correct. Cheers, M. Manese On 8/9/07, Paul Harris <[EMAIL PROTECTED]> wrote: > I dislike answering myself, but no sooner I had sent that email, I tried this: > > sqlite> create table ghi (id integer primary key autoincrement, value text); > sqlite> insert into ghi (value) values ('justvalue'); > sqlite> insert into ghi (id,value) values (null,'withid'); > sqlite> select * from ghi; > id|value > 1|justvalue > 2|withid > sqlite> > > so, why doesn't auto_increment do either of the following more sensible > things: > 1) throw an error > 2) do what "autoincrement" does > > ? > > thanks > Paul > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Updating two tables
Hi, I'm sure this is just a beginner's question, but I have been unable to find the answer elsewhere. As I am indeed just a beginner (wrt SQL/sqlite), please bear with me... Suppose I create two tables: CREATE TABLE tblA ( idA PRIMARY KEY UNIQUE ); CREATE TABLE tblB ( idB PRIMARY KEY UNIQUE, A NOT NULL ); Now, I would like to add a number of rows to tblB, one row to tblA, and map each new row in tblB to the new row in tblA by setting tblB.A to the corresponding tblA.idA. The question is: How? I guess I have to do something with last_insert_rowid(), but I'm not quite sure what exactly... If I do an INSERT INTO tblA, the last_insert_rowid() allows me to find the right idA in tblA. But then after the first INSERT INTO tblB, it changes to reflect the new inserted row, so I cannot use it for the next INSERT INTO tblB, unless I use it to find the right idA in tblB. But that means I'd have to use one statement for the first INSERT INTO tblB and different one for all others, which doesn't feel right. Thanks! Koen - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: auto_increment - why does it do the opposite?
On 09/08/07, Paul Harris <[EMAIL PROTECTED]> wrote: > Hi, > > Can someone please explain why adding the auto_increment term seems to > STOP the auto_increment behaviour? > > $ sqlite3 > SQLite version 3.3.17 > Enter ".help" for instructions > sqlite> .headers on > sqlite> create table abc (id integer auto_increment primary key, value text); > sqlite> insert into abc (value) values ('justvalue'); > sqlite> insert into abc (id,value) values (null,'withid'); > sqlite> select * from abc; > id|value > |justvalue > |withid > sqlite> create table def (id integer primary key, value text); > sqlite> insert into def (value) values ('justvalue'); > sqlite> insert into def (id,value) values (null,'withid'); > sqlite> select * from def; > id|value > 1|justvalue > 2|withid > sqlite> > > Note that abc (the one with auto_increment) does NOT fill in the id > primary key. Baffling to anyone except for me? > > thanks > Paul > I dislike answering myself, but no sooner I had sent that email, I tried this: sqlite> create table ghi (id integer primary key autoincrement, value text); sqlite> insert into ghi (value) values ('justvalue'); sqlite> insert into ghi (id,value) values (null,'withid'); sqlite> select * from ghi; id|value 1|justvalue 2|withid sqlite> so, why doesn't auto_increment do either of the following more sensible things: 1) throw an error 2) do what "autoincrement" does ? thanks Paul - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] auto_increment - why does it do the opposite?
Hi, Can someone please explain why adding the auto_increment term seems to STOP the auto_increment behaviour? $ sqlite3 SQLite version 3.3.17 Enter ".help" for instructions sqlite> .headers on sqlite> create table abc (id integer auto_increment primary key, value text); sqlite> insert into abc (value) values ('justvalue'); sqlite> insert into abc (id,value) values (null,'withid'); sqlite> select * from abc; id|value |justvalue |withid sqlite> create table def (id integer primary key, value text); sqlite> insert into def (value) values ('justvalue'); sqlite> insert into def (id,value) values (null,'withid'); sqlite> select * from def; id|value 1|justvalue 2|withid sqlite> Note that abc (the one with auto_increment) does NOT fill in the id primary key. Baffling to anyone except for me? thanks Paul - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] WxDevCpp and Sqlite 3.4.1
If you want to use a DLL then you should download the DLL and the source package. Create an import library against the DLL with dlltool and put the resulting libsqlite.a into your libs folder. Copy sqlite3.h from the source distribution into your includes folder. If you'd rather be statically linked you'll need to compile the source from the preprocessed windows .zip source distribution into a static library. Not hard to do, just compile all the .c files except the source for the client (notable by containing the function main) into a static library. Perform the same file copying operations as you would for the DLL. Clay Massimiliano Marini wrote: > Hi all, > > Under linux I've no problems with g++ I've compiled sqlite 3.4.1 and > created > a makefile with the parameters > (include,library) where I compile the main.cpp and all is right. > > I want to compile the same file main.cpp under WxDevCpp but I don't know > how > setup sqlite 3.4.1. > > Under linux I've the library libsqlite3.so (generated by the compilation > of > sqlite) but under windows > I don't know how to setup WxDevCpp with sqlite 3.4.1. > > main.cpp is simple (shell) program that create the db file and populate it > with a simple insert. > > Any help, links, suggests are appreciated. > > -- > Massimiliano Marini - http://www.linuxtime.it/massimilianomarini/ > "It's easier to invent the future than to predict it." -- Alan Kay > -- Lazarus Registration http://www.lazarusid.com/registration.shtml - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] UNION?
> Also, you may want to consider avoiding performing an IN on a UNION. > As far as I know, SQLite doesn't optimize that, so will build the > entire union before performing the IN. If you instead do the > following, it should be a lot faster (if you have lots of data). But > I may be wrong. Err... I think the sub-query in an IN clause has to be executed before the outer query can be started, so whether you do two sub-queries and UNION them or two sub-queries and separately check the results wouldn't seem to make much difference to me. Either way, both have to be executed before anything else can be done. -T - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UNION?
Hi Lee, This query does not work in SQLite but works in MS SQL Server: sqlite> SELECT items_idx, [name], active FROM Items ...> WHERE active = 'T' AND Items.items_idx IN ...> ((SELECT related_item FROM RelatedItems WHERE item = 1777) ...> UNION ...> (SELECT item FROM RelatedItems WHERE related_item = 1777)) ...> ORDER BY Items.name ASC; SQL error: near "UNION": syntax error Of course the problem is the brackets you have around each SELECT statement, which separates them from the UNION operator. So, it works fine like this: SELECT items_idx, [name], active FROM Items WHERE active = 'T' AND Items.items_idx IN ( SELECT related_item FROM RelatedItems WHERE item = 1777 UNION SELECT item FROM RelatedItems WHERE related_item = 1777 ) ORDER BY Items.name ASC; Also, you may want to consider avoiding performing an IN on a UNION. As far as I know, SQLite doesn't optimize that, so will build the entire union before performing the IN. If you instead do the following, it should be a lot faster (if you have lots of data). But I may be wrong. SELECT items_idx, [name], active FROM Items WHERE active = 'T' AND ( items_idx IN ( SELECT [related_item] FROM RelatedItems WHERE item = 1777 ) OR items_idx IN ( SELECT item FROM RelatedItems WHERE related_item = 1777 ) ) ORDER BY [name] ASC ; or by using a JOIN: SELECT items_idx, [name], active FROM Items JOIN RelatedItems AS RI WHERE active = 'T' AND ( RI.related_item = Items.items_idx AND RI.item = 1777 OR RI.item = Items.items_idx AND RI.related_item = 1777 ) ORDER BY Items.name ASC ; Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] New User: Creating/Modifying tables
How do I create/alter tables with a GUI?<< Short of buying some kind of commercial pre-written db manager, you could use the DisqLite3 you already have to write something in Delphi which you already have. All you really need is an edit control into which you put your SQLite3 statements and a button to execute them, along with some more edit controls or a StringGrid to display the output, but you can make it as complicated as you like. For example, how you display the data you extract from the database is entirely up to you and you can reformat your dates however you like. It's not very much harder than using the Delphi data-aware components, though if you only have a personal edition you won't have seen these. Once you've written something useful, then you don't have to write a program every time - you just modify the one you've already written to add new functionality when/if you need it. Statements, database paths and so on can be stored as text files so they can be re-used - just like SQLiteSpy. Check out the example programs that come with DisqLite3. Ralf Junker has put a lot of work into this, so look at the examples for beginners which he recently added - he wrote these at my request when I was stuck, and I'm very grateful to him. If you need assistance with Delphi programming there is no shortage of helpful people on Forums such as Tek-Tips. Obviously, this isn't the place to deal with those issues. Michael Hooker - Original Message - From: "mos" <[EMAIL PROTECTED]> To: Sent: Thursday, August 09, 2007 6:17 AM Subject: [sqlite] New User: Creating/Modifying tables I created my first Delphi program with DISQLite3 1.40 and I used the Importer component to create the table and I imported a few of the rows from MySQL 5. The problem is the dates in the SQLite table show up as floating point, which is how they are natively stored. 1) How can I determine what the date is with SQLiteSpy? I need the dates formatted as '2006-05-11'. Do I have to write a program every time I 2) How do I create/alter tables with a GUI? I can't find that option in SQLiteSpy or SQLite3 Mgr. TIA Mike - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -