ODBC - Roth Consulting | studyslide.com

ODBC - Roth Consulting

Transcript ODBC - Roth Consulting

Perl and ODBC
The First Annual
Perl
Conference
ODBC
ODBC stands for:
Open DataBase Connectivity.
ODBC
History
• 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.
ODBC
History
ODBC was
designed by:
•
•
•
•
•
•
•
•
•
•
•
•
X/Open
SQL Access Group
ANSI
ISO
Microsoft
Digital
Sybase
IBM
Novell
Oracle
Lotus
and others.
ODBC
There are different ODBC models (or tiers)
each describing the number of layers that must
be passed through before the database is
reached.
The three most common are:
Models
•Tier 1
•Tier 2
•Tier 3
ODBC
Tier 1
Models (Tier 1)
Program calls an ODBC
function.
Program
ODBC Manager
determines what to do.
ODBC Manager
ODBC Driver performs
actual processing.
ODBC Driver
The database file is opened
by the driver and data is
manipulated.
Database
File
ODBC
Tier 2
Models (Tier 2)
Program calls an ODBC
function.
ODBC Manager
determines what to do.
ODBC Driver prepares the
request and passes it on to
the DBMS.
The DBMS processes the
request.
Client
Program
ODBC Manager
ODBC Driver
DBMS
Server
Database
File
Server
ODBC
Client
Program calls an ODBC
function.
Models (Tier 3)
ODBC Manager
determines what to do.
ODBC Driver prepares the
request and passes it on to
the DBMS.
Gateway ODBC
Manager/Driver pass the
request on to the DMBS.
The DBMS processes the
request.
Program
ODBC Manager
ODBC Driver
ODBC Manager/Driver
Gateway
DBMS
Server
Server
Database
File
ODBC
Data Source Name
DSN
DSN
=
{
Database Information
Userid
Password
Connection Information
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.
ODBC
Escape Sequences
ODBC Escape Sequences
Syntax: {escape-token parameter}
•Outer Joins
•Scalar Functions
•Stored Procedures
•Date & Time Stamps
ODBC
Outer Joins
Escape Sequences
{oj outer-join}
where “outer-join” is:
tablename {LEFT | RIGHT | FULL}
OUTER JOIN{tablename | outer-join}
ON search-condition
SELECT *
FROM {oj Machine LEFT OUTER JOIN Users ON
Machine.Name = Users.Name}
ODBC
Scalar Functions
Escape Sequences
{fn function}
where “function” is any of several functions:
•Time Functions
{fn CurDate()}
•Character Functions
{fn LTrim(FieldName)}
•Numeric Functions
{fn Rand()}
ODBC
Stored Procedures
Escape Sequences
{[?=] call procedure[(parameters…)]}
• Calling a stored procedure.
{call clean_database(db1)}
• Calling a stored procedure with a
return result.
{? = call list_users}
{? = copy_table( Table1, Table2)}
ODBC
Escape Sequences
Date & Time Stamps
Date = {d ‘yyyy-mm-dd’}
{d ‘1997-08-20’}
Time = {t ‘hh:mm:ss’}
{t ‘15:23:03’}
Timestamp = {ts ‘yyyy-mm-dd hh:mm:ss’}
{ts ‘1997-08-20 15:23:03’}
Win32::ODBC
Why use Win32::ODBC?
•Easy to use
Why use it
•Interface similar to the ODBC API
•Most ODBC functions are supported
•Full error reporting
•Object oriented model
Win32::ODBC
Alternatives to Win32::ODBC
Alternatives
•DBI interface by Tim Bunce
•IODBC Perl module by Brian Jepson
•ODBCTable by Evangelo Prodromou
Win32::ODBC
How to install Win32::ODBC
Assuming Perl is installed in c:\perl
Installation
1) Create the directory:
c:\perl\lib\auto\win32\odbc
2) Copy ODBC.PLL into the new
directory.
3) Copy ODBC.PM into:
c:\perl\lib\win32
Win32::ODBC
Using the extension
Loading the extension
Before using Win32::ODBC you must
load the extension into Perl:
use Win32::ODBC;
Using the extension
Win32::ODBC
How to use the Win32::ODBC
extension
1) Connect to the database
2) Submit a query
3) Process the result
4) Close the database
Win32::ODBC
Connecting to a database
Make a new connection to a DSN:
Connecting
$db = new Win32::ODBC(“My DSN”);
You can specify userid & passwords:
$DSN = “DSN=My DSN;UID=Dave;PWD=1234”;
$db = new Win32::ODBC($DSN);
Win32::ODBC
Connecting to a database II
Connecting
If the connection succeeds the
result will be an object otherwise it
will be undef:
if (! $db = new Win32::ODBC($DSN)){
…process error…
}
Win32::ODBC
Submitting a Query
Submitting a Query
To submit a SQL query use the Sql()
method:
if ($db->Sql(“SELECT * FROM Foo”)){
…process error…
}
Sql() returns undef if the
query is successful.
Win32::ODBC
Processing Results
Processing Results
To retrieve a row from a dataset use the
FetchRow() method:
while ($db->FetchRow())
…process results…
}
FetchRow() returns a 1 if
a row was successfully
retrieved.
Win32::ODBC
Processing Results
Processing Results II
Once a row has been fetched you need
to extract data with the DataHash()
method:
undef %Data;
%Data = $db->DataHash();
OR
undef %Data;
%Data = $db->DataHash(“Name”, “Age”);
Win32::ODBC
Closing The Database
Once the processing of the data has
completed, close the connection to the
database:
Closing
$db->Close();
Win32::ODBC
Error Processing
Processing Errors
If an error occurs you can determine the
nature of the error with the Error()
method:
print ”Error: “ . $db->Error();
Win32::ODBC
Error Processing
Processing Errors II
A call to Win32::ODBC::Error() will
return the last error that occurred
regardless of what connection
generated it:
$Error = Win32::ODBC::Error();
Win32::ODBC
Processing Errors III
Error Processing
The Error() method returns either an
array or a string depending upon the
context of the return:
Retrieving an array of errors:
@Error = $db->Error();
Retrieving an error string:
$Error = $db->Error();
Win32::ODBC
Error Processing
Processing Errors IV
The array context will return:
1) ODBC Error Number
2) Tagged Text
3) Connection Number
4) SQLState
The string context will return:
“[ErrorNum] [Connection] [SQLState] [Text]”
Win32::ODBC
Use with a CGI script
•Use System DSN’s
•Give proper permissions on files
CGI
•Give proper access to database
Win32::ODBC
Common Gotcha’s
Escaping the apostrophe
Gotcha’s
SELECT *
FROM Foo
WHERE Name like ‘Joe’s’
SELECT *
FROM Foo
WHERE Name like ‘Joe’’s’
Win32::ODBC
Common Gotcha’s II
Determining Delimiters:
Gotcha’s
if ($db->GetTypeInfo(SQL_CHAR)){
$db->FetchRow();
($Pre, $Suf) = $db->Data(
“LITERAL_PREFIX”,
“LITERAL_SUFFIX”
);
}
print “$Pre$Text$Suf”;
Win32::ODBC
Common Gotcha’s III
• There are over 650 constants so only
a few are exported into the main
namespace.
Gotcha’s
To use a constant either refer it through
your object:
$db->SQL_CHAR
Or as function through the namespace:
Win32::ODBC::SQL_CHAR()
Win32::ODBC
Shortcuts
Win32::ODBC reserves the ODBC
namespace; functions can be accessed as:
$db = new Win32::ODBC(“My DSN”);
Shortcuts
…or...
$db = new ODBC(“My DSN”);
In other words, the namespaces ODBC and
Win32::ODBC are synonymous.
Win32::ODBC
More Information
More Information...
Visit the Win32::ODBC Home Page:
http://www.roth.net/odbc/
Win32::ODBC FAQ:
http://www.roth.net/odbc/odbcfaq.htm
Roth Consulting:
http://www.roth.net/consult/

ODBC - Roth Consulting

Transcript ODBC - Roth Consulting

Directory