July 1993 Newsletter

Volume 3, No. 4

Highlights of This Issue

Highlights of This Issue

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

User Group Activities and Events

Special Issue for New Members

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.

Next Meeting

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

Informix User Group BBS System

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.

Newsletter Sponsorship

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:

Informix E-Mail Discussion List Going Strong

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.

Informix 4GL Application Error Logging

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

Search Through Informix 4GL Display Arrays

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

Data Visualization and Analysis Tools for Informix Users

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

Financial Functions in Informix 4GL

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    

Getting Column Names Within VI

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" } '
#############################################################################