soci sample code crash under msys2+mingw64

[asmwarrior]

Hi, I just installed this library by the command pacman -S mingw-w64-x86_64-soci under msys2.

I created a very simple sample code and a test database(I'm running Mysql 8.0.33 under Windows).

Here is the sample code:

#include <soci/soci.h>
#include <soci/mysql/soci-mysql.h>
#include <iostream>

int main()
{
    soci::session sql(soci::mysql, "dbname=testdb user=root password=123456");

    int count;
    sql << "SELECT COUNT(*) FROM testtable", soci::into(count);

    std::cout << "There are " << count << " rows in testtable" << std::endl;

    soci::rowset<soci::row> rows = (sql.prepare << "SELECT * FROM testtable");

    for(soci::row& row : rows)
    {
        std::cout << "id = " << row.get<int>("id")
                  << ", name = " << row.get<std::string>("name")
                  << ", age = " << row.get<int>("age")
                  << std::endl;
    }

    return 0;
}

The code builds OK under my msys2(Code::Blocks + MinGW64 compiler).

When I run the app, I see that it does correctly print the count of the rows as:

There are 2 rows in testtable

But after that, it just crashes in the line soci::rowset<soci::row> rows = (sql.prepare << "SELECT * FROM testtable");

Any ideas?

My table are something like below: (I just export the content of the database from HeidiSQL)

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               8.0.33 - MySQL Community Server - GPL
-- Server OS:                    Win64
-- HeidiSQL Version:             12.5.0.6677
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Dumping database structure for testdb
CREATE DATABASE IF NOT EXISTS `testdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `testdb`;

-- Dumping structure for table testdb.testtable
CREATE TABLE IF NOT EXISTS `testtable` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  `Column 2` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table testdb.testtable: ~2 rows (approximately)
REPLACE INTO `testtable` (`id`, `name`, `age`, `Column 2`) VALUES
    (1, 'jhon', 23, '{"pid": 101, "name": "name1"}'),
    (3, 'halli', 43, NULL);

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;

Also, are there any document or sample code for a beginner to learn this library? Thanks. I don't see much document/samples, for example, I see here: Simple SQL statements

[asmwarrior]

OK, I try to add a C++ try and catch block to see what is the error message:

#include <soci/soci.h>
#include <soci/mysql/soci-mysql.h>
#include <iostream>

int main()
{
    try
    {
        soci::session sql(soci::mysql, "dbname=testdb user=root password=123456");

        int count;
        sql << "SELECT COUNT(*) FROM testtable", soci::into(count);

        std::cout << "There are " << count << " rows in testtable" << std::endl;

        soci::rowset<soci::row> rows = (sql.prepare << "SELECT * FROM testtable");

        for(soci::row& row : rows)
        {
            std::cout << "id = " << row.get<int>("id")
                      << ", name = " << row.get<std::string>("name")
                      << ", age = " << row.get<int>("age")
                      << std::endl;
        }

    }
    catch(const soci::soci_error& e)
    {
        std::cerr << "Error: " << e.what() << std::endl;
        return 1;
    }

    return 0;
}

And the result is:

There are 2 rows in testtable
Error: Unknown data type while executing "SELECT * FROM testtable".

I looks like this may caused by the reason I have a “json" column type. I will try test again.

[asmwarrior]

OK, it looks like the soci library does not know the "json" column in my database, I just change that column type to "binary", and now it works!

Another question: How to use the "json" colum? Maybe it just like a binary type?

[vadz]

JSON fields in MySQL are handled as just strings currently.

I don't think there is any bug here, an exception is not a crash and you do need to handle them when using SOCI, so I'm closing this.

[asmwarrior]

OK, thanks.