Sybase Adaptive Server Enterprise Closing the Cursor

The CLOSE statement terminates processing for the specified cursor. CLOSE releases resources associated with the cursor, and subsequent references to that cursor are allowed only if another OPEN is executed. Although you can have multiple cursors open at the same time, you should close the cursors as soon as possible for efficiency reasons.

Unlike the DB-Library™ interface to SQL Server, the CT-Library interface lets you issue other commands while a cursor is open.

In this example, the additional request for the employee name (shown in bold) is issued while the cursor is open. Under the DB-Library interface, this request would have failed and returned a Results Pending message. Under the CT-Library interface, it succeeds.

string    dname
long      depthead
string    fname
string    lname

SQLCA.dbms = "SYC"
SQLCA.database = "mzctest"
SQLCA.logid = "mikec"
SQLCA.logpass = "mikecx"
SQLCA.servername = "SYB1001"
SQLCA.autocommit = "false"

CONNECT USING SQLCA;
if SQLCA.sqlcode <> 0 then
	MessageBox("Connect Error",SQLCA.sqlerrtext)
end if

DECLARE dept_curs CURSOR FOR SELECT dept_name,
	dept_head_id FROM department;
OPEN dept_curs;
if SQLCA.sqlcode < 0 then
	MessageBox("Open Cursor",SQLCA.sqlerrtext)
end if

DO WHILE SQLCA.sqlcode = 0
	FETCH dept_curs INTO :dname, :depthead;
	if SQLCA.sqlcode < 0 then
		MessageBox("Fetch Error",SQLCA.sqlerrtext)
	elseif SQLCA.sqlcode = 0 then
		SELECT emp_fname, emp_lname INTO
			:fname,:lname FROM employee
			WHERE emp_id = :depthead;
		if SQLCA.sqlcode <> 0 then
			MessageBox("Singleton Select",  & 
				SQLCA.sqlerrtext)
		end if
	end if
LOOP

CLOSE dept_curs;
if SQLCA.sqlcode <> 0 then
	MessageBox("Close Cursor", SQLCA.sqlerrtext)
end if