SQL Server Table to CSV file

[Boss4644]

i want to convert table data(1 laks records) to csv file. if i select the 1 laks data from SQL server through mirth, it takes more then 10mins and then return java heap space error. i already set max java heap size as 4GB. still i got the same error. what is the best way to read large data from sql server and convert into csv file using mirth

[pacmano1]

The "best" way is not to use Mirth at all for very large datasets. Instead use native database tools.

However you should not run out of heap space depending on how you wrote your channel. You shared no code so it is hard to help.

While this is not the Mirth forums, please read https://forums.mirthproject.io/articles/177082-forum-etiquette, it is helpful for understanding how to post questions and ask for help here.

[Boss4644]

Any limitation for SQL reader? what is the maximum value we can read without delay? @pacmano1 and @jonbartels

[Boss4644]

try{
var dbConn = DatabaseConnectionFactory.createDatabaseConnection('com.microsoft.sqlserver.jdbc.SQLServerDriver',DbInsatnce,DbUser,DbPassword);
var sqlquery="select top 50000 * from mirth for xml path('row'), root('delimiter')";

var Result=dbConn.executeCachedQuery(sqlquery);
dbConn.close();
var i=0;
while(Result.next()){
	xmlRes += Result.getString(1);
	i++;
}
logger.info('LoopCount: '+ i);
var csv = SerializerFactory.getSerializer('DELIMITED').fromXML(xmlRes);
msg=csv;

}
catch(ex)
{
logger.info("sql ex: "+ ex.message);
}

This the code for getting values from DB, but its too slow and then throws java heap space error. Any suggestion to optimize?

Thanks in Advance.

[jonbartels]

There isn't a maximum value for MC. This is more dependent on your query an the size of the data you're returning. Even if you read this data using a different program you still have to read the data and put it on heap.

Your SQL query is simple enough. Use a plain SQL reader (not javascript) and then select the "aggregate results" option. That will create one message in MC with all the rows returned from the DB.

1 laks rows isn't that many rows. What is the content of each row? Are these rows a few simple columns or are they a big data set?

[Boss4644]

table data are simple string only, but i want the change the table dynamically, in SQL reader there is no option for dynamic query

[jonbartels]

First - Does SQL Server support something like a SELECT statement that will return a CSV? If it does, use that for your query. Your channel wants a CSV. If the DB can give you a CSV, let the DB do the work!

If getting a CSV from the DB via SQL isn't possible - I think you need to break your channel into smaller pieces.

1. Run the large SELECT and get the rows back. Serialize those to JSON (example below), JSON usually uses less heap than XML.
2. Add a destination to change the JSON into delimited XML
3. Now you have a CSV the way MC usually handles CSVs, do your other channel logic.

try{
var dbConn = DatabaseConnectionFactory.createDatabaseConnection('com.microsoft.sqlserver.jdbc.SQLServerDriver',DbInsatnce,DbUser,DbPassword);

var sqlquery="select top 50000 * from mirth for xml path('row'), root('delimiter')";

var Result=dbConn.executeCachedQuery(sqlquery);
dbConn.close();
var i=0;
while(Result.next()){
	xmlRes += Result.getString(1);
	i++;
}

//you want to change your datatype to JSON
//JSON typically uses less memory than XML
msg=JSON.stringify(xmlRes);

}
catch(ex)
{
logger.info("sql ex: "+ ex.message);
}

[jonbartels]

Are you querying MC using the "aggregate results" option?

[Boss4644]

Not using aggregate results, i just fetch data from single table with simple select query, That table contain 1 laks data

[pacmano1]

Please stop cross posting to the forums. It seems like you have put the same question over there and here a few times.

If you do cross post please reference the posts via links in both places.