In today's digital age, data reigns supreme. From businesses to individuals, access to relevant and timely information can make all the difference in decision-making and fostering innovation. However, efficiently gathering and utilizing data can be a daunting task. Thankfully, Application Programming Interfaces (APIs) provide a seamless solution by facilitating access to external data sources.
With the recent release of the new trading card game, Star Wars Unlimited, I was inspired to create an application to manage my inventory. I decided that entering all 252 cards manually with images would take a good deal of time, so I went looking for an API that I could get all the information from. Fortunately, I discovered an open API that had everything I needed (https://www.swu-db.com/api). Let's explore how to leverage that data and build an app around it.
Understanding APIs
First, let's understand more about APIs. APIs act as intermediaries that allow different applications to communicate and share data with each other. They provide a structured way for developers to interact with external systems, such as databases, web services, or applications. By utilizing APIs, we can access a wealth of data from various sources without needing to understand the intricacies of those systems.
Usually, we need an API key and/or token to access this data, but there are times when there is a public API that you can access without the need for authentication. Most sources will have a detailed guide on how to access the data through the API and whether you need authentication or not.
Our Approach
Our journey will unfold across several simple steps:
- Identify and get access to the API.
- Create tables to handle the data that we will be receiving.
- Save the API response to a CLOB in the Database.
- Parse the JSON CLOB and save the data into a table.
- Create an application to show the data that we have just received.
- (optional) Create other ways to view the data.
For step 1, a quick Google search pulls up an API we can use. https://www.swu-db.com/api. This is an open and public source for the data that we need. There are different calls to get different data, and there is some great documentation available.
Creating a Table for JSON Responses
Let’s start by creating a table to house the JSON response of the API. We need a place to save the API JSON response that will contain all the data that we are looking for. This is a very simple table structure I used with just three columns:
CREATE TABLE SWU_CARD_NAMES_JSON
(SCNJ_ID NUMBER,
SCNJ_DATE DATE,
SCNJ_JSON CLOB);
We have an ID, the Date we got the data, and a CLOB to hold the JSON response.
Writing a Procedure to Fetch the Data
We can now write a quick procedure to get that data and save it to this table:
create or replace PROCEDURE get_swu_card_name_json as
l_response clob;
l_num number;
BEGIN
select max(SCNJ_ID) + 1 into l_num from swu_card_names_json;
l_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(p_url => 'https://api.swu-db.com/cards/sor',
p_http_method => 'GET');
INSERT INTO swu_card_names_json VALUES (l_num, sysdate, l_response);
END;
Let's go through this code. The first thing we will do is get the next ID to save it to the table. Right now this is calculated, but it would be better to have this as a sequence for scalability. Then, we are going to get the JSON return. We want to get the response into a variable that will make it easy to save. We have the URL and the HTTP Method of GET to get that data. The Method GET is usually used just for fetching data. If you have an API where you want to update data, you can use the Method POST. Then, we just insert it all into the table.
A Closer Look at the Data
It is as easy as that. Here is an example of the data we get through this API for a single card from the set:
{
"Set": "SOR",
"Number": "010",
"Name": "Darth Vader",
"Subtitle": "Dark Lord of the Sith",
"Type": "Leader",
"Aspects": [
"Aggression",
"Villainy"
],
"Traits": [
"FORCE",
"IMPERIAL",
"SITH"
],
"Arenas": [
"Ground"
],
"Cost": "7",
"Power": "5",
"HP": "8",
"FrontText": "Action [{C=1}, {Exhaust}]: If you played a Villainy card this phase, deal 1 damage to a unit and 1 damage to a base.",
"EpicAction": "Epic Action: If you control 7 or more resources, deploy this leader. (Flip him, ready him, and move him to the ground arena.)",
"DoubleSided": true,
"BackText": "On Attack: You may deal 2 damage to a unit.",
"Rarity": "Special",
"Unique": true,
"Artist": "Borja Pindado",
"FrontArt": "https://d3alac64utt23z.cloudfront.net/images/cards/SOR/010.png",
"BackArt": "https://d3alac64utt23z.cloudfront.net/images/cards/SOR/010-b.png",
"VariantType": "Normal",
"MarketPrice": "4.30"
}
In the following posts, we will dive deeper into parsing this JSON data, storing it in a structured format, and ultimately displaying it in an APEX application. Our goal is to make the card images searchable and beautifully presented within the app.
SUBMIT YOUR COMMENT