How Do You Auto Increment the Key Column with TopSpeed's ODBC Driver

Does anyone know how to Auto Increment the key column when using the TopSpeed ODBC driver?

When the project’s circumstances are appropriate, we utilize Windows PowerShell and the TopSpeed ODBC driver. We’ll do some lightweight queries, updates, and inserts. On insert, we’re ok if the key is natural. However, I have yet to figure-out how to Auto Increment the key column.

Thanks for the help.

Since there’s no “server” to manage that stuff for you, you need to manage the autoinc yourself similar to how ABC and the Clarion templates do.
Basically, you get the highest existing increment value, then add 1. Then try to add.

Thank you for the reply. I tried doing what you suggested by executing a MAX() on the key column and bumping that value by 1. It worked, but it was too slow. Do you have any other ideas on getting the highest existing increment value?

You can CLEAR the record to a high value and do a previous to get the max value but I question whether this will be any faster.

Thank you for your reply; it is appreciated. I’m thinking you can’t do a CLEAR() or PREVIOUS() in SQL commands with the ODBC driver.

My apology. My head only registered TopSpeed, not ODBC.
In that case, max is the way to go unless of course you allow the server to do the autoinc for you.
Is that not an option for you or are you having Clarion problems with it?

No worries. Just to make sure I understand, when you suggest allowing the server to do the autoinc, do you mean using Clarion code (ABC Insert)? If so, it’s definitely an option. I was just hoping to do the autoinc from PowerShell on occasion too.

Okay, I’ve been a little slow here in recognizing/remembering the back to the TopSpeed ODBC.
What happens if you have a DESC primary or alternate key in the TopSpeed file and your query is something like:
SELECT Id
FROM Mytable
WHERE Id < (a very high number)
ORDER BY IdDescKey
LIMIT 1;

I tried a select with a desc order on the key. But I couldn’t get the LIMIT to work; all records are returned. I am thinking LIMIT isn’t supported. I may just have to either bail on the idea or use a solution involving MAX.

I really appreciate your help.

Assuming you are running some sort of process, get the MAX value once store it in a local variable that you increment by one each time you add a record.

Good suggestion. Thank you.