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

boogabooga Member Posts: 3
edited 2013-10-29 in Navision Attain
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!

Comments

  • ara3nara3n Member Posts: 9,249
    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
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Arjan_SomersArjan_Somers Member Posts: 64
    Or take a look at codeunit 396, wich contains the number series manager.
  • boogabooga Member Posts: 3
    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!
  • jevansiojevansio Member Posts: 1
    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
Sign In or Register to comment.