NULL Bigint function param problem with postgres

[eversun]

Just updated 7.3/8.0 from SVN, has problem with bigint parameters when it's NULL
Postgres reports error: invalid input syntax for type bigint ""

[marsupilami]

I created a ticket for this on our bug tracker: https://sourceforge.net/p/zeoslib/tickets/479/.

[EgonHugeist]

@eversun

please update again. According the report: My crystal ball doesn't tell me what your doing and what you are using for.
Thus please provide:

1: Compiler including version
2: Which postgres version?
3: The SQL or bette a little test-case.

Otherwise i'll close the ticket. With this informations i can't do anything, sorry.

[marsupilami]

Hello eversun,

I tried to cteate a simple test for your bugreport:

Code: Select all

procedure TZTestCompPostgreSQLBugReport.TestSF479;
var
  Query: TZQuery;
begin
  Query := CreateQuery;
  try
    Query.SQL.Text := 'select cast(:param as bigint) as colname';
    Query.ParamByName('param').Value := Null; // Query.ParamByName('param').Clear also doesn't raise an error
    Query.Open;
    Check(VarIsNull(Query.Fields[0].Value), 'The returned Colulmn is expected to contain a null value...');
  finally
    FreeAndNil(Query);
  end;
end;

This code doesn't raise the error on my test environment: PostgreSQL 9.5, Delphi 2010, latest Version of Zeos 8.0 (not trunk).

Could you please try to create a small example that raises the error?

Best regards,

Jan

[eversun]

My pardon, I just do not see email notification and thought this will be a long way.

1. Complier version is 10.1 (Berlin) update 2
2. Postgres version is 12.5, but got same problems with 11.1 too
3. Full code is a little bit complicated, as Zeos is deeply embedded with DWS (Delphi Web Script) components. So after I got this error and failed to find a fast solution, just returned to older 7.3 Zeos code I used before.

Now I had tried a "clean" test, like you offered, i.e. alone TZConnection + TZQuery: select * from XXX.YYYY(:id, null::uuid)
No error appeared !

Finally I had found the problem. I suppose it does exist, but is absolutely different type.
Actually I use the same TZQuery instance several times, i.e. set some SQL, open, close, set new SQL, etc.

First call is made with the SQL: select 1 from pg_catalog.pg_namespace where nspname = :schema
Is runs succesfully.
Then I change SQL to the one I reported, i.e. select * from XXX.YYYY(:id, null::uuid)
And I only do set id if one is needed, and do not apply id parameter value if no need for it. And the ParamByName('id') is kept WideString type and the value one set with the first SQL, while parameter name is changed

After I modified my code to select * from XXX.YYYY(:bigint, null::uuid) and make forced Param.Clear call, it started working, but this way is a bit non-standard.

[aehimself]

Heh.

See this topic.

[EgonHugeist]

That's definitelly related. So please update from SVN and report your findings, eversun.

[eversun]

Great, thanks, it's working !