CS代写 SQL and Programming Languages

程序代写 CS代考 / c++代写, database, javascript, Java代写代考, Python代写代考, SQL代写代考

SQL and Programming Languages

❑ Example:InteractiveUserinterfaceofDB2/Postgresql as we have used it so far
❑ Execution

Copyright By PowCoder代写 加微信 powcoder

✩ We can type any SQL statement.
✩ Statement is sent to DBMS
✩ Statement is executed within DBMS
✩ Response is sent back
✩ User can be on same machine or other machine than DBMS
❑ Limitations
✩ SQL is intended lacks features of traditional application
programming languages.
421B: Database Systems – Programming with SQL

SQL in Application Code
❑ SQLcommandscanbecalledfromwithinahostlanguage (e.g., C++ or Java) program
❑ Twomainintegrationapproaches
✩ Embed SQL in host language (C with Embedded SQL, SQLJ) ✩ Create special API to call SQL commands (JDBC)
❑ Program-DBinteraction
✩ Application program executes at client side.
✩ Each SQL statement is sent to the server, executed there, and the result returned to the client
421B: Database Systems – Programming with SQL

JDBC API: Architecture
DB2 at xxxx-comp421.cs.mcgill.ca
421B: Database Systems – Programming with SQL
JDBC Driver DB2
Application Program
Java.sql package
Driver Oracle
Driver Manager

