Unable to access tables created with sqlite3 from a program using the C API

Question

I generate an sqlite3 database file (call it db.sl3; invoked interactively as $ sqlite3 db.sl3 from a shell) from within the sqlite3 commandline program, for instance

create table people (
        id              integer,
        firstname       varchar(20),
        lastname        varchar(20),
        phonenumber     char(10)
);   
insert into people (id, firstname, lastname, phonenumber) values
        (1, 'Fred', 'Flintstone', '5055551234');    
insert into people (id, firstname, lastname, phonenumber) values
        (2, 'Wilma', 'Flintstone', '5055551234');    
insert into people (id, firstname, lastname, phonenumber) values
        (3, 'Barny', 'Rubble', '5055554321');

I am trying to use this in a program I have written which uses the sqlite3 C API; however, whenever I attempt to open up the database file in the C program using either

sqlite3* db;
rc = sqlite3_open( "db.sl3", &db );

or

rc = sqlite3_open_v2( "db.sl3", &db, SQLITE_READONLY, 0 );

followed by a query where the SQL is contained in the following string

std::string sqlCmd = "select * from sqlite_master where type='table' order by name";

to the sqlite3_get_table wrapper interface invoked as follows

rc = sqlite3_get_table( db, sqlCmd.c_str(), &result, &numRows, &numCols, &errorMsg );

The return code (rc) is 0 in either case implying that there was no problem with the operation but there are no tables entries recorded in the result variable. I have tried all sorts of pathing issues (e.g., using absolute paths to the db file, relative, only in the invocation directory, etc.) to no avail. Also, when I reopen the database file with the sqlite3 commandline program I can see the tables and their entries. As an aside, if I open, create and insert the lines into the table in the C program I can access them fine.

Any help would be much appreciated. Am I not properly initializing things?

Answer 1

By default sqlite3_open will create the database if it does not exist (equivalent with calling sqlite3_open_v2 with flags=S*QLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE*) - and rc will be *SQLITE_OK*.

I've tried this test program against a database created using sqlite3 and each time (existing db with people table: full path, relative; existing 0 sized db: full path, relative; nonexistent db: full path, relative) sqlite3_open_v2 behaved as advertised.

Try to do a 'sqlite3 [full_path_to_db]' then run the query and compare the results with what your program or mine does (again with [full_path_to_db]) just in case your sqlite3_open tests created some 0 sized db.sl3 which are then gadly opened by sqlite3_open_v2 .

Hope this helps.