sql database ado out of memory 3

[kyriakos70]

Hello,
I have an application which connects to an sql database with ado connection and dataset. The database has blob fields when I try to load the blob field table I get an error out of memory and some times the operation continues sometimes it stops with an exception The blob field is bmp, I have two questions can I load only a part of the blob field table (even when I connect to it) so I can save memory and how can I save images and print with quick report jpg images so I can have the blob field with jpg format?

Thank you
Kyriakos

 

[kyriakos70]

Here is the details of the error, (it raises when I try to make a relationship between the master Table and the Detail which has the blob field, through the two ado datasets)

+ $13[527E409B]{adortl100.bpl} ADODB.ADODB.TCustomADODataSet.InternalSetSort (Line 5630, "adodb.pas" + 1) + $13
+ $0[51F2A2A5]{rtl100.bpl } System.System.@CheckAutoResult (Line 18061, "system.pas" + 6) + $0
+ $4[527E40E3]{adortl100.bpl} ADODB.ADODB.TCustomADODataSet.SetSort (Line 5638, "adodb.pas" + 3) + $4
+ $9[22F54725]{dclado100.bpl} ADOReg.ADOReg.TADODataSetFieldLinkProperty.SetIndexFieldNames (Line 479, "..\..\db\ADOReg.pas" + 1) + $9
+ $E[1654A709]{dcldb100.bpl} FldLinks.FldLinks.TLinkFields.Edit (Line 263, "..\..\db\FldLinks.pas" + 6) + $E
+ $9[1654A619]{dcldb100.bpl} FldLinks.FldLinks.TFieldLinkProperty.Edit (Line 225, "..\..\db\FldLinks.pas" + 1) + $9
+ $5[20C9697E]{coreide100.bpl} PropInsp.PropInsp.TPropertyInspector.PropListEditDblClick (Line 837, "PropInsp.pas" + 20) + $5
+ $A[20FDD681]{vclide100.bpl} IDEInspListBox.IDEInspListBox.TInspListBox.DoEditDblClick (Line 981, "ideinsplistbox.pas" + 2) + $A
+ $6[5205CA19]{vcl100.bpl } Controls.Controls.TWinControl.WndProc (Line 7246, "Controls.pas" + 105) + $6
+ $6[5205C1A4]{vcl100.bpl } Controls.Controls.TWinControl.MainWndProc (Line 7021, "Controls.pas" + 3) + $6
+ $0[51F60BC0]{rtl100.bpl } Classes.Classes.StdWndProc (Line 11572, "classes.pas" + 8) + $0
+ $6A[7E398731]{USER32.dll } GetDC + $6A
+ $14A[7E398811]{USER32.dll } GetDC + $14A
+ $122[7E3989C8]{USER32.dll } GetWindowLongW + $122
+ $A[7E398A0B]{USER32.dll } DispatchMessageW + $A

 

[DjangMan]

Where is the code that you're using to load the bitmap from the blob?

 

[kyriakos70]

Well, it happens when I try to establish the relationship between the two tables through the 2 ado datasets, when there is no relationship the dataset becomes active but when I try to create the relationship with the master's table ado dataset I get an error "out of memory". I have no code everything I do it by components.

 

[lespaul]

you realize that Access "relationships" are only there to enforce referential integrity and to allow Access to "help" you with your queries by automatically...why are you trying to create a relationship through a delphi component? And how can you be using Delphi and not have any code?

Leslie

 

[DjangMan]

I think he's using the component properties editor to make a master/detail relationship.

What fields are you connecting together?

 

[kyriakos70]

I am connecting the primary key of the master and the foreign key of the detail table, yes Djangman that is what I want to do, but I found another thing the detail table which has a primary kay a foreign key and a blob field is 500 MB , has too many pictures in it has to do something with it? can I make a relationship any other way not to load all the pics of the table?

 

[whosrdaddy]

Delphi will try to load the table in memory, hence the error.

You will need to construct your queries manually (and only load the blob field when needed)

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[kyriakos70]

I still get the out of memory error after I navigate to 4-5 records, I have manually created the relationship with an adoquery (to move with the master and put it in a onclick event of a button record), my question is is there a buffer which holds previous records in memory and needs to be free before another record is called, or do I have to destroy the adoquery every time I don't need it?


Kyriakos

 

[DjangMan]

All of that stuff should be taken care of for you automatically.

What version of Delphi are you using? Tell us about the tables.

