SELECT customer.id AS cust_id, customer.company_name, sales_order.id AS order_id, sales_order.order_date, employee.emp_id, STRING ( employee.emp_fname, ' ', employee.emp_lname ) AS emp_name, sales_order_items.line_id FROM customer INNER JOIN sales_order ON sales_order.cust_id = customer.id INNER JOIN employee ON employee.emp_id = sales_order.sales_rep INNER JOIN sales_order_items ON sales_order_items.id = sales_order.id WHERE STRING ( employee.emp_fname, ' ', employee.emp_lname ) IN ( 'Rollin Overbey', 'Philip Chin' ) AND customer.company_name IN ( 'The Power Group', 'Darling Associates' ) AND sales_order.order_date <= '2000-12-31' ORDER BY 1, 2, 3, 4, 5, 6, 7; Here’s what the SELECT returns: data from 11 different sales_order_item rows in five different orders (five different values of order_id). It also shows that the correct company name, order date, and employee name are being selected. cust_id company_name order_id order_date emp_id emp_name line_id ======= ================== ======== ========== ====== ============== ======= 101 The Power Group 2001 2000-03-16 299 Rollin Overbey 1 101 The Power Group 2001 2000-03-16 299 Rollin Overbey 2 101 The Power Group 2001 2000-03-16 299 Rollin Overbey 3 101 The Power Group 2206 2000-04-16 299 Rollin Overbey 1 101 The Power Group 2206 2000-04-16 299 Rollin Overbey 2 101 The Power Group 2206 2000-04-16 299 Rollin Overbey 3 101 The Power Group 2206 2000-04-16 299 Rollin Overbey 4 101 The Power Group 2279 2000-07-23 299 Rollin Overbey 1 103 Darling Associates 2340 2000-09-25 299 Rollin Overbey 1 103 Darling Associates 2451 2000-12-15 129 Philip Chin 1 103 Darling Associates 2451 2000-12-15 129 Philip Chin 2 Two DELETE statements are required, one for sales_order and one for sales_order_items, because each DELETE can only affect a single table. The DELETE for sales_order_items must come first because it is the child table in a foreign key relationship with sales_order. Here’s what the first DELETE looks like; it has exactly the same FROM and WHERE clauses as the SELECT above: DELETE sales_order_items FROM customer INNER JOIN sales_order ON sales_order.cust_id = customer.id INNER JOIN employee ON employee.emp_id = sales_order.sales_rep INNER JOIN sales_order_items ON sales_order_items.id = sales_order.id WHERE STRING ( employee.emp_fname, ' ', employee.emp_lname ) IN ( 'Rollin Overbey', 'Philip Chin' ) AND customer.company_name IN ( 'The Power Group', 'Darling Associates' ) AND sales_order.order_date <= '2000-12-31'; When that DELETE is executed, it performs exactly the same function as the following single-row DELETE statements: 186 Chapter 5: Deleting Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. DELETE sales_order_items WHERE id = 2001 AND line_id = 1; DELETE sales_order_items WHERE id = 2001 AND line_id = 2; DELETE sales_order_items WHERE id = 2001 AND line_id = 3; DELETE sales_order_items WHERE id = 2206 AND line_id = 1; DELETE sales_order_items WHERE id = 2206 AND line_id = 2; DELETE sales_order_items WHERE id = 2206 AND line_id = 3; DELETE sales_order_items WHERE id = 2206 AND line_id = 4; DELETE sales_order_items WHERE id = 2279 AND line_id = 1; DELETE sales_order_items WHERE id = 2340 AND line_id = 1; DELETE sales_order_items WHERE id = 2451 AND line_id = 1; DELETE sales_order_items WHERE id = 2451 AND line_id = 2; The DELETE for sales_order looks almost the same, except that the INNER JOIN with sales_order_items must either be removed or changed to LEFT OUTER JOIN. The reason for that is because all the matching sales_order_ items rows have already been deleted so an INNER JOIN will result in an empty result set and the DELETE will do nothing. Here’s what the DELETE for sales_order looks like with the INNER JOIN with sales_order_items removed (there’s no real point to using an OUTER JOIN): DELETE sales_order FROM customer INNER JOIN sales_order ON sales_order.cust_id = customer.id INNER JOIN employee ON employee.emp_id = sales_order.sales_rep WHERE STRING ( employee.emp_fname, ' ', employee.emp_lname ) IN ( 'Rollin Overbey', 'Philip Chin' ) AND customer.company_name IN ( 'The Power Group', 'Darling Associates' ) AND sales_order.order_date <= '2000-12-31'; The new FROM clause matches five rows; when that DELETE is executed it does exactly the same thing as these individual statements: DELETE sales_order WHERE id = 2001; DELETE sales_order WHERE id = 2206; DELETE sales_order WHERE id = 2279; DELETE sales_order WHERE id = 2340; DELETE sales_order WHERE id = 2451; The first four steps listed in Section 5.4, “Logical Execution of a Set DELETE,” can be applied to the two set-oriented DELETE statements above to produce SELECT statements that will show the rows that are going to be deleted. Here are those two equivalent SELECT statements: SELECT DISTINCT sales_order_items.* FROM customer INNER JOIN sales_order ON sales_order.cust_id = customer.id INNER JOIN employee ON employee.emp_id = sales_order.sales_rep INNER JOIN sales_order_items ON sales_order_items.id = sales_order.id WHERE STRING ( employee.emp_fname, ' ', employee.emp_lname ) IN ( 'Rollin Overbey', 'Philip Chin' ) AND customer.company_name IN ( 'The Power Group', 'Darling Associates' ) AND sales_order.order_date <= '2000-12-31'; Chapter 5: Deleting 187 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. SELECT DISTINCT sales_order.* FROM customer INNER JOIN sales_order ON sales_order.cust_id = customer.id INNER JOIN employee ON employee.emp_id = sales_order.sales_rep INNER JOIN sales_order_items ON sales_order_items.id = sales_order.id WHERE STRING ( employee.emp_fname, ' ', employee.emp_lname ) IN ( 'Rollin Overbey', 'Philip Chin' ) AND customer.company_name IN ( 'The Power Group', 'Darling Associates' ) AND sales_order.order_date <= '2000-12-31'; The following is an example where a view is used to select sales_order_items rows that are at least three years old, and a simple DELETE is then used to delete old rows where the quantity shipped was 12 or fewer. This DELETE doesn’t need a FROM clause because there’s no join involved, and a view is okay because it involves only one table and it doesn’t use any features like GROUP BY or UNION. CREATE VIEW v_old_items AS SELECT * FROM sales_order_items WHERE ship_date < DATEADD ( YEAR, -3, CURRENT DATE ); DELETE v_old_items WHERE quantity <= 12; That kind of DELETE is useful for purging old rows from the database; it can be repeatedly run, even every day, to delete rows that have become unwanted with the passing of time. 5.5 DELETE WHERE CURRENT OF Cursor This section presents an overview of how a cursor-oriented DELETE statement works. <delete_where_current_of_cursor> ::= DELETE <table_or_view_reference> <where_current_of_clause> <where_current_of_clause> ::= WHERE CURRENT OF <cursor_name> <cursor_name> ::= <identifier> defined in a cursor DECLARE or FOR statement When a cursor fetch loop is used to execute a DELETE statement using the WHERE CURRENT OF clause, the same five steps listed in Section 5.4, “Logi - cal Execution of a Set DELETE,” can be used to explain what happens. The difference is the first four steps, those having to do with the construction of a candidate result set, are now the responsibility of the SELECT statement that is explicitly defined in the cursor declaration. Only the final step, the row deletion, is performed by the actual DELETE statement. This form of DELETE does not use a FROM clause or any join operations; those go in the cursor SELECT. The DELETE must name the table or view being deleted and the cursor being used. Each time a cursor-oriented DELETE statement is executed, it deletes a sin - gle row in a single table. Here is an example that performs exactly the same delete as the example in Section 5.4; the cursor DECLARE defines a SELECT that uses exactly the same FROM and WHERE clauses: 188 Chapter 5: Deleting Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. CREATE TABLE t1 ( key_1 UNSIGNED INTEGER NOT NULL PRIMARY KEY, non_key_1 INTEGER NOT NULL ); INSERT t1 VALUES ( 1, 1 ); INSERT t1 VALUES ( 2, 2 ); INSERT t1 VALUES ( 3, 3 ); INSERT t1 VALUES ( 4, 4 ); INSERT t1 VALUES ( 5, 5 ); BEGIN DECLARE @t1_key_1 INTEGER; DECLARE @t1_non_key_1 INTEGER; DECLARE @SQLSTATE VARCHAR(5); DECLARE cloop1 CURSOR FOR SELECT t1.key_1, t1.non_key_1 FROM t1 CROSS JOIN t1 AS x WHERE t1.key_1 = 2; OPEN cloop1; FETCH cloop1 INTO @t1_key_1, @t1_non_key_1; SET @SQLSTATE = SQLSTATE; WHILE ( @SQLSTATE = '00000' ) LOOP DELETE t1 WHERE CURRENT OF cloop1; FETCH cloop1 INTO @t1_key_1, @t1_non_key_1; SET @SQLSTATE = SQLSTATE; END LOOP; CLOSE cloop1; END; When that loop runs it has exactly the same effect as the following single statement: DELETE t1 WHERE key_1 = 2; In fact, the WHILE loop makes only one pass before the FETCH sets the SQLSTATE to '02000' indicating “row not found,” even though the SELECT specifies a CROSS JOIN that generates a candidate result set containing five rows. The loop ends prematurely because the DELETE removes the base table row that appears in every row in the candidate result set, and that effectively wipes out the result set. For more information about cursor loops, see Chapter 6, “Fetching.” Chapter 5: Deleting 189 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 5.6 TRUNCATE TABLE The TRUNCATE TABLE statement deletes all the rows in a table, often much faster than the equivalent set-oriented DELETE statement. <truncate_table> ::= TRUNCATE TABLE [ <owner_name> "." ] <table_name> TRUNCATE TABLE comes in two versions: fast and slow. The fast form is used if two requirements are met: First, there must be no non-empty child tables, and second, the TRUNCATE_WITH_AUTO_COMMIT database option must be 'ON' (the default). The first requirement means that the table being truncated cannot partici - pate as the parent in any foreign key relationship where the child table contains any rows; there can be child tables, but they have to be empty for the fast form of TRUNCATE TABLE to be used. The second requirement, that TRUNCATE_WITH_AUTO_COMMIT must be 'ON', is a bit confusing. It means that if the first requirement is met, TRUNCATE TABLE will perform a COMMIT when it is finished deleting rows. It also means, again only if the first requirement is met and if a transac- tion is already in progress before TRUNCATE TABLE is executed, that a COMMIT will be issued before it starts deleting rows. If the first requirement is not met, TRUNCATE TABLE will not issue either COMMIT even if TRUNCATE_WITH_AUTO_COMMIT is 'ON'. The difference between fast and slow is striking. In one test, the fast ver- sion of TRUNCATE TABLE took 10 seconds to delete 50M of data in 30,000 rows. Both the slow version of TRUNCATE TABLE and the DELETE state- ment took four and a half minutes to do the same thing. The fast version of TRUNCATE TABLE gets its speed from the fact that it takes several shortcuts. The first shortcut, which is also taken by the slow ver- sion, is that TRUNCATE TABLE does not fire any delete triggers. If you have critical application logic in a delete trigger, it won’t get executed, and you may want to use another method to delete data. This doesn’t mean TRUNCATE TABLE bypasses foreign key checking; on the contrary, if you attempt to remove a row that is a parent in a foreign key relationship, the TRUNCATE TABLE statement will fail. That’s true even if you coded ON DELETE CASCADE; the TRUNCATE TABLE operates as if you had specified ON DELETE RESTRICT, and you cannot use it to cascade deletes from parent to child tables. By definition, of course, the fast version of TRUNCATE TABLE won’t violate referential integrity because if there are any child tables they must be empty; otherwise the fast version isn’t used. Note: If a child table is non-empty, but contains only NULL values in the for - eign key columns, it won’t prevent TRUNCATE TABLE from executing successfully because there will be no referential integrity violations. It will, however, prevent the fast version of TRUNCATE TABLE from being used simply because the child table is non-empty. This combination of circumstances means that a setting of TRUNCATE_WITH_AUTO_COMMIT of 'ON' will not be honored, and TRUNCATE TABLE will not issue any commits. 190 Chapter 5: Deleting Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. The second shortcut, also taken by both the slow and fast forms of TRUNCATE TABLE, is that the individual deleted rows are not written to the transaction log file; just a record of the TRUNCATE TABLE command itself. This means that TRUNCATE TABLE should not be used on a table that is being uploaded via MobiLink if you want the deleted rows to be included in the upload stream. MobiLink determines which rows to upload by examining the transaction log, and rows deleted via TRUNCATE TABLE will be missed. For more informa - tion about MobiLink, see Chapter 7, “Synchronizing.” The third shortcut is only taken by the fast version of TRUNCATE TABLE. It does not acquire locks on the individual deleted rows but instead places an exclusive lock on the entire table. In most cases this will cause fewer problems for concurrency because the alternatives, DELETE or slow TRUNCATE TABLE, run slower and acquire locks on every row. The fourth shortcut, also only taken by the fast version of TRUNCATE TABLE, is that extra space in the database file is not allocated for the rollback and checkpoint logs. Note: If you delete and re-insert all the rows in a large table, using DELETE or the slow version of TRUNCATE TABLE, it is entirely possible for the database file to double or even triple in size because of all the space required to hold the rollback and checkpoint logs. For more information on these logs, see Section 9.11, “Logging and Recovery.” Tip: If you are willing to commit the change after deleting all the rows in a large table, and you want to avoid having the database file grow in size, execute explicit COMMIT and CHECKPOINT statements immediately after the DELETE or TRUNCATE TABLE. These statements will increase the chances that the database engine will be able to reuse or release the extra database file space that may have been allocated to accommodate the rollback and checkpoint logs during the deletion operation. In the case of a fast TRUNCATE TABLE, an explicit COMMIT is not necessary but it will do no harm, and it’s sometimes hard to pre - dict if you’re going to get the fast or slow version. The same is true of the explicit CHECKPOINT; it may not be necessary because the database engine may decide on its own that it’s time to do a CHECKPOINT, but in that case an extra CHECKPOINT will do no harm. Note: CHECKPOINT statements can be expensive. Generally speaking, explicit CHECKPOINT statements are not required in application programs because the server does a good job of scheduling checkpoints to minimize their impact on performance. An explicit CHECKPOINT should never be used without careful consideration, especially in a busy multi-user environment. Following is a table that shows how the actions performed by TRUNCATE TABLE depend on whether there are any rows in a child table, the TRUNCATE_WITH_AUTO_COMMIT setting, and whether or not a database transaction is already in progress. Note that of the eight combinations, only two result in the fast version of TRUNCATE TABLE being used. Also note that in two of the combinations, TRUNCATE_WITH_AUTO_COMMIT is 'ON' but no commits are performed. Chapter 5: Deleting 191 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Non-empty child TRUNCATE_WITH Transaction table? _AUTO_COMMIT in progress? TRUNCATE TABLE Actions ========= ============= ============ =========================================== Yes 'ON' Yes slow TRUNCATE Yes 'ON' No BEGIN TRAN, slow TRUNCATE Yes 'OFF' Yes slow TRUNCATE Yes 'OFF' No BEGIN TRAN, slow TRUNCATE No 'ON' Yes COMMIT, BEGIN TRAN, fast TRUNCATE, COMMIT No 'ON' No BEGIN TRAN, fast TRUNCATE, COMMIT No 'OFF' Yes slow TRUNCATE No 'OFF' No BEGIN TRAN, slow TRUNCATE Note: This book assumes that the CHAINED database option is set to 'ON', and that is why BEGIN TRAN (short for BEGIN TRANsaction) operations are shown in the table above. The chained mode of operation means that any data manipulation operation like INSERT, UPDATE, DELETE, and TRUNCATE TABLE will implicitly start a database transaction if one isn’t already started, and that transaction will not normally end until an explicit COMMIT or ROLLBACK is issued. Some commands, such as CREATE TABLE and the fast version of TRUNCATE TABLE, will perform a COMMIT as a side effect. For more informa - tion about transactions, see Section 9.3. Here is an example that demonstrates how TRUNCATE TABLE works; first, two tables are created and one row is inserted into each: CREATE TABLE t1 ( key_1 UNSIGNED INTEGER NOT NULL PRIMARY KEY, non_key_1 INTEGER NOT NULL ); CREATE TABLE t2 ( key_1 UNSIGNED INTEGER NOT NULL PRIMARY KEY, non_key_1 INTEGER NOT NULL ); INSERT t1 VALUES ( 1, 1 ); INSERT t2 VALUES ( 22, 22 ); COMMIT; In the first test, TRUNCATE_WITH_AUTO_COMMIT is explicitly set to 'ON', the row in table t2 is updated, TRUNCATE TABLE is executed against table t1, and a ROLLBACK statement is executed: SET EXISTING OPTION PUBLIC.TRUNCATE_WITH_AUTO_COMMIT = 'ON'; UPDATE t2 SET non_key_1 = 999; TRUNCATE TABLE t1; ROLLBACK; After those statements are executed, t1 is empty and the value of t2.non_key_1 is 999; the TRUNCATE TABLE performed before-and-after COMMIT opera - tions and the ROLLBACK statement was completely ignored, as is shown by the corresponding entries in the transaction log: BEGIN TRANSACTION UPDATE DBA.t2 SET non_key_1=999 WHERE key_1=22 COMMIT WORK BEGIN TRANSACTION 192 Chapter 5: Deleting Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. truncate table t1 COMMIT WORK If TRUNCATE_WITH_AUTO_COMMIT is 'OFF' the result is completely dif - ferent; the ROLLBACK reverses the effects of the UPDATE and TRUNCATE TABLE statements, and the two tables contain the original rows: SET EXISTING OPTION PUBLIC.TRUNCATE_WITH_AUTO_COMMIT = 'OFF'; UPDATE t2 SET non_key_1 = 999; TRUNCATE TABLE t1; ROLLBACK; Here is what the transaction log looks like when TRUNCATE_WITH_AUTO_COMMIT is 'OFF': BEGIN TRANSACTION UPDATE DBA.t2 SET non_key_1=999 WHERE key_1=22 truncate table t1 ROLLBACK WORK Not only is TRUNCATE TABLE often faster than DELETE when you want to delete all the rows, you can also use it to speed up the deletion of large numbers of rows even when you want to preserve some of them. A three-step technique can be used: First, copy the rows you want to save into a temporary table, then truncate the original table, and finally copy the saved rows back. Here is an example of a table that was filled with 160M of data in 100,000 rows as part of a comparison of TRUNCATE TABLE with DELETE: CREATE TABLE t1 ( key_1 INTEGER NOT NULL PRIMARY KEY, inserted_date DATE NOT NULL DEFAULT CURRENT DATE, blob LONG VARCHAR ); The following set-oriented DELETE took about one minute to delete 99.9% of the rows: DELETE t1 WHERE inserted_date < DATEADD ( DAY, -7, CURRENT DATE ); The following three statements performed exactly the same function in less than half the time (27 seconds): SELECT * INTO #t1 FROM t1 WHERE inserted_date >= DATEADD ( DAY, -7, CURRENT DATE ); TRUNCATE TABLE t1; INSERT t1 SELECT * FROM #t1; Note: If the server crashes (because of a power failure, for example) immedi - ately after the TRUNCATE TABLE in the example above, but before the final INSERT t1 finishes and a COMMIT is done, you will need to restore the database from a backup to recover the rows you want to keep. That’s because the rows only exist in the temporary table and they won’t be there after recovery. Chapter 5: Deleting 193 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. For more information about the SELECT INTO method of creating and filling a temporary table, see Section 1.15.2.3, “SELECT INTO #table_name.” For more information about using INSERT to copy data from one table to another, see Section 2.2.3, “INSERT Select All Columns.” Note: Performance tests described in this book are not intended to be “benchmark quality,” just reasonably fair comparisons of different techniques. The test above, for example, was run on a 933MHz Intel CPU with 512M of cache running Windows 2000, and the sa_flush_cache procedure was called before each test to ensure fairness. 5.7 Chapter Summary This chapter described how to code simple DELETE statements that delete one or more rows from a single table and explained how a DELETE involving a multi-table join works. The full syntax of the set-oriented DELETE was described, followed by the cursor-oriented DELETE WHERE CURRENT OF and the TRUNCATE TABLE statement. The next chapter turns to the subject of application logic written in SQL, with a discussion of cursor fetch loops. 194 Chapter 5: Deleting Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Chapter 6 Fetching 6.1 Introduction This chapter starts with an example of a cursor loop involving cursor DECLARE, OPEN, FETCH, and CLOSE statements as well as DELETE WHERE CURRENT OF. This example is shown in both SQL and C using embedded SQL and comes with a step-by-step explanation of how it works. The next five sections describe the syntax of the three formats of the cursor DECLARE statement followed by the OPEN, CLOSE, and FETCH statements. The last section describes the cursor FOR loop, which can be used to simplify programming. 6.2 Cursor FETCH Loop A cursor loop is a mechanism to deal with a multi-row result set one row at a time. Depending on the cursor type, it is possible to move forward and back- ward one or more rows, to move to a row at a specific position, and to update or delete the current row. Cursor loops are often used in application programs, either explicitly in the code or implicitly by the programming environment; for example, a call to the PowerBuilder DataWindow Retrieve function might look like a single operation but behind the scenes a cursor loop is used to fill the DataWindow buffer. A cursor loop may also be coded inside a SQL stored procedure or other SQL programming block. It is constructed from several different SQL state - ments: some variable DECLARE statements, a WHILE loop, and statements to DECLARE, OPEN, FETCH, and CLOSE a cursor. The following is an example of a typical SQL cursor loop; this example is written to be short and simple while at the same time serving a useful purpose: to delete old rows from a table, limiting the total number of deletions to 1000 rows for each run and executing a COMMIT after every 100 deletions. BEGIN DECLARE @key_1 INTEGER; DECLARE @non_key_1 VARCHAR ( 100 ); DECLARE @last_updated TIMESTAMP; DECLARE @SQLSTATE VARCHAR(5); DECLARE @loop_counter INTEGER; DECLARE c_fetch NO SCROLL CURSOR FOR SELECT TOP 1000 t1.key_1, t1.non_key_1, 195 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. [...]... embedded SQL: #include #include #include #include "sqldef.h" EXEC SQL INCLUDE SQLCA; int main() { EXEC SQL BEGIN DECLARE SECTION; long key_1; char non_key_1 [ 101 ]; char last_updated [ 24 ]; EXEC SQL END DECLARE SECTION; char copy_SQLSTATE [ 6 ]; long loop_counter; ldiv_t loop_counter_ldiv; db_init( &sqlca ); EXEC SQL CONNECT USING 'ENG=test6;DBN=test6;UID=DBA;PWD =SQL' ;... batch files to explicitly specify SQL Anywhere 9 program locations This is easier than hard-coding the whole path It is also safer than relying on the PATH, which may have entries for different versions of SQL Anywhere The SQL Anywhere Studio 9 setup process creates the ASANY9 environment variable to contain the software installation path; e.g., C:\Program Files\Sybase \SQL Anywhere 9 Here is an example... "COMMIT after %d rows.\n", loop_counter ); } EXEC SQL FETCH c_fetch INTO :key_1, :non_key_1, :last_updated; strcpy ( copy_SQLSTATE, SQLSTATE ); } // while EXEC SQL CLOSE c_fetch; EXEC SQL COMMIT; EXEC SQL DISCONNECT; db_fini ( &sqlca ); printf ( "Done after %d rows.\n", loop_counter ); return ( 0 ); } // main Note: This book doesn’t cover embedded SQL in any great detail The example above has been... number of databases SQL Anywhere Studio 9 ships with the components you need to make use of Replication Server, but not Replication Server itself, and for that reason it isn’t covered in this book SQL Remote and MobiLink both work by occasionally copying data between a single consolidated database and a virtually unlimited number of remote databases Both products ship with SQL Anywhere Studio 9, and both... available SQL Anywhere Studio 9 offers four ways to implement distributed data: proxy tables, Replication Server, SQL Remote, and MobiLink Proxy tables provide real-time access to data in different locations as if it were all stored in one database This feature is described in Section 1.14, “Remote Data Access.” Replication Server provides near-real-time copying of data among a small number of databases SQL. .. :last_updated; strcpy ( copy_SQLSTATE, SQLSTATE ); loop_counter = 0; while ( strcmp ( copy_SQLSTATE, "00000" ) == 0 ) { loop_counter = loop_counter + 1; printf ( "Deleting %d, %d, '%s', %s\n", loop_counter, key_1, non_key_1, last_updated ); EXEC SQL DELETE t1 WHERE CURRENT OF c_fetch; loop_counter_ldiv = ldiv ( loop_counter, 100L ); if ( loop_counter_ldiv.rem == 0 ) { EXEC SQL COMMIT; printf ( "COMMIT... superior_list.level DESC FOR READ ONLY; OPEN c_fetch WITH HOLD; FETCH c_fetch INTO @level, @name; SET @SQLSTATE = SQLSTATE; SET @loop_counter = 0; WHILE @SQLSTATE = '00000' LOOP SET @loop_counter = @loop_counter + 1; MESSAGE STRING ( @level, ' ', @name ) TO CONSOLE; FETCH c_fetch INTO @level, @name; SET @SQLSTATE = SQLSTATE; END LOOP; CLOSE c_fetch; END; 6.2.2 DECLARE CURSOR USING Select The query used for a... DECLARE @key_1 DECLARE @non_key_1 DECLARE @last_updated DECLARE @SQLSTATE DECLARE @loop_counter INTEGER; VARCHAR ( 100 ); TIMESTAMP; VARCHAR ( 5 ); INTEGER; DECLARE c_fetch NO SCROLL CURSOR USING @select; OPEN c_fetch WITH HOLD; FETCH c_fetch INTO @key_1, @non_key_1, @last_updated; SET @SQLSTATE = SQLSTATE; SET @loop_counter = 0; WHILE @SQLSTATE = '00000' LOOP SET @loop_counter = @loop_counter + 1; DELETE... DECLARE DECLARE DECLARE DECLARE @key_1 @non_key_1 @last_updated @SQLSTATE @loop_counter INTEGER; VARCHAR ( 100 ); TIMESTAMP; VARCHAR ( 5 ); INTEGER; DECLARE c_fetch NO SCROLL CURSOR FOR CALL p_oldest ( 6 ); OPEN c_fetch WITH HOLD; FETCH c_fetch INTO @key_1, @non_key_1, @last_updated; SET @SQLSTATE = SQLSTATE; SET @loop_counter = 0; WHILE @SQLSTATE = '00000' LOOP SET @loop_counter = @loop_counter + 1;... them as errors, you have to add code to do that Here is an example of an OPEN statement followed by an IF statement that turns any SQLSTATE other than '00000' into an error: OPEN c_fetch WITH HOLD; IF SQLSTATE '00000' THEN RAISERROR 20000 STRING ( 'Cursor OPEN SQLSTATE = ', SQLSTATE ) END IF; For more information about the RAISERROR statement, see Section 9.5.2, “RAISERROR and CREATE MESSAGE.” 6.2.5 . ); } EXEC SQL FETCH c_fetch INTO :key_1, :non_key_1, :last_updated; strcpy ( copy_SQLSTATE, SQLSTATE ); } // while EXEC SQL CLOSE c_fetch; EXEC SQL COMMIT; EXEC. copies the value of SQLSTATE into the local variable @SQLSTATE. This kind of assignment is good practice because many SQL statements change SQLSTATE and this