Re: [sqlite] a c++ newbie question
Hi Stev, Prepared statements are best option, however try sqlite3_mprintf() with '%q' as format specifier which escapes every '\' character. Find more info in http://sqlite.org/capi3ref.html. Bharath Booshan L. On 8/6/07 11:50 AM, "Stephen Sutherland" <[EMAIL PROTECTED]> wrote: > Hi ; > > I am trying to treat a string before passing it through my SQL statement > into the database. > > I know that a single apostrophe will break the SQL statement. > So I have to replace them all to double apostrophes. > Question #1: > What may I ask is the c or C++ code to accomplish that ? > Initially I was using this simple scheme. > > string str2("stephen's test . Bob's test"); > > if ( str2.find("'"!= string::npos) > { > str2.replace(str2.find('"), 1, "''"); > } > I know this doens't test for multiple single apostrophes. > > But are there any other characters that will break the SQL statement ? > > Has anyone created a nice algorithm? > I actually have a situation where the user creates an XML file and the > contents of the XML file gets dumped in the database. So there is opportunity > for a hacker to create an XML file which has some SQL statements in it like ' > DELETE TABLE X ; > > So any thoughts or existing code would be great. > > Thanks > > Stev > > > - > Luggage? GPS? Comic books? > Check out fitting gifts for grads at Yahoo! Search. --- 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] a c++ newbie question
Hi Stev, Why not just use prepared statements? Regards, Eugene Wee Stephen Sutherland wrote: Hi ; I am trying to treat a string before passing it through my SQL statement into the database. I know that a single apostrophe will break the SQL statement. So I have to replace them all to double apostrophes. Question #1: What may I ask is the c or C++ code to accomplish that ? Initially I was using this simple scheme. string str2("stephen's test . Bob's test"); if ( str2.find("'"!= string::npos) { str2.replace(str2.find('"), 1, "''"); } I know this doens't test for multiple single apostrophes. But are there any other characters that will break the SQL statement ? Has anyone created a nice algorithm? I actually have a situation where the user creates an XML file and the contents of the XML file gets dumped in the database. So there is opportunity for a hacker to create an XML file which has some SQL statements in it like ' DELETE TABLE X ; So any thoughts or existing code would be great. Thanks Stev - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] a c++ newbie question
On 8/5/07, Stephen Sutherland <[EMAIL PROTECTED]> wrote: > I am trying to treat a string before passing it through my SQL statement > into the database. > > I know that a single apostrophe will break the SQL statement. > So I have to replace them all to double apostrophes. > But are there any other characters that will break the SQL statement ? > I actually have a situation where the user creates an XML file and the > contents of the XML file gets dumped in the database. So there is opportunity > for a hacker to create an XML file which has some SQL statements in it like ' > DELETE TABLE X ; > > So any thoughts or existing code would be great. Don't attempt to treat strings at all. Instead, always use the parametric binding API for whatever database you're using. You prepare statements like "INSERT INTO table VALUES (?)", and then pass in the input string as a separate argument for the database engine to put in place of the "?". This avoids the entire problem of escaping special characters, and you don't need to treat your input data specially. For sqlite, use sqlite3_prepare_v2() and sqlite3_bind_text(). http://sqlite.org/capi3.html should get you up to speed on the process, and browse through the other documents on the site for more information. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] a c++ newbie question
Hi ; I am trying to treat a string before passing it through my SQL statement into the database. I know that a single apostrophe will break the SQL statement. So I have to replace them all to double apostrophes. Question #1: What may I ask is the c or C++ code to accomplish that ? Initially I was using this simple scheme. string str2("stephen's test . Bob's test"); if ( str2.find("'"!= string::npos) { str2.replace(str2.find('"), 1, "''"); } I know this doens't test for multiple single apostrophes. But are there any other characters that will break the SQL statement ? Has anyone created a nice algorithm? I actually have a situation where the user creates an XML file and the contents of the XML file gets dumped in the database. So there is opportunity for a hacker to create an XML file which has some SQL statements in it like ' DELETE TABLE X ; So any thoughts or existing code would be great. Thanks Stev - Luggage? GPS? Comic books? Check out fitting gifts for grads at Yahoo! Search.
[sqlite] sqlite as server queries
hi group, i have several small questions for the group any experiences or thoughts shared would be greatly appreciated. 1) anybody used sqlite as a sql server? i'm thinking of say using the embedded sqlite in PHP5 or similar. 2) anybody ever implemented something like a single process of sqlite doing queries for a lot of networked clients? 3) how big has your sqlite database grown? have you had any trouble managing the db? any bad experiences as to stability of the db file? i am asking all of these is because i'm seriously considering sqlite to be used as my sql server for a project. how i'll implement it looks something like this: components of app: embedded web server sqlite some scripting language there will only be one sqlite process which will be forked when the web server is launched. queries to sqlite will be sent to the sqlite process via sockets. i can see that the queries will be sequential. no problems there. i'm not worried with speed at the moment. i just want to know if this has been done before and i'd like to solicit wisdom from the group. thank you. ./e -- no sig - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] QT4 ubuntu sqlite driver problem ? (was Re: [sqlite] Weird error)
John Stanton a écrit : Alain Bertrand wrote: In a Qt4 application, I have the following bit of code : QString s; bool b; QSqlQuery q; QSqlError err; s= "CREATE TABLE ttd_bdata (sheetId integer primary key,"; s+="creation_date date, modif_date date,"; s+="speciesId integer,USDA_zone integer,"; s+="generalities text,propagation text,care text,"; s+="esthetics text, phyto text,miscellaneous text)"; //s="SELECT * FROM ttd_species WHERE speciesId=4"; qWarning( s.toAscii() ); b=q.exec( s ); Executing the query fails with the following information SQL logic error or missing database Unable to fetch row Err number 1. I have found the solution in the qt4 doc : - The driver is locked for updates while a select is executed. This may cause problems when using QSqlTableModel because Qt's item views fetch data as needed (with QSqlQuery::fetchMore() in the case of QSqlTableModel). To avoid the problem, one may use the clear() method of the QSqlQuery. Alain - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] [SQLite improve productivity]
Thx, very helpfull reply. One more question: is it need to do "END" after "BEGIN" or enought "COMMIT"? By the way, some edition of sql query improve productivity a little bit too: // Columns in 'data' table defined like (INTEGER, INTEGER, INTEGER, TEXT) char * sql = sqlite3_mprintf( "INSERT INTO data VALUES( '%q', '%q', '%q', '%q' )", m_sources_map[ it->m_source ].m_sid.c_str(), sec.c_str(), usec.c_str(), it->m_value.c_str() ); was changed with: // Columns in 'data' table defined like (INTEGER, INTEGER, INTEGER, TEXT) char * sql = sqlite3_mprintf( "INSERT INTO data VALUES( %d, %d, %d, '%s' )", m_sources_map[ it->m_source ].m_sid, it->m_time.sec(), it->m_time.usec(), it->m_value.c_str() ); where m_sources_map[ it->m_source ].m_sid is long now. A common issue of high latency transactions. SQLite has a high per-transaction overhead, which can be amortized across multiple INSERTs or UPDATEs to improve the average INSERT rate. You are doing a single INSERT per transaction, so wrap multiple INSERTs inside a single "BEGIN" ... "END" transaction. See: http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations Christian -- Regards, Igor Mironchick, Intervale © #ICQ 492-597-570 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] [SQLite improve productivity]
Thx, very helpfull reply. One more question: is it need to do "END" after "BEGIN" or enought "COMMIT"? By the way, some edition of sql query improve productivity a little bit too: // Columns in 'data' table defined like (INTEGER, INTEGER, INTEGER, TEXT) char * sql = sqlite3_mprintf( "INSERT INTO data VALUES( '%q', '%q', '%q', '%q' )", m_sources_map[ it->m_source ].m_sid.c_str(), sec.c_str(), usec.c_str(), it->m_value.c_str() ); was changed with: // Columns in 'data' table defined like (INTEGER, INTEGER, INTEGER, TEXT) char * sql = sqlite3_mprintf( "INSERT INTO data VALUES( %d, %d, %d, '%s' )", m_sources_map[ it->m_source ].m_sid, it->m_time.sec(), it->m_time.usec(), it->m_value.c_str() ); A common issue of high latency transactions. SQLite has a high per-transaction overhead, which can be amortized across multiple INSERTs or UPDATEs to improve the average INSERT rate. You are doing a single INSERT per transaction, so wrap multiple INSERTs inside a single "BEGIN" ... "END" transaction. See: http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations Christian -- Regards, Igor Mironchick, Intervale © #ICQ 492-597-570 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: [2.8.x] Incrementing a column automatically with INSERT/UPDATE?
At 02:21 06/08/2007 +0200, you wrote: I'm sorry to bother you again, but I can't figure out how to write a trigger that will set a column to 1 when a user creates a new record :-/ Found it in "Sam's Sqlite (2004)": CREATE TRIGGER insert_mytable_timestamp AFTER INSERT ON mytable BEGIN UPDATE mytable SET timestamp = 1 WHERE rowid=new.rowid; END; I think it'd be a good thing to add examples in the page: http://sqlite.org/lang_createtrigger.html G. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: [2.8.x] Incrementing a column automatically with INSERT/UPDATE?
Hello At 15:28 04/08/2007 -0400, you wrote: UPDATE mytable SET timestamp = old.timestamp + 1 WHERE rowid=new.rowid; I'm sorry to bother you again, but I can't figure out how to write a trigger that will set a column to 1 when a user creates a new record :-/ I tried the following but neither works: CREATE TRIGGER insert_mytable_timestamp INSERT ON mytable BEGIN INSERT INTO mytable (timestamp) values (1); END; CREATE TRIGGER insert_mytable_timestamp INSERT ON mytable BEGIN UPDATE mytable SET timestamp = 1 WHERE rowid=new.rowid; END; Thanks, - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] [SQLite improve productivity]
A common issue of high latency transactions. SQLite has a high per-transaction overhead, which can be amortized across multiple INSERTs or UPDATEs to improve the average INSERT rate. You are doing a single INSERT per transaction, so wrap multiple INSERTs inside a single "BEGIN" ... "END" transaction. See: http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations Christian Igor Mironchick uttered: Hi. How can I improve productivity of this code: a_messenger_t::data_buff_t --> std::deque< some_struct > // char * errors = 0; for( a_messenger_t::data_buff_t::const_iterator it = msg.m_buff.begin(), last = msg.m_buff.end(); it != last; ++it ) { // Converting long to std::string... std::string sec( itos( it->m_time.sec() ) ); std::string usec( itos( it->m_time.usec() ) ); // Columns in 'data' table defined like (INTEGER, INTEGER, INTEGER, TEXT) char * sql = sqlite3_mprintf( "INSERT INTO data VALUES( '%q', '%q', '%q', '%q' )", m_sources_map[ it->m_source ].m_sid.c_str(), sec.c_str(), usec.c_str(), it->m_value.c_str() ); // m_db --> sqlite3* int ret = sqlite3_exec( m_db, sql, 0, 0, &errors ); if( ret != SQLITE_OK ) { ACE_DEBUG( ( LM_INFO, ACE_TEXT( "[%D] %M -- %C\n" ), errors ) ); sqlite3_free( errors ); } sqlite3_free( sql ); } Any idea? This method are so slow - about 1 kB per second new data in my DB. -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] [SQLite improve productivity]
Hi. How can I improve productivity of this code: a_messenger_t::data_buff_t --> std::deque< some_struct > // char * errors = 0; for( a_messenger_t::data_buff_t::const_iterator it = msg.m_buff.begin(), last = msg.m_buff.end(); it != last; ++it ) { // Converting long to std::string... std::string sec( itos( it->m_time.sec() ) ); std::string usec( itos( it->m_time.usec() ) ); // Columns in 'data' table defined like (INTEGER, INTEGER, INTEGER, TEXT) char * sql = sqlite3_mprintf( "INSERT INTO data VALUES( '%q', '%q', '%q', '%q' )", m_sources_map[ it->m_source ].m_sid.c_str(), sec.c_str(), usec.c_str(), it->m_value.c_str() ); // m_db --> sqlite3* int ret = sqlite3_exec( m_db, sql, 0, 0, &errors ); if( ret != SQLITE_OK ) { ACE_DEBUG( ( LM_INFO, ACE_TEXT( "[%D] %M -- %C\n" ), errors ) ); sqlite3_free( errors ); } sqlite3_free( sql ); } Any idea? This method are so slow - about 1 kB per second new data in my DB. -- Regards, Igor Mironchick, Intervale © #ICQ 492-597-570 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?
Poor comparison in this case. Are you going to make a mathematical model when you got a little stream to cross and you have a few available planks to do it? RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 05 August 2007 16:43 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index? We learn mathematics etc so that we can make numerical models which give us design information. Imagine trying to build every combination of a bridge to settle on a design! Make a mathematical model and get it close to optimal at the first attempt. RB Smissaert wrote: > Yes, I suppose you are right there. > I will see if I can put together a report that runs all possible types of > queries (sequentially) and then see if I have left anything out that would > cause problems. > > RBS > > > -Original Message- > From: Gerry Snyder [mailto:[EMAIL PROTECTED] > Sent: 05 August 2007 03:35 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the > index? > > RB Smissaert wrote: > >> I think an application that >>would produce all the needed indexes based on the table and all the > > possible > >>queries would be helpful. Anybody done such an app? > > _All_ possible queries? Not practical for any significant number of > columns. N factorial gets big fast. > > The indexes would be much larger than the data base itself. > > I'm afraid you are going to have to settle for doing an intelligent > design of the data base. > > > Gerry > > > > - > 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] UI question
John wrote: John wrote: Griggs, Donald wrote: Regarding: " Where I get tripped up is making a typo in a multiline query that may have destructive effects if I terminate with a ';' and allow the CLI to execute the query. To date I have aborted the CLI with a ^C (MS Windows). I would love have a means of escaping/cancelling back to the prompt without executing from a partially entered query. Perhaps the escape key is a good candidate for actioning this functionality should someone implement it." Why not just add the word "damnit;" (or almost anything) to the end of the query, resulting in a syntax error -- and no changes to your database? Probably because I didn't think of it at the time - thanks Donald, but I would still like to push one key and cancel the current entry like I can in a Cmd.exe CLI console. Is this feature not common in CLI consoles on other OS. Talking to myself - perhaps cmd.exe can do this because it only supports one line of entry, I understand (mainly from hearsay) that CLI consoles on other OS can support multiline entries and therefore (my supposition) may not be able to support cancellation of the current entry, so that feature may not be common in non Windows environments. If IRC it wasn't available in MSDOS command.com either, but one could always BS over the current line. Therein lies the problem - you can't edit a previous line in a multiline statement in the sqlite CLI before committing the statement for execution by terminating with ';' whether the line contains garbage to deliberately abort the interpreter or not. There is a host of GUI-based Sqlite tools which replace the command line program and satisfy your wishes. Try out a few and choose the one you like best. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] QT4 ubuntu sqlite driver problem ? (was Re: [sqlite] Weird error)
Alain Bertrand wrote: Joe Wilson a écrit : --- Alain Bertrand <[EMAIL PROTECTED]> wrote: > --- Alain Bertrand <[EMAIL PROTECTED]> wrote: In a Qt4 application, I have the following bit of code : QString s; bool b; QSqlQuery q; QSqlError err; s= "CREATE TABLE ttd_bdata (sheetId integer primary key,"; s+="creation_date date, modif_date date,"; s+="speciesId integer,USDA_zone integer,"; s+="generalities text,propagation text,care text,"; s+="esthetics text, phyto text,miscellaneous text)"; //s="SELECT * FROM ttd_species WHERE speciesId=4"; qWarning( s.toAscii() ); b=q.exec( s ); Executing the query fails with the following information SQL logic error or missing database Unable to fetch row Err number 1. If the query is remplaced by the commented one, I got no error. If I open my database file with sqliteman, the query is executed without any problem. Some ideas ? I don't understand your description, but here are some random thoughts that might help you: Thanks for your answer. CREATE statements do not return any rows. Yes, so why do I get this message from the qt driver ? Your select table did not match the create statement table. Instead: Use "SELECT * FROM ttd_bdata WHERE speciesId=4" This is not an error. The table is not created so the select on a table which I know does exists is here to prove that I don't have a "missing database" error. To add to obscurity, if I run the same function in a different context, it is ok but very simple queries (like UPDATE ttd_version SET minor='1' WHERE Id=1) fails both in my app and in sqliteman. I would now think of a QT/sqlite driver bug. Does anybody heard of some problem with the sqlite driver in the qt4 libs of Ubuntu feisty ? Don't use += in this case ok. AlainQT4 Use the sqlite3 command line tool to look at what has been created. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?
We learn mathematics etc so that we can make numerical models which give us design information. Imagine trying to build every combination of a bridge to settle on a design! Make a mathematical model and get it close to optimal at the first attempt. RB Smissaert wrote: Yes, I suppose you are right there. I will see if I can put together a report that runs all possible types of queries (sequentially) and then see if I have left anything out that would cause problems. RBS -Original Message- From: Gerry Snyder [mailto:[EMAIL PROTECTED] Sent: 05 August 2007 03:35 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index? RB Smissaert wrote: I think an application that would produce all the needed indexes based on the table and all the possible queries would be helpful. Anybody done such an app? _All_ possible queries? Not practical for any significant number of columns. N factorial gets big fast. The indexes would be much larger than the data base itself. I'm afraid you are going to have to settle for doing an intelligent design of the data base. Gerry - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Re: Re: Re: Re: How does SQLite choose the index?
RB Smissaert wrote: But then if the base of the logarithm doesn't matter then how is this equation going to help you? m==N/logN So, basically it comes down to some experimenting? RBS No, the relationship is set by the math. An absolute measurement requires that you experiment to discover the constants, but in real world situations you are operating in a fixed environment and just want to optimize your performance by proportional measurements. Whether you use log or ln merely introduces the famous 2.303 constant proportion. Whether a full scan of an index is slower than a row scan does depend on the index structure. If the index has brother pointers a full index scan can be as fast as a row scan, and if an ordered list is requesteded can be faster. This is where you could experiment to discover more about the performance of the index. I think that Sqlite's indices do not have the extra pointers, but have not looked carefully to find out for sure. A binary tree type index like a B-tree maintains its keys in collating sequence order so a scan of the index gives you an ordered list without extra sorting. Sqlite uses such an index to sort result sets rather than a traditional sort. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UI question
John wrote: Griggs, Donald wrote: Regarding: " Where I get tripped up is making a typo in a multiline query that may have destructive effects if I terminate with a ';' and allow the CLI to execute the query. To date I have aborted the CLI with a ^C (MS Windows). I would love have a means of escaping/cancelling back to the prompt without executing from a partially entered query. Perhaps the escape key is a good candidate for actioning this functionality should someone implement it." Why not just add the word "damnit;" (or almost anything) to the end of the query, resulting in a syntax error -- and no changes to your database? Probably because I didn't think of it at the time - thanks Donald, but I would still like to push one key and cancel the current entry like I can in a Cmd.exe CLI console. Is this feature not common in CLI consoles on other OS. Talking to myself - perhaps cmd.exe can do this because it only supports one line of entry, I understand (mainly from hearsay) that CLI consoles on other OS can support multiline entries and therefore (my supposition) may not be able to support cancellation of the current entry, so that feature may not be common in non Windows environments. If IRC it wasn't available in MSDOS command.com either, but one could always BS over the current line. Therein lies the problem - you can't edit a previous line in a multiline statement in the sqlite CLI before committing the statement for execution by terminating with ';' whether the line contains garbage to deliberately abort the interpreter or not. -- Regards John McMahon [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Select, update on the row, and step leads to crash
Hi, The sample code is below. I am trying this on windows(xp)(sqlite 3.3.13) and have disabled almost all sqlite features. When createUsingExec variable is true in the sample code, I am able to reproduce the problem. Thanks karthik //== begin == int select_while_update (); int main () { select_while_update (); } int select_while_update () { int i = 0; int ret; sqlite3 *pDb = NULL; const char *dbName = "./t.db"; const char *crTable = "CREATE TABLE A ( ID INTEGER, NAME TEXT) ;"; const char *selAll = "SELECT * from A;"; const char *insRow = "INSERT INTO A VALUES (?,?);"; const char *updRow = "UPDATE A SET NAME=? WHERE ID=?;"; sqlite3_stmt *pSelAll = NULL; sqlite3_stmt *pInsRow = NULL; sqlite3_stmt *pUpdRow = NULL; int createUsingExec = 1; char buf [64] = { 0 }; remove (dbName); ret = sqlite3_open (dbName, &pDb); sqlite3_busy_timeout (pDb, 3000); if (createUsingExec) { const char *c2 = "INSERT INTO A VALUES ( 1, 'name is 1' ) ;"; const char *c3 = "INSERT INTO A VALUES ( 2, 'name is 2' ) ;"; const char *c4 = "INSERT INTO A VALUES ( 3, 'name is 3') ;"; const char *c5 = "INSERT INTO A VALUES ( 4, 'name is 4' ) ;"; const char *c6 = "INSERT INTO A VALUES ( 5, 'name is 5' ) ;"; sqlite3_exec (pDb, crTable, 0, 0, 0); sqlite3_exec (pDb, c2, 0, 0, 0); sqlite3_exec (pDb, c3, 0, 0, 0); sqlite3_exec (pDb, c4, 0, 0, 0); sqlite3_exec (pDb, c5, 0, 0, 0); sqlite3_exec (pDb, c6, 0, 0, 0); } else { ret = sqlite3_exec (pDb, crTable, NULL, NULL, NULL); if (SQLITE_OK != ret) { printf ("error creating table\n"); return -1; } ret = sqlite3_prepare_v2 (pDb, insRow, -1, &pInsRow, NULL); for (i = 0; i < 5; i ++) { sprintf (buf, "name is %d", i); sqlite3_reset (pInsRow); sqlite3_bind_int (pInsRow, 1, i); sqlite3_bind_text (pInsRow, 2, buf, -1, SQLITE_STATIC); sqlite3_step (pInsRow); } sqlite3_finalize (pInsRow); } ret = sqlite3_prepare_v2 (pDb, selAll, -1, &pSelAll, NULL); sqlite3_bind_parameter_count (pSelAll); sqlite3_reset (pSelAll); ret = sqlite3_step (pSelAll); while (SQLITE_ROW == ret) { // get id from select statemnt results int id; char *text; sqlite3_data_count (pSelAll); sqlite3_column_count (pSelAll); sqlite3_column_type (pSelAll, 0); sqlite3_column_bytes (pSelAll, 0); // get id id = sqlite3_column_int (pSelAll, 0); sqlite3_column_type (pSelAll, 1); sqlite3_column_bytes (pSelAll, 1); // get text text = sqlite3_column_text (pSelAll, 1); sprintf (buf, "name is %d", ++i); // prepare update statements if (!pUpdRow) { sqlite3_prepare_v2 (pDb, updRow, -1, &pUpdRow, NULL); } sqlite3_bind_parameter_count (pUpdRow); sqlite3_reset (pUpdRow); // bind values to update statement sqlite3_bind_text (pUpdRow, 1, buf, -1, SQLITE_STATIC); sqlite3_bind_int (pUpdRow, 2, i); sqlite3_step (pUpdRow); // step to next result row ret = sqlite3_step (pSelAll); } // need finalize other statements return 0; } //== end == This e-mail and attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient's) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Sunday, August 05, 2007 12:59 AM To: sqlit
Re: [sqlite] UI question
Griggs, Donald wrote: Regarding: " Where I get tripped up is making a typo in a multiline query that may have destructive effects if I terminate with a ';' and allow the CLI to execute the query. To date I have aborted the CLI with a ^C (MS Windows). I would love have a means of escaping/cancelling back to the prompt without executing from a partially entered query. Perhaps the escape key is a good candidate for actioning this functionality should someone implement it." Why not just add the word "damnit;" (or almost anything) to the end of the query, resulting in a syntax error -- and no changes to your database? Probably because I didn't think of it at the time - thanks Donald, but I would still like to push one key and cancel the current entry like I can in a Cmd.exe CLI console. Is this feature not common in CLI consoles on other OS. -- Regards John McMahon [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] UI question
Regarding: " Where I get tripped up is making a typo in a multiline query that may have destructive effects if I terminate with a ';' and allow the CLI to execute the query. To date I have aborted the CLI with a ^C (MS Windows). I would love have a means of escaping/cancelling back to the prompt without executing from a partially entered query. Perhaps the escape key is a good candidate for actioning this functionality should someone implement it." Why not just add the word "damnit;" (or almost anything) to the end of the query, resulting in a syntax error -- and no changes to your database? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UI question
[EMAIL PROTECTED] wrote: Scott Derrick <[EMAIL PROTECTED]> wrote: This is probably a stupid question but has frustrated me a couple of times. When using the command line interface sqlite3, a couple of times I have forgotten to use the "." before a command. After that I get a "...>" prompt that I can't seem to escape from and accepts no commands? My only choice is to shut down that terminal and start a new one.. There must be an easy way to get back to the command mode? And what is the "...>" mode? Type a semicolon on a line by itself. You'll then get a syntax error and you will be back at the command prompt. Where I get tripped up is making a typo in a multiline query that may have destructive effects if I terminate with a ';' and allow the CLI to execute the query. To date I have aborted the CLI with a ^C (MS Windows). I would love have a means of escaping/cancelling back to the prompt without executing from a partially entered query. Perhaps the escape key is a good candidate for actioning this functionality should someone implement it. -- Regards John McMahon [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] QT4 ubuntu sqlite driver problem ? (was Re: [sqlite] Weird error)
Joe Wilson a écrit : --- Alain Bertrand <[EMAIL PROTECTED]> wrote: > --- Alain Bertrand <[EMAIL PROTECTED]> wrote: In a Qt4 application, I have the following bit of code : QString s; bool b; QSqlQuery q; QSqlError err; s= "CREATE TABLE ttd_bdata (sheetId integer primary key,"; s+="creation_date date, modif_date date,"; s+="speciesId integer,USDA_zone integer,"; s+="generalities text,propagation text,care text,"; s+="esthetics text, phyto text,miscellaneous text)"; //s="SELECT * FROM ttd_species WHERE speciesId=4"; qWarning( s.toAscii() ); b=q.exec( s ); Executing the query fails with the following information SQL logic error or missing database Unable to fetch row Err number 1. If the query is remplaced by the commented one, I got no error. If I open my database file with sqliteman, the query is executed without any problem. Some ideas ? I don't understand your description, but here are some random thoughts that might help you: Thanks for your answer. CREATE statements do not return any rows. Yes, so why do I get this message from the qt driver ? Your select table did not match the create statement table. Instead: Use "SELECT * FROM ttd_bdata WHERE speciesId=4" This is not an error. The table is not created so the select on a table which I know does exists is here to prove that I don't have a "missing database" error. To add to obscurity, if I run the same function in a different context, it is ok but very simple queries (like UPDATE ttd_version SET minor='1' WHERE Id=1) fails both in my app and in sqliteman. I would now think of a QT/sqlite driver bug. Does anybody heard of some problem with the sqlite driver in the qt4 libs of Ubuntu feisty ? Don't use += in this case ok. AlainQT4 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?
Yes, I suppose you are right there. I will see if I can put together a report that runs all possible types of queries (sequentially) and then see if I have left anything out that would cause problems. RBS -Original Message- From: Gerry Snyder [mailto:[EMAIL PROTECTED] Sent: 05 August 2007 03:35 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index? RB Smissaert wrote: > I think an application that > would produce all the needed indexes based on the table and all the possible > queries would be helpful. Anybody done such an app? _All_ possible queries? Not practical for any significant number of columns. N factorial gets big fast. The indexes would be much larger than the data base itself. I'm afraid you are going to have to settle for doing an intelligent design of the data base. Gerry - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Problem with glob '137*' ?
Queries where there is a glob comparison on a string that could be interpreted as a number always seem a bit slower than when comparing to a string that can't be compared to a number. So for example: select f from t where f glob '137*' is slower than: select f from t where f glob 'abc*' Is this there any explanation for this and is there any way round it? Or can SQLite not be to blame here and is it something in my wrapper? RBS - To unsubscribe, send email to [EMAIL PROTECTED] -