1, open the database:
Note: Open a database, the filename does not necessarily exist, if this file does not exist, SQLite will be created automatically. The first parameter refers to the file name, and the second parameter is the defined sqlite3 * * struct pointer (the key data structure), the underlying detail of the structure, and you don't have to worry about it.
int Sqlite3_open (
const char *filename,/* Database filename (UTF-8) * *
Sqlite3 **ppdb/* Out:sqlite DB handle * *
);
Return value: Indicates whether the exercise is correct (SQLITE_OK operation is normal)
2. Close the database:
Note: If you open a database with Sqlite3_open, do not forget to close the database with this function at the end.
int Sqlite3_close (sqlite3*); parameter is just the structure, which is the database handle
3. Execute SQL statement:
Description: This function is to execute one or more SQL statements, separated by the ";" between the SQL statements. It is recommended that you specify a third parameter callback function when executing one or more SQL statements, that you can get a detailed procedure for executing SQL in a callback function, or that you should return 0 if all SQL execution is completed, otherwise this execution is not completely successful. Fifth parameter: If execution fails (no return 0), you can view the fifth stated value. To view detailed error messages.
int Sqlite3_exec (
sqlite3*,/* already open database handle * *
const char *SQL,/* SQL statement to execute * *
Sqlite_callback,//callback function * *
void *,/* parameters passed to the callback function * *
Char **errmsg/* Save error message * *
);
Usually the Sqlite3_callback and the void* behind it can be filled with NULL, indicating that no callback is required. For example, you do insert operation, do delete operation, there is no need to use callback. As a SELECT, you use callbacks, because sqlite3 the data, you have to tell you what data is detected by the callback.
4, Exec's callback
typedef int (*sqlite3_callback) (void*, int, char**, char**);
Description: Your callback function must be defined as the type of the function above.
For example:
int loadmyinfo (void * para, int n_column, char * * column_value, char * * column_name)
{
Para is the void parameter you passed in the sqlite3_exec.
With the para parameter, you can pass in some special pointers (such as class pointers, struct pointers), and then cast them into the corresponding type (which is the void* type and must be cast to your type). And then manipulate the data
N_column is the number of fields in this record (that is, how many columns this record has)
char * * Column_value is a key value, the detected data is stored here, it is actually a 1-d array (do not think it is a 2-dimensional array), each element is a char * value, is a field content (in a string to end)
char * * column_name is corresponding to Column_value, which indicates the field name of the field
5, take the current insertion position:
Function: Return to your previous insertion position, starting at 1, sqlite3* the handle to open the database for you.
Long Long int sqlite3_last_insert_rowid (sqlite3*);
6. Non-callback Select query:
Function: Execute query SQL once and return to a recordset.
int sqlite3_get_table (
sqlite3*,/* already open database handle * *
const char *SQL,/* SQL statement to execute * *
Char ***RESULTP,/* Save the pointer to the recordset.
int *nrow,/* Returns the number of records (and how many lines are detected) * *
int *ncolumn,/* return field number (number of columns) * *
Char **errmsg/* RETURN error message * *
)
Description: The third parameter is the query result, it is a one-dimensional array, the memory layout is: The first row is the field name, followed by the value of each field.
Instance:
int main (int, char * *)
{
Sqlite3 * DB;
int result;
char * errmsg = NULL;
Char **dbresult;
int Nrow, Ncolumn;
int I, J;
int index;
result = Sqlite3_open ("c:\\dcg_database.db", &db);
If (Result!= SQLITE_OK)
{
return-1;
}
Database Operation code
Suppose the Mytable_1 table has been created before
Start query, the incoming dbresult is already char * *, and here Add a & address character, passed into the char * * *
result = sqlite3_get_table (db, "select * from Mytable_1", &dbresult, &nrow, &ncolumn, &errmsg);
if (SQLITE_OK = = result)
{
Query successful
index = Ncolumn; I said earlier dbresult the first row of data is the field name, starting with the Ncolumn index is the real data
printf ("%d records found \ \ n", nrow);
for (i = 0; i < nrow; i++)
{
printf ("%d Records \ n", i+1);
for (j = 0; J < Ncolumn; J + +)
{
printf ("Field name:%s?> field value:%s\n", Dbresult[j], Dbresult [index]);
++index; Dbresult field values are contiguous, from the No. 0 index to the nColumn-1 index is the field name, starting with the Ncolumn index, followed by the field value, which takes a two-dimensional table (the traditional row and column notation) in a flat form to represent
}
printf ("-------\ n");
}
}
Here, regardless of whether the database query is successful, release the char** query results, using the features provided by SQLite to release
Sqlite3_free_table (Dbresult);
Close Database
Sqlite3_close (DB);
return 0;
}
7, release the results of the query:
Function: Frees the memory occupied by the recordset of the current query
void Sqlite3_free_table (char **result);
Instance: (SQLite database uses a simple C-language instance of a callback function)
Copy Code code as follows:
#include <stdio.h>
#include <sqlite3.h>
static int callback (void *notused, int argc, char **argv, char **azcolname)
{
int i;
for (i=0; i<argc; i++)
{
printf ("%s =%s\n", Azcolname[i], argv[i]? Argv[i]: "NULL");
}
printf ("\ n");
return 0;
}
int main (int argc, char **argv)
{
Sqlite3 *db;
char *zerrmsg = 0;
int RC;
if (argc!=3)
{
fprintf (stderr, "Usage:%s DATABASE sql-statement\n", argv[0]);
return 1;
}
rc = Sqlite3_open (argv[1], &db);
if (RC)
{
fprintf (stderr, "Can ' t Open database:%s\n", sqlite3_errmsg (db));
Sqlite3_close (DB);
return 1;
}
rc = sqlite3_exec (db, Argv[2], callback, 0, &zerrmsg);
if (RC!=SQLITE_OK)
{
fprintf (stderr, "SQL Error:%s\n", zerrmsg);
Sqlite3_close (DB);
return 1;
}
Sqlite3_close (DB);
return 0;
}
Compile:
[Root@localhost test]# gcc sql.c-o sql-l sqlite3
