Could not parse the GetProcedureParameters metadata command on TSQLStoredProc

Could not parse the GetProcedureParameters metadata command on TSQLStoredProc

Postby luboice » Wed 19 Sep 2012 12:09

Hi DevArt,

Using the new 6.0.1 version of the driver on Delphi XE2 Enterprise and Oracle Database Standard Edition One 11G Release 2 (11.2.0.2) we encountered a problem with TSQLStoredProc trying to execute a stored procedure with VarChar2 out parameter. The following exception is thrown on ExecProc:

Could not parse the GetProcedureParameters metadata command. Problem found near: :. Original query: GetProcedureParameters host:1521:service . user . GetTestValue .


With version 5.1.2 of the driver on the same Delphi XE2 Enterprise and Oracle Database Standard Edition One 11G Release 2 (11.2.0.2) we receive the correct 'Some test value' message.


The following sample code demonstrates the problem (paste it in button click event handler on a new form of new vcl forms application and fill the proper oracle login info):

Code: Select all
uses
  DbxOdaDriverLoader, SqlExpr, DBXCommon, DriverOptions, DB;


Code: Select all
const
  HostName = 'host';
  Port = '1521';
  ServiceName = 'service';
  UserName = 'user';
  Password = 'pass';
  SFalse = 'False';
  STrue = 'True';
var
  con: TSQLConnection;
  sp: TSQLStoredProc;
begin
  con:= TSQLConnection.Create(nil);
  try
    con.DriverName:= DbxOdaDriverLoader.sDirectBuiltinDriverName;
    con.VendorLib:= 'dbexpoda40.dll';
    con.GetDriverFunc:= 'getSQLDriverORADirect';
    con.LibraryName:= 'dbexpoda40.dll';

    con.Params.Clear;
    con.Params.Values[TDBXPropertyNames.DriverPackageLoader]:= TDbxOdaDriverLoader.ClassName;
    con.Params.Values[TDBXPropertyNames.Database]:= Format('%s:%s:%s', [HostName, Port, ServiceName]);
    con.Params.Values[TDBXPropertyNames.UserName]:= UserName;
    con.Params.Values[TDBXPropertyNames.Password]:= Password;
    con.Params.Values[SReconnect]:= SFalse;
    con.Params.Values[SEnableBCD]:= SFalse;
    con.Params.Values[SLongStrings]:= STrue;
    con.Params.Values[SUseUnicode]:= STrue;
    con.Params.Values[SUseUnicodeMemo]:= STrue;

    con.Connected:= True;
    try

      sp:= TSQLStoredProc.Create(nil);
      try

        sp.SQLConnection:= con;
        sp.StoredProcName:= 'GetTestValue';
        sp.ExecProc;

        ShowMessage(sp.Params[0].AsString);

      finally
        FreeAndNil(sp);
      end;

    finally
      con.Connected:= False;
    end;
  finally
    FreeAndNil(con);
  end;
end;



To be able to run the example please create the following simple stored procedure:

Code: Select all
create procedure GetTestValue(AValue out VarChar2) is
begin
  AValue:= 'Some test value';
end;
luboice
 
Posts: 13
Joined: Thu 06 Dec 2007 12:55

Re: Could not parse the GetProcedureParameters metadata command on TSQLStoredProc

Postby AlexP » Thu 20 Sep 2012 14:12

Hello,

Thank you for the information.
We've reproduced the problem.
We will notify you as soon as we have any results.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Could not parse the GetProcedureParameters metadata command on TSQLStoredProc

Postby MattiasNilssonAtNilex » Tue 09 Oct 2012 08:02

We're having an issue with stored procedure parameters too.
Maybe it should be another thread, but I'm wondering if there is an update to the problem of the original poster, or a hint of a release date.

OracleGetSerial := TSQLStoredProc.Create(nil);
OracleGetSerial.SQLConnection := TSQLConnection(FConn.DataInterFace);
OracleGetSerial.StoredProcName := 'GetSerial';
TSQLConnection(FConn.Datainterface).GetProcedureParams('GetSerial', procParams);
if procParams.count = 0 then
raise Exception.Create('count is erroneously 0 after upgrading from v5 to v6');
MattiasNilssonAtNilex
 
Posts: 6
Joined: Wed 06 Aug 2008 10:18

Re: Could not parse the GetProcedureParameters metadata command on TSQLStoredProc

Postby AlexP » Tue 09 Oct 2012 09:11

Hello,

We are still working on this problem.
We will try to fix it in the next product version.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Could not parse the GetProcedureParameters metadata command on TSQLStoredProc

Postby PeterWK » Wed 09 Jan 2013 10:34

Hi DevArt,

Using the new 6.1.2 version of the driver on Delphi XE3 Enterprise and Oracle Database we encountered a problem with TSQLStoredProc trying to execute a stored procedure with integer input parameter. The following exception is thrown on ExecProc:

Could not parse the GetProcedureParameters metadata command. Problem found near: /. Original query: GetProcedureParameters

Do you have any update for this problem?
PeterWK
 
Posts: 15
Joined: Tue 08 Jan 2013 19:21

Re: Could not parse the GetProcedureParameters metadata command on TSQLStoredProc

Postby AlexP » Wed 09 Jan 2013 14:58

Hello,

We cannot reproduce the problem on the latest version of dbExpress driver for Oracle 6.1.2 and Delphi XE3: the following procedure is executed with no errors and returns the correct result:

Code: Select all
CREATE OR REPLACE PROCEDURE sp_test (a_in_val IN NUMBER, a_out_val out NUMBER)
AS
BEGIN
  a_out_val := a_in_val;
END;
/


Code: Select all
  SQLStoredProc1.StoredProcName := 'sp_test';
  SQLStoredProc1.ParamByName('a_in_val').AsInteger := 1;
  SQLStoredProc1.ExecProc;
  ShowMessage(SQLStoredProc1.ParamByName('a_out_val').AsString);



Please check this procedure execution, and, if there are no errors, send your script for creating procedure and the code for calling it from Delphi.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Could not parse the GetProcedureParameters metadata command on TSQLStoredProc

Postby PeterWK » Wed 09 Jan 2013 17:14

Hi Devart,

Thanks for your atention.

I've tested the procedure and I'm still getting the same error

'Could not parse the GetProcedureParameters metadata command. Problem found near: /. Original query: GetProcedureParameters ServerTest/XE . BaseTest . sp_test .'

Code: Select all
 
  SQLStoredProc1.StoredProcName := 'sp_test';
  SQLStoredProc1.Params.CreateParam(ftInteger,'a_in_val',ptInput);
  SQLStoredProc1.ParamByName('a_in_val').AsInteger := 1;
  SQLStoredProc1.ExecProc;
  ShowMessage(SQLStoredProc1.ParamByName('a_out_val').AsString);


If i don't create the param at runtime it will not be found.
PeterWK
 
Posts: 15
Joined: Tue 08 Jan 2013 19:21

Re: Could not parse the GetProcedureParameters metadata command on TSQLStoredProc

Postby PeterWK » Wed 09 Jan 2013 17:19

Just for the records,

I'm having the same problem with SQLServer:

The error is:

Subtype fldstFIXED used to detect GUID NULL Value in SetParameter method (D:\Projects\Delphi\Dbx\SqlServer\Source\dbxsda.pas, line 1183)

I'm not getting run any StoredProc...
PeterWK
 
Posts: 15
Joined: Tue 08 Jan 2013 19:21

Re: Could not parse the GetProcedureParameters metadata command on TSQLStoredProc

Postby AlexP » Thu 10 Jan 2013 09:04

Hello,

In order not to create parameters manually, you can set the property in ParamCheck True.

Code: Select all
SQLStoredProc1.ParamCheck := True;
SQLStoredProc1.StoredProcName := 'sp_test';
...


However, even when creating parameters manually, no error repeats. Please specify your exact versions of Oracle client and server, OS, IDE, the type of the project (x32 or x64), and the version of our driver you are using (Standard or Src).

We have already fixed this problem in the driver for MS SQL Server and will reload the installation in the coming days.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Could not parse the GetProcedureParameters metadata command on TSQLStoredProc

Postby PeterWK » Thu 10 Jan 2013 10:50

Hi Devart,

The property ParamCheck, was true all along.

I'm using dbExpress driver for Oracle Standard 6.1.2.

Windows 8 x64, the project is x32.

Oracle SQL Developer (IDE - 3.1.07.42).
Oracle Database 10g Exress Edition Release 10.2.0.1.0 - Production.
PeterWK
 
Posts: 15
Joined: Tue 08 Jan 2013 19:21

Re: Could not parse the GetProcedureParameters metadata command on TSQLStoredProc

Postby PeterWK » Fri 11 Jan 2013 18:13

Hi Devart,

Are you working on this problem?

About the driver for MS SQL Server... how can I get it after you reload the installation?

Thanks for your atetion...
PeterWK
 
Posts: 15
Joined: Tue 08 Jan 2013 19:21

Re: Could not parse the GetProcedureParameters metadata command on TSQLStoredProc

Postby PeterWK » Mon 14 Jan 2013 11:45

Good Morning Devart,

Any News about the problem?

I realy need to use the SQLStoreProc.

Thanks for your atention.
PeterWK
 
Posts: 15
Joined: Tue 08 Jan 2013 19:21

Re: Could not parse the GetProcedureParameters metadata command on TSQLStoredProc

