TSQLStoredProc Refreshing Parameter List

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
mmalinow
Posts: 24
Joined: Sat 14 May 2011 00:56

TSQLStoredProc Refreshing Parameter List

Post by mmalinow » Mon 16 May 2011 12:27

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
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Post by AlexP » Mon 16 May 2011 14:34

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
Posts: 24
Joined: Sat 14 May 2011 00:56

RE: TSQLStoredProc Refreshing Parameter List

Post by mmalinow » Mon 16 May 2011 16:53

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
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Post by AlexP » Tue 17 May 2011 07:27

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
Posts: 24
Joined: Sat 14 May 2011 00:56

Reply: TSQLStoredProc Refreshing Parameter List (forum="dbEx

Post by mmalinow » Tue 17 May 2011 12:22

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
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Post by AlexP » Tue 17 May 2011 14:06

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
Posts: 24
Joined: Sat 14 May 2011 00:56

TSQLStoredProc Refreshing Parameter List

Post by mmalinow » Tue 17 May 2011 14:12

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
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Post by AlexP » Wed 18 May 2011 12:52

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
Posts: 24
Joined: Sat 14 May 2011 00:56

TSQLStoredProc Refreshing Parameter List

Post by mmalinow » Wed 18 May 2011 12:57

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

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

Post by AlexP » Wed 18 May 2011 13:06

Hello,

If any other questions come up, please contact us.

Post Reply