10-26-2009 ODBC, OLE, ADO, Jdbc, Transfer Data between DBMSODBC, OLE DB, ADOODBCOLE DB GoalsOLE DBADOSlide 7ADO .NETADO.NETADO vs OLE-DBPseudo-FileRelational DB, ADO & XML: OverviewExample of ADO with DatasetsXML File -> Data SourceJDBC & SQLJSteps to use a JDBCExamplesChecklist for JDBC programTransferring Data between DBMSs Extra Credit # 1Simple textMs-Access -> OracleSqlload (sqlldr)Open a DOS WindowMove to the bin sub-directoryLoading Text FileExample of a control fileOracle -> MS-AccessSlide 28Transfer data as XMLExport XMLXML versus HTMLXML versus DBMSXMLTransferring data & web dbwww.w3schools.comNext Assignments, Extra creditEnd of LectureCS 8630 Database Administration, Dr. GuimaraesCopyright © 2009, Dr. Guimaraes10-26-2009ODBC, OLE, ADO, Jdbc, Transfer Data between DBMSClassWill Start Momentarily…CS8630 Database AdministrationDr. Mario GuimaraesCS 8630 Database Administration, Dr. GuimaraesCopyright © 2009, Dr. GuimaraesODBC, OLE DB, ADOOBDC (Open Database Connectivity) is the early standard for relational databases.OLE DB is Microsoft’s object-oriented interface for relational and other databases.ADO (Active Data Objects) is Microsoft’s standard providing easier access to OLE DB data for the non-object-oriented programmer.CS 8630 Database Administration, Dr. GuimaraesCopyright © 2009, Dr. GuimaraesODBCCS 8630 Database Administration, Dr. GuimaraesCopyright © 2009, Dr. GuimaraesOLE DB Goals•Create object interfaces for DBMS functionality pieces:–Query, update, transaction management, etc.•Increase flexibility:–Allow data consumers to use only the objects they need.–Allow data providers to expose pieces of DBMS functionality.–Providers can deliver functionality in multiple interfaces.–Interfaces are standardized and extensible.•Provide object interfaces over any type of data:–Relational and non-relational database, ODBC or native, VSAM and other files, Email, etc.CS 8630 Database Administration, Dr. GuimaraesCopyright © 2009, Dr. GuimaraesOLE DBCS 8630 Database Administration, Dr. GuimaraesCopyright © 2009, Dr. GuimaraesADO•Active Data Objects (ADO) characteristics:–A simple object model for OLE DB data consumers–It can be used from VBScript, JScript, Visual Basic, Java, C#, C++–It is a single Microsoft data access standard–Data access objects are the same for all types of OLE DB dataCS 8630 Database Administration, Dr. GuimaraesCopyright © 2009, Dr. GuimaraesADOCS 8630 Database Administration, Dr. GuimaraesCopyright © 2009, Dr. GuimaraesADO .NET•ADO.NET is a new, improved, and greatly expanded version of ADO that was developed for the Microsoft .NET initiative•It incorporates all of the functionality of ADO and facilitates the transformation of XML documents to and from database data•It uses datasets, which is an in-memory, fully-functioned, independent databasesCS 8630 Database Administration, Dr. GuimaraesCopyright © 2009, Dr. GuimaraesADO.NET•ADO.NET is a new, improved, and greatly expanded version of ADO that was developed for the Microsoft .NET initiative•It incorporates all of the functionality of ADO and facilitates the transformation of XML documents to and from database data•It uses datasets, which is an in-memory, fully-functioned, independent databasesCS 8630 Database Administration, Dr. GuimaraesCopyright © 2009, Dr. GuimaraesADO vs OLE-DB•Just like C#, VB, J# is a higher language than C++•ADO is a higher interface than OLE-DB•Usually, OLE-DB access is done in C++•ADO is accessed through C#, J#, VBCS 8630 Database Administration, Dr. GuimaraesCopyright © 2009, Dr. GuimaraesPseudo-File•Cursor•Recordset•Resultset•Dataset•Remember the SELECT _____ INTO ______ ;CS 8630 Database Administration, Dr. GuimaraesCopyright © 2009, Dr. GuimaraesRelational DB, ADO & XML: Overview•Relational Table -> Read into a DataSet•DataSet -> Write to an XML fileor•XML File -> Read into a DataSet•Dataset -> Write to a Relational Databaseor•Merge one Dataset into another oneCS 8630 Database Administration, Dr. GuimaraesCopyright © 2009, Dr. GuimaraesExample of ADO with Datasets-- VS 2003 Dim fileAndPath As String fileAndPath = "Complete path where you saved the XML file" dsAuthors.ReadXml(fileAndPath) With DataGrid1 .DataSource = dsAuthors .DataMember = "authors" -- VS 2005Dim filePath As StringfilePath = "Complete path where you saved the XML file"dsAuthors.ReadXml(filePath)With (DataGridView1) .DataSource = dsAuthors .DataMember = "authors“ Extra Credit # 2 – Read data from an XML file into an ADO objectCS 8630 Database Administration, Dr. GuimaraesCopyright © 2009, Dr. GuimaraesXML File -> Data Source ‘ Read the contents of an XML file to a data source Dim dsAuthors As New DataSet("authors") Dim fileAndPath As String fileAndPath = "Complete path where you saved the XML file" dsAuthors.ReadXml(fileAndPath) With DataGrid1 .DataSource = dsAuthors .DataMember = "authors" ‘ .CaptionText = .DataMember End With‘ Write schema represenation of the data source to a textbox Dim swXML As New System.IO.StringWriter() dsAuthors.WriteXmlSchema(swXML) TextBox1.Text = swXML.ToStringCS 8630 Database Administration, Dr. GuimaraesCopyright © 2009, Dr. GuimaraesJDBC & SQLJ•JDBC – Java Database Connectivity–A predefined set of classes and methods for accessing SQL databases•SQLJ – SQL for JavaThe Oracle pre-compiler for JAVA. It takes simple Oracle calls and translates them into JAVA code prior to compilation with javacCS 8630 Database Administration, Dr. GuimaraesCopyright © 2009, Dr. GuimaraesSteps to use a JDBC•Download java from sun web-site and install it.•Download jdbc driver for oracle from oracle web-site And place it in the proper subdirectory.In my example:C:\j2sdk1.4.1_06\jre\lib\ext•Create a Java program or download one•If you download a sample program, change connection string (host computer, port number, database instance) •Run your program (make sure you use the full pathname for the Java Compiler and the Java file OR place the files in the proper directory and go to that directoryCS 8630 Database Administration, Dr. GuimaraesCopyright © 2009, Dr. GuimaraesExamples•Lab on JDBC (Extra-Credit # 4)•Query•Update•Insert•Calling a Stored Procedure from JavaCS 8630 Database Administration, Dr. GuimaraesCopyright © 2009, Dr. GuimaraesChecklist for JDBC program•1) Did you download