RE: [sqlite] passing C variable in query

2007-09-13 Thread James Dennett
> -Original Message-
> From: Yuriy Martsynovskyy [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 13, 2007 11:45 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] passing C variable in query
> 
> Hi Nishit,
> 
> > can anyone tell me the syntax of passing a C variable in a query.
> > i have taken a variable as float hd= 2000.0;
> > how it'll be passed in a query and what'll be the syntax of that
query.
> 
> First use sprintf () to insert your variable into SQL code. Then
> execute the resulting SQL

That's just about tolerable for numeric values, but in general this is
the road to SQL injection attacks and other related bugs; prefer the
prepared statement APIs and bind values to placeholders.

-- James


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



Re: [sqlite] passing C variable in query

2007-09-13 Thread Yuriy Martsynovskyy
Hi Nishit,

> can anyone tell me the syntax of passing a C variable in a query.
> i have taken a variable as float hd= 2000.0;
> how it'll be passed in a query and what'll be the syntax of that query.

First use sprintf () to insert your variable into SQL code. Then
execute the resulting SQL

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



[sqlite] Flipping fts1/2 to fts3.

2007-09-13 Thread Scott Hess
After being out of the office for a bit, I came back and thought "My
to-do list is too long."  So instead of working on the hardest bit, I
decided to see how many pieces of low-hanging fruit I could knock
off...

So today I'm getting rid of all these to-do items around fts1/2 rowid
breakage and fts3 rowid fixage.  The changes are:

 - fts1.c will now refuse to build.  If you still want to build it,
   define SQLITE_ENABLE_BROKEN_FTS1.  This was done to make certain
   that nobody is unintentially using it.
 - Likewise with fts2.c.
 - fts3 should work exactly the same as fts2 or fts1, with the
   following changes:
   - the table-named column is now marked HIDDEN.  This means that you
 can now do 'SELECT * FROM t' or 'INSERT INTO t VALUES (...)'
 against an fts3 table, rather than always being required to
 enumerate things.
   - I've added a new implicit column, docid, which is an alias of the
 fts3 table's rowid.  This is meant to mimic how rowid works when
 you explicitely alias it.  I would like to encourage using docid
 rather than rowid, to reduce the chance for future confusion
 around these issues.  docid is also marked HIDDEN, but should be
 usable exactly as rowid.

Building fts3 should be exactly the same as building fts2, except with
the different pathnames.  I'm going to try to get some traction on
refactoring the wiki pages this afternoon.

Thanks for your patience,
scott

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



RE: [sqlite] How is the Index table in sqlite?

2007-09-13 Thread B V, Phanisekhar
Sorry for the spelling mistake in the subject.

Regards,
Phani

-Original Message-
From: B V, Phanisekhar 
Sent: Thursday, September 13, 2007 3:53 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] How is the Index stable in sqlite?

Database configuration:

Tables:

Create table maintable(column1 INTEGER, column2 INTEGER, column3
INTEGER).

Indices:

Create index column1idx on maintable (column1);

 

How does the index table look like in sqlite?

 

c-r

1-3

3-4

3-6

3-7

5-1

5-8

6-2

 

Or 

 

c-r...

 

1->3

3->4-6-7

5->1-8

6-2

 

I believe it looks like the first one. And if I am right, I would like
to know the problem with the second (which seems memory efficient)?

 

Regards,

Phani


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



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 
changes.
You are absolutely right, thanks for the hint! This solved indeed the 
problem.


Have a nice day,
Frank.


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



[sqlite] Re: INSERT after creating an INDEX

2007-09-13 Thread Igor Tandetnik

Frank Fiedler <[EMAIL PROTECTED]> wrote:

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

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


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


Igor Tandetnik 



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



[sqlite] passing C variable in query

2007-09-13 Thread nishit sharma
Hi All.

can anyone tell me the syntax of passing a C variable in a query.
i have taken a variable as float hd= 2000.0;
how it'll be passed in a query and what'll be the syntax of that query.

regards
Nishit


[sqlite] How is the Index stable in sqlite?

2007-09-13 Thread B V, Phanisekhar
Database configuration:

Tables:

Create table maintable(column1 INTEGER, column2 INTEGER, column3
INTEGER).

Indices:

Create index column1idx on maintable (column1);

 

How does the index table look like in sqlite?

 

c-r

1-3

3-4

3-6

3-7

5-1

5-8

6-2

 

Or 

 

c-r...

 

1->3

3->4-6-7

5->1-8

6-2

 

I believe it looks like the first one. And if I am right, I would like
to know the problem with the second (which seems memory efficient)?

 

Regards,

Phani



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:
C++

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

  sqlite3_step(m_begin_transaction_stmt);
  sqlite3_reset(m_begin_transaction_stmt);
- 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) {
 check_step(sqlite3_step(m_commit_stmt),m_sqlite);
 check_result(sqlite3_reset(m_commit_stmt),m_sqlite);
  }

  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);
  sqlite3_finalize(drop_table_stmt);

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

  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);
  sqlite3_finalize(drop_index_stmt);

  // 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);
  sqlite3_finalize(create_table_stmt);

- 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);
 sqlite3_reset(stmt);
 if (should_synchronize) {
assert(sqlite3_step(m_commit_stmt) == SQLITE_DONE);
sqlite3_reset(m_commit_stmt);
assert(sqlite3_step(m_begin_transaction_stmt) == SQLITE_DONE);
sqlite3_reset(m_begin_transaction_stmt);
  }

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

  check_step(sqlite3_step(m_commit_stmt),m_sqlite);
  sqlite3_reset(m_commit_stmt);


TCL

- 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,
Frank.


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



Re: [sqlite] INSERT after creating an INDEX

2007-09-13 Thread Simon Davies
Hi Frank,

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

Rgds,
Simon

On 13/09/2007, Frank Fiedler <[EMAIL PROTECTED]> wrote:
> 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
> base.
>
> Has anyone a clue what I'm doing wrong. Any feedback is welcome.
> Thanks,
> Frank.
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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


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


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