Re: [sqlite] Re: INSERT after creating an INDEX

2007-09-13 Thread Frank Fiedler

Hi Igor,

All prepared statements become invalid when the database schema 
changes. You need to finalize your statement and prepare it again. 
Alternatively, if you are using new enough version of SQLite, use 
sqlite3_prepare_v2 to prepare your statements: it stores the original 
query string and automatically re-prepares the statement when schema 
You are absolutely right, thanks for the hint! This solved indeed the 

Have a nice day,

To unsubscribe, send email to [EMAIL PROTECTED]

Re: [sqlite] INSERT after creating an INDEX

2007-09-13 Thread Frank Fiedler

Hi Simon,

On 3.4.2 built from amalgamation on XP using Visual Studio I can
follow your scenario without problems. I used command line shell and
tcl shell.

More detail will help (version etc).

Ok...let's try the next level of detail:
- We are using sqlite 3.4.0 on linux (Redhat Enterprise 3, Update 4)

Please find below a summary of actions we do on C++ and TCL side:

- Open data base : sqlite3_open("TableName", _sqlite);
- Issue begin-transaction:
  std::string begin_transaction_query("BEGIN TRANSACTION;");
  sqlite3_prepare(m_sqlite, begin_transaction_query.c_str(), 
begin_transaction_query.size(), _begin_transaction_stmt, NULL);

- Create table:
  std::string commit_query("COMMIT;");
  sqlite3_prepare(m_sqlite, commit_query.c_str(), commit_query.size(), 
_commit_stmt, NULL);

  if (have_pending_data) {

  std::string drop_table_query("DROP TABLE IF EXISTS '" + name + "';");
  sqlite3_stmt* drop_table_stmt;
  sqlite3_prepare(m_sqlite, drop_table_query.c_str(), 
drop_table_query.size(), _table_stmt, NULL);

  assert(sqlite3_step(drop_table_stmt) == SQLITE_DONE);

  // Drop index
  std::string drop_index_query("DROP INDEX IF EXISTS '" + name + 

  sqlite3_stmt* drop_index_stmt;
  sqlite3_prepare(m_sqlite, drop_index_query.c_str(), 
drop_index_query.size(), _index_stmt, NULL);

  assert(sqlite3_step(drop_index_stmt) == SQLITE_DONE);

  // Create table
  std::string create_table_query("CREATE TABLE '" + name + "' (" + 
schema + ")");

  sqlite3_stmt* create_table_stmt;
  sqlite3_prepare(m_sqlite, create_table_query.c_str(), 
create_table_query.size(), _table_stmt, NULL);

  assert(sqlite3_step(create_table_stmt) == SQLITE_DONE);

- Insert data:
  while (fill_with_data) {
 sqlite3_stmt* stmt = getInsertQuery(2);
 sqlite3_bind_int64(stmt, 1, a);
 sqlite3_bind_text(stmt, 2, b.c_str(), b.size(), SQLITE_TRANSIENT);
 assert(sqlite3_step(stmt) == SQLITE_DONE);
 if (should_synchronize) {
assert(sqlite3_step(m_commit_stmt) == SQLITE_DONE);
assert(sqlite3_step(m_begin_transaction_stmt) == SQLITE_DONE);

- Flush data to file:
  // for flushing the data to a file we do a commit
  std::string commit_query("COMMIT;");
  sqlite3_prepare(m_sqlite, commit_query.c_str(), commit_query.size(), 
_commit_stmt, NULL);



- Create index from within TCL
  # now we create an index from within a tcl script
  # Open data base from within tcl
  sqlite3 my_db "my_data_base.db"
  # Make index
  my_db eval { CREATE INDEX IF NOT EXISTS memory_access_pc ON 'memory 
access monitor'(pc) }

  # We never explicitely close this db from within tcl

When now inserting data from within the C++ world, it exits with the 
SQLITE_ERROR as mentioned earlier.

I hope this level of detailed was not too fine grain.
Thanks for your help in advance,

To unsubscribe, send email to [EMAIL PROTECTED]

[sqlite] INSERT after creating an INDEX

2007-09-13 Thread Frank Fiedler

Hello everybody,

I have a problem inserting elements into a data base (from process1 
accessing the data base) after creating an INDEX on a table (from 
another process accessing the data base).

I have the following setup:
- I have two processes accessing a single sqlite data base. process1 
accesses the data base via the C++ interface, process2 accesses it via 
the tcl interface of sqlite.
- process1 creates the data base, creates a single table in the data 
base and fills this table with life. This is done via INSERT commands.
- process2 displays the data comprised in the data base. process2 can 
read the data from the data base without problems but after creating an 
INDEX process1 is not able to do an INSERT to the table in the data base 
any more.

The error I get is the following:
"SQLITE_ERROR : SQL logic error or missing database"

The index I created is an index on a single row of the table in the data 

Has anyone a clue what I'm doing wrong. Any feedback is welcome.

To unsubscribe, send email to [EMAIL PROTECTED]