Advanced Structured Query Language Featuring PostgreSQL

1
Advanced Structured Query Language (Featuring
PostgreSQL)

• CS 95 Advanced Database Systems
• Handout 3

2
Review of SQL

• The most often used command in SQL is the SELECT
  statement, used to retrieve data. The syntax is
  (PostgreSQL)
• SELECT ALL DISTINCT ON ( expression , ...
  )
• expression AS output_name , ...
• INTO TEMPORARY TEMP TABLE
  new_table
• FROM from_item , ...
• WHERE condition
• GROUP BY expression , ...
• HAVING condition , ...
• UNION INTERSECT EXCEPT ALL
  select
• ORDER BY expression ASC DESC USING
  operator , ...
• FOR UPDATE OF class_name , ...
• LIMIT count ALL OFFSET ,
  start

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
Advanced Queries in SQL
1. Nested queries and set comparisons. (Query 4
can be restated as Q4A) SELECT
DISTINCT PNUMBER FROM PROJECT
WHERE PNUMBER
IN (SELECT PNUMBER FROM
PROJECT, DEPARTMENT, EMPLOYEE WHERE
DNUMDNUMBER AND MGRSSNSSN
AND LNAMESmith') OR
PNUMBER IN (SELECT PNO
FROM WORKS_ON, EMPLOYEE
WHERE ESSNSSN AND LNAMESmith'
7
Advanced Queries in SQL
2. The EXISTS and UNIQUE Functions (Another form
for Query 16)
SELECT
E.FNAME, E.LNAME
FROM EMPLOYEE E
WHERE EXISTS
(SELECT
FROM DEPENDENT

WHERE ESSNE.SSN AND
SEXE.SEX AND E.FNAMEDEPENDENT_NAME)
SELECT FNAME, LNAME
FROM EMPLOYEE E WHERE
NOT EXISTS (SELECT FROM
DEPENDENT WHERE ESSNSSN )
8
Advanced Queries in SQL
2. The EXISTS and UNIQUE Functions UNIQUE
function. return TRUE if there is no duplicate
tuples in the result of a (nested)
query. Retrieve the names of departments in
which no two employees have the same salary.
SELECT DNAME FROM
DEPARTMENT WHERE
UNIQUE (SELECT SALARY FROM
EMPLOYEE WHERE
DNODNUMBER )
9
Advanced Queries in SQL
3. Explicit Sets and NULLS (Query 17 retrieve
the social security numbers of all employees who
work on project number 1, 2, or 3. SELECT
DISTINCT SSN FROM WORKS_ON

WHERE PNO IN (1,2,3) Query 18
retrieve the names of all employees who don't
have supervisors.
SELECT FNAME, LNAME FROM EMPLOYEE
WHERE SUPERSSN IS NULL
10
Advanced Queries in SQL (Cont.)
4. Renaming attributes and Joined Tables.

Q8A SELECT E.LNAME
AS EMPLOYEE_NAME, S.LNAME AS
SUPERVISOR_NAME FROM
EMPLOYEE E, EMPLOYEE S
WHERE E.SUPERSSNS.SSN Q1A SELECT
LNAME, LNAME,ADDRESS FROM
(EMPLOYEE JOIN DEPARTMENT ON
DNODNUMBER) WHERE
DNAME Research Q1B SELECT LNAME,
LNAME,ADDRESS FROM (EMPLOYEE NATURAL
JOIN (DEPARTMENT AS
DEPT(DNAME,DNO, MSSN, MSDATE)))
WHERE DNAME Research
11
Advanced Queries in SQL (Cont.)
5. Aggregate functions. COUNT, SUM, MAX,
MIN, AVG Find the total salary, maximum
salary, minimum salary,and average salary of
all employees.
Q19 SELECT SUM( SALARY), MAX( SALARY),
MIN(SALARY), AVG( SALARY),
FROM EMPLOYEE Q20
SELECT SUM( SALARY), MAX( SALARY),
MIN(SALARY), AVG( SALARY),
FROM EMPLOYEE,DEPARTMENT
WHERE DNODNUMBER AND
DNAMEResearch
12
Advanced Queries in SQL (Cont.)

• 6. Grouping tuples apply aggregate functions to
  subgroups of tuples.
• Query 24 for each department, retrieve the
  department number of the number of employees,
  their average salary. SELECT DNO, COUNT(),
  AVG ( SALARY) FROM EMPLOYEE
  GROUP BY DNO
• The attributes in GROUP BY clause are called
  grouping attributes.They must appear in the
  SELECT clause.

13
Advanced Queries in SQL (Cont.)

• 7. HAVING clause conditions for selecting
  groups
• Query 26 for each project on which more than 2
  employees work,retrieve the project number, the
  project name, and the number of employees who
  work on the project.
• SELECT PNUMBER, PNAME, COUNT()
  FROM PROJECT, WORKS_ON
  WHERE PNUMBERPNO
  GROUP BY PNUMBER, PNAME
  HAVING COUNT() gt 2

14
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

15
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)
16
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
17
Triggers

• Postgres has various server-side function
  interfaces. Server-side functions can be written
  in SQL, PLPGSQL, TCL, or C.
• Trigger functions can be written in any of these
  languages except SQL.
• Note that STATEMENT-level trigger events are not
  supported in the current version. You can
  currently specify BEFORE or AFTER on INSERT,
  DELETE or UPDATE of a tuple as a trigger event.

18
Triggers

• If a trigger event occurs, the trigger manager
  (called by the Executor) sets up a TriggerData
  information structure (described below) and calls
  the trigger function to handle the event.
• The trigger function must be created before the
  trigger is created as a function taking no
  arguments and returning opaque. If the function
  is written in C, it must use the "version 1"
  function manager interface.

19
Triggers

• The syntax for creating triggers is as follows
• CREATE TRIGGER trigger BEFORE AFTER
  INSERT DELETE UPDATE OR ...
• ON relation FOR EACH ROW STATEMENT
• EXECUTE PROCEDURE procedure
• (args)

20
Advanced Postgres SQL Features

• Inheritance
• The capitals table contains state capitals that
  are also cities. Naturally, the capitals table
  should inherit from cities.
• CREATE TABLE cities (
• name text,
• population real,
• altitude int -- (in ft)
• )
• CREATE TABLE capitals (
• state char(2)
• ) INHERITS (cities)
• In this case, a row of capitals inherits all
  columns (name, population, and altitude) from its
  parent, cities.

21
Advanced Postgres SQL Features

• Non-Atomic Values
• One of the tenets of the relational model is that
  the columns of a table are atomic. Postgres does
  not have this restriction columns can themselves
  contain sub-values that can be accessed from the
  query language. For example, you can create
  columns that are arrays of base types.

22
Advanced Postgres SQL Features

• Arrays
• Postgres allows columns of a row to be defined as
  fixed-length or variable-length multi-dimensional
  arrays. Arrays of any base type or user-defined
  type can be created. To illustrate their use, we
  first create a table with arrays of base types.
• CREATE TABLE SAL_EMP (
• name text,
• pay_by_quarter integer,
• schedule text
• )
• The above query will create a table named SAL_EMP
  with a text string (name), a one-dimensional
  array of integer (pay_by_quarter), which
  represents the employee's salary by quarter and a
  two-dimensional array of text (schedule), which
  represents the employee's weekly schedule.

23
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 PostgreSQL)
• SELECT FROM pg_database
• SELECT FROM pg_class

24
Postgres System Catalogs

• Having introduced the basic extensibility
  concepts, we can now take a look at how the
  catalogs are actually laid out. You can skip this
  section for now, but some later sections will be
  incomprehensible without the information given
  here, so mark this page for later reference. All
  system catalogs have names that begin with pg_.
• The following tables contain information that may
  be useful to the end user. (There are many other
  system catalogs, but there should rarely be a
  reason to query them directly.)

25
Postgres System Catalogs

• Catalog Name Description
• pg_database databases
• pg_class tables
• pg_attribute table columns
• pg_index secondary indices
• pg_proc procedures (both C and SQL)
• pg_type types (both base and complex)
• pg_operator operators
• pg_aggregate aggregates and aggregate
  functions
• pg_am access methods
• pg_amop access method operators
• pg_amproc access method support functions
• pg_opclass access method operator classes

26
Accessing a Database

• In PostgreSQL
• Running the Postgres terminal monitor programs
  (e.g. psql) which allows you to interactively
  enter, edit, and execute SQL commands.
• Using an existing native frontend tool like
  pgaccess, pgadmin or ApplixWare (via ODBC) to
  create and manipulate a database.

27
Accessing a Database (contd)

• Using a language like perl or tcl which has a
  supported interface for Postgres. Some of these
  languages also have convenient and powerful GUI
  toolkits which can help you construct custom
  applications. pgaccess, mentioned above, is one
  such application written in tk/tcl and can be
  used as an example.
• Writing a C program using the LIBPQ subroutine
  library. This allows you to submit SQL commands
  from C and get answers and status messages back
  to your program. This interface is discussed
  further in The PostgreSQL Programmer's Guide.

28
Accessing a Database (contd)
29
Embedded SQL

• SQL can be embedded into a host language (e.g.
  C).
• Two main reasons for using SQL from a host
  language
• There are queries that cannot be formulated using
  pure SQL (i.e. recursive queries). To be able to
  perform such queries, a host language with a
  greater expressive power than SQL is needed.
• Need to access a database from some application
  that is written in the host language (e.g. a
  ticket reservation system with a graphical user
  interface is written in C and the information
  about which tickets are still left is stored in a
  database that can be accessed using embedded
  SQL).

30
Embedded SQL

• A program using embedded SQL in a host language
  consists of statements of the host language and
  of embedded SQL (ESQL) statements.
• Every ESQL statement begins with the keywords
  EXEC SQL.
• The ESQL statements are transformed to statements
  of the host language by a precompiler (which
  usually inserts calls to library routines that
  perform the various SQL commands).

31
Embedded SQL

• When we look at the examples throughout Select we
  realize that the result of the queries is very
  often a set of tuples.
• Most host languages are not designed to operate
  on sets so we need a mechanism to access every
  single tuple of the set of tuples returned by a
  SELECT statement.
• This mechanism can be provided by declaring a
  CURSOR.
• After that we can use the FETCH command to
  retrieve a tuple and set the cursor to the next
  tuple.

32
Why Embedded SQL?

• Embedded SQL has some small advantages over other
  ways to handle SQL queries. It takes care of all
  the tedious moving of information to and from
  variables in your C program. Many RDBMS packages
  support this embedded language.
• There is an ANSI-standard describing how the
  embedded language should work. ecpg was designed
  to meet this standard as much as possible. So it
  is possible to port programs with embedded SQL
  written for other RDBMS packages to Postgres and
  thus promoting the spirit of free software.

33
The Concept

• You write your program in C with some special SQL
  things. For declaring variables that can be used
  in SQL statements you need to put them in a
  special declare section. You use a special syntax
  for the SQL queries.
• Before compiling you run the file through the
  embedded SQL C preprocessor and it converts the
  SQL statements you used to function calls with
  the variables used as arguments. Both variables
  that are used as input to the SQL statements and
  variables that will contain the result are
  passed.

34
The Concept

• Then you compile and at link time you link with a
  special library that contains the functions used.
  These functions (actually it is mostly one single
  function) fetches the information from the
  arguments, performs the SQL query using the
  ordinary interface (libpq) and puts back the
  result in the arguments dedicated for output.
• Then you run your program and when the control
  arrives to the SQL statement the SQL statement is
  performed against the database and you can
  continue with the result.

35
Embedded SQL Examples

• Some examples of ways in which programs can talk
  with PostgreSQL
• embed.pgc -- Embedded SQL within a C program,
  using ecpg (http//basil.cs.uwp.edu/Cs380/embed.pg
  c)
• dynam.pgc -- Dynamic SQL within a C program,
  using ecpg (http//basil.cs.uwp.edu/Cs380/dynam.pg
  c)
• pq.c -- Use of the libpq API within a C program.
  (http//basil.cs.uwp.edu/Cs380/pq.c)
• pq.cpp -- Use of the libpq API within a C
  program. (http//basil.cs.uwp.edu/Cs380/pq.cpp)
• JDBC.java -- Use of the JDBC API within a Java
  program (http//basil.cs.uwp.edu/Cs380/JDBC.java)
• Source http//basil.cs.uwp.edu/Cs380/

36
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..

37
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.

38
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).

39
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

40
DATABASE, SQL AND ODBC
SETTING-UP ODBC Click start then control panel.
Double click the ODBC SOURCES
41
DATABASE, SQL AND ODBC
SETTING-UP ODBC Select System DSN tab then
click the ADD button.
42
DATABASE, SQL AND ODBC
SETTING-UP ODBC Select appropriate Database
driver in the list then click the FINISH button.
43
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.

44
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.

45
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.
46
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)

47
PostgreSQL Documentation

• www.postgresql.org is the PostgreSQL home page.
  The root of the PostgreSQL documentation is at
  http//www.postgresql.org/idocs/
• PostgreSQL Tutorial (. index.php?tutorial.html)
• PostgreSQL User's Guide ( index.php?user.html)
• PostgreSQL Programmer's Guide (
  index.php?programmer.html)
• PostgreSQL Administrator's Guide (
  index.php?admin.html)
