Huge, large out of the ordenary Data Sets

[Rikus Jansen]

Good Day all,

This is my first post so, it may be in the incorrect space.

I am in the process of rewriting an in-house data management app from C# to Pascal using CT. The reason is that the app has to be able to run on the Linux Server that hosts the data and on Windows Desktops and the cross compile works like a bomb.

I tested with Mono and the app was as stable as Linsey Lohan, hence the rewrite. The challenge I am facing is that, the data the app works with is at the moment sitting around the 400 000 000 mark. Now in the current C# app I have a function that needs to export the data for external Geocoding purposes and checks. It is simple using a DataReader, but I find in Pascal using ZeosDB (using a SQLReadonly Dataset) it loads all the data to ram.

The data is hosted in MariaDB 10, using Centos 6.5 as OS. Does ZeosDB have a "data reader" where it does not load all the data, but I can stream the data to file as it comes in, instead of loading all to memory. I am using Zeoslib 7.2 Beta

Please note this is my first pascal project since Turbo Pascal 7 and my last pascal project was done in 1994.

Thanks for any guidance on this and be blessed

[EgonHugeist]

Hi,

Well i know about his behavior for scrollable ResultSets. Note libmysql loads the retrieved results into it's own buffer. See: http://dev.mysql.com/doc/refman/5.0/en/ ... esult.html
There is an internal switch which allows to suppress this behavior by using http://dev.mysql.com/doc/refman/5.0/en/ ... esult.html which loads only one row into a buffer.

This propably (on my side still untested) works with the TZReadOnlyQuery only in IsUnidirectional-Mode. In addition you'll need to add 'useresult=True' to the TZDataSet.Properties-List.
Note actually i've no test availble to test the behavior. This is just prepared. And i would prefere you switch to latest SVN version.

Both together means we(Zeos and MySQL) allocating space for one row only. But you can use Next as navigation only. And you can't open a second resultset while fetch isn't comleted or the dataset closed else MySQL will raise an exception: "comand out of sync! You can't run this query now"

Hope it works! Feel free to test it!

[Rikus Jansen]

Thank you for the response. As soon as I have tested these suggestions from you, I will give an update.

[Rikus Jansen]

So after doing that was suggested by EgonHugeist, an error was thrown by MySQL Client "command out of sync! You can't run this command now".

What I did is to use a "TZReadOnlyQuery" and added the ''useresult=True' to the TZReadOnlyQuery.Properties. Also made sure the IsUnidirectional property is set to true;

Sequance of events are:
Open SQLConnection object;
Set the TZReadOnlyQuery 's select text;
set Active to true;

It seems to fetch the data from MYSQL and when MYSQL seems to start pushing the data through the error is thrown.

[EgonHugeist]

Grumble,

as i sad: untested just theoretical. It might be helpfull to turn off the MetaData use in TZConncetion...

[Rikus Jansen]

Got it to work

Switching Metadata off did the trick.

here is the sample code
var
con : TZConnection;
oQry: TZReadOnlyQuery;
begin
con := TZConnection.Create(nil);
//Set your MYSQL Connection info
oQry := TZReadOnlyQuery.Create(nil);
oQry.Connection := con;
oQry.Properties.Add('useresult=True' );
oQry.IsUnidirectional := true;
con.UseMetadata:=false;

oQry.Sql.Text := 'SELECT * FROM LARGETABLE';
Con.Connect;
oQry.Open; //Instant result on table of 50 000 000 rows. Forward read only
end;