Transcript IDUG / IIUG Europe Conference - October 2006, Vienna, Austria

I07
Open Source and Informix
Dynamic Server
Jonathan Leffler
IBM Information Management
Tuesday 3rd 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
2
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
3
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!
4
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.
5
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);
}
6
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
7
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.
8
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/
9
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/
10
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.
11
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;
12
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;
13
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 = ‘
14
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 });
15
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;
16
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
17
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;
18
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
19
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...
20
Loading ISQLTCL
• Load the ISQLTCL extension
• load isql.so
• Adds the command ‘sql’ to Tcl/Tk
• tclsh
• wish
21
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
22
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
23
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
24
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!
25
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/
26
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…
27
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
28
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
29
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>
30
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.
31
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
32
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
33
New Informix Driver
• Accessed via PDO functions
• See: http://www.php.net/pdo
• <?php
try {
$dbh = new PDO(‘informix:dbname=stores', $user, $pass);
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
?>
34
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
35
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])
36
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
37
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 MENU
END 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!
38
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
39
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 ‘::’)
40
Aubit 4GL – Web Sites
•
•
•
•
•
Web site
Bug Tracker
Bulletin board
Commercial support
Current version:
http://aubit4gl.sourceforge.net
http://www.aubit.com/mantis
http://www.aubit.com/phpBB
http://www.aubit.com
• 0.59-22 dated 2006-09-04
• Pushing towards a 1.00 release
• Release candidates 0.99-xx available October 2006
41
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
42
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
43
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
44
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 by Gerardo Santana Gómez Garrido
• [email protected]
• See also ‘Ruby on Rails’
• http://www.rubyonrails.com/
45
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
46
http://www.ibm.com/software/data/informix
http://www.iiug.org/software
47
http://www.ibm.com/software/data/informix
http://www.iiug.org/software
48
Session I07
Open Source and Informix Dynamic Server
Jonathan Leffler
IBM Information Management
[email protected]
49