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><p><font size="2" face="sans-serif">Hi </font><br><br><tt><font size="2">pgsql-admin-owner(at)postgresql(dot)org wrote on 20/07/2012 03:33:36:<br><br>> From: "Madhu.Lanka" <mlanka(at)avineonindia(dot)com></font></tt><br><tt><font size="2">> To: <pgsql-admin(at)postgresql(dot)org>, </font></tt><br><tt><font size="2">> Date: 20/07/2012 06:37</font></tt><br><tt><font size="2">> Subject: [ADMIN] ERROR: there is no parameter $1</font></tt><br><tt><font size="2">> Sent by: pgsql-admin-owner(at)postgresql(dot)org</font></tt><br><tt><font size="2">> <br>> Hi Friends</font></tt><br><tt><font size="2">> </font></tt><br><tt><font size="2">> I am creating the function like </font></tt><br><tt><font size="2">> </font></tt><br><tt><font size="2">> CREATE OR REPLACE FUNCTION getrowstest3(pname character <br>> varying,ppassword character varying)</font></tt><br><tt><font size="2">> RETURNS SETOF getrows AS</font></tt><br><tt><font size="2">> $BODY$</font></tt><br><tt><font size="2">> declare</font></tt><br><tt><font size="2">> r getrows;</font></tt><br><tt><font size="2">> begin</font></tt><br><tt><font size="2">> for r in EXECUTE </font></tt><br><tt><font size="2">> 'select <br>> 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,</font></tt><br><tt><font size="2">> 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,</font></tt><br><tt><font size="2">> 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,</font></tt><br><tt><font size="2">> r.right_route_analysis,r.right_legend,r.right_treeview,r.insert,r.update,r.delete,r.query,r.lock<br>> from ksdi.user_roles u,ksdi.principals p,ksdi.roles r where <br>> p.PRINCIPAL_ID = u.USER_ID and </font></tt><br><tt><font size="2">> r.role_id = u.role_id and p.name =$1 and p.password = $2;'</font></tt><br><tt><font size="2">> loop</font></tt><br><tt><font size="2">> return next r;</font></tt><br><tt><font size="2">> end loop;</font></tt><br><tt><font size="2">> return;</font></tt><br><tt><font size="2">> end</font></tt><br><tt><font size="2">> $BODY$</font></tt><br><tt><font size="2">> LANGUAGE plpgsql VOLATILE</font></tt><br><tt><font size="2">> COST 100</font></tt><br><tt><font size="2">> ROWS 1000;</font></tt><br><tt><font size="2">> </font></tt><br><tt><font size="2">> Where getrows is the type created by me;</font></tt><br><tt><font size="2">> It is created successfully.</font></tt><br><tt><font size="2">> I am trying to call the function I pgadmin with the following command </font></tt><br><tt><font size="2">> select * from getrowstest2('general_user','aipl(at)123');</font></tt><br><tt><font size="2">> </font></tt><br><tt><font size="2">> I am getting the following error </font></tt><br><tt><font size="2">> </font></tt><br><tt><font size="2">> ERROR: there is no parameter $1</font></tt><br><tt><font size="2">> LINE 5: r.role_id = u.role_id and p.name= $1 and p.password = $2</font></tt><br><tt><font size="2">> ^</font></tt><br><tt><font size="2">> QUERY: select <br>> 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,</font></tt><br><tt><font size="2">> 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,</font></tt><br><tt><font size="2">> 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,</font></tt><br><tt><font size="2">> r.right_route_analysis,r.right_legend,r.right_treeview,r.insert,r.update,r.delete,r.query,r.lock<br>> from ksdi.user_roles u,ksdi.principals p,ksdi.roles r where <br>> p.PRINCIPAL_ID = u.USER_ID and </font></tt><br><tt><font size="2">> r.role_id = u.role_id and p.name= $1 and p.password = $2</font></tt><br><tt><font size="2">> CONTEXT: PL/pgSQL function "getrowstest2" line 8 at FOR over <br>> EXECUTE statement</font></tt><br><br><tt><font size="2">You have named the parametes in the arguments list:</font></tt><br><br><tt><font size="2"> getrowstest3(pname character varying,ppassword character varying)</font></tt><br><br><tt><font size="2">try the sql with:</font></tt><br><tt><font size="2"> p.name =pname and p.password = ppassword;</font></tt><br><br><tt><font size="2">Cheers</font></tt><br><br><tt><font size="2">Martin</font></tt><font face="sans-serif">=============================================
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.
=================================================</font>
</body></html>
Attachment | Content-Type | Size |
---|---|---|
unknown_filename | text/html | 6.0 KB |
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 |