Devart Forums

wnielsenbb

I have a new bug related to above. I can make a separate entry if you like.
We turned Paramcheck off to resolve the above error. I then found TSQLStoredProc wasn't returning string ptOutput parameters. Number parameters were fine.
ptInputOutput had the same issue, Further research found assigning a string value to the parameter, even the ptOutput one, before executing the proc, caused it to return twice as many characters as supplied. If the value property on the parameter is set at design time I get that many +1 characters back.
Our workaround was to just supply as many characters as we expected to get back at most. Not really handy.
On this demo I use a tnsname to test. The button click creates a dynamic stored proc, which works as expected. Then it sets the param values on the fixed proc with paramcheck turned off.

The output is

Open Connection
Create Dynamic StoredProc
Open dynamic StoredProc
Dynamic Proc pAge = 23
Dynamic Proc pOutName = MyNameIsBob
Dynamic Proc pInOutName = MyNameIsBob
Open fixed StoredProc
fixed Proc pAge = 23
fixed Proc pOutName = MyN
fixed Proc pInOutName = MyName

dbcode

Code: Select all

CREATE TABLE BOB( 
  Age   NUMBER(4),
  Name  VARCHAR2(100)
);
Insert Into Bob Values (23,'MyNameIsBob');
Commit;
CREATE OR REPLACE PROCEDURE BobDetailGet
(
    pName           IN        Bob.Name%TYPE,
    pAge                OUT   Bob.Age%TYPE,
    pOutName            OUT   Bob.Name%TYPE,
    pInOutName      IN  OUT   Bob.Name%TYPE
)
AS
BEGIN
  SELECT  Age, Name, Name
  INTO    pAge,pOutName,pInOutName
  FROM Bob
  WHERE Name = pName;
END;
/

form:

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 299
  ClientWidth = 635
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Memo1: TMemo
    Left = 280
    Top = 40
    Width = 313
    Height = 225
    Lines.Strings = (
      'Memo1')
    TabOrder = 0
  end
  object btnGetUserDetail: TButton
    Left = 64
    Top = 79
    Width = 89
    Height = 25
    Caption = 'btnGetUserDetail'
    TabOrder = 1
    OnClick = btnGetUserDetailClick
  end
  object sqlConn: TSQLConnection
    ConnectionName = 'Devart Oracle'
    DriverName = 'DevartOracle'
    LoginPrompt = False
    Params.Strings = (
      'DataBase=wwn3136'
      'UseQuoteChar=False'
      'DriverName=DevartOracle'
      'Password=password1'
      'Oracle TransIsolation=ReadCommitted'
      'User_Name=envision'
      'LongStrings=True'
      'EnableBCD=False'
      'FetchAll=False'
      'UseUnicode=False'
      'UnicodeEnvironment=False')
    Connected = True
    Left = 40
    Top = 16
  end
  object sprBobDetailGet: TSQLStoredProc
    MaxBlobSize = -1
    ParamCheck = False
    Params = <
      item
        DataType = ftWideString
        Precision = 2000
        Name = 'PNAME'
        ParamType = ptInput
      end
      item
        DataType = ftFMTBcd
        Precision = 34
        Name = 'PAGE'
        ParamType = ptOutput
        Size = 34
      end
      item
        DataType = ftString
        Precision = 2000
        Name = 'POUTNAME'
        ParamType = ptOutput
        Size = 2000
        Value = 'aw'
      end
      item
        DataType = ftWideString
        Precision = 2000
        Name = 'PINOUTNAME'
        ParamType = ptInputOutput
        Size = 2000
      end>
    SQLConnection = sqlConn
    StoredProcName = 'BobDetailGet'
    Left = 144
    Top = 16
  end
end

pas

Code: Select all

unit testproc;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, DBXDevartOracle, Data.FMTBcd,
  Vcl.StdCtrls, Data.SqlExpr, Data.DB;

type
  TForm1 = class(TForm)
    sqlConn: TSQLConnection;
    Memo1: TMemo;
    btnGetUserDetail: TButton;
    sprBobDetailGet: TSQLStoredProc;
    procedure btnGetUserDetailClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.btnGetUserDetailClick(Sender: TObject);
var
  proc: TSQLStoredProc;
  param: TParam;
