About EasyMorph Tutorials & Examples Web-help

Select Matching Database Rows action issue

I loaded an excel file with one column - a list of ids. When I try to add the action “Select Matching Database Rows”, I cannot add the pair of columns that I try to match. What have I done wrong.

A column with the list of IDs should be in the input dataset. Therefore, place “Select Matching Database Rows” in the same table where the “Import from Excel” is, right after it. Otherwise, it won’t “know” where to get a list of IDs.

I try it again today. I selected Table 1 with the id column. Then I clicked add “Select Matching Database Rows” action. Instead of of adding the action next to Table 1, a new table popup for “Select Match Database Rows”. I could not get the “Select Match Database Rows” action to show up in Table 1.

Just drag the action into the desired location.

help

Wow. That is easy. I got it working! Thank you.

Looks like this breaks the normal pattern of adding an action by clicking “Add new Action” in the “Actions in Table” list. When I do that it pop-up import table instead of adding an action to the existing table.

2018-08-10_16h31_32

Now I am encountering a strange error. The select table action try to create a table in the database server.
2018-08-10_16h48_46

Select matching database rows action creates a temporary table because of performance reasons. Create temp table will be deleted at the end of this action workflow.

Because of the differences in temporary tables behavior in different database engines, we are using a common table instead of an actual temporary table.

So you’ll have to grand your MySQL user CREATE privilege (along with INSERT, SELECT and DROP privileges) in order to make this action to work.

Thanks for the explanation. Now it makes sense. I cannot use this because I do not have full CRUD privileges on the db server. I will work around by importing data into Easymorph and than run a left join.

I have a similar issue - “cannot execute CREATE TABLE in a read-only transaction”

Is that also because I don’t have (and will never get) CREATE privileges?

If so, how do I work around this?

Hi Hendrik,

Most likely - yes. CREATE privileges are required for the action to work.

Unfortunately, there is no universal and performance efficient workaround.

If the data which you want to match with is present in the same database as the target table,
you can use a custom SQL query to load matched rows.

Otherwise, if the number of rows in the EasyMorph table is relatively small,
you can use the “Iterate” action to import database rows, matching each of the rows of the EasyMorph table.

And if the target database table is not very big, you can load the whole table and then use the “Keep matching” action to select matching rows.

Thanks

Could you not create the temporary table in SqlLite (or MS Access a free and open database) locally?

{I just saw your posted workaround - which does what I suggested}

  1. “Database command” (to create a temporary DB table)
  2. “Export to database”
  3. “Import from database”. Here you can use a custom SQL statement with SELECT * WHERE … EXISTS
  4. “Database command” (delete the temporary DB table)

Hendrik, a temporary table is required for performance reasons.

If performance is not a problem for you, you can just use the “Import from database” and “Keep matching” actions instead of the “Select matching DB rows” action.

SQLite doesn’t support users and permissions. So you should be able to use the “Select matching DB rows” action with SQLite in any case.

As for the workaround which you mentioned - it does create a temporary table.

To learn more about EasyMorph visit easymorph.com.