1 / 25

Connecting to a Database through Programs

Connecting to a Database through Programs. Database Systems. DB Connections. Typically only DBA ‘talk’ directly to a database End users generally connect to a database through some type of program Program code Forms. Connection Part 1. Database tables are not ‘part’ of a program

gilead
Download Presentation

Connecting to a Database through Programs

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Connecting to a Database through Programs Database Systems

  2. DB Connections • Typically only DBA ‘talk’ directly to a database • End users generally connect to a database through some type of program • Program code • Forms

  3. Connection Part 1 • Database tables are not ‘part’ of a program • So you have to establish a connection to them • Connections are categorized in 2 categories • READ • Insert, Update and Delete

  4. Not all DBs are Alike • Not all databases act the same way, you have to know which way your database acts • This determines the facilities needed for your connection • Then you have to remember that relational database systems speak SQL

  5. Connection Introduction • Because database applications today reside in a complicated environment, various standards have been developed for accessing database servers. • Some of the important standards are • OBDC (Open Database Connectivity) is the early standard for relational databases. • OLE DB is Microsoft’s object-oriented interface for relational and other databases. • ADO.NET (Active Data Objects) is Microsoft’s standard providing easier access to OLE DB data for the non-object-oriented programmer.

  6. The Role of the ODBC Standard

  7. Open Database Connectivity (OBDC) • The Open Database Connectivity (ODBC) standard provides a DBMS-independent means for processing relational database data. • It was developed in the early 1990s by an industry committee and has been implemented by Microsoft and many other vendors. • The goal is to allow a developer to create a single application that can access databases supported by different DBMS products without needing to be changed or recompiled.

  8. ODBC Architecture

  9. OBDC Components (Continued) • OBDC consists of a data source, an application program, a driver manager, and a DBMS driver. • Adata source is the database and its associated DBMS, operating system, and network platform. • An ODBC data source can be a relational database, a file server, or a spreadsheet. • Anapplications program issues requests to create a connection with a data source.

  10. OBDC Components • A driver manager determines the type of DBMS for a given ODBC data source and loads that driver in memory. • A DBMS driver processes ODBC requests and submits specific SQL statements to a given type of data source.

  11. Conformance Levels • Levels of conformance balance the scope of the OBDC standard. • There are two types of conformance levels: • ODBC conformance levels concern the features and functions that are made available through the driver’s application program interface (API). • A driver API is a set of functions that the application can call to receive services. • SQL conformance levels specify which SQL statements, expressions, and data types a driver can process.

  12. Summary ofOBDC Conformance Levels

  13. Summary ofSQL Conformance Levels So, why do conformance levels matter? You pick the ODBC driver you use based on your needs

  14. How do you connect? • Depends on the database and the programming environment • Access can establish a DNS so that Access may be used as a front end to a database (ie front end to an oracle db) • Java establishes a ODBC connection via the JDBC library • C#, VB.Net use a Microsoft provided ‘Managed Provider’, ADO.Net or OBDC

  15. The Steps whatever the case • Establish a connection • Open the connection • Select data using an Object with SQL statement • Retrieve the Data • Data is not used directly from the database • Retrieved into hold place such as a variable or object • Display the data • OR Issue a Insert, Update or Delete command • Send data to the database • Usually number of rows affected is returned

  16. Example • Sample Code • http://science.kennesaw.edu/~mcmurray/dbTables/labjava/Customers.java • JDBC walkthrough • http://www.javacoffeebreak.com/articles/jdbc/index.html

  17. Where does the data go? • Insert, Update, Delete • Pushing data out to the Database • What is returned is the number of rows affected • What happens when you read [select] data? • Somehow you have to store the data in memory

  18. Data retrieval • How much data are you retrieving? • One row – it’s fairly trivial • Use variables • Lots of Rows • Considerations to make and it will depend on the technology being used

  19. Cursor • Used by Oracle PL/SQL is known as ‘cursor’ • Way data is accessed row by row • Can be utilized in Java

  20. Example • To retrieve data with SQL one row at a time you need to use cursor processing. • Cursor processing is done in several steps: • Define the rows you want to retrieve. This is called declaring the cursor. • Open the cursor. This activates the cursor and loads the data. • Note that declaring the cursor doesn't load data, opening the cursor does. • Fetch the data into variables. • Close the cursor. http://www.fluffycat.com/SQL/Cursor-Oracle/

  21. Cursor References • http://www.exforsys.com/tutorials/oracle-9i/oracle-cursors.html • http://www.oracle.com/technology/documentation/berkeley-db/je/GettingStartedGuide/Cursors.html

  22. .NET • Microsoft’s .NET uses • Datareader • Processing row by row • Datasets • Declared space in memory (data structure that looks like a table) • Defined using XML schema • Loads all results of select statements into memory for processing http://quickstart.developerfusion.co.uk/quickstart/howto/doc/adoplus/GetDataFromDB.aspx

  23. Labs • ACCESS connecting to Oracle • http://aspalliance.com/893_Creating_an_MS_Access_2003_Front_End_for_an_Oracle_10g_Express_Edition_Database • ADO.Net using Visual Studio (for IS and ISA majors) • Need an Oracle Managed Provider • One is includes with Visual Studio but many problems reported so need to get one from Oracle • Oracle 11g is has managed provided for ADO.Net built in • JDBC for CS majors

  24. SOME JAVA Examples • http://java.sun.com/developer/codesamples/jdbc.html • http://www.cs.ubc.ca/~ramesh/cpsc304/tutorial/JDBC/jdbc1.html • http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/GettingStartedTOC.fm.html • http://www.jdbc-tutorial.com/ • http://www.javacoffeebreak.com/articles/jdbc/index.html

  25. Some MySQL PHP Resources • Connecting to a MySQL database via PHP • http://www.php-mysql-tutorial.com/connect-to-mysql-using-php.php • http://dev.mysql.com/tech-resources/articles/ddws/21.html • Resources for graphical support of forms development PHP • http://phpmyadmin.qarchive.org/ • MySQL with JDBC • http://www.developer.com/java/data/article.php/3417381

More Related