Problem with 2 parameters in subselect

[IzzyWizzy]

Hi there,
following code gives me errorcode 80004005 from Delphi7
using SDAC 3.80.0.31

Code: Select all

SELECT TSG.NAME_NL AS SUBGROUP_NL, TSG.NAME_FR AS SUBGROUP_FR, TSG.NAME_EN AS SUBGROUP_EN, 
  TSG.TOOLSUBGROUPID, 
  D.NAME AS DEPARTMENT,                                                                                       
  (SELECT SDR.TARGET_FREE FROM SFC_TOOLSUBGRPDEPTRELS SDR WHERE SDR.DEPARTMENTID = D.DEPARTMENTID              
     AND SDR.TOOLSUBGROUPID = TSG.TOOLSUBGROUPID) AS TARGET_FREE,                                              
  (SELECT COUNT(*) FROM SFC_TOOLS A JOIN SFC_TOOLTYPES AA ON A.TOOLTYPEID = AA.TOOLTYPEID                      
   WHERE A.DEPARTMENTID_OWNER = D.DEPARTMENTID AND AA.TOOLSUBGROUPID = TSG.TOOLSUBGROUPID                      
     AND A.TOOLSTATEID IN (:P2)) AS AMOUNTFREE,                                                        
  (SELECT SDR.TARGET_NOT_WORNED FROM SFC_TOOLSUBGRPDEPTRELS SDR WHERE SDR.DEPARTMENTID = D.DEPARTMENTID        
     AND SDR.TOOLSUBGROUPID = TSG.TOOLSUBGROUPID) AS TARGET_NOT_WORNED,                                        
  (SELECT COUNT(*) FROM SFC_TOOLS A JOIN SFC_TOOLTYPES AA ON A.TOOLTYPEID = AA.TOOLTYPEID                      
   WHERE A.DEPARTMENTID_OWNER = D.DEPARTMENTID AND AA.TOOLSUBGROUPID = TSG.TOOLSUBGROUPID                      
     AND NOT A.TOOLSTATEID IN (:P1)) AS AMOUNT_NOT_WORNED                                              
FROM SFC_TOOLSUBGROUPS TSG JOIN SFC_TOOLSUBGRPDEPTRELS TSDR ON TSDR.TOOLSUBGROUPID = TSG.TOOLSUBGROUPID        
  JOIN SFC_DEPARTMENTS D ON TSDR.DEPARTMENTID = D.DEPARTMENTID   
WHERE D.DEPARTMENTID = :DEPARTMENTID

When I change params P1 and P2 into their values this works
The param :DEPARTMENTID always works.

Any ideas ? or is this a bug ?

[Jackson]

We couldn't reproduce the problem.
Please send us (evgeniym*crlab*com) a complete small test project to reproduce the problem; include definition of your database objects; don't use third party components.
Specify exact version of Microsoft SQL Server and OLE DB provider you use. You can see it the Info sheet of TMSConnection Editor.

[IzzyWizzy]

I hope you got the test project.
Included was my UntDacRoot unit.
It is my attempt at creating a unit which can access both MSSQL and Oracle. Only change needed would be the connectionstring.
I have written a few apps on Oracle.
I just started testing on the SQL Server, but i hit the wall with this OLE DB error 80004005 when preparing a query.

Any help would be appreciated.
Regards,
Geert

[Jackson]

We couldn't reproduce the problem using your example.
Try to specify parameters information before calling the TMemDataSet.Prepare method.
For example:

Code: Select all

  MSQuery1.SQL.Text := 'select * from TestTable where ID = :p1';
  MSQuery1.ParamByName('p1').ParamType := ptInput;
  MSQuery1.ParamByName('p1').DataType := ftInteger;
  MSQuery1.Prepare;

  MSQuery1.ParamByName('p1').Value := 1;
  MSQuery1.Execute;