1 / 49

Connecting to IDS The Open Source Way

Informix User Forum 2005 Moving Forward With Informix. Connecting to IDS The Open Source Way. Jonathan Leffler Sr. Technical Staff Member/IBM. Atlanta, Georgia December 8-9, 2005. Agenda. Open Source Connecting to IDS

vince
Download Presentation

Connecting to IDS The Open Source Way

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. Informix User Forum 2005Moving Forward With Informix Connecting to IDSThe Open Source Way Jonathan Leffler Sr. Technical Staff Member/IBM Atlanta, Georgia December 8-9, 2005

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

  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 Connecting to IDS the Open Source Way

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

  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. Connecting to IDS the Open Source Way

  6. ESQL/C • Preprocessor which 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); } Connecting to IDS the Open Source Way

  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 Connecting to IDS the Open Source Way

  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. Connecting to IDS the Open Source Way

  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 (2+ years so far) • Current stable version: • 5.8.7 — June 2005 • Obtain via CPAN • Comprehensive Perl Archive Network • http://www.cpan.org/ Connecting to IDS the Open Source Way

  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 Connecting to IDS the Open Source Way

  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.49, November 2005. • Requires Perl 5.6.1 or later. • DBD::Informix – version 2005.02, July 2005. Connecting to IDS the Open Source Way

  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; Connecting to IDS the Open Source Way

  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; Connecting to IDS the Open Source Way

  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 = ‘ Connecting to IDS the Open Source Way

  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 }); Connecting to IDS the Open Source Way

  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; Connecting to IDS the Open Source Way

  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 Connecting to IDS the Open Source Way

  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; Connecting to IDS the Open Source Way

  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.12 – December 2005. • isqltcl – Informix SQL access via Tcl. • Available at http://isqltcl.sourceforge.net/ • Version 5.0 – released February 2002. • Builds into dynamically loadable shared library Connecting to IDS the Open Source Way

  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... Connecting to IDS the Open Source Way

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

  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 Connecting to IDS the Open Source Way

  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 Connecting to IDS the Open Source Way

  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 Connecting to IDS the Open Source Way

  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! Connecting to IDS the Open Source Way

  26. What is PHP? • Hypertext Processor • Was once ‘Personal Home Page’ • Version 4.4.1 released October 2005 • Version 5.0.5 released September 2005 • Version 5.1.1 released November 2005 • An HTML scripting language • Server-side • Cross-platform • Embedded in HTML documents • Extensible Connecting to IDS the Open Source Way

  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/ Connecting to IDS the Open Source Way

  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 Connecting to IDS the Open Source Way

  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 (beta version 0.2.1) • http://pecl.php.net/package/PDO_INFORMIX Connecting to IDS the Open Source Way

  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> Connecting to IDS the Open Source Way

  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. Connecting to IDS the Open Source Way

  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 Connecting to IDS the Open Source Way

  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 Connecting to IDS the Open Source Way

  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();}?> Connecting to IDS the Open Source Way

  35. Python and InformixDB* • http://www.python.org/ • Version 2.4 – November 2004. • InformixDB – under active development • Maintainer: Carsten Haese • See Carsten’s presentation on CD • Python DB-API 2.0 compliant • Requires Python 2.2 or better • Needs Informix ClientSDK Connecting to IDS the Open Source Way

  36. Python and InformixDB import informixdb conn = informixdb.connect(”test”, ”informix”, ”pw”) 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]) Connecting to IDS the Open Source Way

  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 Connecting to IDS the Open Source Way

  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! Connecting to IDS the Open Source Way

  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 Connecting to IDS the Open Source Way

  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 ‘::’) Connecting to IDS the Open Source Way

  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.50-2 dated 2005-06-22 Connecting to IDS the Open Source Way

  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. Connecting to IDS the Open Source Way

  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 Connecting to IDS the Open Source Way

  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 or Bourne shell • It includes flow-control operations • It has a curses-based full-screen mode • Like DB-Access • http://www.4glworks.com/sqsl.htm Connecting to IDS the Open Source Way

  45. Ruby • http://www.ruby-lang.org/ • Version 1.8.3 – September 2005. • No known Informix support • Lots of different database support packages. • No unifying concept like DBI or PDO. • See also ‘Ruby on Rails’ • http://www.rubyonrails.com/ Connecting to IDS the Open Source Way

  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 Connecting to IDS the Open Source Way

  47. http://www.ibm.com/software/data/informix http://www.iiug.org/software Connecting to IDS the Open Source Way

  48. http://www.ibm.com/software/data/informix http://www.iiug.org/software Connecting to IDS the Open Source Way

  49. Informix User Forum 2005Moving Forward With Informix Connecting to IDSThe Open Source Way Jonathan Leffler jleffler@us.ibm.com Atlanta, Georgia December 8-9, 2005

More Related