You could also try using an unbound control for the blob field and manually load the blob thereby eliminating any memory issues.

At the top of the trace you supplied it looks like was crashing on a call to an internal sort routine. What field were you sorting on?

 

[whosrdaddy]

I create my adoqueries on the fly.

ie:

create adoquery
do some query stuff (load data into controls for example)
destroy adoquery (use FreeAndNil function).

I wrote an interface around TADOQuery for this purpose:

unit u_db_utilities;

interface

uses
  Windows,
  SysUtils,
  Classes,
  Controls,
  ADODB,
  DB;

const ADOConnStrTemplate = 'Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=%s;Data Source=%s;User ID=%s;Password=%s';
      MySQL5StrTemplate = 'Provider=MSDASQL.1;Persist Security Info=True;User ID=%0:s;Password=%1:s;Extended Properties="DRIVER={MySQL ODBC 5.1 Driver};SERVER=%2:s;PORT=3306;DATABASE=%3:s;USER=%0:s;PASSWORD=%1:s;OPTION=1048579"';
      MySQL3StrTemplate = 'Provider=MSDASQL.1;Persist Security Info=True;User ID=%0:s;Password=%1:s;Extended Properties="DRIVER={MySQL ODBC 3.51 Driver};SERVER=%2:s;PORT=3306;DATABASE=%3:s;USER=%0:s;PASSWORD=%1:s;OPTION=1048579"';

type
  TDBtype = (dbMsSQL, dbMySQL3, dbMySQL5);

const
  TDBTypeStrings : array[dbMsSQL..dbMySQL5] of string[10] = ('MsSQL', 'MySQL3', 'MySQL5');

type
  TDateTimeType = (dtDate, dtTime, dtDateTime);

  IIADOQuery = interface
  ['{344B711B-FBB2-473B-A864-F5D0ABB7A8F8}']
    procedure ParseQuery(SQL : String);
    procedure SelectParsedQuery;
    procedure UpdateParsedQuery;
    procedure SelectQuery(SQL : String);
    procedure UpdateQuery(SQL : string);
    procedure SelectQueryEx(SQL : String; var Flag : Boolean);
    procedure UpdateQueryEx(SQL : string; var Flag : Boolean);
    procedure UpdateParsedQueryEx(var Flag : Boolean);
    procedure SelectParsedQueryEx(var Flag : Boolean);
    procedure Parameter(ParameterName : String; Value : String); overload;
    procedure Parameter(ParameterName : String; Value : Integer); overload;
    procedure Parameter(ParameterName : String; Value : Boolean); overload;
    procedure Parameter(ParameterName : String; Value : Real); overload;
    procedure Parameter(ParameterName : String; Value : String; CurrencySymbol: String); overload;
    procedure Parameter(ParameterName : String; Value : String; DateTimeType : TDateTimeType); overload;
    function AsString(FieldName : String) : String;
    function AsInteger(FieldName : String) : Integer;
    function AsFloat(FieldName : String) : Double;
    function AsDateTime(Fieldname : String) : TDateTime;
    function EOF : Boolean;
    function RecordCount : Integer;
    procedure Next;
    procedure Previous;
    procedure Edit;
    procedure Post;
    function Qry : TADOQuery;
  end;

  TIADOQuery = class(TInterfacedObject, IIADOQuery)
  private
  public
    ADOQuery : TADOQuery;
    procedure ParseQuery(SQL : String);
    procedure SelectParsedQuery;
    procedure UpdateParsedQuery;
    procedure SelectQuery(SQL : String);
    procedure UpdateQuery(SQL : string);
    procedure SelectQueryEx(SQL : String; var Flag : Boolean);
    procedure UpdateQueryEx(SQL : string; var Flag : Boolean);
    procedure UpdateParsedQueryEx(var Flag : Boolean);
    procedure SelectParsedQueryEx(var Flag : Boolean);
    procedure Parameter(ParameterName : String; Value : String); overload;
    procedure Parameter(ParameterName : String; Value : Integer); overload;
    procedure Parameter(ParameterName : String; Value : Boolean); overload;
    procedure Parameter(ParameterName : String; Value : Real); overload;
    procedure Parameter(ParameterName : String; Value : String; CurrencySymbol: String); overload;
    procedure Parameter(ParameterName : String; Value : String; DateTimeType : TDateTimeType); overload;
    procedure Parameter(ParameterName: String; Value: TDate); overload;
    function AsString(FieldName : String) : String;
    function AsInteger(FieldName : String) : Integer;
    function AsFloat(FieldName : String) : Double;
    function AsDateTime(Fieldname : String) : TDateTime;
    function EOF : Boolean;
    function RecordCount : Integer;
    procedure Next;
    procedure Previous;
    procedure Edit;
    procedure Post;
    function Qry : TADOQuery;
    constructor Create(ADOConnection : TADOConnection); overload;
    constructor Create(ADOConnection : TADOConnection; ctDynamic : Boolean); overload;
    destructor Destroy; override;
  end;

