tMysqlOutput rows have Null string and date values, 0 integer values

One Star

tMysqlOutput rows have Null string and date values, 0 integer values

Hi,
I am new to Talend, and am a bit confused.

I have been able to create ?Job Design? wherein 5 tables (all using TMySqlinput component) from one MySql DB (accessed via metadata > Db connections) are linked together via a tMap and the tMap in turn is linked out to single destination table (using TMySqlOutput component) in another MySql DB (accessed via metadata > Db connections). Then I have mapped the fields from the source 5 tables as expressions into the single destination table.

When I run the Job, it runs OK, and it processes 2402 rows, but all rows in destination table (in 2nd MySql DB) have 0 for integer values and NULL for string values and date values!

To troubleshoot, I even created a tLogrow and mapped some fields form the source tables to it, and the Job execution panel shows those fields as Null also!

Does anyone know how to fix this?

Thanks

Re: tMysqlOutput rows have Null string and date values, 0 integer values

first make sure your tMap join is successful, then check the source data for those rows.

a quick check here would be to check "inner join" on your tMap input tables. if you get less than 2402 rows, your join is not succeeding.
One Star

Re: tMysqlOutput rows have Null string and date values, 0 integer values

I made sure the "inner join" is ON for all input tables, and still get NULL vlaues. In fact, before I built this job, I wrote the MySql query in the native DB, and it also returns 2402 rows, all with proper values. Is there a way yo see the resutls of the query (into source/input tables) inside the tMap or anywhere in the Job?

Could a wrong version of Java isntalled on my desktop result in tMySqlinput component to not work properly?

Thanks
Community Manager

Re: tMysqlOutput rows have Null string and date values, 0 integer values

Hello
Before tMap, using a tLogRow to see if select the correct records from db, eg:
tMyInput--tLogRow--tMap----
or on tMysqlInput, open the sql builder and run the query, review the result.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tMysqlOutput rows have Null string and date values, 0 integer values

Thanks for the suggestion. I did use the Sql query window to verify that all input tables do return all fields (for 100 sample rows). But, even when I simplify my Job to just do an inner join between the Main input table and the fisrt lookup table, and use tMap to join the 2 tables tohgether (with both "Unique match" and "inner join" ON), then send the output to a tLogrow (dispalying only 2 fields, one string, other short interger), the results displayed in the Job execution window still come as null (for string) and 0 for short integer.

Any other ideas? Thx

Re: tMysqlOutput rows have Null string and date values, 0 integer values

Can you post a few screenshots of your job, including the tmap?
One Star

Re: tMysqlOutput rows have Null string and date values, 0 integer values

Attached are a few images:







Community Manager

Re: tMysqlOutput rows have Null string and date values, 0 integer values

Hello
Change the filter expression to :
row1.name.equals("value")
and it should work fine now.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tMysqlOutput rows have Null string and date values, 0 integer values

Thanks Shomg, that seems to have resolved my Debug Job design issue.
I have a couple more questions questions:

1) The syntax for the equating values in an expression is kind oof wierd though! how come th eexpression builder widget shows "==" as the equate qualifier and is actuality one has to use the <.equals("value")> syntax. Is theer alist of these proprietary syntax uses for Talend (there is notihing about this in the doc set I was able to download).
2) how does won dor "outer" joins between main and Lookup tables, and how do you specify left or right outer join?
3) it seems Talend is very strict about data-types, for eaxmple in regular mysel, an field with "int" datatype can be joined to a field with "short" datatype, but Talend barfs on this. Is there a way to relax these kinds of enforcements in Talend?

Thanks again for you insights and help ...
Community Manager

Re: tMysqlOutput rows have Null string and date values, 0 integer values

Hello
1)As you known, Talend application is developed in Java, so in fact, the expression supports Java, if you know Java, you should know the difference between "==" and equals() method for String type.
2)If you uncheck the 'inner join' option, it do a left outer join(see topic). if you want to do a right outer join, just reverse the main flow and lookup flow.
3)In Talend, there is a talend type and db type for each database, go to windows-->preference-->Talend-->specific settings-->metadata of talendType and you see mapping between talend type and db type for each database, of course, you can change the default mapping.
About 'int' datatype join to 'short', you need to convert int to short or shot to int first, then join, for example
int to short: row2.id.shortValue()
short to int: (int)row2.id (see pic)

Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tMysqlOutput rows have Null string and date values, 0 integer values

Thanks, that worked. As you can tell, I am not Java saavy, but I will read up on Java syntax. I am curious though as to when one should use the syntax in the expression buillder tool?

Also, 2 more questions, as you seem to be very knowledeable on Talend:

1) How can one auto-generate "sequences" so that when writing to tMySqloutput Tables, one can insert an In-Talend generated unique ID, plus all the fields fetched from tMySqlinput tables?
2) can one do things suac has we d oin "stored procedures" (Oracle and MySql) within Talend Design Studio? If not, how can one call (execute) a stored procedure as a sub-job?

Thanks so much.
Community Manager

Re: tMysqlOutput rows have Null string and date values, 0 integer values

Hello
1) How can one auto-generate "sequences" so that when writing to tMySqloutput Tables, one can insert an In-Talend generated unique ID, plus all the fields fetched from tMySqlinput tables?

You can use the talend build-in function: Numeric.sequence(String sequenceName,int startIndex,int step), which generate a sequence unique id. (see pic)
2) can one do things suac has we d oin "stored procedures" (Oracle and MySql) within Talend Design Studio? If not, how can one call (execute) a stored procedure as a sub-job?

There is a sp component such as tMysqlSP for each database, which is used to call a stored procedure or function, see a demo job for tOracleSP on [topic]4278[/topic].

Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tMysqlOutput rows have Null string and date values, 0 integer values

Thanks, regarding executing SPs, in MySql the SP cannot be presistently maintained in the DB (unlike Oracle), so every time one needds to execute an SP in MySql, one has to do below 2 steps within an active MySql session:

1) source the SP, e.g.
MySql> source <path_to_sp_file>\sp_file.sql

2) upon successfull compliation of SP:
MySql> call <sp_name>();

My question is, how does one perfrom Step 1 from within Talend, and maitain session integrity (with Mysql DD) so that step 2 can happen subsequently?
Community Manager

Re: tMysqlOutput rows have Null string and date values, 0 integer values

Hello
how does one perfrom Step 1 from within Talend, and maitain session integrity (with Mysql DD) so that step 2 can happen subsequently?

Using the tMysqlConnection to create a connection, and then using tMysqlRow to execute your query, your job looks like:
tMysqlConnecton
|
onsubjobok
|
tMysqlRow(execute the query: "source <path_to_sp_file>\sp_file.sql"
|
onsubjobok
|
tMysqlSP(call your procedure)
|
onsubjobOK
|
tMysqlCommit(close the connection)

Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tMysqlOutput rows have Null string and date values, 0 integer values

Shong, you Rule! Thanks so muck for all the tips ...

One last thing I need to be up and running on Talend is how to schedule tasks to run Jobs. I created 2 subsequent tasks in the scheduler Tab, but thye never fired up. I am currently in regulaer Win XP, but am planning to install talend on a Win 2003 Server, so once on server, do I have to link Talend to the "at" engine or does Talend have it's own scheduler engine. In either case, can you guide me on this last step too, much appreciated...
Community Manager

Re: tMysqlOutput rows have Null string and date values, 0 integer values

Hello
I am currently in regulaer Win XP, but am planning to install talend on a Win 2003 Server, so once on server, do I have to link Talend to the "at" engine or does Talend have it's own scheduler engine. In either case, can you guide me on this last step too, much appreciated...

If you are using TOS, you can export the job script and use 'at' engine or other client tool. If you are using or plan to use TIS, it provide scheduler engine base on time and event.(see pic)

Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Have you checked out Talend’s 2019 Summer release yet?

Find out about Talend's 2019 Summer release

Blog

Talend Summer 2019 – What’s New?

Talend continues to revolutionize how businesses leverage speed and manage scale

Watch Now

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog