Cursors 
Author Message
 Cursors

Hello,

I have a query regarding the informix cursors. The usual behaviour
on the opening of the cursor is that a number of records which match
the where clause in the open cursor statement are fetched. A fetch
from the cursor fetches the records from the local cache and not
from the server. In my application this is causing a problem since
other informix sessions may be updating the table as well and hence
the values which are provided by the cursor may be invalid.

Now is it possible that whenever I issue a fetch cursor the records
are fetched from the server/database itself and not the cursor which
probably has gone stale.

Regards
Deepak



Wed, 18 Jun 1902 08:00:00 GMT
 Cursors

:Hello,
:
:I have a query regarding the informix cursors. The usual behaviour
:on the opening of the cursor is that a number of records which match
:the where clause in the open cursor statement are fetched. A fetch
:from the cursor fetches the records from the local cache and not
:from the server. In my application this is causing a problem since
:other informix sessions may be updating the table as well and hence
:the values which are provided by the cursor may be invalid.
:
:Now is it possible that whenever I issue a fetch cursor the records
:are fetched from the server/database itself and not the cursor which
:probably has gone stale.

This does depend on the development tool you are using.

Assuming it's Informix 4GL or NewEra:
Sadly Informix have no direct support for "key set cursors" which
would have solved this problem. You can however easily implement them
yourself. Simply create the cursor to select the primary key of the
table (or tables) you want to read.
When you need the actual data use another cursor to select a row at a
time using the primary key from the first cursor. This will guarantee
that the data isn't stale (or at least not as stale as using one
cursor).

We often use this approach within the same application to let a user
scroll back and forth in a cursor doing updates as they go. In this
way they allways see the updated data, not the prior versions that are
in the cursor even though the user have done the updates themselves.
This does of course require a cursor with hold if you are using
transactions to avoide the scroll cursor beeing closed by the commits
after the updates.


NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway
My opinions are those of my company
The Informix FAQ is at http://www.iiug.org



Wed, 18 Jun 1902 08:00:00 GMT
 Cursors

I think the behaviour you describe happens only with
scroll cursors , scroll cursors are used in inquiry with update options .
There isn't a INFORMIX syntax which will solve your problem , however
you may program your logic to return only the unique key form the query
and use another cursor to read the record each  time it is viewed.
This would ensure that the info is up to date .... However you
will not get the records which were inserted since your open your first
cursor.

Quote:

>Hello,

>I have a query regarding the informix cursors. The usual behaviour
>on the opening of the cursor is that a number of records which match
>the where clause in the open cursor statement are fetched. A fetch
>from the cursor fetches the records from the local cache and not
>from the server. In my application this is causing a problem since
>other informix sessions may be updating the table as well and hence
>the values which are provided by the cursor may be invalid.

>Now is it possible that whenever I issue a fetch cursor the records
>are fetched from the server/database itself and not the cursor which
>probably has gone stale.

>Regards
>Deepak



Wed, 18 Jun 1902 08:00:00 GMT
 Cursors

Quote:


> :Hello,
> :I have a query regarding the informix cursors. The usual behaviour
> :on the opening of the cursor is that a number of records which match
> :the where clause in the open cursor statement are fetched. A fetch
> :from the cursor fetches the records from the local cache and not
> :from the server. In my application this is causing a problem since
> :other informix sessions may be updating the table as well and hence
> :the values which are provided by the cursor may be invalid.
> :Now is it possible that whenever I issue a fetch cursor the records
> :are fetched from the server/database itself and not the cursor which
> :probably has gone stale.
> This does depend on the development tool you are using.
> Assuming it's Informix 4GL or NewEra:
> Sadly Informix have no direct support for "key set cursors" which
> would have solved this problem. You can however easily implement them
> yourself. Simply create the cursor to select the primary key of the
> table (or tables) you want to read.
> When you need the actual data use another cursor to select a row at a
> time using the primary key from the first cursor. This will guarantee
> that the data isn't stale (or at least not as stale as using one
> cursor).
> We often use this approach within the same application to let a user
> scroll back and forth in a cursor doing updates as they go. In this
> way they allways see the updated data, not the prior versions that are
> in the cursor even though the user have done the updates themselves.
> This does of course require a cursor with hold if you are using
> transactions to avoide the scroll cursor beeing closed by the commits
> after the updates.

Nils' suggestion is excellent, however, it does not protect your users
from overwriting data changed by others while he/she is editing the
current record, as Nils points out.  Immediately before updating the
record you should FETCH the row that you are about to update with a
CURSOR...FOR UPDATE so that the row is locked and compare the values
returned for all columns, or for an indicative column like a timestamp
or update count, to the original values that you FETCHED.  Then only if
the values have not changed UPDATE ... WHERE CURRENT OF <cursorname of
cursor for update>.  This will give you the safety of locking rows but
permits greater concurrency since the lock in only momentary.

