Re: [pgadmin-support] Help for Migration

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:ringerc(at)ringerc(dot)id(dot)au]
Sent: Wednesday, December 07, 2011 1:48 PM
To: Chan, Mamatha Kagathi
Cc: haramrae(at)gmail(dot)com; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] [pgadmin-support] Help for Migration

On 12/07/2011 03:23 PM, Mamatha_Kagathi_Chan(at)DELL(dot)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_ka(at)dell(dot)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