[resolved] Simple job tMysqlRow to tLogRow not working

[Anonymous]

Hi there,
 
I have a simple job with two components tMysqlRow connected via Row/Main to tLogRow.
 
I have a simple select statement in the tMysqlRow component that I have tested directly on the db and it works and returns a single record.  When I run the job it says "1 rows in 0.04s" so I assume that means that the tMysqlRow component is getting 1 row from the db.
 
In the tLogRow component I have pressed the "Sync columns" button on the basic tab and when I edit the schema I can see all the columns are there.
 
But when I run the job, it logs the following:
Starting job LoadContextVariablesFromDBRecordDemo at 13:07 30/09/2015.
connecting to socket on port 3714
connected
 
||||||
 
disconnected
Job LoadContextVariablesFromDBRecordDemo ended at 13:07 30/09/2015.
 
So I am at a loss for why the values are not logged?
 
What am I doing wrong here?
 
Regards,
 
Scott

[Anonymous]

Hi Scott,
Do you really need to use tMysqlRow component? From what I understand, tMysqlInput is just the component you need. It directly returns the result of the query and propagates it into the flow. tMysqlRow component does not handle the returned data by default.
In case you are restricted to use the tMysqlRow component, you have to check the Propagate QUERY's recordset in the Advanced settings of tMysqlRow component and then use the tParseRecordSet component to extract the data. For more information and guide, please, refer to the tMysqlRow Component Guide page. There is also an example on how to do this.
Best Regards,
Matej

[Anonymous]

Matej,
 
Thanks for the response mate.  Thanks for pointing this out.  Going off the name alone I thought tMysqlInput would have been for putting data into the db, not getting it out.
 
So I removed the connection from tMysqlRow to tLogRow, and then dropped a tMysqlInput on the job, and made a Row/Main connection from tMysqlInput to tLogRow.  It then asked me if I wanted to import the schema from the target component (tLogRow), to which I chose Yes.  I then noticed that there was a /!\ info icon on the tMysqlInput.  When I moused over this it says:
" - the schema's dbType is not correct for this component"
 
So I edited the schema for the tMysqlInput to find that the DB Type column had no values.  Fair enough, so I added them in matching the types in my MySQL db.
 
I pressed OK, and the  /!\ info icon remained there with the same message.
 
So I edited the schema again.  Now I see that the DB Type column has 3 cells highlighted in orange.  They are:
Type        DB Type                
Long        BIGINT UNSIGNED
Integer    SMALLINT UNSIGNED
String      ENUM
 
I can not find anything in the documentation about what these orange highlights mean, there is no indication on the dialogue of what they mean, leaving me with no clue as to how to fix this.
 
Do you know what I have done wrong here and how I can resolve it mate?
 
Regards,
 
Scott

[Anonymous]

Some more details:
 
So if I ignore this /!\ info icon and run the job, it works and logs the data read from the tMysqlInput.
 
If I edit the schema in the MysqlInput and press the Reset DB Types button, the orange highlights and the  /!\ info icon disappear, but previously highlighted cells in the schema change to:
Type        DB Type                
Long        BIGINT 
Integer    INT 
String      VARCHAR
 
So it doesn't like BIGINT UNSIGNED, SMALLINT UNSIGNED or ENUM.  Why is it I can not use these types, as that is what the types in the DB are.  Isn't that the point of the DB Type column?
 
Thanks for your time,
 
Scott

[Anonymous · Accepted solution]

Hey Scott,
Names of DB components implies the flow in and out of the studio, not the DB, as you can see. Input means getting data from the DB into the studio, Output means getting data out of the studio into the DB (Same naming convention is used also for files and other systems). It may seem a bit confusing
Regarding the DB types
Yes, this is just a warning and it doesn't affect your job (in most cases). But it's better to avoid warnings at all, as we know
The DB types mapping is defined in the specific XML for every supported Database type. You can find these mappings in Window >> Preferences >> Talend >> Specific Settings >> Metadata of TalendType. Check, what DB types matches your TalendType. You can modify the mapping to get rid of this warning, but remember: If you are working in a team, all your coworkers should import the modified mapping xml file, otherwise they'll see the same warning you got rid of.
Best Regards,
Matej

[Anonymous]

Matej,
 
Thanks for the great response mate.  So I had a look in the Mapping File and I found the following entries:
...

<talendType type="id_Integer">
    <dbType type="INT" default="true" />
    <dbType type="BIGINT" />
</talendType>
<talendType type="id_Long">
    <dbType type="BIGINT" default="true" />		
</talendType>

...

<dbType type="BIGINT UNSIGNED" >
</dbType>

...
 
So are you suggesting that I just change these to add BIGINT UNSIGNED to the talendType id_Integer and id_Long, and add id_Long and id_Integer to the talendType BIGINT UNSIGNED?
Etc...
 
Seems strange that these wouldn't be in there by default.... it is the mapping.Mysql.xml... you would think that would map all the MySQL types.... 
 
Regards,
 
Scott

[Anonymous]

Scott,
If you want to get rid of the warning, then yes, modify the XML file and the warning will disappear. But I must admit that in my experiences with Talend products, this warning had never caused any problem during the runtime.
Happy to help
Matej

[Anonymous]

Matej,
 
Ok cool.  I have made the change.  I think it is good to only get warnings when they are things I should be warned about.  Otherwise developers may overlook more important warnings thinking that the icon is always there in these scenarios......
 
Thanks for your help on this, much appreciated 😉
 
Regards,
 
Scott