MySQL IF and CONCAT is returning null.

[Glyndwr Bartlett]

I am trying to produce an output row from a MySQL stored procedure using IF and CONCAT. However it returns null. I have tried two methods and a backup method, that works, to prove the data is valid. I am expecting the following to be returned:

["cssUnderline", ]
["cssUnderline", "cssItalic"]

Method 1:

" CONCAT('[', IF(pat_transport LIKE 'C', '\"cssOverline\", ', NULL), " +
" IF(pat_transport LIKE 'D', '\"cssUnderline\" , ', NULL), " +
" IF(pat_pre_post_alert LIKE 'Y', '\"cssItalic\"', NULL), " + 
" ']') AS cssString, " +

Method 2:

" CONCAT('[', IF(pat_transport LIKE 'C', CONCAT('\"' + 'cssOverline' + '\"'), NULL), " +
" IF(pat_transport LIKE 'D', CONCAT(' , ' + '\"' + 'cssUnderline' + '\"'), NULL), " +
" IF(pat_pre_post_alert LIKE 'Y', CONCAT(' , ' + '\"' + 'cssItalic' + '\"'), NULL), " + 
" ']') AS cssString, " +    

Proof Method (this returns - cssItalic):

" IF(pat_pre_post_alert LIKE 'Y', 'cssItalic', NULL) AS cssString, " +

[Paul Clapham]

What's this "it" which returns null? And you're telling us what you expect to see but not what you actually see -- unless what you actually see is "null"?

Anyway you've posted some Java code which does some horrible string concatenations to produce some SQL code; it would be more useful for us (and you) to see the SQL code which it produces.

[Glyndwr Bartlett]

Hi Paul,

It is the posted code. As explained there are are two examples that try to produce the some result. The full code is:

                       

