Getting started with Oracle OCI

Bascy · 2012-06-22 11:03:44

We are evaluating a number of OPF frameworks, to determine which (if any) could be a candidate for our next redesign.
Mormot is on our longlist!

To have an idea of the performance of mORMot, we want to test it with a testable Oracle server we have. So i'm trying to adapt your Demo02 to connect to our oracle server in stead of the embedded SQLite, but i cant seem to get the hang of it.

Are there any small tret projects already setup. or easily adapted for connecting to an Oracle Server?

Greetings
Bas

ab · 2012-06-22 17:19:44

You can't connect directly the ORM of the framework to Oracle, for an existing DB.
You'll have to use external DB kind of table, then the DB will be created according to the TSQLRecord fields.
See http://blog.synopse.info/post/2011/08/0 … e3-limited and the corresponding part of the mORMot documentation.
To map an existing DB, it is not yet ready.

Our native Oracle connection layers are very optimized, and used in production with several huge DB.
Those are the SynDB/SynDBOracle units.
See http://blog.synopse.info/tag/Oracle

Note that Oracle as a backend is not fully tested as TSQLRecordExternal (i.e. ORM records).
Feedback and contributions are welcome here!

In fact, when existing DB is very complex, and with a lot of data, we prefer re-use existing tuned SQL statements (using SynDB/SynDBOracle), then publish those as services, or virtual tables.
See http://blog.synopse.info/post/2012/03/0 … d-services and http://blog.synopse.info/post/2011/05/1 … -framework
With virtual tables, you are still able to use CRUD / RESTful operations on the data, have optional in-memory cache at the Client or Server level - see http://blog.synopse.info/post/2012/02/14/ORM-cache - and even JOIN queries among several databases (i.e. query both Oracle and other DBs like SQLite3, MS SQL or other Oracle instances in the same request).
This is quite unique features among other ORMs!

Note also that mORMot feature Client-Server ORM, that is you do not need to deploy the Oracle library on clients, just on application server.
It will be much easier to deploy: JSON + HTTP communication is very easy to configure, and found out to have very good speed (optimization was very high here).
Another genuine feature among other OPF.

ab · 2012-07-03 15:30:29

We have just committed huge code refactoring and set of fixes, mostly related to:
- Oracle handling of BLOB / CLOB fields and Unicode columns (NCLOB/NVARCHAR2);
- mORMot access of external Oracle tables.

See http://synopse.info/fossil/info/7bf4073f38

Here is a modified version of the "Project 3" sample:

unit Unit2;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, SynCommons, SQLite3Commons, SQLite3, StdCtrls,
  SQLite3DB, SynDB, SynDBOracle;

type
  TSQLSampleRecord = class(TSQLRecordExternal)
  private
    fQuestion: RawUTF8;
    fName: RawUTF8;
    fTime: TModTime;
  published
    property Time: TModTime read fTime write fTime;
    property Name: RawUTF8 index 40 read fName write fName;
    property Question: RawUTF8 read fQuestion write fQuestion;
  end;

  TForm1 = class(TForm)
    Label1: TLabel;
    Button1: TButton;
    Label2: TLabel;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure FormShow(Sender: TObject);
  private
  public
    Model: TSQLModel;
    Props: TSQLDBOracleConnectionProperties;
    Server: TSQLRestServerDB;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
  Close;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  Props := TSQLDBOracleConnectionProperties.Create('database','user','password');
  Model := TSQLModel.Create([TSQLSampleRecord]);
  VirtualTableExternalRegister(Model,TSQLSampleRecord,Props,'SampleRecord');
  Server := TSQLRestServerDB.Create(Model,ChangeFileExt(paramstr(0),'.db3'));
  Server.CreateMissingTables;
  Server.ExportServerNamedPipe('03');
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  Server.Free;
  Model.Free;
  Props.Free;
end;

procedure TForm1.FormShow(Sender: TObject);
begin
  Label1.Caption := Caption;
end;

end.

It has been used for this testing.
The default "Client 3" program will connect without any problem to this server, but all will be retrieved from Oracle backend.
We have tuned performance, when used with mORMot external tables.

Any feedback is welcome!

Bascy · 2012-07-04 07:43:45

