Java application not recognizing column name.

[chunkII123]

I have a Java application that uses -
<code>
"SELECT * FROM membersinformation WHERE membernumber = " +memberNumber;
</code>

Which works fine, but I added a code to add another column which will be called 'Age1', with an automatic calculation of the age via

<code>
CONCAT(CAST(IF(DATE_FORMAT(dateofbirth, '%m') < DATE_FORMAT(CURDATE(), '%m'), DATE_FORMAT(CURDATE(), '%Y') - DATE_FORMAT(dateofbirth, '%Y') + '0', DATE_FORMAT(CURDATE(), '%Y') - DATE_FORMAT(dateofbirth, '%Y') - '1') AS char)) AS 'Age1'
</code>

So the new Java call looks like -

<code>
"SELECT *, CONCAT(CAST(IF(DATE_FORMAT(dateofbirth, '%m') < DATE_FORMAT(CURDATE(), '%m'), DATE_FORMAT(CURDATE(), '%Y') - DATE_FORMAT(dateofbirth, '%Y') - '1', DATE_FORMAT(CURDATE(), '%Y') - DATE_FORMAT(dateofbirth, '%Y') + '0') AS char)) AS \"Age1\" FROM MembersInformation WHERE MemberNumber = " + memberNumber;
</code>

the problem is, when that same query is ran in MySQL query browser, it works like a charm, but when I try and use it in Java, the application (which has a dslField that pulls from 'Age1') that column 'Age1' not found.

Any help is apreciated as I have been extremely perplexed in trying to get this to work.


Beware of hackers bearing executables. Happy Hunting. 'irc.2600.net'

 

[tom62]

In standard SQL, one would not put the alias in single quotes.
Maybe it could work if you remove them.

"SELECT *, CONCAT(CAST(IF(DATE_FORMAT(dateofbirth, '%m') < DATE_FORMAT(CURDATE(), '%m'), DATE_FORMAT(CURDATE(), '%Y') - DATE_FORMAT(dateofbirth, '%Y') - '1', DATE_FORMAT(CURDATE(), '%Y') - DATE_FORMAT(dateofbirth, '%Y') + '0') AS char)) AS Age1 " FROM MembersInformation WHERE MemberNumber = " + memberNumber;

 

[Diancecht]

I think it's time for a PreparedStatement

Cheers,
Dian

 

[chunkII123]

In standard SQL, one would not put the alias in single quotes.Maybe it could work if you remove them.

and by standard SQL you mean -- SQL Server, MySQL SQLite...? Not sure if there is a difference in commands or not, but I am using MySQL Server 5.0.18 (not sure if this helps, but thank you).

Beware of hackers bearing executables. Happy Hunting. 'irc.2600.net'

 

[chunkII123]

Here is what I have in my "profile" method --

public ResultSet getMembersInfo (int memberNumber) 
    {
        String query = "SELECT  *, CONCAT(CAST(IF(DATE_FORMAT(dateofbirth, '%m') < DATE_FORMAT(CURDATE(), '%m'), DATE_FORMAT(CURDATE(), '%Y') - DATE_FORMAT(dateofbirth, '%Y') - '1', DATE_FORMAT(CURDATE(), '%Y') - DATE_FORMAT(dateofbirth, '%Y') + '0') AS char)) AS \"Age1\" FROM MembersInformation WHERE MemberNumber = " + memberNumber;


        ResultSet membersInfoResultSet = DAO.executeQuery(query);
        
        return membersInfoResultSet;
    }

and the profile form (our actual gui) uses --

membersInfoResultSet.NEXT

to fill the DSQLFields; I did not write this software, so I'm unsure where I would end up using a (pstmt) to tell the program that the ageField is to be filled from 'Age1'. Hope this helps clear up anything I forgot earlier.

Thanks again.

Beware of hackers bearing executables. Happy Hunting. 'irc.2600.net'

 

[Diancecht]

Some points:

1.- Use Prepared Statement, it helps correcting errors in querys and parameters
2.- If you are using Java to retrieve the values, why using 'as'? Just retrieve the column by number
3.- Provide the code you're using to retrieve the value
4.- Don't mix single, double and escaped quotes

