1 / 49

Open Source and Informix Dynamic Server

I07. Open Source and Informix Dynamic Server. Jonathan Leffler IBM Information Management. Tuesday 3 rd October 2006 • 15:15 – 16:15. Agenda. Open Source Connecting to IDS Perl, DBI, and DBD::Informix Tcl/Tk and isqltcl PHP Aubit 4GL SQLCMD SQSL Python Ruby. Open Source.

lorene
Download Presentation

Open Source and Informix Dynamic Server

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. I07 Open Source and Informix Dynamic Server Jonathan Leffler IBM Information Management Tuesday 3rd October 2006 • 15:15 – 16:15

  2. Agenda • Open Source • Connecting to IDS • Perl, DBI, and DBD::Informix • Tcl/Tk and isqltcl • PHP • Aubit 4GL • SQLCMD • SQSL • Python • Ruby

  3. Open Source • What is Open Source? • Which rock have you been hiding under? • Software released under an Open Source license • Conformant with the Open Source Definition • Found at http://www.opensource.org/ • Free Redistribution • Source Code • Derived Works Permitted • No Discrimination Against People or Groups • No Discrimination Against Fields of Endeavour • Distribution of License

  4. Open Source Licenses • There are many Open Source licenses • GPL – GNU General Public License • LGPL – GNU Lesser General Public License • BSD – Berkeley Systems Distribution • MIT – Massachusetts Institute of Technology • MPL – Mozilla Public License • Academic Free License • Open Software License • Nearly 60 licenses at the Open Source Initiative!

  5. Informix Database Connectivity • ESQL/C • The original connectivity. • Standardized in SQL by ISO/IEC 9075:1992 • ODBC • Originally defined by Microsoft. • Standardized (as CLI) by ISO/IEC 9075-3:1996. • JDBC • Java analogue of ODBC. • Standardized by Sun. • All of these are proprietary. • But can be used with Open Source software.

  6. ESQL/C • Preprocessor that converts extended C into pure C. • Links with specific libraries. • Separates static and dynamic SQL. • Even though Informix does not really do so. int main(void) { EXEC SQL WHENEVER ERROR STOP; EXEC SQL DATABASE Stores; EXEC SQL BEGIN WORK; EXEC SQL DROP TABLE Customer; EXEC SQL ROLLBACK WORK; return(0); }

  7. ODBC • Database agnostic. • Separates driver manager from drivers. • Different drivers can be loaded at run time. • You can avoid database-specific features. • But sometimes you want to use them. • All statements are dynamic. • De-emphasized by Microsoft • In favour of newer technologies • ADO, .NET

  8. JDBC • Database agnostic. • Drivers have different levels of Java-ness. • Type 4: pure Java – usually the best type to use. • The other way to connect in Java is ESQL/J. • Not widely accepted. • JDBC is the lingua franca of the Java database world.

  9. Perl – Practical Extraction and Report Language • Originally written by Larry Wall • Version 1.0 in 1987 • Version 5.0 in 1994 • Version 6 under development for a long time • Apocalypse 1 posted April 2001 (at http://perl.com/) • Current stable version: • 5.8.8 — February 2006 • Obtain via CPAN • Comprehensive Perl Archive Network • http://www.cpan.org/

  10. Perl • Script Language • Does not require separate compilation • Complex looking code • Can be incredibly terse • Can be quite legible • Excellent at string handling • Excellent access to operating system • Extensible • A myriad modules available at CPAN • http://www.cpan.org/

  11. Perl Database Interface • DBI written by Tim Bunce. • Standard way to access databases with Perl. • Many database drivers available. • Including ODBC, DB2, and Oracle. • And, of course, Informix. • And many others. • DBI – version 1.52, August 2006. • Requires Perl 5.6.1 or later. • DBD::Informix – version 2005.02, July 2005.

  12. DBI – Database Handles • Load DBI • use DBI; • Create database handles • $dbh = DBI->connect(‘DBI:Informix:stores7’); • Database methods • $dbh->do(‘DELETE FROM Customer’); • Transaction control • $dbh->rollback; • $dbh->commit; • Disconnect • $dbh->disconnect;

  13. DBI – Statement Handles • Create statement handles • my $xname = $dbh->quote(“%$name%”); • $sth = $dbh->prepare(qq{ DELETE FROM Customer WHERE Lname LIKE $xname AND ZipCode IS NULL }); • Statements can be executed • $sth->execute(); • Statement handles can be released • Implicitly – statement handle goes out of scope • Explicitly – undef $sth;

  14. Danger – SQL Injection • What happens if the code is written as: • $sth = $dbh->prepare(qq{ DELETE FROM Customer WHERE Lname LIKE ‘%$name%’ AND ZipCode IS NULL }); • This is a security breach ready to happen • SQL injection exploit. • What happens if the user enters this name: • X%’ OR fname != ‘X’ OR fname = ‘

  15. Danger – SQL Injection • The query is now: • DELETE FROM Customer WHERE Lname LIKE ‘%X%’ OR fname != ‘X’ OR fname = ‘%’ AND ZipCode IS NULL • This deletes all (most) rows from the table! • Use $dbh->quote($name) – if you must. • Better to use placeholders (?) in the SQL • $sth = $dbh->prepare(qq{ DELETE FROM Customer WHERE Lname LIKE ? AND ZipCode IS NULL });

  16. DBI – Handling SELECT • Statement handles are used for SELECT too • $sth = $dbh->prepare(q% SELECT * FROM Customer WHERE Fname = ? AND Lname = ? ORDER BY Lname, Fname%); • $sth->execute($firstname, $surname); • @results = $sth->fetchall_arrayref; • …process results… • print $results[$rownum][$colnum]; • undef $sth;

  17. DBI – Handling SELECT • Many ways to fetch rows • $sth->fetchrow_array • $sth->fetchrow_hashref • $sth->fetchrow_arrayref • $sth->fetchall_arrayref • All rows • Also utility methods • $dbh->selectrow_array • First row only • $dbh->selectall_arrayref

  18. DBD::Informix – example #! /usr/bin/perl -w use DBI; $dbh = DBI->connect(‘DBI:Informix:stores7’,’’,’’, {RaiseError => 1, PrintError=>1}); $sth = $dbh->prepare(q%SELECT Fname, Lname, Phone FROM Customer WHERE Customer_num = ? %); $sth->execute(106); $ref = $sth->fetchall_arrayref(); for $row (@$ref) { print “Name: $$row[0] $$row[1], Phone: $$row[2]\n”; } $dbh->disconnect;

  19. Tcl/Tk and isqltcl • Tcl – Tool Control Language • Invented by John Ousterhout • Tk – Tool Kit (GUI) • Tcl/Tk – at http://www.tcl.tk/ • Current version 8.4.13 – April 2006. • isqltcl – Informix SQL access via Tcl. • Available at http://isqltcl.sourceforge.net/ • Version 5.0 – released February 2002. • Builds into dynamically loadable shared library

  20. Tcl/Tk Extensions • Tcl/Tk is designed to be easily extended • Many extensions available for all jobs • For example • Expect • Designed to handle scripting of processes • Used for automating testing • ftp://expect.nist.gov/ • And many more...

  21. Loading ISQLTCL • Load the ISQLTCL extension • load isql.so • Adds the command ‘sql’ to Tcl/Tk • tclsh • wish

  22. ISQLTCL – Connections • Connect to a database • sql connect dbase as conn1 user $username \ password $password • Connect to given database • sql disconnect [current|default|all|conn1] • Close database connection • sql setconnection [default|conn1] • Sets the specified connection

  23. ISQLTCL – Statements • Executable statements • Statements that return no data • sql run {delete from sometable where pkcol = ?} $pkval • Prepares and executes the statement • Optionally takes a number of arguments for placeholders • Returns zero on success; non-zero on failure

  24. ISQLTCL – Cursors • SELECT, EXECUTE PROCEDURE • set stmt [sql open {select * from sometable}] • Does PREPARE, DECLARE, and OPEN • Returns a statement number (id) or a negative error • Optionally takes arguments for placeholders • set row [sql fetch $stmt 1] • Collects one row of data • As a Tcl list in the variable ‘row’ • The 1 is optional and means strip trailing blanks • The list is empty if there is no more data

  25. ISQLTCL – Cursors • sql reopen $stmt ?arg1? ?arg2? • Reopens the statement, with new parameters • sql close $stmt • Indicates you have no further use for the statement • It frees both the cursor and statement!

  26. What is PHP? • Hypertext Processor • Was once ‘Personal Home Page’ • Version 4.4.2 released January 2006 • Version 5.0.5 released September 2005 • Version 5.1.6 released August 2006 • An HTML scripting language • Server-side • Cross-platform • Embedded in HTML documents • Extensible • Web site: http://php.net/

  27. What is PHP? • Built into the Apache Web Server • Using DSO (dynamic shared objects) • mod_php • Or as a CGI binary • With any web server • PHP has a reputation for being insecure. • Largely a question of how it is used. • See PHP Security Consortium • http://phpsec.org/ • Not clear how active this is…

  28. What is PHP? • Built-in access to: • Email • XML • HTTP (cookies, sessions) • And databases: • ODBC • DB2, Adabas-D, Empress, Solid, Velocis • mSQL, MySQL, PostgreSQL • Sybase, Oracle • Informix

  29. What is PHP? • IBM also provides modern PDO drivers • PDO – PHP Data Objects • PHP analogue of Perl DBI • Article on DeveloperWorks • http://tinyurl.com/eycg2 • For DB2 • Via PDO_ODBC • For IDS • Version 1.0.0 – December 2005 (stable) • Version 1.0.1 – May 2006 (alpha) • http://pecl.php.net/package/PDO_INFORMIX

  30. Informative PHP Script <HTML> <HEAD> <TITLE> PHP Information </TITLE></HEAD> <BODY> <?php echo “URL: <B>http://$HTTP_HOST$PHP_SELF</B>” echo “<BR>\n” phpinfo() ?> </BODY> </HTML>

  31. Old Informix Driver • Code provided as standard part of PHP. • But not maintained for several years. • Must be explicitly compiled into PHP. • 30 core functions. • 8 functions to manipulate SBLOBs.

  32. Old Informix Driver • Connection management • ifx_connect • ifx_pconnect • ifx_close • Basic Operations • ifx_prepare • ifx_query • ifx_fetch_row • ifx_do • ifx_free_result

  33. Old Informix Driver • Status and Error Handling • ifx_getsqlca • ifx_error • ifx_errormsg • ifx_affected_rows • Attribute Queries • Blob handling • Utility functions • ifx_htmltbl_result

  34. New Informix Driver • Accessed via PDO functions • See: http://www.php.net/pdo • <?phptry {   $dbh = new PDO(‘informix:dbname=stores', $user, $pass);} catch (PDOException $e) {   print "Error!: " . $e->getMessage() . "<br/>";   die();}?>

  35. Python and InformixDB • http://www.python.org/ • Version 2.5 – 19th September 2006 • Version 2.4.3 – March 2006. • InformixDB – under active development • Maintainer: Carsten Haese • Python DB-API 2.0 compliant • Requires Python 2.2 or better • Needs Informix ClientSDK • Version 2.3 – 1st October 2006

  36. Python and InformixDB import informixdb conn = informixdb.connect(”test”, ”someone”, ”somepw”) cur = conn.cursor() cur.execute(“create table test1(a int, b int)”) for i in range(1,25): cur.execute("insert into test1 values(?,?)", (i, i**2)) cur.execute("select * from test1") for row in cur: print "The square of %d is %d." % (row[0], row[1])

  37. Aubit 4GL – Open Source 4GL • 99% Informix™ 4GL Compatible • BODR=Business Oriented, Database Related • Task-focussed language • Embedded SQL for database access • High productivity, easy to learn • Licensed under GPL/LGPL • Includes 4GL-based Open Source software • For commercial and non-commercial applications

  38. Aubit 4GL – New to 4GL? MAIN MENU "Title for my test menu"COMMAND "Impress Me" "Do something to impress me“ HELP 126 CALL OpenMyWindow()COMMAND "Exit" "Exit this menu" HELP 127 EXIT MENUEND MENU END MAIN FUNCTION OpenMyWindow() OPEN WINDOW MyTestWindow AT 2,3 WITH FORM "FormForMyTestWindow" ATTRIBUTE(BORDER, WHITE) END FUNCTION Think about amount of code to achieve same functionality in 3GL!

  39. Aubit 4GL – Features • Database independent • ODBC, native, ESQL/C • Fully modular (plug-in) architecture • User interface independent • GUI and Curses modes • Platform independent • (POSIX, UNIX, Windows) • Easy to embed 3GL in 4GL • Embedded C code

  40. Aubit 4GL – Enhancements • Logical Reports • ASQL – dbaccess/isql replacement • Flexible key mapping • Print Screen functions • Fully integrated testing hooks (including key recording and replay for batch jobs) • Dynamic function calls (like perl ‘::’)

  41. Aubit 4GL – Web Sites • Web site http://aubit4gl.sourceforge.net • Bug Tracker http://www.aubit.com/mantis • Bulletin board http://www.aubit.com/phpBB • Commercial support http://www.aubit.com • Current version: • 0.59-22 dated 2006-09-04 • Pushing towards a 1.00 release • Release candidates 0.99-xx available October 2006

  42. SQLCMD • Originally called RDSQL in 1987. • Renamed SQLCMD in 1992. • Intended as an alternative to ‘isql’. • Before DB-Access was created. • Designed for use in shell scripts. • Exits with non-zero status on error. • Careful use of standard input, output, error. • Output layout independent of selected data. • Designed for interactive use. • Available from the IIUG Software Archive. • Version 80.00 – 2005-11-23

  43. SQSL – Structured Query Scripting Language • SQSL is a scripting language • Created by Marco Greco • Superset of SQL • Features aimed at scripting, reporting, and simple ETL • Lets a DBA perform daily activities as easily as possible

  44. SQSL – Structured Query Scripting Language • It has a low learning curve: • Language features should be familiar • To anyone with experience of: • SQL • SPL • Informix 4GL • Bourne shell • It includes flow-control operations • It has a curses-based full-screen mode • Like DB-Access • http://www.4glworks.com/sqsl.htm • Version 0.02 – September 2005

  45. Ruby • http://www.ruby-lang.org/ • Version 1.8.5 – 29th August 2006. • NEW – Informix support • http://rubyforge.org/projects/ruby-informix/ • Registered February 2006 • Version 0.2.0 released April 2006. • Produced byGerardo Santana Gómez Garrido • gerardo.santana@gmail.com • See also ‘Ruby on Rails’ • http://www.rubyonrails.com/

  46. IIUG Software Archive • http://www.iiug.org/software • Many useful utilities • Art Kagel’s ‘utils2_ak’ package • Generate UPDATE STATISTICS statements • DB-Copy • Stored Procedure Libraries • Example DataBlades • 4GL Code Generators

  47. http://www.ibm.com/software/data/informix http://www.iiug.org/software

  48. http://www.ibm.com/software/data/informix http://www.iiug.org/software

  49. Session I07 Open Source and Informix Dynamic Server Jonathan Leffler IBM Information Management jleffler@us.ibm.com

More Related