Tried this, but the server is saving its data in the local .db3 database ...
To get rid of any confusion about the different Sample projects sharing units and stuff, i've copied unit1, unit2 and Sampledata from the 01 project to my own "project 03a"
But i can see in the .db3-file that the last messages i added with the running client, is actually stored there. There also is no SampleRecord table created on the Oracle server.

I'd be happy to supply you with the total test project, tell me where to place or mail it to.

greetings
Bas

ab · 2012-07-04 08:32:37

I suspect you did not retrieve the latest source from http://synopse.info/fossil

On my computer, the .db3 file only contain the external table definition, not the last messages:

CREATE VIRTUAL TABLE SampleRecord USING External(Time INTEGER, Name TEXT COLLATE SYSTEMNOCASE, Question TEXT COLLATE SYSTEMNOCASE)

All data is written within the Oracle database only.

Bascy · 2012-07-04 08:49:30

Checked the sources of SynOleDB.pas and SynDBOracle.pas and both contain the latest additions as shown in http://synopse.info/fossil/info/7bf4073f38
Removed all .dcu files from the Mormot tree and recompiled my project: same result, the newly added messages can be found in the db3 file.

Can I mail the project as a .rar archive to you? Please provide a mail address or ftp server

Last edited by Bascy (2012-07-04 08:50:15)

ab · 2012-07-04 08:57:52

You can use webcontact01 at synopse dot info as email address.

Bascy · 2012-07-04 09:01:21

.RAR archive sent!

ab · 2012-07-04 17:25:47

Just delete the existing .db3 file and it will recreate a VIRTUAL TABLE instead of the existing regular TABLE.
And it will use Oracle as expected.

I've just found out an issue when external tables content are updated directly via their TSQLRestServerStaticExternal access: the internal SQL cache of the framework is not reset as expected.
The TSQLRestServerStaticInMemory class has now a dedicated TSQLRestServerStaticInMemoryExternal child, for properly handling this SQL/JSON internal cache when called as SQLite3 virtual tables.
It is fixed by http://synopse.info/fossil/info/ce2e1dec5c

Bascy · 2012-07-05 08:44:40

Great, thanks for the quick respons! Perfect support.

[Edit]
Mmmmm was celebrating to early i'm afraid. I'm running into the fenv=nil problem again I made ticket for earlier. This does mean it really connects to Oracle now ;-)
Any idea on the ETA of the fEnv=nil problem?

Last edited by Bascy (2012-07-05 08:51:13)

ab · 2012-07-05 11:27:27

I looked at the ticket, but was not able to reproduce it.
Is it triggered with the sample program you sent me?

It is perfectly normal that fEnv=nil the first time it reaches this part of the code.
The EnvNlsCreate() OCI call will initialize the environment and FILL fEnv parameter with the appropriate handle value.

So I do not get what is wrong here.
Which version of the OCI do you use? Instant Client or Full Client? It needs to support OCI_UTF8 encoding, which should be always the case.
Do you reach TSQLDBOracleConnection.Connect method several time in your code? Normally, it should be launched only once per thread.

Bascy · 2012-07-05 12:14:20

ab wrote:

Is it triggered with the sample program you sent me?

Yes, same program.

ab wrote:

It is perfectly normal that fEnv=nil the first time it reaches this part of the code.
The EnvNlsCreate() OCI call will initialize the environment and FILL fEnv parameter with the appropriate handle value.

The access violation occurs on SynDBOracle.TSQLDBOracleLib.CodePageToCharSet line 1201

function TSQLDBOracleLib.CodePageToCharSet(env: pointer;
  aCodePage: integer): integer;
var ocp: PUTF8Char;
    i: integer;
begin
  case aCodePage of
  CP_UTF8:
    result := OCI_UTF8;
  CP_UTF16:
    result := OCI_UTF16ID;
  else begin
    ocp := CODEPAGES[0].Text; // default is MS Windows Code Page 1252
    for i := 0 to high(CODEPAGES) do
      if aCodePage=CODEPAGES[i].Num then begin
        ocp := CODEPAGES[i].Text;
        break;
      end;
    result := NlsCharSetNameToID(env,ocp); // <==== ACCESS VIOLATION HERE!!!!
    if result=0 then
      result := OCI_WE8MSWIN1252;
  end;
  end;
end;

The call stack directly after the access violation is: (notice the call to CodePageToCharSet having the first parameter NIL)

