Client side-Development

What Is an Information System?

What Is an Information System?

At the most basic level, an information system (IS) is a set of components that work together to manage data processing and storage. Its role is to support the key aspects of running an organization, such as communication, record-keeping, decision making, data analysis and more. Companies use this information to improve their business operations, make strategic decisions and gain a competitive edge.

Information systems typically include a combination of software, hardware and telecommunication networks. For example, an organization may use customer relationship management systems to gain a better understanding of its target audience, acquire new customers and retain existing clients. This technology allows companies to gather and analyze sales activity data, define the exact target group of a marketing campaign and measure customer satisfaction.

The Benefits of Information Systems

Modern technology can significantly boost your company's performance and productivity. Information systems are no exception. Organizations worldwide rely on them to research and develop new ways to generate revenue, engage customers and streamline time-consuming tasks.

With an information system, businesses can save time and money while making smarter decisions. A company's internal departments, such as marketing and sales, can communicate better and share information more easily.Since this technology is automated and uses complex algorithms, it reduces human error. Furthermore, employees can focus on the core aspects of a business rather than spending hours collecting data, filling out paperwork and doing manual analysis.Thanks to modern information systems, team members can access massive amounts of data from one platform. For example, they can gather and process information from different sources, such as vendors, customers, warehouses and sales agents, with a few mouse clicks.

Uses and Applications

There are different types of information systems and each has a different role. Business intelligence (BI) systems, for instance, can turn data into valuable insights.This kind of technology allows for faster, more accurate reporting, better business decisions and more efficient resource allocation. Another major benefit is data visualization, which enables analysts to interpret large amounts of information, predict future events and find patterns in historical data.

Organizations can also use enterprise resource planning (ERP) software to collect, manage and analyze data across different areas, from manufacturing to finance and accounting. This type of information system consists of multiple applications that provide a 360-degree view of business operations. NetSuite ERP, PeopleSoft, Odoo and Intacct are just a few examples of ERP software.

Like other information systems, ERP provides actionable insights and helps you decide on the next steps. It also makes it easier to achieve regulatory compliance, increase data security and share information between departments. Additionally, it helps to ensure that all of your financial records are accurate and up-to-date.In the long run, ERP software can reduce operational costs, improve collaboration and boost your revenue. Nearly half of the companies that implement this system report major benefits within six months.At the end of the day, information systems can give you a competitive advantage and provide the data you need to make faster, smarter business decisions. Depending on your needs, you can opt for transaction processing systems, knowledge management systems, decision support systems and more. When choosing one, consider your budget, industry and business size. Look for an information system that aligns with your goals and can streamline your day-to-day operations.

Data persistence

Persistence refers to object and process characteristics that continue to exist even after the process that created it ceases or the machine it is running on is powered off. When an object or state is created and needs to be persistent, it is saved in a non-volatile storage location, like a hard drive, versus a temporary file or volatile random access memory (RAM).

In terms of data, persistence means an object should not be erased unless it is really meant to be deleted. This entails proper storage and certain measures that allow the data to persist. In terms of computer threads and processes, a persistent process is one that cannot be killed or shut down. This is usually true for core system processes that are essential to a properly functioning system.

For example, even if a Windows operating system (OS) explorer fails or is killed, it simply restarts. A persistent state refers to the retention of that state, even after the process has been killed. In this case, the state is saved in persistent storage before device shutdown and then reloaded when the device turns on, ensuring that the device, workspace or data are in the same state after turning on the device.

What is Data?

Data is distinct pieces of information, usually formatted in a special way. All software is divided into two general categories: data and programs. Programs are collections of instructions for manipulating data.Data can exist in a variety of forms — as numbers or text on pieces of paper, as bits and bytes stored in electronic memory, or as facts stored in a person's mind. Since the mid-1900s, people have used the word data to mean computer information that is transmitted or stored.Strictly speaking, data is the plural of datum, a single piece of information. In practice, however, people use data as both the singular and plural form of the word, and as a mass noun (like "sand").