• PostgreSQL Reference Manual ( index.php?reference
  .html)
• PostgreSQL Developer's Guide (
  index.php?developer.html)
• If you prefer to get these documents in PDF or PS
  formats, visit the address (http//www.ca.postgres
  ql.org/users-lounge/docs/)

48
Accessing PostgreSQL from PHP

• PHP PHP Hypertext Processor (http//www.php.net)
• PHP is a server-side, cross-platform scripting
  language for writing web-based applications. It
  allows you to embed program logic in HTML pages,
  which enables you to serve dynamic web pages. PHP
  allows us to create web-based user interfaces
  that interact with PostgreSQL. (Refer to
  http//www.php.net)
• Using the PHP API for PostgreSQL
• All of the interaction with the PostgreSQL
  database is performed through the PostgreSQL
  extension, which is a comprehensive set of PHP
  functions. (For a complete list of functions and
  further information about the same, refer to
  http//www.php.net/manual/ref.pgsql.php.)

49
PHP API for PostgreSQL(from http//www.php.net/ma
nual/ref.pgsql.php)

• pg_close -- Close a PostgreSQL connection
• pg_cmdtuples -- Returns number of affected tuples
  
• pg_connect -- Open a PostgreSQL connection
• pg_dbname -- Get the database name
• pg_end_copy -- Sync with PostgreSQL backend
• pg_errormessage -- Get the error message string
• pg_exec -- Execute a query
• pg_fetch_array -- Fetch a row as an array
• pg_fetch_object -- Fetch a row as an object
• pg_fetch_row -- Get a row as an enumerated array
• pg_fieldisnull -- Test if a field is NULL
• pg_fieldname -- Returns the name of a field
• pg_fieldnum -- Returns the field number of the
  named field
• pg_fieldprtlen -- Returns the printed length

50
PHP API for PostgreSQL(from http//www.php.net/ma
nual/ref.pgsql.php)

• pg_fieldsize -- Returns the internal storage size
  of the named field
• pg_fieldtype -- Returns the type name for the
  corresponding field number
• pg_freeresult -- Free result memory
• pg_getlastoid -- Returns the last object
  identifier
• pg_host -- Returns the host name associated with
  the connection
• pg_loclose -- Close a large object
• pg_locreate -- Create a large object
• pg_loexport -- Export a large object to file
• pg_loimport -- Import a large object from file
• pg_loopen -- Open a large object
• pg_loread -- Read a large object
• pg_loreadall -- Read a entire large object and
  send straight to browser
• pg_lounlink -- Delete a large object
• pg_lowrite -- Write a large object

51
PHP API for PostgreSQL(from http//www.php.net/ma
nual/ref.pgsql.php)

• pg_numfields -- Returns the number of fields
• pg_numrows -- Returns the number of rows
• pg_options -- Get the options associated with the
  connection
• pg_pconnect -- Open a persistent PostgreSQL
  connection
• pg_port -- Return the port number associated with
  the connection
• pg_put_line -- Send a NULL-terminated string to
  PostgreSQL backend
• pg_result -- Returns values from a result
  identifier
• pg_set_client_encoding -- Set the client encoding
  
• pg_client_encoding -- Get the client encoding
• pg_trace -- Enable tracing a PostgreSQL
  connection
• pg_tty -- Return the tty name associated with the
  connection
• pg_untrace -- Disable tracing of a PostgreSQL
  connection

52
Accessing PostgreSQL from PHP

• A simple PHP script that opens a connection to a
  PostgreSQL database, selects some rows, prints
  the number of rows in the resultset, and closes
  the connection would look something like this
• lt?phpdb_handle pg_connect("dbnamebpsimple")
  query "SELECT FROM item"result
  pg_exec(db_handle, query)echo "Number of
  rows " . pg_numrows(result)pg_freeresult(resu
  lt)pg_close(db_handle)?gt

53
Accessing PostgreSQL from PHP

• References
• PHP API for PostgreSQL (http//www.php.net/manual/
  ref.psql.php)
• Accessing PostgreSQL from PHP (http//www.webmaste
  rbase.com/article/615)
• A Simple Example Tabulated report from PHP using
  data from PostgreSQL(http//www.archonet.com/pgdo
  cs/php-example.html)
• How to access Postgres using PHP
  (http//dhansen.cs.georgefox.edu/dhansen/Postgres
  /example.phps)

54
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

55
ASP AND DATABASE INTEGRATION

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

56
ASP AND DATABASE INTEGRATION

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

57
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
58
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
59
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
60
ASP AND DATABASE INTEGRATION
DATA_OUT.ASP output
61
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
62
ASP AND DATABASE INTEGRATION
Code12-QUERY_out.htm
63
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"))
64
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
65
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
66
ASP AND DATABASE INTEGRATION
Code13-QUERY_out.asp
67
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
68
ASP AND DATABASE INTEGRATION
Code14-DATA_ENTRY.HTM
69
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"))
70
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
71
ASP AND DATABASE INTEGRATION
Code14-DATA_ENTRY.ASP WITH A BLANK ENTRY
72
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
73
ASP AND DATABASE INTEGRATION
Code14-DATA_ENTRY.ASP COMPLETE FORM
74
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