OLE and ODBC: Taming the Technologies

1
OLE and ODBC Taming the Technologies
The Third Annual Perl Conference, 1999

• ODBC

Sunday, August 22, 1999
Roth Consulting
2
ODBC Open DataBase Connectivity

• Uses SQL
• Requires ODBC to be installed
• Win32ODBC supports access into the ODBC API

3
ODBC Options

• Perl can access ODBC
• Win32OLE ( 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
• Win32ODBC
• Requires Win32 machine

4
Who uses Perl and ODBC?

• Unbelievable amount of Perl and ODBC use
• Database maintenance
• Administration
• Web commerce
• Inter/intranet data access
• Professional Applications
• Digital Papers 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.h
  tm

5
What drivers are installed?

• Enumerate installed ODBC drivers with
• Drivers Win32ODBCDrivers()
• Returns a hash
• Hash keys are driver names
• Hash value is string with driver specific
  attributes delimited by semicolons
• Attrib1Value1Attrib2Value2AttribnValuen

6
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!

7
DSN Data Source Name
II

• Create manually using the ODBC control panel
  applet
• Create using Win32ODBCConfigDSN()
• ConfigDSN( Action, Driver, Attrib1, Attribn
  )
• Actions
• ODBC_ADD_DSN Add new DSNODBC_MODIFY_DSN Modify
  existing DSNODBC_REMOVE_DSN Remove existing
  DSNODBC_ADD_SYS_DSN Add new system
  DSNODBC_MODIFY_SYS_DSN Modify existing system
  DSNODBC_REMOVE_SYS_DSN Remove existing system
  DSN
• Driver depends upon installed drivers (keys from
  Win32ODBCDrivers() )
• Microsoft Access Driver (.mdb)

8
DSN Data Source Name
III

• Attributes are any valid ODBC driver keywords
• One required keyword
• DSNFoo
• 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

9
DSN Data Source Name
IV
use Win32ODBC Create a DSN... Win32ODBCC
onfigDSN( ODBC_ADD_DSN,
Driver, "DSNMy DSN
Name", "DescriptionA
Test DSN",
"DBQc\\temp\\MyData.mdb",
"DEFAULTDIR C\\Database",
"UIDAdmin",
"PWDAdminpassword" )
10
Connecting

• Create a new Win32ODBC object
• db new Win32ODBC( "My DSN" )
• The DSN can either be the name of a DSN or it can
  be a full connect string
• My DSN
• DSNMy DSNUIDFooPWDBar
• If the DSN passed in is really a Win32ODBC
  object then that object is cloned
• db2 new Win32ODBC( db )
• db2 is identical to db but with different
  STMTs
• Some database systems do not like such clones

11
Executing SQL Statement

• Submit a text based SQL query
• Result db-gtSql( 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-gtError() for more error details

12
Fetching Results

• Call FetchRow() until it returns false
• Collect the data with Data() or DataHash()
• while( db-gtFetchRow() ) my( Data )
  db-gtDataHash() process data

13
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-gtFetchRow() ) my(
  Data ) db-gtDataHash() process data
  while( db-gtMoreResults() )

14
Closing The Connection

• To close the database connection call Close()
• db-gtClose()

15
Transactions

• By default an ODBC connection is in AutoCommit
  mode
• All transactions are committed to the database
  immediately
• Turn off AutoCommit mode with
• db-gtSetConnectOption( db-gtSQL_AUTOCOMMIT, db-
  gtSQL_AUTOCOMMIT_OFF )
• Perform queries (select, insert, delete, update,
  etc)
• To commit or rollback call Transact()
• db-gtTransact( db-gtSQL_COMMIT )db-gtTransact(
  db-gtSQL_ROLLBACK )

16
Row Counts

• Report number of rows in the result set with
• db-gtRowCount()
• 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

17
Connection Options

• You can set and query connection options with
• db-gtGetConnectOption( Option
  )db-gtSetConnectOption( 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!

18
Connection Option Examples

• To discover the current qualifier (SQL Server
  this is the database, in Access it is the .mdb
  file)Row db-gtGetConnectOption( db-gt
  SQL_CURRENT_QUALIFIER )
• To change the login timeout value (in
  seconds)db-gtSetConnectOption(
  db-gtSQL_LOGIN_TIMEOUT, 10 )
• Query the ODBC trace log filedb-gtGetConnectOpti
  on( db-gtSQL_OPT_TRACEFILE )
• Set ODBC trace logging to ondb-gtSetConnectOptio
  n( db-gtSQL_OPT_TRACE, db-gtSQL_OPT_TRACE_ON )

19
Special Connect Options

• Some connection options must be set before the
  connection to the database is performed
• Pass these into the new() functiondb new
  Win32ODBC( Dsn, ODBCSQL_LOGIN_TIMEOUT gt
  10, ODBCSQL_OPT_TRACEFILE gt
  c\trace.log, ODBCSQL_OPT_TRACE gt
  ODBCSQL_OPT_TRACE_ON )

20
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 stmts with
• db-gtGetStmtOption( Option )db-gtSetStmtOption
  ( 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!

21
Stmt Option Examples

• To discover the current rowRow
  db-gtGetStmtOption( db-gtSQL_ROW_NUMBER )
• To change the query timeout value (in
  seconds)db-gtSetStmtOption( db-gtSQL_QUERY_TIMEO
  UT, 10 )
• Set the driver to not scan the query for escape
  clausesdb-gtSetStmtOption( db-gtSQL_NOSCAN, db
  -gtSQL_NOSCAN_ON )

22
Other Sources Of Information

• Programming DBI, by Tim Bunce and Alligator
  Descartes, OReilly Associates.
• Win32 Perl Programming The Standard Extensions,
  Dave Roth, MacMillan Publishing.
• Win32 Scripting Journal, http//www.winntmag.com/n
  ewsletter/scripting/
• The Perl Journal, http//www.tpj.com/

• Programming DBI, by Tim Bunce and Alligator
  Descartes, OReilly Associates.
• Win32 Perl Programming The Standard Extensions,
  Dave Roth, MacMillan Publishing.
• Win32 Scripting Journal, http//www.winntmag.com/n
  ewsletter/scripting/
• The Perl Journal, http//www.tpj.com/

23
(No Transcript)