Export mysql dataset to dataset with new column names

Ignition
1

I have a Ignition project (Ver. 7.7.1) where I store data and manage recipes in a mysql database.
Now my customer wants to have the column names of the tables changed.
Inside Ignition I used the table column headers to “change” the column names but my customer also uses other front end software to work with the mysql data and there they still get the original column names.
Is there a way to export or copy the original tables to new tables with different column names?
(system.datasettoDataSet)

Thanks.

1 Like
2

I would think you should do this in the mysql management interface, workbench.

3

I only know some workbench basics.

I think I have to keep the original column names in the tables Ignition is writing to. Ignition adds a new line with about 200 values to these tables approximately every 45 seconds.

My idea was to create a new database table with the column names my customer wants and somehow synchronize it constantly with the original table.

Can this be done in workbench or is there any other way in workbench to have new column names without compromising the function of the Ignition project?

4

id say the proper thing to do is to change the column names in workbench and then change the column names in whatever script you are using in ignition.

Im not a sql expert but I guess you could also create a view in mysql based on your original table for the customer, or maybe use a stored procedure to update the new table.

5

Which does bring up a good question. Are you using a script or a transaction group to store data?

6

A transaction group.

7

Creating a view is easy and should work for you if you are dead set against just changing the column names.

mysqltutorial.org/create-sql ... mysql.aspx

your query to create the view may look something like this

Like this you would have the customer query the view instead of the table that Ignition is writing to. Outside of that change the column names in mysql or just have them rename the columns in the query when they pull the info out of the table.

8

diat150’s use of a view would probably be the best way to go. But in the interest of completeness and possibly billable hours… :laughing:

You could create another group to maintain a second table, but really, it’s wasted space.

Here’s what I would do to make it as transparent to the customer as possible, and maintain just one table. And not use a view. Not for the faint of heart. :wink:

  1. To the existing TG, add the columns with the new names. Add them, not replace the old ones.

  2. Use an update query to copy the values from the old columns to the new ones that now have a bunch of null entries at the top. While this expands the size of your table (maybe by quite a bit, depending on the number of new columns), but it will solve the immediate issue.

Now for the cleanup:

  1. In the Ignition project, update your queries to reflect the new columns.

  2. Remove the old columns from the transaction group.

  3. Using the MySQL Workbench, remove the old columns from the db table.

Is it a bunch of work? Yep. Is it annoying to do? Yep. Almost as annoying as having to rename columns on the fly in order to support their non-Ignition stuff without breaking the Ignition stuff.

This, however, will give you the best shot at keeping things going smoothly and still keep your table size the same.

I recommend copious amounts of notes before-- and even while-- you’re doing this.

9

I had to go with renaming the tags in the project and creating new tables using the transaction groups.
I did this not so much for technical reasons, but that was what my customer wanted.
The find and replace function in ignition took care of almost everything.

Thank you for your help.