Re: [pgadmin-support] Help for Migration - Mailing list pgsql-general

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.

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 . 
In the call when I am using CALL as suggested below , I am still getting the same error as the driver seem to be
parsingit eventually like it should for a function. Is it a bug in the driver that it cannot handle procedure from the
ODBCconnection?? 

Pls let me know.

The entire error as I am getting is as follows. Pls note that database is not recognizing the object as it is looking
fora function with that name. But here it is a procedure I am executing. 
-error start
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. 

/postgresql/EmployeeDetails.asp, line 656
-error end

Thanks,
Mamatha

-----Original Message-----
From: Craig Ringer [mailto:ringerc@ringerc.id.au]
Sent: Tuesday, October 25, 2011 8:30 AM
To: Alban Hertroys
Cc: Chan, Mamatha Kagathi; pgsql-general@postgresql.org
Subject: Re: [GENERAL] [pgadmin-support] Help for Migration

On 25/10/11 03:36, Alban Hertroys wrote:
> On 24 Oct 2011, at 14:24, <Mamatha_Kagathi_Chan@DELL.com> wrote:
>
>> Hi ,
>>
>> I have migrated Sql Server 2005 database to PostgreSQL 9.0 . I was also successfully able to connect classic ASP to
themigrated database. But when the page reaches a stage where it is accessing a procedure I get the following error. 
>>
>> PostgreSQL Native Provider error '80040e14'
>>
>> ERROR: syntax error at or near "' 789500'" LINE 1: EXEC
>> Visadetailinsert ' 789500','No','Select visa','','','Se... ^

What database access driver/method are you using?


PostgreSQL doesn't support 'EXEC' ... you probably want to

  SELECT myfunctionname(argument1, argument2);

instead. If you're using ODBC, you should be using the ODBC procedure-call escape sequence to make it
database-agnostic,eg: 

  {? = CALL procname(?,?)}

See: http://msdn.microsoft.com/en-us/library/ms403294.aspx

The ODBC driver for the database you're using should translate that into suitable call syntax for your database, eg in
PostgreSQLit should produce 

  SELECT procname(?,?);

and capture the result.



I was initially suspicious that the issue was the whitespace in the quoted number, but that's not it. You're simply
sendinga completely bogus query string, and that's the first point the parser happens to choke on. 


--
Craig Ringer