Chapter 5 - Advanced SQL. In Chapters 3 and 4, we provided detailed coverage of the basic structure of SQL. In this chapter, we cover some of the more advanced features of SQL. We address the issue of how to access SQL from a general-purpose programming language, which is very important for building applications that use a database to store and retrieve data. | Chapter 5: Advanced SQL Database System Concepts, 6th Ed. ©Silberschatz, Korth and Sudarshan See for conditions on re-use Chapter 5: Advanced SQL Accessing SQL From a Programming Language Dynamic SQL JDBC and ODBC Embedded SQL SQL Data Types and Schemas Functions and Procedural Constructs Triggers Advanced Aggregation Features OLAP Database System Concepts - 6th Edition ©Silberschatz, Korth and Sudarshan JDBC and ODBC API (application-program interface) for a program to interact with a database server Application makes calls to Connect with the database server Send SQL commands to the database server Fetch tuples of result one-by-one into program variables ODBC (Open Database Connectivity) works with C, C++, C#, and Visual Basic Other API’s such as sit on top of ODBC JDBC (Java Database Connectivity) works with Java Database System Concepts - 6th Edition ©Silberschatz, Korth and Sudarshan JDBC JDBC is a Java API for communicating with database systems supporting SQL. JDBC supports a variety of features for querying and updating data, and for retrieving query results. JDBC also supports metadata retrieval, such as querying about relations present in the database and the names and types of relation attributes. Model for communicating with the database: Open a connection Create a “statement” object Execute queries using the Statement object to send queries and fetch results Exception mechanism to handle errors Database System Concepts - 6th Edition ©Silberschatz, Korth and Sudarshan JDBC Code public static void JDBCexample(String dbid, String userid, String passwd) { try { (""); Connection conn = ( "jdbc:oracle:thin:@", userid, passwd); Statement stmt = (); Do Actual Work . (); (); } catch (SQLException
