Skip to Content
Last modified by Carlijn Kokkeler on 2024/09/03 12:34
Show last authors
1 {{container}}{{container layoutStyle="columns"}}(((
2 In this microlearning, we will explore how to use the H2 database within eMagiz to temporarily store information that can be accessed later or used in another process. While the H2 database is commonly employed for ensuring message delivery between the entry and onramp in messaging, it also has several other valuable applications. These include storing tokens for rate-limited APIs, tracking process execution points, and enriching messages with stored data. This microlearning will guide you through setting up, writing to, and reading from your own H2 database within eMagiz.
3
4 Should you have any questions, please contact [[academy@emagiz.com>>mailto:academy@emagiz.com]].
5
6 == 1. Prerequisites ==
7
8 * Advanced knowledge of the eMagiz platform
9
10 == 2. Key concepts ==
11
12 This microlearning centers around how you can use the H2 database to store information temporarily so you can use it at another time or maybe even in another process.
13 With the H2 database, we mean: A component in eMagiz that can easily create a simple database on runtime level that you can access from your flow
14
15 As you know the H2 database within eMagiz is used to store messages to ensure guaranteed delivery between the entry and the onramp in messaging.
16
17 However, there are also other use cases in which an H2 database of your own making will be beneficial:
18
19 * Storing tokens because of rate-limiting of an external API
20 * Remembering where eMagiz stopped the last time when a process was executed (eMagiz remembers this to create deltas)
21 * Enriching your message with data from the H2 database
22
23 == 3. H2 Database for other applications ==
24
25 As you know the H2 database within eMagiz is used to store messages to ensure guaranteed delivery between the entry and the onramp in messaging.
26
27 However, there are also other use cases in which an H2 database of your own making will be beneficial:
28
29 * Storing tokens because of rate-limiting of an external API
30 * Remembering where eMagiz stopped the last time when a process was executed (eMagiz remembers this to create deltas)
31 * Enriching your message with data from the H2 database
32
33 Be sure to validate with others whether using the H2 database is the only option available to store this data. Consider other patterns and other approaches that might provide a better and more robust solution.
34
35 === 3.1 Setting up the table within the H2 database ===
36
37 Regardless of which use case you have you need to implement several steps to make it work in eMagiz. The first part is setting up your table within the H2 database and ensure that eMagiz will create an H2 database for you. To do so you need the following:
38
39 * A SQL resource that defines the table structure
40 * The support object called JDBC H2 Connection Pool
41 * The support object called JDBC initialize database
42
43 A simple SQL statement that defines a table that holds the access token could look something like this.
44
45 {{code language="sql"}}
46 CREATE TABLE IF NOT EXISTS ACCESSTOKENS
47 (
48 CUSTOMERID varchar(255) PRIMARY KEY,
49 EXPIRES varchar(255),
50 ACCESSTOKEN varchar(255),
51 ACCESSID varchar(255)
52 );
53 INSERT INTO ACCESSTOKENS
54 SELECT 'NEW', '0', 'NEW', 'NEW'
55 WHERE NOT EXISTS (SELECT * FROM ACCESSTOKENS);
56 {{/code}}
57
58 After you are satisfied with your SQL statement you can save the file with the .sql extension and upload it to eMagiz and link it to your flow.
59
60 The next step will be to add the support object called JDBC H2 Connection Pool to the flow
61
62 [[image:Main.Images.Microlearning.WebHome@expert-solution-architecture-h2-database-for-other-applications--jdbc-h2-search.png]]
63
64 When you open the component you will see that you need to fill in three variables
65
66 [[image:Main.Images.Microlearning.WebHome@expert-solution-architecture-h2-database-for-other-applications--jdbc-h2-component-empty.png]]
67
68 The URL should look similar to the one shown in the help text. The only distinction is that the actual name (the part after h2/) needs to be changed to indicate that this H2 database is only meant to store something related to your use case. You can determine the username and password yourselves. A best practice is to refer to these values via a property in this component.
69
70 [[image:Main.Images.Microlearning.WebHome@expert-solution-architecture-h2-database-for-other-applications--jdbc-h2-component-filled.png]]
71
72 The third and final step to set up the table within the H2 database is to add the support object called JDBC initialize database.
73
74 [[image:Main.Images.Microlearning.WebHome@expert-solution-architecture-h2-database-for-other-applications--jdbc-h2-initialize-empty.png]]
75
76 There are two things we need to do to configure the component correctly. First, we need to link this component to the support object we have just created. Second, we need to refer to the SQL script that we have uploaded to our flow. To do so press New and fill in the Location. Remember the location starts with resources/ and should hold the unique reference number given by eMagiz and the extension of the resource.
77
78 If you fail to do so eMagiz will not be able to locate the resource. Having done these things should result in something like this:
79
80 [[image:Main.Images.Microlearning.WebHome@expert-solution-architecture-h2-database-for-other-applications--jdbc-h2-initialize-filled.png]]
81
82 === 3.3 Write data to the table ===
83
84 At some point in your process, based on your use case, you will need to write new data to the table to either update an existing row or to create a new row. To do so you can use a JDBC outbound channel adapter.
85
86 For the component to properly function we need to refer to our data source and we need a SQL query that can be executed on this table. See below for an example of such an SQL Query
87
88 {{code language="sql"}}
89 MERGE INTO MESSAGEIDS (CUSTOMERID,EXPIRES,ACCESSTOKEN,ACCESSID) KEY(CUSTOMERID) VALUES (:headers[customer],:headers[expires],:headers[access_key],:headers[accessKeyId]);
90 {{/code}}
91
92 When you have correctly filled in the details of the component it should look similar to below
93
94 [[image:Main.Images.Microlearning.WebHome@expert-solution-architecture-h2-database-for-other-applications--jdbc-write-action.png]]
95
96 === 3.4 Read data from table ===
97
98 Now that we have created a table in the database and we have written data to that table we now need to think about a way in which we can retrieve the data from the table in question. As you can imagine the next time you need the information from the table you will have to retrieve the information. One way of doing that is to use the XSLT Extension Gateway functionality. For more information on how to apply that you can take a look at the following microlearning: [XSLT Extension Gateway](advanced-data-handling-xslt-extension-gateway.md)
99
100 == 4. Key takeaways ==
101
102 * As you know the H2 database within eMagiz is used to store messages to ensure guaranteed delivery between the entry and the onramp in messaging.
103 * However, there are also other use cases in which an H2 database of your own making will be beneficial:
104 * Storing tokens because of rate-limiting of an external API
105 * Remembering where eMagiz stopped the last time when a process was executed (eMagiz remembers this to create deltas)
106 * Enriching your message with data from the H2 database
107 * In the flow designer you need to ensure that the database and table are created, that you can write and read data
108
109 == 5. Suggested Additional Readings ==
110
111 * [[Intermediate (Menu)>>doc:Main.eMagiz Academy.Microlearnings.Intermediate Level.WebHome||target="blank"]]
112 ** [[Solution Architecture (Navigation)>>doc:Main.eMagiz Academy.Microlearnings.Intermediate Level.Solution Architecture.WebHome||target="blank"]]
113 *** [[H2 Database, Function (Explanation)>>doc:Main.eMagiz Academy.Microlearnings.Intermediate Level.Solution Architecture.intermediate-solution-architecture-function-of-h2-database||target="blank"]]
114 ** [[eMagiz Runtime Management (Navigation)>>doc:Main.eMagiz Academy.Microlearnings.Intermediate Level.eMagiz Runtime Management.WebHome||target="blank"]]
115 *** [[Reset H2 Database (Explanation)>>doc:Main.eMagiz Academy.Microlearnings.Intermediate Level.eMagiz Runtime Management.intermediate-runtime-management-reset-h2.WebHome||target="blank"]]
116 ** [[Database connectivity (Navigation)>>doc:Main.eMagiz Academy.Microlearnings.Intermediate Level.Database connectivity.WebHome||target="blank"]]
117 * [[Expert (Menu)>>doc:Main.eMagiz Academy.Microlearnings.Expert Level.WebHome||target="blank"]]
118 ** [[Database connectivity (Navigation)>>doc:Main.eMagiz Academy.Microlearnings.Expert Level.Database Connectivity.WebHome||target="blank"]]
119 * [[H2 Database (Search Result)>>url:https://docs.emagiz.com/bin/view/Main/Search?sort=score&sortOrder=desc&highlight=true&facet=true&r=1&f_space_facet=0%2FMain.&f_type=DOCUMENT&f_locale=en&f_locale=&f_locale=en&text=h2+database||target="blank"]]
120
121
122 )))((({{toc/}}))){{/container}}{{/container}}