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)
• Servlets

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

5
Simple ASP Example

• Hello.asp
• lthtmlgtltheadgtlttitlegtWhat Day is it?lt/titlegtlt/headgt
• ltbodygt
• ltscript languagevbscript runatservergt
• Hello, ltPgt
• lt datToday Date() gt
• Today is lt datToday gt
• ltPgt
• Bye
• lt/bodygtlt/htmlgt

6
ASP Database Access

• lt_at_ languagevbscript gt
• lthtmlgtltheadgtlttitlegtResultslt/titlegtlt/headgt
• ltbodygt
• lt SQL SELECT FROM CUSTOMERS
• Set DBOBJ server.createobject(adodb.connection
  )
• Dbobj.opendsnnwinduidpid
• Set oRS DBOBJ.execute(SQL) gt

7
Continued

• ltPgtCustomerslt/pgt
• lttable border 3gt
• lt while not oRS.EOF gt
• ltTRgt
• ltTDgt lt oRS.Fields(CUSTOMERID).Value gt lt/TDgt
• ltTDgt lt oRS.Fields(COMPANYNAME).Value gt lt/TDgt
• lt/TRgt
• lt oRS.Movenext gt
• ltWEND gt
• lt/tablegt lt/bodygtlt/htmlgt

8
Output

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

9
But what good is this for multimedia?

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

10
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, filename
• Artist Table
• Artist name, bio, link to picture

11
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

12
Additional Applications

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

13
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 applications