Dynamic Data Type

[Anonymous]

Hello,

Trying to bring in data from MS SQL Server into MySQL using Dynamic Datatype. Lot of fields are ntext field on source tables. 

1) What could be the equivalent data type in MySQL? Will LONGTEXT help?

2) How to use this with Dynamic? (I prefer Dropping and Creating new tables every time I run the job. This way, the job handles the metadata of source and target tables instead of us having to maintain the target tables.)

MS SQL Server -> select * from sourcetable -> MySQL -> Target table

Thanks,

Sachin

[manodwhb]

@sachinattalend, you need to use it as a string data type in Talend.

[Anonymous]

Talend could not make ntext of MS SQL Server to automatically convert to its corresponding datatype in MySQL. 

This is what we did: 

- Identified all ntext columns in MSSQL (backup database) and convert to text data types

- Ran jobs using Dynamic. 

[JohnRMK]

Hello,
Unfortunately the dynamic schema has some limitations including this one and the nvarchar (max) for example in sql server.

To help you move forward, here is an approach that I have used in several projects and that works perfectly.

-You must create the target tables before retrieving the data with the dynamic schema ==> You can use  this script to do the same work https: // dataedo .com / kb / query / sql-server / list-columns-names-in-specific-table (execute it in a tDBInput connected to the source DB)

-You must write a simple java routine, which takes as a parameter a column name, size, type ... and returns a character string formatted in mysql

==> sqlToMysqlFormat (String name, String Type, Int precision, Int length, boolean isNull)
Ex: sqlToMysqlFormat (FirstName, nvarchar, 0, 50, false)
Must return "FirstName CHAR (50) NOT NULL"

The example routine below allows me to convert Salesforce fields to SQL Server. You have to do the same thing nothing complicated. (http://www.sqlines.com/sql-server-to-mysql)

This routines must be applied to each attribute (in tMap after retriveing all columns list ) and then you group in a list separated by ',' (tAggregatRow group by table name and ) to get a table creation query that you will inject into a tDBRow (mysql) just before recovering data with the dynamic schema.

After this you will have something like query="CREATE TABLE tableName (Id int .. , ....)"

You can delete the table if it already exists.

Tell me if you want more detail, or if you didn't understand a point

[Anonymous]

Hello @JohnRMK 

Thanks , It's really very helpful for me . I am new to talend.

could you please let us know what are the column you used in tmap and what you applied.

[JohnRMK]

Hello, 

input: 

-TableName

-ColumnName

-ColumnSize

-ColumnType

...

Output

-TableName

-ListFieldsForScript<==> TalendRoutinesFormatSqlToMySQL(ColumnName,ColumnSize,...)

Basically, to summarize, it takes as a parameter the name of the table and all the columns and it returns the table name and a request formatted using the routine.

After tAggregatRow, You add CREATE TABLE TableName ( ListFieldsForScript );

Then you inject that into your db with tDBRows