scotto@crash.cts.com (Scott O'Connell) (12/28/89)
I'm having a problem with this program. When a user exits the program
with DEL (7F), AFTER the query starts (OPEN invoice_row), the sqlexec
process continues to run until I kill it with kill -9 pid. I have
talked to Informix 4GL tech support (Karen), but so far they (she)
have (has) been no help at all. I'm getting the same "rewrite your
program using the stores database, and then call us (me)" stuff I've
been getting for over a year. I've even offered a dial-up into the
systems showing the problem.
So, comp.database friends, I am again looking to you for help. I would
also appreciate any comments you have on making this program better. I
have learned everything I know from _having_ to write programs. I have
no formal training in 4GL.
System specifics: Compaq 386/20, SCO Xenix 2.3.2,
Informix 4GL 1.10.03K, SQL 2.10.03K
GLOBALS
DEFINE p_invoices RECORD LIKE invoices.*,
p_charges RECORD LIKE charges.*,
p_customers RECORD
mail_name LIKE customers.mail_name,
customer_status LIKE customers.customer_status
END RECORD,
a, b, c, d, e SMALLINT,
query_string CHAR(255),
select_string CHAR(255),
choice CHAR(1),
p_inv_total DECIMAL(9,2),
old_id INTEGER
END GLOBALS
MAIN
DEFINE run_line CHAR(50)
OPTIONS ERROR LINE LAST
OPEN FORM form1 FROM "./frm/invsrch"
DISPLAY FORM form1
WHILE TRUE
CLEAR FORM
CONSTRUCT query_string ON invoices.customer, invoices.invoice,
invoices.the_date, invoices.po_number,
invoices.rectype, invoices.warehouse,
charges.quantity, charges.part_number,
charges.vendor, charges.price,
charges.core_price, invoices.the_total
FROM formonly.customer, formonly.invoice,
formonly.the_date, formonly.po,
formonly.rectype, formonly.warehouse,
formonly.quantity, formonly.part_number,
formonly.vendor, formonly.price,
formonly.core_price, formonly.the_total
DISPLAY "SEARCHING..." TO formonly.customer_name ATTRIBUTE(REVERSE)
LET select_string = "SELECT UNIQUE * FROM invoices, charges WHERE ",
query_string CLIPPED,
" AND invoices.invoice_id = charges.invoice_id",
" ORDER BY invoices.the_date DESC"
PREPARE select_string FROM select_string
DECLARE invoice_row SCROLL CURSOR FOR select_string
OPEN invoice_row
FETCH FIRST invoice_row INTO p_invoices.*
IF status = 100 THEN
CLEAR FORM
ERROR "There are no records!"
CONTINUE WHILE
END IF
CALL show_to_screen()
WHILE TRUE
PROMPT " F)irst, L)ast, N)ext, P)revious, H)ardcopy, Q)uery, E)xit" FOR CHAR choice
LET choice = UPSHIFT(choice)
LET old_id = p_invoices.invoice_id
CASE
WHEN choice = "F"
DISPLAY "First..." AT 1,2
FETCH FIRST invoice_row INTO p_invoices.*
EXIT CASE
WHEN choice = "L"
DISPLAY "Last..." AT 1,2
FETCH LAST invoice_row INTO p_invoices.*
EXIT CASE
WHEN choice = "N"
DISPLAY "Next..." AT 1,2
WHILE p_invoices.invoice_id = old_id AND NOT status = 100
FETCH NEXT invoice_row INTO p_invoices.*
END WHILE
EXIT CASE
WHEN choice = "P"
DISPLAY "Previous..." AT 1,2
WHILE p_invoices.invoice_id = old_id AND NOT status = 100
FETCH PREVIOUS invoice_row INTO p_invoices.*
END WHILE
EXIT CASE
WHEN choice = "H"
DISPLAY "Hardcopy..." AT 1,2
LET run_line = "invoice.4ge ", p_invoices.invoice, " > /dev/null"
LET run_line = run_line CLIPPED
RUN run_line WITHOUT WAITING
EXIT CASE
WHEN choice = "Q"
CLOSE invoice_row
EXIT WHILE
WHEN choice = "E"
CLOSE invoice_row
EXIT PROGRAM
END CASE
IF status = 100 THEN
ERROR "No more to display!"
CONTINUE WHILE
END IF
CALL show_to_screen()
END WHILE
CLOSE invoice_row
END WHILE
END MAIN
FUNCTION show_to_screen()
SELECT mail_name, customer_status
INTO p_customers.mail_name, p_customers.customer_status
FROM customers
WHERE customer = p_invoices.customer
DISPLAY p_invoices.customer TO formonly.customer
DISPLAY p_customers.mail_name TO formonly.customer_name
IF p_customers.customer_status = "C" THEN
DISPLAY " (CLOSED)" TO formonly.customer_status ATTRIBUTE(REVERSE)
ELSE
DISPLAY "" TO formonly.customer_status
END IF
DISPLAY p_invoices.invoice TO formonly.invoice
DISPLAY p_invoices.the_date TO formonly.the_date
DISPLAY p_invoices.po_number TO formonly.po
# If it's a return invoice, show it reverse to catch their attention
IF p_invoices.rectype = "R" THEN
DISPLAY p_invoices.rectype TO formonly.rectype ATTRIBUTE(REVERSE)
ELSE
DISPLAY p_invoices.rectype TO formonly.rectype
END IF
DISPLAY p_invoices.warehouse TO formonly.warehouse
DECLARE charge_row CURSOR FOR
SELECT *
FROM charges
WHERE invoice_id = p_invoices.invoice_id
ORDER BY sequence
LET a = 1
LET p_inv_total = 0
FOREACH charge_row INTO p_charges.*
DISPLAY p_charges.quantity,
p_charges.part_number,
p_charges.vendor,
p_charges.price,
p_charges.price_total,
p_charges.core_price,
p_charges.core_total,
p_charges.tax,
p_charges.the_total TO s_items[a].*
LET a = a + 1
LET p_inv_total = p_inv_total + p_charges.the_total
IF a = 14 THEN # If there are more than 13 line items on the invoice
EXIT FOREACH # then exit. This way, the totals wont add up and an
END IF # error will result.
END FOREACH
CLOSE charge_row
FOR b = a TO 13
DISPLAY "", "", "", "", "", "", "", "", "" TO s_items[b].*
END FOR
IF NOT p_inv_total = p_invoices.the_total THEN
DISPLAY "" AT 23,1
DISPLAY p_invoices.the_total USING "-------.&&" AT 23,50
DISPLAY " ERROR " AT 23,67 ATTRIBUTE(REVERSE)
ELSE
DISPLAY "" AT 23,1
DISPLAY p_inv_total USING "--------.&&" AT 23,67
END IF
RETURN
END FUNCTION
--
Scott O'Connell UUCP: {nosc, ucsd, hplabs!hp-sdd}!crash!ipars!scotto
ARPA: crash!ipars!scotto@nosc.mil
INET: scotto@ipars.cts.com