begin
  memo1.Lines.Clear;
  memo1.Lines.Add('Open Connection');
  SQLConn.Open;
  memo1.Lines.Add('Create Dynamic StoredProc');
  proc := TSQLStoredProc.Create(nil);
  try
    proc.SQLConnection := SQLConn;
    proc.StoredProcName := 'BobDetailGet';
    memo1.Lines.Add('Open dynamic StoredProc');
    try
      proc.ParamByName('pName').AsString := 'MyNameIsBob';
      proc.ExecProc;
      param := proc.ParamByName('pAge');
      memo1.Lines.Add('Dynamic Proc pAge = '+param.AsString);
      param := proc.ParamByName('pOutName');
      memo1.Lines.Add('Dynamic Proc pOutName = '+param.AsString);
      param := proc.ParamByName('pInOutName');
      memo1.Lines.Add('Dynamic Proc pInOutName = '+param.AsString);
    except
      on e:exception do begin
        Memo1.Lines.Add('Exception: '+e.Message);
      end;
    end;
  finally
    FreeAndNil(Proc);
  end;
  memo1.Lines.Add('Open fixed StoredProc');
  try
    sprBobDetailGet.ParamByName('pName').AsString := 'MyNameIsBob';
//    sprBobDetailGet.ParamByName('pOutName').AsString := 'ab';
    sprBobDetailGet.ParamByName('pInOutName').AsString := 'aw';
    sprBobDetailGet.ExecProc;
    param := sprBobDetailGet.ParamByName('pAge');
    memo1.Lines.Add('fixed Proc pAge = '+param.AsString);
    param := sprBobDetailGet.ParamByName('pOutName');
    memo1.Lines.Add('fixed Proc pOutName = '+param.AsString);
    param := sprBobDetailGet.ParamByName('pInOutName');
    memo1.Lines.Add('fixed Proc pInOutName = '+param.AsString);
  except
    on e:exception do begin
      Memo1.Lines.Add('Exception: '+e.Message);
    end;
  end;
end;

end.

wnielsenbb

So we had switched to just using a tnsname to work around this issue, but we have had customers with issues with that.
I have dug into it some more. The issue is more clear using a dynamically generated TSQLStoredProc. I added a second button to the form in my earlier post and a new proc with parameters

Code: Select all

CREATE OR REPLACE PROCEDURE CustomerGetParam(pFirst VARCHAR2, pMid VARCHAR2, pLast VARCHAR2, pList OUT NOCOPY SYS_REFCURSOR)
AS
BEGIN
  OPEN    pList FOR
  SELECT  firstname
  FROM CUSTOMER
  WHERE ROWNUM = 1;
END;
/

Code: Select all

procedure TForm1.Button2Click(Sender: TObject);
var
  i: integer;
  proc: TSqlStoredProc;
begin
  memo1.Lines.Clear;
  memo1.Lines.Add('Open Connection');
  SQLConnection1.Open;
  memo1.Lines.Add('Open Query');
  sqlQuery1.Open;
  memo1.Lines.Add('query firstname = '+SQLQuery1FIRSTNAME.AsString);

  memo1.Lines.Add('Open StoredProc');
  proc := TSqlStoredProc.Create(nil);
  try
    proc.SQLConnection := SQLConnection1;
    proc.StoredProcName := 'CustomerGetParam';
    i := proc.Params.Count;
    memo1.Lines.Add('StoredProcParam params = '+i.ToString);
    proc.ParamByName('pFirst').Value := 'Test';
    proc.Open;
    memo1.Lines.Add('StoredProcParam firstname = '+proc.FieldByName('FirstName').AsString);
  except
    on e:exception do begin
      Memo1.Lines.Add('Exception: '+e.Message);
    end;
  end;
end;

with
DataBase=wwn-bvt-3136:1521/bbts
UseQuoteChar=False
as soon as I attempt proc.StoredProcName := 'CustomerGetParam'; I get exception: Exception: Could not parse the GetProcedureParameters metadata command. Problem found near: -. Original query: GetProcedureParameters wwn-bvt-3136:1521/bbts . envision . CustomerGet .

It clearly doesn't like the . in the database name. The exception is in the delphi ui only, it doesn't fire the exception handler. I get the message that the paramcount is 0.
The attempt to set the parameter actually raises the exception, as the paramcount is 0. If I comment that line out, the above exception is raised on the proc.open command.

setting UseQuoteChar=True

Now setting the proc name is good. I get the message that the paramcount is 4, which is correct, so clearly GetProcedureParameters worked just fine. And I can set a param. It is when I get to proc.open I get Exception: ORA-06550: line 2, column 4:
PLS-00201: identifier 'CustomerGet' must be declared
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored

