ODBC-parameter of type BIGINT doesn't seem to casted by the driver

Поиск
Список
Период
Сортировка
От Kevin Verfaille
Тема ODBC-parameter of type BIGINT doesn't seem to casted by the driver
Дата
Msg-id CAMqvpvLrPC8UO5Z59jhVfSZf1xxShDqrok6Kp+LF4BovkHB1hw@mail.gmail.com
обсуждение исходный текст
Список pgsql-odbc
Hi,

I am trying to put an bigint parameter into the below query, but I get an unexpected error.
Is there anything we are doing wrong (given the info below) or is this a bug?

SystemInfo:
ODBC driver version: 13.02 (also tested on: 12.1 Ubuntu20.04/odbc-postgresql)
PostgreSQL DB Version: 14.7 (also tested on: 15.2)

Query:
WITH data_def AS (
    SELECT 1::bigint BIGINT_ID
), data_param AS (
    SELECT ? AS BIGINT_ID_EXPECTED
)
SELECT *
FROM data_def
WHERE BIGINT_ID IN (SELECT BIGINT_ID_EXPECTED FROM data_param LIMIT 1);

ODBC-log parameter binding log & errors:
[7fffea581d40]    bind.c[PGAPI_BindParameter]138: ipar=0, paramType=1, fCType=-25, fSqlType=-5, cbColDef=0, ibScale=6,rgbValue=0x19457b0(8), pcbValue=0x197d170
[7fffea581d40]connection[handle_pgres_error]881: [QLOG] ERROR(ERROR) 42883 'operator does not exist: bigint = text'
[7fffea581d40]connection[handle_pgres_error]950: error message=ERROR: operator does not exist: bigint = text(45)

Full ODBC-log:

