ODBC error with TZConnection

[iancoullie]

Hi Newbie first question posting again
Hi All Any help will be appreciated
Im running a SOA server which functions perfectly with authentication and a SQLite database. I needed to extract data from a propitiatory database. initial tests where positive.
However when I extract many records I get an out of memory error. I hope someone can advise me how to trace the source of the error. I think its in the ODBC driver for the propitiatory database. I would need to trap the error as i have to restart the server for it to continue operating. I am

using Delphi Berlin 1.1 Starter edition and a zeos TZConnection and TZReadOnlyQuery
The code and the error are listed below.

Code: Select all

function TServiceAttache.OneAttache(partNo: RawUTF8; var Desc: string):double;
var
  ta: TZConnection;
  qu: TZReadOnlyQuery;
begin
  ta := nil;
  try
    ta := TZConnection.Create(nil);
    qu := TZReadOnlyQuery.Create(nil);
    ta.AutoCommit := false;
    ta.ReadOnly := true;
    ta.Version := '7.2.1-rc';
    ta.AutoEncodeStrings := false;
    ta.UseMetadata := false;
    ta.Protocol := 'ado';

    ta.Database := 'Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=att;DBQ=G:\prophet\DataHL;CODEPAGE=1252"';
    ta.Connected := true;
    qu.Connection := ta;
    qu.SQL.Clear;
    qu.Active := false;
    qu.SQL.text := 'Select AverageCost, Description from ICBase where Code = ''' + partNo + '''';
    qu.Active := true;

    result := qu.FieldByName('AverageCost').AsFloat;
    Desc := qu.FieldByName('Description').AsString;
    qu.active := false;
    ta.Connected := false;
    ta.Free;
    qu.Free;
  except
    ta.free;
    qu.free;

  end;
end;

error

Code: Select all

20180202 02431342  +    mORMotSQLite3.TSQLRestServerDB(01B0D880).URI(POST root/Attache.OneAttache?session_signature=5EE133260025BC633E826F3F inlen=23)
20180202 02431342 auth  	mORMot.TSQLRestRoutingREST(01AC0330) admin1/1591816998 fe80::9885:a7f8:6d1d:320a%13
20180202 02431342 call  	mORMotSQLite3.TSQLRestServerDB(01B0D880) IAttache.OneAttache["LVFAEA12100001R1",""]
20180202 02431412 srvr  	mORMotSQLite3.TSQLRestServerDB(01B0D880) admin1 fe80::9885:a7f8:6d1d:320a%13 POST root/Attache.OneAttache SOA-Interface -> 200 with outlen=17 in 514555 us
20180202 02431412 ret   	mORMotSQLite3.TSQLRestServerDB(01B0D880) {"result":["",0]}
20180202 02431412  -    00.516.404
20180202 02431412  +    mORMotSQLite3.TSQLRestServerDB(01B0D880).URI(POST root/Attache.OneAttache?session_signature=5EE133260025BC658EBF3D6F inlen=14)
20180202 02431412 auth  	mORMot.TSQLRestRoutingREST(01AC0330) admin1/1591816998 fe80::9885:a7f8:6d1d:320a%13
20180202 02431412 call  	mORMotSQLite3.TSQLRestServerDB(01B0D880) IAttache.OneAttache["EFV3630",""]
20180202 02431439 EXC   	EOleException 80004005 [.NET/CLR unhandled ExternalException] [.NET/CLR unhandled SEHException] ("Unspecified error") at 0077C367  stack trace API 0056F983 0056F9AC 
20180202 02431439 EXC   	EOleException 80004005 [.NET/CLR unhandled ExternalException] [.NET/CLR unhandled SEHException] ("Unspecified error") at 0077C367  stack trace API 0056F983 0056F9AC 0040B3B8 77106D4B 77106BD7 0077C367 0077C367 00AD75A9 

00AD76DA 006C8B74 00AE4013 0063A0DB 
20180202 02431448 EXC   	EOleException 80040E14 ("[Simba][SimbaEngine ODBC Driver]Memory allocation error") at 0077A2CB  stack trace API 0056F983 0056F9AC 0040B3B8 77106D4B 77106BD7 0077A2CB 0077A2CB 0077A5BD 0077CBFB 0075FB61 0077B649 0041171A 

004104A7 00AD7E34 00AD99D9 00ACD6F4 00ACD0E2 00ACC751 00AE4099 0063A0DB 
20180202 02431448 srvr  	mORMotSQLite3.TSQLRestServerDB(01B0D880) admin1 fe80::9885:a7f8:6d1d:320a%13 POST root/Attache.OneAttache SOA-Interface -> 200 with outlen=17 in 573163 us
20180202 02431448 ret   	mORMotSQLite3.TSQLRestServerDB(01B0D880) {"result":["",0]}
20180202 02431448  -    00.574.765
20180202 02431448  +    mORMotSQLite3.TSQLRestServerDB(01B0D880).URI(POST root/Attache.OneAttache?session_signature=5EE133260025BC675784F160 inlen=15)
20180202 02431448 auth  	mORMot.TSQLRestRoutingREST(01AC0330) admin1/1591816998 fe80::9885:a7f8:6d1d:320a%13
20180202 02431448 call  	mORMotSQLite3.TSQLRestServerDB(01B0D880) IAttache.OneAttache["EFV00160",""]
20180202 02431508 EXC   	EOleException 80040E14 ("[Simba][SimbaEngine ODBC Driver]Memory allocation error") at 0077C367  stack trace API 0056F983 0056F9AC 
20180202 02431508 EXC   	EOleException 80040E14 ("[Simba][SimbaEngine ODBC Driver]Memory allocation error") at 0077C367  stack trace API 0056F983 0056F9AC 0040B3B8 77106D4B 77106BD7 0077C367 0077C367 00AD75A9 00AD76DA 006C8B74 00AE4013 0063A0DB 
20180202 02431515 EXC   	EOleException 80004005 [.NET/CLR unhandled ExternalException] [.NET/CLR unhandled SEHException] ("[Simba][SimbaEngine ODBC Driver][DRM File Library]Out of Memory") at 0077A2CB  stack trace API 0056F983 0056F9AC 0040B3B8 77106D4B 

77106BD7 0077A2CB 0077A2CB 0077A5BD 0077CBFB 0075FB61 0077B649 0041171A 004104A7 00AD7E34 00AD99D9 00ACD6F4 00ACD0E2 00ACC751 00AE4099 0063A0DB 

[marsupilami]

Hello iancoullie,

honestly I am confused: Your Code uses Zeos - yes. But the stack trace clearly shows something about you using the mORMot framework.

But maybe you have a general problem here: If you use a select that returns a lot - and I mean a real big lot - of rows, then you can get in trouble because Zeos caches all rows in memory. If it cannot get enough memory you get an out of memory exception.

But then again - your stack trace contains the following message:

"[Simba][SimbaEngine ODBC Driver][DRM File Library]Out of Memory"

Again - your stack trace has information: This error is raised as en EOleException. If all you want to do is handle this exceptin, try something like this:

Code: Select all

try
  // do your thing here
except
  on E: EOleException do begin
    if E.Message <> '[Simba][SimbaEngine ODBC Driver][DRM File Library]Out of Memory' then raise else begin
      // handle out of memory condition here
    end;
  end;
end;

But maybe if you run out of memory, it is the best approach to limit the amouont of rows that are returned to you.

Best reagrds,

Jan

[iancoullie]

Hi marsupilami, thanks for the reply

i am using it in a mormot soa server as the database only has an odbc connection for it that i could not find a odbc driver for it in mormot (perhaps i should add there is probably away but yours was easier).
I found that your component connected and retrieved the data.
The strange thing is that i am only returning 1 record as the 'partno' is unique. I do loop max 20 times for each job. i.e. for 20 'partno'
It appears to be accumulating a memory leak as only after approx 10 jobs does the error occur. I am new to programming so i will have to learn how to use fastmm to check this. I guess it must be a serious leak as i have to shut the server and restart. Thanks for the help i wiil use your exception handler once i determine what will reset the error without having to shut down the server.