What is Data?

Machine Readable Information

The term data is often used to distinguish binary machine-readable information from textual human-readable information. For example, some applications make a distinction between data files (files that contain binary data) and text files (files that contain ASCII data).

Also important to know is that in database management systems (DBMS), data files are the files that store the database information, whereas other files, such as index files and data dictionaries, store administrative information, known as metadata.

Types of data

Quantitative data deals with numbers and things you can measure objectively: dimensions such as height, width, and length. Temperature and humidity. Prices. Area and volume.

Qualitative data deals with characteristics and descriptors that can't be easily measured, but can be observed subjectively—such as smells, tastes, textures, attractiveness, and color. Broadly speaking, when you measure something and give it a number value, you create quantitative data. When you classify or judge something, you create qualitative data. So far, so good. But this is just the highest level of data: there are also different types of quantitative and qualitative data.

What is Database?

A database is a collection of information that is organized so that it can be easily accessed, managed and updated.Data is organized into rows, columns and tables, and it is indexed to make it easier to find relevant information. Data gets updated, expanded and deleted as new information is added. Databases process workloads to create and update themselves, querying the data they contain and running applications against it.

Computer databases typically contain aggregations of data records or files, such as sales transactions, product catalogs and inventories, and customer profiles.Typically, a database manager provides users with the ability to control read/write access, specify report generation and analyze usage. Some databases offer ACID (atomicity, consistency, isolation and durability) compliance to guarantee that data is consistent and that transactions are complete.

Databases are prevalent in large mainframe systems, but are also present in smaller distributed workstations and midrange systems, such as IBM's AS/400 and personal computers.

Evolution of databases

Databases have evolved since their inception in the 1960s, beginning with hierarchical and network databases, through the 1980s with object-oriented databases, and today with SQL and NoSQL databases and cloud databases.

In one view, databases can be classified according to content type: bibliographic, full text, numeric and images. In computing, databases are sometimes classified according to their organizational approach. There are many different kinds of databases, ranging from the most prevalent approach, the relational database, to a distributed database, cloud database or NoSQL database.

Relational database

A relational database, invented by E.F. Codd at IBM in 1970, is a tabular database in which data is defined so that it can be reorganized and accessed in a number of different ways.

Relational databases are made up of a set of tables with data that fits into a predefined category. Each table has at least one data category in a column, and each row has a certain data instance for the categories which are defined in the columns.

The Structured Query Language (SQL) is the standard user and application program interface for a relational database. Relational databases are easy to extend, and a new data category can be added after the original database creation without requiring that you modify all the existing applications.

Distributed database

A distributed database is a database in which portions of the database are stored in multiple physical locations, and in which processing is dispersed or replicated among different points in a network.

Distributed databases can be homogeneous or heterogeneous. All the physical locations in a homogeneous distributed database system have the same underlying hardware and run the same operating systems and database applications. The hardware, operating systems or database applications in a heterogeneous distributed database may be different at each of the locations.

Cloud database

A cloud database is a database that has been optimized or built for a virtualized environment, either in a hybrid cloud, public cloud or private cloud. Cloud databases provide benefits such as the ability to pay for storage capacity and bandwidth on a per-use basis, and they provide scalability on demand, along with high availability.A cloud database also gives enterprises the opportunity to support business applications in a software-as-a-service deployment.

NoSQL database

NoSQL databases are useful for large sets of distributed data.NoSQL databases are effective for big data performance issues that relational databases aren't built to solve. They are most effective when an organization must analyze large chunks of unstructured data or data that's stored across multiple virtual servers in the cloud.

Object-oriented database

Items created using object-oriented programming languages are often stored in relational databases, but object-oriented databases are well-suited for those items.

An object-oriented database is organized around objects rather than actions, and data rather than logic. For example, a multimedia record in a relational database can be a definable data object, as opposed to an alphanumeric value.

