Database Application Development

1
Database Application Development

• MSIT 124 Database Systems

2
Review of SQL

• The most often used command in SQL is the SELECT
  statement, used to retrieve data. The full MySQL
  syntax is
• SELECT STRAIGHT_JOIN
• SQL_SMALL_RESULT
  SQL_BIG_RESULT SQL_BUFFER_RESULT
• SQL_CACHE SQL_NO_CACHE
• SQL_CALC_FOUND_ROWS
  HIGH_PRIORITY
• DISTINCT DISTINCTROW ALL
• select_expression,...
• INTO OUTFILE DUMPFILE 'file_name'
  export_options
• FROM table_references
• WHERE where_definition
• GROUP BY unsigned_integer
  col_name formula ASC DESC, ...
• HAVING where_definition
• ORDER BY unsigned_integer
  col_name formula ASC DESC ,...
• LIMIT offset, rows
• PROCEDURE procedure_name FOR
  UPDATE LOCK IN SHARE MODE

3
Review of SQL

• Data Retrieval (Queries)
• Simple Selects
• Joins/Join Types
• Aggregate Operators
• Aggregation by Groups and Group Condition (GROUP
  BY HAVING clause)
• Subqueries/Subqueries in FROM
• Union, Intersect, Except

4
Review of SQL (contd)

• Data Definition
• Create Table
• Data Types in SQL
• Create Index
• CREATE INDEX index_name ON table_name (
  name_of_attribute )
• Create View
• CREATE VIEW view_name AS select_stmt
• Drop Table, Drop Index, Drop View

5
Review of SQL (contd)

• Data Manipulation
• Insert Into
• Update
• Delete From

6
Views (Virtual Tables) in SQL

• The Command to specify a view is CREATE VIEW.
  The view is given a (virtual) table name, a list
  of attribute names, and a query to specify the
  contents of the view.
• V1 CREATE VIEW WORKS_ON1
  AS SELECT FNAME,LNAME,PNAME, HOUR FROM
  EMPLOYEE,PROJECT,WORKS_ON WHERE SSNESSN
  AND PNOPNUMBER
