How to connect without a TNS name entry

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
wnielsenbb
Posts: 3
Joined: Mon 01 May 2017 17:31

How to connect without a TNS name entry

Post by wnielsenbb » Tue 02 May 2017 18:41

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;
/

MaximG
Devart Team
Posts: 1409
Joined: Mon 06 Jul 2015 11:34

Re: How to connect without a TNS name entry

Post by MaximG » Wed 10 May 2017 15:01

We are investigating this behavior causes and will inform you about the results soon

mesolee
Posts: 1
Joined: Sat 16 Feb 2019 13:34
Contact:

Re: How to connect without a TNS name entry

Post by mesolee » Sat 16 Feb 2019 13:40

Hi
I am trying to use SQLPlus to connect to database directly using just the connect string (ie without referencing to tnsnames.ora)

I have this in my tnsnames.ora
POD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = PODS.GATE.COM)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ODS)
)
)

I can connect to the database if I use the command below, obviously the SQLPlus is refering the tnsnames.ora that I have)
sqlplus USER/PASSWORD@POD

However, if I do this, I will the error shown below.
sqlplus USER/PASSWORD@//PODS.GATE.COM:1521/ODS

ORA-12154: TNS:could not resolve the connect identifier specified

Is there any way, I can connect to the database without refering to tnsnames.ora as defined in the TNS_ADMIN system environment variables?

Thanks in advance for your help

------------------
Stage 4 Mesothelioma Life Expectancy. In stage 4, pleural mesothelioma cancer cells have spread into the chest wall, bones, lining of the heart, abdomen and liver.

MaximG
Devart Team
Posts: 1409
Joined: Mon 06 Jul 2015 11:34

Re: How to connect without a TNS name entry

Post by MaximG » Mon 18 Feb 2019 11:11

The issue you describe does not relate to the work of our driver. To find a required solution, please refer to the Oracle documentation or one of the specialized forums

wnielsenbb
Posts: 3
Joined: Mon 01 May 2017 17:31

Re: How to connect without a TNS name entry

Post by wnielsenbb » Fri 27 Mar 2020 03:01

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?

MaximG
Devart Team
Posts: 1409
Joined: Mon 06 Jul 2015 11:34

Re: How to connect without a TNS name entry

Post by MaximG » Thu 30 Apr 2020 15:35

We've reproduced the issue in our environment. We'll investigate it and offer you a solution.

wnielsenbb
Posts: 3
Joined: Mon 01 May 2017 17:31

Re: How to connect without a TNS name entry

Post by wnielsenbb » Thu 30 Apr 2020 23:18

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.

Post Reply