Docstoc

OLE and ODBC Taming the Technologies

Document Sample
OLE and ODBC Taming the Technologies Powered By Docstoc
					      The Third Annual Perl Conference, 1999




    OLE and ODBC: Taming
       the Technologies

                    ODBC
Roth Consulting                 Sunday, August 22, 1999
   ODBC: Open DataBase
   Connectivity
   • Uses SQL
   • Requires ODBC to be installed
   • Win32::ODBC supports access into the ODBC
     API




Roth Consulting   OLE and ODBC: Taming the Technologies   2
   ODBC Options
   • Perl can access ODBC
         – Win32::OLE ( OLEDB, ADO )
                  • Memory & processor overhead
                  • Not guaranteed to be ODBC or SQL
                  • Some bugs yet to be ironed out (eg. SQL Server and multiple
                    text columns)
         – ODBCTie
                  • Is this still maintained?
         – DBI
                  • General DBI rules apply
                  • Cross platform
         – Win32::ODBC
                  • Requires Win32 machine

Roth Consulting                 OLE and ODBC: Taming the Technologies             3
   Who uses Perl and ODBC?
   • Unbelievable amount of Perl and ODBC use
         –   Database maintenance
         –   Administration
         –   Web commerce
         –   Inter/intranet data access
   • Professional Applications
         – Digital Paper’s Intranet Docs (IDOC) product
                  •   Partner with Xerox
                  •   99% Perl based
                  •   Can serve up to 2 million documents
                  •   http://www.xes.com/usa/products/doc_man/intradoc.htm


Roth Consulting                 OLE and ODBC: Taming the Technologies        4
   What drivers are installed?
   • Enumerate installed ODBC drivers with:
        %Drivers = Win32::ODBC::Drivers()
         – Returns a hash
         – Hash keys are driver names
         – Hash value is string with driver specific attributes
           delimited by semicolons:
           “Attrib1=Value1;Attrib2=Value2;Attribn=Valuen”




Roth Consulting        OLE and ODBC: Taming the Technologies      5
   DSN: Data Source Name
   • All database connections begin with a DSN
   • Named database configuration
   • Three types:
         – User DSN
         – System DSN
         – File DSN
   • Win 95/98 only understand User and File
   • When used as a CGI/ASP script with a web server
     always use System DSN!


Roth Consulting     OLE and ODBC: Taming the Technologies   6
   DSN: Data Source Name II
   • Create manually using the ODBC control panel
     applet
   • Create using Win32::ODBC::ConfigDSN()
             ConfigDSN( $Action, $Driver, $Attrib1, $Attribn )
         – Actions
                  ODBC_ADD_DSN                 Add new DSN
                  ODBC_MODIFY_DSN              Modify existing DSN
                  ODBC_REMOVE_DSN              Remove existing DSN
                  ODBC_ADD_SYS_DSN             Add new system DSN
                  ODBC_MODIFY_SYS_DSN          Modify existing system DSN
                  ODBC_REMOVE_SYS_DSN          Remove existing system DSN
         – Driver depends upon installed drivers (keys from
           Win32::ODBC::Drivers() ):
                  Microsoft Access Driver (*.mdb)

Roth Consulting           OLE and ODBC: Taming the Technologies             7
   DSN: Data Source Name III
         – Attributes are any valid ODBC driver keywords
                  • One required keyword:
                      – DSN=Foo
                  • Other keywords differ based on ODBC driver
                  • Best way to discover keywords is by reading docs or manually
                    creating a DSN then examining the Registry
                  • Do not include a “Driver” keyword




Roth Consulting               OLE and ODBC: Taming the Technologies                8
   DSN: Data Source Name IV
   use Win32::ODBC;

   # Create a DSN...
   Win32::ODBC::ConfigDSN( ODBC_ADD_DSN,
                           $Driver,
                           "DSN=My DSN Name",
                           "Description=A Test DSN",
                           "DBQ=c:\\temp\\$MyData.mdb",
                           "DEFAULTDIR= C:\\Database",
                           "UID=Admin",
                           "PWD=Adminpassword" );




Roth Consulting       OLE and ODBC: Taming the Technologies   9
   Connecting
   • Create a new Win32::ODBC object:
             $db = new Win32::ODBC( "My DSN" );

   • The DSN can either be the name of a DSN or it
     can be a full connect string:
         – “My DSN”
         – “DSN=My DSN;UID=Foo;PWD=Bar”
   • If the DSN passed in is really a Win32::ODBC
     object then that object is “cloned”
             $db2 = new Win32::ODBC( $db );
         – $db2 is identical to $db but with different STMT’s
         – Some database systems do not like such clones

Roth Consulting         OLE and ODBC: Taming the Technologies   10
   Executing SQL Statement
   • Submit a text based SQL query
             $Result = $db->Sql( “SELECT * FROM Foo” );

   • This is the only method call which returns a non-
     false value upon failure
         – Returns error number (ODBC driver specific; not really
           valuable)
         – Call $db->Error() for more error details




