Putting it all together Dynamic Data Base Access

1
Putting it all togetherDynamic Data Base Access

• Norman White
• Stern School of Business

2
Beyond Multimedia

• Question
• How do I add the capability to query/update a
  data base from a WEB page
• Answers
• Need a Client/Server data base
• Oracle, SQL Server tec.
• Need tools to access data base
• Active Server pages
• PERL
• JAVA (JDBC)
• Java Servlets
• Cold Fusion
• Etc

3
Data Driven WEB Pages

• Many business applications depend on knowledge of
  the current environment which is often available
  in a database somewhere
• Need to WEB-Enable applications, so customer,
  supplier etc. can interact directly with a
  companies database.
• Database changes are automatically immediately
  available on web site

4
ExampleActive Server Pages

• ASP files are Visual Basic or Java programs which
  can be mixed with HTML
• The WEB server executes the ASP code as it
  generates the WEB page.
• Code runs on the Server side, client never sees
  it
• Code can dynamically access/update one or more
  databases
• Only runs on Microsoft web servers, but similar
  applications run cross platform (PHP, Cold
  Fusion, Java Servlets etc.)

5
Simple ASP Example

• Hello.asp
• What Day is it?
• Hello,
• Today is
• Bye

6
ASP Database Access

• Results
• Set DBOBJ server.createobject(adodb.connection
  )
• Dbobj.opendsnnwinduidpid
• Set oRS DBOBJ.execute(SQL)

7
Continued

• Customers

8
Output

• Customers
• Id1, Company1
• Id2, Company2
• Idn, CompanyN

9
How do we handle FORMs in ASP

• GET form
• Var REQUEST.QUERYSTRING(field)
• Eg
• Response.write( Your userid is userid
• REQUEST.FORM returns POST parameters
• Retrieves the userid field from the form for
  usage in the ASP program

10
But what good is all this?

• Things get interesting if some of the fields in
  my database contain links to URLs
• Now I can have a database that maintains
  information about web objects
• I can allow user to select certain search
  characteristics
• To update info, I update the database, and the
  links are automatically generated

11
example

• You have a music collection of a bunch of mp3
  files which you keep adding to. You maintain
  information about your collection in a database
• Your database
• Song Table
• Artist Id, album name, record label, date of
  release, type of music,name of song, link to MP3
  file
• Artist Table
• Artist name, bio, link to picture

12
Your application

• Web page with form that allows users to choose
  selection criteria
• ASP file processes form and retrieves a list of
  links that point to songs that satisfy selection
• User clicks on link and song plays

13
Additional Applications

• Add a song
• Add an artist
• Delete a song
• Delete an artist
• Update a song

14
Conclusion

• Simple ASP files can be very powerful
• WEB code doesnt grow as data grows
• Once built, no maintenance (except for updating
  the database, which in many cases is already
  being done)
• Easy to web enable existing database
  applications

15
Server Side Scripting Languages Which support DB
Access

• ASP (Visual Basic, JavaScript) C and C coming
  soon.
• Cold Fusion (CFM files)
• PhP (Free open source solution, runs on Unix,
  Linux, NT) Supports all major databases
• PERL DBI (Data Base Interface)
• Java, Javascript Servlets

16
How do you identify server side scripting

• In most cases, the language processors become
  PART of the WEB browser.
• Dramatically reduces overhead involved with
  running a separate program (ala CGI)

17
Other Alternative

• JDBC Java Data Base Connectivity
• Allow Java applet to directly connect to a
  remote database
• Problems .
• Security (java can only connect back to server
  its code came from)
• Overhead for connection(s) across internet
• Depends on users browser being able to handle
  java
• Best use is within an intranet.

18
PHP

• PHP is more powerful than ASP, and has direct
  support for a number of major databases
• PHP runs on almost all web servers
• PHP is free!
• Downside is documentation is weak, and the
  programming environment complex.
• Better for big, complex projects

19
Simple PHP Program

• PHP Test
• " ?
• OR

20
Cold Fusion

• Cold Fusion is a platform independent development
  system similar to ASP
• Some extra features include replication and
  rollover
• You can replicate cold fusion sites for better
  performance, and you can have one site back up
  another site in case one fails.

21
Other development environments

• Java Servlets
• Java Server Pages
• We will talk about these later in the course, but
  they allow reusable components and a write once
  run anywhere environment.