Graph database

A graph-oriented database, or graph database, is a type of NoSQL database that uses graph theory to store, map and query relationships. Graph databases are basically collections of nodes and edges, where each node represents an entity, and each edge represents a connection between nodes. Graph databases are growing in popularity for analyzing interconnections. For example, companies might use a graph database to mine data about customers from social media.

What is Database Server?

The term database server may refer to both hardware and software used to run a database, according to the context. As software, a database server is the back-end portion of a database application, following the traditional client-server model. This back-end portion is sometimes called the instance. It may also refer to the physical computer used to host the database. When mentioned in this context, the database server is typically a dedicated higher-end computer that hosts the database.

Note that the database server is independent of the database architecture. Relational databases, flat files, non-relational databases: all these architectures can be accommodated on database servers.In the client-server computing model, there is a dedicated host to run and serve up the resources, typically one or more software applications. There are also several clients who can connect to the server and use the resources offered and hosted by this server.

When considering databases in the client-server model, the database server may be the back-end of the database application (the instance), or it may be the hardware computer that hosts the instance. Sometimes, it may even refer to the combination of both hardware and software.

In smaller and mid-sized setups, the hardware database server will also typically host the server part of the software application that uses the database. If we consider a bank, for instance, the hardware database server will host the software database server and the bank's software application. This application will likely connect to the database via specific ports and use inter-process communication to log into and access the data resident in the database. The users in the bank, seated at their personal computers, will also use the client module of the application installed on their computers to connect to the database. In this example, there are actually two client-server models we are looking at: the database and the application.

In larger setups, the volume of transactions may be such that one computer will be unable to handle the load. In this case, the database software will reside on a dedicated computer, and the application on another. In this scenario, there is a dedicated database server, which is the combination of the hardware and software, and a separate dedicated application server.

What is a Database management system?

A database management system (DBMS) is a software package designed to define, manipulate, retrieve and manage data in a database. A DBMS generally manipulates the data itself, the data format, field names, record structure and file structure. It also defines rules to validate and manipulate this data. A DBMS relieves users of framing programs for data maintenance. Fourth-generation query languages, such as SQL, are used along with the DBMS package to interact with a database.

Some other DBMS examples include:

·         MySQL

·         SQL Server

·         Oracle

·         dBASE

·         FoxPro

A database management system receives instruction from a database administrator (DBA) and accordingly instructs the system to make the necessary changes. These commands can be to load, retrieve or modify existing data from the system.

A DBMS always provides data independence. Any change in storage mechanism and formats are performed without modifying the entire application. There are four main types of database organization:

Relational Database: Data is organized as logically independent tables. Relationships among tables are shown through shared data. The data in one table may reference similar data in other tables, which maintains the integrity of the links among them. This feature is referred to as referential integrity – an important concept in a relational database system. Operations such as "select" and "join" can be performed on these tables. This is the most widely used system of database organization.

Flat Database: Data is organized in a single kind of record with a fixed number of fields. This database type encounters more errors due to the repetitive nature of data.

Object-Oriented Database: Data is organized with similarity to object-oriented programming concepts. An object consists of data and methods, while classes group objects having similar data and methods.

Hierarchical Database: Data is organized with hierarchical relationships. It becomes a complex network if the one-to-many relationship is violated.

Files Vs Database systems

It’s interesting to see how databases have come a long way and have clearly out-shadowed file-systems for storing structured or unstructured information.Technically, both of them support the basic features necessary for data access.

For example, both of them:

1.       Ensure data is managed to ensure its integrity and quality

2.       Allow shared access by a community of users

3.       Use well-defined schema for data-access

4.       Support a query language

But filesystems seriously lack some of the critical features necessary for managing data. Let’s take a look at some of these features.

Transaction support

Atomic transactions guarantee complete failure or success of an operation. This is especially needed when there is concurrent access to the same data set. This is one of the basic features provided by all databases.

