So in today’s lecture I want to give you a overview of this application development process. So we have seen that applications play an important role in the whole setup. So what are thevarious approaches that are available for developing the applications we will see in that. Beforewe do that let us see have a discuss the overall system architecture how is the whole thingarchitecture. The database and the other machines that interact with this database. So one of thefirst things that were tried out was long ago, before even the PCs were born, there used to bemainframe systems and then the centralized architecture was practiced.In the sense that all the complete DB functionality, the storage, running of the applications,transaction processing, concurrency control, all of that is on one system with the central server.And then the whatever the access system are used to be dumb machines. So they do not havemuch processing capability, they were just like terminals, right, so that is the initial architecture.But nowadays we go for multi tier kind of architectures.So the basic, you know client server architecture is, what is we will use in this set of slides. Sowe will talk about the 2 tier kind of systems, where the client is you know reasonably powerfuland then it can do some local processing. So typically PCs and workstations connect todatabases. So they can do since they can do some local processing, it is possible for us to youknow run the user interface and even the application programs can be run on this systems, so theclient systems.And then they will be sending the database access request alone to the database server. So thedatabase server is the one that holds all the data authenticates the users who are supposed to usethe data and then grants permissions to use and also keeps track of all the modifications all thecomplete functionality of databases like this taking care of the storage, transaction, processingand concurrency control, recovery all that is in the server ok.So this is one kind of popular architecture you would also see actually this is the basic kind ofyou know client server architecture. It is also possible for us to have 3 tier architectures wherethe kind of the middle layer actually has and these applications are actually moved to the middlelayer. So the middle layer is called an application servers layer or even actively web server layer.So the clients will do only will have only the user interfaces like they collect the parameters fromthe end users and then invoke applications themselves run in the middle layer and then thedatabase has run in the last layer. So the database servers will be in the last layer, so that kind ofmulti tier architectures also are possible. Now the application we will for illustrating the principles we will just take 2 tier systems intoconsideration. So we will call this whatever the language that in which the application is beingdeveloped as a host language. So it can be t typical like C, C++ or Java. And then we will discusswhat are the various approaches that are there for managing this database access ok. Severalapproaches are available broadly they are like this embedded SQL approach, where SQLcommands are embedded in the host language programs.This is some sense a little static kind of approach we will see why it is called a static approach.And there are actually both are possible something called dynamic SQL also we will discusswithin the same embedded SQL approach. Then we have there is something called call levelinterface SQL CLI call level interface, this is part of the SQL standard. So this is a complete inAPI based approach application program interface. So there is a bunch of library programs,library function calls that are made available by the RDBMS vendors right.And that will constitute the application program interface. So using these API calls, it is possiblefor us to interact with the database. So that is one approach and we will see why that approach issome details about that approach. And then for the same kind of approach, if we are doing itthrough Java programming for Java, then we have what is called JDBC it is an approach wherewe have an API through which connections to the database servers can be established and thenwe can work with the database servers.So to illustrate the principles I will focus on some of these things. So the third approach whereyou can even have a dedicated database programming language a dedicated programminglanguage itself can be used. So the example for this is Oracle's PL SQL is a programminglanguage slash SQL they call it PL SQL. So this is their own proprietary language which hasbeen developed it is a general purpose language developed with the purpose of developingdatabase applications ok.So this embedded SQL and dynamic SQL we will go into a little bit detail in some detail otherapproach I will leave it to you to kind of study yourselves ok. There is one term that is used in this context called impedance mismatch. So this one actually theterm itself comes from electronics kind of domain because impedance is normally used inelectronics, circuits and things like that. But anyway it basically, what it talks about is itillustrates the problems that might arise due to the difference between the data model that is therein the host languages, typical programming languages versus the database data models.Of course, you can see the differences are in kind of 2 forms first thing is the data types of thehost language and then the data types provided as part of SQL can be different right. So you canmostly of course, it is they are kind of you know one can map the various data types that areavailable in SQL to kind of nearest you know data types in the host languages.Another major thing is that the host languages you know typically cannot deal with a bunch ofdata records at a time. So this is one major difference, see SQL when you give an SQL query tothe database server. It gives you a bunch of tuples, a collection of tuples, sometimes even it is aset or sometimes it is a multiset right. So a whole bunch of tuples will be written t. Now, whereasin a typical programming language scenario you have a if you are opening a file of records, youwill open one by one and then you know operate with them alright.So you do not have a means of handling a bunch of records at a time, so we need to handle this.So for handling this data type mismatches basically for each of these SQL attribute you knowdata types that are possible in the standard, what are the corresponding you know high levellanguage data types will be specified as part of some kind of a binding. It is called a languagebinding and this language binding has to be done for all the host languages it is more like anindication thing saying that this is the nearest the host language data type that we can make useof.So and in order to handle this query results issue, we need to kind of have some kind of a datastructure and then provide an iterator to go through those data structures. The data structurecould be as simple as an array of you know records and then you have to provide somemechanism to examine the rows of that particular array. So that is how we will handle this entireissue of impedance mismatch between the data model of relational databases versus the datamodel that is available in the typical imperative programming languages ok.So with this kind of background, we will see how ok, so one final point here is that this kind of aimpedance mismatch will not arise if you are using a dedicated database programming language.Because this would have been in some sense taken care at the designs of the programminglanguage itself, the programming language will have constructs to handle bunch of recordscoming from SQL queries and things like that.So this impedance mismatch does not arise in the case of dedicated database programminglanguages. But there is some downsides to using dedicated DB programming languages. Now let us look at one of the simple and easier to use approach which is this embedded SQL. Soin this embedded SQL will gagging assume that there is a C, C++ or Java as one of theimperative languages as the host language high level language, we will call it HL. What we dohere is to embed SQL commands statements interspersed in the program itself. Then of course, ifyou put SQL commands inside C programs obviously the C compiler will grip and then it willthrow it out right.So we need to have a pre compiler, so you know in order to distinguish this SQL commands thatI have been put inside in the C program. So what the pre compiler, so you have to give someindication as to where SQL commands start and things like that. So we will use some reservedwords like this Exec SQL in order to indicate, where SQL commands are coming in C programs.And a pre compiler will replace all these segments, where SQL commands have been put by anappropriate library function calls to the functions that are provided by the RDBMS vendors. So ifyou are using a DB 2 database provide by IBM vendor.Then you will typically have the runtime I mean the library package that has been developed inorder to handle this embedded SQL by the vendor and that will be included in your it has to beincluded in your C program. And then the pre compiler will replace all these segments, whereSQL commands have been used by appropriate function calls to these functions ok. So that ishow it will work and the kind of data transfer from the database to your program will happenthrough specially declared host language variables ok. So we will see how exactly this host willdeclare this things ok. So you need a bunch of variables, so in order to be used in inside SQL commands, SQLstatements and so these will be declared as part of a special section. So and these variables arecalled shared variables ok. So let us look at this set of things here is ok this is a comment, so thislanguage it should actually be here. So this is EXEC SQL begin declare section. So this is whereyou know all the declarations have to be given and you can see that these are the typical variableand arrays that are declared inside a C program.And these variables are available for your rest of the C program. You can you know assignvalues to them you can make use of those values and all that but they can also be used insideSQL commands. And if you use them inside the SQL commands, actually we should distinguishthem and then we will do that by actually putting a colon before these variables. Of course noticethat these variables have been declared keeping in mind this schema of the student relation thatwe have. So, all these variables will correspond to the attributes that are there in the studentsschema.So when we use them in SQL statements these variables names you know will be prefixed with acolon. So colon roll number in an SQL command a statement will basically refer to thisparticular roll number variable. And of course in your C program, of course you will use thisshared variables as the r right without any colon and things like that ok. So this is how you knowshared variables can be used.In order to basically pass data from your environment, where your C program is running to thedatabase environment basically, so through these SQL commands. So you will initiate thesethings in your program and then use them inside an SQL embedded SQL command and thenthose values will be passed on to the database server that is how the whole mechanism will workok. So let us look at some issues here. Now, one other thing that you have to be worried about is the occurrence of errors ok. So what ifyou have given an SQL command, but then you know there is some issue in the data inside thedatabase and what you wanted to execute actually does not is not feasible for some reason. So forthis we have something called SQLSTATE which is a special kind of a variable through whichthe database server will indicate as to what happened when you gave this SQL command to thatserver.So if everything that is you know if the command is successfully run then it will be set to 0.Otherwise some non 0 value will be set and that non 0 value will actually indicate to you as towhat kind of an error has happened during the execution of the command. It says this is a stringof 6 characters and you need to actually declare this. So SQL state is going to be set to a someappropriate value by the RDBMS run time system ok.After executing each of these comments, so when your program is running you have issued anSQL command. And it goes to the database server the database tries to run it and there is someissue and then it will set this SQLSTATE. So this is part of the shared variables, so it will set thisvalue to appropriate thing. And so in your program after issuing each of these SQL commands,in the immediate next statement you must check what is the value of the SQLSTATE it iseverything fine or is something wrong.If something is wrong then you should program the appropriate sequence fractions based on that.So non 0 values indicate errors in execution and it is part of the standard as to what those non 0values stand for and things like that you must refer to the standard to figure out all that. And thenbased on that you should put some series of cases and things like that and then handle thesituation.So this SQL state is a special variable and it has to be declared in the declared section. I did notshow it in the previous slide, but it has to be there in that as an array of 6 characters as a string of6 characters ok. So this is how communication between the your program and the database serverhappens. So database server indicates things to you through this SQLSTATE. Now before you start working with the database it is required that you set up a connection to thedatabase it is required that set up a connection. So while doing that, you will be specifying as towhat is the particular server which host this database. And then and your credentials you knowyou have to authenticate the application has to authenticate itself to the database server.So it has to give the user name password and things like that and typically, it is possible for theapplication program to kind of work with multiple database servers at a time. But at any point oftime, you know only one connection can be active as far as embedded SQL is concerned. At anypoint of time only one connection can be active that means you are connected to one databaseserver at that particular point of time.Of course you can once you are done with that particular database you can close that connectionand then reconnect to another database, so that is possible. So these are the SQL commands thatwill handle these connections. So connect to what is the server name and then there will be aconnection name that is going to be given for this particular connection. And then immediatelyyou will have to follow it up with authorization in which you give user name passwords andthings, so you authenticate yourself.And in order to kind of changed a different server you would basically use this command to setconnection to a new connection. Of course you can disconnect you have to disconnect from oneserver in order to before you can connect to another server ok. So these are all part of developingthe application program, so before you do any actions you basically set up a connection. Let us look at some examples of typical SQL statements and then we will look at how to handlethis query results and all that. Let us say suppose we collect the data through some graphical userinterface into these variables. So say roll numbers, student name, degree, year and all that, so wehave actually collected some data from the end user, now this has to be basically inserted into thedatabase.So you can issue a command like this, so EXEC SQL insert, so this is the standard SQLcommand to insert tuples into database. Now we do not have a tuple of values, but instead thosevalues are in these host language variables right. The host language variables we have set upthose variables have been initiated for with these values. And so that is why you would give atypical command like this insert into student table values.So instead of giving directly values, you will give the variables that hold those values and younotice that these variables are all having colon before that. Because they are the shared variablesthat are being referred in the SQL commands. And then you will so this command would go tothe database server and into try to insert it and if you have not created the student table before.Then it will crib obviously and then you know you will have to check this SQL state to figure outwhat has happened after that. Now let us look at handling query results and in this context we will bring in a term called cursorinto the picture. Though you will be able to handle a set of records in the sense of you knowopening a file of records and then operating with that in a host language. We need some kind of adata structure to kind of you know receive this results from the database server that is the wholeissue about this mismatch.So what is a cursor is basically is a mechanism which allows us to retrieve 1 row at a time 1tuple at a time from this result of a query. A cursor is required when you expect multiple youknow tuples being written by the thing. If you very well know that you know 1 tuple is going tocome, then you actually do not need a cursor ok. So for any SQL query, we can declare a cursor,so as part of the embedded SQL commands, we can declare a cursor for a particular SQL query.So once you declare it, it will have a name a cursor will have a name and then you can use thatname to kind of open that cursor. When you open the cursor the SQL query will be sent to thedatabase server and then will be executed the results will be available in some time kind of atemporary storage. And then you can fetch those tuples and then move the cursor etc, and thenfinally close it.So when usually we will need this when the embedded statement is in a select query thattypically returns multiple tuples. Of course, the insert, delete, update all these things they do notneed a cursor because they are dealing with. Even if they deal with multiple rows, your input isunique for each of these things, right. So you do not have to deal with results from the databaseserver ok. So here is a case where we do not need a cursor, so here is select s name s dot sex into look atthis new clause. So you can now give variables in your program which will receive these valuesinto and then you from student s where s dot roll number equals. So you already have initiatedthe roll number variable with some specific roll number. And now you want to give thiscommand saying that get me the name and sex values of this particular student.So from student s s dot roll number equals particular roll number, so the values. So since rollnumber is a key, you know that you will only get 1 set of values. And so there is really no needfor setting up any cursor for in this particular case even though it is a query. Whereas typicallythis is not the case in general and you would required to declare what are called cursors. So look at this query select s dot name s dot degree from student x where is student sex is F. Soall name and degree details of girls students you are getting. So obviously there are multiplerows and so you cannot use this into approach because it is a problem, so here is how you wouldhandle that. So here is a command, where you are declaring the cursor for a particular query. So it is calleddeclare student info is the cursor name. And this is again a keyword cursor for and then you givethe query. So once you declare this, it is there you know as a definition alone, only when youopen it, if you give an open command, open the student info cursor then the command actuallygoes to the database server.And then you get a bunch of tuples returned by the database server. And then they will be youknow in some kind of a temporary storage it is all handled by the by the embedded SQLapproach now. So the pre compiler will take these things and then appropriately create libraryyou know function calls to handle this kind of situation now, it will create some temporarystorage and things like that.And then get ready to handle various commands related to cursors ok. Now to read the currentrow of the values into the HL, the host language variables, you basically use this commandFETCH and then give the cursor name INTO. And then a sequence of variable names as many asthere are columns in that particular. Now after this FETCH then the cursor is pointed to the nextrow by default.But of course you can actually control as to whether the cursor should move to the next one orshould move to the previous one or should actually jump to the fifth one next or etc. So all thosethings I have, they are there as part of the cursor mechanism, but I have skipped mentioned themin the slides. But there are this command, the FETCH command is actually much more complexthan what I have shown you here.It can have optionally other parts where you know you can say that ok after doing this you jumpto the fifth row or something if you, really needed like that. So you can control the cursor as towhere it goes, it can be optionally controlled by the programmer. Then after reading all therecords that have come, you can actually close the student command that particular cursor. Sothis is how you kind of you know handle this situation that the database server typically returnsset or multi set of tuples.And then you have to make use of those values in your host language program. Maybe you willjust simply display them or print them or something like that but then you need to get hold ofthem or maybe you are actually examining each of them and then trying to figure out the nextactions to be taken extra ok. So this is how embedded SQL typically works. And then let us move on to something called dynamic SQL where ok, so if you are usingembedded SQL then you know the SQL query or command to be given at the time of writing theprogram application program you know what exactly is the command to be given. You may notknow the exact parameters ok, what role number to give and things like that. You may not knowthat you will pick up from the host language variables which are initialized through some otherfunction.And then but you know the command right that is what you will embed here ok. So what if youdo not know the kind of query that you need to post to the database server, what if that query hasto be figured out at runtime. So this is one major issue and in order to handle that, one simpleapproach that is as part of the embedded SQL is to do this. So you have a so it will give youcommands like prepare and then execute ok.So I will show you so basically, what prepare does is to take a string we call it SQL string here,but you can call it anything. Take a string and then treat it as a query ok, so it is a characterstring. And this prepare from will basically prepare some query called runQ from this string ok.And then if you EXEC execute that thing then that particular query will actually be executed andthen you will start getting the results and then you can use cursors and things like that.Now how do you, so this particular string is actually a character string that is declared in yourprogram. And in this case of course I will simply you know instantiated with a constant stringlike this here. But it this may need not be the case, what typically does do I mean you will checksome various conditions and then probably interact with the end user and then depending on theinput that you get from the end user. You decide as to what are the tables from which theinformation is needed to be you know obtained and then what are the conditions to be.So you will actually dynamically form the query and then you know putting characters into thisSQL string to reflect that query got point. So you will at run time after interacting with the enduser, decide as to what is the query to be fired to the database server and formulate that queryinto a character string. And then use this prepare, runQ from SQL query from that string and thensay execute.So why it is split into 2 prepare and execute is basically that you may want to run that querymultiple times after having prepared it ok. So that is why this separation, so this is very brieflyabout this, you know dynamic formation of SQL queries which is actually very much required.Because this makes your program highly you know flexible. In sense it can handle the end userrequests various different kinds of end user requests and then has the ability to dynamically formthe query and then send it to the database server ok. Now I will briefly touch upon the other approaches here which are the APA based approaches.So before SQL CLI was there then there was this ODBC open database connectivity, againfunction set of function calls. And then JDBC is also similar kind of thing which is mentioned, itis to be used for application programs being developed in Java. So as against the embedded SQLapproach the approach here is to access the database through this API function calls.And who provides this APIs obviously the database when it is actually part of the standard SQLCLI and JDBC ODBC, these are all standards. So there are such standard function calls that havefunction calls have been standard as and how you implement it will depend on your databasesystem. So each database management system vendor will implement exactly these function callsand then give you a database DBMS when a JDBC driver for his or her for their system right.So the advantage of this using this approach is that it is pretty flexible you can you know, youcan dynamically prepare SQL queries and things like that. And also many databases can besimultaneously accessed and there is no restriction on how many number of active connectionsyou can have with databases. And then of course in order to use them, you will requireappropriate these drivers.These drivers are the ones that are provided by the RDBMS vendors who will implement thesestandardized you know these SQL CLI and JDBC they give you the templates for function callsand those are implemented by the database. Since they are standardized, so you can actually useyour program in order to kind of access either a oracle database or a DB to a database or anyother database as long as they provide you this JDBC or ODBC connectivity they ok.So typically how it goes is, so at runtime you select your data source and then load theappropriate driver dynamically ok. So you are so you figure out that it is a you are interactingwith an oracle database. And then you will load the oracles a JDBC let us say you are writing aJava program a JDBC driver. And then that will give, so I am of course not giving details aboutthis but it has now various function calls in which now you can establish connections you canmanage connections and all that.So using those function calls you basically establish connections, authenticate yourself,authenticate the program and basically then pipe the SQL commands that you want to actually.You may either have them statically with you or you may dynamically prepare this SQLcommands and things like that. So you will pipe the SQL commands to them and then the closeconnections. So this is a typical this is kind of required and this is how you work with thesethings.