Using SQL command

Introduction

In this engineering epoch, there are Numberss of database entree middleware which become a connexion between applications on client side and database waiter. There are many types of connexion ; ODBC, JDBC and connexion twine. The connexion is used for directing petition informations from API to the database waiter and besides having informations which have been requested by the client and translate to the linguistic communication that can be understand utilizing SQL bid. The thought of implanting SQL codification into package to let it to pass on with databases was introduced in 1980s. Databases did non supply internal storage for SQL questions that could be invoked by external plans. Embedded SQL was introduced as database specific extension to high-ranking linguistic communications like C/C++ in order to let plans to question and pull strings the content of databases. Although this method has been used for some clip, it creates many jobs with type checking that leads to inefficient and decelerate development of database scheme and package that uses the databases.

Open Database Connectivity ( ODBC )

ODBC is a method of communicating to client/server databases that has been produced by Microsoft in 1992, as an sweetening to Name Level Interface ( CLI ) . ODBC is portion of Microsoft ‘s Windows Open Systems Architecture ( WOSA ) , which provides a series of Application Program Interfaces ( APIs ) to simplify and supply criterions for assorted programming activities. With the ODBC theoretical account, the Windows platform provides a general set of database-access services. ODBC is a demand in supplying API with which to entree informations beginnings. Datas beginnings can be merely about anything, provided person has created an ODBC driver for it. The most common information beginnings are SQL and Oracle waiter. ODBC is rapidly emerging as an of import industry criterion for informations entree for both Windows and Macintosh applications.

Executions

Furthermore, ODBC executions run on many runing systems, includingMicrosoft Windows, Unix, Linux, OS/2, OS/400, IBM i5/OS, andMac OS X. Hundreds of ODBC drivers exist, including drivers for enterpriseDBMSsuch asOracle, DB2, Microsoft SQL Server, Sybase, Pervasive SQL, IBM Lotus Domino, MySQL, PostgreSQL, OpenLink Virtuoso, and desktop database merchandises such as Microsoft Access.

The being of ODBC alleviates every database and application developers in larning the use of informations entree interface. With ODBC, application developers can let an application to at the same time entree, observation, and adjust informations from multiple, diverse databases.

ODBC is a specification which application and database developers inscribe as either “ODBC Client” or “ODBC Driver” . For the client side or “front-end” there is a data entree interface that appears on desktop screen, the illustration figure of interface will be shown. The driver is loaded on the “server” Database Management System ( DBMS ) and the “client” , nevertheless, there is a difference in term of interface and processing method for the front-end computing machine. A figure of users can entree informations that have been stored on a computing machine which applied DBMS. The terminal user should be satisfied of the public presentation which is faster because the driver is non installed on the computing machine and no infinite is required. Furthermore, the terminal user ‘s computing machine will procure from any virus onslaught which can be prevented by cut downing unneeded installing.

The ODBC client uses a linguistic communication of bids which have been mentioned before in Introduction subdivision to bespeak informations from, or to direct informations to, the back- terminal or waiter DBMS. However, the DBMS does n’t understand the ODBC client petition until the bid passes through the ODBC Driver for that specific DBMS. This ODBC driver is package that resides on the front-end. The ODBC driver translates the bid into a format that the ODBC Server can understand. The ODBC Server sends the reply back to the ODBC Driver, which translates the reply into a format that the ODBC Client can understand.

Advantages

Harmonizing to ( Harvey, P. 1999 ) , the two chief advantages of coding an application with the ODBC API are portable informations entree codification and dynamic informations binding.

I. Portable Data Access Code

The ODBC API ( or CLI, command-line interface ) , as outlined by X/Open and the ISO, is available on all major platforms. Microsoft platforms include many sweetenings to this specification. The current version from Microsoft is 3.51. The thought is that a coder utilizing the ODBC API is likely to hold informations entree codification which is portable to other platforms. The same codification will besides be portable across different informations beginnings. For illustration, information for an accounting plan application can shack on a light SQL waiter during development and so be moved over to a heavy SQL waiter merely by associating to a different ODBC driver. ODBC delivers platform and informations beginning portability.

two. Dynamic Binding

Dynamic binding allows the user or the system decision maker to easy configure an application to utilize any ODBC-compliant informations beginning. This is the individual biggest advantage of coding an application with the ODBC API and buying such an application. Dynamic binding allows the terminal user to pick a information beginning, e.g. , an SQL waiter, and utilize it for all informations applications. Applications do non hold to be recompiled or recoded for the new mark informations beginning. This is achieved by the ODBC Driver Manager which will go through the ODBC calls to the user ‘s ODBC driver without the demand to re-link the codification. ODBC enables the user to take where the information will be stored.

Disadvantages

It is common in client/server architectures for constituents to hold SQL statements embedded in their beginning codification. Components submit questions to relational databases utilizing such criterions asUniversal Data Access ( UDA ) andOpen Database Connectivity ( ODBC ) .The API that implements these criterions is complex and requires the embedding of SQL statements in the linguistic communication that is used to compose the constituents. Such scheduling patterns are widespread and consequence in increased complexness in keeping systems. We propose an attack that eliminates the embedding of SQL in programming linguistic communications, thereby enabling the mechanization of of import package care undertakings. ( Mark Grechanik et Al, 2002 )

Examples of ODBC in Use

  • Access front-end accessing informations from an Oracle back-end utilizing the Oracle ODBC Driver, which ships with Access 1.1.
  • Ocular Basic front-end accessing informations from a dBASE back-end utilizing the dBASE ODBC Driver, which is portion of the MS ODBC Database Drivers Pack.
  • C application written utilizing C+ODBC SDK+Win SDK accessing informations from an AS/400 utilizing the AS/400 ODBC Driver available from Rochester Software.

Many users can portion a database, but the methodological analysis is really that of sharing files. To enable users to portion a database, all the applications that use the database must hold entree to the database files. Besides, none of the applications can open the database in “exclusive” manner. When you open a database in sole manner, you prevent other applications from utilizing the database.

Before accessing an ODBC database, you must put in on your system the appropriate ODBC driver for that database. You install this driver by utilizing the ODBC Manager applet in the Windows Control Panel.

A Data Source Name ( DSN ) is what ODBC uses to let you to tie in a database with a driver. You use the ODBC Administrator ( in Control Panel ) to configure give the DSN a alone name and so tie in it with both a database and a driver.

When you open a connexion to ODBC, you specify what DSN you want to work with. ODBC takes attention of all the small inside informations involved in opening that database.

Before you can entree ODBC databases, you must configure the ODBC informations beginning names, the ODBC drivers, and the constellation values used in ODBC.INI. You besides should understand the construction of an ODBC driver and the ODBC API every bit good as some ODBC-related nomenclature.

In order for any application to entree informations in a database, it must set up a connexion to that database through its corresponding ODBC driver. To make this, the application must bespeak a connexion from the ODBC Driver Administrator, stipulating the informations beginning desired.

Java Database Connectivity ( JDBC )

JDBC is a scheduling model for Java developers composing plans that entree information stored in databases, spreadsheets, and level files. JDBC is normally used to link a user plan to a “behind the scenes” database, irrespective of what database direction package is used to command the database ( Henry, K. 2001 ) .

Basically, ODBC and JDBC have similar thought of map which becomes a connexion or medium of reassigning informations signifier client to database and frailty versa. These two informations entree middleware were based on X/Open criterion for database connectivity. The difference of both ODBC and JDBC is the linguistic communication use of applications running on the client side. JDBC is merely focused on the applications that written in Java codification or linguistic communication.

Executions

Harmonizing to ( Konchady, M. 1998 ) , to pass on with the database there are two architectures can be used. First, the straight communicating between JDBC driver and database. Once the driver has connected to database, the SQL statements are the bids on representative of the Java plan. Database will give response by directing the result back to the driver director and eventually to the application of the needed bids.

Second, communicating between the ODBC driver and JDBC driver through a “bridge” . A peculiar JDBC driver can match with legion ODBC drivers. Every ODBC drivers perform SQL bids for exact databases to avoid complication of informations retrieval.

I. Database Communication Architecture

The JDBC/ODBC span was developed to take advantage of the big figure of ODBC-enabled informations beginnings. The span converts JDBC calls to ODBC calls and passes them to the appropriate driver for the backend database. The advantage of this strategy is that applications can entree informations from multiple sellers. However, the public presentation of a JDBC/ODBC span is slower than a JDBC driver entirely would be, due to the added operating expense. A database call must be translated from JDBC to ODBC to a native API.

two. Accessing a Database from an Applet

Fewer operations are required to utilize a JDBC driver without a span. In Figure 2, the stairss to entree a database utilizing a JDBC driver from an applet are shown. The Gwe JDBC driver is used with the MySQL database. The JDBC driver categories are foremost downloaded from the Gwe host site. Following, the applet logic passes a JDBC call to a driver director which in bends passes the call to a JDBC driver. The JDBC driver opens a TCP/IP connexion with the MySQL database waiter. Data is transferred back and Forth via the connexion. When database processing is complete, the connexion is closed.

The JDBC API can be used in applets and stand-alone applications. In add-on to the usual limitations for applets, merely connexions from the same waiter from which the applet was downloaded are accepted. If the web waiter and database waiter are non on the same machine, the web waiter must run a proxy service to route the database traffic. Stand-alone applications can give entree to local information and remote waiters.

Javaapplets and plans can work as the drivers ‘ API to associate to databases, depository and convey back database constituent every bit good as achieve saved methods. Therefore, creative activity JDBC a Java-enabled transmittal machinery for SQL. JDBC is to Java ‘s plans what ODBC is to plans inscribed in linguistic communications other than Java. In fact, JDBC ‘s design is based on ODBC ‘s. JDBC, developed by Sun Microsystems to authorise Java ‘s plans to publish SQL statements, was originally an unrelated set of subdivisions a Java plan could lade.

Drivertype 1 is supplying JDBC connectivity via ODBC drivers through a JDBC-ODBC span. Each client device that utilizes a JDBC-ODBC span must be loaded by database-specific client codification and ODBC plan codification. Sun clarified driver type 1 for expedience ‘s intent up to the clip of package sellers had invented the other three types of drivers. The decision makers are required to put in ODBC drivers on the client devices and configure it. Drivertype 2 includes of an interface, formulated to some extent in Java, among the vendor-specific database entree middleware such as Oracle SQL*Net or Sybase Open Client, and Java plans. JDBC driver type 2 is typically a consecutive nexus the proprietary API of to the database merchandises ; Oracle OCI. It does n’t necessitate theexistence of ODBC. It does, nevertheless, require the decision makers to put in the database seller ‘s proprietary database entree middleware and configure it.

Drivertype 3 is working as a calls transcriber into a database-independent web protocol from JDBC at the client side. Furthermore, there is a separate driver portion at the waiter which translates the database-independent JDBC demands into database-specific, local calls. Type 3 drivers are in this manner able to associate Java-based clients to a separate server-side driver that has been developed for from other types of databases. Type 3 drivers desire cardinal web protocol, TCP/IP for case at the client side, nevertheless they do non trust on theexistence of ODBC or vendor-specific middleware. Nevertheless type 3 drivers have to be complex plenty to cover with a multiplicity of networking conditions, affecting firewalls. Furthermore, Java plan using a type 3 driver can pretension to be most general, with a TCP/IP connexion to a database waiter, it will run on any Java-enabled platform. Among its other benefits, a type 3 driver is well-matched for map over the Internet.

Drivertype 4 is a simple Java driver which becomes a petition convertor into a database seller ‘s peculiar web protocol from JDBC. A Java client to that seller ‘s database merchandise attacks besides have mandate. Type 4 drivers are better matched for intranet map than Internet map. Since it relies on database entree protocols from database sellers ‘ proprietorship, a type 4 driver is further clearly possible to travel towards from a database seller.

JDBC Performance

Asimple every bit good as practical linguistic communication, Java tenders a figure of advantages: portability, fewer likelinesss of coder inaccuracies, reusability of entities and combination with intranet/Internet engineerings. Nevertheless, Java is an taken linguistic communication, sense that you run a computing machine plan such as browser which turn executes the recommendations within a Java plan. Sing that Java is interpreted, it sometimes serves public presentation incommodiousnesss to application conceivers. JDBC drivers written in simple Java, or partially in Java, sometimes map slower than expected. To calculate out the incommodiousness, there will productions of Java run-time optimisations, JIT ( Just-In-Time ) compilers, and even Java-specific CPU french friess. Java ‘s public presentation will acquire better over continuance, and the bing province of affairs is often more than sufficient for informations entry. Even so, JDBC public presentation can be a concern for drivers written in Java.

Advantages

There are a figure of faculties, the JDBC driver, the Java run-time environment which is a category file and the driver director. If the users choose to utilize the JDBC-ODBC Bridge, the ODBC driver is another bed. Excess beds of codification suggest excess processing and longer low-level formatting times. Users might take to do the Web waiter computing machine besides a database waiter, an attack that lets you connect an applet ( or application ) straight to the database via a JDBC driver written in pure Java. Alternatively, you might configure the Web waiter with a proxy connexion to a separate database waiter computing machine, a solution that gives you more leeway in platform picks.

Disadvantages

Databaseand driver sellers require to execute some sweetenings in JDBC before it turns out to be truly functional. The first sweetening is to relieve users in recovering informations harmonizing to sequence of rows in a tabular array. The bing pointer is non efficaciously working because it can non travel rearward. Hence, the user can non capture the old informations ; nevertheless, they can capture the current informations that being saved.

JDBCdrivers besides require being able to cover with the interrupting block of records individually and for this affair, it involves multiple rows returned in a individual answer message. Normally, there are a figure of rows for a question to acquire a consequence and by directing every row at one clip and non at the same time is clip devouring. The database should be able to direct multiple rows in a individual answer, and by deblocking the response, the JDBC driver should be able to present the following row to the application.

Decision

Mentions