TSQLConnection.GetProcedureParams and public synonyms

TSQLConnection.GetProcedureParams and public synonyms

Postby MattiasNilssonAtNilex » Mon 18 Feb 2013 10:37

Hello,

We have had problems with TSQLConnection.GetProcedureParams after upgrading from 5.1.2.0 to version 6.* and it doesn't seem to have been solved in 6.1.2.0.

I have made an attempt to copy/paste relevant code into an example below.

Is there a problem with accessing the procedure via public synonyms in the newer drivers?
If you can confirm that there is a problem, when can we expect it to be fixed? ("next version" is a little too vague)

Do you have any other advice?
(Maybe finding out the schema owner name somehow - can't be hardcoded - and append it to the procedure name.)

Code: Select all
unit dbxtest1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, sqlexpr, db, dbxdevartoracle;

type
  TForm1 = class(TForm)
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation
var
  SQLConnection: TSQLConnection;
  OracleGetSerial: TSQLStoredProc;
  paramList: TParams;
  procParams: TList;

{$R *.dfm}
initialization

  SQLConnection := TSQLConnection.Create(nil);
  try
    SQLConnection.ConnectionName := 'Devart Oracle';
    SQLConnection.DriverName := 'DevartOracle';
    SQLConnection.LoginPrompt := False;
    SQLConnection.Params.Values['DataBase'] := 'hbgmedusaora';
    SQLConnection.Params.Values['User_Name'] := 'asu'; // not schema owner!
    SQLConnection.Params.Values['Password'] := 'asu';
    SQLConnection.Connected := True;

    paramList  := TParams.Create;
    procParams := TList.Create;
    try
      OracleGetSerial := TSQLStoredProc.Create(nil);
      OracleGetSerial.SQLConnection  := TSQLConnection(SQLConnection);
      OracleGetSerial.StoredProcName := 'GetSerial'; // Defined in, say schema NX.
      TSQLConnection(SQLConnection).GetProcedureParams('GetSerial', procParams);

      if procParams.count = 0 then // Returns 4 if logged in as schema owner, but erroneously 0 when logged in as another user,
                                   // despite PUBLIC SYNONYM pointing to procedure NX.GetSerial.
        raise Exception.Create('GetSerial PLSQL function not installed or user has no rights to execute.');

      LoadParamListItems(ParamList, ProcParams);
      OracleGetSerial.Params.Assign(ParamList);
    finally
      FreeProcParams(ProcParams);
      paramList.Free;
    end;

  finally
    SQLConnection.Free;
  end;
end.
MattiasNilssonAtNilex
 
Posts: 6
Joined: Wed 06 Aug 2008 10:18

Re: TSQLConnection.GetProcedureParams and public synonyms

Postby AlexP » Thu 21 Feb 2013 09:15

Hello,

In the latest dbExpress driver for Oracle version 6.1.2 the GetProcedureParams method returns stored procedure parameters correctly when using their synonyms, independently on user credentials you enter. Problems may be due to user rights.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: TSQLConnection.GetProcedureParams and public synonyms

Postby MattiasNilssonAtNilex » Fri 22 Feb 2013 16:24

Thanks for the response, but I don't see how it can be a user rights problem since it worked with the 5.* drivers, and it still works in sqlplus.
In sqlplus I can both execute the procedure via the public synonym, and find out information about the procedure via the ALL_* views, or use 'desc' to list the arguments.

We are by the way using Delphi XE (15), Windows 7 64-bit, Oracle 11 (one on 2003 server, and one on Win 7).
We have built dbexpoda40.dll ourselves from source code.

I'm thinking that there either is a problem in the dbexpress drivers, or possibly that our Delphi environments/projects are not properly set up, or possibly that we are doing something wrong in our code.
I was leaning towards the first option, but you seemed pretty sure that they should work correctly.

Can we provide some specific information to help getting to the root of this problem?
MattiasNilssonAtNilex
 
Posts: 6
Joined: Wed 06 Aug 2008 10:18

Re: TSQLConnection.GetProcedureParams and public synonyms

Postby AlexP » Tue 26 Feb 2013 15:18

Hello,

Please send the scripts for creating both Oracle schemes (the one using the procedure, and the other one with the alias), and the script for creating the procedure and alias, and we will try to reproduce the problem once more.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: TSQLConnection.GetProcedureParams and public synonyms

Postby MattiasNilssonAtNilex » Wed 27 Feb 2013 15:35

Code: Select all
-- Connect for example as 'sys as sysdba' and proper password

drop user ASU;
drop user NX cascade;
drop public synonym getserial;

CREATE USER ASU IDENTIFIED BY ASU ACCOUNT UNLOCK;
GRANT UNLIMITED TABLESPACE TO "ASU";
GRANT "CONNECT" TO "ASU";
GRANT "RESOURCE" TO "ASU";

create USER NX identified by NX account unlock;
grant unlimited tablespace to NX;

-- Changed last argument from OUT to IN, because it doesn't matter for the example.
-- The example can be made even easier:
-- CREATE PROCEDURE "NX"."getserial" (Result_W IN integer) IS

CREATE PROCEDURE "NX"."getserial" (tabellid IN varchar2, TabellNamn_I IN varchar2, antal IN integer, Result_W IN integer) IS
tmp_number integer;
BEGIN
tmp_number := 0;
-- Commented out code that is not relevant anyway
END getserial;
/

create public synonym getserial for NX.getserial;
grant execute on getserial to PUBLIC;

-- Uncomment the lines below to make sure it is possible to run the procedure via
-- public synonym in sqlplus, but not via the example program.
-- conn ASU/ASU;
-- exec getserial('1','2',3,4);
MattiasNilssonAtNilex
 
Posts: 6
Joined: Wed 06 Aug 2008 10:18

Re: TSQLConnection.GetProcedureParams and public synonyms

Postby AlexP » Thu 28 Feb 2013 12:30

Hello,

Thank you for the information, we have reproduced the problem and will try to fix it as soon as possible
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: TSQLConnection.GetProcedureParams and public synonyms

Postby Gilson Wingist » Wed 14 May 2014 12:18

Hello,

Recently we have upgrated the Delphi solutions of the company that I work for, using Delphi Xe4 and the Devart Oracle Driver, version 6.3.4.0, Oracle 11g.

When we try to acess a stored procedure in a package with public synonyms and all the correct rights, occurs an error in "TSQLConnection.GetProcedureParams".

In some cases, when I set the property "SchemaName" of the TSQLStoredProc it works, but its needed to configure this hardcoded so the use of public synonyms is meaningless.

In the previous version of the application, in Delphi 7 everything works fine, with the same database, code, rights, public synonyms, the only change its the version of the devart driver.

We need this problem fixed ( or some alternative way to acess our storeds procedures), as soon as possible.
Gilson Wingist
 
Posts: 1
Joined: Wed 14 May 2014 11:43

Re: TSQLConnection.GetProcedureParams and public synonyms

Postby AlexP » Fri 16 May 2014 08:39

Unfortunately, we can't fix this behavior, since on calling the GetProcedureParams method (if procedures with similar names are in different schemas), parameters of all these procedures will be retrieved. Therefore, when generating a parameter retrieving query, we range the selection by the current schema. If you set the TSQLStoredProc.SchemaName property to the schema name, then this specified schema will be add to the query instead of the working schema - and you will be able to correctly call procedures.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35


Return to dbExpress driver for Oracle