Code:
// DB öffnen oder neu erstellen
int retCode = 0;
sqlite3* hDB;
retCode = sqlite3_open_v2(
// SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, SQLITE_OPEN_READONLY
DB_FILENAME, &hDB, SQLITE_OPEN_READWRITE, NULL);
if (retCode!=SQLITE_OK) {
printError (hDB, "Cannot create SQLITE connection.", retCode, true);
}
else
{
sqlite3_stmt* hStmt;
WCSTR pzTail = NULL;
// SELECT operation
WString sql = "select * from test";
retCode = sqlite3_prepare_v2(
hDB,
(WCSTR) sql,
sql.length (),
&hStmt,
&pzTail
);
if (retCode!=SQLITE_OK)
{
printError (hDB, "Cannot create prepared statement.", retCode, true);
}
bool fFetch = true;
while (fFetch)
{
retCode = sqlite3_step(hStmt);
switch (retCode)
{
case SQLITE_ROW:
// Ergebnis-Zeile
printf ("%u Spalten\r\n", (W32) sqlite3_data_count(hStmt)); // Anzahl Spalten im Ergebnis
// printf ("%s\r\n", (WCSTR) sqlite3_column_decltype(hStmt,0)); // Datentypname der 1. Spalte
// printf ("%u\r\n", (WCSTR) sqlite3_column_type(hStmt,0)); // Datentyp-ID der 1. Spalte
// sqlite3_column_origin_name = Name der Spalte, geht nur, wenn
// Präprozessor SQLITE_ENABLE_COLUMN_METADATA aktiviert.
printf ("%s: %s\r\n", (WCSTR) sqlite3_column_origin_name(hStmt,0), (WCSTR) sqlite3_column_text (hStmt, 0));
printf ("%s: %s\r\n", (WCSTR) sqlite3_column_origin_name(hStmt,1), (WCSTR) sqlite3_column_text (hStmt, 1));
printf ("%s: %f\r\n", (WCSTR) sqlite3_column_origin_name(hStmt,2), (double) sqlite3_column_double (hStmt, 2));
printf ("%s: %i\r\n", (WCSTR) sqlite3_column_origin_name(hStmt,3), (int) sqlite3_column_int (hStmt, 3));
printf ("%s: %f\r\n", (WCSTR) sqlite3_column_origin_name(hStmt,4), (double) sqlite3_column_double (hStmt, 4));
printf ("---------\r\n\r\n");
break;
case SQLITE_DONE:
fFetch=false;
break;
case SQLITE_BUSY:
printf ("SQLITE_BUSY\r\n");
sqlite3_sleep(100);
break;
case SQLITE_ERROR:
printf ("SQLITE_ERROR: %i / %s\r\n",
(int) sqlite3_errcode(hDB), (WCSTR) sqlite3_errmsg (hDB));
fFetch=false;
break;
case SQLITE_MISUSE:
printf ("SQLITE_MISUSE\r\n");
fFetch =false;
break;
}
};
retCode = sqlite3_finalize(hStmt);
hStmt = NULL;
// Vorbereitetes Statement mit INSERT (?=Platzhalter für Parameter)
sql = "insert into test (a,b,c,d,j) values (?,?,?,?,?)";
retCode = sqlite3_prepare_v2(
hDB,
(WCSTR) sql,
sql.length (),
&hStmt,
&pzTail
);
if (retCode!=SQLITE_OK)
{
printError (hDB, "Cannot create prepared statement.", retCode, true);
}
WString firstname = "Schopenhauer", timestamp = "1800-01-01 00:00:00";
// SQLITE_TRANSIENT erzeugt eine Kopie des Parameters im Speicher
sqlite3_bind_text (hStmt, 1, (WCSTR) firstname, firstname.length (), SQLITE_STATIC); // SQLITE_TRANSIENT
sqlite3_bind_text (hStmt, 2, (WCSTR) timestamp, timestamp.length (), SQLITE_STATIC);
sqlite3_bind_double (hStmt, 3, (double) 99.999);
sqlite3_bind_int (hStmt, 4, (int) 88);
sqlite3_bind_double (hStmt, 5, (double) 88.2883998282); // Wird automatisch gerundet, wenn Spalte z.B.B NUMBER(10,5)
// sqlite3_clear_bindings (hStmt);
fFetch = true;
while (fFetch)
{
retCode = sqlite3_step(hStmt);
switch (retCode)
{
case SQLITE_ROW:
printf ("SQLITE_ROW\r\n");
break;
case SQLITE_DONE:
printf ("%u rows changed (last insert row id=%u)\r\n",
(W32) sqlite3_changes (hDB),
(W32) sqlite3_last_insert_rowid (hDB));
fFetch=false;
break;
case SQLITE_BUSY:
printf ("SQLITE_BUSY\r\n");
sqlite3_sleep(100);
break;
case SQLITE_ERROR:
printf ("SQLITE_ERROR: %i / %s\r\n",
(int) sqlite3_errcode(hDB), (WCSTR) sqlite3_errmsg (hDB));
fFetch=false;
break;
case SQLITE_MISUSE:
printf ("SQLITE_MISUSE\r\n");
fFetch =false;
break;
}
};
retCode = sqlite3_reset (hStmt);
// Nach sqlite3_reset kann man neu 'inserten'
// Statement schließen
retCode = sqlite3_finalize(hStmt);
hStmt = NULL;
retCode = sqlite3_close (hDB);
hDB = NULL;
}