Invalid parameters specified

Discussion of open issues, suggestions and bugs regarding PgDAC (PostgreSQL Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Invalid parameters specified

Post by radub » Tue 03 Dec 2013 23:28

Hello,

I have a small project with PgConnection, PgQuery, DataseProvider, ClientDataset components.
The ClientDataset's SQL text is the following:
  • 'select d.DOCID, d.DOCDATE, DOCAHD.DUEDATE, d.DOCNO, DOCAHD.DOCVALUE, FZ.FZNAME, FZ.FZCODE,'+
    'coalesce(sum(dlfin.DFLN1VAL),0) DOCPAID, DOCAHD.DOCVALUE-coalesce(sum(dlfin.DFLN1VAL),0) REST '+
    'from DOCHD d '+
    'join SOLDDOCAV s on d.DOCID=s.DOCID '+
    'join DOCAHD DOCAHD on s.DOCID=DOCAHD.DOCID '+
    'join FZ on DOCAHD.FZID=FZ.FZID '+
    'join DEV dev on dev.DEVID=d.DEVID '+
    'join TDOC t on t.TDOCID=d.TDOCID '+
    'left outer join DOCFLN1 dlfin on dlfin.DREFIDFZ=d.DOCID and dlfin.DOCID in (select d1.DOCID from DOCHD d1 Where d1.DOCDATE<= :datastop ) '+
    'left outer join DOCFHD dhfin on dhfin.DOCID=dlfin.DOCID '+
    'where dev.ISDEFAULT=1 and s.YM= :datastart '+
    'group by d.DOCID, d.DOCDATE, DOCAHD.DUEDATE, d.DOCNO, DOCAHD.DOCVALUE, FZ.FZNAME, FZ.FZCODE '+
    'having abs(DOCAHD.DOCVALUE-(select coalesce(sum(DFLN1VAL),0) from DOCFLN1 Where DREFIDFZ=d.DOCID and DOCID in (select DOCID from DOCHD Where DOCDATE< :datastop )))>0.009';
with 3 parameters.
Before opening the ClientDataset I assign values to the parameters:

Code: Select all

    for i:=0 to Params.Count-1 do begin
      Params[i].ParamType:=ptInput;
      Params[i].DataType:=ftInteger;
          if Params[i].Name='datastart' then Params[i].AsInteger:=20131201;
          if Params[i].Name='datastop' then Params[i].AsInteger:=20131231;
    end;
Trying to open the ClientDataset, I receive the above mentioned error.
Strange is, if I remove the last parameter (the one from the having clause), it opens without any error.

Any help would be appreciated.

Thank you,
Radu B.

radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Re: Invalid parameters specified

Post by radub » Wed 04 Dec 2013 06:50

In the meanwhile I've tested with a more simpler query.
Here's the code:

Code: Select all

  with ClientDataSet1 do begin
    if Active then Close;
    CommandText:=
      'select DOCID '+
      'from DOCHD '+
      'where DOCDATE > :datastart '+

      'union '+

      'select DOCID '+
      'from DOCHD '+
      'where DOCDATE > :datastart ';

    for i:=0 to Params.Count-1 do begin
      Params[i].ParamType:=ptInput;
      Params[i].DataType:=ftInteger;
      Params[i].AsInteger:=20131201;
    end;
    Open;
  end;
This time with only 2 parameters, same error.

This type of query works perfectly with ODAC/SDAC.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Invalid parameters specified

Post by AlexP » Wed 04 Dec 2013 08:43

Hello,

Thank you for the information, we have reproduced the problem and will try to fix it in the nearest future.

radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Re: Invalid parameters specified

Post by radub » Mon 09 Dec 2013 12:18

Please keep me informed when the new release with the fix will become available.

Thank you,
Radu B.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Invalid parameters specified

Post by AlexP » Tue 10 Dec 2013 09:04

Hello,

We plan to release a new version in the end of this - beginning of the next month.

radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Re: Invalid parameters specified

Post by radub » Mon 03 Feb 2014 17:42

Hello,

I've tested the example sent with the 4.2.5 version and raise other errors.

When will I have a stable version?
I am waiting for more then 2 months for a working PgDAC and in the meanwhile I have bought it and my stuff asking me when I'll be able to release a PostGres edition.

Right now I am in a very delicate position, so I need a quick solution.

Thank you,
Radu B.

radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Re: Invalid parameters specified

Post by radub » Tue 04 Feb 2014 14:40

Hello,

I've found a solution... In the previous version, I haven't to bother to set the data type of parameters, but in the current one failing to set this, it runs into an error.

So it is a must to set the parameters'data type.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Invalid parameters specified

Post by AlexP » Wed 05 Feb 2014 13:57

This fix wasn't included to the previous versionю It will be included to the next build.

andychez
Posts: 4
Joined: Thu 10 Mar 2011 15:31
Location: United Kingdom

Re: Invalid parameters specified

Post by andychez » Tue 11 Feb 2014 11:39

When will that be?
I have same problem. This is a real killer for me. :evil:
Do you have a beta build??

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Invalid parameters specified

Post by AlexP » Wed 12 Feb 2014 11:15

Yes, we can send you the night build including the fix. Please send your license number to alexp*devart*com and specify the e-mail where we can send the build.

P.S. We plan to release the build including this fix next week.

andychez
Posts: 4
Joined: Thu 10 Mar 2011 15:31
Location: United Kingdom

Re: Invalid parameters specified

Post by andychez » Thu 13 Feb 2014 16:34

Thanks Alex - Much appreciated.

Post Reply