[7fffea581d40] pgapi30.c[PGAPI_GetStmtAttr]1561: entering Handle=0x44a7730 10010
[7fffea581d40]odbcapi30.[SQLGetStmtAttr]411: Entering Handle=0x44a7730 10011
[7fffea581d40] pgapi30.c[PGAPI_GetStmtAttr]1561: entering Handle=0x44a7730 10011
[7fffea581d40]odbcapi30.[SQLGetStmtAttr]411: Entering Handle=0x44a7730 10012
[7fffea581d40] pgapi30.c[PGAPI_GetStmtAttr]1561: entering Handle=0x44a7730 10012
[7fffea581d40]odbcapi30.[SQLGetStmtAttr]411: Entering Handle=0x44a7730 10013
[7fffea581d40] pgapi30.c[PGAPI_GetStmtAttr]1561: entering Handle=0x44a7730 10013
[7fffea581d40] odbcapi.c[SQLPrepare]586: Entering
[7fffea581d40] execute.c[PGAPI_Prepare]47: entering...
[7fffea581d40] execute.c[PGAPI_Prepare]75: **** STMT_ALLOCATED, copy
[7fffea581d40] odbcapi.c[SQLBindParameter]1526: Entering
[7fffea581d40]    bind.c[PGAPI_BindParameter]51: entering...
[7fffea581d40]    bind.c[extend_parameter_bindings]493: entering ... self=0x44a7910, parameters_allocated=0, num_params=1,(nil)
[7fffea581d40]    bind.c[extend_parameter_bindings]518: leaving 0x44ad8b0
[7fffea581d40]    bind.c[extend_iparameter_bindings]526: entering ... self=0x44a7980, parameters_allocated=0, num_params=1
[7fffea581d40]    bind.c[extend_iparameter_bindings]552: leaving 0x44a06c0
[7fffea581d40]    bind.c[extend_putdata_info]947: entering ... self=0x44a7a78, parameters_allocated=0, num_params=1
[7fffea581d40]    bind.c[extend_putdata_info]989: leaving 0x44ad860
[7fffea581d40]    bind.c[PGAPI_BindParameter]138: ipar=0, paramType=1, fCType=-25, fSqlType=-5, cbColDef=0, ibScale=6,rgbValue=0x19457b0(8), pcbValue=0x197d170
[7fffea581d40] odbcapi.c[SQLGetInfo]502: Entering
[7fffea581d40]    info.c[PGAPI_GetInfo]88: entering...fInfoType=81
[7fffea581d40]    info.c[PGAPI_GetInfo]1076: p='<NULL>', len=4, value=15, cbMax=4
[7fffea581d40]odbcapi30.[SQLSetStmtAttr]540: Entering Handle=0x44a7730 27,1
[7fffea581d40] pgapi30.c[PGAPI_SetStmtAttr]1930: entering Handle=0x44a7730 27,1(0x1)
[7fffea581d40]odbcapi30.[SQLSetStmtAttr]540: Entering Handle=0x44a7730 26,71963824
[7fffea581d40] pgapi30.c[PGAPI_SetStmtAttr]1930: entering Handle=0x44a7730 26,71963824(0x44a14b0)
[7fffea581d40]odbcapi30.[SQLSetStmtAttr]540: Entering Handle=0x44a7730 0,60
[7fffea581d40] pgapi30.c[PGAPI_SetStmtAttr]1930: entering Handle=0x44a7730 0,60(0x3c)
[7fffea581d40] options.c[PGAPI_SetStmtOption]618:  entering...
[7fffea581d40] options.c[set_statement_option]178: SQL_QUERY_TIMEOUT, vParam = 60
[7fffea581d40] odbcapi.c[SQLFreeStmt]395: Entering
[7fffea581d40]statement.[PGAPI_FreeStmt]248: entering...hstmt=0x44a7730, fOption=0
[7fffea581d40]statement.[SC_recycle_statement]856: entering self=0x44a7730
[7fffea581d40] qresult.c[QR_Destructor]344: entering
[7fffea581d40]    bind.c[PDATA_free_params]663: entering self=0x44a7a78
[7fffea581d40]    bind.c[PDATA_free_params]689: leaving
[7fffea581d40] odbcapi.c[SQLFreeStmt]395: Entering
[7fffea581d40]statement.[PGAPI_FreeStmt]248: entering...hstmt=0x44a7730, fOption=2
[7fffea581d40] odbcapi.c[SQLExecute]338: Entering
[7fffea581d40] execute.c[PGAPI_Execute]937: entering...5 0x44a7730 status=1
[7fffea581d40] execute.c[PGAPI_Execute]954: clear errors...
[7fffea581d40]    bind.c[PGAPI_NumParams]425: entering...
[7fffea581d40]statement.[SC_scanQueryAndCountParams]966: entering...
[7fffea581d40]statement.[SC_scanQueryAndCountParams]1124: leaving...num_p=1 multi=0
[7fffea581d40] execute.c[PGAPI_Execute]1035: prepare=1 prepared=0  batch_size=100 start_row=0end_row=0 => maybeBatch=0
[7fffea581d40] execute.c[PGAPI_Execute]1048: prepareParameters was not called, prepare state:7
[7fffea581d40] execute.c[PGAPI_Execute]1061: prepare=7 maybeBatch=0 exec_type=0
[7fffea581d40]statement.[SC_recycle_statement]856: entering self=0x44a7730
[7fffea581d40] qresult.c[QR_Destructor]344: entering
[7fffea581d40]    bind.c[PDATA_free_params]663: entering self=0x44a7a78
[7fffea581d40]    bind.c[PDATA_free_params]689: leaving
[7fffea581d40]    bind.c[PDATA_free_params]663: entering self=0x44a7a78
[7fffea581d40]    bind.c[PDATA_free_params]689: leaving
[7fffea581d40] execute.c[Exec_with_parameters_resolved]445: copying statement params: trans_status=1, len=198, stmt='WITH data_def AS (
        SELECT 1::bigint BIGINT_ID
), data_param AS (
        SELECT ? AS BIGINT_ID_EXPECTED)