function CreateADOConnection(DBType : TDBType; SQLServer, SQLUsername, SQLPassword, Catalog : String) : TADOConnection;
function CreateConnectionString(DBType : TDBType; SQLServer, SQLUsername, SQLPassword, Catalog : String) : String;
function StringToDBType(db : string) : TDBtype;

implementation

// general purpose routines
function StringToDBType(db : string) : TDBtype;

var dbtype : TDBtype;

begin
 for dbtype := Low(TDBTypeStrings) to High(TDBTypeStrings) do
  begin
   if AnsiSameText(TDBTypeStrings[dbtype], db) then
    begin
     Result := dbtype;
     Exit;
    end;
  end;
 raise Exception.CreateFmt('Not database type found for "%s"', [db]); 
end;

function CreateConnectionString(DBType : TDBType; SQLServer, SQLUsername, SQLPassword, Catalog : String) : String;

begin
 case DBType of
  dbMsSQL: Result := Format(ADOConnStrTemplate,[Catalog, SQLServer, SQLUsername, SQLPassword]);
  dbMySQL3: Result := Format(MySQL3StrTemplate,[SQLUsername, SQLPassword, SQLServer, Catalog]);
  dbMySQL5: Result := Format(MySQL5StrTemplate,[SQLUsername, SQLPassword, SQLServer, Catalog]);
 end;
end;

function CreateADOConnection(DBType : TDBType; SQLServer, SQLUsername, SQLPassword, Catalog : String) : TADOConnection;
begin
 Result := TADOConnection.Create(nil);
 Result.ConnectionString := CreateConnectionString(DBType, SQLServer, SQLUsername, SQLPassword, Catalog);
end;


{ TInterfacedADO }

constructor TIADOQuery.Create(ADOConnection : TADOConnection);
begin
 if not Assigned(ADOQuery) then
  ADOQuery := TADOQuery.Create(nil);
 ADOQuery.Connection := ADOConnection;
// ADOQuery.CursorLocation := clUseServer;
// ADOQuery.CursorType := ctDynamic;
 ADOQuery.ParamCheck := False;
end;

constructor TIADOQuery.Create(ADOConnection: TADOConnection; ctDynamic: Boolean);
begin
 Create(ADOConnection);
end;

destructor TIADOQuery.Destroy;
begin
 FreeAndNil(ADOQuery);
 inherited;
end;

procedure TIADOQuery.ParseQuery(SQL: String);
begin
 ADOQuery.ParamCheck := True;
 ADOQuery.SQL.Text := SQL;
end;

function TIADOQuery.Qry: TADOQuery;
begin
 Result := ADOQuery;
end;

function TIADOQuery.RecordCount: Integer;
begin
 Result := ADOQuery.RecordCount;
end;

procedure TIADOQuery.SelectParsedQuery;
begin
 ADOQuery.Active := False;
 ADOQuery.Active := True;
end;

procedure TIADOQuery.SelectQuery(SQL: String);
begin
 ADOQuery.Active := False;
 ADOQuery.ParamCheck := False;
 ADOQuery.SQL.Text := SQL;
 ADOQuery.Active := True;
end;

procedure TIADOQuery.SelectQueryEx(SQL: String; var Flag: Boolean);
begin
 try
  SelectQuery(SQL);
 except
  Flag := False;
  raise;
 end;
end;

procedure TIADOQuery.UpdateParsedQuery;
begin
 ADOQuery.Active := False;
 ADOQuery.ExecSQL;
end;

procedure TIADOQuery.SelectParsedQueryEx(var Flag: Boolean);
begin
 try
  SelectParsedQuery;
 except
  Flag := False;
  raise;
 end;
end;

procedure TIADOQuery.UpdateParsedQueryEx(var Flag : Boolean);
begin
 try
  UpdateParsedQuery;
 except
  Flag := False;
  raise;
 end;
