4. Common Questions about tools:

4.1 Informix-4GL

4.1.35 How can I compile the same 4gl program against multiple databases?

On 19th October 1999 batonnet@phase4.com.au (Bryan Tonnet) wrote:-

I have a requirement to run 4GL code against multiple databases within the same IDS 7.3 instance. This is a classic set of production, QA and test databases.

Basically, I want to use the same 7.2 4GL to compile and run across all these databases (not at the same time) with no changes to the 4GL code, which contains a lot of records LIKE tables and variables LIKE fields. I understand the uses of the DATABASE statement within and outside the MAIN function.

Lots of info in here, but it all seemed to assume that the database structures were the same, so that the compile time DATABASE statement would work, and then the dynamic DATABASE statement picks up the runtime database as required from the environment. For me, this won't work as the schemas of these databases are rarely going to be the same, and it certainly is not guaranteed at any point in time. In any case I have inherited .per files with explicit DATABASE statements which are not covered by the suggestions of the past.

Seeing as how I had pretty much made up my mind how I was going to tackle this, I became immediately nervous when the wizards of c.d.i had not mentioned my solution in about 4 years of postings. So, at the risk of being dumb (but that's better than wasting a few weeks of effort), what's wrong with this crude scheme.

Strip out any "top of module" DATABASE statements from .per or .4gl Set $DBNAME to suit your current operational mode. Then, at compile time:-

mv source-file source-file.$ext
echo $DBNAME > source-file
cat source-file.$ext >> source-file
fglpc (or form4gl) source-file
mv source-file.$ext source-file

OK, so fgldb will complain, but that's easily circumvented. Other than that, this would be easy to build into a Makefile or a stand apart script, and in rudimentary testing, seemed to do the job. I get the feeling I'm missing something simple, so I await your critisism.

4.1.36 How can I use a stored procedure from 4gl?

On 18th July 2001 NOahammSPAN@sanderson.net.au (Andrew Hamm) wrote:-

>> LET lc_MySP = "EXECUTE PROCEDURE proc_name(?, ?, ?)"
>> PREPARE Prep_MySp FROM lc_MySp
>> EXECUTE Prep_MySp USING parameter1, parameter2, parameter3

4.2 ESQL/C:

4.2.1 Why have Informix duplicated stleng, stcopy() etc in ESQL/C?

All the library functions that Informix is providing in ESQL/C like stleng, stcopy(), stcmpr, are all provided by the standard c library itself. They look the same as the ones strlen, strcpy etc. Are they the same?

On most platforms they are functionally the same, but they are usually implemented in assembler code by Informix, and may not be implemented in assembler by the OS. This may give a slight performance advantage to using the Informix versions of the functions. Some of them (byleng for example) do not have an analogous function in the standard C library.

4.2.2 How can I get a list of databases?

Jonathan Leffler reveals a C routine which is cunningly disguised as Appendix D.

4.2.3 Can I use ESQL with C++?

Yes, see APPENDIX N How do I compile ESQL/C code for use with C++?

4.2.4 How are prepared statement id's defined in ESQL/C?

There is a big difference between the versions of ESQL/C prior to 5.00 and those from 5.00 upwards.

In the pre-5.00 versions (4.1x, mainly, but also 4.00, 2.10.0x, etc), all cursors and prepared statements had fixed names which were private to the file in which the statement was prepared or the cursor declared. Two separate source files could declare the same names with impunity and no damage was done. You couldn't redeclare the name in the same file, so you couldn't declare a single cursor in two different ways -- this was not allowed:

	if (something == SOMEVALUE)
		$ DECLARE cursor_name CURSOR FOR <select1>;
		$ DECLARE cursor_name CURSOR FOR <select2>;

You got around this by selectively assigning the text of the statement and then preparing the string, and then declaring the cursor.

There were some other peculiarities. For example, the same structure was used for both the cursor and the statement, even though a declaration was emitted for both, just in case they were used independently. So a fussy compiler would warn about unused variables. And you could only free either the cursor or the statement, not both, mainly because of this shared variable implementation. The rule of thumb was to release the cursor where there was one, and to release the statement only when it was not a SELECT statement. Once a cursor was associated with a statement, there was no way to reuse the statement with a different cursor.

In the post-5.00 versions (including 5.00 itself, of course), this all changes. Cursor and statement names become strings, and the names are global -- as the instructor said. This means that if you declare cursorA in file1.ec, you can use it in file2.ec without any work being required. You simply say 'OPEN cursorA' and it all works like a charm. Of course, if file2.ec also had a cursorA in it (eg, a cursor called 'c', or 'c_select' which I used to use in generated code), then all of a sudden you have a very different situation. You can have code which worked in 4.1x no longer working in 5.00, as below. This code should compile, but is otherwise inexcusable on a number of other grounds. But it needed to be simple enough to understand and explain.


	char tabname[19];
	long tabid;

		SELECT TabID, TabName
			FROM SysTables
			WHERE TabID >= 100;

	EXEC SQL OPEN c_cursor;

	while (sqlca.sqlcode == 0)
		EXEC SQL FETCH c_cursor INTO :tabid, :tabname;

	EXEC SQL CLOSE c_cursor;
	EXEC SQL FREE c_cursor;


file2_function(char *p_tabid)
	static int prepared = 0;
	long  tabid = p_tabid;
	date  created;
	char  owner[9];

	if (prepared++ == 0)
		EXEC SQL PREPARE p_cursor FROM "SELECT owner, created FROM SysTables WHERE Tabid = ?";
		EXEC SQL DECLARE c_cursor FOR p_cursor;
	EXEC SQL OPEN c_cursor USING :tabid;
	EXEC SQL FETCH c_cursor INTO :owner, :created;
	EXEC SQL CLOSE c_cursor;

In 5.00, when file2_function() is called, it implicitly closes, frees, and redeclares c_cursor, does an open, fetch, close cycle, and returns. Unfortunately, this means that the next FETCH in file1_function() will fail because it is trying to fetch on a closed cursor. If it casually re-opens the cursor, it then runs into an error because the fetched data values are incompatible with the cursor declared in file2.ec. If file1_function() re-declares and re-opens c_cursor, then when it calls file2_function(), either the OPEN fails because too many parameters are supplied in the USING clause or the FETCH fails because of the type mismatch. Either way, the code is severely broken.

I have been using old-style names so that the code would compile under 4.1x too. However, you can also use strings for cursor and statement names:

char *string1 = "select * from systables where tabid >= 100";
EXEC SQL PREPARE "p_cursor" FROM :string1;
EXEC SQL DECLARE "c_cursor" CURSOR FOR "p_cursor";

And you can use string-valued variables too:

char *s_name = "p_cursor";
char *c_name = "c_cursor";
char *string1 = "select * from systables where tabid >= 100";
EXEC SQL PREPARE :s_name FROM :string1;

Just be very careful to make sure you track down all cursors and statements if you use these. It can get confusing if you are not careful.

Also note that you can have multiple DECLARE statements for a single cursor name now -- that is the cause of all the problems in the file1.ec/file2.ec example. However, you can even do all that in a single file under 5.00.

There is an option '-local' which provides mangled names to ensure that the names are different between files. This more or less works provided that your cursor names were short enough; if they were too long, then from 2 to 9 characters are dropped off the tail of the name (in 5.03 and later versions; I think one version of ESQL/C (possibly a pre-release) generated a name of more than 18 characters), and the previously distinct names may no longer be distinct. And if you use this mechanism, you cannot use:

		SELECT SomeColumn FROM SomeTable FOR UPDATE;
		"UPDATE SomeTable SET SomeColumn = ? WHERE CURRENT OF c_cursor";
	EXEC SQL OPEN c_cursor;
	EXEC SQL FETCH c_cursor INTO :variable;
	EXEC SQL EXECUTE p_update USING :variable;

This is because the declared cursor name is modified, but the WHERE CURRENT OF clause is not modified because it is inside a string. So, with this code, either the EXECUTE fails with unknown cursor name, or it executes an update using some other cursor altogether which may not even point to SomeTable. You'd probably get an error; in the worst case, it might work but simply update a completely different row from the one you thought it was updating.

>I have two prepared statements that have the same name, but they do two
>different selects.  They seem to be working properly, but I don't want
>any unexpected results down the road.

Since they do two different selects, presumably you are also declaring cursors; do the cursors have the same name? And are you using 5.00 and above?

I'm going to assume you have a post-5.00 version, and that you have two different cursor names. What is happening, sequentially, is:

	PREPARE p_stmt FROM "string1"
	DECLARE c_str1 FROM p_stmt
	PREPARE p_stmt FROM "string2"
	DECLARE c_str2 FROM p_stmt

In pre-5.00 ESQL/C, this would not compile in a single file, and wouldn't matter if spread across several files. In post-5.00, you can free a prepared statement once you have declared a cursor for it, without affecting the declared cursor. When p_stmt is prepared from string2, it releases the space from preparing string1 (which does not affect c_str1), and then allows you to declare c_str2 with a totally different select statement. This is all thoroughly legitimate.

One question you did not ask:

>Where does I4GL fit into all this?

There are two sets of versions with slightly different characteristics -- the 4.1x versions and the 6.0x versions. However, they also have a lot in common. Each version uses the nearest available ESQL/C version (4.12.UC1 for 4.1x I4GL where x >= 2, and 6.00.UE1 for 6.0x I4GL). So, the 6.0x I4GL code uses post-5.00 cursors, and therefore the rules on freeing statements and cursors separately apply. However, the program which converts I4GL code into ESQL/C code only understands the 4.1x ESQL/C rules (for both 4.1x and 6.0x I4GL), and therefore it enforces the same restrictions as 4.1x ESQL/C on the 6.0x I4GL code. That means you cannot have multiple DECLARE statements for a single cursor name in a single I4GL source file, nor can you have string-valued cursor names in your I4GL code.

However, you could run into the name-collision problem across files. The 6.0x code provides a cursor-name-mangling mechanism which is different from the ESQL/C mechanism, mainly because of perceived deficiencies in the ESQL/C mechanism. The 6.01 and later versions of I4GL also provide a function called CURSOR_NAME() which mangles the name of the cursor in the same way as the I4GL compiler did. There is a dummy version of this which returns the name unmangled in 4.13 and later versions of I4GL, so the same code can be used in both 4.1x and 6.0y where x >= 3 and y >= 1. There is also a horrifying tendency for the name mangling code to encode two distinct names the same, which is undesirable, but difficult to fix. If it was ever fixed, all code which uses CURSOR_NAME would have to be recompiled. I have previously posted (2nd August 1995) a list of pairs of 2-letter suffixes which always map to the same encoded value with the same prefix. For example: xyzaq & xyzba will always mangle to the same value for any value of xyz. There are 240 such pairs of letters, and my posting detailed them all. There is also a possibility of other, longer names also clashing after mangling, but I have no recorded instances of these at the moment.

Yours, Jonathan Leffler (johnl@informix.com)

Here is some more information from Jonathan detailing the cursor names which collide:-

Version 6.00 4GL (both c-code and p-code) uses 6.00 ESQL/C, and 6.00 ESQL/C provides only global cursor names. (The -local option to esql doesn't work reliably unless your cursor names are short enough: 9 is the maximum guaranteed to work; anything longer than 16 definitely won't work; anything in between may or may not work, depending on the inode number of the source file.) I4GL has always worked with cursor names local to the source file.

To try and maintain the previous semantics, we mangle the cursor names using an algorithm based on the inode number and the cursor name. All cursor and statement names therefore look like I01234568_12345678, where the digits are in hex. You can suppress this transformation by using the -globcurs option to either fglpc or c4gl.

The 6.0x versions of I4GL try to preserve the local cursor and statement name semantics of 4.1x ESQL/C, even though 6.0x ESQL/C does not provide such support. (Don't point out the '-localcurs' option to ESQL/C -- it doesn't work adequately.) To do so, the I4GL compiler hashes the cursor names using an arcane algorithm which is documented in the release notes.

Unfortunately, the algorithm is such that if two statement names are the same apart from the last two characters, and if the last two characters of the two names are one of the 240 pairs listed below, then the names will hash to the same value, leading to compilation problems. I attach the program used to generate this listing so you can reproduce it if you need to. Note that it does not test '_' or the digits, so the list is not 100% complete.

Advice: make cursor and statement names differ with the early parts of the names rather than the later parts of the name. Make the names differ by more than two trailing characters.

    DEFINE n1 CHAR(2)
    DEFINE n2 CHAR(2)
    DEFINE c1 CHAR(18)
    DEFINE c2 CHAR(18)

    FOR i = 0 TO (26 * 26 - 1)
        LET n1 = basic_name(i)
        LET c1 = CURSOR_NAME(n1)
        FOR j = i + 1 TO (26 * 26 - 1)
            LET n2 = basic_name(j)
            LET c2 = CURSOR_NAME(n2)
            IF c1 = c2 THEN
                DISPLAY n1, " & ", n2
            END IF
        END FOR


FUNCTION basic_name(i)

--  DEFINE c1 CHAR(1)
--  DEFINE c2 CHAR(1)
    DEFINE s CHAR(2)

--  LET j = (i / 26) + ORD("a")
--  LET k = (i MOD 26) + ORD("a")
--  LET c1 = ASCII(j)
--  LET c2 = ASCII(k)
--  LET s = c1, c2
    LET s = ASCII((i / 26) + ORD("a")), ASCII((i MOD 26) + ORD("a"))

    RETURN s


aq & ba  ar & bb  as & bc  at & bd  au & be  av & bf  aw & bg  ax & bh
ay & bi  az & bj  bq & ca  br & cb  bs & cc  bt & cd  bu & ce  bv & cf
bw & cg  bx & ch  by & ci  bz & cj  cq & da  cr & db  cs & dc  ct & dd
cu & de  cv & df  cw & dg  cx & dh  cy & di  cz & dj  dq & ea  dr & eb
ds & ec  dt & ed  du & ee  dv & ef  dw & eg  dx & eh  dy & ei  dz & ej
eq & fa  er & fb  es & fc  et & fd  eu & fe  ev & ff  ew & fg  ex & fh
ey & fi  ez & fj  fq & ga  fr & gb  fs & gc  ft & gd  fu & ge  fv & gf
fw & gg  fx & gh  fy & gi  fz & gj  gq & ha  gr & hb  gs & hc  gt & hd
gu & he  gv & hf  gw & hg  gx & hh  gy & hi  gz & hj  hq & ia  hr & ib
hs & ic  ht & id  hu & ie  hv & if  hw & ig  hx & ih  hy & ii  hz & ij
iq & ja  ir & jb  is & jc  it & jd  iu & je  iv & jf  iw & jg  ix & jh
iy & ji  iz & jj  jq & ka  jr & kb  js & kc  jt & kd  ju & ke  jv & kf
jw & kg  jx & kh  jy & ki  jz & kj  kq & la  kr & lb  ks & lc  kt & ld
ku & le  kv & lf  kw & lg  kx & lh  ky & li  kz & lj  lq & ma  lr & mb
ls & mc  lt & md  lu & me  lv & mf  lw & mg  lx & mh  ly & mi  lz & mj
mq & na  mr & nb  ms & nc  mt & nd  mu & ne  mv & nf  mw & ng  mx & nh
my & ni  mz & nj  nq & oa  nr & ob  ns & oc  nt & od  nu & oe  nv & of
nw & og  nx & oh  ny & oi  nz & oj  pq & qa  pr & qb  ps & qc  pt & qd
pu & qe  pv & qf  pw & qg  px & qh  py & qi  pz & qj  qq & ra  qr & rb
qs & rc  qt & rd  qu & re  qv & rf  qw & rg  qx & rh  qy & ri  qz & rj
rq & sa  rr & sb  rs & sc  rt & sd  ru & se  rv & sf  rw & sg  rx & sh
ry & si  rz & sj  sq & ta  sr & tb  ss & tc  st & td  su & te  sv & tf
sw & tg  sx & th  sy & ti  sz & tj  tq & ua  tr & ub  ts & uc  tt & ud
tu & ue  tv & uf  tw & ug  tx & uh  ty & ui  tz & uj  uq & va  ur & vb
us & vc  ut & vd  uu & ve  uv & vf  uw & vg  ux & vh  uy & vi  uz & vj
vq & wa  vr & wb  vs & wc  vt & wd  vu & we  vv & wf  vw & wg  vx & wh
vy & wi  vz & wj  wq & xa  wr & xb  ws & xc  wt & xd  wu & xe  wv & xf
ww & xg  wx & xh  wy & xi  wz & xj  xq & ya  xr & yb  xs & yc  xt & yd
xu & ye  xv & yf  xw & yg  xx & yh  xy & yi  xz & yj  yq & za  yr & zb
ys & zc  yt & zd  yu & ze  yv & zf  yw & zg  yx & zh  yy & zi  yz & zj

Both cursor names and statement names are affected.

4.2.5 How do I use ESQL/C in a makefile?

Jonathan Leffler (johnl@informix.com) writes:

Please note that just as you use CC (or $(CC) or ${CC}) to denote the C compiler, so too you should use a macro to denote the ESQL/C compiler. Also, the ESQL/C compiler script compiles .c to .o quite happily, and it provides the -I options which are in general necessary (this is what Cosmo (simond@informix.com) said). And 'rm -f' should not return an error, (so the '-' is unnecessary), and should be echoed in my view (possibly controversial, but if you agree, the '@' is unnecessary). You should really use:

ESQL = esql
RM   = rm -f


ec.o :
        $(ESQL) -c $(CFLAGS) $<
        $(RM) $*.c

Finally, if you want to guarantee that the .ec file will be used in preference to the .c file, then you have to rewrite the SUFFIXES rule more thoroughly. If you are lucky, your version of MAKE defines a macro SUFFIXES. You can then use the following and then your .ec file will be used in preference to the .c file, even if both exist:

.SUFFIXES:		# Clears current suffix list

If you aren't lucky enough to have $(SUFFIXES) defined for you, you can use the following command to see what the .SUFFIXES rule is, and you can capture that in your own SUFFIXES macro.

make -f /dev/null -p 2>/dev/null

4.2.6 How does the fetch buffer work?

On 31st Dec 1997 djw@smooth1.co.uk (David Williams) wrote:-

You can set an environment variable FET_BUFSIZE do determine the size of the internalbuffer used by ESQL/C when fetching records. But how does the affect concurrency and timing you ask? Well...

On 18th Dec 1997 dsamson@ndsisrael.com (David Samson) wrote:-

The fetch buffer is filled with a number of records at one shot. This is obviously a big performance booster. However, you must understand how this works with concurrency control. If you're using committed read, OnLine checks to make sure that the records are committed WHEN THE BUFFER IS FILLED. If, between filling the buffer and your application executing the fetch, the record gets deleted, you will still retrieve that record (because it fetches from that buffer, not the DB itself).

If that's not good enough, you can switch to "cursor stability". This makes sure that the record exists when you fetch it. There are 2 flavors to this:


We have a system which continually reads records at a extremely high rate (and then sends them up to a satellite). We need very high throughput here. The following are times to fetch 375,000 records on an HP-UX 10.20 workstation with 64Mb of RAM:

        Method                                  Time(Sec)
        Committed Read, No Transactions         143
        Repeatable Read, No Transactions        466     (226% longer)
        Repeatable Read, with Transactions      440     (208% longer)

4.2.7 Why do set-uid programs not work?

On 20th Apr 1998 jleffler@visa.com (Leffler, Jonathan) wrote:-

The reason why SUID doesn't work has been explained in the long distant past on this group, but we're talking about 3 years ago, or maybe more. Anyway, under 5.x and earlier Informix systems, the application forked and execed an engine process. The engine was SUID root, SGID informix, and this effectively overrode the SUID-ness of the application. The real UID is inherited by the engine, and the effective UID was root; the real GID is inherited, and the effective GID was informix. So, when the engine looked to see who it was working for, it only knew about the real UID and GID and was not aware that the application was ever running set-anything.

4.2.8 How can I generate unique cursor names?

On 13th Oct 1998 jleffler@informix.com (Leffler, Jonathan) wrote:-

To be running into this problem, you have to be using 5.00 or later ESQL/C. That's good because it is also possible to create a solution.

You can arrange for your code to use string variables to identify the cursors. Further, you could arrange for a 'unique cursor name generator' function to be used whenever you have to create a new cursor name. As a non-reentrant example:

const char *gen_cursor_name(void)
	static char namebuff[19];
	static int  counter = 0;
	sprintf(namebuff, "mohr_dbalib_%06d", counter++);
	return namebuff;

You can now use:

int some_dbalib_routine(...)
	-- Beware: do not fall for this trap!!!
	-- const char *p_name = gen_cursor_name();
	-- const char *c_name = gen_cursor_name();

	char p_name[19];
	char c_name[19];

	strcpy(p_name, gen_cursor_name());
	strcpy(c_name, gen_cursor_name());

	EXEC SQL PREPARE :p_name FROM "...whatever...";

	EXEC SQL OPEN :c_name USING ...;
	while (SQLCODE == 0)
		EXEC SQL FETCH :c_name INTO ...;
		if (SQLCODE != 0)
	EXEC SQL CLOSE :c_name;
	EXEC SQL FREE :c_name;
	EXEC SQL FREE :p_name;

4.3 ACE:

4.3.1 How do I call a custom C function from ACE?

You say CALL function(); you also create a custom ACE runner using cace. The instructions for this are in the 4.10 Informix-SQL Supplement, or in the 4.00 and earlier ESQL/C manuals. (Yes, there is a reason; it's complex.) You also declare the function in the DEFINE section of the report. See the example reports a_ex1.ace and a_ex2.ace in the $INFORMIXDIR/demo/sql directory. Likewise, see p_ex1.per and p_ex2.per for examples of functions in Perform.

4.3.2 How can I create unique output filenames?

Question: Anyone know how to pass a filename into an ACE report as an argument so that multiple users can generate reports from the SAME .arc file without overwriting what the other users have done?

I can pass the file name into the .arc with no problem (using the param[] stuff), but cannot get ACE to use it as an output file...

Answer: Don't use the ace output option to do this. Make the ace report write to stdout and then wrap the whole report in a shell, something like:

sacego -q $1 > $1.$$ 
echo $1.$$ 
then just: 
a=`acewrap.sh reportname` 
lp $a, pg $a, or whatever 
Of course you can get a lot more involved than that, but it's a starting point.

4.3.3 How can I set isolation levels in an ACE report?

On 7th Oct 1998 Kate_Tomchik@HomeDepot.COM (Kate Tomchik) wrote:-

Thanks for all the responses regarding setting isolation levels in Ace. Since so many of you asked for confirmation of this undocumented feature, here is the scoop:

The environment variable is SACEISOL and the valid values are:

DIRTY READ or dirty read COMMITTED READ or committed read CURSOR STABILITY or cursor stability REPEATABLE READ or repeatable read


If you use SACEISOL with SE, it is ignored. No error is returned.

4.3.4 How can I set isolation levels in Perform?

On 14th Oct 1998 jleffler@informix.com (Jonathan Leffler) wrote:-

In item 4.3.3 How can I set isolation levels in an ACE report?, you discuss ACE; the analogous SPERISOL variable can be used to set the isolation level for Perform.

4.4 WingZ 1.0:

4.4.1 How can I make DataLink not pop-up 'no rows found'-style dialog boxes

This is the most common problem with the aesthetics of DataLink. Sorry, I don't believe there's any fix. HST has commands to handle SQL errors. Upgrade to HST, if possible.

Adam Green (adamg@informix.com)

4.5 Hyperscript Tools:

Note that Informix have now sold this product. Contact information for the new owners can be found in 12.1 Hyperscript Tools.

4.5.1 How do I run a Hyperscript Worksheet directly from a Windows Icon?

We are trying to set up HyperScript Tools so that a user may click on an icon in Windows and be presented with a worksheet loaded with data from an Informix database, which they then may modify, report on etc.

Solution 1: Assuming each sheet already exists.

Drag the sheet icons from the FileMgr and drop them in the Program Mgr. They will all default to the HST icons but of course you can change that. The user can now click on any of these icons, open HST, and have the sheet on screen with the entire user interface. See your Windows manual if you have any questions on adding icons to the Program Mgr.

Put an "ON OPEN" event in the worksheets. Here you can have HST get the data when the sheet is opened automatically.

Solution 2:

Create a script that gets the data and puts it in a sheet. Use the icon info about. If you launch HST by clicking on the script icon, HST will launch & initialize then run the script.

Jim Van Riper (jvr@informix.com)

4.5.2 Which is better Datalens or built-in SQL?

I'd switch to the built-in SQL in HST instead of DataLink. I think you will find it MUCH faster, easier to use, far more flexible, and easier to maintain. DataLink is obsolete.

Jim Van Riper (jvr@informix.com)

4.6 Informix-4GL for Windows

4.6.1 How can I get a UK Pound sign as my MONEY code?

You do this by setting DBMONEY to ALT 0163 for some reason this prints a pound under Windows. The real pound sign on all the machines I have come across print a black box.

Mike Chatwood (mike.chatwood@almac.co.uk)

4.7 Informix-NewEra

4.7.1 What 4GL to NewEra conversion tools exist?

Informix have a product, which may or may not be unsupported freeware (it has been given away for free at usergroup meetings, but is also a part of a "Professional Services" conversion methodology). Try to get a straight answer out of your local Informix office. (And let me know what you get told)

Another company, "The Technology Group" have a similar product. For more information about TTG or the application generator you can contact them via:

Steve Sanderson
The Technology Group, Inc.
124 N. Summit Street, Suite 317 Toledo, Ohio 43604
Ph. (800) 837-0028, 419-242-9228
Fax 419-242-7537
E-Mail: ttgtoledo@aol.com

4.7.2 Arrgh: -4518 in NewEra too!

In NewEra we keep getting the "no more space for temporary string storage" error, although we obeyed the rules mentioned in the informix faq's appendix. (4.1.1 I keep getting -4518 errors! How can I fix this problem?) and (APPENDIX A Temporary String Space (TSS)). Are there any problems known with informix classes causing this error ?

Catalin Badea (cat@tecsys.com) answered:

You may want to check the usage of the concatenate operator (||). We had problems with it in functions being called repeatedly (with p-code, c-code was fine).

Try using ixStrings and their concat() member function instead.

4.7.3 How generate 4GL from .WIF without Window Painter?

Is there any way to generate .4gl and .4gh from a .wif without opening and saving the .wif in the window painter ?

Dennis J. Pimple (dennisp@informix.com) replied:

From DOS, with PATH including $INFORMIXDIR\bin, type: ix iwp -b filename.wif

4.7.4 What's the performance difference between base types and object types?

From: dwc@cix.compulink.co.uk ("David Chan")

Should you use base type (INTEGER, DECIMAL, etc) or the object type (ixInteger, ixDecimal, etc)? Which is faster?

I ran the following test and the results surprised me. The object version took .5 seconds and the base version took 15 seconds. I also tried mult, div, plus, etc. and the difference is not as impressive but it is still better. Here's the sample program:

include system "ixNum.4gh"

        variable anInt1 ixInteger(0)
        variable anInt2 integer = 0
        variable aCount integer

        display "start"
        sleep 3

        -- dummy loop to see cost of for loop
        display current hour to fraction(2)
        for aCount = 1 to 10000
        end for

        -- use object type
        display current hour to fraction(2)
        for aCount = 1 to 10000
                call anInt1.inc()
        end for

        -- use base type
        display current hour to fraction(2)
        for aCount = 1 to 10000
                let anInt2 = anInt2 +1
        end for

        display "finish ",current hour to fraction(2)

end main

4.7.5 Can't run New Era 2.0 applications under Win 95

Geoff Roach (geoffr@informix.com) suggests:

One of out SE's in Houston has done the following to make apps run on Win 95. There seem to be some problems with some of the networking software pieces that we are trying to resolve before certifying the 2.0 release on Win95 and WinNT. The 3.0 release will be native for both Win95 and WinNT.

I have successfully compiled NewEra applications under Windows95 after editing the $INFORMIXDIR\INCL\FGLRT.H file:

Remove the following lines:

#ifdef _WINDOWS
#define INITCODE __based(__segname("PRELOAD_CODE"))
#define INITDATA 

Then skip two lines and remove the following line:

#endif /* _WINDOWS */

The applications seem to run at the same speed as under Windows 3.1. My applications that I compiled under 3.1 run fine also.

Update: There is now apparently a version "2.11" available for a no-charge upgrade which allows 2.0 NE programs to run on Win95, and of course version 3.0 is due RSN.

4.7.6 How can I find NewEra -1363 errors?

From: concepts@interaccess.com (Kristopher Sarpolis) To track down those pesky -1363 errors, try the following:
  1. Edit your .mak file. Add /Zi to the CFLAGS line and /CO to the LINKFLAGS line to generate debug info. (Remember, these changes will be lost the next time you regen the make file in the CrapBuilder :) , I mean AppBuilder)
  2. Remove the lines in the .mak that the delete the .c and .ec files. You'll need these files later.
  3. Remove all objs from your program directory.
  4. Recompile.
  5. Go to lunch (optional).
  6. Bring up VC++ 1.5 and open the .mak file as an external makefile.
  7. Run the program from VC++.
When you normally would see the -1363 error, the VC++ debugger will stop and, if the error is in your source code, it will bring up the ec file and put you at the offending line of esqlc code. From there you can backtrack to the corresponding line of NewEra code.

4.7.7 What New Era Class Libraries are there?

There is the "New Era Windows Toolkit (NEWT)" at http://www.dchsoftware.co.uk/ "David C. Harrison" (dch@informix.com) writes:

A product called "The NewEra Windows Toolkit", it's free of charge (in binary form) and implements NewEra classes for most of the Win32 common controls plus about 20 other classes.

The controls you mention here (Tree and a list vith pictures in it) are implemented as the classes dchTreeView and dchListView.

4.8 Stored Procedure Language (SPL)?

4.8.1 What is SPL?

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 Press. ISBN: 0-13-206723-4

4.8.2 Advantages of SPL

4.8.3 Disadvantages of SPL

4.8.4 Can I access the SQLCA via a Stored Procedure?

Yes. This means that your Delphi/Visual Basic/Whatever program can also access the SQLCA structure by using a Stored Procedure like this:

     RETURN DBINFO('sqlca.sqlerrd1');

4.8.5 When should I use a SP rather than 4GL?

David "Mr. SPL" Berg's rule of thumb is that a stored procedure with 3 or more SQL statements within will be faster than running the 3 SQLs in the 4GL code. Any less than than and the SPL overhead defeats any performance benefits.

4.8.6 How do I convert Sybase Stored Procedures to Informix?

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.

4.8.7 When are Stored Procedures optimized?

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.

4.8.8 How do I generate random numbers in SPL?

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)

	LET seed = n;

	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);

4.8.9 How do return Julian dates in SPL?

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:

	RETURN (YEAR(d) * 1000) + (d - MDY(1, 1, YEAR(d))) + 1;

4.8.10 Can I create Stored Procedures in ISQL?

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.

4.8.11 How do I find the current database in SPL?

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.



        INTO s
        FROM SysMaster:'informix'.SysOpenDB
        WHERE ODB_SessionID = (SELECT DBINFO("sessionid")
                                FROM 'informix'.SysTables
                                WHERE TabID = 1)
		  AND ODB_IsCurrent = "Y";

    RETURN s;


4.8.12 How do write a substr function in SPL?

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
  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;                              

4.8.13 Why does CURRENT always return the same value in SPL?

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;

4.8.14 How do I use variable table/column names in SPL?

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:


The README link on that page describes what it does.

4.9 DBI::Informix (Perl)?

4.9.1 How do I unload to a file?

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");
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.


if you want to get rid of the spaces.

4.10 Java?

4.10.1 How can I connect without a password unsing JDBC?

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.*;
     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.

4.10.2 How can I connect to SE using JDBC?

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;" +

Note the space between the / and /full/path/to/login. 

This style is needed to access SE databases