Week 22

 CST-363 Week 6

This is our sixth week in CST-363, also known as Introduction to Database Systems.

Summary

This week we read about integrating SQL into programs and other languages. We learned about some of  the differences between SQL and other languages and some of the ways to integrate SQL into a program, such as Embedded SQL and APIs. Then, we learned how to use an SQL API for Java called JDBC.

Vocabulary

Programming Languages

Imperative Languages: Contain control flow statements that determine the execution order of program steps. Two popular types of imperative languages are:

  • Procedural Languages: Composed of procedures, also called functions or subroutines. Most languages developed prior to 1990 are procedural. Ex: C and COBOL.
  • Object-Oriented Languages: Organize code into classes. A class combines variables and procedures into a single construct. Most languages developed since 1990 are object-oriented. Ex: Java, Python, and C++.

Declarative Languages: Do not contain control flow statements. Each statement declares what result is desired, using logical expressions, rather than how the result is processed. SQL and HTML are leading examples of declarative languages.

 

Database Programming: Building applications with both SQL and a general-purpose language. Database programming presents to challenges: 

  • Syntax Gap: The syntax of SQL and most other languages are quite different.
  • Paradigm Gap: Query processing is fundamentally different in SQL and procedural or object-oriented languages.

To bridge the syntax and paradigm gaps, three database programming techniques have emerged:

  • Embedded SQL: Codes SQL statements directly in a program written in another language.
  • Procedural SQL: Extends the SQL language with control flow statements, creating a new programming language.
  • API: A library of procedures or classes.

Embedded SQL 

Host Language: Embedded SQL statements appear in programs written in another language, called the host language.

Precompiler: Translates SQL statements into host language statements and function calls.

Shared Variable: A host language variable that appears in SQL statements.

Cursor: An embedded SQL variable that identifies an individual row of a result table. Cursors are necessary to process queries that return multiple rows.

Dynamic SQL: Embedded SQL statements that are generated at run-time.

Static SQL:  Embedded SQL statements that are fixed in program code.

Application Programming Interfaces (APIs)

Database API implementations typically contain two software layers: 

  • Driver: Connects directly to the database.
  • Driver Manager: Connects the application to the drivers.

Database Programming with Java

Connection: An object of the Connection interface that is created with:

  • DiverManager.getConnection(): Specifies the database server address, database name, login username, and password.

connection.close(): Releases the connection.

Statement Interface: Defines and executes SQL queries.

  • createStatement(): Creates Statement objects.

SQL queries are defined and executed with Statement methods:

  • executeQuery(): Associates a statement object with a SELECT query, compiles the query to low-level database operations and executes the query. executeQuery() has one required parameter, a string containing the SQL query, and returns a ResultSet object, described below.
  • executeUpdate(): Executes INSERT, UPDATE, and DELETE queries. executeUpdate() returns the number of rows matched by the query, not the number of rows that were modified.
  • execute(): Executes any SQL statement but is commonly used for data definition statements such as CREATE TABLE. execute() returns a boolean value. If the value is true, the statement has generated a ResultSet object which can be accessed with the Statement getResultSet() method.
  • close: Releases statement objects and must be executed prior to closing the associated connection.

ResultSet Interface: The object that retrieves query results.

next(): Advances the current row to the next row. Returns true if the current row advances, or false if the current row is at the last row of the query result.

Values in the current row are accessed with ResultSet methods:

  • getInt(): Takes a column name parameter and returns the integer value of the column.
  • getDouble(): Takes a column name parameter and returns the Double value of the column.
  • getString(): Takes a column name parameter and returns the String value of the column.

Other ResultSet methods return additional information:

  • getMetaData(): Returns a ResultSetMetaData object, containing information about the columns of a ResultSet object.
  • getWarnings(): Returns the first warning generated by a query.

PreparedStatement: An interface that extends the Statement interface and supports SQL query parameters.

  • prepareStatement(): A Connection interface method that creates PreparedStatement objects. Takes an SQL query String parameter.

SQL Injection Attack: Ehen a user intentionally enters values that alter the intent of an SQL statement.

CallableStatement: An interface that extends PreparedStatement with methods that call stored procedures. 

  • prepareCall(): A Connection interface method that creates CallableStatement objects. 

Comments

Post a Comment

Popular posts from this blog

Week 4

 My Educational Goals This week we briefly discussed setting goals. As part of this we were tasked with setting our educational goals. CSUMB Goals The goal I am currently working on is completing CS Online at CSUMB. My goal is to graduate from the program in 2027. To accomplish this, I need to complete a few smaller goals during the program. I need to complete a foreign language. Currently I am taking Spanish 1 at Sierra College. I need to complete an ethnic studies course. I plan to take Intro to Ethnic Studies (ETHN 11) or Ethnic Images in Film (ETHN 50) at Sierra College during the Summer 2025 semester. I need to complete a physical science course. I plan to take Physical Geology (ESCI 1) or Introduction to Earth Science (ESCI 10) at Sierra College during the Fall 2025 semester. I need to complete a computer science elective course at CSUMB. I plan to take Game Engine Programming (CST 306) or a similar class during the Spring 2026 semester. Other Educational Goals While attendin...
Read more

Week 2

Image
This week we read a document called Effective Study Skills . As the name suggests, it discusses various ways you can improve your studying. One of the areas of studying I'm better at is managing time. I feel that I'm pretty good at making sure I set aside time to study and work on homework. Another area of studying I'm better at is organization. I think I'm pretty good at keeping my notes and assignments organized, usually by class and module. Another area of studying I'm better at is surveying. I believe that I'm good at understanding assignments and questions, as well as eliminating answers on a quiz. One of the areas of studying I'm weak in is schedules. I don't write study schedules, or schedule when to study specific assignments. Another area of studying I'm weak in is outlining textbooks. Whether it's physical or digital, I don't outline textbooks. Another area of studying I'm weak in is taking notes. I'm inconsistent with takin...
Read more

Week 1

Today I completed my first week at my new school program for computer science. This program is something I've been both anxious and excited for for several months. It's nice to finally be in the program, and not waiting for it to start. I think the biggest thing I learned this week was APA formatting. My past schools used MLA formatting. APA might be new to me, but it didn't feel to dissimilar to how I remember MLA being. That being said, I haven't had to cite anything in MLA since before the pandemic. It could be that I don't remember MLA very well. I also learned a little about the school I'm attending. Apparently it used to be a military base! After the military based was closed it was repurposed/reformed into a school. One of the essays we have to write in this class is analyzing an industry that we're interested in, and coming up with a plan to get a job in that industry. One of our assignments for this week was making an outline for this essay. This wa...
Read more