Subject: Re: Error on query execution

From: Tom Lane <[email-masked]>
Date: Tue, 04 Mar 2025 21:37:00 -0500

Igor Korot <[email-masked]> writes:
>     binaryIntVal = htonl( (uint32_t) type );
>     paramValues[0] = (char *) &binaryIntVal;
>     paramLengths[0] = sizeof( binaryIntVal );
>     paramFormats[0] = 1;

You're apparently trying to pass this parameter as an int4 ...

>     res = PQexecParams( m_db, "SELECT * FROM abcatfmt WHERE abf_type =
> $1", 1, nullptr, paramValues, paramLengths, paramFormats, 1 );

... but given that you didn't specify any data type, I think the
parser will fall back to assuming that $1 is the same type as
"abf_type", whatever that is.  Passing data in binary is not at all
forgiving about getting the data type right.

			regards, tom lane

---

From: Igor Korot <[email-masked]>
Date: Wed, 5 Mar 2025 01:42:13 -0600

Hi, ALL,
[code]
    type = 80;
    uint32_t binaryIntVal;
    const char *paramValues[1];
    int paramLengths[1];
    int paramFormats[1];
    binaryIntVal = htonl( (uint32_t) type );
    paramValues[0] = (char *) &binaryIntVal;
    paramLengths[0] = sizeof( binaryIntVal );
    paramFormats[0] = 1;
    res = PQexecParams( m_db, "SELECT * FROM abcatfmt WHERE abf_type =
$1", 1, nullptr, paramValues, paramLengths, paramFormats, 1 );
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        std::wstring err = m_pimpl->m_myconv.from_bytes(
PQerrorMessage( m_db ) );
        errorMsg.push_back( L"Error executing query: " + err );
        result = 1;
    }
[/code]

Running the above I'm getting:

[quote]
(gdb) p err
$1 = L"ERROR:  incorrect binary data format in bind parameter 1\n"
[/quote]

What is wrong and how do I fix it?

Thank you.

---

From: Igor Korot <[email-masked]>
Date: Wed, 5 Mar 2025 18:55:05 -0600

Tom,

> You're apparently trying to pass this parameter as an int4 ...

I thought only binary or text is allowed as parameters.

Guess I was wrong...

Thank you.

---

From: Tom Lane <[email-masked]>
Date: Thu, 06 Mar 2025 12:00:16 -0500

Igor Korot <[email-masked]> writes:
> On Tue, Mar 4, 2025 at 8:37 PM Tom Lane <[email-masked]> wrote:

> Changing the line above to read:

> $1::smallint", 1, nullptr, paramValues, paramLengths, paramFormats, 1
> );

That just makes it explicit that the query is expecting an int16.
You're still passing an int32.  You need to either change the
parameter setup code to pass an int16, or make the query look
more like

    res = PQexecParams( m_db, "SELECT * FROM abcatfmt WHERE abf_type =
    $1::integer", 1, nullptr, paramValues, paramLengths, paramFormats, 1);

			regards, tom lane

---

From: Igor Korot <[email-masked]>
Date: Thu, 6 Mar 2025 15:54:41 -0600

H,

results in:

I am now at a complete loss.

How do I fix the code so it will run?

Thank you.