SELECT *
FROM data_def
WHERE BIGINT_ID IN (SELECT BIGINT_ID_EXPECTED FROM data_param LIMIT 1)'
[7fffea581d40] execute.c[Exec_with_parameters_resolved]486:    stmt_with_params = '(null)'
[7fffea581d40] execute.c[Exec_with_parameters_resolved]491: about to begin SC_execute exec_type=0
[7fffea581d40]statement.[libpq_bind_and_exec]2618: bind stmt=0x44a7730
[7fffea581d40] convert.c[ResolveOneParam]4673: from(fcType)=-25, to(fSqlType)=-5(20), *pgType=0
[7fffea581d40] convert.c[ResolveOneParam]5010: cvt_null_date_string=0 pgtype=20 send_buf=(nil)
[7fffea581d40]statement.[libpq_bind_and_exec]2632: execute stmt=0x44a7730
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=4 status=1 token=WITH
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=8 status=1 token=data_def
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=2 status=1 token=AS
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=1 status=0 token=(
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=6 status=1 token=SELECT
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=1 status=1 token=1
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=1 status=0 token=:
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=1 status=0 token=:
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=6 status=1 token=bigint
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=9 status=1 token=BIGINT_ID
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=1 status=0 token=)
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=1 status=0 token=,
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=10 status=1 token=data_param
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=2 status=1 token=AS
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=1 status=0 token=(
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=6 status=1 token=SELECT
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=1 status=0 token=?
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=2 status=1 token=AS
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=18 status=1 token=BIGINT_ID_EXPECTED
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=1 status=0 token=)
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=6 status=1 token=SELECT
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=1 status=0 token=*
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=4 status=1 token=FROM
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=8 status=1 token=data_def
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=5 status=1 token=WHERE
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=9 status=1 token=BIGINT_ID
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=2 status=1 token=IN
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=1 status=0 token=(
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=6 status=1 token=SELECT
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=18 status=1 token=BIGINT_ID_EXPECTED
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=4 status=1 token=FROM
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=10 status=1 token=data_param
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=5 status=1 token=LIMIT
[7fffea581d40] convert.c[inner_process_tokens]3911: token_len=1 status=1 token=1
[7fffea581d40]statement.[SC_scanQueryAndCountParams]966: entering...
[7fffea581d40]statement.[SC_scanQueryAndCountParams]1124: leaving...num_p=1 multi=0
[7fffea581d40]statement.[SC_scanQueryAndCountParams]966: entering...
[7fffea581d40]statement.[SC_scanQueryAndCountParams]1124: leaving...num_p=0 multi=0
[7fffea581d40] convert.c[prepareParametersNoDesc]2772: parsed for the first command length=-1(-1) num_p=1
[7fffea581d40]statement.[ParseAndDescribeWithLibpq]2938: entering plan_name=_PLAN0x44a7730 query=WITH data_def AS (
        SELECT 1::bigint BIGINT_ID
), data_param AS (
        SELECT $1 AS BIGINT_ID_EXPECTED)
SELECT *
FROM data_def
WHERE BIGINT_ID IN (SELECT BIGINT_ID_EXPECTED FROM data_param LIMIT 1)
[7fffea581d40] qresult.c[QR_Constructor]173: entering
[7fffea581d40] qresult.c[QR_Constructor]242: leaving 0x1ae0bf0
[7fffea581d40]statement.[ParseWithLibpq]2799: entering plan_name=_PLAN0x44a7730 query=WITH data_def AS (
        SELECT 1::bigint BIGINT_ID
), data_param AS (
        SELECT $1 AS BIGINT_ID_EXPECTED)
SELECT *
FROM data_def
WHERE BIGINT_ID IN (SELECT BIGINT_ID_EXPECTED FROM data_param LIMIT 1)
[7fffea581d40]statement.[ParseWithLibpq]2837: sta_pidx=0 end_pidx=0 num_p=1
[7fffea581d40]statement.[ParseWithLibpq]2857: ipdopts->allocated: 1
[7fffea581d40]statement.[ParseWithLibpq]2881: [QLOG]PQprepare: 0x19bec10 'WITH data_def AS (
        SELECT 1::bigint BIGINT_ID
), data_param AS (
        SELECT $1 AS BIGINT_ID_EXPECTED)
