Get generated value of a new dataset

[becktho]

Hi guys

I'm working on a project, which needs a database. So far it's no problem, because it's not the first LV-App which uses a DB. 😉

I want to store a dataset in a certain table. In this table I have an Index-column with automatically generated values (each time a new dataset is stored).
If I now store a new dataset - is it possible to get this generated value?

I'm using NI's Database Connectivity Toolset.

Thanks in advance

Thomas

[Johnner]

I think this will be database-specific.

I just verified that "SELECT LAST_INSERT_ID()" will return the last auto-increment value used if you're talking with a MySQL back end. That's the only DB I have access to. Some Googling suggests that "SELECT SCOPE_IDENTITY()" will do the same thing with SQL Server; armed with those examples, you can probably track down similar functions for other databases.

Maybe you could just get away with doing a query to get the maximum ID value for a given table?

Regards,
John

[becktho]

Hi John

Sorry I forgot to mention that I use MS Access. There are definitely some workarounds as you suggested.

Due to the fact that we spent money for the DB Connectivity Toolset with its nice vis, which were good enough up to now, I was wondering if such functions (as you mentioned them) are available there.

But it seems that I can't get rid of a simple workaround. 😉

Thanks

Thomas

[Johnner]

Thomas,

For the sake of completeness, I wanted to mention that the relevant query for the Access database engine is "SELECT @@IDENTITY". Though I do have Access, I think I've been subliminally conditioned by the anti-MS forces out there to avoid thinking of it when I think of databases! I tested this and it seems to work as advertised (see attached).

It might be nice if NI's toolkit had wrapper functions that abstracted various bits of database-specific functionality, but it probably makes sense for them to stick mostly to a standards-based approach and let the user implement things that are more proprietary. The average application probably ends up more robust that way.

Take care,
John

[becktho]

Hi John

That looks interesting. But from which table do I get this value? Is it the last auto-incremented value from over all the tables?

According to your picture, it still seems that I have to use two statements. One to insert data and a second to retrieve this value. I'd prefer a function which I can tell to insert data and which returns the auto-incremented value.

Thanks

Thomas

[Johnner]

> Is it the last auto-incremented value from over all the tables?

It's the last auto-incremented value from the session associated with the connection reference used. So, if you update multiple tables using the same connection reference, then the value you get back from the function is indeed going to refer to whatever (arbitrary) table was most recently updated.

If you use a separate connection reference to manage each table, then you can be sure that the value returned is associated only with a particular table (and always with only your session, so agents writing to the database in parallel with you shouldn't cause a problem).

With regard to getting this value with only one statement: I don't think (but I'm not sure) that such a primitive exists with the database itself, so I suspect any single-function insert that returns the new ID will always just be a wrapper for multiple statements anyway.

--John

[becktho]

> just be a wrapper for multiple statements

I already expected something like that, but it's still more comfortable to just take such a function out of the palette than create it ;).

But anyway - the rest helps me a lot. 😄

Many Thanks

Thomas