Roth Consulting         OLE and ODBC: Taming the Technologies       11
   Fetching Results
   • Call FetchRow() until it returns false
   • Collect the data with Data() or DataHash()
        while( $db->FetchRow() )
        {
          my( %Data ) = $db->DataHash();
          …process data…
        }




Roth Consulting   OLE and ODBC: Taming the Technologies   12
   Batch Queries
   • If you submitted a batch query or a stored
     procedure returns multiple result sets repeat the
     FetchRow() process until MoreResults()
     returns FALSE.
        do
        {
          while( $db->FetchRow() )
          {
            my( %Data ) = $db->DataHash();
            …process data…
          }
        } while( $db->MoreResults() );

Roth Consulting    OLE and ODBC: Taming the Technologies   13
   Closing The Connection
   • To close the database connection call Close()
             $db->Close();




Roth Consulting         OLE and ODBC: Taming the Technologies   14
   Transactions
   • By default an ODBC connection is in AutoCommit
     mode
         – All transactions are committed to the database immediately
   • Turn off AutoCommit mode with:
        $db->SetConnectOption( $db->SQL_AUTOCOMMIT,
                               $db->SQL_AUTOCOMMIT_OFF );
   • Perform queries (select, insert, delete, update, etc)
   • To commit or rollback call Transact():
             $db->Transact( $db->SQL_COMMIT );
             $db->Transact( $db->SQL_ROLLBACK );




Roth Consulting         OLE and ODBC: Taming the Technologies      15
   Row Counts
   • Report number of rows in the result set with:
             $db->RowCount();
   • Not all ODBC drivers support it
   • Some ODBC drivers only support it for insert and
     delete
   • Alternative is to issue a SQL count query:
             SELECT Count(*) FROM Foo
         – The result set is one row and one column containing a
           value which is the number of rows




Roth Consulting        OLE and ODBC: Taming the Technologies       16
   Connection Options
   • You can set and query connection options with:
             $db->GetConnectOption( $Option );
             $db->SetConnectOption( $Option, $Value );

   • SetConnectOption() returns TRUE if
     successfully set and FALSE if it failed to set the
     option
   • GetConnectOption() returns the current value of
     the specified option. It does not return any errors!




Roth Consulting         OLE and ODBC: Taming the Technologies   17
   Connection Option Examples
   • To discover the current qualifier (SQL Server this is
     the database, in Access it is the .mdb file):
        $Row = $db->GetConnectOption( $db->
        SQL_CURRENT_QUALIFIER );
   • To change the login timeout value (in seconds):
        $db->SetConnectOption( $db->SQL_LOGIN_TIMEOUT, 10 );
   • Query the ODBC trace log file:
        $db->GetConnectOption( $db->SQL_OPT_TRACEFILE );
   • Set ODBC trace logging to on:
        $db->SetConnectOption( $db->SQL_OPT_TRACE,
                               $db->SQL_OPT_TRACE_ON );




Roth Consulting     OLE and ODBC: Taming the Technologies    18
   Special Connect Options
   • Some connection options must be set before the
     connection to the database is performed
   • Pass these into the new() function:
        $db = new Win32::ODBC( $Dsn,
            ODBC::SQL_LOGIN_TIMEOUT => 10,
            ODBC::SQL_OPT_TRACEFILE => „c:\trace.log‟,
            ODBC::SQL_OPT_TRACE => ODBC::SQL_OPT_TRACE_ON );




Roth Consulting     OLE and ODBC: Taming the Technologies   19
   Stmt Options
   • Every time you submit a command to the database
     (such as a query) call it a statement or a stmt (for
     short)
   • You can set and query options for stmt’s with:
             $db->GetStmtOption( $Option );
             $db->SetStmtOption( $Option, $Value );

   • SetStmtOption() returns TRUE if successfully
     set and FALSE if it failed to set the option
   • GetStmtOption() returns the current value of the
     specified option. It does not return any errors!


Roth Consulting         OLE and ODBC: Taming the Technologies   20
   Stmt Option Examples
   • To discover the current row:
        $Row = $db->GetStmtOption( $db->SQL_ROW_NUMBER );
   • To change the query timeout value (in seconds):
        $db->SetStmtOption( $db->SQL_QUERY_TIMEOUT, 10 );
   • Set the driver to not scan the query for escape clauses:
        $db->SetStmtOption( $db->SQL_NOSCAN,
                            $db->SQL_NOSCAN_ON );




Roth Consulting     OLE and ODBC: Taming the Technologies   21
   Other Sources Of Information
   • Programming DBI, by Tim Bunce and Alligator Descartes,
                                       Alligator Descartes,
     O’Reilly & Associates.
   • Win32 Perl Programming: The Standard Extensions, Dave
                                    Standard Extensions, Dave
     Roth, MacMillan Publishing.
   • Win32 Scripting Journal,
     http://www.winntmag.com/newsletter/scripting/
   • The Perl Journal, http://www.tpj.com/




Roth Consulting     OLE and ODBC: Taming the Technologies       22
Roth Consulting   OLE and ODBC: Taming the Technologies   23

				
DOCUMENT INFO