Skip to main content


Showing posts from 2008

4GL's virtues (Part 2) Intergration with SQL

4GL makes code that would be difficult to write purely in SQL easy ,
plus it adds extra capabilities that would not be possible.

The following is a select loop that retrieves the result set
of a SQL query row by row and then manipulates it inside the loop.
Inside the loop you are free to use more SQL queries or 4GL code or mix them both!

For example, you can test conditions using if..then..else statements, assign values to host variables, load a tablefield or update tables in the database!

select h_c_ylik = c_ylik,
h_no_seir = no_seir,
h_ps = ps,
h_sxolia = sxolia
from mlax
where end_axr='Ο'
if h_no_seir = '' then
update mlax m
set ps = ps + :h_ps
where end_axr = '' and
no_seir = '' and
c_ylik = :h_c_ylik;
inquire_ingres (h_rowcount = rowcount,
if h_errorno!=0 then
callframe err_handler;
if h_rowcount = 0 …

4GL's virtues (Part 1) Extensibility with C

One of 4GL's virtues is it's extensibility with C. You can call custom C programs from within 4GL code and pass values between them byval or byref.
Is there something that cannot be done by using 4GL? you need to interact with the OS? do you want do to something more advanced? Call C to the rescue.

A trivial example is generating random numbers, ie random - generic serial numbers. There is a session directive supported by Ingres that sets the seed so it makes it easy to fill a tablefield with random numbers (a part of a larger code sample follows) :

set random_seed

while rowno <= quantity do

loadtable cylki (sn = 'GE'+ varchar(random()));

rowno = rowno + 1;


Which loads a tablefield with as many SN's as the quantity variable directs.

But the set random_seed directive and the random function are supported on Ingres 2.6 onwards.What to do with older Ingres version? Replace functionality by calling a C program.

So the 4GL code now becomes :
while …

Do not trust the manual?

When deploying the query resource limiting project across
a variety of OS's and Ingres's versions a bug(?)/diversity occurred relating to the 'seterr' procedure.
In all environments the compilation succeeded but in some there was a linking error :
Building runnable image of gryl00010aqep . . .
seterr.obj: In function `seterr':
undefined reference to `iiseterr'
collect2: ld returned 1 exit status
E_AB0020 Link failed
The link failed with status '69891'. This could mean either that the
linker could not be run or that it ran and returned an error.

E_AI0002 The application image was NOT built.
An earlier error prevents the application image from being built.
Correct the cause of the earlier error and then run imageapp again.
The error occured using the following specifications:
Ingres II 9.2.0 (int.lnx/118)NPTL
openSUSE 11.0 (i586)

Ingres II 9.2.0 (int.lnx/118)NPTL
CentOS release 5.2 (Final)

Ingres II 2.0/9808 (usl.us5/00)
5 7.1.1 i386 x86at SCO UNIX_SV…

Ingres client side debugging tools (printqry,printtrace)

Very useful debugging tools in the form of environmental variables that can make the developer's life much easier.
They can be set inside the user's profile for persistence or in a shell prompt for temporary operation.
The former is more useful to the developer since he can watch the code running behind his forms and the latter for help-desk operation (when a user calls and complains that something went wrong and you can replicate his actions remotely, activate the directives so you can check in what part of the code the problem arose)

II_EMBED_SET='printqry'; export II_EMBED_SET
produces a file called 'iiprtqry.log' which contains info such as :
Query text:
define query (yl0201p)
is select end_p as end_p, end_par as end
_par, end_or as end_or, end_ak as end_ak,
end_ekt as end_ekt,
d_ekd as d_ekd, c_pr as c_pr
from hyl
where c_en= $0='Υ04'
and last_year= $1=? and last_aa= $2=10
Query Send Time: Fri Oct 10 13:12:05 2008
Query Response Time: Fri Oct 10 13:12…

Ingres resource limiting : Part5 (Screenshots/Log table)

Actual screenshots from operation in action :

Trapped Form

Exception caught
(Admittedly scary message for the user!Not exactly what I was taught once upon a time in the HCI class!!)

Action cancelled

The log table structure :
help qep_abf_log

Name: qep_abf_log
Owner: psnouser
Created: 24/06/2008 09:05:56
Type: user table
Version: II2.5

Column Information:
Column Name Type Length Nulls Defaults Seq
application char 10 no no
dbuser char 10 no no
terminal char 60 no no
entrydate date no no
querytext char 200 no no
errortext char 200 no no

An entry in the table (executing an insert into table (values) select from table1 where...) :

Perl, Win32 API and COM automation

Lots of people (me amongst them) use Perl modules on Windows that interface with the Win32 API and the most usual problem is with Unicode since some modules call the Ansi functions and not their Unicode equivalent counterparts.
Not only this, but the actual job that they are after can be achieved much easier by using facilities readily provided and embedded within Windows itself.

Specifically the COM automation is a great tool for performing myriads of activities, be it monitoring all aspects of the system using WMI, monitoring an Active directory environment using ADSI, working with files (most Unicode related problems start here) with Filesystemobject or fiddling with the network and DCOM using WSH.
The automation abstracts the API , makes work easier and is more tolerant to errors in contrast to their API counterparts. All of that can be used through the Win32::OLE module.

I'm not saying not to use modules that interface with the API, there are many cool ones,and specialized tasks…

Ingres resource limiting : Part4 (Final Thoughts)

ABF is not used widely today as it is considered legacy, but with the advent of OpenRoad these techniques can be applied to modern day applications, as only minor modifications of the ABF/4GL code are required for transiting to the OpenRoad environment.

The rationale behind this project was to use the QEP or the ingres resource limiter as means of making decisions on the fly; whether or not to execute a query based on the results fed back by the optimizer.

Usually a QEP is used for identifying problematic queries using procedures as described in the very good article
A Procedure to Identify and Fix Long running Queries
which requires monitoring the system and analyzing QEP's offline but here I am trying a proactive approach.This could be useful for identifying and not allowing heavy querys to run during peak hours on an already loaded server.
The platform used for both testing and production was Ingres II 2.6 on SCO Unix

The error handling procedures as far 4GL/ABF goes :
The ESQL err…

Ingres resource limiting : Part3 (inquire_ingres)

The other option left was to capture the error from inside the 4GL code by checking the inquire_ingres error number after each sql statement (which does already,but I had to tweak it to also check for the specific error) and act upon it.

Since all code is grouped in frskeys (meaning that the user must press a key for the code to be activated) the whole error handling had to be shifted inside the frskey code.

Inside the frskey code there can be multiple sql statements and each one can trigger the exception.It might not be the first or second statement but it could be the 5th statement in the chain of execution. Furthermore the 'set maxio' directive is the first statement to be executed when the key is pressed,and must reset back to 'set nomaxio' on exiting or one of the conditions described later are met.

So every sql statement must be checked and the following was added after each one:
inquire_ingres (h_rowcount=rowcount,h_errorno=errorno,h_querytext=querytext,h_errorte…

Ingres resource limiting : Part2 (Ingres resource limiter 'maxio')

There was a suggestion to use the resource limiter (set maxio) as to capture and handle the exception thrown when the threshold is reached.

That was a great suggestion but soon discovered several limitations as far as ABF-4GL goes (reports cannot use it) :
In my topframe I am setting the maxio to a threshold value by using the 'set maxio' directive and I programm my errorhandler using iiseterr() to trap the error issued when that threshold is reached.
But,as the manual states, 'Your error-handling routine must not issue any database statements in the same session in which the error occurred. If it is necessary to issue database statements in an error handler, open or switch to another session' OR 'Do not issue any
query statements from inside your error handler routine because the application occasionally "hangs" or returns runtime errors.' OR 'User code in the handler cannot issue database statements for the session from which
the handler was called.&…

Opening files with unicode (japanese/chinese) characters in filename using Perl

(Read a complete article on the matter and much more
"Unicode issues regarding the Window OS file system and their handling from Perl)

Currently there is no way to manipulate a file named using Unicode characters,by using Perl's built in functions.
The perl 5.10 todo wish list states that functions like chdir, opendir, readdir, readlink, rename, rmdir e.g
"could potentially accept Unicode filenames either as input or output".
Windows default encoding is UTF-16LE,but the console 'dir' command will only return ANSI names.Thus unicode characters are replaced with "?"
,even if you invoke the console using the unicode switch (cmd.exe /u),change the codepage to 65001 which is utf8 on windows and use lucida console true type font which supports unicode.
A workaround is to use the COM facilities provided by windows (in this case Scripting.FileSystemObject) which provide a much higher level of abstraction or use the Win32 api calls.
I tried to read a file…