MySQL SELECT LAST_INSERT_ID() After Inserting Items to Database

[Scot07]

Hi all,

I am attempting to write to two separate tables on a mySQL database. The first table will be a list of customer details, and the second will be a record of transactions. These will be linked by a unique key called customer ID. 

I can insert the data into each table but I cannot appear to run the SELECT LAST_INSERT_ID(); statement required to get the unique customer ID key to be used for the transaction table. 

I have ran the same statement in PHPMyAdmin and this runs OK, inserting into the customer table and then returning the customer ID key which could then be used in the transaction table. 

I have also tried running the Select statement as a separate execute statement but this also does not work. 

Has anyone been able to perform this action through LabVIEW or are there any known workarounds to return the unique key after inserting into a table?

Thanks.

Scot07

[aputman]

I think the MySQL driver has an option to enable multiple statements in the same query.  Check to see if this option is enabled.

[aputman · Accepted solution]

DSN = "Driver=MySQL ODBC 5.2w Driver; SERVER=localhost; UID=root; PWD=pwd; DATABASE=default_db; PORT=3306; BIG_PACKETS=1; MULTI_STATEMENTS=1"

[Scot07]

Thanks,

I have now enabled this and no longer getting an error being returned from the query.

However, I am not getting any recordset data being returned from this query. I would have expected the customer ID key to be returned. 

I have moved to the second recordset (I imagine the first is for the INSERT) and getting a single element back but no value. If I change the SELECT statement to return some other data, it returns this OK but is not returning anything on the SELECT LAST_INSERT_ID(); statement. 

Thanks,

Scot07

[JensG69]

Hi Scot07,

how is the column "Customer ID" from your table "Customers" defined within MySQL?

It should be AutoIndexing, so that your command SELECT LAST_INSERT_ID() gives a result. It is also common to NOT include a AutoIndex-Column in an Insert-Command. I am very astonished that your Insert "NULL" for that column does not throw an error.

Regards, Jens

[aputman]

Are you really storing the cost as a string?  and why do you insert the customerID into the transactions table as a string?  

Why not do it this way:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

INSERT INTO Transactions (CustomerID, Item, Cost) VALUES (LAST_INSERT_ID(), 'Bread', 1.50);

[Scot07]

I have figured out what the issue was - turns out the data was being returned correctly but I had to convert the variant to data for it to be displayed. 

The first column is auto-indexing so that was not an issue. I've always inserted into the table with column 1 as NULL with no issues. 

The data in this is just an example. I cannot disclose the real data for obvious reasons. 

[aputman]

I'm glad you figured it out but I think my last post is the better way to handle this.  There is no need to query the ID back into Labview only to send it back to the database in the form of another query.  Send both queries at the same time.