MySQLBulkWriter to return newly generated ids

Follow

Answered

nismon8er

• April 21, 2021 00:00

Is there any way to have MySQLBulkWriter to return the ids of the newly added records.

So for example I have a table like so:

| id | name | last | company | position |

and I have a flat file that has data like so:

^^Joe^^Johnson^^Company ABC^^Manager^^
^^Mike^^Mills^^Company 123^^Staff^^
^^Dale^^Dwells^^Company ABC^^Staff^^

So I have my bulk writer setup to read it with the ^^ delimeter and it inserts the records perfectly

| id | name | last | company | position |
| 5 | Joe | Johnson | Company ABC | Manager |
| 6 | Mike | Mills | Company 123 | Staff |
| 7 | Dale | Dwells | Company ABC | Staff |

Is there anyway that the bulkwriter can return to me a flatfile like this

5
6
7

or maybe

5,6,7

?

0

• 

  admin

  • May 10, 2021 10:07

  Hi,
  
  The way the MySQLBulkWriter and DatabaseWriter are designed, the components can only send to the output the values that have not been loaded correctly to the database. Also, as I understand your situation, the ID column here is being produced by the database itself (auto-increment field or so), is that right? Therefore the component would need to read from the database after the data has been loaded. If that is fine with you and you believe it is still beneficial for you to read the data afterward, you might want to implement the so-called "incremental reading" and use DatabaseReader to get your newly created records. Having this function set in a reader component, with each run the last read value is stored in a file therefore the reader then knows what is the next value that hasn't been read before.
  
  I have created an example graph for your review. In the beginning, I am simply loading some records to the database. Then I am reading from the database using the "incremental reading" function (in a different phase). If you would like to run the graph as well, you might need to set up the same database table and change the connection setups, therefore I am also attaching a screenshot of my database table. Please review mainly the SQL query in the DatabaseReader as there is one increment-related amendment (it is "where `test`.`autotable`.`ID` > #key1" in my case) and then the Incremental file and Incremental key has to be set as well.
  
  Best Regards,
  Eva

  • IncrementalReading.grf
  • table_structure.PNG

  0

  Comment actions Permalink
• 

  admin

  • May 18, 2021 13:06

  Hi there,
  
  Although I am aware of the fact that you are mainly interested in the MySQLBulkWriter, I have realized that I have misled you regarding the usual DatabaseWriter. The function that you are describing is, in fact, available when writing to the database using the DatabaseWriter.
  
  Therefore in addition to my previous post, I am attaching an example of using the so-called "Returning statement" in SQL query. In the graph attached, you should only change the Connection to some MySQL testing database available and after that, the graph is fully runnable. It will create a testing table in your database and then show you how it works when writing the values into a table with AUTO_INCREMENT column in it.
  
  The main part of this solution is the SQL query within the DatabaseWriter:
  

  INSERT INTO \`myUsers\` (\`firstname\`, \`lastname\`) 
  VALUES (?, ?) RETURNING $inserted_id:=AUTO_GENERATED

  
  
  You can find more details on the documentation page regarding the DatabaseWriter.
  
  Best Regards,
  Eva

  • ReturningStmnt.grf

  0

  Comment actions Permalink

Please sign in to leave a comment.