ecmm6018 enterprise networking for electronic commerce tutorial 6 cgi/perl and databases
DESCRIPTION
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.TRANSCRIPT
![Page 1: ECMM6018 Enterprise Networking For Electronic Commerce Tutorial 6 CGI/Perl and databases](https://reader036.vdocuments.us/reader036/viewer/2022082620/5a4d1b387f8b9ab05999d8cf/html5/thumbnails/1.jpg)
ECMM6018 ECMM6018 Enterprise Networking For Electronic Enterprise Networking For Electronic
CommerceCommerceTutorial 6Tutorial 6
CGI/Perl and databases
![Page 2: ECMM6018 Enterprise Networking For Electronic Commerce Tutorial 6 CGI/Perl and databases](https://reader036.vdocuments.us/reader036/viewer/2022082620/5a4d1b387f8b9ab05999d8cf/html5/thumbnails/2.jpg)
DatabasesDatabases 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
![Page 3: ECMM6018 Enterprise Networking For Electronic Commerce Tutorial 6 CGI/Perl and databases](https://reader036.vdocuments.us/reader036/viewer/2022082620/5a4d1b387f8b9ab05999d8cf/html5/thumbnails/3.jpg)
Databases ContinuedDatabases 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.
![Page 4: ECMM6018 Enterprise Networking For Electronic Commerce Tutorial 6 CGI/Perl and databases](https://reader036.vdocuments.us/reader036/viewer/2022082620/5a4d1b387f8b9ab05999d8cf/html5/thumbnails/4.jpg)
Figure 1: Showing table in database
![Page 5: ECMM6018 Enterprise Networking For Electronic Commerce Tutorial 6 CGI/Perl and databases](https://reader036.vdocuments.us/reader036/viewer/2022082620/5a4d1b387f8b9ab05999d8cf/html5/thumbnails/5.jpg)
Figure2: Showing recordset returned for a query
![Page 6: ECMM6018 Enterprise Networking For Electronic Commerce Tutorial 6 CGI/Perl and databases](https://reader036.vdocuments.us/reader036/viewer/2022082620/5a4d1b387f8b9ab05999d8cf/html5/thumbnails/6.jpg)
Structured Query Language Structured Query Language (SQL)(SQL)
A programming language, that allows access to a database.
SQL Data Manipulation Language (DML) SQL Data Definition Language (DDL)
![Page 7: ECMM6018 Enterprise Networking For Electronic Commerce Tutorial 6 CGI/Perl and databases](https://reader036.vdocuments.us/reader036/viewer/2022082620/5a4d1b387f8b9ab05999d8cf/html5/thumbnails/7.jpg)
SQL Data Manipulation Language SQL Data Manipulation Language (DML)(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
![Page 8: ECMM6018 Enterprise Networking For Electronic Commerce Tutorial 6 CGI/Perl and databases](https://reader036.vdocuments.us/reader036/viewer/2022082620/5a4d1b387f8b9ab05999d8cf/html5/thumbnails/8.jpg)
SELECT SYNTAXSELECT 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
![Page 9: ECMM6018 Enterprise Networking For Electronic Commerce Tutorial 6 CGI/Perl and databases](https://reader036.vdocuments.us/reader036/viewer/2022082620/5a4d1b387f8b9ab05999d8cf/html5/thumbnails/9.jpg)
UPDATE SYNTAXUPDATE 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#
![Page 10: ECMM6018 Enterprise Networking For Electronic Commerce Tutorial 6 CGI/Perl and databases](https://reader036.vdocuments.us/reader036/viewer/2022082620/5a4d1b387f8b9ab05999d8cf/html5/thumbnails/10.jpg)
DELETE and INSERT DELETE and INSERT SYNTAXSYNTAX
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')
![Page 11: ECMM6018 Enterprise Networking For Electronic Commerce Tutorial 6 CGI/Perl and databases](https://reader036.vdocuments.us/reader036/viewer/2022082620/5a4d1b387f8b9ab05999d8cf/html5/thumbnails/11.jpg)
SQL Data Definition Language SQL Data Definition Language (DDL)(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
![Page 12: ECMM6018 Enterprise Networking For Electronic Commerce Tutorial 6 CGI/Perl and databases](https://reader036.vdocuments.us/reader036/viewer/2022082620/5a4d1b387f8b9ab05999d8cf/html5/thumbnails/12.jpg)
DDL SYNTAXDDL 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
![Page 13: ECMM6018 Enterprise Networking For Electronic Commerce Tutorial 6 CGI/Perl and databases](https://reader036.vdocuments.us/reader036/viewer/2022082620/5a4d1b387f8b9ab05999d8cf/html5/thumbnails/13.jpg)
Database connection stringsDatabase 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
![Page 14: ECMM6018 Enterprise Networking For Electronic Commerce Tutorial 6 CGI/Perl and databases](https://reader036.vdocuments.us/reader036/viewer/2022082620/5a4d1b387f8b9ab05999d8cf/html5/thumbnails/14.jpg)
Connection String in PerlConnection String in Perl
$dbh=DBI->connect('dbi:Oracle' ,$username,$password);
DBI = Database Independent Module, allows for access to a supported SQL database
![Page 15: ECMM6018 Enterprise Networking For Electronic Commerce Tutorial 6 CGI/Perl and databases](https://reader036.vdocuments.us/reader036/viewer/2022082620/5a4d1b387f8b9ab05999d8cf/html5/thumbnails/15.jpg)
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 SELECTed records Close the statement handle Close the connection
![Page 16: ECMM6018 Enterprise Networking For Electronic Commerce Tutorial 6 CGI/Perl and databases](https://reader036.vdocuments.us/reader036/viewer/2022082620/5a4d1b387f8b9ab05999d8cf/html5/thumbnails/16.jpg)
Useful LinksUseful Links
http://www.suite101.com/article.cfm/perl/67813 http://www.saturn5.com/~jwb/dbi-examples.html