CST363 - Week 6

  CST 363 Introduction to Database

In week 6, the focus is on database programming. In this part of the course, I am learning about the difference between imperative and declarative languages and how to combine both to close the gaps in database programming. Syntax and paradigm gaps exist in database programming, and embedded SQL, procedural SQ, and API (application programming interface) are three techniques that offer solutions to syntax and paradigm gaps.

The embedded SQL technique is used in a host language and begins with the keyword EXEC SQL followed by the SQL statement. The compiler translates the SQL statements to the host language and then to an executable program. For the embedded SQL, establishing a connection between the host and the database is necessary to run queries. The connections within embedded SQL are managed by the three steps: 1- defining the connection name and login credentials, 2- set the connection to the database, and 3- terminate the connection and release any computing resources.

I also learned about the Java Database Connectivity or JDBC. The connection interface is created by calling the DriverManager.getConnection() method and passing the database information and login credentials as a parameter. The Statement interface is used for SQL query execution. A Statement object is created using createStatement() from the connection interface. The ResultSet interface retrieves the query results by returning a ResultSet object. PreparedStatement interface uses the prepareStatement() method in the connection interface by assigning a query. The PreparedStatement.executeQuery() prevents SQL injection attacks.

The most common technique is the API, which is a library of classes or procedures that connects the application programming language to a service host. One example of these libraries is JDBC, which contains the Java classes required to access the database. Most APIs can manage connections, prepare queries, execute queries for single and multiple rows, and call the stored procedure.