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