+==========================================================+ | | | ODBC extension for Win32 Perl | | ----------------------------- | | | | by Dave Roth | | or | | | | version v960528 (hack) | | | | | | ODBC.PM package | | --------------- | | Release v960528 | | | | Copyright (c) 1996 Dave Roth. All rights reserved. | | This program is free software; you can redistribute | | it and/or modify it under the same terms as Perl itself. | | | +==========================================================+ based on original code by Dan DeMaggio (dmag@umich.edu) Use under GNU General Public License or Larry Wall's "Artistic License" This is a hack of Dan DeMaggio's (dmag@umich.edu) NTXS.C ODBC implimentation. I have recoded and restructred much of it including most of the ODBC.PM package but its core is still based on Dan's code (thanks Dan!). ODBC.PM HISTORY: --------------- 96.04.07 Joseph L. Casadonte Jr. -Modified Data() to accept multiple field names and return an array or scalar. -Confirm Win95 compliance. -Various bug fixes. 96.04.10 Dave Roth or -Fixed the RowCount() to default to the current connection. 96.04.15 Dave Roth or -Changed version numbers to a date format. 96.05.07 Dave Roth or -Fixed Data(): If returning an array, the array consisted of the requested fieldnames + the values. Now only returns the values. Thanks to Dan Westerlund ! 96.05.11 Dave Roth or -Corrected SetStmtCloseType(): it was using the CloseType as if it was a ConnectionNumber instead. 96.05.28 Dave Roth or -Added ODBCColAttributes(): returns attribute for a column in a data set. ODBC.PLL HISTORY: ---------------- 96.04.10 Dave Roth or -Fixed a "memory bug": we were using SQL_CLOSE when closing an ODBC statement. This kept the cursor alive in memory so it can be cached in the event the same SQL statement is issued. We are now using SQL_DROP. This may lessen speed if the same SQL statements are issued again and again per connection. -Cleaned up some code. 96.04.12 Dave Roth or -Added G/SetStmtCloseType() functions. -Added some constants. 96.04.13 ** We now are trying to include a version for builds up to and including 105 and another for builds 106 and greater (for now). 96.04.15 Jutta M. Klebe -Fix Bug in ODBCFetchRow(): when retrieving a field with a NULL value the value from the previous field was reported. -Changed version numbers to a date format. 96.04.22 Jutta M. Klebe -Fix the SDWORD wrap-around bug in ODBCFetchRow(): when a column of size 2147483647 adding 1 (for a NULL byte in szBuf) yields (-2147483648) (not easy to "net UCHAR (-2147483648)"!!) 96.04.22 Dave Roth or -Inspired by Jutta, I have increased the max limit for SetMaxBufSize() to 2,147,483,647 bytes. 96.05.03 Dave Roth or -Set the lowest allocated char array to be 20 bytes in ODBCFetchRow(). Evidentally sometimes the ODBC manager will report too few chars that are needed to represent an autonumber field. 96.05.08 Dave Roth or -Convert all results from ODBCTableList() to uppercase since different ODBC drivers impliment this differently (some uppercase some lower). Thanks again to Jutta M. Klebe *** This patch is open for suggestions!!! *** 96.05.13 Mike Knister -Change SQL_COLUMN_LENGTH to SQL_COLUMN_DISPLAY_SIZE in the SQLColAttributes() line withing ODBC_Fetch(). This was returning the number of bytes needed to represent the C data type for the particular column. We need it to return the number of bytes needed to represent an ASCII version of the value held within the data type. 96.05.28 Dave Roth or -Added the ColAttributes() method. -Added Version() method. Following in tradition... ***************************************************************************** * * * Use under GNU General Public License or Larry Wall's "Artistic License" * * * ***************************************************************************** ---------------------------------------------------------------- NOTICE: I do not guarantee ANYTHING with this package. If you use it you are doing so AT YOUR OWN RISK! I may or may not support this depending on my time schedule and I am neither an SQL or ODBC guru so do not ask me questions regarding them! ---------------------------------------------------------------- I compiled this using MSVC++ 2.2 on an Intel machine. I do not have access to other platforms to compile on so I will not be doing so. If someone else does, all the best! What is the deal with this? - The number of ODBC connections are limited by memory and ODBC itself (have as many as you want!) - The working limit to the size of a field is 10240 bytes but you can increase that limit (if needed) to a max of 2147483647. You can always recompile to increase the max limit. - You can open a connection by either specifing a DSN or a connection string! - You can open and close the connections in any order! - Other things that I can not think of right now.... :) What known problems does this thing have? - If the account that the process runs under does not have write permission on the default directory (for the process not the ODBC DSN) you will probably get a run time error durring an SQLConnection(). I don't think that this is a problem with the code, more like ODBC. This happens because some ODBC drivers need to write a temporary file. I noticed this using the MS Jet Engine (Access Driver). - This has not been neither optimized for speed nor optimized for memory consumption. This may run into memory bloat. To test out this ODBC.PLL, install it then run the TEST.PL included with this archive. You must first, however alter TEST.PL: - The second line of code ($DSN="xxx"), replace the xxx with a real DSN that is on your system. ** I use a directory structure of \Perl\lib for my library files and this doc is ** assuming you do too. You will, of course, have to compensate for deviations. T O I N S T A L L T H I S B E A S T : ========================================= 1) You will need to dump the ODBC.PM file into the \PERL\LIB\WIN32\ directory. 2) You need to copy either ODBC105.PLL or ODBC106.PLL (depending on your version of Win32 Perl's build number. Run: perl -v to check which build you have) into \PERL\LIB\AUTO\WIN32\ODBC\ODBC.PLL.\ **** Notice that we are changing the name to ODBC.PLL!!!! --------------------------------------------------------- If you already have another package \PERL\LIB\ODBC.PM (notice the path does NOT include Win32) you do not need it for this!!! To use this you need to define a DSN (Data Source Name) and have all the stuff you need to use it (ie. proper ODBC drivers, database file, etc). You are now ready to ODBC all over town! T O U S E T H I S B E A S T : ================================= Your script will need to have the following line: use Win32::ODBC; Then you need to create a data connection to your DSN... $Data = new Win32::ODBC("MyDSN"); NOTE: "MyDSN" should be either the DSN as defined in the ODBC Administrator -OR- It can be an honest-to-God DSN Connect string example: "DSN=My Database;UID=Brown Cow;PWD=Moo;" You should check to see if $Data is indeed defined otherwise there has been an error. You can now send SQL queries and retrieve info to your heart's content! See the description of functions below and also the test.pl to see how it all works. MAKE SURE that you close you connection when you are finished: $Data->Close(); L I S T O F F U N C T I O N S : ================================= The ODBC.PM Package supports the following functions: Catalog($Qualifier, $Owner, $Name, $Type) Tells ODBC to create a data set that contains table information about the DSN. The returned format is: [Qualifier] [Owner] [Name] [Type] returns: TRUE if error. ColAttributes($Attribute, [@FieldNames]) Returns the attribute $Attribute on each of the fields @FieldNames in the current record set. If @FieldNames is empty then all fields are assumed. returns: associative array of attributes. Connection() Returns the connection number associated with the ODBC connection. returns: Number identifying an ODBC connection Close() Closes the ODBC connection. returns: Nothing. Data($ColName) Returns the contents of column name $ColName or the current row. returns: String. DataSources() Returns an associative array of Data Sources and ODBC remarks about them. They are returned in the form of: $ArrayName{'DSN'}=Remark Where DSN is the Data Source Name and Remark is, well, the remark. returns: Associative array. Drivers() Returns an associative array of ODBC Driverss and thier attributes. They are returned in the form of: $ArrayName{'DRIVER'}=Attrib1;Attrib2;Attrib3;... Where DRIVER is the ODBC Driver Name and AttribX are the driver defined attributes. returns: Associative array. DumpData() Dumps to the screen the fieldnames and all records of the current data set. This is used primarily for debugging. returns: Nothing Error() Returns the last encountered error. returns: String. FetchRow() Retrieves the next record from the dataset. returns: TRUE FieldNames() Returns an array of fieldnames found in the current data set. There is no guarantee on order. returns: Array of field names. GetConnections() Returns an array of connection numbers showing what connections are currently open. returns: Array of connections GetStmtCloseType([$Connection]) Returns the type of closure that will be used everytime the hstmt is freed. See SetStmtClsoeType() for details. By default, the currect object's connection will be used. If $Connection is a valid connection number, then it will be used. returns: String. GetDSN($Con) Returns the DSN that was used during the creation of the connection ($Con). If $Con is not passed then it assumes the current connection. returns: String. GetMaxBufSize() This will report the current allocated limit for the MaxBufSize. For info see SetMaxBufSize(). returns: Max number of bytes. new($DSN) Creates a new ODBC connection based on $DSN. $DSN = Data Source Name or a proper ODBCDriverConnect string. returns: Not true if it failed. RowCount($Connection) For UPDATE, INSERT, and DELETE statements the returned value is the number of rows affected by the request or -1 if the number of affected rows is not available. *** NOTE: This function is not supported by all ODBC drivers! Some drivers do support this but not for all statements (eg. it is supported for UPDATE, INSERT and DELETE commands but not for the SELECT command). *** NOTE: Many data sources cannot return the number of rows in a result set before fetching them; for maximum interoperability, applications should not rely on this behavior. returns: Number of affected rows or -1 if not supported by driver in the current context. Run($Sql) This will execute the $Sql command and dump to the screen info about it. This is used primarily for debugging. returns: Nothing SetMaxBufSize($Size) This will set the MaxBufSize for a particular connection. This will most likely never be needed but... The amount of memory that is allocated to retrieve a records field data is dynamic and changes when it need to be larger. I found that a memo field in an MS Access database ended up requesting 4 Gig of space. This was a bit much so there is an imposed limit (2,147,483,647 bytes) that can be allocated for data retrieval. Since it is possible that someone has a database with field data greater than 10240 you can use this function to increase the limit up to a ceiling of 2147483647 (recompile if you need more). returns: Max number of bytes. SetStmtCloseType($Type [, $Connection]) This will set a particular hstmt close type for the connection. This is the same as ODBCFreeStmt(hstmt, $Type). By default, the currect object's connection will be used. If $Connection is a valid connection number, then it will be used. Types: SQL_CLOSE SQL_DROP SQL_UNBIND SQL_RESET_PARAMS Returns the newly set type. returns: String. ShutDown() This will close the ODBC connection and dump to the screen info about it. This is used primarily for debugging. returns: Nothing Sql($SQLString) Executes the SQL command $SQLString on a particular connection. returns: Error number if it failed TableList($Qualifier, $Owner, $Name, $Type) Returns the catalog of tables that are availabe in the DSN. If you do not know what the parameters are just leave them "". returns: Array of table names. Version(@Packages) Returns the version numbers for the requested packages ("ODBC.PM" or "ODBC.PLL"). If @Packages is empty then all version numbers will be returned. returns: Array of version numbers. ======================= Example of Use ======================================= use Win32::ODBC; uses CGI; $Cgi = new CGI; $| = 1; $Template = "Template"; print "Content-type: text/html\n\n\n\nAn ODBC Test\n\n"; ($DSN, $Table) = ($Cgi->param('Table') =~ /(\S*)\s*(\S.*)/); $Table =~ s/\s*(\S.*)(\s|\n|\x0a|\x0d)*$/$1/gi; $DSN =~ s/\s*(\S.*)(\s|\n|\x0a|\x0d)*$/$1/gi; ($Num = $Cgi->param('ID')) =~ s/[\s|-|\.]//gi; if ($DSN eq "" | $Table eq ""){ $Message = "Your Table or DSN was invald. This may be because you have not specified them or you have a bad browser. You may want to try another browser.

"; print "$Message\n\n"; Log("Error: DSN or Table is blank"); exit(); } $O = new Win32::ODBC("$DSN"); if (!$O){ $Message = "The $DSB database is currently unavailable. Try again later."; print "$Message\n\n"; Log("Error: Could not connect; ODBC error during connection"); exit(); } $Sql = "SELECT * FROM [$Table] "; if (! $O->Sql($Sql)){ print "

\n

";

        foreach $Key ($O->FieldNames){
            print $O->Data($Key), "  ";
            $Line .= "-" x length($Key) . "  ";
        }
        print "\n$Line\n";
        if ($O->FetchRow()){
            foreach $Key ($O->FieldNames){
                print $O->Data($Key), "  ";
            }

            $Result = "Successful";
        }else{
            $Result = "Unsuccessful";
        print "
\n\n"; } }else{ $Message = "The database seems to be down. Try again later."; print "$Message\n\n"; $Result = "Error: " . $O->error; } $O->Close(); Log($Result); sub Log{ local($Result) = @_; if (open(LOG, ">> TEST.log")){ print LOG $Cgi->remote_addr(), " \"", $Cgi->user_agent(), "\" \"$DSN\" \"$Table\" \"$Num\" \"$Result\"\n"; close(LOG); } }