ERROR: there is no parameter $1

From: Steve Manes <smanes(at)magpie(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: ERROR: there is no parameter $1
Date: 2007-11-15 19:53:47
Message-ID: 473CA3CB.5050405@magpie.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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())

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Steve Manes 2007-11-15 20:03:36 Re: ERROR: there is no parameter $1
Previous Message adrobj 2007-11-15 19:46:16 Clustered/covering indexes (or lack thereof :-)