Dynamic Sql 6g732m

Dynamic SQL

Dynamic SQL 





Allows programs to construct and submit SQL queries at run time. Example of the use of dynamic SQL from within a C program. char * sqlprog = ”update set balance = balance ∗1.05 where -number = ?” EXEC SQL prepare dynprog from :sqlprog; char [10] = ”A-101”; EXEC SQL execute dynprog using :; The dynamic SQL program contains a ?, which is a place holder for a value that is provided when the SQL program is executed.

1. ODBC 

Open DataBase Connectivity(ODBC) standard 



standard for application program to communicate with a database server. application program interface (API) to open a connection with a database,  send queries and updates,  get back results. 

ODBC Code int ODBCexample()

{ RETCODE error; HENV env; /* environment */ HDBC conn; /* database connection */ SQLAllocEnv(&env); SQLAllocConnect(env, &conn); SQLConnect(conn, ”aura.bell-labs.com”, SQL NTS, ”avi”, SQL NTS,”aviwd”, SQL NTS);

{ char branchname[80]; float balance; int lenOut1, lenOut2; HSTMT stmt; SQLAllocStmt(conn, &stmt); char * sqlquery = ”select branch name, sum (balance) from group by branch name”; error = SQLExecDirect(stmt, sqlquery, SQL NTS); if (error == SQL SUCCESS)

{ SQLBindCol(stmt, 1, SQL C CHAR, branchname , 80, &lenOut1);

SQLBindCol(stmt, 2, SQL C FLOAT, &balance, 0 , &lenOut2); while (SQLFetch(stmt) >= SQL SUCCESS)

{ printf (” %s %g\n”, branchname, balance);

} } } SQLFreeStmt(stmt, SQL DROP); SQLDisconnect(conn); SQLFreeConnect(conn); SQLFreeEnv(env);

}

JDBC 







JDBC is a Java API for communicating with database systems ing SQL JDBC s a variety of features for querying and updating data, and for retrieving query results JDBC also s metadata retrieval, such as querying about relations present in the database and the names and types of relation attributes Model for communicating with the database:  Open a connection  Create a “statement” object  Execute queries using the Statement object to send queries and fetch results  Exception mechanism to handle errors

JDBC Code public static void JDBCexample(String dbid, String id, String wd) try

ResultSet rset = stmt.executeQuery(”select branch name, avg (balance) from group by branch name”); while (rset.next())

{

{

Class.forName (”oracle.jdbc.driver.OracleDriver”); Connection conn = DriverManager.getConnection( ”jdbc:oracle:thin:@aura.bell-labs.com: 2000:bankdb”,id, wd); Statement stmt = conn.createStatement(); try

System.out.println(rset.getString(”branch name”) + ” ” +rset.getFloat(2));

{

catch (SQLException sqle)

stmt.executeUpdate(”insert into values( ’A-9732’, ’Perryridge’, 1200)”);

{

{

} catch (SQLException sqle) { System.out.println(”Could not insert tuple. ” + sqle);

}

} stmt.close(); conn.close();

}

System.out.println(”SQLException : ” + sqle); } }

Client–Server Architectures 



Centralized database systems are those that run on a single computer system and do not interact with other computer systems. Client–server systems, on the other hand, have functionality split between a server system, and multiple client systems.

A centralized computer system

Client–Server Systems

Distributed Databases It consists of a collection of sites, connected together via some kind of communication network in which  Each site is a full database system site on its own right,  The sites have agreed to work together so that a a at any site can access data anywhere in the network exactly as if the data were all stored at the ’s own site

Reasons for building distributed database systems   

sharing of data Autonomy availability

Objectives 1.

2. 3. 4. 5.

6. 7. 8. 9. 10. 11. 12.

Local autonomy No reliance on a central site (bottleneck, vulnerability) Continuous operation (reliability, availability) Location independence Fragmentation independence Replication independence Distributed Query Processing (optimization) Distributed Transaction Management (concurrency, recovery) Hardware independence OS independence Network independence DBMS independence