Perl and ODBC by bzs12927

VIEWS: 0 PAGES: 36

									Perl and ODBC



       The First Annual
             Perl
         Conference
ODBC




       ODBC stands for:

       Open DataBase Connectivity.
ODBC


          • The ODBC standard      • ODBC has become so
            was designed to work     accepted that some
            on any platform and      vendors like IBM,
            has been ported to       Informix and Watcom
            Win32, Unix,             have designed their
            Macintosh, OS/2 and      DBMS native
            others.                  programming interface
                                     based on ODBC.
History
ODBC
                         •   X/Open
                         •   SQL Access Group
                         •   ANSI
                         •   ISO
                         •   Microsoft
          ODBC was       •   Digital
          designed by:   •   Sybase
                         •   IBM
                         •   Novell
History




                         •   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:
                      •Tier 1
Models




                      •Tier 2
                      •Tier 3
ODBC
                            Tier 1
                  Program calls an ODBC           Program
                  function.
                  ODBC Manager
Models (Tier 1)




                  determines what to do.        ODBC Manager

                  ODBC Driver performs
                                                ODBC Driver
                  actual processing.


                  The database file is opened        Database
                  by the driver and data is            File
                  manipulated.
ODBC
                            Tier 2                  Client
                  Program calls an ODBC             Program
                  function.
                  ODBC Manager
                                                 ODBC Manager
Models (Tier 2)




                  determines what to do.

                  ODBC Driver prepares the
                                                  ODBC Driver
                  request and passes it on to
                  the DBMS.
                                                DBMS
                                                Server   Database
                  The DBMS processes the
                  request.                                 File

                                                   Server
ODBC
                                                     Client
                  Program calls an ODBC              Program
                  function.
                  ODBC Manager
                  determines what to do.          ODBC Manager
Models (Tier 3)




                  ODBC Driver prepares the
                                                   ODBC Driver
                  request and passes it on to
                  the DBMS.
                  Gateway ODBC                  ODBC Manager/Driver
                  Manager/Driver pass the                        Gateway
                  request on to the DMBS.
                                                 DBMS
                                                 Server   Database
                  The DBMS processes the
                  request.                                  File
                                                Server
ODBC

      Data Source Name


                 {
                     Database Information
                     Userid
       DSN   =       Password
                     Connection Information
DSN
ODBC

      Data Source Name : User vs. System

        DSN (aka User DSN) is only
        accessible by the user who created it.

        System DSN is accessible by any user
        including the system itself.
DSN
ODBC

                   ODBC Escape Sequences
                    Syntax: {escape-token parameter}
Escape Sequences




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

                   Outer Joins
                    {oj outer-join}
Escape Sequences




                     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
                    {fn function}
Escape Sequences




                     where “function” is any of several functions:
                       •Time Functions
                           {fn CurDate()}
                        •Character Functions
                           {fn LTrim(FieldName)}
                        •Numeric Functions
                           {fn Rand()}
ODBC

                   Stored Procedures
                    {[?=] call procedure[(parameters…)]}
Escape Sequences




                       • Calling a stored procedure.
                         {call clean_database(db1)}

                       • Calling a stored procedure with a
                         return result.
                         {? = call list_users}
                         {? = copy_table( Table1, Table2)}
ODBC

                   Date & Time Stamps
                    Date = {d „yyyy-mm-dd‟}
                           {d „1997-08-20‟}
Escape Sequences




                    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
               •Interface similar to the ODBC API
               •Most ODBC functions are supported
Why use it




               •Full error reporting
               •Object oriented model
Win32::ODBC

               Alternatives to Win32::ODBC

                •DBI interface by Tim Bunce
                •IODBC Perl module by Brian Jepson
Alternatives




                •ODBCTable by Evangelo Prodromou
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
Win32::ODBC

                      Loading the extension
Using the extension




                        Before using Win32::ODBC you must
                        load the extension into Perl:

                          use Win32::ODBC;
Win32::ODBC

                      How to use the Win32::ODBC
                      extension
Using the 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:
              $db = new Win32::ODBC(“My DSN”);

              You can specify userid & passwords:
Connecting




              $DSN = “DSN=My DSN;UID=Dave;PWD=1234”;
              $db = new Win32::ODBC($DSN);
Win32::ODBC

             Connecting to a database II
              If the connection succeeds the
              result will be an object otherwise it
              will be undef:
Connecting




              if (! $db = new Win32::ODBC($DSN)){
                   …process error…
              }
Win32::ODBC

                     Submitting a Query
                      To submit a SQL query use the Sql()
                      method:
Submitting a Query




                      if ($db->Sql(“SELECT * FROM Foo”)){
                           …process error…
                      }


                             Sql() returns undef if the
                               query is successful.
Win32::ODBC

                     Processing Results
                      To retrieve a row from a dataset use the
                      FetchRow() method:
Processing Results




                      while ($db->FetchRow())
                           …process results…
                      }

                            FetchRow() returns a 1 if
                             a row was successfully
                                   retrieved.
Win32::ODBC

                     Processing Results II
                      Once a row has been fetched you need
                      to extract data with the DataHash()
Processing Results




                      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:
           $db->Close();
Closing
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();
Win32::ODBC

                   Processing Errors II
                    A call to Win32::ODBC::Error() will
                    return the last error that occurred
                    regardless of what connection
Error Processing




                    generated it:
                     $Error = Win32::ODBC::Error();
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();
Win32::ODBC

                   Processing Errors IV
                    The array context will return:
                    1) ODBC Error Number
                    2) Tagged Text
Error Processing




                    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

        •Give proper access to database
CGI
 Win32::ODBC

           Common Gotcha‟s
            Escaping the apostrophe
             SELECT *
             FROM Foo
             WHERE Name like „Joe‟s‟
Gotcha‟s




            SELECT *
            FROM Foo
            WHERE Name like „Joe‟‟s‟
 Win32::ODBC

           Common Gotcha‟s II
            Determining Delimiters:
             if ($db->GetTypeInfo(SQL_CHAR)){
                $db->FetchRow();
               ($Pre, $Suf) = $db->Data(
                              “LITERAL_PREFIX”,
                              “LITERAL_SUFFIX”
Gotcha‟s




                              );
             }
             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.
            To use a constant either refer it through
            your object:
                $db->SQL_CHAR
Gotcha‟s




            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”);

            …or...
               $db = new ODBC(“My DSN”);
Shortcuts




            In other words, the namespaces ODBC and
            Win32::ODBC are synonymous.
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/

								
To top