RE: [sqlite] passing C variable in query
> -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
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.
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?
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
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
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] -
Re: [sqlite] INSERT after creating an INDEX
Frank Fiedler <[EMAIL PROTECTED]> wrote: >assert(sqlite3_step(drop_table_stmt) == SQLITE_DONE); I don't know what is wrong with your program, but I do see that you are misusing assert() - misusing it rather badly. An assert() statement is intended to express an invariant. It is intended to express a fact that is always true about your code. If you compile with -DNDEBUG=1 then assert() becomes a no-op. You should think of an assert() as an executable comment - a comment about your code that is verified to be correct at runtime. Your program should always operate identically if all the assert() statements are removed. There are two problems with your use of assert(). This first is that you are calling sqlite3_step() from within assert(). I don't think you want your sqlite3_step() calls to become no-ops, do you? The second error is that asserts are intended to detect internal inconsistencies within your own code - not external error conditions. So you should never assert() that the return value from sqlite3_step() is going to be SQLITE_DONE unless SQLite promises that it will always return that value. And it makes no such promise. You might also get SQLITE_ERROR, or SQLITE_BUSY. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] passing C variable in query
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?
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
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", &m_sqlite);
- Issue begin-transaction:
std::string begin_transaction_query("BEGIN TRANSACTION;");
sqlite3_prepare(m_sqlite, begin_transaction_query.c_str(),
begin_transaction_query.size(), &m_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(),
&m_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(), &drop_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(), &drop_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(), &create_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(),
&m_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
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
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] -