I am thinking internally it is putting extra quotes around the stored proc name or something
In all these cases the TSQLQuery works just fine.

Any ideas?

wnielsenbb

It is package with two procedures with the same name. One gets customer info given the customerId and the second gets customer info given the customerNumber.

Package CustomerFunctions
Procedure GetCustInfo
( customerId IN Customer.CustomerId%Type,
pList OUT NOCOPY SYS_REFCURSOR
);
Procedure GetCustInfo
( customerNumber IN Customer.CustomerNumber%Type,
pList OUT NOCOPY SYS_REFCURSOR
)
When I make a TSQLStoredProc and assign package='CustomerFunctions' and storedProcName = 'GetCustInfo' I get all 4 parameters.
My workaround is to turn Paramcheck off and manually create parameters, but that is unwieldy and the next time the dba decides to overload another function that one will break.

wnielsenbb

Probably a dumb question, but I can't figure out how to access an overloaded stored procedure.
I have a package with two procedures with different names. How do I access a particular one?

wnielsenbb

I am using the dbExpress Driver for Oracle version 6.9.13
Delphi 10.1 Update 2
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production. It is not using unicode.
My client is 32 bit, and my Delphi project is 32 bit. I have a tnsname entry 'wwn3134'
I put a new SQLConnection on a form and set it to non-unicode.
I put a SQLQuery and two SQLStoredProc on the form. I create a stored proc and a package with a proc.
The query and procs all just SELECT firstname FROM CUSTOMER WHERE ROWNUM = 1;
I add a memo to show results add a button to run. It connects and opens the data and presents it.
All is good.
Now I want to connect without using a TNS entry, as when I deploy my app I don't want customers to have to set up a TNS name. I will deploy the Oracle instaclient with my app.
I change my database to 'wwn-bvt-3134:1521/bbts'
It connects and the query runs fine, but the stored procs give me exceptions:

Could not parse the GetProcedureParameters metadata command. Problem found near: -. Original query: GetProcedureParameters wwn-bvt-3134:1521/bbts . envision . CustomerGet .
Open Package Proc

If I turn off ParamCheck at runtime on the stored procs everything works fine. But that isn't very handy as it needs to be on at design time to set up the fields.

Am I connecting incorrectly?
I tried setting UseQuoteChar='True' and got

Exception: ORA-06550: line 2, column 4:
PLS-00201: identifier 'CustomerGet' must be declared
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored

-- run with tnsname Database='wwn3134'

Open Connection
Open Query
query firstname = Herman
Open StoredProc
Proc firstname = Herman
Open Package Proc
Package Proc firstname = Herman

-- run with connection string Database='wwn-bvt-3134:1521/bbts' or Database='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wwn-bvt-3134)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=bbts)))'

Open Connection
Open Query
query firstname = Herman
Open StoredProc
Exception: Could not parse the GetProcedureParameters metadata command. Problem found near: -. Original query: GetProcedureParameters wwn-bvt-3134:1521/bbts . envision . CustomerGet .
Open Package Proc
Exception: Could not parse the GetPackageProcedureParameters metadata command. Problem found near: -. Original query: GetPackageProcedureParameters wwn-bvt-3134:1521/bbts . envision . CustomerFuncs . GetPackCustomer .

