Pages: 1

#1 2015-06-22 15:47:52

Cahaya
Member
Registered: 2015-06-21
Posts: 36

Get Start with SynDBOracle

Hi AB,

I try to use, TSQLDBOracleConnectionProperties.Create('ORCL81','orcl81','scott','tiger');
I got error, "raise ESQLDBOracle.CreateFmt('Invalid oci.dll: missing %s',[OCI_ENTRIES[]]);"

I use oracle 8.1.7, is it compatible with mormot 1.18 ?

Thank you.

Offline

#2 2015-06-22 16:16:59

Cahaya
Member
Registered: 2015-06-21
Posts: 36

Re: Get Start with SynDBOracle

procedure TForm1.Button1Click(Sender: TObject);
var
  Props      : TSQLDBOracleConnectionProperties;
  rows       : ISQLDBRows;
begin
  Props := TSQLDBOracleConnectionProperties.Create('localhost','XE','scott','tiger');
  try
    rows := Props.Execute('select * from truck_trans',[]);
    rows.Step;
    Showmessage(utf8ToString(rows.ColumnUTF8(0)));
  finally
    //Props.Free;
  end;
end;

It is running well for oracle XE 11g, OS windows 7 64bit (apps compiled inside 32bit windows XP).
Props.Free will raise exception "access violation, ntdll.dll"

Last edited by Cahaya (2015-06-22 16:18:26)

Offline

#3 2015-06-22 17:54:01

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: Get Start with SynDBOracle

1. Oracle 8 is not compatible, I guess.
It is a pretty old version.

2. The rows: ISQLDBRows is freed after Props by the compiler.
Please use a common property, or a sub-function, as stated by http://synopse.info/files/html/Synopse% … l#TITL_137
Or release rows by hand:

procedure TForm1.Button1Click(Sender: TObject);
var
  Props      : TSQLDBOracleConnectionProperties;
  rows       : ISQLDBRows;
begin
  Props := TSQLDBOracleConnectionProperties.Create('localhost','XE','scott','tiger');
  try
    rows := Props.Execute('select * from truck_trans',[]);
    rows.Step;
    Showmessage(utf8ToString(rows.ColumnUTF8(0)));
  finally
    rows := nil; // should be freed BEFORE Props
    Props.Free;
  end;
end;

Offline

#4 2015-06-23 03:01:38

Cahaya
Member
Registered: 2015-06-21
Posts: 36

Re: Get Start with SynDBOracle

Hi AB,

Your last code still error.
Something wrong with this,       OCI.Check(nil,self,OCI.StmtRelease(fStatement,fError,nil,0,RELEASE_MODE[AfterError]),fError) else
      OCI.HandleFree(fStatement,OCI_HTYPE_STMT);

Delphi 7 debug come to that line when Props.Free execute.

Thank you.

Offline

#5 2015-06-23 03:17:44

Cahaya
Member
Registered: 2015-06-21
Posts: 36

Re: Get Start with SynDBOracle

This code is good without error. I guess something wrong with OCI.Check line.
By the way, any problem with my oracle XE 32 bit inside 64 bit windows ? In my view, my oracle database is good, but I don't know whether any connection inside your code, related to memory.

  if fStatement<>nil then begin
    {
    if fUseServerSideStatementCache then
      OCI.Check(nil,self,OCI.StmtRelease(fStatement,fError,nil,0,RELEASE_MODE[AfterError]),fError) else
      OCI.HandleFree(fStatement,OCI_HTYPE_STMT);
    }
    OCI.HandleFree(fStatement,OCI_HTYPE_STMT);
    fStatement := nil;
  end;

Offline

#6 2015-06-23 05:03:43

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: Get Start with SynDBOracle

Use a sub function.

OCI.Check() is fine.

Offline

#7 2015-06-23 14:57:21

Cahaya
Member
Registered: 2015-06-21
Posts: 36

Re: Get Start with SynDBOracle

Your procedure TSQLDBOracleStatement.FreeHandles(AfterError: boolean) probably has problem. Because I got an error when I execute Free. The error probably because your code :

if fUseServerSideStatementCache then
      OCI.Check(nil,self,OCI.StmtRelease(fStatement,fError,nil,0,RELEASE_MODE[AfterError]),fError) else
      OCI.HandleFree(fStatement,OCI_HTYPE_STMT);

I don't understand your last posted. What sub function you mean ?

Last edited by Cahaya (2015-06-23 15:54:01)

Offline

#8 2015-06-23 16:02:53

Cahaya
Member
Registered: 2015-06-21
Posts: 36

Re: Get Start with SynDBOracle

AB,

I got the same error when I free TSQLDBOracleConnectionProperties from lazarus 1.2.6.

I think you don't get my problem. Did you test it for oracle xe ?

Thank you.

Offline

#9 2015-06-23 16:26:22

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: Get Start with SynDBOracle

We did not test SynDBOracle in FPC/Lazarus at all.

Offline

#10 2015-06-23 16:42:53

Cahaya
Member
Registered: 2015-06-21
Posts: 36

Re: Get Start with SynDBOracle

Did you test it, for delphi 7 ? The same problem arise when you free TSQLDBOracleConnectionProperties;
The problem is OCI.StmtRelease. Any solution ?

Thank you.

Offline

#11 2015-06-23 16:49:42

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: Get Start with SynDBOracle

I do not understand the context of the problem.

Did you correct your previous code about Props: TSQLDBOracleConnectionProperties and rows: ISQLDBRows ?

What you posted above was wrong: the ISQLDBRows should be released before the TSQLDBOracleConnectionProperties, otherwise the statement is released on a closed connection.

