Quick Links

Re: ERROR: there is no parameter $1

From:	"Martin French" <Martin(dot)French(at)romaxtech(dot)com>
To:	<mlanka(at)avineonindia(dot)com>
Cc:	pgsql-admin(at)postgresql(dot)org,pgsql-admin-owner(at)postgresql(dot)org
Subject:	Re: ERROR: there is no parameter $1
Date:	2012-07-20 06:34:15
Message-ID:	OF4D2531FB.8E17316E-ON80257A41.0023F417-80257A41.00241861@LocalDomain
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-admin

<html><body>Hi <tt>pgsql-admin-owner(at)postgresql(dot)org wrote on 20/07/2012 03:33:36: > From: "Madhu.Lanka" <mlanka(at)avineonindia(dot)com></tt> <tt>> To: <pgsql-admin(at)postgresql(dot)org>, </tt> <tt>> Date: 20/07/2012 06:37</tt> <tt>> Subject: [ADMIN] ERROR:  there is no parameter $1</tt> <tt>> Sent by: pgsql-admin-owner(at)postgresql(dot)org</tt> <tt>> > Hi Friends</tt> <tt>>  </tt> <tt>> I am creating the function like </tt> <tt>>  </tt> <tt>> CREATE OR REPLACE FUNCTION getrowstest3(pname character > varying,ppassword character varying)</tt> <tt>>   RETURNS SETOF getrows AS</tt> <tt>> $BODY$</tt> <tt>> declare</tt> <tt>> r getrows;</tt> <tt>> begin</tt> <tt>> for r in EXECUTE </tt> <tt>> 'select > u.role_id,u.user_id,p.name,p.creation_date,p.telephone_number,p.email,p.remarks,p.city,p.state,p.country,p.postal_address,p.company_name,p.department_name,p.contact_person,</tt> <tt>> p.role_group,p.role_description,p.first_name,p.last_name,p.pincode,p.status,p.department_id,p.designation_id,r.role_name,r.right_edit,r.right_filter,r.right_search,r.right_browse,</tt> <tt>> r.right_print,r.right_download,r.right_admin_user,r.right_image_creator,r.right_vector_download,r.right_wps,r.right_queries_filter,r.right_google_map,r.right_user_management,</tt> <tt>> r.right_route_analysis,r.right_legend,r.right_treeview,r.insert,r.update,r.delete,r.query,r.lock > from ksdi.user_roles u,ksdi.principals p,ksdi.roles r where > p.PRINCIPAL_ID = u.USER_ID and </tt> <tt>> r.role_id = u.role_id and p.name =$1  and p.password = $2;'</tt> <tt>> loop</tt> <tt>> return next r;</tt> <tt>> end loop;</tt> <tt>> return;</tt> <tt>> end</tt> <tt>> $BODY$</tt> <tt>>   LANGUAGE plpgsql VOLATILE</tt> <tt>>   COST 100</tt> <tt>>   ROWS 1000;</tt> <tt>>  </tt> <tt>> Where getrows is the type created by me;</tt> <tt>> It is created successfully.</tt> <tt>> I am trying to call the function I pgadmin with the following command </tt> <tt>> select * from getrowstest2('general_user','aipl(at)123');</tt> <tt>>  </tt> <tt>> I am getting the following error </tt> <tt>>  </tt> <tt>> ERROR:  there is no parameter $1</tt> <tt>> LINE 5: r.role_id = u.role_id and p.name= $1 and p.password = $2</tt> <tt>>                                           ^</tt> <tt>> QUERY:  select > u.role_id,u.user_id,p.name,p.creation_date,p.telephone_number,p.email,p.remarks,p.city,p.state,p.country,p.postal_address,p.company_name,p.department_name,p.contact_person,</tt> <tt>> p.role_group,p.role_description,p.first_name,p.last_name,p.pincode,p.status,p.department_id,p.designation_id,r.role_name,r.right_edit,r.right_filter,r.right_search,r.right_browse,</tt> <tt>> r.right_print,r.right_download,r.right_admin_user,r.right_image_creator,r.right_vector_download,r.right_wps,r.right_queries_filter,r.right_google_map,r.right_user_management,</tt> <tt>> r.right_route_analysis,r.right_legend,r.right_treeview,r.insert,r.update,r.delete,r.query,r.lock > from ksdi.user_roles u,ksdi.principals p,ksdi.roles r where > p.PRINCIPAL_ID = u.USER_ID and </tt> <tt>> r.role_id = u.role_id and p.name= $1 and p.password = $2</tt> <tt>> CONTEXT:  PL/pgSQL function "getrowstest2" line 8 at FOR over > EXECUTE statement</tt> <tt>You have named the parametes in the arguments list:</tt> <tt> getrowstest3(pname character varying,ppassword character varying)</tt> <tt>try the sql with:</tt> <tt> p.name =pname  and p.password = ppassword;</tt> <tt>Cheers</tt> <tt>Martin</tt>=============================================

Romax Technology Limited
Rutherford House
Nottingham Science & Technology Park
Nottingham,
NG7 2PZ
England

Telephone numbers:
+44 (0)115 951 88 00 (main)

For other office locations see:
http://www.romaxtech.com/Contact
=================================
===============
E-mail: info(at)romaxtech(dot)com
Website: www.romaxtech.com
=================================

================
Confidentiality Statement
This transmission is for the addressee only and contains information that is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation.
=================================================
</body></html>

Attachment	Content-Type	Size
unknown_filename	text/html	6.0 KB

In response to

ERROR: there is no parameter $1 at 2012-07-20 02:33:36 from Madhu.Lanka

Browse pgsql-admin by date

	From	Date	Subject
Next Message	Martin French	2012-07-20 07:30:47	Re: ERROR: there is no parameter $1
Previous Message	Umer Asghar	2012-07-20 05:24:03	Re: Postgres Database got down