[sqlite] Problem with select

[deltagam...@gmx.net]

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