How to increment count id for each insert and every loop iteration?

Question

I have for loop that should increment count_id for every query and each loop iteration. Here is example of my code:

qryCode = queryExecute("SELECT max(display_order) AS maxdisplay FROM type_ref",{},{datasource: application.datasource}); // Result set of the query is: 58 
qryItems = queryExecute("SELECT DISTINCT type_id, label, type_shortcode FROM types gtr WHERE item_id = :item_id",{item_id: {cfsqltype: "cf_sql_numeric",value: arguments.item_id}},{datasource: application.datasource}); 

// Result set of qryItems:
TYPE_ID  LABEL    TYPE_SHORTCODE
1        2012-1   HOA
2        2012-1   HOC
5        2012-1   HOR

local.display_count = qryCode.maxdisplay;

for ( row in qryItems ) {
    local.sqlInsert &= " INSERT INTO type_ref (display_order) VALUES (?) ";
    qryParams.append({cfsqltype="CF_SQL_NUMERIC", value: display_count+1});
    local.sqlInsert &= " INSERT INTO type_ref (display_order) VALUES (?) ";
    qryParams.append({cfsqltype="CF_SQL_NUMERIC", value: display_count+2});
    display_count++;
}

The code above will increment first two values correct (59 & 60) but for the second iteration it will start from 60 instead of 61. The code should produce count_id's int his order: 59,60,61,62,63,64. There are three records in qryItems. The qryCode has max value of 58. The first query in first iteration should start from 58 + 1 = 59. The next one should be 58 + 2 = 60. In the second iteration the first count_id should be 61 and so on. I'm not sure why the code I have above starts second iteration from 60 instead of 61. I do have this line that should increase the count_id at the end of each iteration: display_count++;.

Edit - You could also just insert the select max() directly, instead of pulling it back into a query object. Just keep in mind your approach is NOT thread safe. Without locking, multiple requests might get the same order value under load... If that is a problem for your app, use a sequence instead. Also, use transaction so all of the inserts are rolled back if an error occurs. — SOS, Jun 03 '20 at 01:39
@Agreax I use Sybase ASE and sequence is not supported. The alternative I guess would be to use `identity`. That would make the `display_order` column increase the value automatically every time new row is inserted. I agree that transaction should be applied in this case. — espresso_coffee, Jun 03 '20 at 02:04
Oh, I thought ASE did support them? Yeah, an identity column would work, assuming the table doesn't already have one. IIRC, the limit us one per table. — SOS, Jun 03 '20 at 14:21
@Agreax Yes, identity is supported. The only issue is that you only can have one identity column per table as you mentioned. — espresso_coffee, Jun 03 '20 at 14:41
Been a while since I used Sybase, so I'll have to take you word for it ;-) Thought I saw 'create sequence' in the Sybase docs, but only might be for certain versions. — SOS, Jun 03 '20 at 18:08

score 3 · Accepted Answer · answered Jun 02 '20 at 20:29

It's because you're doing 2 inserts per iteration, therefore you should increment display_count by 2 instead of 1. So your for loop should look like this instead.

for ( row in qryItems ) {
    local.sqlInsert &= " INSERT INTO type_ref (display_order) VALUES (?) ";
    qryParams.append({cfsqltype="CF_SQL_NUMERIC", value: display_count+1});
    local.sqlInsert &= " INSERT INTO type_ref (display_order) VALUES (?) ";
    qryParams.append({cfsqltype="CF_SQL_NUMERIC", value: display_count+2});
    display_count +=2;
}

score 2 · Answer 2 · answered Jun 02 '20 at 22:37

How about

for ( row in qryItems ) {
    local.sqlInsert &= " INSERT INTO type_ref (display_order) VALUES (?),(?) ";

    qryParams.append({cfsqltype="CF_SQL_NUMERIC", value: ++display_count});
    qryParams.append({cfsqltype="CF_SQL_NUMERIC", value: ++display_count});

}

Also see: Inserting multiple rows in a single SQL query?

How to increment count id for each insert and every loop iteration?

2 Answers2