Call-Level Interface - Database Systems - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Call-Level Interface - Database Systems - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Database-Connection Libraries

Call-Level Interface Java Database Connectivity PHP

An Aside: SQL Injection

• SQL queries are often constructed by programs.
• These queries may take constants from user input.
• Careless code can allow rather unexpected queries to be constructed and executed.

User (Who Is Not Bill Gates) Types

4

Name:

Password:

Your account number is 1234-

gates’ --

who cares?

Comment in Oracle

The Query Executed

SELECT acct FROM Accounts

WHERE name = ’gates’ --’ AND

passwd = ’who cares?’

5

All treated as a comment

Three-Tier Architecture

• A common environment for using a database has three tiers of processors: 1. Web servers --- talk to the user. 2. Application servers --- execute the business logic. 3. Database servers --- get what the app servers need from the database.

Example: Amazon

• Database holds the information about products, customers, etc.
• Business logic includes things like “what do I do after someone clicks ‘checkout’?” - Answer: Show the “how will you pay for this?” screen.

Diagram to Remember

10

Environment Connection

Statement

SQL/CLI

• Instead of using a preprocessor (as in embedded SQL), we can use a library of functions. - The library for C is called SQL/CLI = “ Call-Level Interface .” - Embedded SQL’s preprocessor will translate the EXEC SQL … statements into CLI or similar calls, anyway.

Handles

• Function SQLAllocHandle(T,I,O) is used to create these structs, which are called environment, connection, and statement handles. - T = type, e.g., SQL_HANDLE_STMT. - I = input handle = struct at next higher level (statement < connection < environment). - O = (address of) output handle.

Example: SQLAllocHandle

SQLAllocHandle(SQL_HANDLE_STMT,

myCon, &myStat);

• myCon is a previously created connection

handle.

• myStat is the name of the statement handle

that will be created.

Example: Prepare and Execute

SQLPrepare(myStat, ”SELECT beer, price FROM Sells WHERE bar = ’Joe’’s Bar’”, SQL_NTS);

SQLExecute(myStat);

16

This constant says the second argument is a “null-terminated string”; i.e., figure out the length by counting characters.

Direct Execution

• If we shall execute a statement S only once, we can combine PREPARE and EXECUTE with:

SQLExecuteDirect(H,S,L);

• As before, H is a statement handle and L is the length of string S.

Accessing Query Results

• When we fetch a tuple, we need to put the components somewhere.
• Each component is bound to a variable by the function SQLBindCol.
• This function has 6 arguments, of which we shall show only 1, 2, and 4: 1 = handle of the query statement. 2 = column number. 4 = address of the variable.

Example: Binding

• Suppose we have just done SQLExecute(myStat), where myStat is the handle for query

SELECT beer, price FROM Sells

WHERE bar = ’Joe’’s Bar’

• Bind the result to theBeer and thePrice:

SQLBindCol(myStat, 1, , &theBeer, , );

SQLBindCol(myStat, 2, , &thePrice, , );