end;

procedure TIADOQuery.UpdateQuery(SQL: string);
begin
 ADOQuery.Active := False;
 ADOQuery.ParamCheck := False;
 ADOQuery.SQL.Text := SQL;
 ADOQuery.ExecSQL;
end;

procedure TIADOQuery.UpdateQueryEx(SQL: string; var Flag: Boolean);
begin
 try
  UpdateQuery(SQL);
 except
  Flag := False;
  raise;
 end;
end;

procedure TIADOQuery.Parameter(ParameterName, Value: String);
begin
 ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
 ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftString;
end;

procedure TIADOQuery.Parameter(ParameterName: String; Value: Integer);
begin
 ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
 ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftInteger;
end;

procedure TIADOQuery.Parameter(ParameterName: String; Value: Boolean);
begin
 ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
 ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftBoolean;
end;

procedure TIADOQuery.Parameter(ParameterName: String; Value: Real);
begin
 ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
 ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftFloat;
end;

procedure TIADOQuery.Parameter(ParameterName: String; Value: TDate);
begin
 ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
 ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftDate;
end;


procedure TIADOQuery.Parameter(ParameterName, Value, CurrencySymbol: String);

var ConvertedValue : Real;
    Ps : Integer;

begin
 Ps := Pos(CurrencySymbol, Value);
 if Ps > 0 then
  Value := Copy(Value, 1, Ps - 1);
 ConvertedValue := StrToFloat(Value);
 ADOQuery.Parameters.ParamByName(ParameterName).Value := ConvertedValue;
 ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftFloat;
end;

procedure TIADOQuery.Parameter(ParameterName, Value: String; DateTimeType: TDateTimeType);
begin
 case DateTimeType of
  dtDate : 
 end;
end;

function TIADOQuery.AsDateTime(Fieldname: String): TDateTime;
begin
 Result := ADOQuery.FieldByName(FieldName).AsDateTime;
end;

function TIADOQuery.AsFloat(FieldName: String): Double;
begin
 Result := ADOQuery.FieldByName(FieldName).AsFloat;
end;

function TIADOQuery.AsInteger(FieldName: String): Integer;
begin
 Result := ADOQuery.FieldByName(FieldName).AsInteger;
end;

function TIADOQuery.AsString(FieldName: String): String;
begin
 Result := ADOQuery.FieldByName(FieldName).AsString;
end;

procedure TIADOQuery.Edit;
begin
 Qry.Edit;
end;

function TIADOQuery.EOF: Boolean;
begin
 Result := Qry.Eof;
end;

procedure TIADOQuery.Next;
begin
 Qry.Next;
end;

procedure TIADOQuery.Post;
begin
 Qry.Post;
end;

procedure TIADOQuery.Previous;
begin
 Qry.Prior;
end;

end.

How to use it:

uses ..., u_db_utilities;

...

procedure DoSomeQuery;

var Qry : IIADOQuery;

begin
 Qry := TIADOQuery.Create(MyDBConnection); // MyDBConnection is your TADOConnection
 // delete/insert/update statements
 Qry.UpdateQuery('DELETE FROM DaTable');
 // parameters
 Qry.ParseQuery('SELECT name FROM DaTable WHERE id=:id';
 Qry.Parameter('id', 10);
 Qry.SelectParsedQuery;
 ShowMessage(Qry.AsString('name'));
end;

I use this unit for a while now and you get clean/small procedures containing your queries. As you may have noticed
I don't do FreeAndNil(Qry), the reason behind this is that I am using an interface and interfaces have the benefit that they will destroy themselves when the variable goes out of scope (ie is no longer referenced).

Cheers,

/Daddy




-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[DjangMan]

Awesome - thanks Daddy!

 

[kyriakos70]

Hello,
I am still getting the out of memory error, even I have used the adoquery with this sql string :

select * from scan inner join Table1 on scan.id=Table1.id where scan.id like '+quotedstr(edit1.text)

and have in the edit box the value of the primary key of the Table1 (master table), it still loads all the table scan (500-600 Mb) and I get the error out of memory, is there any other sql string to perform a better performance of memory?

Thank you
Kyriakos

 

[DjangMan]

Don't use select *. Select just the fields that you need.

You could also use something like:

select top 5 * from scan inner join Table1 on scan.id=Table1.id where scan.id like '+quotedstr(edit1.text)