[resolved] Can MySQLOutput Create a table with an auto_increment Column?

[Anonymous]

Can MySQLOutput Create a table with an auto_increment Column?
The table schema that I need is very simple
CRefID INT(10) auto_increment primary_key
DB1LeadID INT(10)
DB2LeadID INT(10)
Completed INT(1)
How can I get this schema, without executing SQL ahead of time?

[Anonymous]

Yes,
Don't define CRefID in the schema. Instead use the Advanced settings / Additional columns. Add in the full definition including the type and primary key flag. Pay attention to when quotes are used. For example, this job has a set of columns defined in the schema starting with "Business_Name". 'ai_contact_id' is an auto_increment primary key.
Additional screenshots with an example for creating a UNIQUE index in MySQL: http://bekwam.blogspot.com/2011/10/controlling-ddl-behind-talend-open.html.
-Carl

[Anonymous]

Thanks for your post walkerca
That looks like what I am looking for, but I don't have that option in my MySQLOutput component. Is that a custom component?
My "About Talend Open Studio" page shows this:
-----------------------
"Talend Open Studio"
Version: 4.2.2
Build id: r63143-20110622-0628
-----------------------

Also, I would like to read your blog, but the page you have linked is missing. Do you have another link?
Thank you again for your time.

[Anonymous]

Hi,
The link's ok, but a period was added at the end.
http://bekwam.blogspot.com/2011/10/controlling-ddl-behind-talend-open.html
4.2.2 has the same component. You have to check the "use field options" box in order to display the insert/update configuration.
Here's a cross reference of the Talend articles on the blog: https://sites.google.com/site/bekwam/home/talend . (period spaced properly)

[Anonymous]

Thanks for fixing the Blog Links.
I figured out what I did wrong. You have to set the "Action on Table" setting to a create setting for the "Data Type Column" to Appear.

[Anonymous]

Ok, so for any newbies trying to make this work.....

To make an autoincrement column in Talend, do the following:
1. Place a tMysqlOutput component.
2. Change the "Action on Table" setting to a create setting, ie "Drop and Create". This enables the "Data Type Column" to Appear.
3. Click on the advanced settings tab
4. Add an additional column
5. Set the following for the Auto_Increment column
Name: "TableID" <---Don't forget the quotes
Data Type: INT(10) PRIMARY KEY NOT NULL auto_increment
SQL expression: 0 <---No quotes
6. Field Options: the fields must be updatable and insertable

Thanks everyone!

[Anonymous]

Glad it's working. I added your Step #2 to the blog post. Thanks.

[Anonymous]

Thanks, this helped.