Understanding AMDP: A Comprehensive Guide to Database Processors and Functions in SAP

what is amdp amdp is a framework to manage and call the database processors and the function

from ab itself so what i mean by the database processors and functions we are calling

from the web app so as you already know we have the three tire architecture we have the presentation layer we have

application layer and database layer in the sap but in the database layer we can have

any database suppose if you are working on the hana database we have the

sfps for hana database we can have oracle database we can have db6 database we can we can have ibm database there

are a lot of database providers are there we can use any database out of that

but for the if as sap provides the hana database for the hana database we have the

the native language we will call the sql script language we have for the oracle there is a

different native language and for the other database there is a different different native language and we have

one open sql language that is above where we are writing our ab code which is valid for the all the databases

but here we are talking about the hana database our hana database is having the sql script language

so in the sql script language we create the processors and the function

to do the day-to-day programming at the database level like it is the same way how we uh manage

our co uh our code using the methods subroutines or functions we create day-to-day same way in the sql script

language they have the database processors and the database functions they will combine the logic and put in

one container so they can reuse or reuse that particular part of code in the different different section and they

will modularize like this in the same way sql script having the processors and functions

since our database code powerful noun now what as sap

sap has given us the opportunity we can write the sql script processors and function

from the app itself using this amdp framework so for that first we have to understand

what is this database processors and database function as i told you these are nothing

just this is just like the logical content logic containers how we are having the methods

subroutines and function in our web same way these are the logic containers are there

this allows you to group the sql statement and logic in the one container so there is a two things one is the

processor another is a function so what is the difference between the database processors and database

function we have to understand the difference because

amdp also create the mdp functions and mdb processors to understand that difference first we have to understand

what is the difference between the mdp processor and mdp function so

mainly the we can say normal difference

the mdp functions always have one return parameter but the

processors it's not mandatory to have written parameter

and but the function it should have one return parameter otherwise it we will get the

syntax error if you write the code in sql script at the database level the time they should have the return

parameter that's a very important thing and the function we can

uh where how we can call the function normal wherever we call the table

uh in the select statement same places we can call function as well we can paste the data from function like

we are getting the data from the table we can write the operand position and the where close as well so this function

can be called there but the procedures for calling processors we have the syntax we have to use call processor and

we have to pass the values these are the native syntax native database syntax in ab we call the processor normally which

we are going to discuss and the in the procedure not only we can read the data we can write write data as

well we can update the data in the database but in the function we can only read the data these are the basic

difference between the processors and the functions to define the database processors and

the database function in the database from the app we use the amdp framework

so amdp framework manages the two things one is the amdp processor and second is

the imdb functions amdb functions further divided into two parts mdp table function and amdp scale up functions

in this video we will talk about the amdp processors we have already discussed about the amdp

table functions in my previous videos you can watch out mdp table functions from there

but in our upcoming videos we will understand what is this amdp scalar function and what is the difference

between the mdp table function and mdp scalar function and what is the use of this amdp scalar function

in this video we will see the mdp processors so first what we will do we will create the basic mdp processor and

we will do what all are the things we have to make sure when we are creating the mdp

processor and when we create the mdp processor in the app level and the database level it will create the

obviously sql script processors and how it will convert from amdp processor to the sql script processor

that also we will see let's get it started for creating the amdp processor what we have to create first

normal ab class but before starting most important thing we can create mdp processor only in

eclipse edt tool we can't create the

mdp processors in sap gui why we can't create i don't know ask sap so for creating that first what we will

do we will create the normal ab class so i have already one package with the mdp

what i will do i will right click on that package let me close it out right click on

package new object i want to create the abac class

so amdp processors and amdb functions in the above these are we create as a method so that is the reason we need the

class so class also first we create the normal class so let me create this a web class

and let me give some meaningful name jcl underscore amdp amdp class i can say because this i will

be converting this class to the mdp class let's give some description just click on next button

and just give some tr name and click on finish button it will create the

basic class let me minimize it

project explorer now my first step will be i have to convert

this amdp this normal class to the amdp class so for converting this normal class to a

mdp class i have to use one interface which is provided by sap in the public section i want what is

the interface name if underscore amdp underscore marker you do control space

you will get the name of that interface you can do enter and just you have to declare this interface in your class as

soon as you declare this interface and that's your class this class become

amdp class we have created the amdp class now we have to create the mdp processor

for this we have we need some requirement so my requirement is that for for that

what i will do i will open the vbk table let me open vba database table and show you some data

let me do f8 so it's asking from the let me do data preview

i can select column letter point of time as well so i need i don't need this many columns

i can select columns from here i need only vbln deselect all

i need vblm second is the net amount and third is the

if it is so this three column i need so what i want to display in my final

output suppose there uh for one customer is having the multiple sales order so what i want to get for that

particular customer how much total amount he have bought i want to combine the for the total amount

for the each customer then finally i want to get who all are the customer which is the

buying from me more with more amount for example there is 10 customers 100 customers top 10

customer which is buying before that for those customer net amount is more i want to display those customers name along

with the amount which we they have both from me and apart from that i want to display the

list customer as well which is buying very rarely or which which they are not buying it at all

for the reporting purpose this is my requirement so i i need to field first build is the customer name and second

field is total amount which they have bought from me

like that i want to display so for that i need obviously uh to get the data i want to create the types also

so in the types i'll let me create one type begin off

ty underscore thrust to make it shorter first field will be custom name

customer name uh that will be coming from the na1 table

because is not having name so kna one name one is the customer and second

field will be the net amount which which will be the total net amount

i want to get next year that will be coming from the vba table type vba

net amount and end off this particular

this these two detail i need to get this to detail i need some method so uh so for that i have to create one

table for stable type also so what i will do i will create one method

and the important thing is that first i let me create one simple method instead of

the database processor i will create simple method method

get custom detail and i have to pass some field exporting what i want to pass

exporting i want to pass how many number of customer you want i

want top 10 customer from upper side and top 10 customer from the bottom side to see which are the best customer and

which are the worst customer for me so i have to pass some number so it can be exporting then et underscore num

number this should be and what i want to get importing importing uh

i want to get the id underscore cluster detail

first that should be type of i have to create one table tt underscore cost

so it should be like this one i have to create types ct underscore cust

the table type table of

ty underscore cast so like that i want there is some issue is there

obviously we need i think control x we have to declare importing first then exporting

control v dot dot

okay this is the sequence actually set f1 it will say that there is no

implementation is there so simply you have to click control one create implementation

it will add the implementation port this one and let me activate this one control up

this is how we create the normal method in ab now

this is the normal method i can write the code in the ab and it will be returned and i can get the older data

from the vba k and sort it by order by and top 10 i can sum up and i can get the data but i don't want to get using

normal open sql i want to do this one directly natively in the

native language in the using the mdp processor so what

so first important thing whenever we're creating the method

in the mdp class it's not necessary that all the methods will be the amdp functions or mdp processors that

can be normal method can co-exist along with the amdp functions in mdp

mdp processors so let me create let me convert this method to the mdp processor so what a lot of the things we have to

make sure when we are converting the uh this particular uh this particular method to the

amdp processor the first thing whenever you are exporting some value

it should be not passed by reference currently though it is uh i hope so you know what is the pass by

reference and pass by value so here we are as of now we are passing by reference just we are passing the memory

address but we can't do with the uh with the mdp processors this pass by reference because

this amdp processor will run on the hana database and our hana database the problem is that that these memory

location will be not available at the database level because this memory locations

are available at the application cell or application server level and our processors will run directly at the

database level that is the reason we have to pass by value first change we have to do we have to instead of using

now using pass by reference by default it is passed by reference and when we give like the value it will pass by

value instead of pass by reference this is the first change and exporting etinum what i did

i want the top 10 customer i want to not i t curse two table i want i want to top customer and second in the second

second table i want the value flop customer

i can say which is not buying for me at all i want to check those customer also

type tt underscore custom those detail and those details also i want and these are the normal thing this is

the method one most important thing one other things what we have to make sure since

this amdp i want to convert this method to the amdp processor

and amdp framework never do the client handling because

it will directly run on the database it will not go to the database interface which is available on on the application

server it is directly connected like how we run the native native statements directly connected to

from the database to the there will be no interconnectivity between so it can convert open sql to native sql because

we are writing directly in the native sql itself so it will be run directly on the database so there will be no

implicit client handling will happen so that is the reason if you don't pass here the client

when we get the data from the database we will be not able to pass the client when we are writing the select statement

and we will be we will get the data from all the clients so that is the reason i want to pass the client field as well

here because i want to get the data for the current client only so for that i have to pass one more

one more value over here which is client again

c-l-i-n-t and it should be type of mandate

so these are the two things we have to make sure when we are doing the processor declaration but here we can't

differentiate between the it is the normal method implementation or it is the

uh it is a mdp processor implementation normal method also we can pass by value

so here we can't differentiate between it is normal method or it is a mdp processor or imdb function

so for differentiating that we we have to see the implementation part of this particular method

in this implementation as you can see to make this particular normal method to the amdp processor we have to write by

