Yet Another REST POST (and GET!): BLOBs

thatjeffsmith ORDS 2 Comments

Tell Others About This Story:

I wrote recently about using REST to upload a file to your table with ORDS.

I want to extend that and share more code. I want you to see how to not only load the pics, but then how to get a collection of items, with links to those items, and in this case, also links to the media (pics) which we’ve uploaded.

AND IT’S SO LITTLE CODE.

What I’ll share:

  • table ddl
  • a few rows as INSERTs
  • the REST module definition
  • POSTMAN demo of the pic upload via POST

The Table

It’s a PEOPLE table, I built it to store folks who would participate in a conference.

take out the identity bits if you’re not on 12c

And the code – including the INSERTs:

 
  CREATE TABLE "PEOPLE" 
   (	"FIRST_NAME" VARCHAR2(100) NOT NULL ENABLE, 
	"MIDDLE_INIT" VARCHAR2(100), 
	"LAST_NAME" VARCHAR2(100) NOT NULL ENABLE, 
	"TITLE" VARCHAR2(100), 
	"EMAIL" VARCHAR2(255) NOT NULL ENABLE, 
	"ID" NUMBER(*,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 10000 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	"STREET1" VARCHAR2(100) NOT NULL ENABLE, 
	"STREET2" VARCHAR2(100), 
	"STREET3" VARCHAR2(100), 
	"CITY" VARCHAR2(100) NOT NULL ENABLE, 
	"STATE" VARCHAR2(2) NOT NULL ENABLE, 
	"ZIP" VARCHAR2(12) NOT NULL ENABLE, 
	"COUNTRY" VARCHAR2(100), 
	"ACE" NUMBER(*,0), 
	"BIO_PIC" BLOB
   )   NO INMEMORY ;
  ALTER TABLE "PEOPLE" ADD CONSTRAINT "PEOPLE_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE;
 
   COMMENT ON COLUMN "PEOPLE"."FIRST_NAME" IS 'given name';
   COMMENT ON COLUMN "PEOPLE"."MIDDLE_INIT" IS 'first 1 or 2 letters of middle name';
   COMMENT ON COLUMN "PEOPLE"."LAST_NAME" IS 'familyi name';
   COMMENT ON COLUMN "PEOPLE"."TITLE" IS 'Mr, Mrs, Dr, Evil Dr, ...';
   COMMENT ON COLUMN "PEOPLE"."EMAIL" IS 'primary method of contact, can be biz or personal';
   COMMENT ON COLUMN "PEOPLE"."ID" IS 'system generated';
   COMMENT ON COLUMN "PEOPLE"."STREET1" IS 'mailing address 1';
   COMMENT ON COLUMN "PEOPLE"."STREET2" IS 'mailing address line 2';
   COMMENT ON COLUMN "PEOPLE"."STREET3" IS 'mailing address line 3';
   COMMENT ON COLUMN "PEOPLE"."CITY" IS 'city address';
   COMMENT ON COLUMN "PEOPLE"."STATE" IS '2 char abb for state or province';
   COMMENT ON COLUMN "PEOPLE"."ZIP" IS 'zip code allows for last 4 postal code digits';
   COMMENT ON COLUMN "PEOPLE"."COUNTRY" IS 'if null, we assume USA!';
   COMMENT ON COLUMN "PEOPLE"."ACE" IS '0=No, 1=ACED, 2=ACE, 3=ACE Associate';
   COMMENT ON COLUMN "PEOPLE"."BIO_PIC" IS 'image for presenters if necessary';
 
REM INSERTING INTO PEOPLE
SET DEFINE OFF;
INSERT INTO PEOPLE (FIRST_NAME,MIDDLE_INIT,LAST_NAME,TITLE,EMAIL,STREET1,STREET2,STREET3,CITY,STATE,ZIP,COUNTRY,ACE) VALUES ('Jeff',NULL,'Smith',NULL,'[email protected]','909 Nort St',NULL,NULL,'Cary','NC','27513',NULL,4);
INSERT INTO PEOPLE (FIRST_NAME,MIDDLE_INIT,LAST_NAME,TITLE,EMAIL,STREET1,STREET2,STREET3,CITY,STATE,ZIP,COUNTRY,ACE) VALUES ('Scott',NULL,'Spendolini',NULL,'[email protected]','123456 Password Dr',NULL,NULL,'Reston','VA','90210',NULL,1);
INSERT INTO PEOPLE (FIRST_NAME,MIDDLE_INIT,LAST_NAME,TITLE,EMAIL,STREET1,STREET2,STREET3,CITY,STATE,ZIP,COUNTRY,ACE) VALUES ('Linda',NULL,'Hoover',NULL,'[email protected]','404 Redwood Dr',NULL,NULL,'Wilmington','NC','12345',NULL,NULL);
INSERT INTO PEOPLE (FIRST_NAME,MIDDLE_INIT,LAST_NAME,TITLE,EMAIL,STREET1,STREET2,STREET3,CITY,STATE,ZIP,COUNTRY,ACE) VALUES ('Jeff',NULL,'Test',NULL,'[email protected]','a',NULL,NULL,'town','DC','91100',NULL,NULL);

The REST Module

Here are a few URI patterns and handlers.

Don’t worry, I’ll break this down for you.

So a GET on peeps/ will retrieve a list of people, with links to their records and to their pictures.
a GET on peeps/{id} will get the person record – I need this to make the link I build for the GET on peeps/ output
a GET on media/:id will get the image for a particular person – I also need this to make the link I build for the GET on peeps/ output
a POST on media/:id will upload the image

Here is that code.

-- Generated by Oracle SQL Developer REST Data Services 17.3.0.271.1803
-- Exported REST Definitions from ORDS Schema Version 17.3.0.244.09.12
-- Schema: HR   Date: Tue Nov 07 15:20:07 EST 2017
--
BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'HR',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'hr',
      p_auto_rest_auth      => FALSE);    
 
  ORDS.DEFINE_MODULE(
      p_module_name    => 'abstract',
      p_base_path      => '/abstract/',
      p_items_per_page =>  25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);      
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'abstract',
      p_pattern        => 'media/:id',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'abstract',
      p_pattern        => 'media/:id',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_items_per_page =>  0,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'DECLARE
 PEEPS_PIC BLOB := :body;
 how_big integer;
BEGIN
 ADD_PEOPLE_PIC(B_PIC => PEEPS_PIC, P_ID => :id, BLOB_SIZE => how_big);
 :bytes := how_big;
END;'
      );
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'abstract',
      p_pattern            => 'media/:id',
      p_method             => 'POST',
      p_name               => 'bytes',
      p_bind_variable_name => 'bytes',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'INT',
      p_access_method      => 'OUT',
      p_comments           => NULL);      
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'abstract',
      p_pattern        => 'media/:id',
      p_method         => 'GET',
      p_source_type    => 'resource/lob',
      p_items_per_page =>  25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'SELECT ''image/png'', bio_pic
FROM PEOPLE
where id = :id'
      );
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'abstract',
      p_pattern        => 'peeps/',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'abstract',
      p_pattern        => 'peeps/',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_items_per_page =>  25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'SELECT
    id AS "$self",
    first_name,
    last_name,
    EMAIL,
    ''../media/'' || id as "$bio_pic"
FROM
    PEOPLE
order by ID'
      );
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'abstract',
      p_pattern        => 'peeps/{id}',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'abstract',
      p_pattern        => 'peeps/{id}',
      p_method         => 'GET',
      p_source_type    => 'json/query;type=single',
      p_items_per_page =>  0,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'SELECT
    first_name,
    middle_init,
    last_name,
    title,
    email,
    id,
    street1,
    street2,
    street3,
    city,
    state,
    zip,
    country,
    CASE ace
            WHEN 0   THEN ''No''
            WHEN 1   THEN ''ACE Director''
            WHEN 2   THEN ''ACE''
            WHEN 3   THEN ''ACE Associate''
            WHEN 4   THEN ''ACE Alumni''
            ELSE ''Unknown''
        END ACE
FROM
    PEOPLE
WHERE
    id =:id'
      );
 
 
  COMMIT; 
END;

The ‘Demo’

So assuming you have put this in a REST enabled schema and have ORDS up and running I can start doing stuff.

List the people, provide LINKs to move along.

I’ve highlighted the links in the response output. It might not be great REST to provide the link for the picture alongside the item link, maybe I should have included that on the item GET itself, but I wanted it there, so I put it there. Do what works for you, but don’t be surprised if someone says, ‘that is not REST.’

Here’s how I generated those links, the code behind the GET handler is VERY simple. Click the pic to go to ORACLE-BASE’s example of how to generate LINKS with ORDS.

Let’s go look at those links.

Let’s get the person record, and let’s go look at their picture.

Ok, here’s how I’m adding the pictures, and a quick demo.

My post HANDLER code is very simple, if you scroll up and look at it, you’ll see this line:
ADD_PEOPLE_PIC(B_PIC => PEEPS_PIC, P_ID => :id, BLOB_SIZE => how_big);

That’s a procedure call. So when I call my POST, ORDS end’s up running a stored procedure. Here’s that PL/SQL – but it accepts an integer on the request header, a file via the POST :body, and it returns the size of the uploaded file in the POST body response.

CREATE OR REPLACE PROCEDURE ADD_people_pic
(
  P_ID IN INTEGER 
, B_PIC IN BLOB
, BLOB_SIZE OUT INTEGER
) AS 
BEGIN
  UPDATE PEOPLE SET BIO_PIC = B_PIC WHERE ID = P_ID;
  COMMIT;
 
  SELECT DBMS_LOB.getlength(bio_pic) INTO BLOB_SIZE FROM people WHERE id = P_ID;
 
END add_people_pic;

Very simple code, definitely not production worthy, but it will load a file to a table, assuming you get the PK right. So let’s see that ‘live.’

I have a table, I have a person. Now I need to find their PK, and make sure they don’t already have a picture. Then we’ll make the REST call, and go re-check the record to make sure everything’s OK.

But Jeff, I really want this to be REST-y.

Ok, let’s look at the GET we have on the item, and have it include a link to the picture.

I want the person page to pull up their data, and I want a link to their picture.

So I need to add a new URI to handle the resty’ier link for my collection item, and it’s picture.

And in my browser…

I think this is more REST-y.

Tell Others About This Story:

Comments 2

  1. Hi Jeff,

    I have problem with creating “PEOPLE” table from Your example. Both SQLplus and SQL Developer throws an error “ORA-00907: missing right parenthesis”. I managed to find out that the source of the problem is “NOSCALE” option for identity column. If I remove this option, the table is created without any problem.
    I tested it on Oracle Database 12c Standard Edition Release 12.2.0.1.0 – 64bit Production.
    Any clue how to resolve this problem? On another 12.2 DB it runs smoothly.

    Best regards
    Michal Wyszynski

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *