Skip to Content
Last modified by Martijn Woudstra on 2023/04/13 15:33
Show last authors
1 {{container}}{{container layoutStyle="columns"}}(((
2 In this microlearning, we will focus on 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.
3 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.
4
5 However, there are also other use cases in which an H2 database of your own making will be beneficial:
6
7 * Storing tokens because of rate-limiting of an external API
8 * Remembering where eMagiz stopped the last time when a process was executed (eMagiz remembers this to create deltas)
9 * Enriching your message with data from the H2 database
10
11 Should you have any questions, please contact [[academy@emagiz.com>>mailto:academy@emagiz.com]].
12
13 == 1. Prerequisites ==
14
15 * Advanced knowledge of the eMagiz platform
16
17 == 2. Key concepts ==
18
19 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.
20 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
21
22 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.
23
24 However, there are also other use cases in which an H2 database of your own making will be beneficial:
25
26 * Storing tokens because of rate-limiting of an external API
27 * Remembering where eMagiz stopped the last time when a process was executed (eMagiz remembers this to create deltas)
28 * Enriching your message with data from the H2 database
29
30 == 3. H2 Database for other applications ==
31
32 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.
33
34 However, there are also other use cases in which an H2 database of your own making will be beneficial:
35
36 * Storing tokens because of rate-limiting of an external API
37 * Remembering where eMagiz stopped the last time when a process was executed (eMagiz remembers this to create deltas)
38 * Enriching your message with data from the H2 database
39
40 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.
41
42 === 3.1 Setting up the table within the H2 database ===
43
44 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:
45
46 * A SQL resource that defines the table structure
47 * The support object called JDBC H2 Connection Pool
48 * The support object called JDBC initialize database
49
50 A simple SQL statement that defines a table that holds the access token could look something like this.
51
52 {{code language="sql"}}
53 CREATE TABLE IF NOT EXISTS ACCESSTOKENS
54 (
55 CUSTOMERID varchar(255) PRIMARY KEY,
56 EXPIRES varchar(255),
57 ACCESSTOKEN varchar(255),
58 ACCESSID varchar(255)
59 );
60 INSERT INTO ACCESSTOKENS
61 SELECT 'NEW', '0', 'NEW', 'NEW'
62 WHERE NOT EXISTS (SELECT * FROM ACCESSTOKENS);
63 {{/code}}
64
65 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.
66
67 The next step will be to add the support object called JDBC H2 Connection Pool to the flow
68
69 [[image:Main.Images.Microlearning.WebHome@expert-solution-architecture-h2-database-for-other-applications--jdbc-h2-search.png]]
70
71 When you open the component you will see that you need to fill in three variables
72
73 [[image:Main.Images.Microlearning.WebHome@expert-solution-architecture-h2-database-for-other-applications--jdbc-h2-component-empty.png]]
74
75 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.
76
77 [[image:Main.Images.Microlearning.WebHome@expert-solution-architecture-h2-database-for-other-applications--jdbc-h2-component-filled.png]]
78
79 The third and final step to set up the table within the H2 database is to add the support object called JDBC initialize database.
80
81 [[image:Main.Images.Microlearning.WebHome@expert-solution-architecture-h2-database-for-other-applications--jdbc-h2-initialize-empty.png]]
82
83 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.
84
85 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:
86
87 [[image:Main.Images.Microlearning.WebHome@expert-solution-architecture-h2-database-for-other-applications--jdbc-h2-initialize-filled.png]]
88
89 === 3.3 Write data to the table ===
90
91 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.
92
93 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
94
95 {{code language="sql"}}
96 MERGE INTO ACCESSTOKENS SET CUSTOMERID = :headers[customer], EXPIRES = :headers[expires], ACCESSTOKEN = :headers[access_key], ACCESSID = :headers[accessKeyId];
97 {{/code}}
98
99 When you have correctly filled in the details of the component it should look similar to below
100
101 [[image:Main.Images.Microlearning.WebHome@expert-solution-architecture-h2-database-for-other-applications--jdbc-write-action.png]]
102
103 === 3.4 Read data from table ===
104
105 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)
106
107 == 4. Assignment ==
108
109 Build an exit or entry flow in which you incorporate the logic that you have just learned
110 This assignment can be completed with the help of the (Academy) project that you have created/used in the previous assignment.
111
112 == 5. Key takeaways ==
113
114 * 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.
115 * However, there are also other use cases in which an H2 database of your own making will be beneficial:
116 * Storing tokens because of rate-limiting of an external API
117 * Remembering where eMagiz stopped the last time when a process was executed (eMagiz remembers this to create deltas)
118 * Enriching your message with data from the H2 database
119 * In the flow designer you need to ensure that the database and table are created, that you can write and read data
120
121 == 6. Suggested Additional Readings ==
122
123 If you are interested in this topic and want more information on it please read the help text provided by eMagiz.
124
125 == 7. Silent demonstration video ==
126
127 There is no demonstration video for this microlearning. We believe that the implementation of this is too specific based on the used case that a video would not be beneficial.
128
129 )))((({{toc/}}))){{/container}}{{/container}}