Database APIs: JDBC
❑ Special,standardizedinterfaceofobjectsand method calls
❑ AttemptstobeDBMS-neutral
✩ A “driver” traps the calls and translates them into DBMS
specific calls
✩ Database can be across a network
❑ Fourcomponents
✩ At client:
● Application (submits SQL statements)
● Driver manager (handles drivers for different DBMS)
● Driver (connects to a data source, transmits requests, and returns/translates results and error codes
✩ At server:
● Data source (processes SQL statements)
421B: Database Systems – Programming with SQL

Execution Overview ❑ Load Driver
✩ Since only known at runtime which DBMS the application is going to access, drivers are loaded dynamically
❑ Connect to Data Source
✩ Connection Object represents the connection
between a Java client and DBMS
✩ Each connection identifies a logical session.
❑ Execute SQL statements
421B: Database Systems – Programming with SQL

Connecting to Database
import java.sql.*;
class connectExample {
public static void main( String args [] ) throws SQLException { // Load the DB2 JDBC driver
// Alternative 1:
// Class.forName(“com.ibm.db2.jcc.DB2Driver”);
// Alternative 2:
DriverManager.registerDriver (new com.ibm.db2.jcc.DB2Driver());
// Connect to the database
Connection conn = DriverManager.getConnection(
“jdbc:db2://comp421.cs.mcgill.ca:50000/cs421”,
“user_name”, “user_password” …
// Close the connection
Replace with actual server name
conn.close();
JDBC drivers are not part of standard Java runtime environment. You may have to download them from the vendors separately.
421B: Database Systems – Programming with SQL

SQL statements
❑ CreateastatementobjectsowecansubmitSQL statements to the driver
Statement stmt = con.createStatement();
❑ Closestatementwhennomoreneeded
stmt.close();
❑ Update/insert/createstatement
Don’t create schemas From application
stmt.executeUpdate(“CREATE TABLE Skaters ” +
“(sid INT, name CHAR(40), rating INT, age INT)” );
String sqlString = “INSERT INTO Skaters ” + “VALUES (58, ‘Lilly’, 10, 12)”;
stmt.executeUpdate(sqlString);
✩ DDL return always zero,
✩ insert/update/delete return the number of affected tuples
421B: Database Systems – Programming with SQL

Queries with several result tuples
❑ MismatchbetweenstandardSQLqueryand programming language:
✩ Result of a query is usual a SET of tuples with no a priori bound on the number of records
✩ No support for such a data type in conventional programming languages
❑ Solution: get result tuples step by step ❑ CursorConcept:
✩ Think about a cursor as a pointer to successive tuples in the result relation. At a given moment the cursor can be
● Beforethefirsttuple ● Onatuple
● Afterthelasttuple
421B: Database Systems – Programming with SQL

SQL Select Statements
using * is bad practice !
ResultSet rs = stmt.executeQuery(“SELECT * FROM Skaters”);
while ( rs.next() ) {
sid = rs.getInt(“sid”);
sname = rs.getString(”name”); age = rs.getInt(3);
rating = rs.getInt(4);
using index number is bad practice ! Use column names
System.out.println(“skater “ + sname + “ has age “ + age + “ and rating “ + rating);
rs.close();
✩ Cursor initially set just before first row
✩ rs.next makes cursor point to the next row
✩ rs.getInt/rs.getString etc. retrieve individual attributes of row ● Input is either name or position of attribute
✩ methods to find out where you are in the result set:
● getRow, isFirst, isBeforeFirst, isLast, isAfterLast.
✩ Methods to get tuples
● next(); previous(); absolute(int num); relative(int num); first(); last()
421B: Database Systems – Programming with SQL

JDBC Statements
❑ Statement
✩ Includes methods for executing text queries
❑ ResultSet
✩ Contains the results of the execution of a query
✩ We can receive all result rows iteratively
✩ For each row we can receive each column explicitly ✩ Implementation?
421B: Database Systems – Programming with SQL

SQL Injection Attacks
What could go wrong?
inssql = “INSERT INTO customers (‘” + username + “‘,”); stmt.executeUpdate(inssql);
What if somebody entered a username that is D’Silva ?
What if somebody entered a username that is D’;DROP TABLE customers;–‘ ?
https://en.wikipedia.org/wiki/SQL_injection
421B: Database Systems – Programming with SQL

Dynamic vs. Prepared Statements
❑ DynamicExecutionatDBMS
✩ Parses statement (1), builds execution plan (2), optimizes execution
plan (3), executes (4), and returns (5)
✩ If statement is called many times (e.g., loop), steps (1)-(3) are executed over and over again
❑ PreparedStatement
✩ Represents precompiled and stored queries
✩ Can contain parameters; values determined at run-time
421B: Database Systems – Programming with SQL

Prepared Statements
// Statement can have input parameters; indicated with ? PreparedStatement prepareCid =
con.prepareStatement(“SELECT cid
FROM Participates where sid = ?”)
while () {
// get cid input from user into variable x;
// provide values for input parameters prepareCid.setInt(1, x);
ResultSet rs = prepareCid.executeQuery() ; while (rs.next())
System.out.println(“skater “ + x +
“participates in competition “
+ rs.getInt(1));
421B: Database Systems – Programming with SQL

Error Handling
❑ two levels of error conditions: ✩ SQLException and SQLWarning
❑ InJava,statementswhichareexpectedto“throw” an exception or a warning are enclosed in a try block.
✩ If a statement in the try block throws an exception or a warning, it can be “caught” in one of the corresponding catch statements.
✩ Each catch statement specifies which exceptions it is ready to “catch”.
421B: Database Systems – Programming with SQL

Error Handling
stmt.executeUpdate(“CREAT TABLE Skaters ” +
“(sid INT, name CHAR(40), rating INT, age INT)” );
}catch(SQLException e) { System.err.println(”msg: ” + e.getMessage() +
“code: “ + e.getErrorCode() + state: “ + e.getSQLState());
❑ msg:Anunexpectedtoken”CREAT”wasfoundfollowing “BEGIN-OF-STATEMENT”. Expected tokens may include: ““.
❑ code:-104
❑ state:42601
To get next exception: e.getNextException
421B: Database Systems – Programming with SQL

Data Type Mapping
❑ There exist default mappings between JDBC datatypes (defined in java.sql.Types), and native Java datatypes
✩ Examples
Java.lang.String
Java.lang.String
Java.sql.Date
getBoolean
REAL float
421B: Database Systems – Programming with SQL

Database Specific Programming Languages
❑ Java with JDBC or C with Embedded SQL run as an application in an outside process
✩ Application program makes calls to the DBS ✩ Transfer of requests and results through a
communication channel
❑ Stored Procedures
✩ Programs that run within the DBS process
✩ They are called from the outside but execute within the DBS
421B: Database Systems – Programming with SQL

Database Specific Programming Languages
❑ Procedural extension to SQL
✩ Certain Database Systems allow the use of standard
programming languages (with extensions) ● E.g. DB2 supports Java, C
✩ Specialized programming languages specifically designed for DB access
● DB2:SQLstoredprocedure(standard):speciallydesigned programming language
● PostgreSQL:PL/pgSQL-SQLProceduralLanguage
● Hasconstructsofastandardproceduralprogramminglanguage
▲variables, assignments, flow control constructs, …
● Canhaveinputandoutputparameters
421B: Database Systems – Programming with SQL

SQL Procedure
CREATE PROCEDURE MIN_SALARY (IN deptnumber SMALLINT, IN minsal DOUBLE) LANGUAGE SQL
DECLARE v_salary DOUBLE;
DECLARE v_id SMALLINT;
DECLARE at_end INT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE ‘02000’;
DECLARE C1 CURSOR FOR
SELECT id, salary FROM staff WHERE did = deptnumber;
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1; OPEN C1;
FETCH C1 INTO v_id, v_salary;
WHILE at_end = 0 DO
IF (v_salary < minsal) THEN UPDATE staff SET salary = minsal WHERE id = v_id; FETCH C1 INTO v_id, v_salary; END WHILE; 421B: Database Systems - Programming with SQL ❑ Stored procedure stored as executable at DBMS ❑ Client makes one call to call stored procedure ✩ Embedded SQL EXEC SQL CALL min_salary(5,2000); ✩ JDBC CallableStatement cs = con.prepareCall(“{call min_salary(?,?)}”); cs.setInt(1, 5); cs.setInt(2,2000); ResultSet rs = cs.executeQuery(); ❑ Stored procedure executed within DBMS ✩Less context switches ✩Less calls from client to server => less network traffic
421B: Database Systems – Programming with SQL

Application Architecture ❑ Applicationscanbebuiltusingoneoftwoarchitectures
✩ Two tier model
● Application program running at user site directly uses
JDBC/Embedded SQL to communicate with the database
● Flexible, no restrictions
● Security problems (passwords)
● Code shipping problematic in case of upgrades
● Not appropriate across organizations
✩ Three tier model
● User program only provides presentation logic (browser): client tier
● Business semantic (JDBC programs) in application server: middle tier
● Application server communicates with database: backend tier
421B: Database Systems – Programming with SQL

Three Tier Model
HTTP/ others
❑ Web-Server:
✩ communication (Http and other protocols)
● receives and unmarshals requests
● sends responses in message format back to browser
✩ presentation
● generates the pages from result set
Application Server
✩ implements the business logic: application programs 421B: Database Systems – Programming with SQL
Web- Server
Application Server
Database Server

Connection Pooling
Network HTTP/ others
Web- Server
Application Server
DB Connection Pool
Database Server
Connection Pooling
✩ In practice, client-side database connections are not always active, ● Ex: when you browse amazon to make purchases.
✩ Establishing a database connection takes time, costs resources.
✩ Application Server can establish a set number of database
✩ When a client request arrives that needs database processing, an idle DB connection is selected from the Pool to perform it.
✩ All end users share the same database user id. DB authentication is stored in Application Server, agnostic to end users.
connections in advance.
421B: Database Systems – Programming with SQL

Connection Pooling
Context envCtx = (Context) new
InitialContext().lookup(“java:comp/env”);
datasource = (DataSource)
envCtx.lookup(“jdbc/MyDataSource”);
Connection con = datasource.getConnection();
// JDBC calls…
421B: Database Systems – Programming with SQL

Example: Minerva ❑ Client Program (Browser)
✩ Html pages
● Log into System (faculty, student)
● See courses, register, transcript, grants,
✩ might use JavaScript and Cookies ❑ Web-Server
✩ gets requests from browser
✩ analyzes them and calls application server methods
✩ gets responses from application server and generates dynamic web- pages
✩ uses Servlets / JSP
❑ Application servers
✩ Implements methods
✩ Retrieve data from database
✩ Modify database
✩ uses Servlets / Beans / general programs
❑ Database System
✩ Students, courses, grant information, …
421B: Database Systems – Programming with SQL

Application Design Choices
❑ Should we check if the date_of_birth is valid at the Webpage or at DB ?
❑ If we have to increment the rating of all skaters, where can it efficiently executed ? ( Cursor at the application code vs Vs Stored Procedure Vs a single update query in the DB)
❑ Rule of thumb
✩ If the objective is to trap user errors, it is more efficient to do it at client side.
✩ Complex iterative processing over large data that requires procedural logic will benefit from stored procedures, as data does not “leave” the database server and therefore does not incur network overhead.
✩ For updates on large amounts of data where update to each tuple is independent of other tuples, it is best to do regular SQL update – Modern DBMS systems can process tuples in multiple parallel batches, thus providing several leaps of bounds of performance !
✩ In your home work however, we encourage you to do most of the programming in the database and not on the client side, so as to explore and learn the features and functionality !
421B: Database Systems – Programming with SQL

Embedded Databases
Python example: SQLite embedded database.
dbcon = sqlite3.connect(‘datafile’, …)
cursor = dbcon.cursor()
cursor.execute(‘SELECT ….’)
for row in cursor.fetchall():
print(row)
cursor.close()
https://docs.python.org/3/library/sqlite3.html
Data (filesystem)
421B: Database Systems – Programming with SQL
Application Program
database engine (package)

程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com

Related Posts