Postby AlexP » Tue 15 Jan 2013 11:28

Hello,

We still cannot reproduce the problem with stored procedures. As soon as we reproduce it, we will inform you.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Could not parse the GetProcedureParameters metadata command on TSQLStoredProc

Postby AlexP » Wed 16 Jan 2013 13:11

Hello,

We tried to reproduce the problem with stored procedures in your dbExpress Driver For Oracle on all OSs: XP, Win7(x32/x64), Win8(x32/x64), and the problem didn't reproduce when executing the below code in both OCI and Direct modes. Please modify this code so that the problem reproduces.

Code: Select all
  SQLConnection := TSQLConnection.Create(nil);
  try
    SQLConnection.ConnectionName := 'Devart Oracle Direct';
    SQLConnection.DriverName := 'DevartOracleDirect';
    SQLConnection.LoginPrompt := False;
    SQLConnection.Params.Values['HostName'] := 'host:port:sid';
    SQLConnection.Params.Values['User_Name'] := 'login';
    SQLConnection.Params.Values['Password'] := 'passwd';
    SQLConnection.Connected := True;
    SQLStoredProc := TSQLStoredProc.Create(nil);
    try
      SQLStoredProc.SQLConnection := SQLConnection;
      SQLStoredProc.StoredProcName := 'sp_test';
      SQLStoredProc.Prepared := True;
      SQLStoredProc.ParamByName('a_in_val').AsInteger := 1;
      SQLStoredProc.ExecProc;
      Writeln('Direct: ' + SQLStoredProc.ParamByName('a_out_val').AsString);
    finally
      SQLStoredProc.Free;
    end;
  finally
    SQLConnection.Free;
  end;

Code: Select all
  SQLConnection := TSQLConnection.Create(nil);
  try
    SQLConnection.ConnectionName := 'Devart Oracle';
    SQLConnection.DriverName := 'DevartOracle';
    SQLConnection.LoginPrompt := False;
    SQLConnection.Params.Values['DataBase'] := 'sid';
    SQLConnection.Params.Values['User_Name'] := 'login';
    SQLConnection.Params.Values['Password'] := 'passwd';
    SQLConnection.Connected := True;
    SQLStoredProc := TSQLStoredProc.Create(nil);
    try
      SQLStoredProc.SQLConnection := SQLConnection;
      SQLStoredProc.StoredProcName := 'sp_test';
      SQLStoredProc.Prepared := True;
      SQLStoredProc.ParamByName('a_in_val').AsInteger := 1;
      SQLStoredProc.ExecProc;
      Writeln('OCI: ' + SQLStoredProc.ParamByName('a_out_val').AsString);
    finally
      SQLStoredProc.Free;
    end;
  finally
    SQLConnection.Free;
  end;
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Could not parse the GetProcedureParameters metadata command on TSQLStoredProc

Postby PeterWK » Thu 17 Jan 2013 16:52

Hi Devart,

I'm Connecting with OCI, and I can't Direct connect.

This code didn't work:

Code: Select all
  SQLConnection := TSQLConnection.Create(nil);
  try
    SQLConnection.ConnectionName := 'Devart Oracle';
    SQLConnection.DriverName := 'DevartOracle';
    SQLConnection.LoginPrompt := False;
    SQLConnection.Params.Values['DataBase'] := 'sid';
    SQLConnection.Params.Values['User_Name'] := 'login';
    SQLConnection.Params.Values['Password'] := 'passwd';
    SQLConnection.Connected := True;
   
    //so far it worked and connected...

    SQLStoredProc := TSQLStoredProc.Create(nil);
    try
      SQLStoredProc.SQLConnection := SQLConnection;

      // here the param 'a_in_val' is not created
      SQLStoredProc.StoredProcName := 'sp_test';
     
      SQLStoredProc.Prepared := True;

      // if 'a_in_val' wasn't created an exception is raised
      //SQLStoredProc.ParamByName('a_in_val').AsInteger := 1;

      // if I create the param before
      SQLStoredProc.Params.CreateParam(ftInteger,'a_in_val',ptInput);
      // then OK
      SQLStoredProc.ParamByName('a_in_val').AsInteger := 1;

      // but when I try to ExecProc
      SQLStoredProc.ExecProc;
      //raise this exection:
      //'Could not parse the GetProcedureParameters metadata command. Problem found near: /. Original query: GetProcedureParameters 'DataBase'. 'User_Name' . sp_test .'

      Writeln('OCI: ' + SQLStoredProc.ParamByName('a_out_val').AsString);
    finally
      SQLStoredProc.Free;
    end;
  finally
    SQLConnection.Free;
  end;


Thanks,
Peter
PeterWK
 
Posts: 15
Joined: Tue 08 Jan 2013 19:21

Next

Return to dbExpress driver for Oracle