Hello:

I am somewhat new to Firebird and cannot tell what I am doing wrong. I
have a text file with some patient data. I have a couple of domains,
an internal table to hold the patient data and a stored procedure to
attempt to update or insert the text into the internal table. When I
execute the stored procedure, the routine inserts the data from the
last physical row in the text file but only that row. If I create a
record with a value in MR_NUM in the internal table, then the update
part of the procedure correctly updates the name fields and if I have
multiple MR_NUM values all the records get updated where the values
match. The data file and domain, table and procedure definitions are
shown below.

If someone has a thought to help me out in my ignorance, I would truly
appreciate it.

000000001waugh PATIENT 2/09/1951MWN
2727smiley thomas-----zz 2/10/1952M
000003737zarnowiecki barry------xl 2/09/1951M
000004350BROWNie JEFFERSON B 2/24/1956MBN
000014628ADAIRs KEMMIE L 4/09/1922FW
999999999LASTy FIRST I 2/09/1951MWN
000025133ACOSTAz JOE A 9/04/1991MO
000030001BROWNer JEFFERSON B 2/09/1952MWH
000030003CRAWFORDs BASIL A 0/00/0000MW
000030004arnoldz STEVE R 6/14/1940MW

==========================================================
CREATE DOMAIN DATEOFBIRTH AS
DATE
CHECK ((VALUE IS NULL) OR ( (VALUE >= '01 Jan 1875') AND (VALUE <=
CURRENT_DATE) ))

CREATE DOMAIN IDNUM AS
NUMERIC(9,0)
CHECK ((value is null) or (VALUE IS NOT NULL AND VALUE >= 0))
==========================================================
CREATE TABLE PATIENTS (
MR_NUM IDNUM NOT NULL /* IDNUM = NUMERIC(9,0) CHECK ((value is
null) or (VALUE IS NOT NULL AND VALUE >= 0)) */,
LNAME VARCHAR(50) COLLATE EN_US,
FNAME VARCHAR(50) COLLATE EN_US,
MNAME VARCHAR(50) COLLATE EN_US,
DOB DATEOFBIRTH /* DATEOFBIRTH = DATE CHECK ((VALUE IS
NULL) OR ( (VALUE >= '01 Jan 1875') AND (VALUE <= CURRENT_DATE) )) */,
SEX CHAR(1) COLLATE EN_US,
RACE CHAR(1) COLLATE EN_US,
ETHNICITY CHAR(1) COLLATE EN_US
);

==========================================================
CREATE TABLE EXT_PATIENTS EXTERNAL 'C:\TMP\PATIENTS.TXT' (
MR_NUM CHAR(9),
LNAME CHAR(20),
FNAME CHAR(12),
MNAME CHAR(1),
DOB CHAR(10),
SEX CHAR(1),
RACE CHAR(1),
ETHNICITY CHAR(1),
CRLF CHAR(2)
);

==========================================================
CREATE PROCEDURE PAT_UPDATE
AS
DECLARE VARIABLE WSMR_NUM INTEGER;
DECLARE VARIABLE WSLNAME CHAR(20);
DECLARE VARIABLE WSFNAME CHAR(12);
DECLARE VARIABLE WSMNAME CHAR(1);
DECLARE VARIABLE WSSEX CHAR(1);
DECLARE VARIABLE WSRACE CHAR(1);
DECLARE VARIABLE WSETHNICITY CHAR(1);
begin

for select cast(mr_num as integer), lname, fname, mname, sex, race,
ethnicity from ext_patients
into :wsmr_num, :wslname, :wsfname, :wsmname, :wssex, :wsrace,
:wsethnicity
do

update patients set
mr_num = :wsmr_num,
lname = :wslname,
fname = :wsfname
where mr_num = :wsmr_num;

if(row_count = 0) then
begin
insert into patients
(mr_num, lname, fname)
values (:wsmr_num, :wslname, :wsfname);
end

end