Java Hard Coded SQL Query works but the exact same query read from a file buffer fails

Question

I am using Java and MySQL. This code works and returns the expected results:-

    queryStr = "SELECT PROGRAM, EQUIPMENT, t.PART_NUMBER, SN, GFE_CAP, t.STATUS, ii.Part_Number, ii.Serial_Number, [Inventory_Type – Level 1], ii.Status, ii.Destination, ii.Location" + 
            " FROM tblGFE_CAP as t, NGC_BJ_DEV.dbo.ISSUE_ITEMS as ii" +
            " WHERE (t.PART_NUMBER = ii.Part_Number)" +
            " AND (SN = ii.SERIAL_NUMBER)" +
            " AND (GFE_CAP != [Inventory_Type – Level 1])" +
            " AND NOT (([Inventory_Type – Level 1] = 'GFP') and (GFE_CAP = 'GFE'))" + 
            " AND NOT ((ii.Status = 'Retired') AND (t.Status = 'INACTIVE-REPLACED/RETIRED/DISPOSITION'))" +
            " AND NOT ((ii.Status = 'Consumed') and (t.Status = 'INACTIVE-REPLACED/RETIRED/DISPOSITION'))" +
            " GROUP BY PROGRAM, EQUIPMENT, t.PART_NUMBER, SN, GFE_CAP, t.Status, ii.Part_Number, ii.Serial_Number, [Inventory_Type – Level 1], ii.Destination, ii.Location, ii.Status" +
            " ORDER BY GFE_CAP, t.PART_NUMBER, SN";
        stmt = con.prepareStatement(queryStr);
        rs = stmt.executeQuery();

However, if I read the exact same text from a file (as below) and copy the results into queryStr, the code chokes on the Inventory_Type - Level 1 column name. I confirmed that the resultant strings are identical when passed to preparedStatement. I understand that special characters are not a good idea for the column name, but that is beyond my control.

I cannot change the column name. My real question is why the hard coded query works but the file read does not. Alternatively if there is a syntax that can be used to capture the column name within the java built query string.

        String bStr = "";
            StringBuilder sb = new StringBuilder();
            BufferedReader br = new BufferedReader(new FileReader(in.getAbsolutePath()));   
            while (true) 
            {
                bStr = br.readLine();
                if (bStr == null)
                    break;
                sb.append(bStr);
            }
            br.close();
            queryStr = sb.toString();
  stmt = con.prepareStatement(queryStr);
  rs = stmt.executeQuery();

1. Where's the text file text within your question? 2. What has debugging shown you? For example, have you printed the hard coded text and then printed the StringBuilder text and compared the two? — Hovercraft Full Of Eels, Jul 27 '17 at 17:01
Could you try printing `sb.toString()` before you execute it and share the results with us? — Mureinik, Jul 27 '17 at 17:01
I have a feeling the text file has some characters you are not seeing, use something like Notepad++ and use view all symbols there might be some hex characters in there — shakeel osmani, Jul 27 '17 at 17:03
How have you confirmed they're the same? (They're almost certainly not.) — shmosel, Jul 27 '17 at 17:04
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'Inventory_Type Level 1'. — Honus, Jul 27 '17 at 18:02
@talex Thanks for the reply, that missing - led me to the problem, which is that there is apparently two different hyphens available, one of which works while the other causes the failure--but presents a hyphen in the stack trace... Not sure why the suggested approach to use the Unicode reader didn't catch that, but se la vie. — Honus, Jul 27 '17 at 21:37
@Honus Maybe SQL server understand only ASCII version of hyphen (minus sign. real hyphen available only in UNICODE). — talex, Jul 28 '17 at 09:09

score 1 · Accepted Answer · answered Jul 27 '17 at 17:04

1

Use sb.append(bStr).append("\n") instead of sb.append(bStr).

Your current code lose line breaks and probably two different words from adjusting lines become one.

answered Jul 27 '17 at 17:04

talex

17,973
3
29
66

I am building a string with no linebreaks, so appending a line break would not be appropriate. Thanks though. – Honus Jul 27 '17 at 17:21
Do you have linebreaks in your file? – talex Jul 27 '17 at 17:25
This is not pertinent to the issue (not trying to be rude, just saying)...a similar query without the goofy column name works fine. – Honus Jul 27 '17 at 18:00
Thanks for the reply, that missing - led me to the problem, which is that there is apparently two different hyphens available, one of which works while the other causes the failure--but presents a hyphen in the stack trace... Not sure why the suggested approach to use the Unicode reader didn't catch that, but se la vie. – Honus Jul 27 '17 at 21:39

score 0 · Answer 2 · answered Jul 27 '17 at 17:05

0

This issue has to do with the fact that you are trying to read in special characters (in this case brackets) using your buffered reader. That problem has already been addressed.

see the following approaches:

Read/write .txt file with special characters

Read special characters in java with BufferedReader

answered Jul 27 '17 at 17:05

zeke askirion

1

Sadly, using the suggested approach had no impact upon the result. Thanks though. – Honus Jul 27 '17 at 17:21

Java Hard Coded SQL Query works but the exact same query read from a file buffer fails

2 Answers2