colem/cole-maxwell1.github.io
1
0
Fork 0
mirror of https://github.com/cole-maxwell1/cole-maxwell1.github.io.git synced 2026-01-25 22:41:47 -05:00
Code Issues Packages Projects Releases Wiki Activity
Files
gh-pages
cole-maxwell1.github.io/posts/rest-api-ibmi-qsys2/index.html
GitHub Actions 08f440e17b [Automation] Site update No.14
2023-08-12 14:57:06 +00:00

236 lines
41 KiB
HTML
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
<!DOCTYPE html><html lang="en" ><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"><meta name="generator" content="Jekyll v4.2.2" /><meta property="og:title" content="Utilize a RESTful API with IBMi QSYS_HTTP Tools in SQL" /><meta property="og:locale" content="en" /><meta name="description" content="The Api" /><meta property="og:description" content="The Api" /><link rel="canonical" href="https://colemaxwell.dev/posts/rest-api-ibmi-qsys2/" /><meta property="og:url" content="https://colemaxwell.dev/posts/rest-api-ibmi-qsys2/" /><meta property="og:site_name" content="Cole Maxwell" /><meta property="og:type" content="article" /><meta property="article:published_time" content="2022-07-14T15:30:55-05:00" /><meta name="twitter:card" content="summary" /><meta property="twitter:title" content="Utilize a RESTful API with IBMi QSYS_HTTP Tools in SQL" /><meta name="twitter:site" content="@twitter_username" /> <script type="application/ld+json"> {"@context":"https://schema.org","@type":"BlogPosting","dateModified":"2022-08-03T21:32:53-05:00","datePublished":"2022-07-14T15:30:55-05:00","description":"The Api","headline":"Utilize a RESTful API with IBMi QSYS_HTTP Tools in SQL","mainEntityOfPage":{"@type":"WebPage","@id":"https://colemaxwell.dev/posts/rest-api-ibmi-qsys2/"},"url":"https://colemaxwell.dev/posts/rest-api-ibmi-qsys2/"}</script><title>Utilize a RESTful API with IBMi QSYS_HTTP Tools in SQL | Cole Maxwell</title><link rel="apple-touch-icon" sizes="180x180" href="/assets/img/favicons/apple-touch-icon.png"><link rel="icon" type="image/png" sizes="32x32" href="/assets/img/favicons/favicon-32x32.png"><link rel="icon" type="image/png" sizes="16x16" href="/assets/img/favicons/favicon-16x16.png"><link rel="manifest" href="/assets/img/favicons/site.webmanifest"><link rel="shortcut icon" href="/assets/img/favicons/favicon.ico"><meta name="apple-mobile-web-app-title" content="Cole Maxwell"><meta name="application-name" content="Cole Maxwell"><meta name="msapplication-TileColor" content="#da532c"><meta name="msapplication-config" content="/assets/img/favicons/browserconfig.xml"><meta name="theme-color" content="#ffffff"><link rel="preconnect" href="https://fonts.googleapis.com" ><link rel="dns-prefetch" href="https://fonts.googleapis.com" ><link rel="preconnect" href="https://fonts.gstatic.com" crossorigin><link rel="dns-prefetch" href="https://fonts.gstatic.com" crossorigin><link rel="preconnect" href="https://fonts.googleapis.com" ><link rel="dns-prefetch" href="https://fonts.googleapis.com" ><link rel="preconnect" href="https://cdn.jsdelivr.net" ><link rel="dns-prefetch" href="https://cdn.jsdelivr.net" ><link rel="stylesheet" href="https://fonts.googleapis.com/css2?family=Lato&family=Source+Sans+Pro:wght@400;600;700;900&display=swap"><link rel="preconnect" href="https://www.google-analytics.com" crossorigin="use-credentials"><link rel="dns-prefetch" href="https://www.google-analytics.com"><link rel="preconnect" href="https://www.googletagmanager.com" crossorigin="anonymous"><link rel="dns-prefetch" href="https://www.googletagmanager.com"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4/dist/css/bootstrap.min.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free@5.11.2/css/all.min.css"><link rel="stylesheet" href="/assets/css/style.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/afeld/bootstrap-toc@1.0.1/dist/bootstrap-toc.min.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/magnific-popup@1/dist/magnific-popup.min.css"> <script src="https://cdn.jsdelivr.net/npm/jquery@3/dist/jquery.min.js"></script> <script type="text/javascript"> class ModeToggle { static get MODE_KEY() { return "mode"; } static get MODE_ATTR() { return "data-mode"; } static get DARK_MODE() { return "dark"; } static get LIGHT_MODE() { return "light"; } static get ID() { return "mode-toggle"; } constructor() { if (this.hasMode) { if (this.isDarkMode) { if (!this.isSysDarkPrefer) { this.setDark(); } } else { if (this.isSysDarkPrefer) { this.setLight(); } } } let self = this; /* always follow the system prefers */ this.sysDarkPrefers.addEventListener("change", () => { if (self.hasMode) { if (self.isDarkMode) { if (!self.isSysDarkPrefer) { self.setDark(); } } else { if (self.isSysDarkPrefer) { self.setLight(); } } self.clearMode(); } self.notify(); }); } /* constructor() */ get sysDarkPrefers() { return window.matchMedia("(prefers-color-scheme: dark)"); } get isSysDarkPrefer() { return this.sysDarkPrefers.matches; } get isDarkMode() { return this.mode === ModeToggle.DARK_MODE; } get isLightMode() { return this.mode === ModeToggle.LIGHT_MODE; } get hasMode() { return this.mode != null; } get mode() { return sessionStorage.getItem(ModeToggle.MODE_KEY); } /* get the current mode on screen */ get modeStatus() { if (this.isDarkMode || (!this.hasMode && this.isSysDarkPrefer)) { return ModeToggle.DARK_MODE; } else { return ModeToggle.LIGHT_MODE; } } setDark() { $('html').attr(ModeToggle.MODE_ATTR, ModeToggle.DARK_MODE); sessionStorage.setItem(ModeToggle.MODE_KEY, ModeToggle.DARK_MODE); } setLight() { $('html').attr(ModeToggle.MODE_ATTR, ModeToggle.LIGHT_MODE); sessionStorage.setItem(ModeToggle.MODE_KEY, ModeToggle.LIGHT_MODE); } clearMode() { $('html').removeAttr(ModeToggle.MODE_ATTR); sessionStorage.removeItem(ModeToggle.MODE_KEY); } /* Notify another plugins that the theme mode has changed */ notify() { window.postMessage({ direction: ModeToggle.ID, message: this.modeStatus }, "*"); } } /* ModeToggle */ const toggle = new ModeToggle(); function flipMode() { if (toggle.hasMode) { if (toggle.isSysDarkPrefer) { if (toggle.isLightMode) { toggle.clearMode(); } else { toggle.setLight(); } } else { if (toggle.isDarkMode) { toggle.clearMode(); } else { toggle.setDark(); } } } else { if (toggle.isSysDarkPrefer) { toggle.setLight(); } else { toggle.setDark(); } } toggle.notify(); } /* flipMode() */ </script><body data-spy="scroll" data-target="#toc" data-topbar-visible="true"><div id="sidebar" class="d-flex flex-column align-items-end"><div class="profile-wrapper text-center"><div id="avatar"> <a href="/" class="mx-auto"> <img src="https://avatars.githubusercontent.com/u/58858010?v=4" alt="avatar" onerror="this.style.display='none'"> </a></div><div class="site-title mt-3"> <a href="/">Cole Maxwell</a></div><div class="site-subtitle font-italic">Craftsmanship in software and systems</div></div><ul class="w-100"><li class="nav-item"> <a href="/" class="nav-link"> <i class="fa-fw fas fa-home ml-xl-3 mr-xl-3 unloaded"></i> <span>HOME</span> </a><li class="nav-item"> <a href="/categories/" class="nav-link"> <i class="fa-fw fas fa-stream ml-xl-3 mr-xl-3 unloaded"></i> <span>CATEGORIES</span> </a><li class="nav-item"> <a href="/tags/" class="nav-link"> <i class="fa-fw fas fa-tag ml-xl-3 mr-xl-3 unloaded"></i> <span>TAGS</span> </a><li class="nav-item"> <a href="/archives/" class="nav-link"> <i class="fa-fw fas fa-archive ml-xl-3 mr-xl-3 unloaded"></i> <span>ARCHIVES</span> </a><li class="nav-item"> <a href="/about/" class="nav-link"> <i class="fa-fw fas fa-info-circle ml-xl-3 mr-xl-3 unloaded"></i> <span>ABOUT</span> </a></ul><div class="sidebar-bottom mt-auto d-flex flex-wrap justify-content-center align-items-center"> <button class="mode-toggle btn" aria-label="Switch Mode"> <i class="fas fa-adjust"></i> </button> <span class="icon-border"></span> <a href="https://github.com/cole-maxwell1" aria-label="github" target="_blank" rel="noopener"> <i class="fab fa-github"></i> </a> <a href="https://www.linkedin.com/in/cole-n-maxwell" aria-label="linkedin" target="_blank" rel="noopener"> <i class="fab fa-linkedin"></i> </a> <a href=" javascript:location.href = 'mailto:' + ['cole','codecraftsmen.dev'].join('@')" aria-label="email" > <i class="fas fa-envelope"></i> </a> <a href="/feed.xml" aria-label="rss" > <i class="fas fa-rss"></i> </a></div></div><div id="topbar-wrapper"><div id="topbar" class="container d-flex align-items-center justify-content-between h-100 pl-3 pr-3 pl-md-4 pr-md-4"> <span id="breadcrumb"> <span> <a href="/"> Home </a> </span> <span>Utilize a RESTful API with IBMi QSYS_HTTP Tools in SQL</span> </span> <i id="sidebar-trigger" class="fas fa-bars fa-fw"></i><div id="topbar-title"> Post</div><i id="search-trigger" class="fas fa-search fa-fw"></i> <span id="search-wrapper" class="align-items-center"> <i class="fas fa-search fa-fw"></i> <input class="form-control" id="search-input" type="search" aria-label="search" autocomplete="off" placeholder="Search..."> </span> <span id="search-cancel" >Cancel</span></div></div><div id="main-wrapper" class="d-flex justify-content-center"><div id="main" class="container pl-xl-4 pr-xl-4"><div class="row"><div id="core-wrapper" class="col-12 col-lg-11 col-xl-9 pr-xl-4"><div class="post pl-1 pr-1 pl-md-2 pr-md-2"><h1 data-toc-skip>Utilize a RESTful API with IBMi QSYS_HTTP Tools in SQL</h1><div class="post-meta text-muted"> <span> Posted <em class="" data-ts="1657830655" data-df="ll" data-toggle="tooltip" data-placement="bottom"> Jul 14, 2022 </em> </span> <span> Updated <em class="" data-ts="1659580373" data-df="ll" data-toggle="tooltip" data-placement="bottom"> Aug 3, 2022 </em> </span><div class="d-flex justify-content-between"> <span> By <em> <a href="https://github.com/cole-maxwell1">Cole Maxwell</a> </em> </span><div> <span class="readtime" data-toggle="tooltip" data-placement="bottom" title="1244 words"> <em>6 min</em> read</span></div></div></div><div class="post-content"><h2 id="the-api"><span class="mr-2">The Api</span><a href="#the-api" class="anchor text-muted"><i class="fas fa-hashtag"></i></a></h2><p>This demo used the <code class="language-plaintext highlighter-rouge">fakeStoreApi</code> which is a free online REST API that you can use whenever you need Pseudo-real data for without running any server-side code. It’s awesome for teaching purposes, sample codes, tests, etc. This API does not require authentication for requests</p><blockquote><p>A list of other public APIs can be found <a href="https://github.com/public-apis/public-apis">here</a></p></blockquote><h2 id="http-functions-overview"><span class="mr-2">HTTP Functions Overview</span><a href="#http-functions-overview" class="anchor text-muted"><i class="fas fa-hashtag"></i></a></h2><p>These HTTP functions are used to make HTTP requests that use web services. These functions allow the SQL programmer to use Representational State Transfer (RESTful) via SQL, including Embedded SQL. They provide the same capabilities as the <a href="https://www.ibm.com/docs/en/ssw_ibm_i_75/rzajq/rzajqhttpoverview.htm">SYSTOOLS HTTP functions</a> without the overhead of creating a JVM.</p><p>These HTTP functions exist in QSYS2 and have lower overhead than the SYSTOOLS HTTP functions. Additional benefits of the QSYS2 HTTP functions are HTTP authentication, proxy support, configurable redirection attempts, and configurable SSL options.</p><p>The URL parameter supports http: and https: URLs. The https: URL indicates that network communication should take place over a secure communication channel. An https request uses TLS (Transport Layer Security) to create the secure channel. This secure channel encrypts any transmitted data and also prevents man-in-the-middle attacks. Any communication that contains secure information should use https instead of http. Because of the sensitive nature of userids and passwords, HTTP authentication is not allowed for http URLs.</p><h3 id="foundational-http-functions"><span class="mr-2">Foundational HTTP functions</span><a href="#foundational-http-functions" class="anchor text-muted"><i class="fas fa-hashtag"></i></a></h3><p>The foundational functions are named according to the two dimensions used when making HTTP requests. The first dimension is the HTTP operation. There are 5 different HTTP operations: GET, PUT, POST, PATCH, and DELETE. The second dimension indicates whether the verbose version of the function should be used. The non-verbose functions are scalar functions that return the response as a CLOB. The verbose functions are table functions that return a single row, which includes the return header information that is sent from the HTTP server. The header information is formatted as JSON. The names of the functions reflect these dimensions. For example, HTTP_GET_VERBOSE uses the GET operation from the first dimension and the VERBOSE setting from the second dimension. All the functions return CLOB data.</p><blockquote><p>See the <a href="https://www.ibm.com/docs/en/i/7.4?topic=programming-http-functions-overview">IBM Docs</a> for more details</p></blockquote><h1 id="get-a-list-of-products">Get a List of Products</h1><p>The first demo receives a list of products from the <code class="language-plaintext highlighter-rouge">fakeStoreApi</code> as JSON. Here is a look at the JSON we can expect to receive:</p><div class="language-json highlighter-rouge"><div class="code-header"> <span data-label-text="JSON"><i class="fas fa-code small"></i></span> <button aria-label="copy" data-title-succeed="Copied!"><i class="far fa-clipboard"></i></button></div><div class="highlight"><code><table class="rouge-table"><tbody><tr><td class="rouge-gutter gl"><pre class="lineno">1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
</pre><td class="rouge-code"><pre><span class="p">[</span><span class="w">
</span><span class="p">{</span><span class="w">
</span><span class="nl">"id"</span><span class="p">:</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w">
</span><span class="nl">"title"</span><span class="p">:</span><span class="w"> </span><span class="s2">"Fjallraven - Foldsack No. 1 Backpack, Fits 15 Laptops"</span><span class="p">,</span><span class="w">
</span><span class="nl">"price"</span><span class="p">:</span><span class="w"> </span><span class="mf">109.95</span><span class="p">,</span><span class="w">
</span><span class="nl">"description"</span><span class="p">:</span><span class="w"> </span><span class="s2">"Your perfect pack for everyday use and walks in the forest. Stash your laptop (up to 15 inches) in the padded sleeve, your everyday"</span><span class="p">,</span><span class="w">
</span><span class="nl">"category"</span><span class="p">:</span><span class="w"> </span><span class="s2">"men's clothing"</span><span class="p">,</span><span class="w">
</span><span class="nl">"image"</span><span class="p">:</span><span class="w"> </span><span class="s2">"https://fakestoreapi.com/img/81fPKd-2AYL._AC_SL1500_.jpg"</span><span class="p">,</span><span class="w">
</span><span class="nl">"rating"</span><span class="p">:</span><span class="w"> </span><span class="p">{</span><span class="w">
</span><span class="nl">"rate"</span><span class="p">:</span><span class="w"> </span><span class="mf">3.9</span><span class="p">,</span><span class="w">
</span><span class="nl">"count"</span><span class="p">:</span><span class="w"> </span><span class="mi">120</span><span class="w">
</span><span class="p">}</span><span class="w">
</span><span class="p">},</span><span class="w">
</span><span class="p">{</span><span class="w">
</span><span class="nl">"id"</span><span class="p">:</span><span class="w"> </span><span class="mi">2</span><span class="p">,</span><span class="w">
</span><span class="nl">"title"</span><span class="p">:</span><span class="w"> </span><span class="s2">"Mens Casual Premium Slim Fit T-Shirts "</span><span class="p">,</span><span class="w">
</span><span class="nl">"price"</span><span class="p">:</span><span class="w"> </span><span class="mf">22.3</span><span class="p">,</span><span class="w">
</span><span class="nl">"description"</span><span class="p">:</span><span class="w"> </span><span class="s2">"Slim-fitting style, contrast raglan long sleeve, three-button henley placket, light weight &amp; soft fabric for breathable and comfortable wearing. And Solid stitched shirts with round neck made for durability and a great fit for casual fashion wear and diehard baseball fans. The Henley style round neckline includes a three-button placket."</span><span class="p">,</span><span class="w">
</span><span class="nl">"category"</span><span class="p">:</span><span class="w"> </span><span class="s2">"men's clothing"</span><span class="p">,</span><span class="w">
</span><span class="nl">"image"</span><span class="p">:</span><span class="w"> </span><span class="s2">"https://fakestoreapi.com/img/71-3HjGNDUL._AC_SY879._SX._UX._SY._UY_.jpg"</span><span class="p">,</span><span class="w">
</span><span class="nl">"rating"</span><span class="p">:</span><span class="w"> </span><span class="p">{</span><span class="w">
</span><span class="nl">"rate"</span><span class="p">:</span><span class="w"> </span><span class="mf">4.1</span><span class="p">,</span><span class="w">
</span><span class="nl">"count"</span><span class="p">:</span><span class="w"> </span><span class="mi">259</span><span class="w">
</span><span class="p">}</span><span class="w">
</span><span class="p">}</span><span class="w">
</span><span class="p">]</span><span class="w">
</span></pre></table></code></div></div><p>This is actually an array of two JSON objects. The JSON tools provided by DB2 are smart and will know to treat each object separately. To make sure we are getting the expected JSON we can print the results of <code class="language-plaintext highlighter-rouge">QSYS2.HTTP_GET</code> with the <code class="language-plaintext highlighter-rouge">VALUES</code> keyword.</p><div class="language-sql highlighter-rouge"><div class="code-header"> <span data-label-text="Sql"><i class="fas fa-code small"></i></span> <button aria-label="copy" data-title-succeed="Copied!"><i class="far fa-clipboard"></i></button></div><div class="highlight"><code><table class="rouge-table"><tbody><tr><td class="rouge-gutter gl"><pre class="lineno">1
2
3
4
</pre><td class="rouge-code"><pre><span class="k">VALUES</span> <span class="n">QSYS2</span><span class="p">.</span><span class="n">HTTP_GET</span><span class="p">(</span>
<span class="s1">'http://fakestoreapi.com/products?limit=2'</span><span class="p">,</span>
<span class="s1">''</span>
<span class="p">);</span>
</pre></table></code></div></div><p><code class="language-plaintext highlighter-rouge">QSYS2.HTTP_GET</code> takes two arguments. The first argument is the URL of the API endpoint the GET request will be sent to. In this case it is the <code class="language-plaintext highlighter-rouge">http://fakestoreapi.com/products</code> endpoint and the <code class="language-plaintext highlighter-rouge">limit=2</code> parameter is added to only get two items total. The second argument is for <a href="https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers">HTTP header</a> parameters. In this case no HTTP header parameters need to be specified to complete the request so this field can be left empty.</p><p>The <code class="language-plaintext highlighter-rouge">JSON_TABLE</code> function can take any <code class="language-plaintext highlighter-rouge">QSYS2.HTTP_XXXX</code> that produces JSON as an argument. The values from the JSON keys can then be extracted and placed directly into a table.</p><div class="language-sql highlighter-rouge"><div class="code-header"> <span data-label-text="Sql"><i class="fas fa-code small"></i></span> <button aria-label="copy" data-title-succeed="Copied!"><i class="far fa-clipboard"></i></button></div><div class="highlight"><code><table class="rouge-table"><tbody><tr><td class="rouge-gutter gl"><pre class="lineno">1
2
3
4
5
6
7
8
9
10
11
</pre><td class="rouge-code"><pre><span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="n">JSON_TABLE</span><span class="p">(</span>
<span class="n">QSYS2</span><span class="p">.</span><span class="n">HTTP_GET</span><span class="p">(</span>
<span class="s1">'http://fakestoreapi.com/products?limit=10'</span><span class="p">,</span>
<span class="s1">''</span>
<span class="p">),</span>
<span class="s1">'$'</span> <span class="n">COLUMNS</span><span class="p">(</span>
<span class="n">name</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">75</span><span class="p">)</span> <span class="n">PATH</span> <span class="s1">'lax $.title'</span><span class="p">,</span>
<span class="n">totalRatings</span> <span class="nb">INT</span> <span class="n">PATH</span> <span class="s1">'lax $.rating.count'</span>
<span class="p">)</span>
<span class="p">);</span>
</pre></table></code></div></div><p>This example will extract the values from the <code class="language-plaintext highlighter-rouge">title</code> (name of item) key and the nested field <code class="language-plaintext highlighter-rouge">count</code> (total number of ratings) key in the ratings array. and then place them into a table with the column names <code class="language-plaintext highlighter-rouge">name</code> and <code class="language-plaintext highlighter-rouge">totalRatings</code>.</p><h4 id="result"><span class="mr-2">Result:</span><a href="#result" class="anchor text-muted"><i class="fas fa-hashtag"></i></a></h4><div class="table-wrapper"><table><thead><tr><th>Name<th>Rating<tbody><tr><td>Fjallraven - Foldsack No. 1 Backpack, Fits 15 Laptops<td>120<tr><td>Mens Casual Premium Slim Fit T-Shirts<td>259<tr><td>Mens Cotton Jacket<td>500<tr><td>Mens Casual Slim Fit<td>430<tr><td>John Hardy Women’s Legends Naga Gold &amp; Silver Dragon Station Chain Bracelet<td>400<tr><td>Solid Gold Petite Micropave<td>70<tr><td>White Gold Plated Princess<td>400<tr><td>Pierced Owl Rose Gold Plated Stainless Steel Double<td>100<tr><td>WD 2TB Elements Portable External Hard Drive - USB 3.0<td>203<tr><td>SanDisk SSD PLUS 1TB Internal SSD - SATA III 6 Gb/s<td>470</table></div><h1 id="post-a-new-user">POST a New User</h1><p>The next example is sending a POST request to the <code class="language-plaintext highlighter-rouge">https://fakestoreapi.com/users</code> endpoint to create a new user. The <code class="language-plaintext highlighter-rouge">fakeStoreApi</code> <a href="https://fakestoreapi.com/docs">docs</a> indicate that the body of our HTTP POST request should contain the following JSON object:</p><div class="language-json highlighter-rouge"><div class="code-header"> <span data-label-text="JSON"><i class="fas fa-code small"></i></span> <button aria-label="copy" data-title-succeed="Copied!"><i class="far fa-clipboard"></i></button></div><div class="highlight"><code><table class="rouge-table"><tbody><tr><td class="rouge-gutter gl"><pre class="lineno">1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
</pre><td class="rouge-code"><pre><span class="p">{</span><span class="w">
</span><span class="nl">"email"</span><span class="p">:</span><span class="s2">"John@gmail.com"</span><span class="p">,</span><span class="w">
</span><span class="nl">"username"</span><span class="p">:</span><span class="s2">"johnd"</span><span class="p">,</span><span class="w">
</span><span class="nl">"password"</span><span class="p">:</span><span class="s2">"m38rmF$"</span><span class="p">,</span><span class="w">
</span><span class="nl">"name"</span><span class="p">:{</span><span class="w">
</span><span class="nl">"firstname"</span><span class="p">:</span><span class="s2">"John"</span><span class="p">,</span><span class="w">
</span><span class="nl">"lastname"</span><span class="p">:</span><span class="s2">"Doe"</span><span class="w">
</span><span class="p">},</span><span class="w">
</span><span class="nl">"address"</span><span class="p">:{</span><span class="w">
</span><span class="nl">"city"</span><span class="p">:</span><span class="s2">"kilcoole"</span><span class="p">,</span><span class="w">
</span><span class="nl">"street"</span><span class="p">:</span><span class="s2">"7835 new road"</span><span class="p">,</span><span class="w">
</span><span class="nl">"number"</span><span class="p">:</span><span class="mi">3</span><span class="p">,</span><span class="w">
</span><span class="nl">"zipcode"</span><span class="p">:</span><span class="s2">"12926-3874"</span><span class="p">,</span><span class="w">
</span><span class="nl">"geolocation"</span><span class="p">:{</span><span class="w">
</span><span class="nl">"lat"</span><span class="p">:</span><span class="s2">"-37.3159"</span><span class="p">,</span><span class="w">
</span><span class="nl">"long"</span><span class="p">:</span><span class="s2">"81.1496"</span><span class="w">
</span><span class="p">}</span><span class="w">
</span><span class="p">},</span><span class="w">
</span><span class="nl">"phone"</span><span class="p">:</span><span class="w"> </span><span class="s2">"1-570-236-7033"</span><span class="w">
</span><span class="p">}</span><span class="w">
</span></pre></table></code></div></div><p>Upon a successful add of a new user the API will return a 200 response code and a JSON object with the user’s new id:</p><div class="language-json highlighter-rouge"><div class="code-header"> <span data-label-text="JSON"><i class="fas fa-code small"></i></span> <button aria-label="copy" data-title-succeed="Copied!"><i class="far fa-clipboard"></i></button></div><div class="highlight"><code><table class="rouge-table"><tbody><tr><td class="rouge-gutter gl"><pre class="lineno">1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
</pre><td class="rouge-code"><pre><span class="p">{</span><span class="w">
</span><span class="nl">"address"</span><span class="p">:</span><span class="w"> </span><span class="p">{</span><span class="w">
</span><span class="nl">"geolocation"</span><span class="p">:</span><span class="w"> </span><span class="p">{</span><span class="w">
</span><span class="nl">"lat"</span><span class="p">:</span><span class="w"> </span><span class="s2">"-37.3159"</span><span class="p">,</span><span class="w">
</span><span class="nl">"long"</span><span class="p">:</span><span class="w"> </span><span class="s2">"81.1496"</span><span class="w">
</span><span class="p">},</span><span class="w">
</span><span class="nl">"city"</span><span class="p">:</span><span class="w"> </span><span class="s2">"kilcoole"</span><span class="p">,</span><span class="w">
</span><span class="nl">"street"</span><span class="p">:</span><span class="w"> </span><span class="s2">"7835 new road"</span><span class="w">
</span><span class="p">},</span><span class="w">
</span><span class="nl">"_id"</span><span class="p">:</span><span class="w"> </span><span class="s2">"62c73539f0321700139f4682"</span><span class="p">,</span><span class="w">
</span><span class="nl">"id"</span><span class="p">:</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w">
</span><span class="nl">"email"</span><span class="p">:</span><span class="w"> </span><span class="s2">"John@gmail.com"</span><span class="p">,</span><span class="w">
</span><span class="nl">"username"</span><span class="p">:</span><span class="w"> </span><span class="s2">"johnd"</span><span class="p">,</span><span class="w">
</span><span class="nl">"password"</span><span class="p">:</span><span class="w"> </span><span class="s2">"m38rmF$"</span><span class="p">,</span><span class="w">
</span><span class="nl">"phone"</span><span class="p">:</span><span class="w"> </span><span class="s2">"1-570-236-7033"</span><span class="w">
</span><span class="p">}</span><span class="w">
</span></pre></table></code></div></div><p><code class="language-plaintext highlighter-rouge">QSYS2.HTTP_POST</code> takes three This time arguments the URL, the HTTP body and the HTTP header parameters. This time the URL and body arguments of the <code class="language-plaintext highlighter-rouge">QSYS2.HTTP_POST</code> will be assigned to variables for readability.</p><p>We also need to specify the “Content-Type” in the HTTP header to indicate that our HTTP body will be in JSON format. By default <code class="language-plaintext highlighter-rouge">QSYS2.HTTP_POST</code> specifies the content type of the body to be XML. To override this setting we pass in the header settings in JSON format:</p><div class="language-json highlighter-rouge"><div class="code-header"> <span data-label-text="JSON"><i class="fas fa-code small"></i></span> <button aria-label="copy" data-title-succeed="Copied!"><i class="far fa-clipboard"></i></button></div><div class="highlight"><code><table class="rouge-table"><tbody><tr><td class="rouge-gutter gl"><pre class="lineno">1
</pre><td class="rouge-code"><pre><span class="p">{</span><span class="nl">"header"</span><span class="p">:</span><span class="s2">"Content-Type,application/json;charset=utf-8"</span><span class="p">}</span><span class="w">
</span></pre></table></code></div></div><blockquote><p>More information about the different header settings that can to passed to the <code class="language-plaintext highlighter-rouge">QSYS2.HTTP_XXXX</code> tools can be found in the <a href="https://www.ibm.com/docs/en/i/7.4?topic=functions-http-get#rbafzscahttpget__HTTP_options">IBM docs</a></p></blockquote><p>Here all the moving parts put together:</p><div class="language-sql highlighter-rouge"><div class="code-header"> <span data-label-text="Sql"><i class="fas fa-code small"></i></span> <button aria-label="copy" data-title-succeed="Copied!"><i class="far fa-clipboard"></i></button></div><div class="highlight"><code><table class="rouge-table"><tbody><tr><td class="rouge-gutter gl"><pre class="lineno">1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
</pre><td class="rouge-code"><pre><span class="k">Create</span> <span class="k">or</span> <span class="k">replace</span> <span class="k">variable</span> <span class="o">@</span><span class="n">userURL</span> <span class="nb">varchar</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span> <span class="p">;</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">userURL</span> <span class="o">=</span> <span class="s1">'http://fakestoreapi.com/users'</span><span class="p">;</span>
<span class="k">Create</span> <span class="k">or</span> <span class="k">replace</span> <span class="k">variable</span> <span class="o">@</span><span class="n">postBody</span> <span class="nb">varchar</span><span class="p">(</span><span class="mi">500</span><span class="p">)</span> <span class="p">;</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">postBody</span> <span class="o">=</span> <span class="s1">'{
"email":"John@gmail.com",
"username":"johnd",
"password":"m38rmF$",
"name":{
"firstname":"John",
"lastname":"Doe"
},
"address":{
"city":"kilcoole",
"street":"7835 new road",
"number":3,
"zipcode":"12926-3874",
"geolocation":{
"lat":"-37.3159",
"long":"81.1496"
}
},
"phone": "1-570-236-7033"
}'</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="n">JSON_TABLE</span><span class="p">(</span>
<span class="n">QSYS2</span><span class="p">.</span><span class="n">HTTP_POST</span><span class="p">(</span>
<span class="o">@</span><span class="n">userURL</span><span class="p">,</span>
<span class="o">@</span><span class="n">postBody</span><span class="p">,</span>
<span class="s1">'{"header":"Content-Type,application/json;charset=utf-8"}'</span>
<span class="p">),</span>
<span class="s1">'$'</span> <span class="n">COLUMNS</span><span class="p">(</span>
<span class="n">testID</span> <span class="nb">INT</span> <span class="n">PATH</span> <span class="s1">'lax $.id'</span><span class="p">,</span>
<span class="n">email</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span> <span class="n">PATH</span> <span class="s1">'lax $.email'</span><span class="p">,</span>
<span class="n">username</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span> <span class="n">PATH</span> <span class="s1">'lax $.username'</span><span class="p">,</span>
<span class="n">password</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span> <span class="n">PATH</span> <span class="s1">'lax $.password'</span>
<span class="p">)</span>
<span class="p">);</span>
</pre></table></code></div></div><p>And the result:</p><div class="table-wrapper"><table><thead><tr><th>TESTID<th>EMAIL<th>USERNAME<th>PASSWORD<tbody><tr><td>1<td>John@gmail.com<td>johnd<td>m38rmF$</table></div></div><div class="post-tail-wrapper text-muted"><div class="post-meta mb-3"> <i class="far fa-folder-open fa-fw mr-1"></i> <a href='/categories/ibm-i/'>IBM i</a></div><div class="post-tags"> <i class="fa fa-tags fa-fw mr-1"></i> <a href="/tags/ibmi/" class="post-tag no-text-decoration" >ibmi</a> <a href="/tags/api/" class="post-tag no-text-decoration" >api</a> <a href="/tags/qsys2/" class="post-tag no-text-decoration" >qsys2</a> <a href="/tags/rest/" class="post-tag no-text-decoration" >rest</a> <a href="/tags/sql/" class="post-tag no-text-decoration" >sql</a></div><div class="post-tail-bottom d-flex justify-content-between align-items-center mt-3 pt-5 pb-2"><div class="license-wrapper"> This post is licensed under <a href="https://creativecommons.org/licenses/by/4.0/"> CC BY 4.0 </a> by the author.</div><div class="share-wrapper"> <span class="share-label text-muted mr-1">Share</span> <span class="share-icons"> <a href="https://twitter.com/intent/tweet?text=Utilize+a+RESTful+API+with+IBMi+QSYS_HTTP+Tools+in+SQL+-+Cole+Maxwell&url=https%3A%2F%2Fcolemaxwell.dev%2Fposts%2Frest-api-ibmi-qsys2%2F" data-toggle="tooltip" data-placement="top" title="Twitter" target="_blank" rel="noopener" aria-label="Twitter"> <i class="fa-fw fab fa-twitter"></i> </a> <a href="https://www.facebook.com/sharer/sharer.php?title=Utilize+a+RESTful+API+with+IBMi+QSYS_HTTP+Tools+in+SQL+-+Cole+Maxwell&u=https%3A%2F%2Fcolemaxwell.dev%2Fposts%2Frest-api-ibmi-qsys2%2F" data-toggle="tooltip" data-placement="top" title="Facebook" target="_blank" rel="noopener" aria-label="Facebook"> <i class="fa-fw fab fa-facebook-square"></i> </a> <a href="https://t.me/share/url?url=https%3A%2F%2Fcolemaxwell.dev%2Fposts%2Frest-api-ibmi-qsys2%2F&text=Utilize+a+RESTful+API+with+IBMi+QSYS_HTTP+Tools+in+SQL+-+Cole+Maxwell" data-toggle="tooltip" data-placement="top" title="Telegram" target="_blank" rel="noopener" aria-label="Telegram"> <i class="fa-fw fab fa-telegram"></i> </a> <i id="copy-link" class="fa-fw fas fa-link small" data-toggle="tooltip" data-placement="top" title="Copy link" data-title-succeed="Link copied successfully!"> </i> </span></div></div></div></div></div><div id="panel-wrapper" class="col-xl-3 pl-2 text-muted"><div class="access"><div id="access-lastmod" class="post"><div class="panel-heading">Recently Updated</div><ul class="post-content pl-0 pb-1 ml-1 mt-2"><li><a href="/posts/ibmi-odbc-on-linux/">Using ODBC on IBM i for Local Linux Development</a><li><a href="/posts/rest-api-ibmi-qsys2/">Utilize a RESTful API with IBMi QSYS_HTTP Tools in SQL</a></ul></div><div id="access-tags"><div class="panel-heading">Trending Tags</div><div class="d-flex flex-wrap mt-3 mb-1 mr-3"> <a class="post-tag" href="/tags/ibmi/">ibmi</a> <a class="post-tag" href="/tags/sql/">sql</a> <a class="post-tag" href="/tags/api/">api</a> <a class="post-tag" href="/tags/as400/">as400</a> <a class="post-tag" href="/tags/linux/">linux</a> <a class="post-tag" href="/tags/odbc/">odbc</a> <a class="post-tag" href="/tags/qsys2/">qsys2</a> <a class="post-tag" href="/tags/rest/">rest</a></div></div></div><script src="https://cdn.jsdelivr.net/gh/afeld/bootstrap-toc@1.0.1/dist/bootstrap-toc.min.js"></script><div id="toc-wrapper" class="pl-0 pr-4 mb-5"><div class="panel-heading pl-3 pt-2 mb-2">Contents</div><nav id="toc" data-toggle="toc"></nav></div></div></div><div class="row"><div id="tail-wrapper" class="col-12 col-lg-11 col-xl-9 pl-3 pr-3 pr-xl-4"><div id="related-posts" class="mt-5 mb-2 mb-sm-4"><h3 class="pt-2 mt-1 mb-4 ml-1" data-toc-skip>Further Reading</h3><div class="card-deck mb-4"><div class="card"> <a href="/posts/ibmi-odbc-on-linux/"><div class="card-body"> <em class="small" data-ts="1659573540" data-df="ll" > Aug 3, 2022 </em><h3 class="pt-0 mt-1 mb-3" data-toc-skip>Using ODBC on IBM i for Local Linux Development</h3><div class="text-muted small"><p> If you are new to the IBM i platform coming right out of school, like me, or you are a developer used to working exclusively with open-source tooling, the IBM i platform can be a strange place. The...</p></div></div></a></div></div></div><div class="post-navigation d-flex justify-content-between"><div class="btn btn-outline-primary disabled" prompt="Older"><p>-</p></div><a href="/posts/ibmi-odbc-on-linux/" class="btn btn-outline-primary" prompt="Newer"><p>Using ODBC on IBM i for Local Linux Development</p></a></div></div></div><footer class="row pl-3 pr-3"><div class="col-12 d-flex justify-content-between align-items-center text-muted pl-0 pr-0"><div class="footer-left"><p class="mb-0"> © 2023 <a href="https://github.com/cole-maxwell1">Cole Maxwell</a>. <span data-toggle="tooltip" data-placement="top" title="Except where otherwise noted, the blog posts on this site are licensed under the Creative Commons Attribution 4.0 International (CC BY 4.0) License by the author.">Some rights reserved.</span></p></div><div class="footer-right"><p class="mb-0"> Powered by <a href="https://jekyllrb.com" target="_blank" rel="noopener">Jekyll</a> with <a href="https://github.com/cotes2020/jekyll-theme-chirpy" target="_blank" rel="noopener">Chirpy</a> theme.</p></div></div></footer></div><div id="search-result-wrapper" class="d-flex justify-content-center unloaded"><div class="col-12 col-sm-11 post-content"><div id="search-hints"><div id="access-tags"><div class="panel-heading">Trending Tags</div><div class="d-flex flex-wrap mt-3 mb-1 mr-3"> <a class="post-tag" href="/tags/ibmi/">ibmi</a> <a class="post-tag" href="/tags/sql/">sql</a> <a class="post-tag" href="/tags/api/">api</a> <a class="post-tag" href="/tags/as400/">as400</a> <a class="post-tag" href="/tags/linux/">linux</a> <a class="post-tag" href="/tags/odbc/">odbc</a> <a class="post-tag" href="/tags/qsys2/">qsys2</a> <a class="post-tag" href="/tags/rest/">rest</a></div></div></div><div id="search-results" class="d-flex flex-wrap justify-content-center text-muted mt-3"></div></div></div></div><div id="mask"></div><a id="back-to-top" href="#" aria-label="back-to-top" class="btn btn-lg btn-box-shadow" role="button"> <i class="fas fa-angle-up"></i> </a><div id="notification" class="toast" role="alert" aria-live="assertive" aria-atomic="true" data-animation="true" data-autohide="false"><div class="toast-header"> <button type="button" class="ml-2 ml-auto close" data-dismiss="toast" aria-label="Close"> <span aria-hidden="true">&times;</span> </button></div><div class="toast-body text-center pt-0"><p class="pl-2 pr-2 mb-3">A new version of content is available.</p><button type="button" class="btn btn-primary" aria-label="Update"> Update </button></div></div><script src="https://cdn.jsdelivr.net/npm/simple-jekyll-search@1.10.0/dest/simple-jekyll-search.min.js"></script> <script> SimpleJekyllSearch({ searchInput: document.getElementById('search-input'), resultsContainer: document.getElementById('search-results'), json: '/assets/js/data/search.json', searchResultTemplate: '<div class="pl-1 pr-1 pl-sm-2 pr-sm-2 pl-lg-4 pr-lg-4 pl-xl-0 pr-xl-0"> <a href="{url}">{title}</a><div class="post-meta d-flex flex-column flex-sm-row text-muted mt-1 mb-1"> {categories} {tags}</div><p>{snippet}</p></div>', noResultsText: '<p class="mt-5">Oops! No results found.</p>', templateMiddleware: function(prop, value, template) { if (prop === 'categories') { if (value === '') { return `${value}`; } else { return `<div class="mr-sm-4"><i class="far fa-folder fa-fw"></i>${value}</div>`; } } if (prop === 'tags') { if (value === '') { return `${value}`; } else { return `<div><i class="fa fa-tag fa-fw"></i>${value}</div>`; } } } }); </script> <script src="https://cdn.jsdelivr.net/combine/npm/magnific-popup@1/dist/jquery.magnific-popup.min.js,npm/lozad/dist/lozad.min.js,npm/clipboard@2/dist/clipboard.min.js"></script> <script src="https://cdn.jsdelivr.net/combine/npm/dayjs@1/dayjs.min.js,npm/dayjs@1/locale/en.min.js,npm/dayjs@1/plugin/relativeTime.min.js,npm/dayjs@1/plugin/localizedFormat.min.js"></script> <script defer src="/assets/js/dist/post.min.js"></script> <script src="https://cdn.jsdelivr.net/npm/bootstrap@4/dist/js/bootstrap.bundle.min.js"></script> <script defer src="/app.js"></script> <script defer src="https://www.googletagmanager.com/gtag/js?id=G-T9FVDLVRR3"></script> <script> document.addEventListener("DOMContentLoaded", function(event) { window.dataLayer = window.dataLayer || []; function gtag(){dataLayer.push(arguments);} gtag('js', new Date()); gtag('config', 'G-T9FVDLVRR3'); }); </script>
Reference in New Issue View Git Blame Copy Permalink