Web program-SQL.pptx Web program-SQL.pptx
iqramalik30328
7 views
16 slides
Aug 06, 2024
Slide 1 of 16
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
About This Presentation
Web program-SQL.pptx
Slide Content
6-Aug-24 Advanced Programming Spring 2002 Henning Schulzrinne Dept. of Computer Science Columbia University
6-Aug-24 Advanced Programming Spring 2002 2 SQL basics relational database: tables with labeled columns, combined into database columns are atomic types: create table person ( person integer unsigned auto_increment primary key, name varchar(40), state enum ('', 'AK', 'AL', ...), biography text, verified date, index(name) )
6-Aug-24 Advanced Programming Spring 2002 3 SQL basics Integer: tinyint, smallint, mediumint, int(eger), bigint Floating point: float, double, real Decimal: decimal( m,d ) (for $) Date: date, datetime, timestamp, time, year String: char( N ), varchar( N ), tinyblob, tinytext, blob, text, enum, set
6-Aug-24 Advanced Programming Spring 2002 4 SQL basics Retrieval: SELECT field1, field2 FROM table WHERE condition ORDER BY expression Insertion: INSERT table SET field1=value1,field2=value2, ... Update: UPDATE table SET field1=value1, field2=value2 WHERE expression Delete row: DELETE FROM table WHERE expression
6-Aug-24 Advanced Programming Spring 2002 5 SQL basics: joins Join two tables that have a common value ("product") e.g., SELECT lastname,city.name FROM person,city WHERE city.zip=person.zip AND lastname='Jones'
6-Aug-24 Advanced Programming Spring 2002 6 SQL Get description of table: $ mysql -h grandcentral -u cs3995 -p mysql> use grades mysql> describe students; +-----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+-------+ | firstname | text | YES | | NULL | | | lastname | text | YES | | NULL | | | points | int(11) | YES | | NULL | | +-----------+---------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
6-Aug-24 Advanced Programming Spring 2002 7 SQL Python interface import MySQLdb import MySQLdb.cursors try: db = connect(host='grandcentral', user='cs3995', passwd='cs3995', db='grades') except MySQLdb.Error, e: print "Error %d: %s" % (e.args[0], e.args[1]) sys.exit(1) c = db.cursor() c.execute("SELECT ... FROM ...") results = c.fetchall() # list of tuples c.close()
6-Aug-24 Advanced Programming Spring 2002 8 SQL Python interface Results are just tuples, with fields in order of table definition can also fetch one row at a time: c.execute("SELECT firstname,lastname FROM students ORDER BY lastname") print "<ul>" while (1): student = c.fetchone() if student == None: break print "<li>", student, student[0] print "</ul>"
6-Aug-24 Advanced Programming Spring 2002 9 Python SQL – dictionary cursor Map rows to dictionary elements instead of list elements: c.close() c = db.cursor(MySQLdb.cursors.DictCursor) c.execute("SELECT firstname,lastname FROM students") results = c.fetchall() for row in results: print "%s, %s" % (row["firstname"], row["lastname"]) print "%d rows were returned" % c.rowcount
6-Aug-24 Advanced Programming Spring 2002 10 Servlet life cycle server application loads ServletClass creates instance via no-args constructor servers call servlet's init() method server calls service( req, res ) method for each request (often, with class name as URL), possibly concurrently servers calls destroy() on shutdown
6-Aug-24 Advanced Programming Spring 2002 11 HTTP requests as servlets HTTP method GET, PUT, POST, ... doGet, doPut, doPost subclass of HttpServlet overrides default implementation
6-Aug-24 Advanced Programming Spring 2002 12 Servlet example import java.io.*; import javax.servlet.*; import javax.servlet.http.*; public class HelloClientServlet extends HttpServlet { protected void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { res.setContentType("text/html"); PrintWriter out = res.getWriter(); out.println("<HTML><HEAD><TITLE>Hello Client!</TITLE>" + "<HEAD><BODY>Hello Client!</BODY><HTML>"); out.close(); } public String getServletInfo() { return "HelloClientServlet 1.0 by Stefan Zeiger"; } }
6-Aug-24 Advanced Programming Spring 2002 13 2-tier architecture "client-server", "fat client" e.g., ODBC on client (PC), accessing SQL database business logic on PC (-) transport data across network (-) need applications for each platform (-) need to update applications on many desktops
6-Aug-24 Advanced Programming Spring 2002 14 n-tier architecture
6-Aug-24 Advanced Programming Spring 2002 15 n-tier architecture client tier: receives user events (keyboard, mouse) presentation of data user interface e.g., Java applets, web browser, thin client application application-server tier: "business logic" actual data processing, algorithms can be component-based (Java Beans)
6-Aug-24 Advanced Programming Spring 2002 16 n-tier architecture Data-server tier data storage relational and legacy databases all tiers could run on same machine, but usually separated HTTP (or SOAP) from client to server Corba or SOAP or remote-SQL between server tiers Advantages: independent of storage model simpler authentication to database
Tags
Categories
GeneralDownload
Download Slideshow Get the original presentation fileQuick Actions
Statistics
- Views 7
- Slides 16
- Age 486 days
Related Slideshows
22
Pray For The Peace Of Jerusalem and You Will Prosper
RodolfoMoralesMarcuc
33 views
26
Don_t_Waste_Your_Life_God.....powerpoint
chalobrido8
36 views
31
VILLASUR_FACTORS_TO_CONSIDER_IN_PLATING_SALAD_10-13.pdf
JaiJai148317
33 views
14
Fertility awareness methods for women in the society
Isaiah47
30 views
35
Chapter 5 Arithmetic Functions Computer Organisation and Architecture
RitikSharma297999
29 views
5
syakira bhasa inggris (1) (1).pptx.......
ourcommunity56
30 views