SELECT *
FROM data_def
WHERE BIGINT_ID IN (SELECT BIGINT_ID_EXPECTED FROM data_param LIMIT 1)' plan=_PLAN0x44a7730 nParams=1
[7fffea581d40]connection[handle_pgres_error]864: PG_DIAG_SEVERITY_NONLOCALIZED=ERROR
[7fffea581d40]connection[handle_pgres_error]881: [QLOG] ERROR(ERROR) 42883 'operator does not exist: bigint = text'
[7fffea581d40]connection[handle_pgres_error]950: error message=ERROR: operator does not exist: bigint = text(45)
[7fffea581d40] qresult.c[QR_Destructor]344: entering
[7fffea581d40]statement.[SC_log_error]2467: STATEMENT ERROR: func=desc_params_and_sync, desc='', errnum=1, errmsg='Error while preparing parameters'
[7fffea581d40]connection[CC_log_error]2618: CONN ERROR: func=desc_params_and_sync, desc='', errnum=110, errmsg='(NULL)'
[7fffea581d40]statement.[SC_SetExecuting]3115: set 0x44a7730 STMT_FINISHED
[7fffea581d40] execute.c[Exec_with_parameters_resolved]564: count_of_deffered=0
[7fffea581d40] execute.c[PGAPI_Execute]1188: leaving 0x44a7730 retval=-1 status=3
[7fffea581d40] execute.c[DiscardStatementSvp]803: in_progress=0 previous=0
[7fffea581d40]odbcapi30.[SQLGetDiagRec]342: Entering
[7fffea581d40] pgapi30.c[PGAPI_GetDiagRec]43: entering type=3 rec=1
[7fffea581d40] environ.c[ER_ReturnError]202: entering status = 1, msg = #ERROR: operator does not exist: bigint = text;
Error while preparing parameters#
[7fffea581d40] environ.c[ER_ReturnError]259:         szSqlState = '42883',len=79, szError='ERROR: operator does not exist: bigint = text;
Error while preparing parameters'
[7fffea581d40] pgapi30.c[PGAPI_GetDiagRec]70: leaving 0
[7fffea581d40]odbcapi30.[SQLGetDiagRec]342: Entering
[7fffea581d40] pgapi30.c[PGAPI_GetDiagRec]43: entering type=3 rec=2
[7fffea581d40] environ.c[ER_ReturnError]202: entering status = 1, msg = #ERROR: operator does not exist: bigint = text;
Error while preparing parameters#
[7fffea581d40] pgapi30.c[PGAPI_GetDiagRec]70: leaving 100
[7fffea581d40]odbcapi30.[SQLGetDiagRec]342: Entering
[7fffea581d40] pgapi30.c[PGAPI_GetDiagRec]43: entering type=3 rec=1
[7fffea581d40] environ.c[ER_ReturnError]202: entering status = 1, msg = #ERROR: operator does not exist: bigint = text;
Error while preparing parameters#
[7fffea581d40] environ.c[ER_ReturnError]259:         szSqlState = '(null)',len=79, szError='(null)'
[7fffea581d40] pgapi30.c[PGAPI_GetDiagRec]70: leaving 1
[7fffea581d40]odbcapi30.[SQLGetDiagRec]342: Entering
[7fffea581d40] pgapi30.c[PGAPI_GetDiagRec]43: entering type=3 rec=1
[7fffea581d40] environ.c[ER_ReturnError]202: entering status = 1, msg = #ERROR: operator does not exist: bigint = text;
Error while preparing parameters#
[7fffea581d40] environ.c[ER_ReturnError]259:         szSqlState = '42883',len=79, szError='ERROR: operator does not exist: bigint = text;
Error while preparing parameters'
[7fffea581d40] pgapi30.c[PGAPI_GetDiagRec]70: leaving 0
[7fffea581d40]odbcapi30.[SQLGetDiagRec]342: Entering
[7fffea581d40] pgapi30.c[PGAPI_GetDiagRec]43: entering type=3 rec=2
[7fffea581d40] environ.c[ER_ReturnError]202: entering status = 1, msg = #ERROR: operator does not exist: bigint = text;
Error while preparing parameters#
[7fffea581d40] environ.c[ER_ReturnError]259:         szSqlState = '(null)',len=0, szError='(null)'
[7fffea581d40] pgapi30.c[PGAPI_GetDiagRec]70: leaving 0
[7fffea581d40] odbcapi.c[SQLCancel]77: Entering
[7fffea581d40] execute.c[PGAPI_Cancel]1270: entering...
[7fffea581d40] odbcapi.c[SQLFreeStmt]395: Entering
[7fffea581d40]statement.[PGAPI_FreeStmt]248: entering...hstmt=0x44a7730, fOption=0

Looking into the ODBC-code I noticed this:
- The ResolveOneParam uses sqltype_to_bind_pgtype
- sqltype_to_bind_pgtype always return 0 and states it is deprecated and sqltype_to_pgcast should be used.
- I guess this last call is missing somewhere, but
--> ResolveOneParam comments on top that server should determine in case pg_type = 0, but it seems to give an error when not casted
--> /* pgType is set to the PostgreSQL type OID that should be used when binding * (or 0, to let the server decide) */

Regards,
Kevin

В списке pgsql-odbc по дате отправления:

Предыдущее
От: Brad White
Дата:
Сообщение: Re: Quoting issue from ODBC
Следующее
От: Tarik EL KHOUDRI
Дата:
Сообщение: Postgresql-odbc for AIX