Re: [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
Pgadminas 
>
> 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
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 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@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 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