Application Programming Flashcards
Embedded SQL
placing of SQL statements directly in a host program
Static SQL
embedded SQL in the case that the SQL statements in the program are static (they are the same each time the program is ran)
all statements begin with an introducer and end with a terminator
Static Embedded SQL program layout (in C)
introducer = “EXEC SQL”
“EXEC SQL INCLUDE SQLCA”
“Exec SQL begin declare section”
[declare stuff]
“Exec SQL end declare section”
[fetch inputs from user]
“Exec SQL” [query here]
(The variable defined above referred to as ‘:variable’
Dynamic Embedded SQL query
char* sqlprog = [query here]
“EXEC SQL prepare dynprog from :sqlprog”
char account[] = blah
“Exec SQL execute dynprog using :acount” (replace ? with account variable)
Cursors
data structures that allow for the handling of SQL queries that return multiple rows
Cursor structure
“Exec SQL declare [cursor_name] Cursor for [query] ;”
“Exec SQL open [cursor name]”
“Exec SQL fetch into :variable1, :variable2 …;”
"while (sqlca.sqlcode != 100) {
printf();
EXEC SQL fetch into
:variable1, :variable2
}"
“Exec SQL close [cursor name]”
Components of Client Server architecture
database client - connects to DB to manipulate data
client software - provides general and specific capabilities
Two Tier Client Server Model
data server and client
client tier communicates directly with data tier.
Pros and cons of Two Tier model
pro: flexible need not be restricted to predefined queries
cons: security, more code shipped to client, not good for large organizations
3 tier client server model
user layer, middleware layer, database layer
3 tier client server model for ODBC
application program, odbc driver manager, db server
ODBC Starter code
“HENV ;” (environment variable)
“HDBC ;” database connection variable
“SQLAllocEnv(&env);” (allocate sql environment)
“SQLAllocConnect(env, &conn);” (allocate sql connection)
“SQLConnect(conn, server url, …)”
“SQLDisconnect(conn);”
“SQLFreeConnect(conn);”
“SQLFreeEnv(env);”
JDBC Code
“Class.forName(driver)” (find, open, load appropriate driver)
“Connection conn = DriverManager.getConnection(url, user, pass);”
“Statement stat = conn.createStatement();”
“ResultSet rs = null;”
“rs = stat.executeQuery(sql_command);”
while(rs.next()) {
var = rs.getString();
}
entity set
a set of entities (think a table) that share the same properties
domain
set of permitted values for each attribute