database and if you want to convert in processor so you have to write processor and if you want to convert in function

you have to write the function by database processor and second thing i want to say for which database you want

to create this procedure so before i want to create hdb means hana database in future if some other database also

supports the amdp we can give that database name our name also over here suppose oracle it is

something odb or something else some some other or db6 is supported and we can give the db6 name here so some other

database will support the mdp that time we have we will give the database name is that as per that particular

database currently we are running this particular method on hana database that is the reason we have given hdb second

thing we have we have to give the language what is the language of your database i know for hana databases it's

it's sql script language so i have given the sql script for other database there is some different native languages there

we have to give that language name one more thing you might have noticed what is this error i am getting

cannot be used as a name for the parameter okay let me use something because it is reserved

value mandate i can use yeah

so this is the error client is a reserved key field so this is the reason we can't use as

a name so i have changed the name to the mandate so one more thing you might have

noticed if i click enter enter enter this color code change over here so it soon as we have

write this database processor why i have changed this color where how to change this color that also i will

show you mean a while it will tell me that you are whatever you have going to write in this

particular blog this will be this will be the sql script language this will be not normal

language how to change this color you have to go to the windows you have to go to appearance

so i or recently changed that is the reason already there you have to go to the general then appearance then color

and phones are there inside this you have to inside the at the below above you can see the ab above and inside the

back we have something called syntax color coloring inside that there is something called embedded language

background color or directly you can search embedded language also that time also it will be displayed over here you

have to select this one then edit this one and you have to select as per your requirement what color you want so i

have choose this color that is the reason whenever i will write the database processors and database

function it will come automatically in this color let me cancel it out so that is again

important set setting in the eclipse so we can easily differentiate when we write the code this is normal method or

it is amdp processor or imdb function so this is the part of the implementation

so i am getting the error like that the body of an amdp

method cannot be empty obviously it can't be empty so i have to get the i have to fill this top cast table with

some data so i will write the select statement select what i want to get i want to get first the customer name from

the kna1 table so top 10 entries i want to get so that is the reason i will tell top uh that 10 i will pass from the

exporting parameter 80 underscore num and um

and what i want to get the customer name so i will gonna give the customer known as

a cost is alias name custom dot name one is first

underscore name comma second field will be the our the summation of the

uh summation of the net price so some some of the as show underscore hat table htt that

again alias name i will gonna give so dot head net price

and this will be i want to display is

netwear okay and this will be comma separated

et num everything looks good or you can give any meaningful name so

these two field i want to get from where from first

from vba is so underscore had

hed i want to get a and i want to do the inner zoning

with the k n a one is uh what i have given the name

uh cost c u as t and if i am doing the inner join

obviously i have to pass some condition on and that will be the condition our so

head control c whatever the gunner i am getting from the vba k table this should be equal to

the customer vba table i think

and once i get this data since i am using the summation i have to use group by obviously so the

group by and i have used two field only so uh apart from the summation what are

what else group by customer name so what other fields we have to mention

in the group by as i already explained in the group by video and you can close it

but i want top 10 customer so that is the reason i want to get the top 10 but with which top 10 after the order by let

me do order by uh order by this amount

and you have to pass additional ascending was a i think this is the ascending

order by is sc i think ascending order that is the reason i'm getting some error what

is the error okay this is the this is telling that vba k is unknown one most important

thing whenever we are using the database processor then we have to mention these

what all are the table you are using from the current schema so once one more important thing whenever this particular

database processor will activate it then it will be saved in one schema of the database the schema

will be the assigned to the application server web so that particular in that particular

schema only it will be saved or whenever we are accessing other object from that particular schema we have to mention

those all the object here since i'm i'm using the vba table from that

schema so that is the reason i have to tell vba k and k na1 these two table i'm using so that is the reason i have i

have to mention this table name otherwise if you are getting the data from the other schema no need to mention

here those schema name so these are the things let me do say f1 let me do

there is i'm getting error it is telling invalid column name okay i have to group by name one control c

ctrl v september group by name one and i got as a

customer name okay so like that i go i want to get the top 10 customer i i i i got the all the customer first then

i did the submission then i sorted those customer by the uh ascending order in this case it should

be descending not ascending so it should be d

sc d esc i think so descending order so this is the descending order

means uh top customer will come first and then letter uh other customer will come and i will fill this top customer

table like that we can do so this is the my first statement same way i can fill the other table also control c because i

have to fill not only the top customer i have to fill the bottom customer flop customer also so what i will do

just you have to do you have to copy this flop customer control c

