Delphi Berlin 10.1

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

Delphi Berlin 10.1

Post by mmalinow » Fri 10 Jun 2016 17:07

Hello,

Attempting to evaluate Embarcadero Berlin 10.1 32-bit with dbExpress for Oracle. Installed trial Berlin 10.1 and latest dbExpress for Oracle. Migrated a working Delphi 7 project in which we use Devart dbExpress for Oracle 11g access. I find that I am unable to execute stored procedures in the new environment. Receiving the following: "Could not parse the GetProcedureParameters metadata command...". Do you have any information I could use to determine where the problem may be?

Thanks for your help,
Michael Malinowski
Production Resource Group

mmalinow
Posts: 25
Joined: Sat 14 May 2011 00:56

Re: Delphi Berlin 10.1

Post by mmalinow » Mon 13 Jun 2016 19:27

AlexP,

I've corresponded in the past on a few issues and have received help. We have been using dbExpress driver for Oracle for the past few years with dependable results. Unfortunately in this evaluation for a possible IDE and driver upgrade we've been stumped. I've successfully connected and exercised SQLQuery. But in the case of SQLStoredProc we're stuck. I have even tried clearing the parameters and creating them. But on ExecProc we continue to receive the "could not parse the getprocedureparameters metadata command". Any information you could supply would be a great help. Please see my previous post for information. Additionally we are running on Windows 7.

Thank you much,
Mike

Michael Malinowski
Production Resource Group

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

Re: Delphi Berlin 10.1

Post by AlexP » Tue 14 Jun 2016 09:11

Hello,

In the new IDE versions, you should set the Hostname connection parameter instead of database to avoid this error.

mmalinow
Posts: 25
Joined: Sat 14 May 2011 00:56

Re: Delphi Berlin 10.1

Post by mmalinow » Tue 14 Jun 2016 12:37

Alex,

Thank you very much for the information. I see that the stored procedure is now responding differently and attempting to execute because I'm getting information regarding the number of parameters. In our application (upon your recommendation a long time back) we have been using a Refresh Parameters approach to reconcile the parameter list before execution. In our current program this is the method we have been using.

Lst:= TList.Create;
Sender.Params.Clear;
// Refresh Param List
try
Sender.SQLConnection.GetProcedureParams(Sender.StoredProcName, Lst);
LoadParamListItems(sender.Params, Lst);
FreeProcParams(Lst);
finally
FreeAndNil(Lst);
end;

I would want to replicate this in the new code. This is the attempt I've made but it doesn't seem to be working. Can you point out what I may be doing wrong?

List := TProcParamList.Create;
Sender.Params.Clear;
// Refresh Param List
try
Sender.SQLConnection.GetProcedureParams( Sender.StoredProcName, List );
LoadParamListItems(sender.Params, List);
FreeProcParams(List);
finally
FreeAndNil(Lst);
end;

Thanks again for the help so far,
Mike

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

Re: Delphi Berlin 10.1

Post by AlexP » Tue 14 Jun 2016 13:10

Please clarify, what errors occur when using TProcParamList?

mmalinow
Posts: 25
Joined: Sat 14 May 2011 00:56

Re: Delphi Berlin 10.1

Post by mmalinow » Tue 14 Jun 2016 14:15

Alex,

After I execute the revised RefreshParams I try to populate a parameter with a value and receive a parameter not found error. Not sure that I'm getting the parameter list successfully in the GetProcedureParams or not passing the list correctly in the LoadParamListItems. I don't receive any errors while executing the RefreshParams. It looks like the GetProcedureParams is not populating the TProcParamList. Does the method for getting the parameters look correct?

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

Re: Delphi Berlin 10.1

Post by AlexP » Wed 15 Jun 2016 06:47

We can't reproduce this behavior on the latest driver version. Please execute the following code and let us know the result.

Code: Select all

CREATE OR REPLACE PROCEDURE SP_TEST(A_IN IN NUMBER, A_OUT OUT NUMBER) AS 
BEGIN
  A_OUT := A_IN;
END;

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  SysUtils,
  SqlExpr,
  DB,
  DBXDevartOracle;

var
  SQLConnection: TSQLConnection;
  SQLStoredProc: TSQLStoredProc;

procedure RefreshParameters(Sender: TSQLStoredProc);
var
  List: TProcParamList;
begin
  List := TProcParamList.Create;
  Sender.Params.Clear;
  try
    Sender.SQLConnection.GetProcedureParams( Sender.StoredProcName, List );
    LoadParamListItems(sender.Params, List);
    FreeProcParams(List);
  finally
    FreeAndNil(List);
  end;
end;

begin
  SQLConnection := TSQLConnection.Create(nil);
  try
    SQLConnection.LibraryName := 'dbexpoda40.dll';
    SQLConnection.DriverName := 'DevartOracleDirect';

    SQLConnection.LoginPrompt := false;
    SQLConnection.Params.Clear;
    SQLConnection.Params.Add('hostname=localhost:1521:orcl');
    SQLConnection.Params.Add('User_Name=scott');
    SQLConnection.Params.Add('Password=tiger');
    SQLConnection.Connected := true;
    SQLStoredProc := TSQLStoredProc.Create(nil);
    try
      SQLStoredProc.SQLConnection := SQLConnection;
      SQLStoredProc.StoredProcName := 'sp_test';
      RefreshParameters(SQLStoredProc);
      SQLStoredProc.Params[0].AsInteger := 1;
      SQLStoredProc.ExecProc;
      Writeln(SQLStoredProc.Params[1].AsString);
    finally
      SQLStoredProc.Free;
    end;
  finally
    SQLConnection.Free;
    Readln;
  end;
end.

mmalinow
Posts: 25
Joined: Sat 14 May 2011 00:56

Re: Delphi Berlin 10.1

Post by mmalinow » Wed 15 Jun 2016 12:16

Alex,

I want to thank you for replying and for helping us to get going in the right direction. Your initial reply instructing us to use HostName instead of Database in the connection parameters was great. After getting the connection it was a matter of determining why we weren't loading parameters. I eventually determined that Delphi 2010 and dbExpress were more forgiving. Since our last upgrade from Delphi 3 to Delphi 2010 we had left many StoredProcs properties set incorrectly. I discovered that by specifying StoredProcName, PackageName and SchemaName explicitly in the Delphi objects and also when calling GetProcedureParams that we are now getting the parameters list and refreshing the procedures. It was a matter of just trying different things. In our current D2010 EXE we are able to pass packagename.storedprocname in the storedProcName property and it works fine. I'm sure that you would have assumed we had the properties set correctly and maybe never suggested that. Thanks again for your help.

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

Re: Delphi Berlin 10.1

Post by AlexP » Mon 20 Jun 2016 10:27

If you have any other questions, feel free to contact us

Post Reply