Offline

#12 2015-06-23 17:16:43

Cahaya
Member
Registered: 2015-06-21
Posts: 36

Re: Get Start with SynDBOracle

I did, this before as you suggested,

procedure TForm1.Button1Click(Sender: TObject);
var
  Props      : TSQLDBOracleConnectionProperties;
  rows       : ISQLDBRows;
begin
  Props := TSQLDBOracleConnectionProperties.Create('localhost','XE','scott','tiger');
  try
    rows := Props.Execute('select * from truck_trans',[]);
    rows.Step;
    Showmessage(utf8ToString(rows.ColumnUTF8(0)));
  finally
    rows := nil; // should be freed BEFORE Props
    Props.Free;
  end;
end;

I still got an error for delphi 7 but it work for lazarus 1.2.6

By the way, I can not hack my delphi 7 for system and sysutils. Is it enough if I just replace the dcu ?

Thank you.

Offline

#13 2015-06-23 17:23:08

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: Get Start with SynDBOracle

Your code is just wrong.

Please try what I stated:

procedure TForm1.Button1Click(Sender: TObject);

procedure DoQuery(Props: TSQLDBOracleConnectionProperties);
var rows       : ISQLDBRows;
begin
  rows := Props.Execute('select * from truck_trans',[]);
  rows.Step;
  Showmessage(utf8ToString(rows.ColumnUTF8(0)));
end;

var
  Props      : TSQLDBOracleConnectionProperties;
begin
  Props := TSQLDBOracleConnectionProperties.Create('localhost','XE','scott','tiger');
  try
    DoQuery(Props);
  finally
    Props.Free;
  end;
end;

FPC do release the interface variable ASAP, whereas Delphi does it at the end of the method.

Please see http://synopse.info/forum/viewtopic.php?pid=9174#p9174

Offline

#14 2015-06-23 17:29:29

Cahaya
Member
Registered: 2015-06-21
Posts: 36

Re: Get Start with SynDBOracle

AB,

procedure TForm1.Button3Click(Sender: TObject);
var
  rows  : ISQLDBRows;
  vStr  : string;
  vStart,vEnd: dword;
begin
  try
    vStart := GetTickCount;
    rows := DB.Execute('select * from truck_trans;',[]);
    vStr := Utf8ToString(rows.FetchAllAsJSON(true));
    vEnd := GetTickCount - vStart;
    ShowMessage(IntToStr(vEnd));
  finally
    rows._Release;
    rows := nil;
    DB.Free;
  end;
end;

I work well after release ISQLDBRows and set it to nil;
By the way, I use the same code and measure performance executable between delphi 7 and lazarus 1.2.6, and I found lazarus code is faster around 4x. I use gettickcount to measure the timing.

Offline

#15 2015-06-23 17:32:50

Cahaya
Member
Registered: 2015-06-21
Posts: 36

Re: Get Start with SynDBOracle

How to use your optimize RTL for delphi 7 ? I failed to run SynopseRTL042004.exe
Can I just replace my dcu with SynopseRTLdcu.zip ?

Thank you again.

Offline

#16 2015-06-23 18:23:30

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: Get Start with SynDBOracle

I sent a link to the sources to you per email.

GetTickCount is not very precise - use TPrecisionTimer instead.

Offline

#17 2015-06-24 17:15:40

Cahaya
Member
Registered: 2015-06-21
Posts: 36

Re: Get Start with SynDBOracle

Thank you AB,

By the way I tried to test your SynDBOracle compare to Zeos, the result zeos for many times is faster around 5%.
Your blog said http://blog.synopse.info/post/2011/07/0 … cle-access, Syndboracle 10x faster.

I used delphi 7 to test it. All inside the same coding for 33.402 rows.

FetchallAsJson also slow compare to todataset. sad

Offline

#18 2015-06-24 20:50:04

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: Get Start with SynDBOracle

The TDataSet does not retrieve the data, until it is needed, whereas FetchAllAsJson returns all data.
Try to fetch all data from TDataSet, and I guess you would find out.
Compare using SynDBExplorer, and you would make yourself a better idea.

Zeos has been recently optimized, so is very fast.
But SynDBOracle is faster for insertion, by the ORM, using array binding, for instance, I guess.

Offline

#19 2015-06-25 01:40:30

Cahaya
Member
Registered: 2015-06-21
Posts: 36

Re: Get Start with SynDBOracle

AB,

Both between SynDB and Zeos get all data for TDataset. But of course SynDBOracle has a very nice feature, need one oci.dll only. I just guessing, both is very efficient.

Your framework is great. I'm still exploring before decide to moving my hospital apps.

Offline

#20 2015-06-25 03:42:52

Cahaya
Member
Registered: 2015-06-21
Posts: 36

Re: Get Start with SynDBOracle

AB,

After using your RTL properly, I got an amazing speed. FetchAllAsJson boost 3x faster. Impressing.
And now, SynDbOracle is faster than Zeos, for select stmt, around 14% faster.

Amazing !

Offline

#21 2015-06-25 06:36:30

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: Get Start with SynDBOracle

Also ensure you use FastMM4 under Delphi 7.

Offline

#22 2015-06-25 15:21:14

Cahaya
Member
Registered: 2015-06-21
Posts: 36

Re: Get Start with SynDBOracle

I got those speed after declare FastMM4 unit. Delphi SynDBOracle vs Lazarus SynDbOracle.
Delphi is faster now.

Great job.

By the way I'm looking an article on how ORM link to vcl. With my slow brain, I did not find any real example from your lengthy pdf. smile

Thank you anyway.

Offline

Pages: 1

Board footer

Powered by FluxBB