ECMM6018 Tutorial6

basesprocketData Management

Oct 31, 2013 (4 years and 13 days ago)

102 views

ECMM6018

Enterprise Networking For Electronic
Commerce

Tutorial 6





CGI/Perl and databases


Databases



Defn: A database is a collection of data that is organized so that
its contents can easily be accessed, managed, and updated.
[1]




Different types of databases


i.

Relational Database: Stores data in the form of related tables


ii.Object Oriented Programming Database: This has the data
defined in objects classes and sub classes.



Some of the Databases in use: IBM's DB2, Microsoft's Access,
MySQL, Postgresql and products from Oracle, Sybase, and
Computer Associates.




[1]

http://searchdatabase.techtarget.com/sDefinition/0,,sid13_gci211895,00.html


Databases Continued


For DBMS a record is collection of fields each field
containing one entry


In databases records are also known as tuples


Recordset


collection of records returned which match the
query entered.



Figure 1: Showing table in database





Figure2: Showing recordset returned for a query




Structured Query Language
(SQL)



A programming language, that allows access to a
database.



SQL Data Manipulation Language (DML)



SQL Data Definition Language (DDL)


SQL Data Manipulation Language
(DML)


This allows us to either extract, insert, delete or
update data in the database


SELECT
-

extracts data from a database table


UPDATE
-

updates data in a database table


DELETE
-

deletes data from a database table


INSERT INTO
-

inserts new data into a database
table



SELECT SYNTAX


SELECT column_name(s) FROM table_name



WHERE clause: This allows us to conditionally
select data from a table based upon some criteria



SELECT column FROM table WHERE column
operator value



e.g.
Select * from nur


UPDATE SYNTAX


UPDATE table_name


SET column_name = new_value


WHERE column_name = some_value




e.g. update #period#


set period ='#period#',


Primary='#form.Primary#',Last1='#form.Last1#',


First1'#form.First1#',Last2='#form.Last2#',First2='#form
.First2#',editor_last1='#editor_last1#


where


key=#form.key#

DELETE and INSERT
SYNTAX


DELETE FROM table_name


WHERE column_name = some_value


e.g. delete from nur where l_name='zincir'




INSERT INTO table_name


VALUES (value1, value2,....)



e.g. insert into nur (l_name,f_name) values
('lastname1','firstname1')

SQL Data Definition Language
(DDL)



This allows us to create or delete tables in the
database.



CREATE TABLE
-

creates a new database table


ALTER TABLE
-

alters (changes) a database
table




DROP TABLE
-

deletes a database table


DDL SYNTAX


CREATE TABLE table_name


(


column_name1 data_type,


column_name2 data_type,


.......


)


ALTER TABLE table_name


ADD column_name datatype




ALTER TABLE table_name


DROP COLUMN column_name


Database connection strings



ODBC (Object Database Connectivity)


An
application interface for accessing a database. Makes
it possible to assess data from any application
irrelevant of the underlying DBMS used. It carries
out this task by inserting a middle layer known as a
driver between the application and the DBMS. The
role of this is to convert application's data queries into
commands that the DBMS understands.
[1]

It is also
language independent.




JDBC


[1]

http://www.webopedia.com/TERM/O/ODBC.html


Connection String in Perl


$dbh=DBI
-
>connect(
'dbi:Oracle'

,$username,$password);


DBI = Database Independent Module, allows for
access to a supported SQL database

Using a database through the DBI
module


Connect to the database


Prepare the SQL statement and return a statement
handle


Using the Statement handle, Execute the SQL
statement


If the SQL statement is a SELECT, use the
Statement handle to get the
SELECT
ed records


Close the statement handle


Close the connection


Useful Links


http://www.suite101.com/article.cfm/perl/67813


http://www.saturn5.com/~jwb/dbi
-
examples.html