Chapter 5 Advanced SQL JDBCODBCADO NET Database System

Chapter 5: Advanced SQL JDBC/ODBC/ADO. NET Database System Concepts, 6 th Ed. ©Silberschatz, Korth and Sudarshan See www. db-book. com for conditions on re-use

Chapter 5: JDBC/ODBC/ADO. NET n Accessing SQL From a Programming Language l Dynamic SQL 4 JDBC 4 ODBC 4 ADO. NET l Embedded SQL

SQL in Application Code n SQL commands can be called from within a host program language (e. g. , C++ or Java). l SQL statements can refer to host variables (including special variables used to return status). l Must include a statement to connect to the right database. n Two main integration approaches: l Embed SQL in the host language 4 Embedded l SQL, SQLJ Create special API to call SQL commands 4 E. g. , JDBC. ODBC and ADO>NET

SQL in Application Code (Contd. ) Impedance mismatch: n SQL relations are (multi-) sets of records, with no a priori bound on the number of records. No such data structure exist traditionally in procedural programming languages. n SQL supports a mechanism called a cursor to handle this. l Recall discussions in PL/SQL.

JDBC and ODBC n API (application-program interface) for a program to interact with a database server n Application makes calls to l Connect with the database server l Send SQL commands to the database server l Fetch tuples of result one-by-one into program variables n ODBC (Open Database Connectivity) works with C, C++, C#, and Visual Basic l Other API’s such as ADO. NET sit on top of ODBC n JDBC (Java Database Connectivity) works with Java

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

The JDBC Usage Steps Ø Importing Packages Ø Registering the JDBC Drivers Ø Opening a Connection to a Database Ø Creating a Statement Object Ø Executing a Query and Returning a Result Set Object Ø Processing the Result Set Ø Closing the Result Set and Statement Objects Ø Closing the Connection NOTE: The TA will go over them in Lab.

JDBC Code public static void JDBCexample(String dbid, String userid, String passwd) { try { Class. for. Name ("oracle. jdbc. driver. Oracle. Driver"); Connection conn = Driver. Manager. get. Connection( "jdbc: oracle: thin: @db. yale. edu: 2000: univdb", userid, passwd); Statement stmt = conn. create. Statement(); … Do Actual Work …. stmt. close(); conn. close(); } catch (SQLException sqle) { System. out. println("SQLException : " + sqle); } }

JDBC Code (Cont. ) n Update to database try { stmt. execute. Update( "insert into instructor values(’ 77987’, ’Kim’, ’Physics’, 98000)"); } catch (SQLException sqle) { System. out. println("Could not insert tuple. " + sqle); } n Execute query and fetch and print results Result. Set rset = stmt. execute. Query( "select dept_name, avg (salary) from instructor group by dept_name"); while (rset. next()) { System. out. println(rset. get. String("dept_name") + " " + rset. get. Float(2)); }

JDBC Code Details n Getting result fields: l rs. get. String(“dept_name”) and rs. get. String(1) equivalent if dept_name is the first argument of select result. n Dealing with Null values l int a = rs. get. Int(“a”); if (rs. was. Null()) Systems. out. println(“Got null value”);

Prepared Statement n Prepared. Statement p. Stmt = conn. prepare. Statement( "insert into instructor values(? , ? , ? )"); p. Stmt. set. String(1, "88877"); p. Stmt. set. String(2, "Perry"); p. Stmt. set. String(3, "Finance"); p. Stmt. set. Int(4, 125000); p. Stmt. execute. Update(); p. Stmt. set. String(1, "88878"); p. Stmt. execute. Update(); n For queries, use p. Stmt. execute. Query(), which returns a Result. Set n WARNING: always use prepared statements when taking an input from the user and adding it to a query l NEVER create a query by concatenating strings which you get as inputs l "insert into instructor values(’ " + ID + " ’, ’ " + name + " ’, " + " ’ + dept name + " ’, " ’ balance + ")“ l What if name is “D’Souza”?

SQL Injection n Suppose query is constructed using "select * from instructor where name = ’" + name + "’" n Suppose the user, instead of entering a name, enters: l X’ or ’Y’ = ’Y n the resulting statement becomes: l "select * from instructor where name = ’" + "X’ or ’Y’ = ’Y" + "’" l which is: 4 select * from instructor where name = ’X’ or ’Y’ = ’Y’ l User could have even used 4 X’; update instructor set salary = salary + 10000; -n Prepared statement internally uses: "select * from instructor where name = ’X’ or ’Y’ = ’Y’ l Always use prepared statements, with user inputs as parameters l

Metadata Features n Result. Set metadata n E. g. , after executing query to get a Result. Set rs: l Result. Set. Meta. Data rsmd = rs. get. Meta. Data(); for(int i = 1; i <= rsmd. get. Column. Count(); i++) { System. out. println(rsmd. get. Column. Name(i)); System. out. println(rsmd. get. Column. Type. Name(i)); } n How is this useful?