Art S. Kagel



Wed, 18 Jun 1902 08:00:00 GMT
 Cursors

:I think the behaviour you describe happens only with
:scroll cursors ,

It can happen with a regular cursor as well if you have an order by
that requires a temp table to do the sorting. If you do use order by
you should *allways* expect that to happen as you have no real control
over what the optimizer (current or future version) will do even if
you have worked hard at creating indexes to avoide the use of temp
tables.

: scroll cursors are used in inquiry with update options .
:There isn't a INFORMIX syntax which will solve your problem , however
:you may program your logic to return only the unique key form the query
:and use another cursor to read the record each  time it is viewed.
:This would ensure that the info is up to date .... However you
:will not get the records which were inserted since your open your first
:cursor.
:
:
:>Hello,
:>
:>I have a query regarding the informix cursors. The usual behaviour
:>on the opening of the cursor is that a number of records which match
:>the where clause in the open cursor statement are fetched. A fetch
:>from the cursor fetches the records from the local cache and not
:>from the server. In my application this is causing a problem since
:>other informix sessions may be updating the table as well and hence
:>the values which are provided by the cursor may be invalid.
:>
:>Now is it possible that whenever I issue a fetch cursor the records
:>are fetched from the server/database itself and not the cursor which
:>probably has gone stale.
:>
:>Regards
:>Deepak
:
:


NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway
My opinions are those of my company
The Informix FAQ is at http://www.iiug.org



Wed, 18 Jun 1902 08:00:00 GMT
 Cursors

:Hi Deepak,
:
:Is your database logging? If so look into isolation levels. If
:you set an isolation level of cursor stability or repeatable
:read you will place a shared lock as you fetch each record from the
:cursor. With cursor stability the lock is released when you fetch the
:next row from the cursor (unless you update it); with repeatable
:read the lock is released at the end of the current transaction.

But I do think he wanted to let the other applications do their
updates while he was fetching from his cursor. In that case repeatable
read isn't very usefull. Even cursor stability will usually be
detrimental. If the user is looking at the data and has to press a key
to go to the next row you should be very carefull with locking the
current row.

:-Bill
:} Hello,
:}
:} I have a query regarding the informix cursors. The usual behaviour
:} on the opening of the cursor is that a number of records which match
:} the where clause in the open cursor statement are fetched. A fetch
:} from the cursor fetches the records from the local cache and not
:} from the server. In my application this is causing a problem since
:} other informix sessions may be updating the table as well and hence
:} the values which are provided by the cursor may be invalid.
:}
:} Now is it possible that whenever I issue a fetch cursor the records
:} are fetched from the server/database itself and not the cursor which
:} probably has gone stale.
:}
:} Regards
:} Deepak
:}
:
:
:--
:Bill Ennis                           Voice: 312-474-7516
:SSA                                  Fax:   312-474-7460


NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway
My opinions are those of my company
The Informix FAQ is at http://www.iiug.org



Wed, 18 Jun 1902 08:00:00 GMT
 Cursors

Quote:

> Nils' suggestion is excellent, however, it does not protect your users
> from overwriting data changed by others while he/she is editing the
> current record, as Nils points out.  Immediately before updating the
> record you should FETCH the row that you are about to update with a
> CURSOR...FOR UPDATE so that the row is locked and compare the values
> returned for all columns, or for an indicative column like a timestamp
> or update count, to the original values that you FETCHED.  Then only if
> the values have not changed UPDATE ... WHERE CURRENT OF <cursorname of
> cursor for update>.  This will give you the safety of locking rows but
> permits greater concurrency since the lock in only momentary.

 Perhaps you can help me with with doing this in ESQL/C.

I'd like to do something like:

$ prepare select_id from $select_statement;

$ describe select_id into  in_vals;

$ declare sel_cursor cursor for select_id for update;

$ prepare update_id from "update set column1 = ? where current of
sel_cusor;

$ execute update_id using in_vals;

The problem is that $declare will not let me use a prepared statement id
with the
"for update" keywords. Thus I cannot dynamically set the "select cursor for
update".

Any ideas on how to do this?

Phil

--
--------------------------------------------------------------------------
Philip Walden
Hewlett Packard
Supply Chain Information Systems
1501 Page Mill Road, M/S 5L-A
Palo Alto, CA 94304
(415) 857-3899 FAX (415) 857-8234
http://www.pgis.hp.com/~pwalden

--------------------------------------------------------------------------



Wed, 18 Jun 1902 08:00:00 GMT
 Cursors

Hi,

Append "FOR UPDATE" onto the end of the $select_statement string.

-Bill

