Some of our customers demand dumps of their data on every moment.
Basically this isn’t so hard as you can loop through the tables of your server and get a dataset filtered by ‘owner_id’ via a query.
So far no problem.
Then I use ```
$dataset.getAsText(‘,’,‘\n’,‘"’,true);
This is no problem if the dataset is quite small, but it looks like there's a restriction on size.
If it's large the function just fails without any notification and my method just ends there.
Anyone a clue what the restrictions are?
mboegem:
Some of our customers demand dumps of their data on every moment.
Basically this isn’t so hard as you can loop through the tables of your server and get a dataset filtered by ‘owner_id’ via a query.
So far no problem.
Then I use ```
$dataset.getAsText(‘,’,‘\n’,‘"’,true);
This is no problem if the dataset is quite small, but it looks like there's a restriction on size.
If it's large the function just fails without any notification and my method just ends there.
Anyone a clue what the restrictions are?
Marc we have very similar issues and have discussed it before (when using .writeXML and .write TXT). Originally Johan asked me to post a case but when I did I got the response that they will not fix this issue. (viewtopic.php?f=22&t=13563)
In our case Servoy simply runs out of memory and crash’s. We have been working on this issue again today strangely enough so we too would really like to see some resolution on it. 1k records seems fine but we have a dump of 45k we really need to be able to handle to XLS or CSV.
Thomas suggested using Jasper but for the moment we are still reviewing how this might work. It seems like an essential to be able to output data to CSV at the very least.
The restriction is your RAM size (assigned to the client).
When you use getDataSetByQuery you will in fact have the whole resultset in RAM (or at least it tries to).
Also when your client runs out of RAM then the method that was running simply stops running. No errors, no nothing.
It’s better to use a (dynamicaly created?) form and let Servoy handle the selects.
@Robert: so far the query > dataset wasn’t the issue yet, but the conversion to txt which just fails…
I think basically that part is subject of the same RAM restriction as well.
The problem really is: there just seems no alternative to get the data out of the database via Servoy including columnnames.
Just a bunch of data (easy to do via sybase iSQL) isn’t much use to me or my customers, I really need the column name to be included…
@Robert: so far the query > dataset wasn’t the issue yet, but the conversion to txt which just fails…
I think basically that part is subject of the same RAM restriction as well.
The problem really is: there just seems no alternative to get the data out of the database via Servoy including columnnames.
Just a bunch of data (easy to do via sybase iSQL) isn’t much use to me or my customers, I really need the column name to be included…
Looks like McCourt has come up with a master stroke, exporting and importing like a mad man - seemingly no limits to the amount of data and very fast too! But I know he wants to tidy it up before he posts anything so hopefully tomorrow or Monday I’ll persuade him to share it!
Kahuna:
Looks like McCourt has come up with a master stroke, exporting and importing like a mad man - seemingly no limits to the amount of data and very fast too! But I know he wants to tidy it up before he posts anything so hopefully tomorrow or Monday I’ll persuade him to share it!
very anxious about what this will be… any directions yet?
Harjo:
you need to write the records, line by line, into a file.
I will search tommorow for the code, we use this also, with creating (very large) rtf-files.
I was hoping to find the tool to do this.
Even thought exporting the whole txt file via raw sql plugin to the server, download it to the client and add the header row to it.
Small issue though: there doesn’t seem a usable command to do this with sybase via the plugin…
We have made use of the following code to loop through a dataset and write it line by line similar to the method suggested by Harjo.
I hope this helps
var dataset = databaseManager.getDataSetByQuery("tci_test","SELECT * FROM fabric_condition", null, -1)
var rows = dataset.getMaxRowIndex()
var cols = dataset.getMaxColumnIndex()
var csvdir = "c:/mac"
plugins.file.createFolder(csvdir)
csv_file = csvdir + "/fabric_condition.csv"
var write = new java.io.FileWriter(csv_file, false)
var rowinfo = null;
var colname = null;
for (var w=1; w<=cols; w++)
{
if (colname == null)
{
colname = dataset.getColumnName(w)
}
else
{
colname = colname + "," + dataset.getColumnName(w)
}
}
colname = colname + "\n"
write.write(colname)
for (var b=1; b<=rows; b++)
{
rowinfo = null;
for (var c=1; c<=cols; c++)
{
if (rowinfo == null)
{
rowinfo = dataset.getValue(b,c)
}
else
{
rowinfo = rowinfo + "," + dataset.getValue(b,c)
}
}
rowinfo = rowinfo + "\n"
application.output(b)
write.write(rowinfo)
}
write.flush
write.close
This uses a java filewriter, and then loops through each row column by column and writes to the csv file with comma delimited fields and \n delimeted rows. On 20,000 plus records it works fine, nice and quick. You can also choose to write data to the end of an existing file by setting the boolean to true.
var write = new java.io.FileWriter(csv_file, true)
This morning I did use the code Harjo referenced as well and now it runs like crazy!
Even had some 75Mb+ files and finished within 2 minutes…
Finally happy…
Just remember to use brackets on the close statement
write.close()
as it seems the last data flush to the file happens on close, i have been pulling my hair out trying to figure out why the last 8 records weren’t being written to disk.
Zuke:
Just remember to use brackets on the close statement
write.close()
as it seems the last data flush to the file happens on close, i have been pulling my hair out trying to figure out why the last 8 records weren’t being written to disk.
thnkx, I did notice the remark of Jan Blok in the post Harjo referenced, so I did include that.
I flush every record as well, as I don’t know if only a flush in the end will affect the use of memory in a negative way as well.
Performance-wise Servoy doesn’t seem to bother.
// log_bw is a global (type media)
var fw = new Packages.java.io.FileWriter('/tmp/xyz.log');
globals.log_bw = new Packages.java.io.BufferedWriter(fw);
globals.log_bw.write('my important log line');
// you might want to add newlines
globals.log_bw.newLine();
// and flush it to disk in the mean time
globals.log_bw.flush();
// IMPORTANT!!!
dont forget to close() the log_bw in the solutionclose method
Zuke:
Just remember to use brackets on the close statement
write.close()
as it seems the last data flush to the file happens on close, i have been pulling my hair out trying to figure out why the last 8 records weren’t being written to disk.
Yes… With java you need to remember the Spiderman motto: “With great power, comes great responsability”
This morning I did use the code Harjo referenced as well and now it runs like crazy!
Even had some 75Mb+ files and finished within 2 minutes…
Finally happy…
Marc, would you share your whole export-streaming routine here?
for instance, how are you handling blobs?
Harjo:
Marc, would you share your whole export-streaming routine here?
Will look it up & post it here…
Harjo:
for instance, how are you handling blobs?
We don’t store blobs other than some customers logo’s in the database.
As this is the least important data to a customer (I don’t think his ‘logo masters’ are in our DB) I just don’t.
In fact I just output only the columns with a length property <100000, so this will leave out blobs anyway.
Harjo:
Marc, would you share your whole export-streaming routine here?
As promised:
function exportAllData()
{
// Select a folder to export
var $folder = plugins.file.showDirectorySelectDialog();
if($folder == null) return;
// Convert path to universal path
var $subst = utils.stringMiddle(application.getOSName(), 1, 7) == "Windows" ? '\\' : ':';
$folder = utils.stringReplace($folder, $subst, '/') + '/';
// Declare vars
var $server = '<YOUR SERVER NAME>';
var $tables = databaseManager.getTableNames($server);
var $count;
var $query;
var $dataset;
var $table, $cols;
var $file, $fileName, $data, $result, $javaFW;
// Build an array with tablenames which you DO NOT want to export
var $except = new Array();
$except[$except.length] = 'dummy';
$except[$except.length] = 'gen_log';
$except[$except.length] = 'mastertable';
$except[$except.length] = 'owner'; // this is my 'tenant' table
// Filter 'all tables' against the exceptions
$tables = $tables.filter(function(item){ return $except.indexOf(item) == -1 });
// Loop through the tables you DO want to export
for(var i = 0; i < $tables.length; i++)
{
$table = databaseManager.getTable($server, $tables[i]);
$cols = $table.getColumnNames();
// exporting a table can only be done when the table has 'tenant-specific' data
// in my case this means 'k_owner_id' should exist as a column
if($cols.indexOf('k_owner_id') > -1)
{
// now you can apply a filter to your columns so you get only the cols you DO want to export
$cols = $cols.filter(function(item){ return !(/password/.test(item)); });
$cols = $cols.filter(function(item){ return !(/k_owner_id/.test(item)); });
$cols = $cols.filter(function(item) { return $table.getColumn(item).getLength() < 100000; });
$cols.sort();
// build the query string
$query = 'SELECT ' + $cols.join(',') + ' FROM ' + $tables[i] + ' WHERE k_owner_id = ?'
// get your dataset by query
$dataset = databaseManager.getDataSetByQuery($server, $query, ['<YOUR OWNER ID>'], -1);
$count = $dataset.getMaxRowIndex();
// export the data when there's more then 0 rows returned
if($count > 0)
{
$fileName = $tables[i] + '_' + $count + 'recs.csv';
$javaFW = new Packages.java.io.FileWriter($folder + $fileName);
// ioData is a media type global var
globals.ioData = new Packages.java.io.BufferedWriter($javaFW);
globals.ioData.write('"' + $cols.join('","') + '"');
globals.ioData.newLine();
for(var r = 1; r <= $count; r++)
{
globals.ioData.write('"' + $dataset.getRowAsArray(r).join('","') + '"');
globals.ioData.newLine();
globals.ioData.flush();
}
globals.ioData.close();
}
}
}
}