Cheers,
Dian

 

[chunkII123]

Thanks Dian, I changed it to a pstmt, and it seems to work - when I load the profile page it says column aged could not be found, now when I go into a different section of the program and go to view profile it works phenominally. here is the code that it uses to intially retrieve the data when the "Profile" button is pressed -

public ProfileForm (int memberNumber) {
        profile = new Profile();
       
        /* initilize the components and add the action listeners */
        initComponents();
        initDefaults();
        
        /* Lock the components */
        IS_LOCKED = true;
        unlockComponents(profilePanel, false);

        setLocation(0, 88);
        java.awt.Dimension screenSize = java.awt.Toolkit.getDefaultToolkit().getScreenSize();
        setSize((int)screenSize.getWidth(),(int)screenSize.getHeight() - 109);
        
        membersInfoResultSet = profile.getMembersInfo(memberNumber);
        changeRecord(NEXT_RECORD); /* move to the first record */

Then window activated -

public void windowActivated(WindowEvent e) {
        if (e.getOppositeWindow() instanceof Search && mySearch != null) {
            if (mySearch.getResultSet() != null) {
                membersInfoResultSet = mySearch.getResultSet();
                try {
                    membersInfoResultSet.beforeFirst();
                      if (membersInfoResultSet.next()) {
                        setComponents(profilePanel, this.membersInfoResultSet);

                        if (membersInfoResultSet.getString("PicName") != null)
                        {
                            pictureLabel.setText("Click to view picture");
                        } 
                        else
                        { 
                            pictureLabel.setText("No picture on file");
                        }

                        fillHobbiesTable();

                     }

                    else {
                        JOptionPane.showMessageDialog(null, "No records found.",
                            "Search", JOptionPane.INFORMATION_MESSAGE);
                    }
                } catch( Exception ex ) {
                    ex.printStackTrace();

                    String warningMessage = "Class: " + this.getClass().getName() + " " +
                                     "windowActivated()\n" +
                                     ex.getMessage();

                    JOptionPane.showMessageDialog(this,
                                                    warningMessage,
                                                    "Error Message",
                                                    JOptionPane.ERROR_MESSAGE);
                }

                mySearch = null;
            }
        }

        else if (e.getOppositeWindow() instanceof AddHobbiesForm) {
            fillHobbiesTable();
        }

        // Input form could be notes, feedback, or matches field updates
        else if (e.getOppositeWindow() instanceof InputForm) {
            try
            {
                // Does not run if user cancelled
                if(input.getSaveClick() == 1)
                {
                    // Verifies user has a selected member
                    if(membersInfoResultSet != null)
                    {
                        try
                        {
                            String memnum = memberNumberField.getText();
                            int mn = Integer.parseInt(memnum);
                            
                            // Update the profile page
                            membersInfoResultSet = profile.getMembersInfo(mn);
                            // Selected the record
                            changeRecord(NEXT_RECORD, membersInfoResultSet);

                            // Refreshes the text
                            setComponents(profilePanel, membersInfoResultSet);
                        }
                        catch(Exception exc)
                        {
                            JOptionPane.showMessageDialog(this,
                                "This error occurred due to an incorrect status change (Sorry).",
                                "Status change; Status Changed Method",
                                JOptionPane.ERROR_MESSAGE);
                        } 
                    }
                }
            } 
            catch (Exception ex) 
            {
                JOptionPane.showMessageDialog(this,
                    "Exception: Profile Page/WindowActivated Method",
                    "Error Message",
                    JOptionPane.ERROR_MESSAGE);

                ex.printStackTrace();
            }
            fillMatchesTable();
        }
    }

thanks again for your help Dianecht!

Beware of hackers bearing executables. Happy Hunting. 'irc.2600.net'

 

[chunkII123]

Thanks everyone for the help, the idiot programmer put it in a different method than the one the SELECT should have been it.

Beware of hackers bearing executables. Happy Hunting. 'irc.2600.net'

 

[Diancecht]

Don't be so rude, everyone can make a mistake

Cheers,
Dian