Re: [sqlite] C++ - HOW MANY rows?

[Igor Tandetnik]

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

Re: [sqlite] C++ - HOW MANY rows?

[Arbol One]

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, , 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, , 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, ,
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?

[Simon Slavin]

On 12 Sep 2012, at 6:31pm, Arbol One  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



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?

[Arbol One]

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?

[Black, Michael (IS)]

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, , 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, , 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, ,
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?

[Igor Tandetnik]

Arbol One  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

Re: [sqlite] C++ - HOW MANY rows?

[Arbol One]

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, , 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, ,
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?

[Igor Tandetnik]

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?

[Igor Tandetnik]

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