Thanks to Jack Parker:
Stored Procedure Language could be descibed as a cut-down 4GL which lives inside the database engine. Essentially it is used to write functions essentially consisting of SQL code and extensions to SQL code. So you can do things like SELECT SPL(column) FROM TABLE. SPL became available with 5.0. Triggers with 5.1.
Books on the subject:
INFORMIX STORED PROCEDURE PROGRAMMING by Michael Gonzales.
Informix Press. ISBN: 0-13-206723-4
CREATE PROCEDURE GetSerialNumber() RETURNING integer;
RETURN DBINFO('sqlca.sqlerrd1');
END PROCEDURE
verdy_p@msn.com (Philippe Verdy) writes:
Such a converter exist to convert Sybase Transac-SQL to Informix SPL. Ask your local informix representative that will provide you the tools required. I have worked on these tools and successfully converted up to 30+ACU- of our Sybase SQL procedures without need to rewrite them, at least for the first release.
Then it required many enhancements to the converter, and I have completely revamped it to satisfy our needs for up to 95+ACU- of our procedures (on 600 and more Sybase procedures, only 12 had to be patched manually, mainly due to semantics difference of some Sybase constructs like ambiguous +- operator for string concatenation or numeric addition, concatenation of NULL-possible values).
Then I have written the support procedures that allow unlimited use of Sybase constructs in Informix SQL language: explicit conversions, handling substring(), isnull(), datetime conversion styles. Because I did it with the aggreement of Informix I am not authorized to communicate it directly, but may be my work has been trnasmitted by my local Informix representative to the Informix world as a successfull conversion.
However expect at least a step in optimizing and controlling the results of the conversion. The Informix converter now is much better than ever, cleaner, uses non ambiguous and complete LALR grammar for Sybase Transac SQL with no shift/reduce and no reduce/reduce ambiguity, smarter when it generates the effective SQL code: automatic handling of SET ROWCOUNT constructs, detection of multiple result sets, support for compound-join updates and deletes, near complete support for most of Sybase cursors declared in the procedure, ...
Beware during the conversion to Sybase recursive procedures that use temporary tables (Informix temporary tables are local to the session, not to the procedure call instance like Sybase). Such procedures, often used to handle data organised as trees, will have to be rewritten using non-recursive algorithms using loops and may be an auxiliary work table to simulate the recursion call stack. I have experimented that rewriting those Sybase procedures without recursion also gained (much) in performance in Sybase while gaining also in portability to Informix and Oracle.
Beware of Sybase ISQL scripts that manage the data dictionary. Most of such scripts use non portable SQL like calling system stored procedures specific to Sybase, creating storage devices, and so on... Use a case tool like AMC Designor to manage this conversion.
On 4th Dec 1997 ericr@informix.com (Eric Ruhnke) wrote:-
From "Incorporating Stored Procedures and Triggers into Your Informix Databases Training Manual" Version 01-95 Page 6-128:
When a stored procedure is created all optimization will be attempted at that time. If the tables cannot be examined at compile time (they may not exist or may not be available), the create procedure will not fail. The SQL in this case will be optimized the first time the stored procedure is executed, and the query plan will be stored for use by other processes.
An SQL statement will also be optimized at execution time if any DDL statement (e.g. alter table, drop index, create index) has been run that might alter the query plan. Altering a table which is linked to another table via a referential constraint (in either direction) will cause re-optimization of procedures which operate on the referenced table. The dependency list is used to track which changes would cause re-optimization.
If "update statistics for table" is run for any table involved in the query, the SQL statement will be re-optimized the next time the stored procedure is executed.
On 8th Dec 1997 johnl@informix.com (Jonathan Leffler) wrote:-
Since that's the case, we need a proper random number generator. On the assumption that the C Standard committee knew roughly what they were doing when they specified a random number generator in the ISO 9899:1990 standard, here's an implementation of that generator in two functions, sp_setseed(0 and sp_random(). The range of returned values is 0..32767. Note the comment about MOD. That was in a 9.12.UC2 IUS system.
-- @(#)$Id: ifaq04c.htm,v 1.11 1999/01/09 14:30:06 root Exp $ -- -- Simple emulation of SRAND and RAND in SPL -- Using random number generator suggested by C standard (ISO 9899:1990)
CREATE PROCEDURE sp_setseed(n INTEGER)
DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1;
LET seed = n;
END PROCEDURE;
CREATE PROCEDURE sp_random() RETURNING INTEGER;
DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1;
DEFINE d DECIMAL(20,0);
LET d = (seed * 1103515245) + 12345;
-- MOD function does not handle 20-digit values... Dammit!!
LET seed = d - 4294967296 * TRUNC(d / 4294967296);
RETURN MOD(TRUNC(seed / 65536), 32768);
END PROCEDURE;
On 16th Dec 1997 johnl@informix.com (Jonathan Leffler) wrote:-
It is not very hard to devise a stored procedure which will do the job:
CREATE PROCEDURE julian_date(d DATE) RETURNING INTEGER;
RETURN (YEAR(d) * 1000) + (d - MDY(1, 1, YEAR(d))) + 1;
END PROCEDURE;
On 15th Dec 1997 johnl@informix.com (Jonathan Leffler) wrote:-
ISQL thinks statements are terminated by semi-colons. CREATE PROCEDURE statements have semi-colons in the middle of them, but ISQL doesn't know about this, so it sends the CREATE PROCEDURE statement up to the first semi-colon to the database. Your empty SP therefore worked -- anything meaningful doesn't.
As already advised, use DB-Access.
On 19th Oct 1998 jleffler@informix.com (Jonathan Leffler) wrote:-
How about:
# "@(#)$Id: ifaq04c.htm,v 1.11 1999/01/09 14:30:06 root Exp $"
#
# Stored procedure CURRENT_DATABASE written by Jonatha Leffler
# (johnl@informix.com), based on a tip from John Lysell
# (jlysell@informix.com), with corrigenda from Raj Muralidharan
# (rmurali@informix.com) and Tue Hejlskov Larsen (tue@informix.com).
#
# If this stored procedure is created (by user informix to get
# the necessary permissions) in the SysMaster database, then any
# user in any database can run it (or call it in a SELECT
# statement) and get the name of the current database. You can
# drop the owner part if you are not using a MODE ANSI database:
#
# EXECUTE PROCEDURE sysmaster:'informix'.current_database()
# EXECUTE PROCEDURE sysmaster:current_database()
#
# The size of the return parameter probably only needs to be 18.
CREATE PROCEDURE current_database() RETURNING VARCHAR(64);
DEFINE s VARCHAR(64);
SELECT ODB_DBName
INTO s
FROM SysMaster:'informix'.SysOpenDB
WHERE ODB_SessionID = (SELECT DBINFO("sessionid")
FROM 'informix'.SysTables
WHERE TabID = 1)
AND ODB_IsCurrent = "Y";
RETURN s;
END PROCEDURE;
On 11th May 1998 VFraenkle@cs-controlling.de (Volker Fraenkle) wrote:-
I knew, that there will be a substr function in INFORMIX 7.30. But my Problem is, I need it for 7.22, 7.23.
With help of John H. Frantz (he developed base part of code), I can show you a fine working substr SPL:
create procedure substr (sText varchar(255), iFPos smallint, iLen
smallint)
returning varchar(255);
define i smallint;
define sReturn varchar(255);
if iFPos > 1 then
for i = 1 to iFPos - 1 step 1
let sText = sText[2,255];
end for;
end if;
let sReturn = sText[1,1];
if iLen > 1 then
let sText = sText[2,255];
for i = 2 to iLen step 1
let sReturn = sReturn || sText[1,1];
let sText = sText[2,255];
end for;
end if;
return sReturn;
end procedure;
On 1st Feb 1999 kagel@bloomberg.net (Art S. Kagel) wrote:-
It is a requirement of ANSI SQL that a stored procedure return a constant value for CURRENT throughout its lifetime. This is the way that Informix implemented CURRENT in SPL to meet the standard.
On 9th Mar 2001 Leonids.Voroncovs@dati.lv (Leonid Belov) wrote:-
SELECT DBINFO( 'UTC_TO_DATETIME', sh_curtime ) FROM sysmaster:sysshmvals;
On 3rd April 2000 paul.brown@informix.com (Paul Brown) wrote:-
Well, I wrote an extension that works in IDS.2000/IIF.2000 to let you do this. If you want to take advantage of it, you're going to have to upgrade, I'm afraid.
It's available from:
http://www.iiug.org/software/index_ORDBMS.htmlThe README link on that page describes what it does.
On 2nd March 2001 jleffler@informix.com (Jonathan Leffler) wrote:-
No, you can't declare cursors in SPL. You use the SELECT statement directly in the FOREACH clause, and you give it a name if you need to use WHERE CURRENT OF (see the manual), and giving the cursor a name implies the FOR UPDATE qualifier which you cannot write explicitly as part of the SELECT statement.
create procedure t96()
define l_name char(20);
foreach c_update for select lname into l_name from customer {for update}
let l_name = 'X' || TRIM(l_name);
update customer set lname = l_name where current of c_update;
end foreach;
end procedure;
On 14th August 2000 stes@pandora.be (David Stes) wrote:-
You can write an unload format file as follows :-
$sth=$dbh->prepare("select * from mytable");
$sth->execute();
$"='|';
while (($ref=$sth->fetch)) {
print "@{$ref}|\n";
}
I believe that UNLOAD is a keyword of the dbaccess or sqlcmd or dbunload tools, not of the server, so you can't send unload to the engine.
Note that the above is unload format (note the '|' before the newline).
Also you'll notice the trailing spaces for char() fields ...
There is a DBI option "ChopBlanks" for that.
$sth->{ChopBlanks}=1;
if you want to get rid of the spaces.
On 23rd January 2001 SbPgAdanaM@hotmail.com (B. Dana) wrote:-
Here is some quick perl with DBI-
#!/usr/bin/perl -w
#
# Don't forget to set INFORMIXDIR and INFORMIXSERVER
use DBI;
$dbh = DBI->connect("DBI:Informix:sysmaster");
$sth = $dbh->prepare(q%SELECT unique systabnames.tabname, nrows,
dbinfo("DBSPACE", pe_partnum) dbspace, lockreqs, lockwts, deadlks, lktouts,
isreads, iswrites, isrewrites, isdeletes, bufreads, bufwrites, seqscans,
pagreads, pagwrites
FROM sysptnext, outer systabnames, outer sysptprof, sysptnhdr
WHERE pe_partnum = systabnames.partnum
AND pe_partnum = sysptprof.partnum
AND pe_partnum = sysptnhdr.partnum
ORDER BY tabname
%);
$sth->execute;
$ref = $sth->fetchall_arrayref();
print " tabname, nrows, dbspace, lockreqs, lockwts, deadlks, lktouts,
isreads, iswrites, isrewrites, isdeletes, bufreads, bufwrites, seqscans,
pagreads, pagwrites \n";
for $row (@$ref)
{
print " $$row[0], $$row[1], $$row[2], $$row[3], $$row[4], $$row[5],
$$row[6], $$row[7], $$row[8], $$row[9], $$row[10], $$row[11], $$row[12],
$$row[13], $$row[14], $$row[15] \n";
}
$dbh->disconnect;
On 24th August 2000 Sujit.Pal@bankofamerica.com (Sujit.Pal) wrote:-
I saw a posting here some days back asking if one could get away with not specifying the user name and password in the JDBC URL, since he wanted the user to log in as himself rather than with some canned URL.
I just had a chance to try this out, and it appears that one can...heres the code I used if you are still around reading this newsgroup....
1 import java.sql.*;
2
3 public class Test
4 {
5 public static void main (String[] argv)
6 {
7 try
8 {
9 Class.forName ("com.informix.jdbc.IfxDriver");
10 }
11 catch (ClassNotFoundException cnfe)
12 {
13 System.out.println ("Class Not Found: " +
14 cnfe.getMessage ());
15 }
16 String jdbc_url = "jdbc:informix-sqli://host:1234/dbname:" +
17 "informixserver=server_name";
18 System.out.println (jdbc_url);
19 try
20 {
21 Connection conn = DriverManager.getConnection (jdbc_url);
22 PreparedStatement ps = conn.prepareStatement (
23 "SELECT * FROM systables");
24 ResultSet rs = ps.executeQuery ();
25 while (rs.next ())
26 {
27 System.out.println (rs.getString (1));
28 }
29 rs.close ();
30 ps.close ();
31 conn.close ();
32 }
33 catch (SQLException sqle)
34 {
35 System.out.println ("SQL Exception: " +
36 sqle.getMessage ());
37 }
38 }
39 }
This returns a list of tables as one would expect.
Now if you wanted to log who was doing what, then you would probably need to wrap a call to getpwent() in a JNI call and use that. I have done that too, so if you want help mail me directly and I will send the code.
On 24th August 2000 Sujit.Pal@bankofamerica.com (Sujit.Pal) wrote:-
Your URL needs to be specified like so...
Connection conn = DriverManager.getConnection
("jdbc:informix-sqli://somehost:1536:/ /full/path/to/login" +
"INFORMIXSERVER=server_se;" +
"user=vwfecom;" +
"password=testpass;"
Note the space between the / and /full/path/to/login.
This style is needed to access SE databases
On 7th February 2014 satyan@us.ibm.com (Sathyanesh Krishnan) wrote:-
The Informix LIB C++ library has no support for BIGINT/BIGSERIAL. The BIGINT/BIGSERIAL are relatively new data type added to IDS as an alternative to INT8/SERIAL8. By the time this type has introduced with IDS the LIB C++ library has gone in the maintenance only mode and now it got deprecated as well (though LIB C++ continue to ship with CSDK).