Connecting to Oracel db
lazarus / 16-Databases

----------------------------------------------------------------------
SteenJorgensen · 2018-08-25 13:37

I am trying to connect to a Oracel sql database, and this give me big
trouble. I thing i have try everything without luck.
Is there a nice person who can give me a hist or an example?

Maby my problems is the Oracle driver, OCI.dll files??? and where to
set port?

I have made a simpel code for test. This give me a "The creation of an
Oracle environment failed." or "OCI.DLL_ERROR"

procedure TForm1.btConnectClick(Sender: TObject);
var
  Conn : TOracleConnection;
  Tran : TSQLTransaction;
  Q    : TSQLQuery;
begin
  Try
    try
      Conn := TOracleConnection.Create(nil);
      Tran := TSQLTransaction.Create(nil);
      Q := TSQLQuery.Create(nil);

      Conn.HostName := ''; //my ip, user, password and databasename.
      Conn.UserName := '';
      Conn.Password := '';
      Conn.DatabaseName := '';
      Conn.Connected := True;

      Conn.Transaction := Tran;
      Q.DataBase := Conn;
      Conn.Open;
      Tran.Active := true;

      Q.SQL.Text := 'SELECT * From EditionView_SPR';
      Q.Open;
      Q.First;
      while not (Q.EOF) do
      begin
        Memo1.Lines.Add('x');
        Q.Next;
      end;
      Q.Close;
    finally
      Q.Free;
      Tran.Free;
      Conn.Free;
    end;

  except
      on E: Exception do
      Begin
        Memo1.Lines.Add('Except fejl: ' + E.Message);
      end;
    end;
end;

----------------------------------------------------------------------
Thaddy · 2018-08-25 14:10

Do you have the Oracle client (from Oracle, not the Lazarus component)
installed and in your path?
The error indicates there is either NO Oracle client installed or a
wrong version (64 bit vs 32 bit but also too old or too new).
Lazarus relies on a proper installation of the Oracle client for sqldb
to work with an Oracle client.

----------------------------------------------------------------------
SteenJorgensen · 2018-08-25 14:36

I have downloaded "instantclient-basic-windows.x64-12.2.0.1.0.zip" and
copy the files  into a catalog, then and add this path to PATH
variable. Nothing more....

Wow, get another Oracle client 32-bit and this give me "ORA-03134:
Connections to this server version are no longer supported." Mabye
this is just about to find the right version of Oracledriver???

----------------------------------------------------------------------
Mike.Cornflake · 2018-08-25 16:10

I use the method Thaddy suggests - my apps require Oracle Client
installed on the PC first.  I use the Zeos components for connecting
(From memory the reason is I prefer Zeos method of handling
RecordCount for queries).

I've never used InstantClient.  I know that LazSQLX does though. 
That's open source, you can download that have a look how it's done
there.

https://github.com/flakron-shkodra/LazSqlX

----------------------------------------------------------------------
Thaddy · 2018-08-25 16:18

Quote from: SteenJorgensen on August 25, 2018, 04:36:44 pmI have
downloaded "instantclient-basic-windows.x64-12.2.0.1.0.zip" and copy
the files  into a catalog, then and add this path to PATH variable.
Nothing more....

Wow, get another Oracle client 32-bit and this give me "ORA-03134:
Connections to this server version are no longer supported." Mabye
this is just about to find the right version of Oracledriver???
Oracle itself provides free client drivers for personal use. Also
x86_64. You just need the correct dll.

----------------------------------------------------------------------
SteenJorgensen · 2018-08-26 06:33

Found a good solutions for this problem: Devart ODAC package.

Both Lazarus TOraclecomponent and Zeos TZConnection are depending of a
Oracle drivere and i can't use this in a commercial solutions. Special
when it restricted to personal use.

Thanks for fine answer that lead me to a solutions.

----------------------------------------------------------------------
Thaddy · 2018-08-26 07:37

A poor man's solution is of course going through ODBC. But devart's
commercial solutions are well known and usually high quality.

----------------------------------------------------------------------
tudi_x · 2018-08-26 09:46

@SteenJorgenser
i have Devart Unidac license.
for USD200 more than ODac you get connection to other types of DBs.
for me it was worth the cost.