[sqlite] Threading issues in SQLite

[Bharath Booshan L]

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

[Igor Mironchick]

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; ipush(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", _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, );
};

[/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 

Re: [sqlite] Object Oriented Question About Adding a vector inside the callback function

[Stephen Sutherland]

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, );
> };
> 
> [/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, , );
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_NullCallback)) ){
if( 0==nCallback ){
for(i=0; i azCols[i] = (char *)sqlite3_column_name(pStmt, i);
}
nCallback++;
}
if( rc==SQLITE_ROW ){
azVals = [nCol];
for(i=0; i azVals[i] = (char *)sqlite3_column_text(pStmt, i);
}
}
if( xCallback(pArg, nCol, azVals, azCols) ){
rc = 

RE: [sqlite] [Visual Basic] How do you work with SQLite?

[mos]

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

[Andrew Finkenstadt]

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

[Stephen Sutherland]

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", _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, );
> };
> 
> [/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

[Stephen Sutherland]

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, );
> };
> 
> [/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, , );
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_NullCallback)) ){
if( 0==nCallback ){
for(i=0; i azCols[i] = (char *)sqlite3_column_name(pStmt, i);
}
nCallback++;
}
if( rc==SQLITE_ROW ){
azVals = [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 unsubscribe, send email to [EMAIL 

Re: [sqlite] Extra sqlite DB file

[drh]

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

[Dennis Cote]

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; iflags_NullCallback)) ){

   if( 0==nCallback ){
 for(i=0; i

[sqlite] Extra sqlite DB file

[Ken]

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?

[RB Smissaert]

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

[Zlatko Matic]

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,
   

[sqlite] [Visual Basic] How do you work with SQLite?

[Gilles Ganault]

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

[Robert Simpson]

> -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

[Ken]

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

[Joe Wilson]

--- 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

[Marco Antonio Abreu]

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 

Re: [sqlite] Re: Re: Updating two tables

[Koen Vermeer]

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

[Lee Crain]

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

[Dwight Ingersoll]

--- 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

[Lee Crain]

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

[Igor Mironchick]

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", _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

Re: [sqlite] Creating index takes over an hour

[Dwight Ingersoll]

> 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

[mos]

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

[Stephen Sutherland]

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

[Igor Mironchick]

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

[Joe Wilson]

--- 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

[Ian Frosst]

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

[mos]

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

[Igor Tandetnik]

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

[Koen Vermeer]

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

[Joe Wilson]

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=396545469

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] New User: Creating/Modifying tables

[Zlatko Matic]

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

[Brad Stiles]

> > >   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

[Griggs, Donald]

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

[Zlatko Matic]

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

Re: [sqlite] Proposed incompatible change to SQLite

[Mike Johnston]

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

[Marco Bambini]

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.

[Joe Wilson]

--- "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

[Igor Tandetnik]

[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?

[Lee Crain]

Tom,

Thanks for taking the time to document for my benefit more efficient
implementations.

Lee Crain

__

-Original Message-
From: T [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

[Joe Wilson]

--- 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

[John Stanton]

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?

[Lee Crain]

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

[Joe Wilson]

--- [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=396545469

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] In-Memory

[Uma Krishnan]

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

[mos]

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

[drh]

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

[Joe Wilson]

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?

[Griggs, Donald]

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

[Ken]

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.

[Babu, Lokesh]

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

[Massimiliano Marini]

[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

[Paul Harris]

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?

[Paul Harris]

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

[mos]

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?

[miguel manese]

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

[koen]

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?

[Paul Harris]

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?

[Paul Harris]

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

[Clay Dowling]

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?

[Tom Briggs]

 

> 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?

[T]

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

[Michael Hooker]

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]
-

[sqlite] WxDevCpp and Sqlite 3.4.1

[Massimiliano Marini]

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