Metadata (Cont) n Database metadata n Database. Meta. Data dbmd = conn. get. Meta. Data(); Result. Set rs = dbmd. get. Columns(null, "univdb", "department", "%"); // Arguments to get. Columns: Catalog, Schema-pattern, Table-pattern, // and Column-Pattern // Returns: One row for each column; row has a number of attributes // such as COLUMN_NAME, TYPE_NAME while( rs. next()) { System. out. println(rs. get. String("COLUMN_NAME"), rs. get. String("TYPE_NAME"); } n And where is this useful?

Transaction Control in JDBC n By default, each SQL statement is treated as a separate transaction that is committed automatically l bad idea for transactions with multiple updates n Can turn off automatic commit on a connection l conn. set. Auto. Commit(false); n Transactions must then be committed or rolled back explicitly l conn. commit(); l conn. rollback(); or n conn. set. Auto. Commit(true) turns on automatic commit.

Other JDBC Features n Calling functions and procedures l Callable. Statement c. Stmt 1 = conn. prepare. Call("{? = call some function(? )}"); l Callable. Statement c. Stmt 2 = conn. prepare. Call("{call some procedure(? , ? )}");

ODBC n Open Data. Base Connectivity(ODBC) standard l standard for application program to communicate with a database server. l application program interface (API) to 4 open a connection with a database, 4 send queries and updates, 4 get back results. n Applications such as GUI, spreadsheets, etc. can use ODBC n Was defined originally for Basic and C, versions available for many languages.

ODBC (Cont. ) n Each database system supporting ODBC provides a "driver" library that must be linked with the client program. n When client program makes an ODBC API call, the code in the library communicates with the server to carry out the requested action, and fetch results. n ODBC program first allocates an SQL environment, then a database connection handle. n Opens database connection using SQLConnect(). Parameters for SQLConnect: l connection handle, l the server to which to connect l the user identifier, l password n Must also specify types of arguments: l SQL_NTS denotes previous argument is a null-terminated string.

ODBC Code n int ODBCexample() { RETCODE error; HENV env; /* environment */ HDBC conn; /* database connection */ SQLAlloc. Env(&env); SQLAlloc. Connect(env, &conn); SQLConnect(conn, “db. yale. edu", SQL_NTS, "avipasswd", SQL_NTS); { …. Do actual work … } SQLDisconnect(conn); SQLFree. Connect(conn); SQLFree. Env(env); }

ADO. NET n API designed for Visual Basic. NET and C#, providing database access facilities similar to JDBC/ODBC l Partial example of ADO. NET code in C# using System, System. Data. Sql. Client; Sql. Connection conn = new Sql. Connection( “Data Source=<IPaddr>, Initial Catalog=<Catalog>”); conn. Open(); Sql. Command cmd = new Sql. Command(“select * from students”, conn); Sql. Data. Reader rdr = cmd. Execute. Reader(); while(rdr. Read()) { Console. Write. Line(rdr[0], rdr[1]); /* Prints result attributes 1 & 2 */ } rdr. Close(); conn. Close(); n Can also access non-relational data sources such as l OLE-DB (e. g. , MS Excel), XML data, Entity framework

Embedded SQL n The SQL standard defines embeddings of SQL in a variety of programming languages such as C, Java, and Cobol. n A language to which SQL queries are embedded is referred to as a host language, and the SQL structures permitted in the host language comprise embedded SQL. n EXEC SQL statement is used to identify embedded SQL request to the preprocessor EXEC SQL <embedded SQL statement > END_EXEC Note: this varies by language (for example, the Java embedding uses # SQL { …. }; )

Example Query n From within a host language, find the ID and name of students who have completed more than the number of credits stored in variable credit_amount. n Specify the query in SQL and declare a cursor for it EXEC SQL declare c cursor for select ID, name from student where tot_cred > : credit_amount END_EXEC

Embedded SQL (Cont. ) n The open statement causes the query to be evaluated EXEC SQL open c END_EXEC n The fetch statement causes the values of one tuple in the query result to be placed on host language variables. EXEC SQL fetch c into : si, : sn END_EXEC Repeated calls to fetch get successive tuples in the query result n A variable called SQLSTATE in the SQL communication area (SQLCA) gets set to ‘ 02000’ to indicate no more data is available n The close statement causes the database system to delete the temporary relation that holds the result of the query. EXEC SQL close c END_EXEC Note: above details vary with language. For example, the Java embedding defines Java iterators to step through result tuples.

Updates Through Cursors n Can update tuples fetched by cursor by declaring that the cursor is for update declare c cursor for select * from instructor where dept_name = ‘Music’ for update n To update tuple at the current location of cursor c update instructor set salary = salary + 100 where current of c

What is SQLJ? n SQLJ is a set of programming extensions that allow a programmer using the Java programming language to embed statements that provide SQL database requests. n SQLJ is similar to existing extensions for SQL that are provided for other programming languages, e. g. C/C++. n IBM, Oracle, and several other companies proposed SQLJ as an alternative to JDBC.

SQLJ Example #sql { … } ; SQL can span multiple lines Java host expressions in SQL statement throws java. sql. SQLException String bug = “spider”; #sql { INSERT INTO bugs (name, num. Legs) VALUES (: bug, : (get. Num. Legs(bug))) };