SELECT csm_patient.pat_id,  
	    			 IF(pat_intercurrent LIKE 'Y', CONCAT('(',pat_surname, ', ', pat_other_names,')'), CONCAT(pat_surname, ', ', pat_other_names)) AS name,  
	    			 stt_id, stt_start_date, stt_start_time, stt_end_time, stt_end_date,  
	    			 CONCAT('[ ', IF(stt_sunday = 'Y', '0, ', ''), IF(stt_monday = 'Y', '1, ', ''),  
    				 IF(stt_tuesday = 'Y', '2, ', ''), IF(stt_wednesday = 'Y', '3, ', ''),  
    				 IF(stt_thursday = 'Y', '4, ', ''), IF(stt_friday = 'Y', '5, ', ''),  
    				 IF(stt_saturday = 'Y', '6, ', ''), ']') AS daysOfWeek,   
    				 st_type, stt_colour,      	    		
	    			 CONCAT('[', IF(pat_transport LIKE 'C', CONCAT('\'  'cssOverline'  '\'), NULL),  
    					 IF(pat_transport LIKE 'D', CONCAT(' , '  '\'  'cssUnderline'  '\'), NULL),  
    	    			 IF(pat_pre_post_alert LIKE 'Y', CONCAT(' , '  '\'  'cssItalic'  '\'), NULL),   
    	    			 ']') AS cssString,   					 
                                 IF(pat_transport LIKE 'C', 'fa-bus-alt', IF(pat_transport LIKE 'D', 'fa-taxi', NULL)) AS icon  
	  	      	 FROM csm_session_type, csm_patient_session_type, csm_patient, csm_session_timetable  
	    		 WHERE csm_session_type.cli_id = ?  
	    			 AND csm_session_type.st_id = csm_session_timetable.st_id  
	    			 AND csm_session_timetable.gym_id = ?  
	    			 AND csm_session_type.st_id = csm_patient_session_type.st_id  
	    			 AND csm_patient_session_type.pat_id = csm_patient.pat_id  
	  	      	 ORDER BY stt_start_date, stt_start_time;

And the second example:

                     

   SELECT csm_patient.pat_id,  
	    			 IF(pat_intercurrent LIKE 'Y', CONCAT('(',pat_surname, ', ', pat_other_names,')'), CONCAT(pat_surname, ', ', pat_other_names)) AS name,  
	    			 stt_id, stt_start_date, stt_start_time, stt_end_time, stt_end_date,  
	    			 CONCAT('[ ', IF(stt_sunday = 'Y', '0, ', ''), IF(stt_monday = 'Y', '1, ', ''),  
    				 IF(stt_tuesday = 'Y', '2, ', ''), IF(stt_wednesday = 'Y', '3, ', ''),  
    				 IF(stt_thursday = 'Y', '4, ', ''), IF(stt_friday = 'Y', '5, ', ''),  
    				 IF(stt_saturday = 'Y', '6, ', ''), ']') AS daysOfWeek,   
    				 st_type, stt_colour,      	    		
    				 CONCAT('[', IF(pat_transport LIKE 'C', '\cssOverline\, ', NULL),  
    					 IF(pat_transport LIKE 'D', '\cssUnderline\ , ', NULL),  
    	    			 IF(pat_pre_post_alert LIKE 'Y', '\cssItalic\', NULL),   
    	    			 ']') AS cssString,   
					 IF(pat_transport LIKE 'C', 'fa-bus-alt', 
                                 IF(pat_transport LIKE 'D', 'fa-taxi', NULL)) AS icon  
	  	      	 FROM csm_session_type, csm_patient_session_type, csm_patient, csm_session_timetable  
	    		 WHERE csm_session_type.cli_id = ?  
	    			 AND csm_session_type.st_id = csm_session_timetable.st_id  
	    			 AND csm_session_timetable.gym_id = ?  
	    			 AND csm_session_type.st_id = csm_patient_session_type.st_id  
	    			 AND csm_patient_session_type.pat_id = csm_patient.pat_id  
	  	      	 ORDER BY stt_start_date, stt_start_time;

[Paul Clapham]

I don't understand what it means for an SQL select statement to "return null". Does it return zero rows? Does it return one row containing one column which is NULL? Something else?

[Glyndwr Bartlett]

In the while statement System.out.println("cssString: " + result.getString("cssString")); is null:

     while (result.next()) {
		  	    	
		  	    	byte[] bytesEncoded1 = Base64.encodeBase64(result.getString("csm_patient.pat_id").getBytes());//encoding part
	  	  	  		String encoded_pat_id = new String(bytesEncoded1);
	  	  	  		
		  	  	  	byte[] bytesEncoded2 = Base64.encodeBase64(result.getString("stt_id").getBytes());//encoding part
	  	  	  		String encoded_stt_id = new String(bytesEncoded2);
	  	  	  		
	  	  	  		System.out.println("cssString: " + result.getString("cssString"));
	  	  	  		
	  	  	  		CalendarEvent calendarEventDtls = new CalendarEvent(
	  	  	  			encoded_pat_id,
	  	  	  			result.getString("name"),
	  	  	  			result.getString("daysOfWeek"),
	  	  	  			result.getString("stt_start_date"),
	  	  	  			result.getString("stt_end_date"),
	  	  	  			result.getString("stt_start_time"),
	  	  	  			result.getString("stt_end_time"),
	  	  	  			result.getString("stt_colour"),
	  	  	  			result.getString("cssString"),
	  	  	  			encoded_stt_id,
	  	  	  			result.getString("icon"));
	  	  	  		calendarEventList.add(calendarEventDtls);

[Campbell Ritchie]

Please also write shorter lines; your Java® code is difficult to read.

So you are getting null returned from one of the getString(...) calls? Which one? cssString?
Where are you amending that text in the SQL? Does it mean your pat_pre_post_alert LIKE is returning false and you are therefore getting NULL instead of concatenating something? Is it on line 11?

[Tim Holloway]

There has GOT to be a prettier way to form that query!

Nevertheless, a "LIKE" clause with no "like operator(s)" (%) in it probably isn't going to work very well.

[Glyndwr Bartlett]

Hi Campbell,

Yes, the cssString is returning null. I have  System.out.println("cssString: " + result.getString("cssString")); to show what is in cssString.

Kind regards,

Glyn

[Glyndwr Bartlett]

Hi Tim,

I am very open to a better way of forming the query if you have one :-).

The line

IF(pat_pre_post_alert LIKE 'Y', 'cssItalic', NULL) AS cssString,

Works perfectly well so I do not think the LIKE not having operators is an issue.

Kind regards,

Glyn

[Glyndwr Bartlett]

Hi Tim,

I did try LIKE '%C%', etc and that did not resolve the issue.

Kind regards,

Glyn

[Paul Clapham]

Glyndwr Bartlett wrote:The line

IF(pat_pre_post_alert LIKE 'Y', 'cssItalic', NULL) AS cssString,

Works perfectly well so I do not think the LIKE not having operators is an issue.

I'd agree about the LIKE not needing %. But what you claim "works perfectly well" there is different from the query you're complaining about. Namely, the latter has some backslashes in it for some reason. I assume that what you posted is SQL code and not Java code? What are the backslashes for -- are they supposed to appear literally in the output or are they some kind of SQL syntax?

[Tim Holloway]

Paul Clapham wrote:

Glyndwr Bartlett wrote:The line

IF(pat_pre_post_alert LIKE 'Y', 'cssItalic', NULL) AS cssString,

Works perfectly well so I do not think the LIKE not having operators is an issue.

I'd agree about the LIKE not needing %. But what you claim "works perfectly well" there is different from the query you're complaining about. Namely, the latter has some backslashes in it for some reason. I assume that what you posted is SQL code and not Java code? What are the backslashes for -- are they supposed to appear literally in the output or are they some kind of SQL syntax?

It's building something like HTML.

And the more I look at it the more I think that about 90% of the work should be done in procedural statements, not in a SELECT. Plus, some of those "AND" clauses in the WHERE look worryingly like a cross-product join, but that's for later.

Normally, I'd fetch the data into Java Entity objects using JPA and use Java code to build up the result text and it would be relatively simple.

And normally, I'd avoid stored procedures like COVID; I've already expounded at tedious lengths here multiple times about Why Stored Procedures Are Bad. But if for some reason this MUST be a stored procedure, again, I'd fetch the data and operate on it using the chosen stored procedure language, not try and cram it into a SQL SELECT. After all, why use stored procedures and not maximize their power? Just do a big ugly SELECT and forget the stored procedure.

[Campbell Ritchie]

Tim Holloway wrote:. . . I'd avoid stored procedures like . . .

Why do people teach about stored procedures? I believe they don't work at all well via JDBC because of security problems (use prepared statements instead), but I thought they would be all right if interacting with the DBMS directly.

[Tim Holloway]

Campbell Ritchie wrote:

Tim Holloway wrote:. . . I'd avoid stored procedures like . . .

Why do people teach about stored procedures? I believe they don't work at all well via JDBC because of security problems (use prepared statements instead), but I thought they would be all right if interacting with the DBMS directly.

I'm not sure that they are any less secure. For one thing, I'm fairly certain you can invoke them via PreparedStatements. But there are about 8 points on my list of why NOT to use stored procedures and if I haven't posted them to a Wiki here I should have.

The primary reasons FOR using stored procedures are when you get a significant performance boost from doing business logic  wholly within the DBMS server instance (and it's enough to offset the extra load on that machine!), for cases where the same business logic MUST be followed from disparate apps (and even then when you cannot simply share a library), and for cases where users are talking directly to the DBMS via the vendor's own interface program ("mysql", "psql", "sqlplus" or whatever). Which you generally don't want because A) the DBA probably hasn't got security that finely tuned and B) because those are programs that aren't exactly friendly for idiot users.

[Campbell Ritchie]

Thank you

[Glyndwr Bartlett]

I am very sorry to lead you down the wrong path. This is a prepared statement; not a stored procedure. I got my terminology mixed up.

This is MySQL and I do not think that the fact I am using java is relevant. As long as I can get the MySQL to produce the correct output then the rest will follow (i.e., I am passing lots of other information without any issues).  The following code, as I stated, works in producing a simgle css name:

                             

String selectQry1 = ("SELECT csm_patient.pat_id, " +
	    			" IF(pat_intercurrent LIKE 'Y', CONCAT('(',pat_surname, ', ', pat_other_names,')'), CONCAT(pat_surname, ', ', pat_other_names)) AS name, " +
	    			" stt_id, stt_start_date, stt_start_time, stt_end_time, stt_end_date, " +
	    			" CONCAT('[ ', IF(stt_sunday = 'Y', '0, ', ''), IF(stt_monday = 'Y', '1, ', ''), " +
    				" IF(stt_tuesday = 'Y', '2, ', ''), IF(stt_wednesday = 'Y', '3, ', ''), " +
    				" IF(stt_thursday = 'Y', '4, ', ''), IF(stt_friday = 'Y', '5, ', ''), " +
    				" IF(stt_saturday = 'Y', '6, ', ''), ']') AS daysOfWeek, " + 
    				" st_type, stt_colour, " +
    				" IF(pat_pre_post_alert LIKE 'Y', 'cssItalic', NULL) AS cssString, " +
                                " IF(pat_transport LIKE 'C', 'fa-bus-alt', IF(pat_transport LIKE 'D', 'fa-taxi', NULL)) AS icon " +
	  	      	" FROM csm_session_type, csm_patient_session_type, csm_patient, csm_session_timetable " +
	    		" WHERE csm_session_type.cli_id = ? " +
	    			" AND csm_session_type.st_id = csm_session_timetable.st_id " +
	    			" AND csm_session_timetable.gym_id = ? " +
	    			" AND csm_session_type.st_id = csm_patient_session_type.st_id " +
	    			" AND csm_patient_session_type.pat_id = csm_patient.pat_id " +
	  	      	" ORDER BY stt_start_date, stt_start_time;");

Produces the cssString of cssItalic. And this works. However, I want to be able to pass more than one css name to the client side. For this to work the client side needs to receive the css names in square brackets, quotes and separated by commas, e.g.,:

["cssItalic", "cssUnderline"]

To achieve this I have tired:

String selectQry1 = ("SELECT csm_patient.pat_id, " +
	    			" IF(pat_intercurrent LIKE 'Y', CONCAT('(',pat_surname, ', ', pat_other_names,')'), CONCAT(pat_surname, ', ', pat_other_names)) AS name, " +
	    			" stt_id, stt_start_date, stt_start_time, stt_end_time, stt_end_date, " +
	    			" CONCAT('[ ', IF(stt_sunday = 'Y', '0, ', ''), IF(stt_monday = 'Y', '1, ', ''), " +
    				" IF(stt_tuesday = 'Y', '2, ', ''), IF(stt_wednesday = 'Y', '3, ', ''), " +
    				" IF(stt_thursday = 'Y', '4, ', ''), IF(stt_friday = 'Y', '5, ', ''), " +
    				" IF(stt_saturday = 'Y', '6, ', ''), ']') AS daysOfWeek, " + 
    				" st_type, stt_colour, " +
                                " CONCAT('[', IF(pat_transport LIKE 'C', '\"cssOverline\", ', NULL), " +
    					" IF(pat_transport LIKE 'D', '\"cssUnderline\" , ', NULL), " +
    	    			" IF(pat_pre_post_alert LIKE 'Y', '\"cssItalic\"', NULL), " + 
    	    			" ']') AS cssString, " + 
                                 " IF(pat_transport LIKE 'C', 'fa-bus-alt', IF(pat_transport LIKE 'D', 'fa-taxi', NULL)) AS icon " +
	  	      	" FROM csm_session_type, csm_patient_session_type, csm_patient, csm_session_timetable " +
	    		" WHERE csm_session_type.cli_id = ? " +
	    			" AND csm_session_type.st_id = csm_session_timetable.st_id " +
	    			" AND csm_session_timetable.gym_id = ? " +
	    			" AND csm_session_type.st_id = csm_patient_session_type.st_id " +
	    			" AND csm_patient_session_type.pat_id = csm_patient.pat_id " +
	  	      	" ORDER BY stt_start_date, stt_start_time;");

And:

String selectQry1 = ("SELECT csm_patient.pat_id, " +
	    			" IF(pat_intercurrent LIKE 'Y', CONCAT('(',pat_surname, ', ', pat_other_names,')'), CONCAT(pat_surname, ', ', pat_other_names)) AS name, " +
	    			" stt_id, stt_start_date, stt_start_time, stt_end_time, stt_end_date, " +
	    			" CONCAT('[ ', IF(stt_sunday = 'Y', '0, ', ''), IF(stt_monday = 'Y', '1, ', ''), " +
    				" IF(stt_tuesday = 'Y', '2, ', ''), IF(stt_wednesday = 'Y', '3, ', ''), " +
    				" IF(stt_thursday = 'Y', '4, ', ''), IF(stt_friday = 'Y', '5, ', ''), " +
    				" IF(stt_saturday = 'Y', '6, ', ''), ']') AS daysOfWeek, " + 
    				" st_type, stt_colour, " +
                                " CONCAT('[', IF(pat_transport LIKE 'C', CONCAT('\"' + 'cssOverline' + '\"'), NULL), " +
    					" IF(pat_transport LIKE 'D', CONCAT(' , ' + '\"' + 'cssUnderline' + '\"'), NULL), " +
    	    			" IF(pat_pre_post_alert LIKE 'Y', CONCAT(' , ' + '\"' + 'cssItalic' + '\"'), NULL), " + 
    	    			" ']') AS cssString, " + 
                            " IF(pat_transport LIKE 'C', 'fa-bus-alt', IF(pat_transport LIKE 'D', 'fa-taxi', NULL)) AS icon " +
	  	      	" FROM csm_session_type, csm_patient_session_type, csm_patient, csm_session_timetable " +
	    		" WHERE csm_session_type.cli_id = ? " +
	    			" AND csm_session_type.st_id = csm_session_timetable.st_id " +
	    			" AND csm_session_timetable.gym_id = ? " +
	    			" AND csm_session_type.st_id = csm_patient_session_type.st_id " +
	    			" AND csm_patient_session_type.pat_id = csm_patient.pat_id " +
	  	      	" ORDER BY stt_start_date, stt_start_time;");

I am using prepared statements for security reasons (e.g., prevent SQL injection).

[Paul Clapham]

Glyndwr Bartlett wrote:... Produces the cssString of cssItalic. And this works. However, I want to be able to pass more than one css name to the client side. For this to work the client side needs to receive the css names in square brackets, quotes and separated by commas, e.g.,:

["cssItalic", "cssUnderline"]

So to change from your original working SQL code to the non-working code, you've included a CONCAT function and you've included an attempt at including " characters with backslashes as escapes. So I would suggest just taking one of those steps at a time. That way you have more chance of knowing which one you did wrong.

I use MySQL Workbench for hacking away at ugly SQL code, you get to know immediately whether your SQL is what you want rather than having to repeatedly try running it via Java. Are you doing that too?

[Tim Holloway]

Whether it's a prepared statement or a stored procedure, my recommendation still stands. Use application logic and forget about trying to do it all in SQL. All those IF and CONCAT clauses can be reduced to relatively simple Java code using SQL that just retrieves the columns and doesn't try to do fancy formatting tricks at the same time. You've quite enough of a challenge in the WHERE clauses.

If you do Java code, not only does the SELECT statement get a lot shorter, it's much easier to debug step-by step using a debuggger.

It's doesn't make the SELECT more efficient to put the formatting logic all in the SQL - in fact it does the opposite.

And while I say Java, if the actual app using thing data is written in Python, then do it in Python. The point is, that SQL is just too darned complex!

And it really does look like you're doing multiple cross-product joins. Which can not only give you unexpected data, it can really bog down the database server. You almost certainly need to be doing actual JOIN operations!

[Glyndwr Bartlett]

Hi Paul,

Yes, I do use MySQL Workbench and have been hacking away at this. Eventually I just replaced NULL with '' and it now works. So, for some reason, if one of the CONCAT statements returns NULL then the whole string is NULL. So, do not use NULL in a CONCAT statement.

Kind regards,

Glyn