TSQLStoredProc Refreshing Parameter List

[mmalinow]

I am upgrading a project which was previously written to use BDE to access Oracle aliases. In upgrading this project to use dbExpress for Oracle I am having a problem with parameter lists on several TSQLStoredProcs. The lists are imcomplete when first accessed. I have resorted to changing the StoredProcName to another string, then back again to load the params list. But I feel that this is not good practice on an ongoing basis. Do you recommend a way to refresh the params list without negatively impacting running the stored procedures. There are numerous instances of this in the project, too many to locate manually in the IDE. Thank you for you input.

[AlexP]

hello,

Please specify what you mean by "problem with parameter lists on several TSQLStoredProcs" and provide a piece of code where the problem arises.
Also please try to set the TSQLStoredProc.ParamCheck property to true, maybe it'll help to resolve your problem.

[mmalinow]

This large project has 1000-2000 instances of stored procedures saved on forms. After the migration process to Delphi 2010 and dbDBExpress and Oracle 10g we are finding that in accessing these stored procedures that the parameter lists saved with forms are imcomplete and require refreshing. To manually open each form and refresh each parameter list is not practical at this time. Too many. Programatically, by changing the StoredProcName and then changing back to the original accomplishes the refreshing of the parameters. But the question is about the performance hit of using this approach. Is there another method to accomplish this? Or is it a moot point? In other words, refreshing the list still equates to refeshing the list regardless of the method. Thanks again.

[AlexP]

Hello,

Please describe how you migrated from BDE to dbExpress:
- did you change the corresponding components on the forms manually?
- did you change text in the *.dfm and *.pas modules?
- did you use third-party utilities?
- did you use any other way?

[mmalinow]

Hi,

We wrote an application to scan the several hundred .pas and .dfm files and make the appropriate changes to the component types, etc. For example, change all instances of TStoredProc to TSQLStoredProc etc. After several iterations we got a good compile. Then we cleaned up issues to allow us to start up our system and begin to access the DB. After getting deeper into specific functions on various forms we noticed the issue with the parameter lists, among some other issues, which we have been able to resolve. Thank you.

[AlexP]

Hello,

I could not reproduce the problem.
Please send any changed *.dfm and *.pas files and a script to create a stored procedure to alexp*devart*com.

[mmalinow]

Thank you for your help. We basically wanted to know if there is a method available to force a refresh of the parameter list of a TSQLStoredProc other than changing/assigning the StoredProcName.
We will pursue this problem.

[AlexP]

Hello,

There are no methods for refreshing parameters list in TSQLStoredProc. Parameters are defined in design-time or in run-time when defining procedure name (the ParamCheck property should be set to True).
But you can create your own procedure that will refresh parameters list, for example:

Code: Select all

procedure RefreshParams(Sender: TSQLStoredProc);
var
  lst: TList;
begin
  if not Sender.SQLConnection.Connected then
    exit;
  lst:= TList.Create;
  sender.Params.Clear;
  try
    sender.SQLConnection.GetProcedureParams(sender.StoredProcName,lst);
    LoadParamListItems(sender.Params,lst);
  finally
    lst.Free;
  end;
end;

You will be able to use this procedure to refresh parameters list instead of changing procedure name. Also you can create a component that is a successor of TSQLStoredProc and add this procedure to the public section.

[mmalinow]

Thank you for your help.
Thank you for the suggested procedure.

[AlexP]

Hello,

If any other questions come up, please contact us.