Form:

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 299
  ClientWidth = 635
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Memo1: TMemo
    Left = 280
    Top = 40
    Width = 313
    Height = 225
    Lines.Strings = (
      'Memo1')
    TabOrder = 0
  end
  object Button1: TButton
    Left = 136
    Top = 232
    Width = 75
    Height = 25
    Caption = 'Button1'
    TabOrder = 1
    OnClick = Button1Click
  end
  object SQLConnection1: TSQLConnection
    ConnectionName = 'Devart Oracle'
    DriverName = 'DevartOracle'
    LoginPrompt = False
    Params.Strings = (
      'BlobSize=-1'
      'DataBase=wwn-bvt-3134:1521/bbts'
      'DriverName=DevartOracle'
      'ErrorResourceFile='
      'LocaleCode=0000'
      'Password=password1'
      'Oracle TransIsolation=ReadCommitted'
      'RoleName=Normal'
      'User_Name=envision'
      'LongStrings=False'
      'EnableBCD=False'
      'InternalName='
      'FetchAll=False'
      'CharLength=0'
      'Charset='
      'UseQuoteChar=True'
      'UseUnicode=False'
      'UnicodeEnvironment=False')
    Left = 72
    Top = 40
  end
  object SQLQuery1: TSQLQuery
    MaxBlobSize = -1
    Params = <>
    SQL.Strings = (
      ' SELECT  firstname  FROM CUSTOMER  WHERE ROWNUM = 1')
    SQLConnection = SQLConnection1
    Left = 192
    Top = 40
    object SQLQuery1FIRSTNAME: TStringField
      FieldName = 'FIRSTNAME'
      Size = 30
    end
  end
  object SQLStoredProc1: TSQLStoredProc
    MaxBlobSize = -1
    Params = <
      item
        DataType = ftCursor
        Precision = 8000
        Name = 'PLIST'
        ParamType = ptOutput
        Size = 8000
      end>
    SQLConnection = SQLConnection1
    StoredProcName = 'CustomerGet'
    Left = 192
    Top = 104
    object SQLStoredProc1FIRSTNAME: TStringField
      FieldName = 'FIRSTNAME'
      Size = 30
    end
  end
  object SQLStoredProc2: TSQLStoredProc
    MaxBlobSize = -1
    Params = <
      item
        DataType = ftCursor
        Precision = 8000
        Name = 'PLIST'
        ParamType = ptOutput
        Size = 8000
      end>
    PackageName = 'CustomerFuncs'
    SQLConnection = SQLConnection1
    StoredProcName = 'GetPackCustomer'
    Left = 192
    Top = 160
    object SQLStoredProc2FIRSTNAME: TStringField
      FieldName = 'FIRSTNAME'
      Size = 30
    end
  end
end

Code:

Code: Select all

unit testproc;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, DBXDevartOracle, Data.FMTBcd,
  Vcl.StdCtrls, Data.SqlExpr, Data.DB;

type
  TForm1 = class(TForm)
    SQLConnection1: TSQLConnection;
    Memo1: TMemo;
    Button1: TButton;
    SQLQuery1: TSQLQuery;
    SQLStoredProc1: TSQLStoredProc;
    SQLStoredProc2: TSQLStoredProc;
    SQLQuery1FIRSTNAME: TStringField;
    SQLStoredProc1FIRSTNAME: TStringField;
    SQLStoredProc2FIRSTNAME: TStringField;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
  memo1.Lines.Clear;
  memo1.Lines.Add('Open Connection');
  SQLConnection1.Open;
  memo1.Lines.Add('Open Query');
  sqlQuery1.Open;
  memo1.Lines.Add('query firstname = '+SQLQuery1FIRSTNAME.AsString);
  memo1.Lines.Add('Open StoredProc');
  try
    SQLStoredProc1.Open;
    memo1.Lines.Add('Proc firstname = '+SQLStoredProc1FIRSTNAME.AsString);
  except
    on e:exception do begin
      Memo1.Lines.Add('Exception: '+e.Message);
    end;
  end;
  memo1.Lines.Add('Open Package Proc');
  try
    SQLStoredProc2.Open;
    memo1.Lines.Add('Package Proc firstname = '+SQLStoredProc2FIRSTNAME.AsString);
  except
    on e:exception do begin
      Memo1.Lines.Add('Exception: '+e.Message);
    end;
  end;
end;
end.

Stored proc code

Code: Select all

CREATE OR REPLACE PROCEDURE CustomerGet(pList OUT NOCOPY SYS_REFCURSOR)
AS
BEGIN
  OPEN    pList FOR
  SELECT  firstname
  FROM CUSTOMER
  WHERE ROWNUM = 1;
END;
/

Package Code:

Code: Select all

CREATE OR REPLACE PACKAGE Envision.CustomerFuncs AUTHID Definer
AS
  PROCEDURE GetPackCustomer (pList              OUT NOCOPY SYS_REFCURSOR);
END;
/

CREATE OR REPLACE PACKAGE BODY Envision.CustomerFuncs
AS

  PROCEDURE GetPackCustomer (pList              OUT NOCOPY SYS_REFCURSOR)
  AS BEGIN
      OPEN    pList FOR
      SELECT  firstname
      FROM CUSTOMER
      WHERE ROWNUM = 1;
  END;
END;
/

Devart Forums

Search found 5 matches

Re: How to connect without a TNS name entry

Re: How to connect without a TNS name entry

Re: How to access Overloaded Stored Procedure

How to access Overloaded Stored Procedure

How to connect without a TNS name entry