ERROR: there is no parameter $1 - Mailing list pgsql-general
From | Steve Manes |
---|---|
Subject | ERROR: there is no parameter $1 |
Date | |
Msg-id | 473CA3CB.5050405@magpie.com Whole thread Raw |
Responses |
Re: ERROR: there is no parameter $1
|
List | pgsql-general |
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())
pgsql-general by date: