Parameters Errors

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
jjmorlan
Posts: 11
Joined: Mon 31 Aug 2015 12:29

Parameters Errors

Post by jjmorlan » Sun 28 May 2017 11:37

I am so intent on migrating all our projects developed with ADO objects to their products SDAC 8.0.2 but we have a big problem, our projects are very large and we use query objects with parameters. In ADOQuery when we define parameters in the SQL statement within the query, the query extracts all configured parameters (data type, type of parameter, address, etc). We have noticed that this functionality does not exist or does not work in SDAC 8.0.2. We need to be able to get that functionality. Otherwise we would have to configure each parameter manually, and it would be a lot of work. We want to warn that we choose SDAC instead of other tools like FireDAC, both tools (ADO and FireDAC) have that functionality, which we understand is a basic and important functionality. The question is: is a product error (SDAC)? Or is that the product does not have this functionality?

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Parameters Errors

Post by azyk » Mon 29 May 2017 10:18

In order for SDAC to request the information about SQL query parameters from SQL Server and return it for dataset parameters, set TMSQuery.Options.DescribeParams to True. More details about DescribeParams: https://www.devart.com/sdac/docs/?devar ... params.htm .

jjmorlan
Posts: 11
Joined: Mon 31 Aug 2015 12:29

Re: Parameters Errors

Post by jjmorlan » Mon 29 May 2017 17:07

Hi, thanks for the support. We have reviewed the property you have indicated, but we do not see the effect we are looking for. When we add a SQL statement containing parameters even with that property enabled to true, we have to manually configure each parameter individually. What are we doing wrong? If at design time I add a SQL statement to the query object having previously configured the properties that are indicated to us, which is supposed to happen? I understand that when defining a parameter the object should extract all the information from the parameter, as we have tried and we notice that everything remains without any effect. This method is only a run time ?

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Parameters Errors

Post by azyk » Tue 30 May 2017 09:34

Please specify whether the mentioned problem is reproduced when using the following code:

Code: Select all

MSQuery.Options.DescribeParams := True;
MSQuery.SQL.Text := 'SELECT * FROM TableName WHERE ' +
' WHERE Field1 = :Field1 AND Field2 = :Field2)';
MSQuery.Prepare;

MSQuery.ParamByName('Field1').Value := Value1;
MSQuery.ParamByName('Field2').Value := Value2;
MSQuery.Open;
To get the information about SQL query parameters in Object Inspector expand Options and set AutoPrepare and DescribeParams to True. then open the TMSQuery editor, on the SQL tab insert SQL query and click the Execute button. Now on the Parameters tab there will be the information for each parameter taken from SQL Server.

More details about the Prepare method in our online documentation: https://www.devart.com/sdac/docs/?devar ... pare().htm и свойстве AutoPrepare: https://www.devart.com/sdac/docs/?devar ... repare.htm

jjmorlan
Posts: 11
Joined: Mon 31 Aug 2015 12:29

Re: Parameters Errors

Post by jjmorlan » Tue 30 May 2017 13:50

Hi, thanks for the support. We have done as you indicated and certainly works.
A suggestion, could this option (AutoPrepare and DescribeParams) be included in the connection object besides the query?
Because now we have to go through the whole project and go through all the units and object by Object
Manually set those properties. I think that putting that option (AutoPrepare and DescribeParams) on the connection object and that in turn is updated for all query objects that are linked to it.

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Parameters Errors

Post by azyk » Fri 02 Jun 2017 10:33

You can use the TCustomConnection.DataSets property to specify values for AutoPrepare and DescribeParams of all datasets, which use this connection instance.

jjmorlan
Posts: 11
Joined: Mon 31 Aug 2015 12:29

Re: Parameters Errors

Post by jjmorlan » Sat 03 Jun 2017 16:19

Hi, thanks for the support. Could you indicate by an example how this part could be implemented? Because we are not clear how to do it using TCustomConnection.DataSets.


Thanks

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Parameters Errors

Post by azyk » Tue 06 Jun 2017 08:41

To specify values for AutoPrepare and DescribeParams of all TMSQuery datasets, which use this connection instance, you can use the following code, for example, when creating a form:

Code: Select all

procedure TMainForm.FormCreate(Sender: TObject);
var
  i: integer;
  MSQuery: TMSQuery;
begin
  for i := 0 to MSConnection.DataSetCount - 1 do
  if (MSConnection.DataSets[i] is TMSQuery) then begin
    MSQuery := TMSQuery(MSConnection.DataSets[i]);

    MSQuery.Options.AutoPrepare := True;
    MSQuery.Options.DescribeParams := True;
  end;
end;

jjmorlan
Posts: 11
Joined: Mon 31 Aug 2015 12:29

Re: Parameters Errors

Post by jjmorlan » Tue 06 Jun 2017 19:58

Hi, thanks again. We've tried the part of the code that gives us an example. When we tried it, and at the time of executing the action post in the query we receive this error message "Syntax error, permission violation, or other nonspecific error". However, we tried a code like this:

Code: Select all

 for i := 0 to (Sender as TForm).ComponentCount -1 DO
  begin
      if Components[i].ClassType = TMSQuery then
      begin
         (Components[i] as TMSQuery).Options.AutoPrepare:=True;
         (Components[i] as TMSQuery).Options.DescribeParams:=True;
       end;
  end;
This code works and we get the effects that suggested us.
Both codes are similar, and we think the effect should be the same, but in the code proposed by you causes the error mentioned above. What's the cause?

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Parameters Errors

Post by azyk » Wed 07 Jun 2017 07:08

The provided code will work, but do not forget to execute it for all forms and data modules.

Post Reply