SQLite SELECT JOIN and VIEWS in C++

Question

I have a two tables in SQLITE and View where I try to compine these two tables (my idea is to get all the rows from table1 where table1.field1 and table2.field99 have the same value.

Tables and View are Table1,Table2 and View1
View code is  "SELECT * FROM Table1 JOIN Table2 ON UPPER(Field1) LIKE UPPER(Table2.Field99)"

If I run

SELECT * FROM View1;

or if I run

SELECT * FROM Table1 JOIN Table2 ON UPPER(Field1) LIKE UPPER(Field99);

from sqlite shell it returns the rows just fine.

But if i try to run those SQL statements in C++ with this code:

if(sqlite3_prepare_v2(database,SQLStr,-1,&stmt,0)==SQLITE_OK){
   int cols = sqlite3_column_count(stmt);
   printf("Query OK: %s \nColumns: %i\n", SQLStr, cols);
   while(1) {
      status = sqlite3_step(stmt);
      if(status == SQLITE_ROW) {
         printf("This is a row!\n");
      } else if(status == SQLITE_DONE) {
         printf("Rows handled!\n");
         break;
      } else {
         printf("Other status!\n");
         break;
      }
   }
}

It just returns:

Rows handled: SELECT * FROM View1; 
Columns: 7
Rows handled!

But it doesn't return any rows like the shell does. (there should be "This is a row!" printed for every row in query. I tried to add table names to queries but no help. I also tried to run for example SELECT * FROM Table1; and then the C++ returns the rows. Is it so that SQLITE in C++ can't handle JOINS or Views?

ken · Accepted Answer · 2012-02-22 06:06:21Z

1

if u want to collect the rows then use Sqlite3_exec() API or column API.sqlite3_step() only evaluates the prepared statement it does not fetch the results, for that u have to use different api available or u can use sqlite3_exec which is a wrapper.

check the following link http://www.sqlite.org/c3ref/funclist.html http://www.sqlite.org/cintro.html

All the best.

answered Feb 22, 2012 at 6:06

ken

8361 gold badge9 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

varjis Over a year ago

Well I actually tried to use the sqlite3_exec. All goes well if I do SELECT * FROM Table WHERE Col1='1'; but if I do SELECT * FROM Table WHERE Col2 LIKE 'Text%'; the callback function doesn't get any records. Also if i try to SELECT * FROM View1; It doesn't return any rows. Although it seems the database engine is doing something because the queries seem to take time to run.

Collectives™ on Stack Overflow

SQLite SELECT JOIN and VIEWS in C++

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related