SQLite Visual C++ INSERT INTO 'table'

Question

I've successfully created a TABLE and am trying to insert string text into it. I get an error output when trying to sqlite3_execute(sqlstatement). Tried converting to const char*. I am new with SQLite and Visual C++ overall. What am I doing wrong? Also is there a way to successfully retrieve SQLite error message, that would help a bunch since I can't find a way of doing it with Visual c++. Thanks!

    private:System::Void SaveButton_Click(System::Object^  sender, System::EventArgs^  e) {

        // Convert double to string
        String^ convert;
        std::string MapString[16];

        String^ NameRaw = SaveName->Text;


        std::string nameString = msclr::interop::marshal_as< std::string >(NameRaw);

        // double array to string
        for (int i = 0; i < 15; i++) {
            convert = MeasurementsAndPricesPublic[i].ToString();
            std::string convertString = msclr::interop::marshal_as< std::string >(convert);
            MapString[i] = convertString;
        }

        // SQLITE DATABASE CODE

        sqlite3* db = NULL;
        sqlite3_stmt* query = NULL;


        // created table for the first run
        /*std::string generateTable = "CREATE TABLE PARAMETERS("
            "Name        TEXT      NOT NULL,"
            "PatchFL         TEXT     NOT NULL,"
            "PatchFW           TEXT     NOT NULL,"
            "PatchSL            TEXT     NOT NULL,"
            "PatchSW            TEXT     NOT NULL,"
            "PatchHL            TEXT     NOT NULL,"
            "PatchHW            TEXT     NOT NULL,"
            "AvailableFL            TEXT     NOT NULL,"
            "AvailableFW            TEXT     NOT NULL,"
            "AvailableSL            TEXT     NOT NULL,"
            "AvailableSW            TEXT     NOT NULL,"
            "AvailableHL            TEXT     NOT NULL,"
            "AvailableHW            TEXT     NOT NULL,"
            "AvailableFP            TEXT     NOT NULL,"
            "AvailableSP            TEXT     NOT NULL,"
            "AvailableHP            TEXT      NOT NULL);";*/

        // Table statement I suspect an error here
        std::string sqlstatement = "INSERT INTO PARAMETERS (Name, PatchFL, PatchFW, PatchSL, PatchSW, PatchHL, PatchHW, AvailableFL, AvailableFW, AvailableSL, AvailableSW, AvailableHL, AvailableHW, AvailableFP, AvailableSP, AvailableHP) VALUES ('"
            + nameString + "','"
            + MapString[0] + "','"
            + MapString[1] + "','"
            + MapString[2] + "','"
            + MapString[3] + "','"
            + MapString[4] + "','"
            + MapString[5] + "','"
            + MapString[6] + "','"
            + MapString[7] + "','"
            + MapString[8] + "','"
            + MapString[9] + "','"
            + MapString[10] + "','"
            + MapString[11] + "','"
            + MapString[12] + "','"
            + MapString[13] + "','"
            + MapString[14] + ");";

        // statement for testing did not work either
        const char* sql_insert = "INSERT INTO PARAMETERS (Name, PatchFL, PatchFW, PatchSL, PatchSW, PatchHL, PatchHW, AvailableFL, AvailableFW, AvailableSL, AvailableSW, AvailableHL, AvailableHW, AvailableFP, AvailableSP, AvailableHP)  VALUES (A, A, A, A, A, A, A, A, A, A, A, A, A, A, A, A);";

        // Trying to convert to char with no success
        char * buffer = new char[sqlstatement.length()];
        strcpy(buffer, sqlstatement.c_str());

        int ret = 0;

        do // avoid nested if's
        {
            // initialize engine
            if (SQLITE_OK != (ret = sqlite3_initialize()))
            {
                MessageBox::Show("Failed to initialize library. Error: ", ret.ToString());
                break;
            }
            // open connection to a DB 
            if (SQLITE_OK != (ret = sqlite3_open_v2("parameters.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL)))
            {
                MessageBox::Show("Failed to open connection to database. Error: ", ret.ToString());
                break;
            }

            // executing statement I GET ERROR HERE
            if (SQLITE_OK != (ret = sqlite3_exec(db, buffer, NULL, NULL, errmsg)))
            {
                MessageBox::Show("Failed to prepare the statement. "); //I get this message 

                break;
            }
            MessageBox::Show("Successfully saved parameters");

            //// prepare the statement
            //if (SQLITE_OK != (ret = sqlite3_prepare_v2(db, "SELECT 2012", -1, &query, NULL)))
            //{
            //  MessageBox::Show("Failed to prepare the statement. Error: ", ret.ToString(), sqlite3_errmsg(pDb).ToString());
            //  break;
            //}
            //// step to 1st row of data
            //if (sqlite_row != (ret = sqlite3_step(query))) // see documentation, this can return more values as success
            //{
            //  printf("failed to step: %d, %s\n", ret, sqlite3_errmsg(pdb));
            //  break;
            //}
            //// ... and print the value of column 0 (expect 2012 here)
            //printf("value from sqlite: %s", sqlite3_column_text(query, 0));

        } while (false);
        // cleanup
        if (NULL != query) sqlite3_finalize(query);
        if (NULL != db) sqlite3_close(db);
        sqlite3_shutdown();
    }

Answer 1

The problem you have is, that you mix C++/CX UTF-16 Platform::Strging^ with standard C UTF-8 string.

First of all, you have to convert String^ to char*. Check following question: Convert Platform::String to std::string .

Than you should prepare your query by calling sqlite3_prepare_v2:

sqlite3_stmt *query;
const char *tail;
int ser = sqlite3_prepare_v2(m_Db, "INSERT INTO PARAMETERS (a, b, c) VALUES (?, ?, ?)", -1, &result, &tail);
if (ser != SQLITE_OK) {
    const char *err = sqlite3_errmsg(m_Db);
    // report some error
}

When the statement is prepared you have to bind data to it and execute it:

sqlite3_bind_text(query, 1, "attempt", 7, SQLITE_STATIC);
sqlite3_bind_text(query, 2, "xxx", 3, SQLITE_STATIC);
sqlite3_bind_text(query, 3, "yyy", 3, SQLITE_STATIC);

if ((ser = sqlite3_step(query)) != SQLITE_DONE) {
    const char *err = sqlite3_errmsg(m_Db);
    // report some error
}   

Answer 2

Spent 5 hours digging and the solution turned out to be the missed " ' " at + MapString[14] + ");";