C/ODBC - alternative to TOP / LIMIT / MAX() ?

booga

Hi,

First time poster, so please forgive any faux paus .

I'm trying to add a record to the end of a table via C/ODBC. As far as I can see, there is no auto increment mechanism, so it looks like I'm going to have to determine the last primary key value, and do my insert with that value + 1.

'SELECT MAX(id)' gives me the result I need, but it consistently takes between 20 and 30 seconds to process the query - which is a bit of a problem. The table in question has around 27,000 rows.

'SELECT TOP n' and 'LIMIT' don't seem to be supported in C/ODBC.

So my question is: does anyone know of an alternative way to get the last row in a table, or determine the highest value of a field, hopefully a quick method than MAX(id)?

I'm running Navision 3.01

Cheers!

ara3n

you could add a new field (boolean) in the table. and from navision as new records get inserted, to update this field and uncheck the previous record. That way you can filter on this new boolean field and get your last row in a table

Arjan_Somers

Or take a look at codeunit 396, wich contains the number series manager.

booga

The boolean idea is rather smart, might give that a go. Unfortunately I don't have the requiste license granule to view codeunits, which is a fairly major PITA

Thanks both!

jevansio

Incase anyone is looknig for a generic way to solve this, the ODBC API call SQLGetInfo(handle, SQL_KEYWORDS, ...) gives a comma seperated list of keywords supported by the ODBC driver. MS Access includes TOP in this list, whereas others (SQLite for example) don't. You can use this to programatically construct your SQL without knowing the underlying ODBC database type. i.e

If keywords contains "TOP" then
   sql = "SELECT TOP n * FROM <table>"
else
   sql = "SELECT * FROM <table> LIMIT n"

See MSDN for SQLGetInfo parameters and usage http://msdn.microsoft.com/en-us/library ... 81(v=vs.85).aspx

The DBMS name is also available from SQLGetInfo(handle, SQL_DBMS_NAME, ...) which returns "ACCESS" for MS Access & "SQLite" for SQLite. If you're using a driver which is giving incorrect keywords back you could additionally use the specific DBMS name to determine which type of syntax to use

Jay