But most filesystems don’t have this feature. Only the lesser known filesystems, such as Transactional NTFS(TxF), Sun ZFS, and Veritas VxFS, support this feature. Most of the popular open source filesystems (including ext3, xfs, reiserfs) are not even POSIX compliant.

Fast Indexing

Databases allow indexing based on any attribute or data property (i.e. SQL columns). This helps fast retrieval of data, based on the indexed attribute. This functionality is not offered by most filesystems, e.g. you can’t quickly access “all files created after 2 PM today.”

The desktop search tools like Google desktop or MAC spotlight offer this functionality. But for this, they have to scan and index the complete filesystem and store the information in an internal relational database.

Snapshots

Snapshot is a point-in-time copy/view of the data. Snapshots are needed for backup applications, which need consistent point-in-time copies of data.The transactional and journaling capabilities enable most of the databases to offer snapshots without shopping access to the data. Most filesystems however, don’t provide this feature (ZFS and VxFS being only exceptions). The backup software has to either depend on the running application or underlying storage for snapshots.

Clustering

Advanced databases like MySQL also offer clustering capabilities. MySQL offers shared-nothing clusters using synchronous replication. This helps the databases scale up and support larger & more fault tolerant production environments.

Filesystems still don’t support this option. The only exceptions are Veritas CFS and GFS (Open Source).

Replication

Replication is a commodity with databases and forms the basis for disaster-recovery plans. File-systems still have to evolve to handle it.

Relational View of Data

Filesystems store files and other objects only as a stream of bytes, and have little or no information about the data stored in the files. Filesystems also provide only a single way of organizing the files, namely via directories and file names. The associated attributes are also limited in number, e.g. type, size, author, creation time, etc. This does not help in managing related data, as disparate items do not have any relationships defined.

Databases, on the other hand, offer easy means to relate stored data. It also offers a flexible query language (SQL) to retrieve the data. For example, it is possible to query a database for “contacts of all persons who live in Acapulco and sent emails yesterday”, but impossible in the case of a filesystem.

Filesystems need to evolve and provide capabilities to relate different data sets. This will help the application writers to make use of native filesystem capabilities to relate data. A good effort in this direction has been Microsoft WinFS.

There are features that databases have that filesystems could truly benefit from, but there is no reason why filesystems in the future will not borrow those features.  In fact, they already are in many cases.

DBMS Advantages

The feature list of a DBMS can only be considered an advantage if those features are essential to providing an effective solution to the application data management requirements. A quick summary of the features that address the questions above is as follows:

Ø  Transaction Support- Atomic transactions guarantee complete failure or success of an operation. This includes automatic recovery of the database to a transaction-consistent point in the event of an abnormal termination of the application (crash, power loss, etc.).

Ø  Concurrent Access- The ability to share data by controlling access to data items; many users (process or threads) can access data concurrently.

Ø  Data Normalization- A well-designed database schema can reduce storage requirements on the target storage media by reducing duplicate data.

Ø  Expandability, Flexibility, Scalability- A database system can scale easily to larger datasets.

Ø  Standards Enforcement- One example of this advantage would be to use the DBMS for all data storage requirements for the application. Multiple data structures can be manipulated using the same API functions. The can lead to reduced application development times and reduced maintenance costs in the future.

Ø  Fast Query Access- Databases allow indexing based on any attribute or data-property (i.e. SQL columns). This helps fast retrieval of data, based on the indexed attribute. This is an important advantage as data-sets begin to grow large as it provides a more predictable query response time.

Ø  Interoperability- Connectivity through industry standard protocols allowing third-party tools to access and analyze data.

Different arrangements of data

Big Data includes huge valume, high velocity, and extensible variaty of data. These are 3 types: Structured data, Semi-structured data, and Unstructured data.

Structured data –

