SQLite3 - Taking function parameters as values

Question

I'm trying to make a class using sqlite3 database with a method that should insert the data in the table when the user adds the files to the application, but it is not working as intended,

    void Database::InsertSample(int Favorite, std::string Filename,
                                std::string SamplePack, int Channels, int Length,
                                int SampleRate, int Bitrate, std::string Comment,
                                std::string Path)
    {
        try
        {
            rc = sqlite3_open("Samples.db", &DB);
    
        sql = "INSERT INTO SAMPLES (FAVORITE, FILENAME, SAMPLEPACK, CHANNELS, \
                                    LENGTH, SAMPLERATE, BITRATE, BITSPERSAMPLE, PATH) \
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
    
        rc = sqlite3_prepare_v2(DB, sql.c_str(), 0, &stmt, 0);   // create the prepared statement
    
        rc = sqlite3_bind_int(stmt, 1, Favorite);
    
        rc = sqlite3_bind_text(stmt, 2, Filename.c_str(), Filename.size(), SQLITE_STATIC);
    
        rc = sqlite3_bind_text(stmt, 3, SamplePack.c_str(), SamplePack.size(), SQLITE_STATIC);
    
        rc = sqlite3_bind_int(stmt, 4, Channels);
    
        rc = sqlite3_bind_text(stmt, 5, Filename.c_str(), Filename.size(), SQLITE_STATIC);
    
        rc = sqlite3_bind_int(stmt, 6, Length);
    
        rc = sqlite3_bind_int(stmt, 7, SampleRate);
    
        rc = sqlite3_bind_int(stmt, 8, Bitrate);
    
        rc = sqlite3_bind_text(stmt, 9, Comment.c_str(), Comment.size(), SQLITE_STATIC);
    
        rc = sqlite3_bind_text(stmt, 10, Path.c_str(), Path.size(), SQLITE_STATIC);
    
        rc = sqlite3_step(stmt);
    
        rc = sqlite3_exec(DB, Sample.c_str(), NULL, 0, &ErrorMessage);
    
            if (rc != SQLITE_OK)
            {
                std::cerr << "Error! Cannot insert data into table." << std::endl;
                sqlite3_free(ErrorMessage);
            }
            else
            {
                std::cout << "Data inserted successfully." << std::endl;
            }
    
            sqlite3_close(DB);
        }
        catch (const std::exception &exception)
        {
            std::cerr << exception.what();
        }
    }

But this fails, throwing the error statement "Error! Cannot insert data into table.". Am I doing something wrong here.

I'm using this function in another class as,

void Browser::OnClickDirCtrl(wxCommandEvent& event)
{
    TagLib::FileRef File (DirCtrl->GetFilePath());
    TagLib::String Artist = File.tag()->artist();
    TagLib::String Album = File.tag()->album();
    TagLib::String Genre = File.tag()->genre();
    TagLib::String Title = File.tag()->title();
    TagLib::String Comment = File.tag()->comment();
    int Bitrate = File.audioProperties()->bitrate();
    int Channels = File.audioProperties()->channels();
    int Length = File.audioProperties()->lengthInMilliseconds();
    int LengthSec = File.audioProperties()->lengthInSeconds();
    int SampleRate = File.audioProperties()->sampleRate();

    wxVector<wxVariant> Data;
    Data.clear();
    Data.push_back(false);
    Data.push_back(TagLibTowx(Title));
    Data.push_back(TagLibTowx(Artist));
    Data.push_back(wxString::Format("%d",Channels));
    Data.push_back(wxString::Format("%d",LengthSec));
    Data.push_back(wxString::Format("%d",SampleRate));
    Data.push_back(wxString::Format("%d",Bitrate));
    Data.push_back(TagLibTowx(Comment));

    SampleListView->AppendItem(Data);

    db.InsertSample(0, Title.to8Bit(), Artist.to8Bit(), Channels, Length, SampleRate, Bitrate, Comment.to8Bit(), DirCtrl->GetFilePath().ToStdString());
}

This just a part of the function that should add the files to the database. As you can see, I am storing the path of the files in the database which is important data that I need for the project.

/---------/

EDIT: Adding a short sample,

main.cpp

#include "testdb.hpp"

int main()
{
    Database db;

    db.InsertData("Hello, World!");
    return 0;
}

testdb.hpp

#include <sqlite3.h>
#include <string>

class Database
{
    public:
        Database();
        ~Database();

    public:
        sqlite3* DB;
        int rc;
        char* ErrorMessage;
        std::string Test;
        std::string sql;

        sqlite3_stmt* stmt;

    public:
        void InsertData(std::string Path);
};

testdb.cpp

#include <exception>
#include <iostream>
#include <string>

#include "testdb.hpp"

Database::Database()
{
    /* Create SQL statement */
    Test = "CREATE TABLE TEST("
        "TEST           TEXT    NOT NULL);";

    try
    {
        rc = sqlite3_open("Test.db", &DB);
        rc = sqlite3_exec(DB, Test.c_str(), NULL, 0, &ErrorMessage);

        if (rc != SQLITE_OK)
        {
            std::cerr << "Error! Cannot create table." << std::endl;
            sqlite3_free(ErrorMessage);
        }
        else
        {
            std::cout << "Table created successfuly." << std::endl;
        }

        sqlite3_close(DB);
    }
    catch (const std::exception &exception)
    {
        std::cerr << exception.what();
    }
}

void Database::InsertData(std::string Test)
{
    try
    {
        rc = sqlite3_open("Test.db", &DB);

    sql = "INSERT INTO TEST (PATH) VALUES (?);";

    rc = sqlite3_prepare_v2(DB, sql.c_str(), 10, &stmt, 0);   // create the prepared statement
    // error handling goes here

    rc = sqlite3_bind_text(stmt, 10, Test.c_str(), Test.size(), SQLITE_STATIC);
    // error handling goes here

    rc = sqlite3_step(stmt);
    // error handling goes here

    rc = sqlite3_finalize(stmt);

        if (rc != SQLITE_OK)
        {
            std::cerr << "Error! Cannot insert data into table." << std::endl;
            sqlite3_free(ErrorMessage);
        }
        else if (rc == SQLITE_BUSY)
        {
            std::cout << "BUSY" << std::endl;
        }
        else if (rc == SQLITE_DONE)
        {
            std::cout << "DONE" << std::endl;
        }
        else if (rc == SQLITE_ERROR)
        {
            std::cout << "ERROR" << std::endl;
        }
        else if (rc == SQLITE_MISUSE)
        {
            std::cout << "MISUSE" << std::endl;
        }
        else
        {
            std::cout << "Data inserted successfully." << ErrorMessage << std::endl;
        }

        sqlite3_close(DB);
    }
    catch (const std::exception &exception)
    {
        std::cerr << exception.what();
    }
}

Database::~Database(){}

Compile using g++ main.cpp testdb.cpp -l sqlite3 -o db.

Same thing happening here, it says data inserted but database shows empty in sqlitebrowser.

Answer 1

You have a sqlite3_step followed by sqlite3_exec, which is probably not what you intended. It's certainly not good. You must call sqlite3_reset or sqlite3_finalize to complete the prepared statement. It will also provide a specific error code that better describes the error if you get one from sqlite3_step.

See this explanation of `sqlite3_step'