This one has me stumped. Does anyone know under which circumstances
this error would be returned by PHP's pg_query_params() even if the
procedure completed without an apparent error?
The procedure and PHP API code haven't changed in weeks. I started
getting this after I upgraded the database to 8.3.
Here's the PHP call:
....
$sql = 'SELECT insert_patient_person ($1, $2, $3, $4, $5, $6, NULL,
$7, $8, $9,
$10, $11, $12, $13, $14, $15,
$16, $17, $18, $19,
$20, $21, $22, $23, $24, $25,
$26, $27, $28, $29,
ENTITY_INCOMPLETE())';
$result = pg_query_params($trms->db, $sql, array(
util_clean_str($post['first_name']),
util_clean_str($post['middle_name']),
util_clean_str($post['last_name']),
$post['ss_num'],
$post['ref_language_id'],
$post['ref_gender_type_id'],
$post['ref_race_type_id'],
util_clean_str($post['address1']),
util_clean_str($post['address2']),
util_clean_str($post['city']),
$post['ref_state_id'],
util_clean_str($post['postal_code']),
util_clean_str($post['email']),
util_clean_str($post['phone1']),
$post['ref_phone1_type_id'],
util_clean_str($post['phone2']),
$post['ref_phone2_type_id'],
util_clean_str($post['phone3']),
$post['ref_phone3_type_id'],
util_clean_str($post['alias_name']),
$post['dob'],
$post['mr_num'],
$post['ehris_num'],
$post['medicaid_num'],
$post['other_num'],
$post['is_shelter_resident'],
$post['is_icm'],
util_clean_str($post['comments']),
$post['caseworker_id']));
// debug('insert', $sql);
$result = pg_query($trms->db, $sql);
if ($result === false) {
return array(null, "New patient insert failed.<br>" .
pg_last_error($trms->db));
}
$patient_id = pg_fetch_result($result, 0, 0);
return array($patient_id, "Patient insert succeeded.");
--- And here's the stored procedure (with debugging strings)
------------------------------------------------------------
-- Insert a new patient with new person and demographic.
--
-- Returns: new patient insert_id
-- -1 (error)
------------------------------------------------------------
CREATE OR REPLACE FUNCTION insert_patient_person (
v_first_name VARCHAR,
v_middle_name VARCHAR,
v_last_name VARCHAR,
v_ss_num VARCHAR,
v_ref_language_id INTEGER,
v_ref_gender_type_id INTEGER,
v_acl_group_id INTEGER,
v_ref_race_type_id INTEGER,
v_address1 VARCHAR,
v_address2 VARCHAR,
v_city VARCHAR,
v_state_id INTEGER,
v_postal_code VARCHAR,
v_email VARCHAR,
v_phone1 VARCHAR,
v_ref_phone1_type_id INTEGER,
v_phone2 VARCHAR,
v_ref_phone2_type_id INTEGER,
v_phone3 VARCHAR,
v_ref_phone3_type_id INTEGER,
v_alias_name VARCHAR,
v_dob VARCHAR,
v_mr_num VARCHAR,
v_ehris_num VARCHAR,
v_medicaid_num VARCHAR,
v_other_num VARCHAR,
v_is_shelter_resident BOOLEAN,
v_is_icm BOOLEAN,
v_comments TEXT,
v_caseworker_person_id INTEGER,
v_entity_status INTEGER) RETURNS INTEGER AS $$
DECLARE
d_person_insert_id person.person_id%TYPE;
d_patient_insert_id patient.patient_id%TYPE;
d_person_associate_id person_associate.person_associate_id%TYPE;
BEGIN
-- Insert a new person and demographic record.
RAISE INFO 'insert_patient_person: 1';
SELECT INTO d_person_insert_id
insert_person_and_demographic (
CAST('' AS NAME),
CAST('' AS VARCHAR),
true,
v_first_name,
v_middle_name,
v_last_name,
v_ss_num,
v_dob,
false,
false,
v_ref_language_id,
v_ref_gender_type_id,
v_acl_group_id,
v_ref_race_type_id,
v_address1,
v_address2,
v_city,
v_state_id,
'USA',
v_postal_code,
v_email,
v_phone1,
v_ref_phone1_type_id,
v_phone2,
v_ref_phone2_type_id,
v_phone3,
v_ref_phone3_type_id,
CAST('' AS TEXT));
IF d_person_insert_id < 1 THEN
RAISE NOTICE 'insert_patient_person: Could not insert new
person record';
ROLLBACK;
RETURN -1;
END IF;
-- Insert a new patient record.
RAISE INFO 'insert_patient_person: 2';
SELECT INTO d_patient_insert_id
insert_patient (
NULL,
d_person_insert_id,
v_alias_name,
v_mr_num,
v_ehris_num,
v_medicaid_num,
v_other_num,
v_is_shelter_resident,
v_is_icm,
v_comments,
v_entity_status);
IF d_patient_insert_id < 1 THEN
RAISE NOTICE 'insert_patient_person: Could not insert new
patient record';
ROLLBACK;
RETURN -1;
END IF;
-- Insert a new caseworker record (if we have one)
RAISE INFO 'insert_patient_person: 3';
IF v_caseworker_person_id > 0 THEN
SELECT INTO d_person_associate_id
insert_person_associate (
d_person_insert_id,
v_caseworker_person_id,
get_person_associate_type_id('caseworker'));
IF d_person_associate_id < 0 THEN
RAISE NOTICE 'insert_patient_person: Could not insert new
caseworker record';
ROLLBACK;
RETURN -1;
END IF;
END IF;
RAISE INFO 'insert_patient_person: done';
RETURN d_patient_insert_id;
-- EXCEPTION
-- WHEN others THEN RETURN -1;
END;
$$ LANGUAGE plpgsql;
--- and here's the log output
Nov 15 14:31:24 jack postgres[48240]: [1-1] INFO: insert_patient_person: 1
Nov 15 14:31:24 jack postgres[48240]: [2-1] INFO: insert_patient_person: 2
Nov 15 14:31:24 jack postgres[48240]: [3-1] INFO: insert_patient_person: 3
Nov 15 14:31:24 jack postgres[48240]: [4-1] INFO:
insert_patient_person: done
Nov 15 14:31:24 jack postgres[48240]: [5-1] ERROR: there is no parameter $1
Nov 15 14:31:24 jack postgres[48240]: [5-2] STATEMENT: SELECT
insert_patient_person ($1, $2, $3, $4, $5, $6, NULL, $7, $8, $9,
Nov 15 14:31:24 jack postgres[48240]: [5-3]
$10, $11, $12, $13, $14, $15, $16, $17, $18,
$19,
Nov 15 14:31:24 jack postgres[48240]: [5-4]
$20, $21, $22, $23, $24, $25, $26, $27, $28,
$29,
Nov 15 14:31:24 jack postgres[48240]: [5-5]
ENTITY_INCOMPLETE())
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly