SQL in applications

What is the preprocessor approach in SQL in applications

A preprocessor is a tool that scans your code and converts SQL into equivalent function calls in the host programming language before compilation so the compiler can understand.

What is the library (API) approach

The library approach uses an API which is a set of prewritten functions that we can use to connect to a Db and run sql.

Dynamic SQL

SQL statements constructed at runtime. Could include user inputted arguments.

Embedded SQL

Static sql statements

Pros of Preprocessor approach

Able to be used in many languages

Cons of the Preprocessor approach

Have to learn preprocessor commands and explicit cursors

What problems do cursors solve?

How much space we need to have to store query results. We can use a cursor to get one tuple at a time and allow DBMS and OS to do buffering

Explicit Cursors

Have to manually create and manage these cursors. Mainly is the preprocessor approach.

Implicit Cursors

System creates and manages cursor for you, usually with library approach

Pros of the library approach

You can use it using implicit cursors and it is like an API

Cons of the library approach

You may have to install a third party add on like a driver

ODBC stands for and does what

Open database connectivity , connects c programs to DBMS

What are the core capabilities of JDBC 1-3

1. Connect to a Data Source

2. Send SQL statements to the source

3. Process the returned results and messages

Steps to use JDBC 1-3

1. import java.sql and load jdbc driver. initate the driver and connect to DBMS.

2. Send sql statements to the source by creating a statement and result set. use executequery() to run sql.

3. Process the results by using the cursor (answer.next() and answer.getString(“sno”))