LabVIEW

cancel
Showing results forΒ 
Search instead forΒ 
Did you mean:Β 

Get generated value of a new dataset

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
Using LV8.0
--------------------------------------------------------------------
Don't be afraid to rate a good answer... πŸ˜‰
--------------------------------------------------------------------
0 Kudos
Message 1 of 7
(2,752 Views)
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
Message 2 of 7
(2,735 Views)
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
Using LV8.0
--------------------------------------------------------------------
Don't be afraid to rate a good answer... πŸ˜‰
--------------------------------------------------------------------
0 Kudos
Message 3 of 7
(2,731 Views)
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
Message 4 of 7
(2,719 Views)
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
Using LV8.0
--------------------------------------------------------------------
Don't be afraid to rate a good answer... πŸ˜‰
--------------------------------------------------------------------
0 Kudos
Message 5 of 7
(2,709 Views)
> 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
Message 6 of 7
(2,697 Views)
> 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
Using LV8.0
--------------------------------------------------------------------
Don't be afraid to rate a good answer... πŸ˜‰
--------------------------------------------------------------------
0 Kudos
Message 7 of 7
(2,687 Views)