[Oledb-dev] OleDb problems with stored procedure

[mcgr at poczta.onet.pl]

An HTML attachment was scrubbed...
URL: http://pgfoundry.org/pipermail/oledb-devel/attachments/20050803/b6ff1ac9/attachment.html
-------------- next part --------------
Hi,
I have problem with calling stored procedure.
I created a stored procedure:

 

create or replace function test("DATA" refcursor)
retirns refcursor AS $$
begin
 open "DATA" for
 select
  ID,
  NAZWA,
  OPIS,
  USUNIETY
 from MIASTA;

 return "DATA";
end;
$$ language plpgsql;

 

I try calling this stored procedure from dotnet (C#):


DataSet lvDataSet = new DataSet();


OleDbConnection lvConnection = new OleDbConnection();
lvConnection.ConnectionString = "Provider=PostgreSQL; Data Source=localhost;
Location = KANCELARIA; User id = postgres; Password = postgres;";
lvConnection.Open();

 

OleDbCommand lvCommand = new OleDbCommand();
lvCommand.Connection = lvConnection;
lvCommand.CommandType = CommandType.StoredProcedure;
lvCommand.CommandText = "test('Data'); fetch all in "Data""; //I try: select
test('Data'); fetch all in "Data"

 

OleDbDataAdapter lvAdapter = new OleDbDataAdapter();
lvAdapter.SelectCommand = lvCommand;
lvAdapter.Fill(lvDataSet);

lvConnection.Close();

 

but I always get the same error:

 

Fatal error in query
ERROR:  syntax error at or near "exec" znak 1

 

Where is problem? How should I call a stored procedure in 'CommandText'?


Please help me.

 

Maciek
mcgr at poczta.onet.pl