Expand/Shrink

sqlite3_get_table

Definition: include pSQLite.e

object data = sqlite3_get_table(sqlite3 db, string cmd)
Description: Perform an SQL query.

This differs from sqlite3_exec() in that it returns the complete data set.

db: an open database, a result from sqlite3_open().
cmd: the SQL to be executed.
pwa/p2js: Not supported
Notes: It is in fact perfectly legal to execute non-queries via sqlite3_get_table(), however the result of success will always be {}, and unlike sqlite3_exec(), only a single SQL statement is allowed.

If the query could not be executed because a database file is locked or busy, then this function returns SQLITE_BUSY or perhaps SQLITE_ABORT.

On successful completion, data will be a sequence.

For example, given the table 

                Name        | Age
                -----------------------
                Alice       | 43
                Bob         | 28
                Cindy       | 21

The result might be 

                {{"Name","Age"},
                 {"Alice","43"},
                 {"Bob","28"},
                 {"Cindy","21"}}

Notice that there is an extra row of data containing the column headers.

The sqlite3_free_table() routine is in fact wrapped and used internally, but not made publicly available.
(By the time this function returns, it has in fact already been invoked, on the C-string table that is.)
Example:
Copy 
include pSQLite.e
sqlite3 db = sqlite3_open("test.sqlite")
integer res = sqlite3_exec(db,"CREATE TABLE table1(id INTEGER, desc TEXT);")
pp(sqlite3_get_table(db,"PRAGMA table_info(table1);"),{pp_Nest,1})
pp(sqlite3_get_table(db,"SELECT * FROM sqlite_master;"),{pp_Nest,1})
sqlite3_close(db)

-- output:
--{{"cid", "name", "type", "notnull", "dflt_value", "pk"},
-- {"0", "id", "INTEGER", "0", {}, "0"},
-- {"1", "desc", "TEXT", "0", {}, "0"}}
--{{"type", "name", "tbl_name", "rootpage", "sql"},
-- {"table", "test", "test", "2", "CREATE TABLE test(id INTEGER, desc TEXT)"}}
For more details of that special instruction and the results, see PRAGMA table_info.
The "sqlite_master" is a special control database that contains details of all the other databases that the database contains (!) - for more information you will probably have to fire up and start typing into your favourite search engine.
See Also: sqlite3_open