Quote:
}

}
} > Nils' suggestion is excellent, however, it does not protect your users
} > from overwriting data changed by others while he/she is editing the
} > current record, as Nils points out.  Immediately before updating the
} > record you should FETCH the row that you are about to update with a
} > CURSOR...FOR UPDATE so that the row is locked and compare the values
} > returned for all columns, or for an indicative column like a timestamp
} > or update count, to the original values that you FETCHED.  Then only if
} > the values have not changed UPDATE ... WHERE CURRENT OF <cursorname of
} > cursor for update>.  This will give you the safety of locking rows but
} > permits greater concurrency since the lock in only momentary.
}
}  Perhaps you can help me with with doing this in ESQL/C.
}
} I'd like to do something like:
}
} $ prepare select_id from $select_statement;
}
} $ describe select_id into  in_vals;
}
} $ declare sel_cursor cursor for select_id for update;
}
} $ prepare update_id from "update set column1 = ? where current of
} sel_cusor;
}
} $ execute update_id using in_vals;
}
}
} The problem is that $declare will not let me use a prepared statement id
} with the
} "for update" keywords. Thus I cannot dynamically set the "select cursor for
} update".
}
} Any ideas on how to do this?
}
} Phil
}
} --
} --------------------------------------------------------------------------
} Philip Walden
} Hewlett Packard
} Supply Chain Information Systems
} 1501 Page Mill Road, M/S 5L-A
} Palo Alto, CA 94304
} (415) 857-3899 FAX (415) 857-8234
} http://www.pgis.hp.com/~pwalden

} --------------------------------------------------------------------------
}
}
}
}

--
Bill Ennis                           Voice: 312-474-7516
SSA                                  Fax:   312-474-7460



Wed, 18 Jun 1902 08:00:00 GMT
 Cursors

Wow!

I tried everything but this, but it works!

Thanks!

Phil

Quote:

> Hi,

> Append "FOR UPDATE" onto the end of the $select_statement string.

> -Bill
> }

> }
> } > Nils' suggestion is excellent, however, it does not protect your users
> } > from overwriting data changed by others while he/she is editing the
> } > current record, as Nils points out.  Immediately before updating the
> } > record you should FETCH the row that you are about to update with a
> } > CURSOR...FOR UPDATE so that the row is locked and compare the values
> } > returned for all columns, or for an indicative column like a timestamp
> } > or update count, to the original values that you FETCHED.  Then only if
> } > the values have not changed UPDATE ... WHERE CURRENT OF <cursorname of
> } > cursor for update>.  This will give you the safety of locking rows but
> } > permits greater concurrency since the lock in only momentary.
> }
> }  Perhaps you can help me with with doing this in ESQL/C.
> }
> } I'd like to do something like:
> }
> } $ prepare select_id from $select_statement;
> }
> } $ describe select_id into  in_vals;
> }
> } $ declare sel_cursor cursor for select_id for update;
> }
> } $ prepare update_id from "update set column1 = ? where current of
> } sel_cusor;
> }
> } $ execute update_id using in_vals;
> }
> }
> } The problem is that $declare will not let me use a prepared statement id
> } with the
> } "for update" keywords. Thus I cannot dynamically set the "select cursor for
> } update".
> }
> } Any ideas on how to do this?
> }
> } Phil
> }
> } --
> } --------------------------------------------------------------------------
> } Philip Walden
> } Hewlett Packard
> } Supply Chain Information Systems
> } 1501 Page Mill Road, M/S 5L-A
> } Palo Alto, CA 94304
> } (415) 857-3899 FAX (415) 857-8234
> } http://www.pgis.hp.com/~pwalden

> } --------------------------------------------------------------------------
> }
> }
> }
> }

> --
> Bill Ennis                           Voice: 312-474-7516
> SSA                                  Fax:   312-474-7460


--
--------------------------------------------------------------------------
Philip Walden
Hewlett Packard
Supply Chain Information Systems
1501 Page Mill Road, M/S 5L-A
Palo Alto, CA 94304
(415) 857-3899 FAX (415) 857-8234
http://www.pgis.hp.com/~pwalden

--------------------------------------------------------------------------


Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. Cursor Cursor Cursor

2. Could not complete cursor operation, because the table schema changed after cursor was declared

3. cursor without cursors

4. Could not complete cursor operation, because the table schema changed after cursor was declared

5. Need Help Please , Invalid Cursor Position Error and Cursor Not Open Error

6. Should I use ODBC cursors or Server cursors?

7. declare curW cursor for EXEC spMySproc - Trying to assign sp resultset to cursor variable

8. client side cursor vs. server side cursor

9. Why declare cursor and open cursor both cost same amount of time

10. Detect if Cursor Opened and Detect if Cursor Allocated

11. Cursor in Trigger - with cursor not open when running second time in the same SQL session


 
Powered by phpBB® Forum Software