1

I got strange result with NpgsqlParameter in C++/CLI project.

NpgsqlParameter^ status = wCMD->Parameters->Add(gcnew NpgsqlParameter("param1", NpgsqlDbType::Array | NpgsqlDbType::Smallint));
status->Direction = System::Data::ParameterDirection::InputOutput;
status->Value = DBNull::Value;
status->Size = 20;

CALL MySchema.MyProcedure(:param1);

In MyProcedure, I assign a array of null and 0 to param1:

CREATE OR REPLACE PROCEDURE MySchema.MyProcedure(
    INOUT param1 smallint[])
LANGUAGE 'plpgsql'
AS $BODY$
        BEGIN
            param1 := array[null, 0];
            raise notice 'param1: %', param1;
$BODY$;

Then I got the following strange result in C++/CLI project: NpgsqlValue with arrays of shorts is [0, 0]

Could somebody help me to explain why NpgsqlParameter received arrays of zeros instead of null and 0?

Maybe NULL in C++ is 0? Or array cannot receive NULL?

How could I receive correct returned result from postgres? Example: NpgsqlValue with arrays of shorts is [NULL, 0]

I am using npgsql 4.1.12, postgresql 14.

Thanks a lot.

Thanh Dong
  • 9
  • 3

1 Answers1

-1

Roji who is Lead dev of Npgsql, the PostgreSQL provider, answered me like this:

Right, older versions of Npgsql indeed read null array elements as the default value for the CLR type; that meant that for int you'd get 0. Npgsql 6.0 changed this problematic behavior.

You're using ParameterDirection.InputOutput; this means that when reading the value, Npgsql doesn't know which CLR type you want (short?[] or short[]); it defaults to assuming short[], and then throws because it sees a null that cannot be represented in that.

My recommendation would be to not use ParameterDirection.InputOutput; you can simply call your stored function/procedure, and read the results via the regular NpgsqlDataReader; see these docs to understand that better. If you do this, then you can simply call NpgsqlDataReader.GetFieldValue<short?[]> to tell Npgsql exactly which type you're looking for, and you'll get the null as expected.

If you don't want to go down this route, then you can modify Npgsql's behavior when reading non-generically (e.g. with InputOutput); see the Array Nullability Mode connection string parameter. That's Never by default (the behavior described above, but you can set it to Always to make Npgsql always return a nullable array, or PerInstance for nullable/non-nullable arrays to be returned based on the actual array contents.

For detail, please refer here: Github-npgsql

Thanh Dong
  • 9
  • 3