Transcript Jonathan Leffler - Washington Area Informix User Group

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
1
Agenda










Open Source
Connecting to IDS
Perl, DBI, and DBD::Informix *
Tcl/Tk and isqltcl
PHP
Aubit 4GL
SQLCMD *
SQSL
Python *
Ruby
9th December 2005
Connecting to IDS the Open Source Way
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
9th December 2005
Connecting to IDS the Open Source Way
3
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!
9th December 2005
Connecting to IDS the Open Source Way
4
Informix Database
Connectivity

ESQL/C

ODBC

JDBC


– The original connectivity.
– Standardized in SQL by ISO/IEC 9075:1992
– Originally defined by Microsoft.
– Standardized (as CLI) by ISO/IEC 9075-3:1996.
– Java analogue of ODBC.
– Standardized by Sun.
All of these are proprietary.
But can be used with Open Source software.
9th December 2005
Connecting to IDS the Open Source Way
5
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);
}
9th December 2005
Connecting to IDS the Open Source Way
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
9th December 2005
Connecting to IDS the Open Source Way
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.
9th December 2005
Connecting to IDS the Open Source Way
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 (2+ years so far)

Current stable version:
– 5.8.7

— June 2005
Obtain via CPAN
– Comprehensive Perl Archive Network
– http://www.cpan.org/
9th December 2005
Connecting to IDS the Open Source Way
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
9th December 2005
Connecting to IDS the Open Source Way
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.49, November 2005.
– Requires Perl 5.6.1 or later.
 DBD::Informix
– version 2005.02, July 2005.
9th December 2005
Connecting to IDS the Open Source Way
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;
9th December 2005
Connecting to IDS the Open Source Way
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
– $sth->execute();
 Statement
executed
handles can be released
– Implicitly – statement handle goes out of scope
– Explicitly – undef $sth;
9th December 2005
Connecting to IDS the Open Source Way
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 = ‘
9th December 2005
Connecting to IDS the Open Source Way
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 });
9th December 2005
Connecting to IDS the Open Source Way
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;
9th December 2005
Connecting to IDS the Open Source Way
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

9th December 2005
Connecting to IDS the Open Source Way
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;
9th December 2005
Connecting to IDS the Open Source Way
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.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
9th December 2005
Connecting to IDS the Open Source Way
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...
9th December 2005
Connecting to IDS the Open Source Way
20
Loading ISQLTCL

Load the ISQLTCL extension
– load isql.so

Adds the command ‘sql’ to Tcl/Tk
– tclsh
– wish
9th December 2005
Connecting to IDS the Open Source Way
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]


9th December 2005
Sets the specified connection
Connecting to IDS the Open Source Way
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
9th December 2005
Connecting to IDS the Open Source Way
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
9th December 2005
Connecting to IDS the Open Source Way
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!
9th December 2005
Connecting to IDS the Open Source Way
25
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
9th December 2005
Connecting to IDS the Open Source Way
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/
9th December 2005
Connecting to IDS the Open Source Way
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
9th December 2005
Connecting to IDS the Open Source Way
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 (beta version 0.2.1)
 http://pecl.php.net/package/PDO_INFORMIX
9th December 2005
Connecting to IDS the Open Source Way
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>
9th December 2005
Connecting to IDS the Open Source Way
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.
9th December 2005
Connecting to IDS the Open Source Way
31
Old Informix Driver

Connection management

Basic Operations
– ifx_connect
– ifx_pconnect
– ifx_close
–
–
–
–
–
ifx_prepare
ifx_query
ifx_fetch_row
ifx_do
ifx_free_result
9th December 2005
Connecting to IDS the Open Source Way
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
9th December 2005
Connecting to IDS the Open Source Way
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();
}
?>
9th December 2005
Connecting to IDS the Open Source Way
34
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
9th December 2005
Connecting to IDS the Open Source Way
35
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])
9th December 2005
Connecting to IDS the Open Source Way
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
9th December 2005
Connecting to IDS the Open Source Way
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!
9th December 2005
Connecting to IDS the Open Source Way
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
9th December 2005
Connecting to IDS the Open Source Way
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 ‘::’)
9th December 2005
Connecting to IDS the Open Source Way
40
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
9th December 2005
Connecting to IDS the Open Source Way
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.
9th December 2005
Connecting to IDS the Open Source Way
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
9th December 2005
Connecting to IDS the Open Source Way
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 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
9th December 2005
Connecting to IDS the Open Source Way
44
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/
9th December 2005
Connecting to IDS the Open Source Way
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
9th December 2005
Connecting to IDS the Open Source Way
46
http://www.ibm.com/software/data/informix
http://www.iiug.org/software
9th December 2005
Connecting to IDS the Open Source Way
47
http://www.ibm.com/software/data/informix
http://www.iiug.org/software
9th December 2005
Connecting to IDS the Open Source Way
48
Informix User Forum 2005
Moving Forward With Informix
Connecting to IDS
The Open Source Way
Jonathan Leffler
[email protected]
Atlanta, Georgia
December 8-9, 2005
49