From: | Mazen Abdel-Rahman <saba(dot)mazen(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Calling stored function that returns a cursor from a libpq program |
Date: | 2009-08-13 16:04:37 |
Message-ID: | 92265adf0908130904q47847001qf71d8243db272795@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi Tom,
Thank you for the quick reply.
I put in a check to see what the BEGIN transaction command returns - and it
seems to be successful as it returns "PGRES_COMMAND_OK".
I also look at the result returned from calling the stored function and use
that name in the fetch command - however I am still getting the following
error message:
" cursor "portalname" does not exist "
I am able to use cursors successfully in my program if I retrieve the CURSOR
directly and not through a stored function - for example using the line:
char * cursorCall = "DECLARE myPortal CURSOR for select * from cars";
My problem is with using CURSOR returned by stored functions.
Here my modified code to check the being transaction result and to use the
name returned by the call to the stored function in the fetch command:
//BEGIN the transaction
queryResult4 = PQexec(connection, "BEGIN");
execStatus = PQresultStatus(queryResult4); //Get the result status
char * beginTransactionErrorMessage = PQresultErrorMessage(queryResult4);
NSLog([NSString stringWithCString:beginTransactionErrorMessage]);
char* cursorCall = "select reffunc('portalName')"; //call stored function
to get the cursor
//Call the stored function that returns a CURSOR
queryResult4 = PQexec(connection, cursorCall);
execStatus = PQresultStatus(queryResult4); //Get the result status
char * errorMessage = PQresultErrorMessage(queryResult4);
NSLog([NSString stringWithCString:errorMessage]);
if ((execStatus == PGRES_COMMAND_OK) || (execStatus ==
PGRES_TUPLES_OK)) { //cursor retrieved succesfully
int numRows; //variable to store number of rows returned in query
int numColumns; //number of columns per row
numRows = PQntuples(queryResult4);
NSLog(@"The stored function call returned %i rows.", numRows);
numColumns = PQnfields(queryResult4); //get number of columns
//get the name of the returned cursor
char * cursorReturned;
cursorReturned = PQgetvalue(queryResult4, 0, 0);
NSString * fetchNextQuery = [NSString stringWithFormat:@"FETCH ALL in %s",
cursorReturned];
queryResult4 = PQexec(connection,[fetchNextQuery UTF8String] );
execStatus = PQresultStatus(queryResult4); //Get the result status
char * errorMessage2 = PQresultErrorMessage(queryResult4);
NSLog([NSString stringWithCString:errorMessage2]);
....
Thanks!
Mazen Abdel-Rahman
On Thu, Aug 13, 2009 at 8:30 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Mazen Abdel-Rahman <saba(dot)mazen(at)gmail(dot)com> writes:
> > I am trying to use a stored functions that returns a CURSOR in a C
> > program that uses that libpq library.
>
> It looks like you've just hardwired an assumption about what the name of
> the cursor will be. It'd be better to pay attention to the name
> returned by the function.
>
> It also looks like you're not bothering to check that the BEGIN command
> succeeded. If it didn't for some reason, that could explain the
> failure.
>
> regards, tom lane
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-08-13 16:43:49 | Re: Calling stored function that returns a cursor from a libpq program |
Previous Message | Tom Lane | 2009-08-13 15:30:38 | Re: Calling stored function that returns a cursor from a libpq program |