Win a copy of TDD for a Shopping Website LiveProject this week in the Testing forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

MySQL IF and CONCAT is returning null.

 
Ranch Hand
Posts: 153
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:



Method 1:



Method 2:



Proof Method (this returns - cssItalic):

 
Marshal
Posts: 27211
87
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 153
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:

                       


And the second example:

                     

 
Paul Clapham
Marshal
Posts: 27211
87
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 153
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In the while statement System.out.println("cssString: " + result.getString("cssString")); is null:


 
Marshal
Posts: 75644
354
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Saloon Keeper
Posts: 25461
180
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 153
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 153
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Tim,

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

The line




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

Kind regards,

Glyn
 
Glyndwr Bartlett
Ranch Hand
Posts: 153
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Tim,

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

Kind regards,

Glyn
 
Paul Clapham
Marshal
Posts: 27211
87
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Glyndwr Bartlett wrote:The line

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
Saloon Keeper
Posts: 25461
180
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Paul Clapham wrote:

Glyndwr Bartlett wrote:The line

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
Marshal
Posts: 75644
354
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Saloon Keeper
Posts: 25461
180
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Marshal
Posts: 75644
354
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you
 
Glyndwr Bartlett
Ranch Hand
Posts: 153
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:

                             

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.,:



To achieve this I have tired:



And:



I am using prepared statements for security reasons (e.g., prevent SQL injection).
 
Paul Clapham
Marshal
Posts: 27211
87
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.,:


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
Saloon Keeper
Posts: 25461
180
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 153
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
The harder you work, the luckier you get. This tiny ad brings luck - just not good luck or bad luck.
free, earth-friendly heat - a kickstarter for putting coin in your pocket while saving the earth
https://coderanch.com/t/751654/free-earth-friendly-heat-kickstarter
reply
    Bookmark Topic Watch Topic
  • New Topic