1 / 33

10-31-2008 ODBC, OLE, ADO, Jdbc, Transfer Data between DBMS

CS8630 Database Administration Dr. Mario Guimaraes. 10-31-2008 ODBC, OLE, ADO, Jdbc, Transfer Data between DBMS. Class Will Start Momentarily…. ODBC, OLE DB, ADO. OBDC (Open Database Connectivity) is the early standard for relational databases.

livi
Download Presentation

10-31-2008 ODBC, OLE, ADO, Jdbc, Transfer Data between DBMS

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. CS8630 Database Administration Dr. Mario Guimaraes 10-31-2008ODBC, OLE, ADO, Jdbc, Transfer Data between DBMS • Class • Will • Start • Momentarily…

  2. ODBC, OLE DB, ADO • 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 (Active Data Objects) is Microsoft’s standard providing easier access to OLE DB data for the non-object-oriented programmer.

  3. ODBC

  4. OLE 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.

  5. OLE DB

  6. ADO • 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 data

  7. ADO

  8. ADO .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 databases

  9. ADO.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 databases

  10. ADO 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#, VB

  11. -- VS 2003 Dim fileAndPath As String fileAndPath = "Complete path where you saved the XML file" dsAuthors.ReadXml(fileAndPath) WithDataGrid1 .DataSource = dsAuthors .DataMember = "authors" -- VS 2005 Dim filePath As String filePath = "Complete path where you saved the XML file" dsAuthors.ReadXml(filePath) With (DataGridView1) .DataSource = dsAuthors .DataMember = "authors“ VS 03 & VS 05

  12. Relational DB & XML: Overview • Relational Table -> Read into a DataSet • DataSet -> Write to an XML file or • XML File -> Read into a DataSet • Dataset -> Write to a Relational Database or • Merge one Dataset into another one

  13. XML 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.ToString

  14. JDBC & SQLJ • JDBC – Java Database Connectivity • A predefined set of classes and methods for accessing SQL databases • SQLJ – SQL for Java The Oracle pre-compiler for JAVA. It takes simple Oracle calls and translates them into JAVA code prior to compilation with javac

  15. Steps 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 directory

  16. Lab on JDBC Query Update Insert Calling a Stored Procedure from Java Examples

  17. Checklist for JDBC program • 1) Did you download the proper JDBC driver for your version of Oracle ? • 2) Is the JDBC driver in the proper sub-directory ? • 3) Is the .Java or .Class files in the proper subdirectory and/or used proper paths • 4) Is Java compiler in your path • 5) Is the Oracle instance open ? • 6) Does your program has the correct host computer, port number and database instance in the connection string ? • 7) Are the tables that you are trying to access belong to the user/password that your java program is trying to login to ?

  18. Transferring Data between DBMSs

  19. Simple text • Simplest and Most traditional way to transfer data from DBMS 1 to DBMS 2 1) DBMS 1 saves a table to a simple text file 2) Simple text file is edited to specific format that DBMS 2 will recognize 3) DBMS 2 imports the simple text file

  20. Ms-Access -> Oracle • 1) Launch MS-Access & Open a Database • 2) Select Table (Export Menu -> Save as Simple Text • 3) Using Notepad or Wordpad, remove special characters such as ‘$’ • 4) Create a table in Oracle • 5) Use SQL Loader (sqlldr) to load the text file into the table

  21. Sqlload (sqlldr) • Launch MS-Access, Open DB and Select Table • File Menu -> Export Option -> Save file as Text (in bin subdirectory)

  22. Open a DOS Window

  23. Move to the bin sub-directory • Note that sqlldr is located in the bin sub-directory. sqlldr may be in the path, but it is easier if you place the text file in the bin subdirectory and change to the bin sub-directory

  24. Loading Text File • SQLLDR userid/pwd control=[controlFile] log=[logfile]

  25. Example of a control file load data infile orderDetails.txt replace into table orderDetails fields terminated by ",“ ( OrdNo, ProductID, UnitPrice, Quantity)

  26. Oracle -> MS-Access • Login to Oracle • Type in the spool and a name of a file • Type select * from [tablename]; • Open the text file with wordpad & notepad and remove headers (clean file), etc. Also rename it as txt • Launch MS-Access and Import the file

  27. Note that when you type in • SQL> spool orders SQL will print everything that normally appears just on the screen to the file orders.lst in the bin sub-directory.

  28. Transfer data as XML

  29. Export XML • You need the xml file and the schema file (data definition, similar to DDL) to transfer data • You need to select the presentation of your data also if if you want to display data with a web browser.

  30. XML versus HTML • Pre-defined tags • Data with how data is presented is in the same file • Seperates data from how data is presented • Allows you to define new datatypes

  31. XML versus DBMS • DDL and • Data • Schema • XML file

  32. XML

  33. End of Lecture End Of Today’s Lecture.

More Related