Hello,
I recently helped a customer solve a problem where they need to read a few CSV files and filter certain rows so they go to different people via email with an Excel multi-sheet spreadsheet. The issue was there were close 60 different filters and we didn’t want to statically define each filter within a Router snap.
Instead we used H2 in-memory database to temporarily store and filter the data using SQL queries to generate each of the 60 Excel files then email them to different end-users. To use H2 with SnapLogic you need to use the Generic JDBC snap pack and you will need to download the H2 JAR from their website. Within the zip file under the bin folder there is a JAR file called something like h2-1.4.193.jar.
When you setup the JDBC Account it will look something like this:
You will need to upload that jar file to the Manager usually under the Global Shared and reference it within the JDBC Account you setup.
JDBC Driver Class = org.h2.Driver
JDBC URL = jdbc:h2:mem:snaplogic;DB_CLOSE_DELAY=-1;LOG=0;CACHE_SIZE=65536;UNDO_LOG=0;MAX_QUERY_TIMEOUT=0;AUTO_RECONNECT=TRUE;TRACE_LEVEL_FILE=0;LOCK_MODE=0
For more information on the JDBC URL settings refer to this website.
I also created a short video recording stepping through how I used the H2 database to solve my customer problem which you can watch here:
Video URL: https://vimeo.com/207395968
Video Password: snaplogic
I’ve attached the pipelines I created in the video below:
Unknown and Open Role Notifications (child)_2017_03_07.slp (23.8 KB)
Unknown and Open Role Notifications (parent)_2017_03_07.slp (50.1 KB)
