Informix 4GL Application Error Logging - by Lester Knutsen
Searching Through Informix 4GL Display Arrays - by Sameer Gupta
Financial Functions in Informix 4GL - by David Heller
Getting Column Names Within VI - by Lester Knutsen
The Informix User Group in the Washington area was started in May 1991. Nineteen people from a variety of companies and federal agencies met to hear about release 5.0 and make plans for a user group. At the meeting we identified the goals of the user group as, to share information, learn about new products, provide technical input, discuss problems, share solutions, and network with other Informix Users. Our first newsletter was sent out in June 1991 and we called it the Informix Mid-Atlantic User Group News. Since that time we have grown to include almost 200 people on our mailing list. We have meetings six times a year and our newsletter is produced six times a year. This is a special issue of our by-monthly newsletter for distribution at the Informix Worldwide User Conference in San Jose. This issue is also being mailed to many people who are not on our membership lists in the Maryland, Virginia and the District of Columbia area. If you would like to receive regular mailings from our user group please send in the membership form on the last page.
Our next meeting is scheduled for Wednesday, August 18, 1993 from 9:30 to 11:30 am. The meeting will be held at the Informix Office, 2111 Wilson Blvd., Suite 500, Arlington, VA.
Agenda: Presentation by Informix of New Products
Question and answer session.
Discussion on plans for a one day Informix User Group event
We have an Informix User Group BBS, thanks to Rick Montgomery and Internal Revenue Service. The IRS has established a sub-board on their public bulletin board for use by our Informix User Group. The board is available 24 hours; however, the BBS administrator requests that non-IRS users utilize the 9600 baud line before 7:00 am and after 5:00 pm on weekdays.
The system is fairly self explanatory, however if you have any questions, please call the appropriate individual identified below:
Connections/Hardware/Software: Marianne Crockford or Brian Hupman at 202- 501-5173 Sub-Board Contents/User Group Questions: Rick Montgomery at 703-756-7273 The numbers are : 202-219-9977 at 1200/2400 202-219-9991 at 9600 ( V.32, Non IRS callers after 5:00 pm) 202-219-9995 at 9600-19200 (Trailbizer/PEP, Non IRS callers after 5:00 pm) Set your communications software to no parity, 8 bits and 1 stop bit. You will be prompted to register and fill in a brief online questioner. From the main menu, select 'J' followed by the name of our sub-board, MAIUG. Feel free to test out the options. There is a bulletin available for you to download more detailed instructions.
Please use this BBS to send and receive information about the User Group. We will be uploading all information from our newsletters. If you have any questions please leave a message for Rick Montgomery on the BBS or call 703-756-7273.
The user group has been supported by many companies over the last three years. If your company would like to sponsor a mailing of the newsletter please call for more information. We would like to thank the following companies for sponsoring this newsletter:
Information Technology Development Corp.
Summit Data Group
Westmount USA, Inc.
Windstar Corporation
Do you have an Informix problem that's got you stumped, but you don't know where to turn? Why not discuss it electronically with over 20,000 other Informix users from the comfort of your own terminal? That's what subscribers to the Informix e-mail discussion list do every day.
An e-mail discussion mailing list is essentially a mail forwarding service run by a selected site. An e-mail alias (address) is established at the site to allow messages to be posted to the list. When a message is mailed to the posting address, a separate copy of the message is automatically mailed to each subscriber address on the mailing list.
The Informix mailing list is just such a list. It is dedicated to the discussion of Informix software and related subjects. Topics include all of the Informix offerings, from C-ISAM to WingZ, plus third-party products. Membership in the list is open to anyone, including end-users, vendors, and employees of Informix Software, Inc. The list is unmoderated, so members are solely responsible for its content. All contributions are welcome, as long as they emphasize substantive information.
Using the mailing list does not require any special software, other than the existing e-mail utilities that came with your system. You will need a link to the outside world, such as dial-up UUCP or some other transport mechanism. There is no subscription fee for joining the list. Your only cost will be any charges associated with your off-site e-mail transfer. The number of messages posted to the list is currently running around 250 messages per month, with about 500KB in total monthly traffic.
The Informix mailing list was started in February of 1991 at Emory University in Atlanta, Georgia. By September of that year, the list was reaching an estimated 700 people. There was also enough support on Usenet by then to form a newsgroup dedicated to Informix, "comp.databases.informix".
"Usenet" is the name given to a collection of thousands of computer systems that are connected world-wide into one large network. Some systems are linked by various physical networks, some by satellite, and some by dial-up links. The Usenet NetNews facility can be characterized as a merging of the concepts of an electronic mailing list and a bulletin board system. A single newsgroup is analogous to an individual discussion forum on a typical BBS.
When comp.databases.informix was formed, a two-way bridge, or "gateway," was established between the Usenet newsgroup and the mailing list. All messages sent to the mailing list are automatically posted to the newsgroup, and all articles posted to comp.databases.informix are sent to the mailing list.
Given the fluid nature of the mailing list and Usenet, the total number of people who currently read list messages is difficult to gauge. About a third of the addresses on the main list at Emory are remote aliases that explode out to multiple addresses and perhaps even other aliases or DATA The Usenet readership counts done by the DEC Western Research Lab put the comp.databases.informix readership at around 24,000 users (which does not include people on the mailing list side of the gateway).
While this number is not exact since it is derived by extrapolation, it does give some sense of the potential resource that world-wide distribution represents. Whatever your situation or question, someone on the Net will almost certainly have some relevant experience they are willing to share.
If you would like more information about the Informix mailing list, contact Walt Hultgren at 404/727-0648. You may also reach him via e-mail, of course, by sending e-mail to "walt@rmy.emory.edu". In UUCP style addressing, his address is "...!emory!rmy!walt", where the "..." is replaced by the mail path from your site to "emory". "emory" has UUCP links to "gatech", "rutgers" and "uunet", among others.
This article was written by Walt for the Southeast Informix Users Group.
In every major application that I have developed, a requirement has been to have some way of logging what is going on inside the program. There are three types of application logging I like to use. The first type is to log what the user is doing so that when a user calls with a question or problem it is easier to figure out what went on. This is also a good way to find out what actually gets used in an application and what does not get used. The second type is to log all errors within the program. The third type is to log more detailed debugging information while a program is under development. The log could be to a file, to a network message system, or to a backup device. The log must contain the 4GL module name, the version, the line number in the source code that caused the error, and a message.
Informix 4GL has a function to write to a log file. First you need to create an error log with the function startlog("logname"). Then the function errorlog() can be used to save SQL error messages, informational messages about the application, and debugging messages. Informix 4GL will automatically write to an open error log the Informix error that causes a program to abort, unless you have the statement "whenever error continue" in your code. The log will include the source code line number and module name. However, because the program aborts, it does not allow you to do any clean-up that may be required. I have created a function called dtlog which includes the same information, and by setting the option "whenever error continue" in your code it allows your program to handle and recover from errors. The function uses features of the Unix Source Code Control System (SCCS) to write a module name, version and line number to the log file. The following is a description of the function and some examples.
When the function dtlog is called, a number (code), a string containing some SCCS information and a programmer supplied message is passed. With the SCCS information you can get the module name, SCCS version, and line number in the source file that generated the function call. The following are examples of a line calling my error logging function.
# error logging function when checked out of SCCS for editing (get -e filename) call dtlog( code, "MOD:%M% REL:%I% LINE:%C% :",message ) # error logging function when checked out of SCCS for compiling ( get filename) call dtlog( code, "MOD:dtlog.4gl REL:1.5 LINE:52 :",message )
There are three types of message calls to the function dtlog which writes the log file. The first argument passed is a number code for the type of message. The first type of message is a program error. If the number is negative then it is assumed it is an Informix error message. By replacing the variable "code" in the above function with the SQL error code (sqlca.sqlcode) or status an error message will always print in the log if there is an error, and nothing will print if there is no error. The second type of message is informational. I use the number 1000 for messages that must be written to the log to indicate what is happening in the program. The third type of message is for debugging only. I use the number 0 to code messages that only get written to the log when debugging is turned on. To turn debugging on or off, one variable in the function, debug_flag, needs to be changed.
The second argument is a string containing the SCCS module name, version and line number. When the source code is checked out of SCCS using the get command, the %M% is replaced by the module file name, the %I% by the SCCS version, and the %C% by the line number within the file. This allows you to quickly find the file and line number that caused an error. See your Unix documentation for more information on SCCS.
The third argument is a user supplied message. This allows you to put messages like "preparing.." or "open form new.frm" in the log file to track what you are doing.
The following are some examples of how this function can be used. I like to start and end a program with a message to the log saying that the program started or ended with a function call like:
call dtlog(1000,"MOD:%M% REL:%I% LINE:%C% :","Starting Program")
After every SQL statement I put a function call to check for errors in addition to whatever error handling I have in the program. If debugging is turned off these will only print if there is an error.
call dtlog(status,"MOD:%M% REL:%I% LINE:%C% :","SQL Error ") or call dtlog(sqlca.sqlcode,"MOD:%M% REL:%I% LINE:%C% :","SQL Error ")
For debugging, you can put the following type of statement anywhere in your program that a message would be helpful. It will print only when debugging is turned on.
call dtlog(0,"MOD:%M% REL:%I% LINE:%C% :","Debugging Message")
This is the Informix 4GL code for the function:
#############################################################################
# Copyright 1993 Advanced DataTools Corporation
# Module: %W% Date: %D%
# Author: Lester B. Knutsen
# Description: General Informix 4GL Logging function
#############################################################################
function dtlog(code,relid,msg)
define
code integer, # message type
relid char(40), # SCCS filename, release and line number
msg char(60), # application message passed to function
msgline char(200), # message output to log
debug_flag integer # set level of error logging
whenever error continue # keep going if there is an error
# set the level of debugging for messages to appear in the log
# one of the following must be uncommented
let debug_flag = true # turn on debugging - all messages will
# appear in the log
#let debug_flag = false # turn off debugging - only sql error
# messages or messages when code is 1000
# will appear in the log
case
when ( code = 1000 ) # always write messages to the log
let msgline = "MESSG: ",code using "------& ", relid ,
msg clipped
call errorlog(msgline)
return
when ( code < 0 ) # always write errors to the log
let msgline = "ERROR: ",code using "------& ", relid ,
msg clipped, "\n", err_get(code)
call errorlog(msgline)
return
when ( code >= 0 and debug_flag = true ) # only when debugging
let msgline = "DEBUG: ",code using "------& ", relid ,
msg clipped
call errorlog(msgline)
end case
end function
#############################################################################
# this is an example Informix 4GL program showing how you could use the
functions
main
whenever error continue # keep going if there is an error
call startlog("program.log") # start the error log
# example that will always create a message to the log
call dtlog(1000,"MOD:%M% REL:%I% LINE:%C% :","Message 1 - Starting Program")
# example that will only create a message if debugging is true
call dtlog(0,"MOD:%M% REL:%I% LINE:%C% :","Message 2 - Debugging Message")
# example that will only create a message if their is an error or debugging is
on
call dtlog(status,"MOD:%M% REL:%I% LINE:%C% :","Message 3 - Error ")
# or
call dtlog(sqlca.sqlcode,"MOD:%M% REL:%I% LINE:%C% :","Message 3 - Error ")
# example that will always create a message to the log
call dtlog(1000,"MOD:%M% REL:%I% LINE:%C% :","Message 4 - Tracking Message")
end main
The conventional way for creating lookup displays is to populate an array and display it using the DISPLAY ARRAY statement. The user can make use of the scrolling features which are built into the DISPLAY ARRAY function. After reaching a particular record, the user can hit the ACCEPT key to select it. This works very well if the number of records in the table (or for the search criterion) is a handful. However, if the number of records are too large it becomes difficult for the user to scroll around and reach to the desired record.
The following function allows the user to specify a search pattern (first few characters) for a certain field (by which the records are sorted) and highlights the first record which meets the requirement. If there is no record which starts with the given pattern, the next record in the sort order is highlighted. If the record is not on the current screen, the screen is appropriately refreshed.
The user can specify the search pattern by hitting a specified key (F2 in the example). In the example, the user can enter some characters and these are treated as the starting pattern for the search. The search has been made case insensitive. The function makes use of the SCROLL function which the Informix 4GL provides. Any questions or suggestions may be directed to
Sameer Gupta c/o Tata Consultancy Services, Sheet Metal Workers' Int'l Association
1750 New York Avenue NW. Washington DC 20006
Phone (202) 662 0808 Fax (202) 662 0889
This program uses the following table:
####################################################################
create table state_cm (
state_cd char(2) not null,
state_desc char(20) not null );
####################################################################
-- Help on State Description
-- Tables Used : state_cm
-- Screen Length = 8
-- Written By : Sameer Gupta
####################################################################
DATABASE lcl
--------------------------------------------------------------------
-- Global Variables
--------------------------------------------------------------------
DEFINE pdesc LIKE state_cm.se_desc
DEFINE arrcnt SMALLINT -- number of elements in the array
DEFINE scrline SMALLINT -- current position on screen
DEFINE currrec SMALLINT -- current record in program array
DEFINE jumppos SMALLINT -- relative position to jump to
DEFINE parray ARRAY[100] OF RECORD
se_state_cod LIKE state_cm.se_state_cod,
se_desc LIKE state_cm.se_desc
END RECORD
--------------------------------------------------------------------
MAIN
--------------------------------------------------------------------
LET int_flag = 0
OPEN WINDOW hmd0010 AT 4, 20
WITH FORM "hmd0010"
ATTRIBUTE ( BORDER, PROMPT LINE FIRST, MESSAGE LINE LAST,
COMMENT LINE FIRST, FORM LINE FIRST)
DECLARE state_list CURSOR FOR
SELECT se_state_cod, se_desc FROM state_cm ORDER BY se_desc
LET arrcnt = 1
FOREACH state_list
INTO parray[arrcnt].*
LET arrcnt = arrcnt + 1
IF arrcnt > 100 THEN
ERROR "Press F7 to Exit. Contact System Manager."
EXIT FOREACH
END IF
END FOREACH
LET arrcnt = arrcnt - 1
CALL set_count(arrcnt)
MESSAGE "Press F10 To Select."
LET scrline = 1
LET currrec = 1
DISPLAY BY NAME pdesc ATTRIBUTE (REVERSE, WHITE)
DISPLAY ARRAY parray TO h010.* ATTRIBUTE (REVERSE, WHITE)
ON KEY ("left") CALL up()
ON KEY ("right") CALL down()
ON KEY ("return") CALL down()
ON KEY (F7) LET int_flag = 1 EXIT DISPLAY
ON KEY (F10) LET int_flag = 0 EXIT DISPLAY
ON KEY (F2) CALL jumpto()
ON KEY (F3, NEXTPAGE) CALL pgup()
ON KEY (F4, PREVPAGE) CALL pgdown()
ON KEY (UP) CALL up()
ON KEY (DOWN) CALL down()
END DISPLAY
CLOSE WINDOW hmd0010
--------------------------------------------------------------------
-- The following variables can be returned, if this is converted
-- into a function
DISPLAY parray[currrec].se_state_cod, parray[currrec].se_desc
--------------------------------------------------------------------
END MAIN
--------------------------------------------------------------------
--------------------------------------------------------------------
-- This function converts the description to upper case so as to
-- make the jump case insensitive.
FUNCTION jumpto()
--------------------------------------------------------------------
DEFINE udesc LIKE state_cm.se_desc
INPUT BY NAME pdesc ATTRIBUTE (REVERSE, WHITE)
LET pdesc = UPSHIFT(pdesc)
FOR jumppos = 1 TO arrcnt
LET udesc = UPSHIFT(parray[jumppos].se_desc)
IF udesc >= pdesc THEN EXIT FOR
END IF
END FOR
LET pdesc = ""
DISPLAY BY NAME pdesc ATTRIBUTE (REVERSE, WHITE)
IF jumppos > arrcnt THEN LET jumppos = arrcnt
END IF
SCROLL h010.* UP BY jumppos
LET currrec = jumppos
FOR scrline = 1 TO 8
DISPLAY parray[currrec].* TO h010[scrline].*
ATTRIBUTE (REVERSE, WHITE)
LET currrec = currrec + 1
IF currrec > arrcnt THEN EXIT FOR
END IF
END FOR
IF scrline = 9 THEN LET currrec = currrec - scrline + 1
ELSE LET currrec = currrec - scrline
END IF
LET scrline = 1
DISPLAY parray[currrec].* TO h010[scrline].* ATTRIBUTE (REVERSE, RED)
END FUNCTION
--------------------------------------------------------------------
FUNCTION pgdown()
--------------------------------------------------------------------
LET jumppos = currrec - 8
IF jumppos < 1 THEN LET jumppos = 1
END IF
SCROLL h010.* UP BY jumppos
LET currrec = jumppos
FOR scrline = 1 TO 8
DISPLAY parray[currrec].* TO h010[scrline].*
ATTRIBUTE (REVERSE, WHITE)
LET currrec = currrec + 1
IF currrec > arrcnt THEN EXIT FOR
END IF
END FOR
IF scrline = 9 THEN LET currrec = currrec - scrline + 1
ELSE LET currrec = currrec - scrline
END IF
LET scrline = 1
DISPLAY parray[currrec].* TO h010[scrline].* ATTRIBUTE (REVERSE, RED)
END FUNCTION
--------------------------------------------------------------------
FUNCTION pgup()
--------------------------------------------------------------------
LET jumppos = currrec + 8
IF jumppos > arrcnt THEN
LET jumppos = arrcnt
END IF
SCROLL h010.* UP BY jumppos
LET currrec = jumppos
FOR scrline = 1 TO 8
DISPLAY parray[currrec].* TO h010[scrline].*
ATTRIBUTE (REVERSE, WHITE)
LET currrec = currrec + 1
IF currrec > arrcnt THEN
EXIT FOR
END IF
END FOR
IF scrline = 9 THEN LET currrec = currrec - scrline + 1
ELSE LET currrec = currrec - scrline
END IF
LET scrline = 1
DISPLAY parray[currrec].* TO h010[scrline].* ATTRIBUTE (REVERSE, RED)
END FUNCTION
--------------------------------------------------------------------
-- up by ine row
FUNCTION up()
--------------------------------------------------------------------
IF currrec = 1 THEN
ERROR "BEGINNING OF ARRAY"
GOTO endfunc
END IF
DISPLAY parray[currrec].* TO h010[scrline].* ATTRIBUTE (REVERSE, WHITE)
IF scrline = 1 THEN SCROLL h010.* DOWN BY 1
ELSE LET scrline = scrline - 1
END IF
LET currrec = currrec - 1
DISPLAY parray[currrec].* TO h010[scrline].* ATTRIBUTE (REVERSE, RED)
LABEL endfunc:
END FUNCTION
--------------------------------------------------------------------
-- down by ine row
FUNCTION down()
--------------------------------------------------------------------
IF currrec = arrcnt THEN
ERROR "END OF ARRAY"
GOTO endfunc
END IF
DISPLAY parray[currrec].* TO h010[scrline].* ATTRIBUTE (REVERSE, WHITE)
IF scrline = 8 THEN SCROLL h010.* UP BY 1
ELSE LET scrline = scrline + 1
END IF
LET currrec = currrec + 1
DISPLAY parray[currrec].* TO h010[scrline].* ATTRIBUTE (REVERSE, RED)
LABEL endfunc:
END FUNCTION
help.per
####################################################################
DATABASE lcl
SCREEN
{
State Description
-----------------------------
Code Description
[s0][s1 ]
[s0][s1 ]
[s0][s1 ]
[s0][s1 ]
[s0][s1 ]
[s0][s1 ]
[s0][s1 ]
[s0][s1 ]
[s2 ]
}
TABLES state_cm END
ATTRIBUTES
s0 = state_cm.se_state_cod, NOENTRY;
s1 = state_cm.se_desc, NOENTRY;
-- Following field is to receive pattern
s2 = FORMONLY.pdesc TYPE LIKE state_cm.se_desc;
END
INSTRUCTIONS
DELIMITERS " "
SCREEN RECORD h010[8](se_state_cod, se_desc)
END
The June User's Group meeting was keynoted by Soft-tek International, with a presentation on their products, GRAFSMAN and TACTICIAN Plus. Marketed since 1984, and with over 140,000 users worldwide, Soft-tek's products interface directly with the Informix 4GL language.
GRAFSMAN gives Informix developers the ability to enhance their applications with 2D or 3D graphics that are generated directly from within the application ...eliminating the need to re-format data for use with standalone packages.
Developers use GRAFSMAN to define a template describing how they wish the data to be displayed, then select the information to be displayed and issue a call to invoke GRAFMSAN. It's that simple!
Available in Unix, VMS, DOS and MS-Windows environments, GRAFSMAN is also device independent, giving users the ability to utilize an extensive array of terminals and printers.
TACTICIAN Plus is Soft-tek's recently enhanced full-featured spreadsheet, which also provides data analysis functionality for Unix, DOS and VMS users.
Also with direct links to the Informix database, TACTICIAN Plus gives developers and end users the opportunity to query multiple databases, perform analytical functions, and display the results graphically.
Lotus 1-2-3 files can be imported/exported, and TACTICIAN Plus can process virtually any amount of information, while working equally well with character or graphical devices. It's sophisticated macro language provides the ability to analyze data automatically.
In addition, Informix developers can implement run time spreadsheet applications, so that TACTICIAN Plus will automatically query the database, perform calculations and graph the results without operator interaction.
There are a great number of Informix users worldwide who are using GRAFSMAN and TACTICIAN Plus with extremely favorable results, both in government and commercial settings. Soft-tek International will also be participating in the Informix Users Conference July 12-15 in San Jose.
For more information on GRAFSMAN and TACTICIAN Plus, contact Leesa Jabara at Soft-tek's Regional Office in Alexandria at 703.836.9141 (tel), 703.836.8934 (fax).
These programs contain commonly used financial functions for present value, future value, and equal payment series. These are written as six stand-alone 4GL programs and can easily be converted to 4GL functions for inclusion into other programs. I have successfully compared the results to interest rate factor tables in my Industrial Engineering textbook, but I cannot guarantee the accuracy of the results. Please contact David at 301-299-6967
#################################################################
######
# main
# By David Heller
function AFV(future_value, interest_rate, term)
# calculates an equal series payment amount needed to equal some
# future value over a known term at a known interest rate
# e.g. how much of an annual deposit is needed to
# accumulate $lOOK over 30 years at 10% interest
# AFV(100000, 10, 30)
define
future_value money(32,2),
regular_payment money(32,2),
interest_rate decimal(8,4),
interest_factor decimal(8,4),
term smallint
let future_value = arg_val(1)
let interest_rate = arg_val(2)
let term = arg_val(3)
let interest_factor = interest_rate / 100
let regular_payment = future_value * (interest_factor / (((1 +
interest_factor ) ** term ) -1 ))
display regular_payment
# end main
end function
#################################################################
########
# main
# By David Heller
function APV (present_value, interest_rate, term)
# calculates an equal series payment equal to
# a present value over a known term at a known interest rate
# e.g. monthly payment on $1OOK mortgage at lO% for 30 years
# APV(10000, .83, 360)
define
present_value money(32,2),
regular_payment money(32,2),
interest_rate decimal(8,4),
interest_factor decimal(8,4),
term smallint
let present_value = arg_val(1)
let interest_rate = arg_val(2)
let term = arg_val(3)
let interest_factor = interest_rate / 100
let regular_payment = present_value * (interest_factor * ( ( 1
+ interest_factor ) ** term ) /
(((1 + interest_factor) ** term) -1))
display regular_payment
# end main
end function
#################################################################
########
# main
# By David Heller
function FVA(regular_payment , interest_rate, term)
# calculate the future value equal to a series of equal payments
# over a know period at a known interest rate
# e.g. how much will accumlate from regular deposits of 100
# at 10% over 30 years
# FVA(100, 10, 30)
define
future_value money(32,2),
regular_payment money(32,2),
interest_factor decimal(8,4),
interest_rate decimal(8,4),
term smallint
let regular_payment = arg_val(1)
let interest_rate = arg_val(2)
let term = arg_val(3)
let interest_factor = interest_rate / 100
let future_value = regular_payment * ((( 1 + interest_factor ) **
term ) -1 ) / interest_factor
display future_value
# end main
end function
#################################################################
########
# main
# By David Heller
function FVP(present_value, interest_rate, term)
# calculate future amount equal to a present amount
# and accumulated interest over a known term.
# e.g. how much money will accumulate if $1000 is deposited
# at 10% and left alone for 30 years
# FVP(1000, 10, 30)
define
future_value money(32,2),
present_value money(32,2),
interest_rate decimal(8,4),
interest_factor decimal(8,4),
term smallint
let present_value = arg_val(1)
let interest_rate = arg_val(2)
let term = arg_val(3)
let interest_factor = interest_rate / 100
let future_value = present_value * ((1+interest_factor)**term)
display future_value
# end main
end function
#################################################################
########
# main
# By David Heller
function PVA (regular_payment, interest_rate, term)
# calculate the present worth of a series of equal payments
# at a known interest rate over a known term
# e.g. what amount must be deposited now to generate, with 10%
interest,
# 30 annual amounts of $1000
# PVA(1000, 10, 30)
define
regular_payment money(32,2),
present_value money(32,2),
interest_factor decimal(8,4),
interest_rate decimal(8,4),
term smallint
let regular_payment = arg_val(1)
let interest_rate = arg_val(2)
let term = arg_val(3)
let interest_factor = interest_rate / 100
let present_value = regular_payment * (((1 + interest_factor) **
term) -1) /
(interest_factor * ((1 + interest_factor) ** term))
display present_value
# end main
end function
#################################################################
########
# main
# By David Heller
function PVF( future_value, interest_rate, term)
# calculate the present value of some future amount
# at a known interest rate over a known term
# what is the current worth of 1000 in 5 years
# at 10% interest
define
present_value money(32,2),
future_value money(32,2),
interest_rate decimal(8,4),
interest_factor decimal(8,4),
term smallint
let future_value = arg_val(1)
let interest_rate = arg_val(2)
let term = arg_val(3)
let interest_factor = interest_rate / 100
let present_value = future_value / ((1+interest_factor)**term)
display present_value
# end main
end function
How often have you tired of retyping the same column names repeatedly in vi? One solution is to save the column names in a file and read them in as needed. However, vi has the capability to insert into a document the results of an operating system command. With a little work with shell scripts and awk, you can create a command that will get the columns names from any table in any database, and insert them in to your current document. And with a little more work, you can add formatting like commas, datatypes or the 4gl format of "variablename like tablename.columnname".
To insert the results of a unix command on the current line in vi use "!!command". For example, start vi and type "!!ls". This inserts, the names of the files in the current director into your document. To run the Informix sql info command for the item table in the stores database, and insert the results in the current document you would do the following:
1. Create an sql script called itemsinfo.sql with one line as follows: info columns for items; 2. Load vi and type the following: !!isql stores itemsinfo 3. This runs the sql script and inserts the results in the current document.
The following shell script is one I have created to get the column names from a database for sql, esql/c, 4gl programs. As long as the script getcol is in my path I can get columns names from any table. Type in the script, put it in your path and within vi type "!!getcol databasename tablename". I use awk to format the output so it's more useful. The script can also be run from unix command line and the output will display on standard out.
#############################################################################
# PROGRAM: getcol - this is a shell/awk script to get the columns from
# an Informix database in a format ready to use in a sql
# query, a 4gl program etc...
# In vi, put the cursor on a blank line where you want to
# insert the columns and type !!getcol database columns. The
# columns will be inserted into the current file.
#
# USAGE: getcol database table [-d\-l]
# -d displays as column data type
# -l displays as column like table.column
# The default displays column names only
#
# AUTHOR: Lester B. Knutsen
# Advanced DataTools Corporation
# 4510 Maxfield Drive, Annandale, VA 22003
# 703-256-0267 or lester@access.degix.net
#
# Copyright (c) 1993, Advanced DataTools Corporation, All rights reserved.
#############################################################################
# function to display usage error message and exit
usage() {
echo "usage: $1 database table [-d|-l]"
echo "\t-d: column data type\n\t-l: column like table.column" ; exit 1
}
#############################################################################
# test for correct number of arguments - call usage function if incorrect
[ $# -lt 2 ] || [ $# -gt 3 ] && { usage $0
}
# test for valid third argument ( -l or -d ) - call usage function if
incorrect
case $3 in
-d|-l|"") ;;
*) usage $0 ;;
esac
#############################################################################
# check for dbaccess or isql
SQL=isql; [ -f $INFORMIXDIR/bin/dbaccess ] && SQL=dbaccess
# use the info columns command to get the data from informix and pipe to awk
# everything within the { } is excuted and piped to awk
{ echo $3 $2; $SQL $1 - <<EOF
info columns for $2;
EOF
} | awk '
#############################################################################
# The awk commands to format the data start within the two ''
# set variables
BEGIN { opt = " "; tab = " " ; outcnt = 0 }
# First Line (NR=1) check options from echo $3 $2 statement piped to awk
NR==1 && $1 == "-l" { opt = $1; tab = $2; getline }
NR==1 && $1 == "-d" { opt = $1; getline }
# check lines after first line and format based on options
NR >1 && $1 > "" && $1 != "Column" && $1 != "Error" && $1 != "Near" {
# add the comma and a newline to the end of the last line
if ( outcnt >= 1 ) printf(",\n")
# option -d print column and data type
if ( opt == "-d" ) printf("%-18s\t%s",$1 , $2)
# option -l print column like table.column
else if ( opt == "-l" ) printf("%-18s\tlike %s.%s",$1 , tab, $1 )
# no option print column name only
else printf("%s",$1)
outcnt++
}
# print a newline at the end
END { print "\n" } '
#############################################################################
Washington Area Informix Users Group
4216 Evergreen Lane, Suite 136, Annandale, VA 22003
Phone: 703-256-0267
lester@access.digex.net