In this blog, you will come to know how to extract your SQL Objects without connecting to your live database but through Touchless Deployment. This will help you, Comp Developer, SQL Admins .. etc to validate your SQL Script which is deployed in EXT Schema..

If you’ve ever used Touchless Deployment to extract SQL objects, you know how convenient and easy it can be. With a few simple steps, you can quickly retrieve objects from your databases, saving yourself a ton of time and hassle.

Extracting these objects can reduce the amount of development time needed, as well as simplify the process of updating the stored objects in the database.

Understanding of Touchless Deployment Flow

UML Diagram to represent each steps of execution for Touchless Deployment

Let’s see through an example how to extract your SQL Objects.

Follow the below each steps which are the basics of how to extract SQL objects from SAP Commissions Database EXT Schema.

Step 1 : Prepare the sample Stored Procedure

CREATE TABLE "YOGA"
(
  "DATETIME" DATETIME
);


CREATE OR REPLACE PROCEDURE "EXT"."MY_STORED_PROCEDURE1" ()
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
	insert into yoga
	SELECT CURRENT_TIMESTAMP FROM DUMMY;
commit;
END;


CALL "EXT"."MY_STORED_PROCEDURE1"();

Function

CREATE FUNCTION EXT.GROUP_AGE (age NVARCHAR(10), level INTEGER)
RETURNS outValue NVARCHAR(50) AS
BEGIN
  DECLARE defineValue INTEGER DEFAULT 5;
  DECLARE interval INTEGER;
  DECLARE rangeFrom INTEGER;
  IF (level > 0) THEN 
    interval := defineValue * power(2, level - 1);
    rangeFrom = FLOOR(age / interval) * interval;
    outValue := '[' || rangeFrom || '-' || rangeFrom + interval - 1 || ']';
  ELSE
    outValue := age;
  END IF;
END

Trigger

CREATE ROW TABLE TARGET ( A INT);

CREATE ROW TABLE SAMPLE ( A INT);

CREATE TRIGGER TEST_TRIGGER
 AFTER INSERT ON TARGET FOR EACH ROW
 BEGIN
     DECLARE SAMPLE_COUNT INT;
     SELECT COUNT(*) INTO SAMPLE_COUNT FROM SAMPLE;
     IF :SAMPLE_COUNT = 0
     THEN
       INSERT INTO SAMPLE VALUES(5);
     ELSEIF :SAMPLE_COUNT = 1
     THEN
       INSERT INTO SAMPLE VALUES(6);
     END IF;
 END;

Step 2 : You can use Touchless Deployment to deploy above Stored Procedure by following blogs

https://blogs.sap.com/2022/06/27/touchless-deployment-working-session/

Step 3 : Verify if your Stored Procedure is displayed in your Schema Explorer
( This will not apply for Production environment – you can find it through Step 2 – when JobId is successful)

Step 4

you can execute the TD_Client Shell Script with below provided syntax. Refer SAP Help Doc to download TD_Client

you must be in bin path to get this work.

TDClient.sh -o codeDownload -type PROCEDURE -object MY_STORED_PROCEDURE1

codeDownload:

parameter will execute the request sent and gets the Job to download the custom code for type PROCEDURE/LIBRARY/FUNCTION/TRIGGER.

The code will be downloaded in the directory pointed by environment variable TD_LOGDIRECTORY.

Through Postman

---- Stored Procedure ----

Method : POST
URL : https://<tenantId>.callidusondemand.com/TrueComp-SaaS/services/rest/touchlessdeployment/downloadCustomCode?OBJECTTYPE=PROCEDURE&OBJECTNAME=MY_STORED_PROCEDURE1
Authorization : Basic username:password


----- Function -----

Method : POST
URL : https://<<tenantId>>.callidusondemand.com/TrueComp-SaaS/services/rest/touchlessdeployment/downloadCustomCode?OBJECTTYPE=FUNCTION&OBJECTNAME=GROUP_AGE
Authorization : Basic username:password

Step 5

Download the SQL Object only if 200ok is shown. Click Save to Response file and download the zip file. Extract the Zip file and you can see SQL Object.

Final End Result

In Addition to all above, extracting SQL objects can help you to better organize and debug code within the database, allowing developers and administrators to quickly correct any issues that may arise.

Finally, extracting objects can help ensure that changes do not unintentionally affect existing functionality.