Structured data is a data whose elements are addressable for effective analysis. It has been organised into a formatted repository that is typically a database. It concern all data which can be stored in database SQL in table with rows and columns. They have relational key and can easily mapped into pre-designed fields. Today, those data are most processed in development and simplest way to manage information. Example: Relational data.

semi-structured data –

Semi-structured data is information that does not reside in a rational database but that have some organizational properties that make it easier to analyze. With some process, you can store them in the relation database (it could be very hard for some kind of semi-structured data), but Semi-structured exist to ease space. Example: XML data.

Unstructured data –

Unstructured data is a data that is which is not organised in a pre-defined manner or does not have a pre-defined data model, thus it is not a good fit for a mainstream relational database. So for Unstructured data, there are alternative platforms for storing and managing, it is increasingly prevalent in IT systems and is used by organizations in a variety of business intelligence and analytics applications. Example: Word, PDF, Text, Media logs.

Differences between Structured, Semi-structured and Unstructured data:

Big Data vs Data Warehouse

Ø  Big data

Big data is an evolving term that describes a large volume of structured, semi-structured and unstructured data that has the potential to be mined for information and used in machine learning projects and other advanced analytics applications.

Big data is often characterized by the 3Vs: the extreme volume of data, the wide variety of data types and the velocity at which the data must be processed. Those characteristics were first identified by Gartner analyst Doug Laney in a report published in 2001. More recently, several other Vs have been added to descriptions of big data, including veracity, value and variability. Although big data doesn't equate to any specific volume of data, the term is often used to describe terabytes, petabytes and even exabytes of data captured over time.

Ø  Data warehousing

A data warehouse (DW) is a collection of corporate information and data derived from operational systems and external data sources. A data warehouse is designed to support business decisions by allowing data consolidation, analysis and reporting at different aggregate levels. Data is populated into the DW through the processes of extraction, transformation and loading.

The data warehouse architecture was born in the 1980s as an architectural model designed to support the flow of data from operational systems to decision support systems. These systems require analysis of large amounts of heterogeneous data accumulated by companies over time.

In a data warehouse, data from many heterogeneous sources is extracted into a single area, transformed according to the decision support system needs and stored into the warehouse. For example, a company stores information pertaining to its employees, their salaries, developed products, customer information, sales and invoices. The CEO might want to ask a question pertaining to the latest cost-reduction measures; the answers will involve analysis of all of this data. This is a main service of the data warehouse, i.e., allowing executives to reach business decisions based on all these disparate raw data items.

Thus, a data warehouse contributes to future decision making. As in the above example, a firm administrator can query warehouse data to find out the market demand of a particular product, sales data by geographical region or answers other inquiries. This provides insight about required steps to more effectively market a particular product. Unlike an operational data store, a data warehouse contains aggregate historical data, which may be analyzed to reach critical business decisions. Despite associated costs and effort, most major corporations today use data warehouses.

Differences between big data and data warehousing

The Difference Between Big Data vs Data Warehouse, are explained in the points presented below:

Data Warehouse is an architecture of data storing or data repository. Whereas Big Data is a technology to handle huge data and prepare the repository.

Any kind of DBMS data accepted by Data warehouse, whereas Big Data accept all kind of data including transnational data, social media data, machinery data or any DBMS data.

Data warehouse only handles structure data (relational or not relational), but big data can handle structure, non-structure, semi-structured data.

Big data normally used a distributed file system to load huge data in a distributed way, but data warehouse doesn’t have that kind of concept.

From a business point of view, as big data has a lot of data, analytics on that will be very fruitful, and the result will be more meaningful which help to take proper decision for that organization. Whereas Data warehouse mainly helps to analytic on informed information.

Data warehouse means the relational database, so storing, fetching data will be similar with normal SQL query. And big data is not following proper database structure, we need to use hive or spark SQL to see the data by using hive specific query.

100% data loaded into data warehousing are using for analytics reports. But whatever data loaded by Hadoop, maximum 0.5% used on analytics reports till now. Others data are loaded into the system, but in not use status.

Data Warehousing never able to handle humongous data (totally unstructured data). Big data (Apache Hadoop) is the only option to handle humongous data.

The timing of fetching increasing simultaneously in data warehouse based on data volume. Means, it will take small time for low volume data and big time for a huge volume of data just like DBMS. But in case of big data, it will take a small period of time to fetching huge data (as it specially designed for handling huge data), but taken huge time if we somehow try to load or fetch small data in HDFS by using map reduce.

BASIS FOR COMPARISON	DATA WAREHOUSE	BIG DATA
Meaning	Data Warehouse is mainly an architecture, not a technology. It extracting data from varieties SQL based data source (mainly relational database) and help for generating analytic reports. In terms of definition, data repository, which using for any analytic reports, has been generated from one process, which is nothing but the data warehouse.	Big Data is mainly a technology, which stands on volume, velocity, and variety of the data. Volumes define the amount of data coming from different sources, velocity refers to the speed of data processing, and varieties refer to the number of types of data (mainly support all type of data format).
Preferences	If an organization wants to know some informed decision (like what is going on in their corporation, next year planning based on current year performance data etc), they prefer to choose data warehousing, as for this kind of report they need reliable or believable data from the sources.	If organization need to compare with a lot of big data, which contain valuable information and help them to take a better decision (like how to lead more revenue, more profitability, more customers etc), they obviously preferred Big Data approach.
Accepted Data Source	Accepted one or more homogeneous (all sites use the same DBMS product) or heterogeneous (sites may run different DBMS product) data sources.	Accepted any kind of sources, including business transactions, social media, and information from sensor or machine specific data. It can come from DBMS product or not.
Accepted type of formats	Handles mainly structural data (specifically relational data).	Accepted all types of formats. Structure data, relational data, and unstructured data including text documents, email, video, audio, stock ticker data and financial transaction.
Subject-Oriented	Data warehouse is subject oriented because it actually provides information on the specific subject (like a product, customers, suppliers, sales, revenue etc) not on organization ongoing operation. It does not focus on ongoing operation, it mainly focuses on analysis or displaying data which help on decision making.	Big Data is also subject-oriented, the main difference is a source of data, as big data can accept and process data from all the sources including social media, sensor or machine specific data. It also main on provide exact analysis on data specifically on subject oriented.
Time-Variant	The data collected in a data warehouse is actually identified by a particular time period. As it mainly holds historical data for an analytical report.	Big Data have a lot of approach to identified already loaded data, a time period is one of the approaches on it. As Big data mainly processing flat files, so archive with date and time will be the best approach to identify loaded data. But it have the option to work with streaming data, so it not always holding historical data.
Non-volatile	Previous data never erase when new data added to it. This is one of the major features of a data warehouse. As it totally different from an operational database, so any changes on an operational database will not directly impact to a data warehouse.	For Big data, again previous data never erase when new data added to it. It stored as a file which represents a table. But here sometime in case of streaming directly use Hive or Spark as operation environment.
Distributed File System	Processing of huge data in Data Warehousing is really time-consuming and sometimes it took an entire day for complete the process.	This is one of the big utility of Big Data. HDFS (Hadoop Distributed File System) mainly defined to load huge data in distributed systems by using map reduce program.

SQL statements, Prepared statements, and Callable statements

Once a connection is obtained we can interact with the database.

The JDBC Statement,CallableStatement, and PreparedStatement interfaces define the methods and properties that enable you to send SQL or PL/SQL commands and receive data from your database.They also define methods that help bridge data type differences between Java and SQL data types used in a database.

A summary of each interface's purpose to decide on the interface to use;

Statement:  Use this for general-purpose access to your database. Useful               when you are using static SQL statements at runtime. The Statement interface cannot accept parameters.

PreparedStatement: Use this when you plan to use the SQL statements many times. The PreparedStatement interface accepts input parameters at runtime.