:61d41761 ; D:\oracle\product\10.2.0\client_1\bin\OraClient10.Dll
:61c28fee OraClient10.OCINlsCharSetNameToId + 0xe
:1000e8e0 oci.OCINlsCharSetNameToId + 0x18
SynDBOracle.TSQLDBOracleLib.CodePageToCharSet(nil,1252)
SynDBOracle.TSQLDBOracleConnection.Connect
SynDB.TSQLDBStatement.Prepare('select sysdate from dual',True)
SynDBOracle.TSQLDBOracleStatement.Prepare('select sysdate from dual',True)
SynDB.TSQLDBStatement.Execute('select sysdate from dual',True,(...))
SynDB.TSQLDBConnectionProperties.Execute('select sysdate from dual',(...),nil)
SynDB.TSQLDBConnection.GetServerTimeStamp
SQLite3DB.TSQLRestServerStaticExternal.Create(TSQLSampleRecord,$1FBFBA0,'D:\Sources\Research\mORMot\SQLite3\Samples\03a - Client-Server Oracle\SampleRecord.external',False)
SQLite3Commons.TSQLVirtualTable.Create($1F71350,'SampleRecord',3,$208ABE4)
SQLite3.vt_Create(33589048,$1F71350,6,$208ABD8,$20437C8,nil {#0})
SynSQLite3.sqlite3_create_module_v2(33589048,'ˆ7'#4#2'ÿÿÿÿ',$1FB74A8,$5B9520,$1F91CFC)
:0059865e sqlite3_create_module_v2 + $5BE
SynSQLite3.sqlite3_create_module_v2(33589048,nil {#0},$20437A8,$1F91CFC,$2008738)
:005989fb sqlite3_create_module_v2 + $95B
SynSQLite3.sqlite3_value_numeric_type(33103048)
:00578e6a sqlite3_value_numeric_type + $5CE6
SynSQLite3.sqlite3_result_value(33103048,0)
:00572042 sqlite3_result_value + $1E2
SynSQLite3.sqlite3_step(33103048)
:0057215b sqlite3_step + $4F
SynSQLite3.TSQLRequest.Step
SynSQLite3.TSQLRequest.Execute
SynSQLite3.TSQLRequest.Execute(33589048,'CREATE VIRTUAL TABLE SampleRecord USING External(Time INTEGER, Name TEXT COLLATE SYSTEMNOCASE, Question TEXT COLLATE SYSTEMNOCASE);')
SynSQLite3.TSQLDatabase.Execute('CREATE VIRTUAL TABLE SampleRecord USING External(Time INTEGER, Name TEXT COLLATE SYSTEMNOCASE, Question TEXT COLLATE SYSTEMNOCASE);')
SQLite3.TSQLRestServerDB.CreateMissingTables(0)
ufrmServer.TfrmServer.FormCreate($1FED0C0)
:00501c4b TCustomForm.DoCreate + $37
:0050188f TCustomForm.AfterConstruction + $17
:0050185e TCustomForm.Create + $1A6
:0050c289 TApplication.CreateForm + $79
Project03aServer.Project03aServer
:7605339a kernel32.BaseThreadInitThunk + 0x12
:77299ef2 ntdll.RtlInitializeExceptionChain + 0x63
:77299ec5 ntdll.RtlInitializeExceptionChain + 0x36

Which version of the OCI do you use? Instant Client or Full Client? It needs to support OCI_UTF8 encoding, which should be always the case.

I have oracle client 10.2.0 installed, actually don't really know if its InstantClient or FullClient .. I'm not such a Oracle expert

If you need any more info, feel free to ask. I really like the framework you are setting up here, but for us to use it, Oracle is a MustHave

PS Is there a way to get notified by mail on new entriues of this forum thread? I cannot seem to find a Notify button

ab · 2012-07-05 16:26:27

I suspect the EnvNlsCreate() call returns an error code.

Could you change the code as such:

   Status: integer;
(...)
   if fEnv=nil then 
   begin
      // will use UTF-8 encoding by default, in a mono-thread basis
     Status := EnvNlsCreate(fEnv,OCI_DEFAULT,nil,nil,nil,nil,0,nil,OCI_UTF8,OCI_UTF8);
     assert(Status=OCI_SUCCESS); 
   end;

Status should be 0.
What is the returned value?

By the way, does SQLPlus work as expected?
Are you able to connect to Oracle with this client?
Are there several Oracle clients installed on the PC?
See also http://perezgb.com/2009/08/17/ocienvnls … urn-code-1 - that is, check .dll versions.

Could you try on another PC with the same client? If possible installed from scratch, or with the Instant Client (need no installation, just to be in the .exe folder).

Bascy · 2012-07-05 17:54:23

The Status returned is -1
I can connect to the database with the Enterprise Manager COnsole
I have only one client installed on my PC.

I added ORACLE_HOME environment var. cause that wasnt defined, but doesnt make a difference
I checked all loaded oracle dlls, but loaded libs and dll are from the correct clientfolder ...

Last edited by Bascy (2012-07-05 17:56:16)

ab · 2012-07-05 20:24:14

AFAIK Enterprise Manager COnsole is a Java app which does not use OCI.DLL but OJDBC.

Could you try with SQLPlus?
Or the BDE? Or DBExpress client?

ORACLE_HOME environment var. is used only if OCI.DLL is not found.
So it won't change anything.

Please try on another PC.
It is working perfectly with OCI 11g, and should work with the 10g client.

Bascy · 2012-07-06 06:57:46

Tried it on another pc with the same Oracle client installed, and get the same results
I can connect with commandline sqlplus, resulting in the following connect message:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

ab · 2012-07-06 08:42:00

Is it the 32 bit client?

Bascy · 2012-07-06 09:01:45

Looking at the registry, i see the Oracle settings in HKLM\SOFTWARE\Wow6432Node\Oracle
so i'm pretty sure its the 32bits client i have installed

ab · 2012-07-06 11:27:36

Is the tnsname.ora file in the right path?

I just downloaded Oracle instant client 10.2.0.5 32 bit from http://www.oracle.com/technetwork/topic … 85727.html (oci.dll is shown as 10.2.0.1 in ProcessExplorer), and it works as expected (including UTF-8 encoding)...

Therefore, I can't understand your particular issue here.

You could do the same (go to this link and unzip all files to your .exe directory), and see what is happening.

By the way, the Instant Client is faster to start than the default full OCI client installed on the same PC.
Then processing speed is almost the same.
Sounds like the Full Client uses much more libraries (.dll files), so is slower to initialize!

Bascy · 2012-07-06 12:19:58

did as you asked, and with the instant client 10.2.0.5 in the exe folder, and the TNSNAMES file there to everything is working!

ab · 2012-07-06 12:47:42

Sounds definitively like a strange issue with your main client installation.

I'm happy it is working now!

mORMot Open Source

#1 2012-06-22 11:03:44

Getting started with Oracle OCI

#2 2012-06-22 17:19:44

Re: Getting started with Oracle OCI

#3 2012-07-03 15:30:29

Re: Getting started with Oracle OCI

#4 2012-07-04 07:43:45

Re: Getting started with Oracle OCI

#5 2012-07-04 08:32:37

Re: Getting started with Oracle OCI

#6 2012-07-04 08:49:30

Re: Getting started with Oracle OCI

#7 2012-07-04 08:57:52

Re: Getting started with Oracle OCI

#8 2012-07-04 09:01:21

Re: Getting started with Oracle OCI

#9 2012-07-04 17:25:47

Re: Getting started with Oracle OCI

#10 2012-07-05 08:44:40

Re: Getting started with Oracle OCI

#11 2012-07-05 11:27:27

Re: Getting started with Oracle OCI

#12 2012-07-05 12:14:20

Re: Getting started with Oracle OCI

#13 2012-07-05 16:26:27

Re: Getting started with Oracle OCI

#14 2012-07-05 17:54:23

Re: Getting started with Oracle OCI

#15 2012-07-05 20:24:14

Re: Getting started with Oracle OCI

#16 2012-07-06 06:57:46

Re: Getting started with Oracle OCI

#17 2012-07-06 08:42:00

Re: Getting started with Oracle OCI

#18 2012-07-06 09:01:45

Re: Getting started with Oracle OCI

#19 2012-07-06 11:27:36

Re: Getting started with Oracle OCI

#20 2012-07-06 12:19:58

Re: Getting started with Oracle OCI

#21 2012-07-06 12:47:42

Re: Getting started with Oracle OCI

Board footer