Perl and ODBC

1
Perl and ODBC
The First AnnualPerlConference
2
ODBC
ODBC stands forOpen DataBase Connectivity.
3
ODBC

• The ODBC standard was designed to work on any
  platform and has been ported to Win32, Unix,
  Macintosh, OS/2 and others.

• ODBC has become so accepted that some vendors
  like IBM, Informix and Watcom have designed their
  DBMS native programming interface based on ODBC.

History
4
ODBC

• X/Open
• SQL Access Group
• ANSI
• ISO
• Microsoft
• Digital
• Sybase
• IBM
• Novell
• Oracle
• Lotus
• and others.

• ODBC was designed by

History
5
ODBC
There are different ODBC models (or tiers) each
describing the number of layers that must be
passed through before the database is reached.
Models

• The three most common are
• Tier 1
• Tier 2
• Tier 3

6
ODBC
Tier 1
(Tier 1)
Models
7
ODBC
Tier 2
Client
(Tier 2)
Models
Server
8
ODBC
Client
(Tier 3)
Models
Gateway
Server
9
ODBC
Data Source Name

Database InformationUseridPasswordConnection
Information

DSN
DSN
10
ODBC
Data Source Name
User vs. System
DSN (aka User DSN) is only accessible by the user
who created it.
DSN
System DSN is accessible by any user including
the system itself.
11
ODBC
ODBC Escape Sequences
Syntax
escape-token parameter

• Outer Joins
• Scalar Functions
• Stored Procedures
• Date Time Stamps

Escape Sequences
12
ODBC
Outer Joins
oj outer-join
where outer-join is tablename LEFT RIGHT
FULL OUTER JOINtablename outer-join ON
search-condition
Escape Sequences
SELECT FROM oj Machine LEFT OUTER JOIN Users
ON Machine.Name Users.Name
13
ODBC
Scalar Functions
fn function

• where function is any of several functions
• Time Functions
• Character Functions
• Numeric Functions

Escape Sequences
fn CurDate()
fn LTrim(FieldName)
fn Rand()
14
ODBC
Stored Procedures
? call procedure(parameters)

• Calling a stored procedure.
• Calling a stored procedure with a return result.

Escape Sequences
call clean_database(db1)
? call list_users? copy_table( Table1,
Table2)
15
ODBC
Date Time Stamps
Date d yyyy-mm-dd Time t
hhmmss Timestamp ts yyyy-mm-dd
hhmmss
d 1997-08-20
Escape Sequences
t 152303
ts 1997-08-20 152303
16
Win32ODBC
Why use Win32ODBC?

• Easy to use
• Interface similar to the ODBC API
• Most ODBC functions are supported
• Full error reporting
• Object oriented model

Why use it
17
Win32ODBC
Alternatives to Win32ODBC

• DBI interface by Tim Bunce
• IODBC Perl module by Brian Jepson
• ODBCTable by Evangelo Prodromou

Alternatives
18
Win32ODBC
How to install Win32ODBC
Assuming Perl is installed in c\perl
1) Create the directoryc\perl\lib\auto\win32\od
bc
Installation
2) Copy ODBC.PLL into the new directory.
3) Copy ODBC.PM intoc\perl\lib\win32
19
Win32ODBC
Loading the extension
Before using Win32ODBC you must load the
extension into Perl
Using the extension
use Win32ODBC
20
Win32ODBC
How to use the Win32ODBC extension
1) Connect to the database
Using the extension
2) Submit a query
3) Process the result
4) Close the database
21
Win32ODBC
Connecting to a database
Make a new connection to a DSN
db new Win32ODBC(My DSN)
Connecting
You can specify userid passwords
DSN DSNMy DSNUIDDavePWD1234 db new
Win32ODBC(DSN)
22
Win32ODBC
II
Connecting to a database
If the connection succeeds the result will be an
object otherwise it will be undef
Connecting
if (! db new Win32ODBC(DSN)) process
error
23
Win32ODBC
Submitting a Query
To submit a SQL query use the Sql() method
if (db-gtSql(SELECT FROM Foo)) process
error
Submitting a Query
Sql() returns undef if thequery is successful.
24
Win32ODBC
Processing Results
To retrieve a row from a dataset use the
FetchRow() method
while (db-gtFetchRow()) process results
Processing Results
FetchRow() returns a 1 if a row was successfully
retrieved.
25
Win32ODBC
II
Processing Results
Once a row has been fetched you need to extract
data with the DataHash() method
Processing Results
undef DataData db-gtDataHash() OR undef
Data Data db-gtDataHash(Name, Age)
26
Win32ODBC
Closing The Database
Once the processing of the data has completed,
close the connection to the database
Closing
db-gtClose()
27
Win32ODBC
Processing Errors
If an error occurs you can determine the nature
of the error with the Error() method
Error Processing
print Error . db-gtError()
28
Win32ODBC
II
Processing Errors
A call to Win32ODBCError() will return the
last error that occurred regardless of what
connection generated it
Error Processing
Error Win32ODBCError()
29
Win32ODBC
Processing Errors
III
The Error() method returns either an array or a
string depending upon the context of the return
Error Processing
Retrieving an array of errors
_at_Error db-gtError()
Retrieving an error string
Error db-gtError()
30
Win32ODBC
Processing Errors
IV
The array context will return
1) ODBC Error Number2) Tagged Text3) Connection
Number4) SQLState
Error Processing
The string context will return
ErrorNum Connection SQLState Text
31
Win32ODBC
Use with a CGI script

• Use System DSNs
• Give proper permissions on files
• Give proper access to database

CGI
32
Win32ODBC
Common Gotchas
Escaping the apostrophe
SELECT FROM FooWHERE Name like Joes
Gotchas
SELECT FROM FooWHERE Name like Joes
33
Win32ODBC
II
Common Gotchas
Determining Delimiters
if (db-gtGetTypeInfo(SQL_CHAR)) db-gtFetchRow()
(Pre, Suf) db-gtData(LITERAL_PREFIX,LIT
ERAL_SUFFIX) print PreTextSuf
Gotchas
34
Win32ODBC
III
Common Gotchas

• There are over 650 constants so only a few are
  exported into the main namespace.

Gotchas
To use a constant either refer it through your
object
db-gtSQL_CHAR
Or as function through the namespace
Win32ODBCSQL_CHAR()
35
Win32ODBC
Shortcuts
Win32ODBC reserves the ODBC namespace
functions can be accessed as
db new Win32ODBC(My DSN)
Shortcuts
or...
db new ODBC(My DSN)
In other words, the namespaces ODBC and
Win32ODBC are synonymous.
36
Win32ODBC
More Information...
Visit the Win32ODBC Home Page
http//www.roth.net/odbc/
More Information
Win32ODBC FAQ
http//www.roth.net/odbc/odbcfaq.htm
Roth Consulting
http//www.roth.net/consult/