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
charactervarying 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::charactervarying, statuscd  character varying DEFAULT NULL::character varying, costcenter  character varying
DEFAULTNULL::character varying, "location"  character varying DEFAULT NULL::character varying, grade  character varying
DEFAULTNULL::character varying, hrmgrid  character varying DEFAULT NULL::character varying, hrorgmgr  character varying
DEFAULTNULL::character varying, hrdepartment  character varying DEFAULT NULL::character varying, financedepartment
charactervarying DEFAULT NULL::character varying, financedepartmentdescription  character varying DEFAULT
NULL::charactervarying, passport  character varying DEFAULT NULL::character varying, passvalidfrom  character varying
DEFAULTNULL::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-ASPpage 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
Idoubt 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@ringerc.id.au]
Sent: Wednesday, December 07, 2011 1:48 PM
To: Chan, Mamatha Kagathi
Cc: haramrae@gmail.com; pgsql-general@postgresql.org
Subject: Re: [GENERAL] [pgadmin-support] Help for Migration

On 12/07/2011 03:23 PM, Mamatha_Kagathi_Chan@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
psqlcommand 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
functionin 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
suchthing 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@dell.com','12/10/2010','','7','Active','','Bangalore','IG','906','Muralikrishna','TG-,'TPDBA01','TPDBA01-
DBAPractice','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
givenname 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
functionsin 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'vegot the wrong data types; 

- You might need to specify explicit data types for your parameters in the ODBC call, eg 'Active'::text . Please show
yourfunction definition (at least the line with "CREATE OR REPLACE FUNCTION ( parameters ) RETURNS ..." on it) so we
cansee 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
beingsearched; 

- .... ?


Please show your function definition.

--
Craig Ringer