Touchless Deployment – How to extract your SQL objects without connecting to Database ?
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..
(This operation is not supported in SAP Commissions running on Oracle tenants.)
SQL Objects which you can download – PROCEDURE/LIBRARY/FUNCTION/TRIGGER.
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"();
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
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
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)
Explaining how to extract your SQL objects without connecting to Database
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
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.
---- 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
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.