Re: [sqlite] C++ - HOW MANY rows?
Try using this method...you just need to ensure mystmt is set to NULL to start with and reset to NULL in finalize(). const int mySQLite3::read_int(int pos) throw(somexception) { if (mystmt == NULL) { // Ensure mystmt is set to NULL in constructor rc = sqlite3_prepare_v2(db, SQLStatement.c_str(), -1, mystmt, NULL); if(rc != SQLITE_OK) { try { this-display(rc, FILE, METHOD, LINE); } catch(somexception e) { throw e; } } } rc = sqlite3_step(mystmt); if(rc == SQLITE_ROW ) { apint = sqlite3_column_int(mystmt,pos); counter++; return apint; } if (rc != SQLITE_DONE) { this-display(rc,FILE,METHOD,LINE+:+sqlite3_errmsg(db)); } try { this-finalize(); // ensure mystmt set to null in finalize } catch(somexception e) { throw e; } return -1; // how do you know when you're done? Will -1 work? } Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Arbol One [arbol...@gmail.com] Sent: Tuesday, September 11, 2012 9:44 PM To: 'General Discussion of SQLite Database' Subject: EXT :Re: [sqlite] C++ - HOW MANY rows? Thanks Igor for your prompt response. Since the call to the sqlite3_step function is inside the mySQLite3::read_* class-function/method, I call the read_* method from Runner::read_tblName() for each datum I need to retrieve. Now, in a while loop inside Runner::read_tblName() I could call the mySQLite3::read_* for the data to be retrieved, but instead of reading the next row of data, it keeps on reading the same row again and again. Furthermore, I have no way to let Runner::read_tblName 'know' that there is no more data, hence stopping the while loop. Obviously my problem is in design, but I don't have enough experience using SQLite3 to come up with a better idea. My question is, really, how do you, C++ programmers, have resolved this issue? There must be a solution. void Runner::read_tblName() { . sql_statement = SELECT * FROM name; //while(there is more data){ // I have no-way to report to this method that there is no more data in the bank // I could add a flag to the mySQLite3 class and have the while loop check on its status, // or a signal that would be trigger by the ending of the while loop. db-setStmt(sql_statement); int pos = 0; data1 = db-read_int(pos); db-setStmt(sql_statement); pos = 1; data2 = db-read_str(pos); data3 = db-read_str(++pos); data4 = db-read_str(++pos); data5 = db-read_str(++pos); Glib::ustring str; str = apstr.format(data1); str += ; str += data2; str += . ; str += data3; str += ; str += data4; str += ; str += data5; apex-setException(str, FILE, METHOD, LINE); apex-Display(); // } --- } const int mySQLite3::read_int(int pos) throw(somexception) { rc = sqlite3_prepare_v2(db, SQLStatement.c_str(), -1, mystmt, NULL); if(rc != SQLITE_OK) { try { this-display(rc, FILE, METHOD, LINE); } catch(somexception e) { throw e; } } else { counter++; } rc = sqlite3_step(mystmt); if(rc == SQLITE_ROW ) { apint = sqlite3_column_int(mystmt,pos); } try { this-finalize(); } catch(somexception e) { throw e; } return apint; } const Glib::ustring mySQLite3::read_str(const int pos) throw(somexception) { //if(pos == 0) { rc = sqlite3_prepare_v2(db, this-SQLStatement.c_str(), -1, mystmt, NULL); //} if(rc != SQLITE_OK) { try { this-display(rc, FILE, METHOD, LINE); } catch(somexception e) { throw e; } } else { counter++; } rc = sqlite3_step(mystmt); if(rc == SQLITE_ROW ) { apstr = (const char*)sqlite3_column_text(mystmt,pos); } try { this-finalize(); } catch(somexception e) { throw e; } return apstr; } ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C++ - HOW MANY rows?
Look, Sir, I appreciate that you have spend your time and energy responding to my question, in fact, I have used your suggestion to have a look at the other guys' C++ wrappers of the SQLite3 library, but my intention is to learn SQL using the SQLite3 API. I am using C++ and not the 'prompt', because I will ultimately use C++ to implement my knowledge of SQL and SQLite3. Just so that we are clear, I don't mind trying and failing a thousand times, I need to know how this thing works! On the other hand, I really like your analogy of the book being opened, the pages being read and then closing the book again, it is brilliant concept. I see now that I need to reorganize my class so that I can have the 'finalizer' method called only once at the end of the database transaction. I hope, and I am counting on everyone here, to eventually release mySQLite3 in the public domain. Again, thanks so much for your help and have a wonderful day. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Tuesday, September 11, 2012 11:24 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] C++ - HOW MANY rows? Arbol One arbol...@gmail.com wrote: Since the call to the sqlite3_step function is inside the mySQLite3::read_* class-function/method ... it is clear that you have a poor class design. As you would generally want to read several columns from the same row, it is unwise to have a read_* method call sqlite3_step, thus advancing to the next row. It is even less wise to have it call sqlite3_prepare and sqlite3_finalize. Now, in a while loop inside Runner::read_tblName() I could call the mySQLite3::read_* for the data to be retrieved, but instead of reading the next row of data, it keeps on reading the same row again and again. Because you prepare and finalize the stament again and again. Think about it this way: sqlite3_prepare call opens a book, sqlite3_step call turns a page, sqlite3_finalize call closes the book shut. Your read_* method opens the book, turns to the first page, reads one line, then closes the book. The next read_* call opens the book again, turns to the first page again, reads another line, and closes the book. Again, and again. Is it still surprising that you never progress past the first page? Obviously my problem is in design Indeed. but I don't have enough experience using SQLite3 to come up with a better idea. In this case, perhaps you should consider using, or at least studying, some existing libraries. There's no shortage of them: http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C++ - HOW MANY rows?
On 12 Sep 2012, at 6:31pm, Arbol One arbol...@gmail.com wrote: Look, Sir, I appreciate that you have spend your time and energy responding to my question, in fact, I have used your suggestion to have a look at the other guys' C++ wrappers of the SQLite3 library, but my intention is to learn SQL using the SQLite3 API. Now that you understand the basics of the SQLite API I think you might be at a good stage to read through the documentation on the SQLite site. For instance, if you read http://www.sqlite.org/c3ref/stmt.html you will learn that it is necessary to call _step() multiple times after calling _prepare() once. So go read through /all/ of it, not necessarily every word but definitely every page, making notes as you read about which pages are going to be useful once you go back to programming again. One thing I'll point out is that you should handle the result codes returned by /every/ call to the API. This not only helps your wrapper to handle errors properly but will help you detect problems while you're writing your wrapper. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C++ - HOW MANY rows?
yes, it worked, thanks Michael. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS) Sent: Wednesday, September 12, 2012 8:13 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] C++ - HOW MANY rows? Try using this method...you just need to ensure mystmt is set to NULL to start with and reset to NULL in finalize(). const int mySQLite3::read_int(int pos) throw(somexception) { if (mystmt == NULL) { // Ensure mystmt is set to NULL in constructor rc = sqlite3_prepare_v2(db, SQLStatement.c_str(), -1, mystmt, NULL); if(rc != SQLITE_OK) { try { this-display(rc, FILE, METHOD, LINE); } catch(somexception e) { throw e; } } } rc = sqlite3_step(mystmt); if(rc == SQLITE_ROW ) { apint = sqlite3_column_int(mystmt,pos); counter++; return apint; } if (rc != SQLITE_DONE) { this-display(rc,FILE,METHOD,LINE+:+sqlite3_errmsg(db)); } try { this-finalize(); // ensure mystmt set to null in finalize } catch(somexception e) { throw e; } return -1; // how do you know when you're done? Will -1 work? } Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Arbol One [arbol...@gmail.com] Sent: Tuesday, September 11, 2012 9:44 PM To: 'General Discussion of SQLite Database' Subject: EXT :Re: [sqlite] C++ - HOW MANY rows? Thanks Igor for your prompt response. Since the call to the sqlite3_step function is inside the mySQLite3::read_* class-function/method, I call the read_* method from Runner::read_tblName() for each datum I need to retrieve. Now, in a while loop inside Runner::read_tblName() I could call the mySQLite3::read_* for the data to be retrieved, but instead of reading the next row of data, it keeps on reading the same row again and again. Furthermore, I have no way to let Runner::read_tblName 'know' that there is no more data, hence stopping the while loop. Obviously my problem is in design, but I don't have enough experience using SQLite3 to come up with a better idea. My question is, really, how do you, C++ programmers, have resolved this issue? There must be a solution. void Runner::read_tblName() { . sql_statement = SELECT * FROM name; //while(there is more data){ // I have no-way to report to this method that there is no more data in the bank // I could add a flag to the mySQLite3 class and have the while loop check on its status, // or a signal that would be trigger by the ending of the while loop. db-setStmt(sql_statement); int pos = 0; data1 = db-read_int(pos); db-setStmt(sql_statement); pos = 1; data2 = db-read_str(pos); data3 = db-read_str(++pos); data4 = db-read_str(++pos); data5 = db-read_str(++pos); Glib::ustring str; str = apstr.format(data1); str += ; str += data2; str += . ; str += data3; str += ; str += data4; str += ; str += data5; apex-setException(str, FILE, METHOD, LINE); apex-Display(); // } --- } const int mySQLite3::read_int(int pos) throw(somexception) { rc = sqlite3_prepare_v2(db, SQLStatement.c_str(), -1, mystmt, NULL); if(rc != SQLITE_OK) { try { this-display(rc, FILE, METHOD, LINE); } catch(somexception e) { throw e; } } else { counter++; } rc = sqlite3_step(mystmt); if(rc == SQLITE_ROW ) { apint = sqlite3_column_int(mystmt,pos); } try { this-finalize(); } catch(somexception e) { throw e; } return apint; } const Glib::ustring mySQLite3::read_str(const int pos) throw(somexception) { //if(pos == 0) { rc = sqlite3_prepare_v2(db, this-SQLStatement.c_str(), -1, mystmt, NULL); //} if(rc != SQLITE_OK) { try { this-display(rc, FILE, METHOD, LINE); } catch(somexception e) { throw e; } } else { counter++; } rc = sqlite3_step(mystmt); if(rc == SQLITE_ROW ) { apstr = (const char*)sqlite3_column_text(mystmt,pos); } try { this-finalize(); } catch(somexception e) { throw e; } return apstr; } ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C++ - HOW MANY rows?
On 9/12/2012 1:31 PM, Arbol One wrote: Just so that we are clear, I don't mind trying and failing a thousand times, Well, the participants in this list might not possess quite as infinite a patience. If you plan to post a what am I doing wrong type of message to the list a thousand times after each such attempt, you run the risk of outstaying your welcome. I need to know how this thing works! Which part, specifically, do you find unclear? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] C++ - HOW MANY rows?
I read my newly created database using this method: void Runner::read_tblName() { . sql_statement = SELECT * FROM name; db-setStmt(sql_statement); int pos = 0; data1 = db-read_int(pos); db-setStmt(sql_statement); pos = 1; data2 = db-read_str(pos); data3 = db-read_str(++pos); data4 = db-read_str(++pos); data5 = db-read_str(++pos); Glib::ustring str; str = apstr.format(data1); str += ; str += data2; str += . ; str += data3; str += ; str += data4; str += ; str += data5; apex-setException(str, FILE, METHOD, LINE); apex-Display(); } const int mySQLite3::read_int(int pos) throw(somexception) { rc = sqlite3_prepare_v2(db, SQLStatement.c_str(), -1, mystmt, NULL); if(rc != SQLITE_OK) { try { this-display(rc, FILE, METHOD, LINE); } catch(somexception e) { throw e; } } else { counter++; } rc = sqlite3_step(mystmt); if(rc == SQLITE_ROW ) { apint = sqlite3_column_int(mystmt,pos); } try { this-finalize(); } catch(somexception e) { throw e; } return apint; } const Glib::ustring mySQLite3::read_str(const int pos) throw(somexception) { //if(pos == 0) { rc = sqlite3_prepare_v2(db, this-SQLStatement.c_str(), -1, mystmt, NULL); //} if(rc != SQLITE_OK) { try { this-display(rc, FILE, METHOD, LINE); } catch(somexception e) { throw e; } } else { counter++; } rc = sqlite3_step(mystmt); if(rc == SQLITE_ROW ) { apstr = (const char*)sqlite3_column_text(mystmt,pos); } try { this-finalize(); } catch(somexception e) { throw e; } return apstr; } This works just fine, but it only reads the first row, how can I 'tell' my program HOW MANY rows are there to be read and which row I WANT TO READ? Is there a function in SQLite3 that would help me do this? ... work out your salvation with fear and trembling ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C++ - HOW MANY rows?
On 9/11/2012 7:28 PM, Arbol One wrote: This works just fine, but it only reads the first row Of course. You only ever call sqlite3_step step after prepare and before finalize. how can I 'tell' my program HOW MANY rows are there to be read and which row I WANT TO READ? You are supposed to call sqlite3_step repeatedly, for as long as it returns SQLITE_ROW. Every call fetches a new row. When sqlite3_step returns SQLITE_DONE, there are no more rows. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C++ - HOW MANY rows?
On 9/11/2012 7:37 PM, Igor Tandetnik wrote: On 9/11/2012 7:28 PM, Arbol One wrote: This works just fine, but it only reads the first row Of course. You only ever call sqlite3_step step after prepare and before finalize. I mean, you only ever call sqlite3_step *once* after prepare and before finalize. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C++ - HOW MANY rows?
Thanks Igor for your prompt response. Since the call to the sqlite3_step function is inside the mySQLite3::read_* class-function/method, I call the read_* method from Runner::read_tblName() for each datum I need to retrieve. Now, in a while loop inside Runner::read_tblName() I could call the mySQLite3::read_* for the data to be retrieved, but instead of reading the next row of data, it keeps on reading the same row again and again. Furthermore, I have no way to let Runner::read_tblName 'know' that there is no more data, hence stopping the while loop. Obviously my problem is in design, but I don't have enough experience using SQLite3 to come up with a better idea. My question is, really, how do you, C++ programmers, have resolved this issue? There must be a solution. void Runner::read_tblName() { . sql_statement = SELECT * FROM name; //while(there is more data){ // I have no-way to report to this method that there is no more data in the bank // I could add a flag to the mySQLite3 class and have the while loop check on its status, // or a signal that would be trigger by the ending of the while loop. db-setStmt(sql_statement); int pos = 0; data1 = db-read_int(pos); db-setStmt(sql_statement); pos = 1; data2 = db-read_str(pos); data3 = db-read_str(++pos); data4 = db-read_str(++pos); data5 = db-read_str(++pos); Glib::ustring str; str = apstr.format(data1); str += ; str += data2; str += . ; str += data3; str += ; str += data4; str += ; str += data5; apex-setException(str, FILE, METHOD, LINE); apex-Display(); // } --- } const int mySQLite3::read_int(int pos) throw(somexception) { rc = sqlite3_prepare_v2(db, SQLStatement.c_str(), -1, mystmt, NULL); if(rc != SQLITE_OK) { try { this-display(rc, FILE, METHOD, LINE); } catch(somexception e) { throw e; } } else { counter++; } rc = sqlite3_step(mystmt); if(rc == SQLITE_ROW ) { apint = sqlite3_column_int(mystmt,pos); } try { this-finalize(); } catch(somexception e) { throw e; } return apint; } const Glib::ustring mySQLite3::read_str(const int pos) throw(somexception) { //if(pos == 0) { rc = sqlite3_prepare_v2(db, this-SQLStatement.c_str(), -1, mystmt, NULL); //} if(rc != SQLITE_OK) { try { this-display(rc, FILE, METHOD, LINE); } catch(somexception e) { throw e; } } else { counter++; } rc = sqlite3_step(mystmt); if(rc == SQLITE_ROW ) { apstr = (const char*)sqlite3_column_text(mystmt,pos); } try { this-finalize(); } catch(somexception e) { throw e; } return apstr; } ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C++ - HOW MANY rows?
Arbol One arbol...@gmail.com wrote: Since the call to the sqlite3_step function is inside the mySQLite3::read_* class-function/method ... it is clear that you have a poor class design. As you would generally want to read several columns from the same row, it is unwise to have a read_* method call sqlite3_step, thus advancing to the next row. It is even less wise to have it call sqlite3_prepare and sqlite3_finalize. Now, in a while loop inside Runner::read_tblName() I could call the mySQLite3::read_* for the data to be retrieved, but instead of reading the next row of data, it keeps on reading the same row again and again. Because you prepare and finalize the stament again and again. Think about it this way: sqlite3_prepare call opens a book, sqlite3_step call turns a page, sqlite3_finalize call closes the book shut. Your read_* method opens the book, turns to the first page, reads one line, then closes the book. The next read_* call opens the book again, turns to the first page again, reads another line, and closes the book. Again, and again. Is it still surprising that you never progress past the first page? Obviously my problem is in design Indeed. but I don't have enough experience using SQLite3 to come up with a better idea. In this case, perhaps you should consider using, or at least studying, some existing libraries. There's no shortage of them: http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users