1 / 36

The First Annual Perl Conference

Perl and ODBC. The First Annual Perl Conference. ODBC. ODBC stands for: O pen D ata B ase C onnectivity. ODBC. The ODBC standard was designed to work on any platform and has been ported to Win32, Unix, Macintosh, OS/2 and others.

jane-ashley
Download Presentation

The First Annual Perl Conference

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Perl and ODBC The First AnnualPerlConference

  2. ODBC ODBC stands for:Open 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 was designed by: X/Open SQL Access Group ANSI ISO Microsoft Digital Sybase IBM Novell Oracle Lotus and others. ODBC 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 Program calls an ODBC function. Program ODBC Manager determines what to do. ODBC Manager ODBC Driver performs actual processing. ODBC Driver Database File The database file is opened by the driver and data is manipulated. Tier 1 (Tier 1) Models

  7. ODBC Program calls an ODBC function. Program ODBC Manager determines what to do. ODBC Manager ODBC Driver prepares the request and passes it on to the DBMS. ODBC Driver DBMSServer Database File The DBMS processes the request. Tier 2 Client (Tier 2) Models Server

  8. ODBC Program calls an ODBC function. Program ODBC Manager determines what to do. ODBC Manager ODBC Driver prepares the request and passes it on to the DBMS. ODBC Driver ODBC Manager/Driver Gateway ODBC Manager/Driver pass the request on to the DMBS. DBMSServer Database File The DBMS processes the request. 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 JOIN{tablename | 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 ‘hh:mm:ss’} Timestamp = {ts ‘yyyy-mm-dd hh:mm:ss’} {d ‘1997-08-20’} Escape Sequences {t ‘15:23:03’} {ts ‘1997-08-20 15:23:03’}

  16. Win32::ODBC Why use Win32::ODBC? • Easy to use • Interface similar to the ODBC API • Most ODBC functions are supported • Full error reporting • Object oriented model Why use it

  17. Win32::ODBC Alternatives to Win32::ODBC • DBI interface by Tim Bunce • IODBC Perl module by Brian Jepson • ODBCTable by Evangelo Prodromou Alternatives

  18. Win32::ODBC How to install Win32::ODBC Assuming Perl is installed in c:\perl 1) Create the directory:c:\perl\lib\auto\win32\odbc Installation 2) Copy ODBC.PLL into the new directory. 3) Copy ODBC.PM into:c:\perl\lib\win32

  19. Win32::ODBC Loading the extension Before using Win32::ODBC you must load the extension into Perl: Using the extension use Win32::ODBC;

  20. Win32::ODBC How to use the Win32::ODBC extension 1) Connect to the database Using the extension 2) Submit a query 3) Process the result 4) Close the database

  21. Win32::ODBC Connecting to a database Make a new connection to a DSN: $db = new Win32::ODBC(“My DSN”); Connecting You can specify userid & passwords: $DSN = “DSN=My DSN;UID=Dave;PWD=1234”; $db = new Win32::ODBC($DSN);

  22. Win32::ODBC II Connecting to a database If the connection succeeds the result will be an object otherwise it will be undef: Connecting if (! $db = new Win32::ODBC($DSN)){…process error…}

  23. Win32::ODBC Submitting a Query To submit a SQL query use the Sql() method: if ($db->Sql(“SELECT * FROM Foo”)){ …process error…} Submitting a Query Sql() returns undef if thequery is successful.

  24. Win32::ODBC Processing Results To retrieve a row from a dataset use the FetchRow() method: while ($db->FetchRow()) …process results…} Processing Results FetchRow() returns a 1 if a row was successfully retrieved.

  25. Win32::ODBC II Processing Results Once a row has been fetched you need to extract data with the DataHash() method: Processing Results undef %Data;%Data = $db->DataHash(); OR undef %Data; %Data = $db->DataHash(“Name”, “Age”);

  26. Win32::ODBC Closing The Database Once the processing of the data has completed, close the connection to the database: Closing $db->Close();

  27. Win32::ODBC Processing Errors If an error occurs you can determine the nature of the error with the Error() method: Error Processing print ”Error: “ . $db->Error();

  28. Win32::ODBC II Processing Errors A call to Win32::ODBC::Error() will return the last error that occurred regardless of what connection generated it: Error Processing $Error = Win32::ODBC::Error();

  29. Win32::ODBC 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: @Error = $db->Error(); Retrieving an error string: $Error = $db->Error();

  30. Win32::ODBC 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. Win32::ODBC Use with a CGI script • Use System DSN’s • Give proper permissions on files • Give proper access to database CGI

  32. Win32::ODBC Common Gotcha’s Escaping the apostrophe SELECT * FROM FooWHERE Name like ‘Joe’s’ Gotcha’s SELECT *FROM FooWHERE Name like ‘Joe’’s’

  33. Win32::ODBC II Common Gotcha’s Determining Delimiters: if ($db->GetTypeInfo(SQL_CHAR)){ $db->FetchRow(); ($Pre, $Suf) = $db->Data(“LITERAL_PREFIX”,“LITERAL_SUFFIX”); } print “$Pre$Text$Suf”; Gotcha’s

  34. Win32::ODBC III Common Gotcha’s • 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()

  35. 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.

  36. Win32::ODBC More Information... Visit the Win32::ODBC Home Page: http://www.roth.net/odbc/ More Information Win32::ODBC FAQ: http://www.roth.net/odbc/odbcfaq.htm Roth Consulting: http://www.roth.net/consult/

More Related