How to debug SQL statements created by boost::mysql::with_params()?

Other Questions MySQL
1

I’m working on a database application using boost::mysql with boost::asio to connect to MariaDB. My program manages projects and their associated tasks.

I keep getting MySQL Server Error: er_parse_error [mysql.common-server:1064] when trying to insert records that contain date fields. Other inserts work fine when no dates are involved.

Is there a way to see the actual SQL query that boost::mysql::with_params() generates? I need to examine the final SQL statement to figure out what’s causing the parse error.

I’ve attempted using both boost::mysql::date objects and string representations for the date values, but both approaches result in the same parsing error.

My setup:

  • Ubuntu 24.04
  • g+±14 compiler
  • C++23 standard
  • boost 1.88
  • MariaDB 10.11.13
#include <boost/asio.hpp>
#include <boost/mysql.hpp>
#include <chrono>
#include "DatabaseManager.h"
#include <exception>
#include <iostream>
#include <optional>
#include <string>
#include <sstream>

static std::string formatDateString(std::chrono::year_month_day inputDate)
{
    std::stringstream stream;
    stream << inputDate;
    return "'" + stream.str() + "'";
}

static boost::asio::awaitable<void> async_create_project(ProjectModel project)
{
    std::optional<std::size_t> parentProjectID = project.getParentID();
    std::optional<unsigned int> currentStatus = static_cast<unsigned int>(project.getCurrentStatus());
    std::optional<boost::mysql::date> actualStartDate;
    std::optional<boost::mysql::date> projectedEndDate;
    std::optional<boost::mysql::date> finishedDate;
    std::string creationTimestamp = formatDateString(project.getCreatedDate());
    std::string deadline = formatDateString(project.getDeadline());
    std::string plannedStart = formatDateString(project.getPlannedStartDate());

    boost::mysql::any_connection dbConnection(co_await boost::asio::this_coro::executor);

    co_await dbConnection.async_connect(connectionConfig);

    boost::mysql::results queryResult;
    co_await dbConnection.async_execute(
        boost::mysql::with_params(
            "INSERT INTO ProjectManagementDB.Projects ("
                "CreatedBy, AssignedTo, Title, ParentProject, Status, CompletionPercent, CreatedAt, Deadline, PlannedStart"
                "ActualStart, EstimatedFinish, CompletedAt, EstimatedHours, ActualHours, PriorityGroup, GroupOrder"
                ") VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15})",
            project.getCreatorID(),
            project.getAssigneeID(),
            project.getTitle(),
            parentProjectID,
            currentStatus,
            project.getCompletionRate(),
            creationTimestamp,
            deadline,
            plannedStart,
            actualStartDate,
            projectedEndDate,
            finishedDate,
            project.getEstimatedHours(),
            project.getActualHours(),
            project.getPriorityGroup(),
            project.getOrderInGroup()
        ),
        queryResult
    );

    std::cout << "Project created successfully with ID:" << queryResult.last_insert_id() << std::endl;

    co_await dbConnection.async_close();
}

bool DatabaseManager::addProjectToDatabase(ProjectModel& project)
{
    resetErrorMessages();

    if (project.existsInDatabase())
    {        
        addErrorMessage("Project already exists in database.\n");
        return false;
    }
    if (!project.hasAllRequiredData())
    {
        addErrorMessage(project.getMissingFieldsReport());
        return false;
    }

    boost::asio::io_context ioContext;

    boost::asio::co_spawn(
        ioContext,
        [=] { return async_create_project(project); },
        [](std::exception_ptr exceptionPtr) {
            if (exceptionPtr)
            {
                std::rethrow_exception(exceptionPtr);
            }
        }
    );

    try
    {
        ioContext.run();
    }
    catch (const std::exception& ex)
    {
        std::string errorMsg("Database Error: ");
        errorMsg += ex.what();
        addErrorMessage(errorMsg);
        return false;
    }

    return true;
}
2

Try enabling MariaDB’s binary log and use mysqlbinlog to see what’s actually being executed. Run SHOW VARIABLES LIKE 'log_bin'; to check if it’s on. I see you’re manually converting dates to strings before passing them to parameterized queries - that defeats the whole point of prepared statements. The boost::mysql library wants raw date values with with_params() since it handles escaping automatically. For quick debugging, swap your parameterized query for a regular string query so you can print the full SQL before execution. You’ll see exactly what’s hitting the database. Once you find the problem, switch back to parameterized queries for security.

3

Your issue is mixing string formatting with parameterized queries. When you use boost::mysql::with_params(), don’t manually wrap date strings in quotes - the library does that for you.

For debugging the SQL, boost::mysql doesn’t let you see the final query since it uses prepared statements. But you can turn on MariaDB’s query log with SET GLOBAL general_log = 'ON'; and check what’s actually running.

Also caught a syntax error - you’re missing a comma between PlannedStart and ActualStart in your column list. Drop the manual quotes around your dates and pass the raw values straight to with_params().