ODBC binding string longer than 255

[o4worksp]

I am using Poco 1.12.4 to connect PostgreSQL using ODBC .

#include <Poco/Data/ODBC/Connector.h>
#include <Poco/Data/MySQL/Connector.h>
#include <Poco/Data/Session.h>
#include <Poco/Data/Statement.h>
#include <Poco/Data/RecordSet.h>
#include <iostream>
#include <Poco/Exception.h>
using namespace Poco::Data::Keywords;

int main()
{
    try
    {
        Poco::Data::ODBC::Connector::registerConnector();
        
        std::stringstream connect_str;
        std::string con_str = "Driver={PostgreSQL};Server=127.0.0.1;Port=5432;Databas=postgres;Uid=postgres;Pwd=123ABCdef;";
        Poco::Data::Session session("ODBC", con_str);
        Poco::Data::Statement st(session);

        try
        {
           
            int id;
            std::string file_path = "超长文件名称的沙发上粉色的方法的沙发上粉色的方法的沙发上粉色的方法的沙发上粉色的方法的沙发上粉色的方法的沙发上粉色的方法的沙发上粉色的方法的沙发上粉色的方法的沙发上粉色的方法的沙发上粉色的方法的沙发上粉色的方法的沙发上粉色的方法的沙发上粉色的方法的沙发上粉色的方法的沙发上粉色的方法的沙发上粉色的方法的沙发上.docx";
            st << "select id from \"test_bind\" where file_path = ?", use(file_path), into(id), now;
            std::cout << id << std::endl;

        }
        catch (const Poco::Exception &ex)
        {
            std::cerr << ex.displayText() << '\n';
        }
    }
    catch (const Poco::Exception &ex)
    {
        std::cerr << "Error: " << ex.displayText() << std::endl;
    }
    Poco::Data::ODBC::Connector::unregisterConnector();

    return 0;
}

create table sql

CREATE TABLE IF NOT EXISTS TEST_BIND (
ID BIGSERIAL NOT NULL,
FILE_PATH VARCHAR(1024) DEFAULT NULL
);

output:

Data too long : Error binding column 0 size=467 ,max size=255

question:

how to bind string longer than 255 using odbc?

[aleks-f]

poco/Data/ODBC/testsuite/src/ODBCPostgreSQLTest.cpp

Line 174 in 1c6e5aa

session().setProperty("maxFieldSize", Poco::Any(maxFldSize-1));

[o4worksp]

I solved the problem by adding "MaxVarcharSize" to the connection string after reading this test code.

[bas524]

Hi!
First of all, this is a fail of odbc. You can verify that SQLGetTypeInfo returns 254 on most string-like type (SQL_CHAR, SQL_VARCHAR).

One more workaround for you case is using Poco::format mode for sql, for example,

Poco::Data::Statement stmt = (session() << "SELECT id FROM \"%s\" WHERE file_path = '%s'", into(id), "test_bind", file_path);
stmt.execute();

[o4worksp]

It's true that this approach you said can help solve the problem, but currently the architecture can only use the 'use' method.