Parameter cannot be derived from sql with subselect queries

[andsilva81]

hi there!

i use delphi xe2 with zeos 7.0.4 without problems.

Now i update to delphi 10.4, and download the 7.2.6.1 version to compile my program. But on run, zeos raise an exception that i can not understand: "Parameter Cannot Be Derived from SQL Statements With Sub-select Queries".

This is my query (SQLServer):

Code: Select all

select * from tbparametro  with (nolock)
where 
	(tbparametro.idempresa = :pidempresa and tbparametro.idunidade = :pidunidade) and 
	(idparametro = :pidparametro or idparametro in (
		select idparametro from tbhstparam with (nolock)
		  where idempresa = :pidempresa and 
				idunidade = :pidunidade and 
				idcolaborador = :pidcolaborador and
				((inicio < :pdini and fim is null) or 
				 (:pdini between inicio and fim) or
				 (:pdfim between inicio and fim) or
				 (inicio between :pdini and :pdfim and fim between :pdini and :pdfim) or
				 (:pdfim > inicio and fim is null) )
		union all 
		select idparametro from tbparamdia with (nolock) where
				idempresa = :pidempresa and
				idcolaborador = :pidcolaborador and
				dia between :pdini and :pdfim
		) 
	)

This error occurred at line 873 of ZDbcAdoUtils.pas, in RefreshFromAdo procedure:

Code: Select all

  procedure RefreshFromADO;
  var
    I: Integer;
    Parameter: _Parameter;
  begin
    with AdoCommand do
    try
      Parameters.Refresh;         <<<<<<<<<<<<<<<<<< THIS LINE
      for I := 0 to Parameters.Count - 1 do
        with Parameters[I] do
        begin
        {...}

Someone can help me!?

Thanks!

[marsupilami]

Hello,

a possible fix has been introduced into the current development version Zeos 7.3. I am sure it will make its way into the next stable Zeos 7.2 release. Is there a chance for you to test the current 7.3 version?

Best regards,

Jan

[marsupilami]

Hello,

my last post is wrong. The change was done in the Zeos 7.2 branch. Could you check there please?

Best regards,

Jan

[direstraits]

I had the same problem using ADO connection with MySql 8.0 x64 server.

I tried the fix explained here and it worked: https://stackoverflow.com/a/31141172

Replaced this line inside /src/dbc/ZDbcAdoUtils.pas

Code: Select all

Parameter.Attributes := dwFlags and $FFFFFFF0; { Mask out Input/Output flags }

With

Code: Select all

if dwFlags and $FFFFFFF0 <= adParamSigned + adParamNullable + adParamLong then
                    Parameter.Attributes := dwFlags and $FFFFFFF0; { Mask out Input/Output flags }

[marsupilami]

I applied your patch. Thanks for pointing this out