CallableStatement: Use this when you want to access the database stored procedures. The CallableStatement interface can also accept runtime input parameters.

The Statement Objects

Creating Statement Object

Before you can use a Statement object to execute a SQL statement, you need to create one using the Connection object's createStatement( ) method, as in the following example −

Statement stmt = null;

try {

   stmt = conn.createStatement( );

   . . .

}

catch (SQLException e) {

   . . .

}

finally {

   . . .

}

Once you've created a Statement object, you can then use it to execute an SQL statement with one of its three execute methods.

boolean execute (String SQL): Returns a boolean value of true if a ResultSet object can be retrieved; otherwise, it returns false. Use this method to execute SQL DDL statements or when you need to use truly dynamic SQL.

int executeUpdate (String SQL): Returns the number of rows affected by the execution of the SQL statement. Use this method to execute SQL statements for which you expect to get a number of rows affected - for example, an INSERT, UPDATE, or DELETE statement.

ResultSet executeQuery (String SQL): Returns a ResultSet object. Use this method when you expect to get a result set, as you would with a SELECT statement.

Closing Statement Object

Just as you close a Connection object to save database resources, for the same reason you should also close the Statement object.

A simple call to the close() method will do the job. If you close the Connection object first, it will close the Statement object as well. However, you should always explicitly close the Statement object to ensure proper cleanup.

Statement stmt = null;

try {

   stmt = conn.createStatement( );

   . . .

}

catch (SQLException e) {

   . . .

}

finally {

   stmt.close();

}

The PreparedStatement Objects

The PreparedStatement interface extends the Statement interface, which gives you added functionality with a couple of advantages over a generic Statement object.

This statement gives you the flexibility of supplying arguments dynamically.

Creating PreparedStatement Object

PreparedStatement pstmt = null;

try {

   String SQL = "Update Employees SET age = ? WHERE id = ?";

   pstmt = conn.prepareStatement(SQL);

   . . .

}

catch (SQLException e) {

   . . .

}

finally {

   . . .

}

All parameters in JDBC are represented by the ? symbol, which is known as the parameter marker. You must supply values for every parameter before executing the SQL statement.

The setXXX() methods bind values to the parameters, where XXX represents the Java data type of the value you wish to bind to the input parameter. If you forget to supply the values, you will receive an SQLException.

Each parameter marker is referred by its ordinal position. The first marker represents position 1, the next position 2, and so forth. This method differs from that of Java array indices, which starts at 0.

All of the Statement object's methods for interacting with the database (a) execute(), (b) executeQuery(), and (c) executeUpdate() also work with the PreparedStatement object. However, the methods are modified to use SQL statements that can input the parameters.

Closing PreparedStatement Object

Just as you close a Statement object, for the same reason you should also close the PreparedStatement object.

A simple call to the close() method will do the job. If you close the Connection object first, it will close the PreparedStatement object as well. However, you should always explicitly close the PreparedStatement object to ensure proper cleanup.

PreparedStatement pstmt = null;

try {

   String SQL = "Update Employees SET age = ? WHERE id = ?";

   pstmt = conn.prepareStatement(SQL);

   . . .

}

catch (SQLException e) {

   . . .

}

finally {

   pstmt.close();

}

The CallableStatement Objects

Just as a Connection object creates the Statement and PreparedStatement objects, it also creates the CallableStatement object, which would be used to execute a call to a database stored procedure.

Creating CallableStatement Object

Suppose, you need to execute the following Oracle stored procedure −

CREATE OR REPLACE PROCEDURE getEmpName

   (EMP_ID IN NUMBER, EMP_FIRST OUT VARCHAR) AS

BEGIN

   SELECT first INTO EMP_FIRST

   FROM Employees

   WHERE ID = EMP_ID;

END;

NOTE: Above stored procedure has been written for Oracle, but we are working with MySQL database so, let us write same stored procedure for MySQL as follows to create it in EMP database