Once I start playing with Cursors & Statements & using them to update stuff with functions & jobs ... do I have to do any house-keeping?

i.e.

say I have this SQL

 DECLARE Crsr CURSOR FOR Stmt;
 DECLARE Result INTEGER;
 DECLARE UID INTEGER;

PREPARE Stmt FROM ' SELECT UID, NextMaxUID FROM UIDGenerator ';
OPEN Crsr;
FETCH FIRST FROM Crsr ('UID') INTO UID;

I know I don't _have_ to:

CLOSE Crsr;

Is it sensible to do so?
If I want to re-open the Crsr later with a different piece of SQL is it a good idea to CLOSE first?

Say something interrupts the Function in mid-process (i.e. a power-cut) what happens to any Crsr which is hanging around at that time, does it have any impact on the tables it is working on?

Adam


Thinking about it I don't know. From previous posts of Tim's I suspect that when the function is closed it will do the housekeeping. However, there is little penalty in keeping cursors open (both in DBISAM and ElevateDB) and if they are left open then it will simply improve performance since they won't need to be opened next time they're wanted.

Roy Lambert

Adam,

Roy is correct, opened cursors are automatically closed when the module execution ends, *except* if they were explicitly declared as WITH RETURN cursors.
In that case they stay opened and are returned to the calling program; note that this only applies to  stored procedures, not functions.
However, for readability and resource saving reasons, I always explicitly close all cursors and unprepare all previously prepared queries, but that's optional.

If something interrupts the execution nothing special happens except if you were writing to the cursor and only in case the write operation is interrupted or cached changes where pending; in that case corruption can occur but that's not specific to EDB or cursors... it happens with any database system.  

About reusing the cursor name for another query, that implies preparing a new statement and unpreparing a statement automaticallycloses the cursor.

--
Fernando Dias
[Team Elevate]

Thank you Fernando. I will include basic house-keeping to close/unprepare things when they are no longer needed.

I'm sure it is unavoidable that there are risks of corruption if a cursor is open at the moment of a major failure such as a power-cut. However, in the more disconnected world of the internet it would be useful to have mechanisms for controlling this as much as possible.

Say we have an action on a database which generates a stream of further actions:

Insert Customer.
... this might call a trigger for an ID, and also insert a record in an audit-trail table.

If my user falls over (network / internet connection fails, or power fails) directly after calling the insert, but before setting some NOT NULL value for the Customer record (i.e. Name) the Customer record will be abandoned by the database. However the ID & audit-trail actions may already have occurred.

As a programmer I have to remember that the user may drop out, but the server will follow through with all the actions built into its programming.

... I guess this means there are lots of things I can do to minimize the risk of corruption, stuff like audit-trail needs to be put AFTER POST to ensure it only occurs once other data is definitely present & lots of actions on the server should be wrapped up in transactions.

If these are on the server then they will definitely run right through to COMMIT or ROLLBACK, even if the user has gone missing.

Thank you Fernando. I will include basic house-keeping to close/unprepare things when they are no longer needed.

I'm sure it is unavoidable that there are risks of corruption if a cursor is open at the moment of a major failure such as a power-cut. However, in the more disconnected world of the internet it would be useful to have mechanisms for controlling this as much as possible.

Say we have an action on a database which generates a stream of further actions:

Insert Customer.
... this might call a trigger for an ID, and also insert a record in an audit-trail table.

If my user falls over (network / internet connection fails, or power fails) directly after calling the insert, but before setting some NOT NULL value for the Customer record (i.e. Name) the Customer record will be abandoned by the database. However the ID & audit-trail actions may already have occurred.

As a programmer I have to remember that the user may drop out, but the server will follow through with all the actions built into its programming.

... I guess this means there are lots of things I can do to minimize the risk of corruption, stuff like audit-trail needs to be put AFTER POST to ensure it only occurs once other data is definitely present & lots of actions on the server should be wrapped up in transactions.

If these are on the server then they will definitely run right through to COMMIT or ROLLBACK, even if the user has gone missing.

Adam

>If my user falls over (network / internet connection fails, or power fails) directly after calling the insert, but before setting some NOT NULL value for the Customer record (i.e. Name) the Customer record will be abandoned by the database. However the ID & audit-trail actions may already have occurred.

If this is likely to be a major problem then build in an intermediate stage. The user posts to a holding table(s) and only when the information in there is complete is the data transferred to the live system. You'd also need to build in notifications for when the process fails.

[Team Elevate]

Adam,

<< Once I start playing with Cursors & Statements & using them to update
stuff with functions & jobs ... do I have to do any house-keeping? >>

Only if you need to execute any DDL statements against tables that may be
involved in the queries that you're using in the SQL/PSM.  If not, then EDB
will automatically close/unprepare any cursors or statements as necessary.

--
Tim Young
Elevate Software
www.elevatesoft.com