Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

What is the difference between ADO and ODBC ?

( #117694=categorized question: print w/ replies, xml ) Need Help??
Contributed by ciamack on Oct 09, 2001 at 15:10 UTC
Q&A  > database programming


Description:

Regarding CGI programing:

I am new to DB in Perl and I'm not sure what the difference between these 2 methods below is. I have a MS access mdb file with tables and data on a server. I can't ask my ISP to create a DSN for me. I have used "method2" below and it works fine.

  • I wonder if these methods are multi threading?
  • If several users access access my DB at the same time it will function properly?

Thank you in advance.

method 1:

use Win32::OLE; $ConnStr = "DSN=DsnName;UID=UserName;PWD=pass; APP=PerlTest;WSID=MyComp;DATABASE=MyDB"; $Conn = Win32::OLE->new('ADODB.Connection'); $Conn->Open($ConnStr);

method 2:

use Win32::ODBC; $DriverType = "Microsoft Access Driver (*.mdb)"; $DSN = "Win32 ODBC --MAOmaoMAOmaoMAO--"; $Dir = "c:\\perl\\cgi-bin"; $DBase = "mydb.mdb"; Win32::ODBC::ConfigDSN(ODBC_ADD_DSN, $DriverType, ("DSN=$DSN", "Description=MAO Win32 ODBC Test DSN for perl", "DBQ=$Dir\\$DBase", "DEFAULTDIR=$Dir", "UID=", "PWD=")) or die "ConfigDSN(): Could not add temporary DSN" . Win32::ODBC::Error(); $db=new Win32::ODBC($DSN) or die "couldn't ODBC $DSN because", Win32::ODBC::Error(), "\n";

Answer: What is the difference between ADO and ODBC ?
contributed by jehuni

ADO is basically a high-level OO interface to various low-level OLEDB data providers. ODBC is another kind of low-level data provider, and if there is no native OLEDB provider for the type of database you use, but there is an ODBC one, you can always use the OLEDB provider for ODBC to bridge that gap.

IMHO, ADO is a much more flexible option than dealing directly with a data provider like ODBC, unless you really have the need to some sort of low-level optimization. Plus, for an MS database technology like Access, using a native OLEDB provider should be faster.

Since you can't set up a DSN, I believe that something like this will work for you:

use strict; use Win32::OLE; my $provider = 'Microsoft.Jet.OLEDB.4.0'; my $dir = 'c:\perl\cgi-bin'; my $dbase = 'mydb.mdb'; my $user = ''; #use if needed my $password = ''; #use if needed my $connStr = "Provider=$provider;Data Source=$dir\\$dbase;User ID=$us +er;Password=$password;"; my $conn = Win32::OLE->new('ADODB.Connection') or die("Could not creat +e Connection: $!"); $conn->Open($connStr);

-jehuni

Answer: What is the difference between ADO and ODBC ?
contributed by CubicSpline

While ADO and ODBC are both Microsoft standards, ODBC is a de facto standard across most platforms. ADO is a set of COM objects (read Windows-only) that wrap ODBC functionality into tight little objects. ADO is a quick and easy way to set up database access in a windows environment. If you're programming in Windows, then you're set either way. However it is widely considered that you'll get better performance using straight ODBC rather than using ADO.

As far as your problems connecting to your database, I don't quite understand what you're saying. You are in control of your own DSNs. If you have an Access database sitting on a server, you can create your own DSN using the ODBC Data Source control panel. You choose the ODBC driver, the server that hosts your database, and then choose the path to the database itself.

Answer: What is the difference between ADO and ODBC ?
contributed by Anonymous Monk

ODBC is alow level engine for accessing DB. It requires dealing with DOBC APIs in order to be able to access a DB, this will make it a little bit tedious but effecient. On the other hand, ADO is a COM component that is based on the ODBC, it provides the functionality offered by the ODBC in an Object Oriented way. ADO is way more easier to deal with than to deal with ODBC, but on the cost of lower effeciency.

Answer: What is the difference between ADO and ODBC ?
contributed by Anonymous Monk

From experience in writing wrappers for both and profiling both using DevPartner I can actually say that ODBC is a lot faster. You trade of ease against performance when going with ADO. Though this depends on the wrapper you write and the way you use the ODBC calls. Besides ODBC is portable between platforms and you can use it to read flat files, access files, excel files, sql server, oracle, mysql etc.

Please (register and) log in if you wish to add an answer



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others scrutinizing the Monastery: (8)
    As of 2014-08-21 10:32 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      The best computer themed movie is:











      Results (133 votes), past polls