• V1 CREATE VIEW DEPT_INFO(DEPT_NAME,
  NO_OF_EMPS, TOTAL_S AS
  SELECT DNAME,COUNT (),SUM(SALARY) FROM
  DEPARTMENT, EMPLOYEE WHERE DNODNUMBER
  GROUP BY DNAME

7
VIEW Implementation and View Update
UV1 UPDATE WORKS_ON1
SET PNAME ProductY WHERE LN
AMESmith AND FNAME John AND PNAME
ProductX (a)
UPDATE WORKS_ON
SET PNO (SELECT PNUMBER FROM PORJECT
WHERE PNAMEProductY) WHERE
ESSN IN (SELECT SSN FROM EMPLOYEE
WHERE LNAMESmith AND
FNAME John) AND PNO
IN (SELECT PNUMBER FROM PROJECT
WHERE PNAME ProductX)
8
VIEW Implementation and View Update
(b) UPDATE PROJECT
SET PNAME ProductY WHERE PNA
ME ProductX UV2 UPDATE DEPT_INFO SET T
OTAL-SAL 100000 WHERE DNAME Research
9
System Catalog

• In every SQL database system system catalogs are
  used to keep track of which tables, views indexes
  etc. are defined in the database
• These system catalogs can be queried as if they
  were normal relations.
• Example. Display list of databases and database
  objects (in MySQL)
• SHOW DATABASES
• SHOW TABLES
• DESCRIBE lttablegt
• USE mysql
• SELECT FROM user
• SELECT FROM db
• SELECT FROM host

10
Accessing a Database

• In MySQL
• Running the MySQL terminal monitor programs (e.g.
  mysql) which allows you to interactively enter,
  edit, and execute SQL commands.
• Using an existing native or third party front-end
  tools like MySQLGUI and phpMyAdmin to create and
  manipulate a database.

11
Accessing a Database (contd)

• MySQL Application Programming Interface (API).
• MySQL PHP API
• MySQL Perl API (DBI/DBD)
• MySQL C/C API
• MySQL Python API
• MySQL Tcl API
• MySQL Eiffel Wrapper
• MySQL ODBC Support
• MySQL Java Connectivity (JDBC)

12
Accessing a Database via the Web
13
(Typical) Structure of a Database Application
Program

• Connect (to a data source name or database
  server/database using access parameters such as
  username and password).
• Execute a query on the connection (any valid SQL
  query supported by the host language DB API, ODBC
  or JDBC driver)
• Process the results (e.g. do whatever you want
  with the results using a WHILE or FOR loop).
• De-allocate resources used for storing the
  results (free up memory used to reference the
  results GOOD PROGRAMMING PRACTICE.
• Close (the connection to the data source,
  database server/database GOOD PROGRAMMING
  PRACTICE.

14
PHP-MySQL Functions (APIs)

• mysql_affected_rows -- Get number of affected
  rows in previous MySQL operation
• mysql_change_user --  Change logged in user of
  the active connection
• mysql_character_set_name -- Returns the name of
  the character set
• mysql_close -- Close MySQL connection
• mysql_connect -- Open a connection to a MySQL
  Server
• mysql_create_db -- Create a MySQL database
• mysql_data_seek -- Move internal result pointer
• mysql_db_name -- Get result data
• mysql_db_query -- Send a MySQL query
• mysql_drop_db -- Drop (delete) a MySQL database

15
PHP-MySQL Functions (contd)

• mysql_errno --  Returns the numerical value of
  the error message from previous MySQL operation
• mysql_error --  Returns the text of the error
  message from previous MySQL operation
• mysql_escape_string --  Escapes a string for use
  in a mysql_query.
• mysql_fetch_array --  Fetch a result row as an
  associative array, a numeric array, or both.
• mysql_fetch_assoc --  Fetch a result row as an
  associative array
• mysql_fetch_field --  Get column information from
  a result and return as an object
• mysql_fetch_lengths --  Get the length of each
  output in a result
• mysql_fetch_object -- Fetch a result row as an
  object

16
PHP-MySQL Functions (contd))

• mysql_fetch_row -- Get a result row as an
  enumerated array
• mysql_field_flags --  Get the flags associated
  with the specified field in a result
• mysql_field_len --  Returns the length of the
  specified field
• mysql_field_name --  Get the name of the
  specified field in a result
• mysql_field_seek --  Set result pointer to a
  specified field offset
• mysql_field_table --  Get name of the table the
  specified field is in
• mysql_field_type --  Get the type of the
  specified field in a result

17
PHP-MySQL Functions (contd)

• mysql_free_result -- Free result memory
• mysql_get_client_info -- Get MySQL client info
• mysql_get_host_info -- Get MySQL host info
• mysql_get_proto_info -- Get MySQL protocol info
• mysql_get_server_info -- Get MySQL server info
• mysql_info --  Get information about the most
  recent query
• mysql_insert_id --  Get the id generated from the
  previous INSERT operation
• mysql_list_dbs --  List databases available on a
  MySQL server
• mysql_list_fields -- List MySQL result fields
• mysql_list_processes -- List MySQL processes

18
PHP-MySQL Functions (contd)

• mysql_list_tables -- List tables in a MySQL
  database
• mysql_num_fields -- Get number of fields in
  result
• mysql_num_rows -- Get number of rows in result
• mysql_pconnect --  Open a persistent connection
  to a MySQL server
• mysql_ping -- Ping a server connection or
  reconnect if there is no connection
• mysql_query -- Send a MySQL query
• mysql_real_escape_string --  Escapes special
  characters in a string for use in a SQL
  statement, taking into account the current
  charset of the connection.
• mysql_result -- Get result data
• mysql_select_db -- Select a MySQL database

19
PHP-MySQL Functions (contd)

• mysql_stat -- Get current system status
• mysql_tablename -- Get table name of field
• mysql_thread_id -- Return the current thread id
• mysql_unbuffered_query --  Send an SQL query to
  MySQL, without fetching and buffering the result
  rows

20
PHP-MySQL mysql_fetch Constants

• The function mysql_fetch_array() uses a constant
  for the different types of result arrays. The
  following constants are defined

21
PHP-MySQL Example
22
myphpmenu.html

• lthtmlgt
• ltheadgt
• lttitlegtMYPHP Tests Main Menult/titlegt
• lt/headgt
• ltbodygt
• lthrgt
• lth3gtMYSQL-PHP TESTSlt/h3gt
• lthrgt
• lta href"http//appserver.cs.xu.edu.ph/gsd/myphps
  el.php"gt1. Display
• Database Recordslt/agtltbrgt
• lta href"http//appserver.cs.xu.edu.ph/gsd/myphpa
  dd.html"gt2. Add Database
• Recordslt/agtltbrgt
• lta href"http//appserver.cs.xu.edu.ph/gsd/myphpu
  pd.html"gt3. Update
• Database Recordslt/agtltbrgt
• lta href"http//appserver.cs.xu.edu.ph/gsd/myphpd
  el.html"gt4. Delete
• Database Recordslt/agtltbrgt
• lt/bodygt
• lt/htmlgt

23
myphpsel.php

• lt?php
• / Connection details - customize this /
• host "localhost"
• user "testuser"
• password "testpassword"
• database "testdb"
• table "pet"
• / Connecting, selecting database /
• link mysql_connect(host, user,
  password) or die("Could not connect")
• mysql_select_db(database) or die("Could not
  select database")
• print "Connected successfully to
  ltbgtdatabaselt/bgt at ltbgthostlt/bgtlthrgt."

24
myphpsel.php (contd)

• / Performing SQL query /
• query "SELECT FROM table"
• result mysql_query(query) or die("Query
  failed")
• / Printing results in HTML /
• print "lttable border1gt\n"
• while (line mysql_fetch_array(result,
  MYSQL_ASSOC))
• print "\tlttrgt\n"
• foreach (line as col_value)
• print "\t\tlttdgtcol_valuelt/tdgt\n"
• print "\tlt/trgt\n"
• print "lt/tablegt\n"
• / Free resultset /
• mysql_free_result(result)
• / Closing connection /

25
myphpadd.html

• lthrgt
• ltform action"myphpadd.php" method"post"gt
• Name ltinput type"text" name"name"gtltbrgt
• Owner ltinput type"text" name"owner"gtltbrgt
• Species ltinput type"text" name"species"gtltbrgt
• Sex ltinput type"text" name"sex"gtltbrgt
• Birth ltinput type"text" name"birth"gtltbrgt
• Death ltinput type"text" name"death"gtltbrgt
• ltinput type"submit"gtltbrgt
• lthrgt
• lt/formgt

26
myphpadd.php

• lt?php
• / Connection details - customize this /
• host "localhost"
• user "testuser"
• password "testpassword"
• database "testdb"
• table "pet"
• / Connecting, selecting database /
• link mysql_connect(host, user,
  password) or die("Could not connect")
• mysql_select_db("testdb") or die("Could not
  select database")
• print "Connected successfully to
  ltbgtdatabaselt/bgt at ltbgthostlt/bgtlthrgt."

27
myphpadd.php (contd)

• / Performing SQL INSERT /
• query "INSERT INTO table VALUES ('name',
  'owner', 'species','sex', 'birth',
  'death')"
• print "query"
• result mysql_query(query) or die("Insert
  failed")
• / Performing SQL query /
• query "SELECT FROM table"
• result mysql_query(query) or die("Query
  failed")
• / Printing results in HTML /
• print "lttable border1gt\n"
• while (line mysql_fetch_array(result,
  MYSQL_ASSOC))
• print "\tlttrgt\n"
• foreach (line as col_value)
• print "\t\tlttdgtcol_valuelt/tdgt\n"
• print "\tlt/trgt\n"

28
myphpupd.html

• lthrgt
• ltform action"myphpupd.php" method"post"gt
• Enter Old Pet Name ltinput type"text"
  name"oname"gtltbrgt
• Enter New Pet Name ltinput type"text"
  name"nname"gtltbrgt
• ltinput type"submit"gtltbrgt
• lthrgt
• lt/formgt

29
myphpupd.php

• lt?php
• / Connection details - customize this /
• host "localhost"
• user "testuser"
• password "testpassword"
• database "testdb"
• table "pet"
• / Connecting, selecting database /
• link mysql_connect(host, user,
  password) or die("Could not connect")
• mysql_select_db("testdb") or die("Could not
  select database")
• print "Connected successfully to
  ltbgtdatabaselt/bgt at ltbgthostlt/bgtlthrgt."

30
myphpupd.php (contd)

• / Performing SQL UPDATE /
• query "UPDATE table SET name'nname'
  WHERE name'oname'"
• result mysql_query(query) or die("Update
  failed")
• / Performing SQL query /
• query "SELECT FROM table"
• result mysql_query(query) or die("Query
  failed")
• / Printing results in HTML /
• print "lttable border1gt\n"
• while (line mysql_fetch_array(result,
  MYSQL_ASSOC))
• print "\tlttrgt\n"
• foreach (line as col_value)
• print "\t\tlttdgtcol_valuelt/tdgt\n"
• print "\tlt/trgt\n"
• print "lt/tablegt\n"
• / Free resultset /

31
myphpdel.html

• lt?php
• lthrgt
• ltform action"myphpdel.php" method"post"gt
• Enter Name of Pet to Delete ltinput type"text"
  name"name"gtltbrgt
• ltinput type"submit"gtltbrgt
• lthrgt
• lt/formgt

32
myphpdel.php

• lt?php
• / Connection details - customize this /
• host "localhost"
• user "testuser"
• password "testpassword"
• database "testdb"
• table "pet"
• / Connecting, selecting database /
• link mysql_connect(host, user,
  password) or die("Could not connect")
• mysql_select_db("testdb") or die("Could not
  select database")
• print "Connected successfully to
  ltbgtdatabaselt/bgt at ltbgthostlt/bgtlthrgt."

33
myphpdel.php (contd)

• / Performing SQL query /
• query "SELECT FROM table"
• result mysql_query(query) or die("Query
  failed")
• / Printing results in HTML /
• print "lttable border1gt\n"
• while (line mysql_fetch_array(result,
  MYSQL_ASSOC))
• print "\tlttrgt\n"
• foreach (line as col_value)
• print "\t\tlttdgtcol_valuelt/tdgt\n"
• print "\tlt/trgt\n"
• print "lt/tablegt\n"
• / Free resultset /
• mysql_free_result(result)

34
Open Database Connectivity (ODBC)
35
ODBC Interface

• ODBC (Open Database Connectivity) is an abstract
  API that allows you to write applications that
  can interoperate with various RDBMS servers.
• ODBC provides a product-neutral interface between
  frontend applications and database servers,
  allowing a user or developer to write
  applications that are transportable between
  servers from different manufacturers..

36
ODBC Interface

• The ODBC API matches up on the backend to an
  ODBC-compatible data source. This could be
  anything from a text file to an Oracle or
  Postgres RDBMS.
• The backend access come from ODBC drivers, or
  vendor specifc drivers that allow data access.
  psqlODBC is such a driver, along with others that
  are available, such as the OpenLink ODBC drivers.

37
ODBC Interface

• Once you write an ODBC application, you should be
  able to connect to any back end database,
  regardless of the vendor, as long as the database
  schema is the same.
• For example. you could have MS SQL Server and
  Postgres servers that have exactly the same data.
  Using ODBC, your Windows application would make
  exactly the same calls and the back end data
  source would look the same (to the Windows app).

38
DATABASE, SQL AND ODBC

• SETTING-UP ODBC
• ODBC
• Open Data Base Connectivity - used as standard
  for connecting to
• database sources.
• ACCESS
• MS SQL
• ORACLE
• INFORMIX
• JAVA and others

39
DATABASE, SQL AND ODBC
SETTING-UP ODBC Click start then control panel.
Double click the ODBC SOURCES
40
DATABASE, SQL AND ODBC
SETTING-UP ODBC Select System DSN tab then
click the ADD button.
41
DATABASE, SQL AND ODBC
SETTING-UP ODBC Select appropriate Database
driver in the list then click the FINISH button.
42
DATABASE, SQL AND ODBC

• SETTING-UP ODBC
• Type in the Data Source Name that you want to
  name your connection to the database. Filling in
  the optional Description will help in the future
  when you have more data sources registered.
• Click the SELECT button under the database
  portion.

43
DATABASE, SQL AND ODBC

• SETTING-UP ODBC
• Select the database name that you want for
  connection. Browse if necessary to locate
  databases. (Data Bases can also be in another
  computer or another server so be prepared to
  browse the network too) When the database is
  located click OK.

44
DATABASE, SQL AND ODBC
SETTING-UP ODBC After selecting the database
source name or connection the DATA SOURCE is now
added in the list of the System DSN tab. The
data base can now be viewed/edited/populated
using any database tool.
45
myphpodbc.php

• lt?php
• / Connection details - customize this /
• dsn "company" / ODBC data source name /
• user "testuser" / ODBC user name /
• password "testpassword" / ODBC password
  /
• table "employee"
• / Connecting, selecting database /
• link odbc_connect(dsn, user, password)
  or die("Could not connect")
• print "Connected successfully to ltbgtdsnlt/bgt
  data source name.lt/bgtlthrgt"
• / Performing SQL query /
• query "SELECT FROM table"
• result odbc_exec(link,query) or
  die("Query failed")
• numfields odbc_num_fields(result)
• / Printing results in HTML /
• print "ltbgtTable tablelt/bgt"

46
myphpodbc.php (contd)

• print "\tlttrgt\n"
• for (i1 i lt numfields i)
• field odbc_field_name(result,i)
• print "\t\tlttdgtltbgtfieldlt/bgtlt/tdgt\n"
• print "\tlt/trgt\n"
• while (line odbc_fetch_row(result))
• print "\tlttrgt\n"
• for (i1 i lt numfields i)
• value odbc_result(result,i)
• print "\t\tlttdgtvaluelt/tdgt\n"
• print "\tlt/trgt\n"
• print "lt/tablegt\n"
• / Free resultset /

47
Java Data Base Connectivity (JDBC)
48
JDBC Interface

• JDBC is a core API of Java 1.1 and later. It
  provides a standard set of interfaces to
  SQL-compliant databases.
• Postgres provides a type 4 JDBC Driver. Type 4
  indicates that the driver is written in Pure
  Java, and communicates in the database system's
  own network protocol. Because of this, the driver
  is platform independent once compiled, the
  driver can be used on any system.
• Example How to use the JDBC to connect to a
  DBMS, PostgreSQL (http//dhansen.cs.georgefox.edu/
  dhansen/Postgres/JDBCExample.java)

49
jdbcMySQLDemo.java

• import java.sql.
• public class jdbcMySQLDemo
• public static void main(String args)
• Connection con null
• Statement st null
• ResultSet rs null
• try
• Class.forName("com.mysql.jdbc.Driver").newIn
  stance()
• con DriverManager.getConnection("jdbcmysq
  l//appserver.cs.xu.edu.ph/testdb",
• "testuser", "testpassword")
• st con.createStatement()
• rs st.executeQuery("SELECT ssn, fname,
  lname,"
• "dno FROM employee")

50
jdbcMySQLDemo.java (contd)

• while(rs.next())
• int ssn rs.getInt(1)
• String fname rs.getString(2)
• String lname rs.getString(3)
• String dno rs.getString(4)
• System.out.println(ssn ". " fname
  ", "
• lname " (" dno ")")
• catch (Exception e)
• System.err.println("Exception "
  e.getMessage())
• finally
• try
• if(rs ! null)
• rs.close()
• if(st ! null)
• st.close()
• if(con ! null)

51
jdbcOdbcDemo.java

• import java.sql.
• public class jdbcOdbcDemo
• public static void main(String args)
• Connection con null
• Statement st null
• ResultSet rs null
• try
• Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"
  )
• con DriverManager.getConnection("jdbcodbc
  testdb",
• "testuser", "testpassword")
• st con.createStatement()
• rs st.executeQuery("SELECT ssn, fname,
  lname,"
• "dno FROM employee")

52
jdbcOdbcDemo.java (contd)

• while(rs.next())
• int ssn rs.getInt(1)
• String fname rs.getString(2)
• String lname rs.getString(3)
• String dno rs.getString(4)
• System.out.println(ssn ". " fname
  ", "
• lname " (" dno ")")
• catch (Exception e)
• System.err.println("Exception "
  e.getMessage())
• finally
• try
• if(rs ! null)
• rs.close()
• if(st ! null)
• st.close()
• if(con ! null)

53
Accessing Database using ASP
ASP INTRODUCTION

• What is ASP?
• ASP stands for Active Server Pages
• ASP is a program that runs inside IIS
• IIS stands for Internet Information Services
• IIS comes as a free component with Windows 2000
• IIS is also a part of the Windows NT 4.0 Option
  Pack
• The Option Pack can be downloaded from Microsoft
• PWS is a smaller - but fully functional - version
  of IIS
• PWS can be found on your Windows 95/98 CD
• What is an ASP File?
• An ASP file is just the same as an HTML file
• An ASP file can contain text, HTML, XML, and
  scripts
• Scripts in an ASP file are executed on the server
  
• An ASP file has the file extension ".asp"
• How Does it Work?
• When a browser requests HTML file, the server
  returns the file
• When a browser requests ASP file, IIS passes the
  request to the ASP engine
• ASP engine reads the ASP file, line by line,
  executes the scripts in the file
• Finally, the ASP file is returned to the browser
  as plain HTML

54
ASP AND DATABASE INTEGRATION

• MS ACCESS DATABASE EXAMPLE
• Heres an example database that taken from a
  PERSONNEL
• MONITORING SYSTEM (PMS) of STII-DOST.

55
ASP AND DATABASE INTEGRATION

• MS ACCESS DATABASE EXAMPLE
• Snapshot of the attributes of Personal Info and
  Post_school tables
• In the PMS database.

56
ASP AND DATABASE INTEGRATION
CREATING DATABASE CONNECTION Code11-data_out.asp
THIS PROGRAM WILL LIST SELECTED INFORMATION FROM
THE POST_SCHOOL TABLE OF THE PMS DATABASE NAMED
AS PMS_DATA IN THE ODBC DATA SOURCE. IT WILL
DISPLAY IT USING A WEB BROWSER. lt Option
Explicit ' Variable definition, by the way this
is a comment Dim cnnDB, strQuery, rsInfo,
greeting 'Creation of an instance of the
Connection object. Set cnnDB Server.CreateObject
("ADODB.Connection") 'Opening the data source
named in the ODBC. cnnDB.Open "pms_data" 'Building
the SQL query string and getting information
from post_school table. strQuery SELECT FROM
post_school" 'Execute the query and return a
recordset equating it in the rsInfo variable. Set
rsInfo cnnDB.Execute(strQuery) gt
57
ASP AND DATABASE INTEGRATION
Code11-data_out.asp continued lt This
HTML snippet is for the printing of headings to
be used by the data taken from the PMS
databases Post_school table. Again this is a
comment gt ltHTMLgtltBODYgtltCENTERgtltH2gt From
POST_School TABLE OF THE PMS DATABASElt/H2gtltBRgt ltTA
BLEgt ltTRgtltTH align"left"gtEMP_NOlt/THgtlttd
width"15"gtltbrgtlt/tdgt ltTH
align"left"gtUNIVERSITYlt/THgtlttd
width"15"gtltbrgtlt/tdgt ltTH
align"left"gtDEGREE/UNITS lt/THgtlttd
width"15"gtltbrgtlt/tdgt lt/TRgt
58
ASP AND DATABASE INTEGRATION
Code11-data_out.asp continued lt 'Iterate
through the recordset, pull out the required
data, and insert it 'into an HTML table. Do While
Not rsInfo.EOF gt ltTRgt ltTDgtlt
rsInfo("Emp_No") gtlt/TDgtlttd width"15"gtltbrgtlt/tdgt
ltTDgtlt rsInfo("Name of School")
gtlt/TDgtlttd width"15"gtltbrgtlt/tdgt ltTDgtlt
rsInfo("Degree/Units Earned") gtlt/TDgtlttd
width"15"gtltbrgtlt/tdgt lt/TRgt lt 'Move to
the next record in the record set rsInfo.MoveNext
Loop 'Close the recordset. rsInfo.Close 'And
close the database connection. cnnDB.Close
gt lt/TABLEgtlthrgtlt/CENTERgt lt/BODYgt lt/HTMLgt
59
ASP AND DATABASE INTEGRATION
DATA_OUT.ASP output
60
ASP AND DATABASE INTEGRATION
QUERYING A DATABASE Code12-QUERY_out.htm THIS
HTML WILL ACCEPT QUERIED DATA FROM PERSONAL INFO
TABLES ATTRIBUTES THEN CALL THE QUERY_OUT.ASP
THAT WILL PROCESS THE REQUEST. ltHTMLgtltBODYgt ltFOR
M Action"query_out.asp" Method"post"gt ltPgtYour
Query Please, a letter or a word is acceptable
ltINPUT Name"Qsearch" Size48gt ltPgtWhich Field
? ltSELECT Name "Qfield"gt ltOPTIONgtSurname
ltOPTIONgtFirst_name ltOPTIONgtEMP_NO ltOPTIONgtCivi
l_Status lt/SELECTgt ltPgtltINPUT Type"SUBMIT"
Value"Submit Query"gt lt/FORMgt lt/BODYgtlt/HTMLgt
61
ASP AND DATABASE INTEGRATION
Code12-QUERY_out.htm
62
ASP AND DATABASE INTEGRATION
QUERYING A DATABASE Code13-QUERY_out.asp THIS
PROGRAM WILL DISPLAY QUERIED INFORMATION FROM THE
PERSONAL INFO TABLE OF THE PMS DATABASE DATA
ENTERED FROM THE HTM FILE QUERY_OUT.HTM. lt_at_
LANGUAGE"VBSCRIPT" gt lt Option Explicit 'Define
our variables. Dim cnnDB, strQuery, rsInfo,
QAsearch, QAfield 'Create an instance of the
Connection object. Set cnnDB Server.CreateObject
("ADODB.Connection") 'And open it. cnnDB.Open
"pms_data" 'Display first the posted data gtlthrgt
ltcentergtltbgtYou Searched for lt
Request.Form("Qsearch") gt In the field lt
Request.Form("Qfield")gt lt/bgtlt/centergtlthrgt lt 'Equ
ate the inputted value to variables QAsearch
(Request.Form("Qsearch")) QAfield
(Request.Form("Qfield"))
63
ASP AND DATABASE INTEGRATION
Code13-QUERY_out.asp continued 'Build our SQL
query string strQuery "select from
Personal_Info where
("QAfield" LIKE '"QAsearch"')" 'Execute the
query and return a recordset. Set rsInfo
cnnDB.Execute(strQuery) gt ltCENTERgtltH2gt From
Personal_Info TABLE OF THE PMS DATABASElt/H2gtltBRgt lt
TABLEgt ltTRgt ltTH align"left"gtEmp_nolt/THgtlttd
width"15"gtltbrgtlt/tdgt ltTH align"left"gtLast
namelt/THgtlttd width"15"gtltbrgtlt/tdgt ltTH
align"left"gtFirst namelt/THgtlttd
width"15"gtltbrgtlt/tdgt ltTH align"left"gtMiddle
namelt/THgtlttd width"15"gtltbrgtlt/tdgt ltTH
align"left"gtCivil Statuslt/THgtlttd
width"15"gtltbrgtlt/tdgt ltTH align"left"gtDate of
Birthlt/THgtlttd width"15"gtltbrgtlt/tdgt ltTH
align"left"gtTax IDlt/THgtlttd width"15"gtltbrgtlt/tdgt
lt/TRgt
64
ASP AND DATABASE INTEGRATION
Code13-QUERY_out.asp continued lt 'Iterate
through the recordset, pull out the required
data, and insert it into an HTML table. Do While
Not rsInfo.EOF gt ltTRgtltTDgtlt rsInfo("Emp_No")
gtlt/TDgtlttd width"15"gtltbrgtlt/tdgt ltTDgtlt
rsInfo("Surname") gtlt/TDgtlttd width"15"gtltbrgtlt/tdgt
ltTDgtlt rsInfo("First_Name") gtlt/TDgtlttd
width"15"gtltbrgtlt/tdgt ltTDgtlt
rsInfo("Middle_Name") gtlt/TDgtlttd
width"15"gtltbrgtlt/tdgt ltTDgtlt
rsInfo("Civil_Status") gtlt/TDgtlttd
width"15"gtltbrgtlt/tdgt ltTDgtlt
rsInfo("Date of Birth") gtlt/TDgtlttd
width"15"gtltbrgtlt/tdgt ltTDgtlt
rsInfo("TIN") gtlt/TDgtlttd width"15"gtltbrgtlt/tdgt lt/T
Rgt lt 'Move to the next record in the
recordset rsInfo.MoveNext Loop 'Close the
recordset. rsInfo.Close 'And close the database
connection. cnnDB.Close gt lt/TABLEgtlthrgtlt/fontgtlt/C
ENTERgtlt/scriptgt
65
ASP AND DATABASE INTEGRATION
Code13-QUERY_out.asp
66
ASP AND DATABASE INTEGRATION
POPULATING A DATABASE Code14-DATA_ENTRY.HTM
THIS HTML WILL ACCEPT INPUTTED DATA ONLINE THEN
CALL THE DATA_ENTRY.ASP TO INSERT THE DATA
ENTERED IN THE POST_SCHOOL TABLE OF PMS DATABASE.
lthtmlgtltheadgtlttitlegtPersonal Info Data
Entrylt/titlegtlt/headgt ltFORM Action
"data_entry.asp" Method"POST" gt ltCENTERgt
ltTABLEgtltTRgt ltTDgtltbgtEMP NOlt/Bgtlt/TDgt
ltTDgtltINPUT Type"text" Name"T_Emp_No" Value" "
Size"20" gtlt/TDgt ltTDgtltbgtName of
Schoollt/Bgtlt/TDgt ltTDgtltINPUT Type"text"
Name"t_Name_of_School" Value" " Size"50"
gtlt/TDgt lt/TRgtltTRgt
ltTDgtltBgtDegree/Units Earnedlt/bgtlt/TDgt
ltTDgtltINPUT Type"text" Name"T_Degree_Units_Earned
" Value" " Size"20"gtlt/TDgt
ltTDgtltbgtInclusive Dates of Attendancelt/Bgtlt/TDgt
ltTDgtltINPUT Type"text" Name"t_Inclusive_Dates
_of_Attendance" Value" " Size"50"gtlt/TDgt
lt/TRgtltTRgt ltTDgtltbgtHonors
Receivedltbgtlt/TDgt ltTDgtltINPUT Type"text"
Name"T_Honors_Received" Value" "
Size"20"gtlt/TDgt lt/TRgtlt/TABLEgt ltINPUT
Type"Submit" Value"Submit"gt ltINPUT
Type"Reset" Value"Reset"gt lt/CENTERgtlt/FORMgtlt/body
gtlt/htmlgt
67
ASP AND DATABASE INTEGRATION
Code14-DATA_ENTRY.HTM
68
ASP AND DATABASE INTEGRATION
POPULATING A DATABASE Code15-DATA_ENTRY.ASP
THIS PROGRAM WILL ACCEPT DATA PASSED BY
DATA_ENTRY.HTM THEN INSERT VALUES TO
CORRESPONDING ATTRIBUTE IN THE POST_SCHOOL TABLE
OF THE PMS DATABASE. THIS PROGRAM ALSO CHECKS
BLANK ENTRIES AND DISPLAYS CORRESPONDING
MESSAGES. lt_at_ LANGUAGE"VBSCRIPT" gt lt Option
Explicit 'DeCLARATION OF our variables Dim
Q_Emp_No, Q_Name_of_School, Q_Degree_Units_Earned
Dim Q_Inclusive_Dates_of_Attendance,
Q_Honors_Received Dim cnnDB, strQuery,
RsInfo 'Create an instance of the Connection
object. Set cnnDB Server.CreateObject("ADODB.Con
nection") 'And open it. cnnDB.Open
"pms_data" 'Retrieving values keyed in by the
user in the user interface into
variables Q_Emp_No (REQUEST.FORM("t_Emp_No")) Q
_Name_of_School (REQUEST.FORM("t_Name_of_School")
) Q_Degree_Units_Earned (REQUEST.FORM("T_Degree_
Units_Earned")) Q_Inclusive_Dates_of_Attendance
(REQUEST.FORM("t_Inclusive_Dates_of_Attendance"))
Q_Honors_Received (REQUEST.FORM("T_Honors_Receiv
ed"))
69
ASP AND DATABASE INTEGRATION
Code15-DATA_ENTRY.ASP continued THIS SNIPPET
CHECKS IF THERE ARE ENTERED VALUES IN EACH
FIELD. IT DISPLAYS THE INCOMPLETE MESSAGE IF
ONE OR MORE OF THE IMPORTANT FIELDS ARE LEFT
BLANK. ltif Q_Emp_No " " Or Q_Name_of_School
" " or Q_Degree_Units_Earned " " or
Q_Inclusive_Dates_of_Attendance " " Then
gt lthtmlgtltheadgtlttitlegtErrors in the
Formlt/titlegt ltbodygt ltbgtltfont face"Arial,
Helvetica, sans-serif" size"5" color"red"gtltbrgt
You submitted a blank or incomplete
form! lt/fontgtlt/bgt
lt/bodygt lt/htmlgt lt
70
ASP AND DATABASE INTEGRATION
Code14-DATA_ENTRY.ASP WITH A BLANK ENTRY
71
ASP AND DATABASE INTEGRATION
Code15-DATA_ENTRY.ASP continued IF ALL THE
IMPORTANT FIELDS HAVE VALUES, THIS SNIPPET WILL
INSERT ALL THE INFORMATION IN THE POST_SCHOOL
TABLE THEN WILL DISPLAY THE SUCCESS MESSAGE. lt
Else strQuery "insert into POST_SCHOOL
(EMP_NO,Name_of_School,Degree_Units_E
arned, Inclusive_Dates_of_Attendance
,Honors_Received) values
('"Q_Emp_No"','"Q_Name_of_School"','"Q_Degree
_Units_Earned"', '"QInclusive_Dates
_of_Attendance"','" Q_Honors_Received"')
'Execute the query and return a recordset.
cnnDB.Execute(strQuery) gt
lthtmlgt ltheadgtlttitlegtData Entry Form of POST
GRADlt/titlegtlt/headgtltbodygtltbrgt ltbgtltfont
face"Arial, Helvetica, sans-serif" size"5"
color"green"gt The information you entered is
now searcheable in our database.lt/fontgtlt/bgtlt/bodygt
lt/htmlgt lt 'Closing the
connection cnnDB.Close set cnnDBnothing End
If gt
72
ASP AND DATABASE INTEGRATION
Code14-DATA_ENTRY.ASP COMPLETE FORM
73
ASP AND DATABASE INTEGRATION

• REFERENCES
• PRACTICAL ASP ISBN 81-7656-310-2 www.bponline.co
  m
• ACTIVE SERVER 101 www.asp101.com
• ASP TUTORIAL www.w3schools.com