Soci/sqlite throwing bad_cast on any row.get<int64_t>

I've got an established C++ program using SOCI/SQlite. I need to write a type conversion that will retrieve largish integers into 64-bit int class members. AFAIK I need to use row.get<int64_t>(myvar) or something like it.

On Windows, this explicit templated get is throwing std::bad_cast. On Linux, it appears to be overflowing an int32, and returning a value of -1. Everything magically works if I change my columns to BIGINT, but I'd rather not -- my values are all under 64 bits.

Advice welcome!

MCVE:

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

int main() {
    try {
        // Create an in-memory SQLite database session
        soci::session sql(soci::sqlite3, ":memory:");
        sql << "CREATE TABLE test_table (id INTEGER PRIMARY KEY, myInt INTEGER);";
        sql << "INSERT INTO test_table (myInt) VALUES (9223372036854775807);";

        // This works
        int64_t retrievedValue1;
        sql << "SELECT myInt FROM test_table WHERE id = 1", soci::into(retrievedValue1);
        std::cout << "Implicit retrieval: " << retrievedValue1 << std::endl;

        // This throws std::bad_cast (windows) or overflows int32 for a -1 (linux)
        soci::row r;
        sql << "SELECT myInt FROM test_table WHERE id = 1", soci::into(r);
        int64_t retrievedValue2 = r.get<int64_t>("myInt");
        std::cout << "Explict 64-bit retrieval: " << retrievedValue2 << std::endl;

    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }

    return 0;
}

Windows/Visual Studio vcpkg.json:

{
  "version": "1.0",
  "builtin-baseline": "c82f74667287d3dc386bce81e44964370c91a289",
  "dependencies": [
    {
      "name": "soci",
      "features": [
        "sqlite3"
      ]
    }
  ]
}

Linux CMakeLists.txt. Needs soci in /usr/local/

cmake_minimum_required(VERSION 3.10)
project(CppSQLiteSociExample)

# Set C++ standard
set(CMAKE_CXX_STANDARD 17)
set(CMAKE_CXX_STANDARD_REQUIRED ON)

# Set paths to Soci
set(SOCI_INCLUDE_DIR "/usr/local/include/soci")
set(SOCI_LIBRARY "/usr/local/lib/libsoci_sqlite3.so")

# Find SQLite3 package
find_package(SQLite3 REQUIRED)

# Check if Soci includes and libraries are found
if(NOT EXISTS "${SOCI_INCLUDE_DIR}/soci.h" OR NOT EXISTS "${SOCI_LIBRARY}")
    message(FATAL_ERROR "Uggggh.  Soci library or header files not found!")
endif()

# Add your source files
add_executable(c c.cpp)

# Include Soci headers and link libraries
target_include_directories(c PRIVATE ${SOCI_INCLUDE_DIR})

target_link_libraries(c PRIVATE
    SQLite::SQLite3
    /usr/local/lib/libsoci_sqlite3.so
    /usr/local/lib/libsoci_core.so
)

# Ensure linker can find the libraries
link_directories(/usr/local/lib)

Update: Added SOCI bug report here.

The upcoming version 4.1 of SOCI will have fixed the compile error on Windows (which is what this question references in its title) by allowing automatic widening conversions for integers. That is, as long as the integer type you provide to soci::row::get<>() is at least as wide as the integer type SOCI uses internally, the call will succeed.

Relevant upstream PR: https://github.com/SOCI/soci/pull/1127

However, as of now, SOCI will still overflow the 32bit integer on Linux so that it'll return -1 in row::get<>(). I have created a new issue for this problem specifically: https://github.com/SOCI/soci/issues/1190.

As has been mentioned in the question's comments, this can be fixed by specifying the column type as BIGINT. At a fundamental level, this is an issue in that SQLite doesn't really have the concept of rigorous data types, whereas SOCI does.