PostgreSQL pgsql API
Free. Actually, the main problem is more PostgreSQL than the API. PostgreSQL is supposedly better than MySQL, but setting up the database server is somewhat complex (on a unix system; it seems to be easier on a Windows system) and it seems that MySQL is faster. It supports atomic transactions.
The API is C oriented, and is farily simple. The function used to send a SQL query is:
PGresult *PQexec(PGconn *conn, const char *command);
To retrieve the results, I have to use
// to test the PGresult status:
ExecStatusType PQresultStatus(const PGresult *res);
// to count the number of tuples
int PQntuples(const PGresult *res);
// to get the number of column in each tuple
int PQnfields(const PGresult *res);
// to get the type of a column
Oid PQftype(const PGresult *res, int column_number);
// to get the value
char *PQgetvalue(const PGresult *res, int row_number, int column_number);
Free too. No dependencies (SQLite doesn't connect to a database, it stores your datas in plain files) but is limited to a subset of SQL.
The API is very simple too: it is described here.
int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**);
Is used to send a SQL query to the engine. Newt, we use
int sqlite3_column_count(sqlite3_stmt *pStmt);
int sqlite3_column_type(sqlite3_stmt*, int iCol);
sqlite3_column_XXX() functions are used to read the data.
There is an atomic transaction management.
Free for open-source use (which is not my plan at all); otherwise it is not very expensive - well... It can be...
The InnoDB table engine provide the needed atomic transactions.
The API is very simple too: there is a bunch of mysql_* C function that are rather easy to use. There is also a C++ API (which can be found here) - and that's a very good point (because, take it or not, I program in C++).
Here is a simple example of the MySQL++ API.
Of course, if you know some other SQL API (either C or C++), I encourage you to post a comment here ;)