06-07-2021 02:25 PM
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
Solved! Go to Solution.
06-07-2021 02:38 PM
I think the MySQL driver has an option to enable multiple statements in the same query. Check to see if this option is enabled.
06-07-2021 02:42 PM
DSN = "Driver=MySQL ODBC 5.2w Driver; SERVER=localhost; UID=root; PWD=pwd; DATABASE=default_db; PORT=3306; BIG_PACKETS=1; MULTI_STATEMENTS=1"
06-08-2021 04:26 AM - edited 06-08-2021 05:07 AM
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
06-08-2021 06:43 AM
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
06-08-2021 08:46 AM
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);
06-09-2021 09:46 AM
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.
06-09-2021 01:47 PM - edited 06-09-2021 01:56 PM
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.