You missed the prepare for your select_sql:
sqlite3_stmt *select_stmt = NULL;
sqlite3_prepare_v2(db,select_sql,strlen(select_sql),&select_stmt,NULL);
Add that one line and you get:
Successfully bound string for insert: 'zweiter Anlauf/Versuch'
Successfully bound real for insert: 22
INSERT completed
Found row
Column eventtype(0): 'zweiter Anlauf/Versuch'
Column counter(1): '22'
SELECT successfully completed
[mblack@melb0113 sqlite]$
If you close and re-open the database last_row_id won't know anything. That
only lasts for your session.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of deltagam...@gmx.net [deltagam...@gmx.net]
Sent: Wednesday, June 20, 2012 8:03 AM
To: sqlite-users
Subject: EXT :[sqlite] Problem with select
Hello,
I have a problem with the selection of the row, errorcode 21 s
Indeed I would like to retrieve all rows, but as far as I understand it,
this construction will retrieve only the last row/insert.
What happens if the db is closed after the insert and reopened then for
the select-statement ?
===================================================
#include <stdio.h>
#include <stdlib.h>
#include "sqlite3.h"
#include <ctime>
#include <iostream>
#include <string>
#include <fstream>
using namespace std;
int main() {
string filename = "errorlog.txt";
std::ofstream outfile(filename);
char eventtype[]="zweiter Anlauf/Versuch";
int zaehler = strlen(eventtype);
int rc;
char *exec_errmsg;
const char dbname[] = "ef.db";
sqlite3 *db = NULL;
rc = sqlite3_open(dbname, &db);
if(SQLITE_OK != rc) {
outfile << "Can't open database "<< dbname << " (" << rc << "):
" << sqlite3_errmsg(db) << std::endl;
//fprintf(stderr, "Can't open database %s (%i): %s\n", dbname,
rc, sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
///////////////////////////////////// maybe discard this part
///////////////////
const char create_sql[] = "CREATE TABLE eventlog ("
"id INTEGER PRIMARY KEY,"
"eventdate DATETIME default current_timestamp,"
"eventtype TEXT,"
"counter INTEGER"
")";
// Use exec to run simple statements that can only fail/succeed
rc = sqlite3_exec(db, create_sql, NULL, NULL, &exec_errmsg);
if(SQLITE_OK != rc) {
outfile << "Error creating table (" << rc << "): " <<
sqlite3_errmsg(db) << std::endl;
//fprintf(stderr, "Error creating table (%i): %s\n", rc,
exec_errmsg);
sqlite3_free(exec_errmsg);
//sqlite3_close(db);
//exit(1);
}
///////////////////////////////////// maybe discard this part
///////////////////
const char insert_sql[] = "INSERT INTO eventlog (eventtype,
counter) VALUES (?,?)";
sqlite3_stmt *insert_stmt = NULL;
rc = sqlite3_prepare_v2(db, insert_sql, -1, &insert_stmt, NULL);
if(SQLITE_OK != rc) {
outfile << "Can't prepare insert statment " << insert_sql << "
(" << rc << "): " << sqlite3_errmsg(db) << std::endl;
//fprintf(stderr, "Can't prepare insert statment %s (%i):
%s\n", insert_sql, rc, sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
rc = sqlite3_bind_text(insert_stmt, 1, eventtype,
strlen(eventtype), NULL);
if(SQLITE_OK != rc) {
outfile << "Error binding value in insert (%i): %s\n", rc,
sqlite3_errmsg(db);
//fprintf(stderr, "Error binding value in insert (%i): %s\n",
rc, sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
} else {
printf("Successfully bound string for insert: '%s'\n", eventtype);
}
rc = sqlite3_bind_int(insert_stmt, 2, zaehler);
if(SQLITE_OK != rc) {
outfile << "Error binding value in insert (%i): %s\n", rc,
sqlite3_errmsg(db);
//fprintf(stderr, "Error binding value in insert (%i): %s\n",
rc, sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
} else {
printf("Successfully bound real for insert: %d\n", zaehler);
}
rc = sqlite3_step(insert_stmt);
if(SQLITE_DONE != rc) {
outfile << "insert statement didn't return DONE (%i): %s\n",
rc, sqlite3_errmsg(db);
//fprintf(stderr, "insert statement didn't return DONE (%i):
%s\n", rc, sqlite3_errmsg(db));
} else {
printf("INSERT completed\n\n");
}
// start selecting ///////////////////////////////
const char select_sql[] = "SELECT eventtype, counter FROM eventlog
WHERE id=?";
//const char insert_sql[] = "INSERT INTO eventlog (eventtype,
counter) VALUES (?,?)";
sqlite3_stmt *select_stmt = NULL;
// Now attempt to get that row out
sqlite3_int64 id = sqlite3_last_insert_rowid(db);
sqlite3_bind_int64(select_stmt, 1, id);
// This is your standard pattern
while(SQLITE_ROW == (rc = sqlite3_step(select_stmt))) {
int col;
printf("Found row\n");
for(col=0; col<sqlite3_column_count(select_stmt); col++) {
// Note that by using sqlite3_column_text, sqlite will
coerce the value into a string
printf("\tColumn %s(%i): '%s'\n",
sqlite3_column_name(select_stmt, col), col,
sqlite3_column_text(select_stmt, col));
}
}
if(SQLITE_DONE != rc) {
fprintf(stderr, "select statement didn't finish with DONE (%i):
%s\n", rc, sqlite3_errmsg(db));
} else {
printf("\nSELECT successfully completed\n");
}
sqlite3_finalize(insert_stmt);
sqlite3_finalize(select_stmt);
sqlite3_close(db);
outfile.close();
return (0);
} // main
===================================================================
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
