PERL,INGRES,PROGRAMMING

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, h_errorno=errorno); if h_errorno!=0 then rollback; callframe err_handler; ...

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; endwhile; 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 co...

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': seterr.c:(.text+0xe): 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)...

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 Tim...

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 t...

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 speciali...

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

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

(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 re...