topic Re: SQL SELECT -> If (numrows > 0) in Talend Studio

SQL SELECT -> If (numrows > 0)

Anonymous — Sat, 16 Nov 2024 12:49:47 GMT

Hi,
Im using the Java version and have got Talend connected to a MySQL database. What I want to know is how to perform a SELECT SQL statement, and if it returns one or more rows do one thing, else if it returns no rows do another. It sounds simple and probably is but Ive searched the net and google and have had no luck.
Thanks in advance

Re: SQL SELECT -> If (numrows > 0)

Anonymous — Tue, 05 Jul 2011 10:12:35 GMT

Hi
You can use the global variable nb_line of tMyslqInput which counts the total number of selected rows. For example:
tMysqlInput_1--main--tLogRow
|
onsubjobok
|
tJava--runIf---do one thing
--RunIf---do another thing
on tJava:

int nb_line=((Integer)globalMap.get("tMysqlInput_1_NB_LINE"));
if(nb_line>0){
globalMap.put("hasRows",true);
}else{
globalMap.put("hasRows",true);
}

set the condition of the first runIf:
(Boolean)globalMap.get("hasRows")
set the condition of the second runIf:
!(Boolean)globalMap.get("hasRows")

Best regards
Shong

Re: SQL SELECT -> If (numrows > 0)

Anonymous — Tue, 05 Jul 2011 10:28:47 GMT

Many thanks, worked perfectly.

Re: SQL SELECT -> If (numrows > 0)

Anonymous — Tue, 05 Jul 2011 15:51:56 GMT

Another quick thing carrying on from the above.
Ive been trying to get it to insert a row when the IF statements returns false and update the row when it returns true. I imagine I will need MySQL row somewhere along the line but have had no luck as of yet.
Any response would be much appreciated.
Thanks in advance

Re: SQL SELECT -> If (numrows > 0)

Anonymous — Wed, 06 Jul 2011 12:57:45 GMT

Another quick thing carrying on from the above.
Ive been trying to get it to insert a row when the IF statements returns false and update the row when it returns true. I imagine I will need MySQL row somewhere along the line but have had no luck as of yet.
Any response would be much appreciated.
Thanks in advance

Hi
Sorry, I don't understand your new question.;(
Best regards
Shong

Re: SQL SELECT -> If (numrows > 0)

Anonymous — Thu, 07 Jul 2011 10:58:54 GMT

Sorry I will try and be clearer.
Assuming you understand PHP syntax, below is a simplified version of I want to be able to do within Talend.

	$sql = " select * from departments  where country_id = 'UK'";
        $result = mysql_query($sql);
        $num_rows = mysql_num_rows($result);
	if ($num_rows == 1)
	{
                $row = mysql_fetch_array($result);
                $id = $row;
                $sql = "update departments set dept_name = 'xxx', dept_type = 'xxx' where dept_id = '$id'";
                mysql_query($sql);
	}
	else
	{
                $sql = "insert into departments (dept_name, dept_type, deptfloor) values ($deptname, $depttype, $deptfloor)";
                mysql_query($sql);
	}

The code may not be correct in syntax as I typed it out quickly for this example but hopefully itll give you a greater understanding of what I want to achieve.
Thanks alot.

Re: SQL SELECT -> If (numrows > 0)

Anonymous — Thu, 07 Jul 2011 13:35:37 GMT

Hi aaronb
I came up with a solution to your problem however I am not sure what components you are using so if you need help changing things around just let me know.
tForeach (I use to execute multiple queries)
new query on every line and can't have new lines eg.
"SELECT * FROM Table"
"SELECT * FROM Table2"
-- have as many of these as you need (Like I said I don't know what your input queries will be so we can always change this) --
NOT
"SELECT *
FROM Table"
"SELECT *
FROM Table2"
tMySQL_input_5 (!This is to get the count from the query first!)
query - ((String)globalMap.get("tForeach_1_CURRENT_VALUE"))
schema would be count and other hard coded values
eg.
"SELECT COUNT(*), 'uk' as country from table1"
where uk will be used in a query later on.
tJavaRow_1 code

if(!String.valueOf(input_row.count).equals("1"))
{
globalMap.put("query", "SELECT * FROM table where country = "+input_row.country+"");   // input_row.country is the hard coded value from the query
globalMap.put("TF", "false");
}
else if(String.valueOf(input_row.count).equals("1"))
{
globalMap.put("query", "SELECT * FROM table where country = "+input_row.country+"");   // input_row.country is the hard coded value from the query
globalMap.put("TF", "true");
}

if - to -> tMySQLInput_6
globalMap.get("TF").equals("true")

if - to -> tMySQLInput_7
globalMap.get("TF").equals("false")
tMySQLInput_6 would be your update.
query - (String)globalMap.get("query")
and tMySQLOutput_1 would be set to update
tMySQLInput_7 would be your insert.
query - (String)globalMap.get("query")
and tMySQLOutput_2 would be set to insert
Here is the image of the job below:

I Hope this gets you on the right track there may be an easier way of doing this, I just thought of this solution quickly as earlier in this post you said you wanted to use select statements that can have different values added into them.

Or if you want to use the nb line to count the rows we can also do it that way
Regards,
Brandon