I'm using boost::mysql and boost:asio to access a MariaDB database. I'm prototyping a project/task management system. A task has a number of dates associated with it. When attempting to insert a task into the database I am getting a MySQL Server Error: er_parse_error [mysql.common-server:1064] exception. I am able to insert into other tables that don't require dates.
How do I view the SQL code generated by boost::mysql::with_params so I can debug the issue?
I have tried boost::mysql::date as a input as well as strings, both generate the parse error.
#include <boost/asio.hpp>
#include <boost/mysql.hpp>
#include <chrono>
#include "DBInterface.h"
#include <exception>
#include <iostream>
#include <optional>
#include <string>
#include <string_view>
#if 0
static boost::mysql::date convertChronoDateToBoostMySQLDate(std::chrono::year_month_day source)
{
std::chrono::sys_days tp = source;
boost::mysql::date boostDate(tp);
return boostDate;
}
#endif
static std::string dateToString(std::chrono::year_month_day taskDate)
{
std::stringstream ss;
ss << taskDate;
return "'" + ss.str() + "'";
}
static boost::asio::awaitable<void> coro_insert_task(TaskModel task)
{
std::optional<std::size_t> parentTaskID = task.getParentTaskID();
std::optional<unsigned int> status = static_cast<unsigned int>(task.getStatus());
std::optional<boost::mysql::date> actualStart;
std::optional<boost::mysql::date> estimatedCompleteDate;
std::optional<boost::mysql::date> completeDate;
std::string createdOn = dateToString(task.getCreationDate());
std::string dueDate = dateToString(task.getDueDate());
std::string scheduledStart = dateToString(task.getScheduledStart());
boost::mysql::any_connection conn(co_await boost::asio::this_coro::executor);
co_await conn.async_connect(dbConnectionParameters);
boost::mysql::results result;
co_await conn.async_execute(
boost::mysql::with_params(
"INSERT INTO PlannerTaskScheduleDB.Tasks ("
"CreatedBy, AsignedTo, Description, ParentTask, Status, PercentageComplete CreatedOn RequiredDelivery ScheduledStart"
"ActualStart EstimatedCompletion Completed EstimatedEffortHours ActualEffortHours SchedulePriorityGroup PriorityInGroup"
") VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15})",
task.getCreatorID(),
task.getAssignToID(),
task.getDescription(),
parentTaskID,
status,
task.getPercentageComplete(),
createdOn,
dueDate,
scheduledStart,
actualStart,
estimatedCompleteDate,
completeDate,
task.getEstimatedEffort(),
task.getactualEffortToDate(),
task.getPriorityGoup(),
task.getPriority()
),
result
);
std::cout << "Successfully created task with ID: " << result.last_insert_id() << std::endl;
co_await conn.async_close();
}
bool DBInterface::insertIntoDataBase(TaskModel& task)
{
clearPreviousErrors();
if (task.isInDataBase())
{
appendErrorMessage("The task is already in the database.\n");
return false;
}
if (!task.allRequiredFieldsHaveData())
{
appendErrorMessage(task.reportMissingRequiredFields());
return false;
}
boost::asio::io_context ctx;
// Launch our coroutine
boost::asio::co_spawn(
ctx,
[=] { return coro_insert_task(task); },
// If any exception is thrown in the coroutine body, rethrow it.
[](std::exception_ptr ptr) {
if (ptr)
{
std::rethrow_exception(ptr);
}
}
);
try
{
ctx.run();
}
catch (const std::exception& e)
{
std::string eMsg("MySQL Server Error: ");
eMsg += e.what();
appendErrorMessage(eMsg);
return false;
}
return true;
}
You are missing a lot of comma's in the insertion statement. There was also no whitespace between ScheduledStart and ActualStart.
That said, with_params is expressly NOT prepared statements, but Text queries and client-side SQL formatting.
You can get the query test with format_sql:
std::string sql_text = boost::mysql::format_sql(
conn.format_opts().value(),
R"sql(INSERT INTO PlannerTaskScheduleDB.Tasks (
CreatedBy, AsignedTo, Description, ParentTask, Status,
PercentageComplete, CreatedOn, RequiredDelivery,
ScheduledStart, ActualStart, EstimatedCompletion,
Completed, EstimatedEffortHours, ActualEffortHours,
SchedulePriorityGroup, PriorityInGroup)
VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}))sql",
task.getCreatorID(), task.getAssignToID(), task.getDescription(), parentTaskID, status,
task.getPercentageComplete(), createdOn, dueDate, scheduledStart, actualStart, estimatedCompleteDate,
completeDate, task.getEstimatedEffort(), task.getactualEffortToDate(), task.getPriorityGoup(),
task.getPriority());
It compiles fine for me:
Live On Coliru
#include <boost/asio.hpp>
#include <boost/mysql.hpp>
#include <chrono>
#include <exception>
#include <iostream>
#include <optional>
#include <string>
#include <string_view>
struct TaskModel {
// Assume these methods are defined in TaskModel
std::optional<std::size_t> getParentTaskID() const { return 42; }
unsigned int getCreatorID() const { return 1; }
unsigned int getAssignToID() const { return 1; }
std::string getDescription() const { return "Sample Task"; }
unsigned int getStatus() const { return 1; } // Assume 1 is a valid status
double getPercentageComplete() const { return 0.5; } // Assume 50% complete
std::chrono::year_month_day getCreationDate() const {
return {std::chrono::year{2023}, std::chrono::month{10}, std::chrono::day{1}};
}
std::chrono::year_month_day getDueDate() const {
return {std::chrono::year{2023}, std::chrono::month{10}, std::chrono::day{31}};
}
std::chrono::year_month_day getScheduledStart() const {
return {std::chrono::year{2023}, std::chrono::month{10}, std::chrono::day{5}};
}
double getEstimatedEffort() const { return 10.0; } // Assume 10 hours estimated effort
double getactualEffortToDate() const { return 5.0; } // Assume 5 hours actual effort to date
unsigned int getPriorityGoup() const { return 1; } // Assume 1 is a valid priority group
unsigned int getPriority() const { return 1; } // Assume 1 is a valid priority
bool isInDataBase() const { return false; } // Assume the task is not in the database
bool allRequiredFieldsHaveData() const { return true; } // stub
std::string reportMissingRequiredFields() const { return {}; } // stub
};
struct DBInterface {
void clearPreviousErrors(){}
void appendErrorMessage(const std::string& /*message*/){}
bool insertIntoDataBase(TaskModel& task);
};
#if 0
static boost::mysql::date convertChronoDateToBoostMySQLDate(std::chrono::year_month_day source)
{
std::chrono::sys_days tp = source;
boost::mysql::date boostDate(tp);
return boostDate;
}
#endif
static std::string dateToString(std::chrono::year_month_day taskDate)
{
std::stringstream ss;
ss << taskDate;
return "'" + ss.str() + "'";
}
static const boost::mysql::connect_params dbConnectionParameters{
{}, // Hostname or IP address
"planner_db", // Database name
"user", // Username
"password" // Password
};
static boost::asio::awaitable<void> coro_insert_task(TaskModel task)
{
std::optional<std::size_t> parentTaskID = task.getParentTaskID();
std::optional<unsigned int> status = static_cast<unsigned int>(task.getStatus());
std::optional<boost::mysql::date> actualStart;
std::optional<boost::mysql::date> estimatedCompleteDate;
std::optional<boost::mysql::date> completeDate;
std::string createdOn = dateToString(task.getCreationDate());
std::string dueDate = dateToString(task.getDueDate());
std::string scheduledStart = dateToString(task.getScheduledStart());
boost::mysql::any_connection conn(co_await boost::asio::this_coro::executor);
co_await conn.async_connect(dbConnectionParameters);
std::string sql_text = boost::mysql::format_sql(
conn.format_opts().value(),
R"sql(INSERT INTO PlannerTaskScheduleDB.Tasks (
CreatedBy, AsignedTo, Description, ParentTask, Status,
PercentageComplete, CreatedOn, RequiredDelivery,
ScheduledStart, ActualStart, EstimatedCompletion,
Completed, EstimatedEffortHours, ActualEffortHours,
SchedulePriorityGroup, PriorityInGroup)
VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}))sql",
task.getCreatorID(), task.getAssignToID(), task.getDescription(), parentTaskID, status,
task.getPercentageComplete(), createdOn, dueDate, scheduledStart, actualStart, estimatedCompleteDate,
completeDate, task.getEstimatedEffort(), task.getactualEffortToDate(), task.getPriorityGoup(),
task.getPriority());
std::cout << "Running: " << sql_text << std::endl;
boost::mysql::results result;
co_await conn.async_execute( //
boost::mysql::with_params(
R"(INSERT INTO PlannerTaskScheduleDB.Tasks ( CreatedBy, AsignedTo,
Description, ParentTask, Status, PercentageComplete, CreatedOn,
RequiredDelivery, ScheduledStart, ActualStart, EstimatedCompletion, Completed,
EstimatedEffortHours, ActualEffortHours, SchedulePriorityGroup, PriorityInGroup
) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15})
)",
task.getCreatorID(), task.getAssignToID(), task.getDescription(), parentTaskID, status,
task.getPercentageComplete(), createdOn, dueDate, scheduledStart, actualStart,
estimatedCompleteDate, completeDate, task.getEstimatedEffort(), task.getactualEffortToDate(),
task.getPriorityGoup(), task.getPriority()),
result);
std::cout << "Successfully created task with ID: " << result.last_insert_id() << std::endl;
co_await conn.async_close();
}
bool DBInterface::insertIntoDataBase(TaskModel& task)
{
clearPreviousErrors();
if (task.isInDataBase())
{
appendErrorMessage("The task is already in the database.\n");
return false;
}
if (!task.allRequiredFieldsHaveData())
{
appendErrorMessage(task.reportMissingRequiredFields());
return false;
}
boost::asio::io_context ctx;
// Launch our coroutine
boost::asio::co_spawn(
ctx,
[=] { return coro_insert_task(task); },
// If any exception is thrown in the coroutine body, rethrow it.
[](std::exception_ptr ptr) {
if (ptr)
{
std::rethrow_exception(ptr);
}
}
);
try
{
ctx.run();
}
catch (const std::exception& e)
{
std::string eMsg("MySQL Server Error: ");
eMsg += e.what();
appendErrorMessage(eMsg);
return false;
}
return true;
}
#define BOOST_MYSQL_SEPARATE_COMPILATION 1
#include <boost/mysql/src.hpp>
int main() {
}
Consider using prepared statements for safety and performance.