Sqlite learning notes 8: Using sqlite to create a table in C language, sqlite learning notes

Sqlite learning notes 8: Using sqlite to create a table in C language, sqlite learning notes

We have already discussed how to open and close the database, and how to execute SQL statements to create a table.

Functions to be used:

sqlite3_exec(sqlite3* db, const char *sql, sqlite_callback callback, void *data, char **errmsg)

Parameter: db: opened database instance SQL: SQL statement, is a string callback: Is a callback function data: As the first parameter of the callback function errmsg: Used to bring back error information

The callback function has two types of return values. 1. return zero: sqlite3_exec (). The query continues. 2. return non-zero: sqlite3_exec () will immediately interrupt the query, and sqlite3_exec () will return SQLITE_ABORT.

The format of the callback function is as follows: int sqlite_callback (void * pv,/* is passed by the fourth parameter of sqlite3_exec () */int argc, /* Number of columns in the table */char ** argv,/* pointer array pointing to the query result, which can be set by sqlite3_column_text () get the */char ** col/* pointer array pointing to the header name, which can be obtained by sqlite3_column_name () */). Parameter format: the callback function passed to sqlite3_exec, this callback function is called once to display query results for each query result. Parameter: pv: the initialization parameter passed by sqlite3_exec. argc: the number of columns in the table header. col: the name of the table header. array pointer: argv: returned value of the data array pointer of the header: 1: interrupted query 0: Continue to list the queried data

Instance:

#include <stdio.h>#include <stdlib.h>#include "sqlite/sqlite3.h"#define DB_NANE "sqlite/test.db"sqlite3 *db = NULL;char* sql = NULL;char *zErrMsg = NULL;int ret = 0;typedef enum{    false,    true} bool;static int callback(void *NotUsed, int argc, char **argv, char **azColName){    int i = 0;    for(i=0; i < argc; i++){        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");    }    printf("\n");        return 0;}bool connectDB(){    ret = sqlite3_open(DB_NANE, &db);        if( ret != SQLITE_OK){        fprintf(stderr, "Error open database: %s\n", sqlite3_errmsg(db));        sqlite3_free(zErrMsg);                return false;    }        fprintf(stdout, "Successfully opened database\n");    return true;}

/* Added this function */bool createTable () {/* Create SQL statement */SQL = "CREATE TABLE COMPANY (" \ "ID INT PRIMARY KEY NOT NULL, "\" name text not null, "\" age int not null, "\" address char (50), "\" salary real );"; /* Execute SQL statement */ret = sqlite3_exec (db, SQL, callback, 0, & zErrMsg); if (ret! = SQLITE_ OK) {fprintf (stderr, "Error SQL: % s \ n", zErrMsg); sqlite3_free (zErrMsg); return false;} fprintf (stdout, "Successfully table created \ n"); return true;} bool closeDB () {int ret = 0; ret = sqlite3_close (db); if (ret = SQLITE_BUSY) {return false;} return true;} int main (int argc, char * argv []) {connectDB (); createTable (); /* added a function call */closeDB (); return 0 ;}

This function can only be executed once. If the table already exists during the second execution, the generated test. db can be deleted and executed again.

How does SQlite insert a column in a created table?

Alter table table-name add column column-name column-type
For example, in the student table, add a column named name and the type is varchar:
Alter table student add column name varchar;

How does sqlite3 query all tables in the database using C language?

SELECT name FROM sqlite_master

WHERE type = 'table'
Order by name;
Use this query statement in C Language