Hi Craig, The procedure definition is CREATE OR REPLACE PROCEDURE employeedetailinsert(dellbadgeid character varying DEFAULT NULL::character varying, empid character varying DEFAULT NULL::character varying, firstname character varying DEFAULT NULL::character varying, lastname character varying DEFAULT NULL::character varying, dob_day character varying DEFAULT NULL::character varying, dob_month character varying DEFAULT NULL::character varying, contactno character varying DEFAULT NULL::character varying, email character varying DEFAULT NULL::character varying, hiredate character varying DEFAULT NULL::character varying, rehiredate character varying DEFAULT NULL::character varying, totalexp character varying DEFAULT NULL::character varying, statuscd character varying DEFAULT NULL::character varying, costcenter character varying DEFAULT NULL::character varying, "location" character varying DEFAULT NULL::character varying, grade character varying DEFAULT NULL::character varying, hrmgrid character varying DEFAULT NULL::character varying, hrorgmgr character varying DEFAULT NULL::character varying, hrdepartment character varying DEFAULT NULL::character varying, financedepartment character varying DEFAULT NULL::character varying, financedepartmentdescription character varying DEFAULT NULL::character varying, passport character varying DEFAULT NULL::character varying, passvalidfrom character varying DEFAULT NULL::character varying, passvalidupto character varying DEFAULT NULL::character varying) AS...
-- So I am not calling a function but a procedure. The ODBC string is: conn.Open "Provider=PGNP.1;Password=PGDBTest5;Persist Security Info=True;User ID=enterprisedb;Initial Catalog=edb;Data Source=<ip_address>;PORT=5444;SSL=allow;" here <IP address> is replaced for discretion. I have all reasons to believe that this connection string works as I am able to connect to other pages where the database-ASP page interaction is directly using select, insert and update statements rather than calling procedures. Finally I have copy-pasted the "exec proc" form the error in Pgadmin and executed the same to get desirable results. So I doubt if the parameters have anything to do with this. Hope I have provided all the information. Please let me know if I am still lost somewhere. Thanks, Mamatha -----Original Message----- From: Craig Ringer [mailto:ring...@ringerc.id.au] Sent: Wednesday, December 07, 2011 1:48 PM To: Chan, Mamatha Kagathi Cc: haram...@gmail.com; pgsql-general@postgresql.org Subject: Re: [GENERAL] [pgadmin-support] Help for Migration On 12/07/2011 03:23 PM, mamatha_kagathi_c...@dell.com wrote: > Hi Alban/Craig, > > Employeedetailinsert is procedure I have created in PostgreSQL. When I > try to execute the procedure directly in Pgadmin as > > EXEC Employeedetailinsert (parameters same as below) It works fine with > desired result. PgAdmin uses libpq directly. It must be translating the `EXEC' into something PostgreSQL can understand, because the psql command line tool (which also uses libpq) doesn't know what EXEC means. regress=> EXEC dummyfunction(); ERROR: syntax error at or near "EXEC" LINE 1: EXEC dummyfunction(); > But When I call the procedure in Classic ASP I get the below error. I do not > understand why the driver assumes it as function in the 1st place. I am using > Postgres Native driver . Since you're talking about ODBC, I presume you're *actually* using PsqlODBC as your database driver. AFAIK there's no such thing as "postgres native driver". > In the call when I am using CALL as suggested below OK, and since you're using ODBC the CALL gets translated to a server-side proc invocation, that should be no problem. You're not actually showing your ODBC code or ODBC query string, which would be helpful. > Executing Procedure =EXEC employeedetailinsert( ' > 123','55','Mamatha','Chandrashekar','06','05','9886269427','mamatha...@dell.com','12/10/2010','','7','Active','','Bangalore','IG','906','Muralikrishna','TG-,'TPDBA01','TPDBA01- > DBA Practice','No','','') PostgreSQL Native Provider error '80040e14' > > ERROR: function employeedetailinsert(unknown, unknown, unknown, unknown, > unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, > unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, > unknown, unknown, unknown) does not exist LINE 1: SELECT * FROM > employeedetailinsert(' 123','55','Mamat... ^ HINT: No function matches the > given name and argument types. You might need to add explicit type casts. Possibilities: - You might have defined your function with a double-quoted name, eg "Employeedetailinsert". This makes it case sensitive. If that's the case, you'll see it with a capital letter when you run the "\df" command in psql or browse functions in PgAdmin; - You've mucked up your argument list and it isn't really the same length as what you used in PgAdmin after all or you've got the wrong data types; - You might need to specify explicit data types for your parameters in the ODBC call, eg 'Active'::text . Please show your function definition (at least the line with "CREATE OR REPLACE FUNCTION ( parameters ) RETURNS ..." on it) so we can see what you're actually trying to call; - You've set a search_path that means that your ODBC call can't find the function because it's in a schema that isn't being searched; - .... ? Please show your function definition. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