control v i didn't give the correct naming convention that is okay just i gave some name and instead of descending

order i have to do ascending order in this case live with the list amount

the customer will come first and those customers will be displayed except f1 shift f1

left f1 let me try to activate this one i i i'm not getting any hope now any syntax error the most important thing

now previously in the mdp the syntax error was not supported it was not giving error but now i know but in your

version the sap support the syntax error as soon as you get get the error you will give you

will get that error you have to fix that error and then you can proceed ctrl f3 it got activated now we have created the

amdp processors and as soon as we use first time this amdp

processor in any report or somewhere in any class method or we call this a mdp processor it will be created in the

database so let me create one report and call this particular

mdp processor implementation in my report to create

to create a program what we have to do we have to go to our context menu project explorer you

have to right click on that your package name and you have to create the

new about repository object and you have to give the program

so you have to select about program so you you can write z underscore amdp

underscore consumption or something like something meaningful name you can give and you can click on next button

give some description give some tr and finish it up it will create the normal report program

so in this normal report program what we will do we will call our mdp

processor it's not the much different uh how we are calling normal class and method so i know the class

name so what i have to do ctrl c so simply i have to come here control v since it is a static method if

it is instance method also i can create as a mdp processor i have to create instance but in this case it's a static

method so what i have to do control space it's not static it's the instance method so i have to create the

instance of the class so i can do data or i can create new data

lr underscore custom equal to control v

and i can create the new class

it will create new instance lr underscore cost and from that i can call

i can directly call here as well but i don't want to do that one i can call the instance method of that

what is the instance method get customer detail you have to do shift enter i think i did some mistake you can see

the i am put i am calling the exporting it should be importing parameter so let

me interchange again uh what i will do ctrl x

and ctrl v this should be ctrl x but this should be here

dot and this should be ctrl v and remove this one and set f1

and then activate this one control f3 let me call this lr underscore first get customer detail again

so i can do control space and i can call this control space

shift enter so you can see it got cold so here i will pass the some number i

want to get 10 time i want to pass the client

psi mandate and i will do the inline declaration

for these two so data under i t underscore top

and this will be data i t underscore

after doing this this let me display out this table cl underscore demo underscore output

control space shift enter i can write it down first write it down first thing

it underscore top and let me do a ctrl alt arrow down button and this time display instead of

right thing display display and i will display the id underscore

flow control c control c

control v step one let me activate this one ctrl f3

before executing this program we will put the breakpoint one breakpoint in our web program

and another breakpoint we will put in the our

mdp processor implementation so let me take it both the things together and let me do f9 so i want to display

the output in the console as soon as i do the f9 the debugger perspective got open

in the debugger perspective you can see there is two things one is the amdp debugger and another is the above

debugger now currently we are we are in the ab debugger if i do f5 still we are in the above debugger and

we didn't go inside this implementation of this particular mdp processor let me decrease little bit

this and let me do f5

now it will go from the above debugger to the mdp

processor it's taking time so you can see it came to the

this particular cursor came to the particular mdp processor implementation this one now i want to see what is the

value of this customer top cost currently it is blank you can see there is a zero entry and here we can see also

there is no entry let me do f6 here f5 will not work you have to do f6 and you will get the data you can you go

to the 10 entries and what all are the 10 entries you got you can see these entries here this code is running in sql

script directly database level let me do one more thing one more time f6 i can see the flop customer as well so you can

see the flop customer as well there we go 10 entries again let me double click on that so you can see we've got the 10

entries flop customer and you can see the all are the zeros and apart from last three where we have some values 10

48 and 200 and let me do finally f8 now let me do f8

and now the console got open switch to the about perspective and we can see the result we go the top

10 customer which is having the more amount values and we go to the top flop 10 customer which is which is not

buying at all or buying the very less amount or number of sales order might be less or more that we are not sure but we

are sure that we can display the number of sales order as well but just just to displaying purposes of

now we have displayed this amount how much amount they bought and based on that we can make some decision

at last i want to explain some of the theory points so let me put this console here

back i think it is not required let me close it out

and i want to pull some theory point which i have saved already in the template uh so let me do

control space mdp theory is that a lot of points are there some of the points relevant for today and some of

the points are relevant for the next session uh we will discuss in next session so these are the points relevant

for the two days session the first one just some of the point will be the revision whatever we

have discussed and some of the points will be the newer points which we are going to do

first thing an amdp method as soon as we will activate this imdp method or when we use first time this amdp method

it will create this database processor in the database one more thing i don't have as of now the database access so i

i was not able to view how the database in the database how the this particular database processes got created but if

you go to the app the schema of the your current database how to see that schema i will show you next session but

the name will be your uh the name will be your database processor will be like this

ctrl c and ctrl v in the capital so you have to do control

shift u to convert in template capital control shift u control set sorry see this will be the your database

processor name okay and as soon as we use first time it will

create this database processor in your ab database schema which is schema i will show you next session it can be

static is insane method i told you we created is instance method you can create as a static method also but when

you are calling you have to call like the static method second point third point it can be declare any visibility

section okay now i have declared in the public section you can declare in the protected section or private section but

once you declare in the protected section it can be called in the subclasses only and you if you declare

in the private section you can't call outside this class you have to go you you have to use this method inside this

class only these are the basic concepts so it even called an invisibility section there is no

restriction over that it can be declared a declaration we can't say amdp or not as i told you

why are seeing this method declaration we can't say that this is the amdp method or it's a normal method we have

to see the implementation part then only we can see by seeing this by database procedure which database and

language by seeing this only we can say this is the mdp processor implementation this is not a normal method

return value cannot be declared if i try to use some returning value here i i will not able to do it will give me a

syntax error if i try to use returning value i will get some error

let me give some some value returning i t tape type

dt underscore cost only i will get some error like this a returning parameter

must be fully typed okay let me put fully typed with empty key

so now it is fully typed with the default key sorry so you can see now what error we are

getting changing a mdp method changing exporting parameter cannot be used combination with the returning parameter

so it is saying that you can't use because it is a processor if you want to create a function there you can create

the uh you can create the returning parameter that is the reason i was telling it is not possible

to create the mdp processor with the returning parameter that is the statement i didn't

say anything this is the all state documentation saying that the database objects in the current database schema

accessed in the imdb method must be declared using the so as i told you we have to declare whatever the database

object which we are calling inside this particular in processor implementation we which is if we are calling the those

tables or those functions or those processors from the current schema we have to write those all the database

object after the using if you are calling from the other database you have to give the obviously the schema name

that that time it is not required to use those names here okay

and uh the amd framework does not support the automatic client handling that is the reason we have passed the

client and then we have we did the client handling that is also these are the basic points

regarding the amdp declaration some of the point regarding the mdp implementation also there mdp method and

mdp implementation method without a return value same point

mdp processor implementation with the addition by database processor we have to write that is also an amdp method

should not be empty obviously if it is empty we are getting the error so these are the basic things

these three last three things bit a little bit complex or i can say little bit uh different

rights cannot be performed on the database table where the table buffering is switched on switched on what it is it

is saying that if some of the table which we are accessing here in the

currently i didn't modified anything in the in these tables i just got the data from

this table i can modify those stable data as well but you should not modify those table

which is buffered on the application server why you should not modify because that data is already

offered on application server if you try to modify the synchronous means synchronization between the application

server and database server will not happen and you there will be lot of data inconsistency between the application

server and database server so that is the reason it is saying you should not you should not write

cannot be performed on database table where the buffering is switched on and since the sql script access are ignored

by by buffer synchronization buffer synchronization

work is that it will synchronize the data between the applications server and database server but in this case the he

it will not know that something would change because we changed using the sql script language directly at the database

level so that is the reason we should not do anything

more any modification on the data where the those table which which is buffered on the application server

amd did not have the implicit enhancement options we will talk about the announcement also we can do the

uh we can create there's there is something called the amdp bodies also there but

there is no implicit enhancement option if you open sap ui and click on the squirrel button and

show implicit announcement options you will not get anything here it it will not have any implicit enhancement

options and ddl statement are not allowed we can't create the new since it is a sql

script language so in the sql script language we can create the table we can create the database processors using the

some statements so those is not allowed in this we can read the table you can change the data in the table but this uh

these things we can't do the means what what it is saying you can't create the details create change and deleting of

the database object is not allowed in this sql script last thing no database commit and database rollback statement

are allowed because database commit and rollback we have to handle those all the things at the application layer not at

the sql script layer that those are the most important things you don't know what is the database commit and the

rollback uh what is sap logs and database logs i already created those in detail those videos in my previous

lesson this that that all you can watch so these are the basic points which we have discussed today that there are some

other points also which i didn't discuss we will discuss in our next session in this video we have learned about the

basics of the amdp processor implementation and lot of basic things and theory part

as well in my next video we will take up from here we will add lot of additional things and we will try to understand

additional things like how the interface of the amdp processor and converted into the

native database processor what is the mapping how the mapping is happening at the database level not only

that thing we will discuss a lot of other things as well before going to that video please like this video share

this videos with others as well so that they can also learn with that thank you and happy learning