[Oledb-dev] OleDb problems with stored procedure

[Shachar Shemesh]

mcgr at poczta.onet.pl wrote:

>Hi,
>I have problem with calling stored procedure.
>I created a stored procedure:
>
> 
>
>create or replace function test("DATA" refcursor)
>  
>
 From memory, I don't think that we currently support binary passing of 
refcursors from user to database. This is only a side note, however, as 
you passed the refcursor as text.

>retirns refcursor AS $$
>  
>
I'll assume you meant "returns..." 

>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"
>  
>
I tried this command line in psql, and it didn't work either:
sun=# select * from test('Data'); fetch all in "Data";
 test
------
 Data
(1 row)

ERROR:  cursor "Data" does not exist

A cursor in Postgresql exists only so long as the transaction that 
created it exists. I see no code to perform the above operation inside a 
transaction, so it seems there is no hope for it to work. When I try it 
inside a transaction, however, I get:
sun=# begin
sun-# ;
BEGIN
sun=# select * from test('Data'); fetch all in "Data";
 test
------
 Data
(1 row)

 id | nazwa | opis | usuniety
----+-------+------+----------
(0 rows)

sun=# commit;
COMMIT

So I'll assume that this is not the problem you are complaining about.

>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.
>
>  
>
I'll try.

I see several problems with the code you are trying to run. Fortunately, 
they are mutually canceling. Part of the problem, I think, is that 
you've hit a singularity point in PgOleDb. PgOleDb actually does a 
special detection for this particular type of queries, and treats them 
differently. I believe that if you just turn the line that says

lvCommand.CommandText = "test('Data'); fetch all in "Data"";
 to
lvCommand.CommandText = "test('Data')";

All your problems will be solved.

Explanation:
Problem #1 with your code: You are passing a compound statement into "command text". This is not supported. Multiple commands need to be carried out in multiple "exec"s.

Problem #2: You are passing a statement (fetch) after telling ADO that you are going to run a stored procedure (lvCommand.CommandType = CommandType.StoredProcedure;).
This cannot work. Even if compound statements were supported, you would have had to turn the calling into an SQL. One way to do that is by doing "select * from test('Data'); fetch ...." instead.

Problem #3: The "fetch" is done implicitly, automatically.
Due to the fact that OLE DB is required to be able to return multiple result sets, any function (in fact, any SQL statement at all) that returns, as a result, a "table" containing a single row of type "refcursor" (as is the case with your stored procedure) is assumed to be trying to return multiple result sets. As such, PgOleDb performs the fetch for each row, and feeds that back as a multiple results set (which is how we support the "next result" interface).

One way (the way I would suggest) to solve this "problem" is to just let PgOleDb run the fetchs, as is demonstrated by the code I showed above. If, for whatever reason, you want to insist on writing an explicit "fetch", then make sure that your function does not return a single column of type "refcursor". Either retype the return code to "text", move from explicit procedure call to SQL, and do "select test('Data'), null" or "select *, null from test('Data')" (the later allows returning multiple rows), or do anything else you think is right to avoid the "one column of type refcursor" symptom.

>Maciek
>mcgr at poczta.onet.pl
>  
>
I hope I've helped,
       Shachar

-- 
Shachar Shemesh
Lingnu Open Source Consulting ltd.
http://www.lingnu.com/