How to return mysql function select count

Question

I am tring to get the count value of a sql select using a mysql function. Functions executes without errors but When I try to get the value using java following error is appeared.

25-May-2016 05:14:02.180 SEVERE [http-nio-8084-exec-109] CORE.ApplicationMgt.Get_New_Application_Count null com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'temp' in 'field list'

Here is mysql funtion

DELIMITER //
CREATE FUNCTION APPLICATION_API_Get_New_Application_Count() RETURNS INTEGER
BEGIN
IF((SELECT COUNT(*) AS temp FROM application_view WHERE status = 'NEW')>0) THEN
    RETURN temp;
END IF;

RETURN 0;

END//

This is the function call point

public int Get_New_Application_Count() {
        CallableStatement stmt;
        int temp_ = 0;
        try {
            stmt = MySqlManager.getDbConnection().prepareCall("{ ? = call APPLICATION_API_Get_New_Application_Count()}");

            stmt.registerOutParameter(1, java.sql.Types.INTEGER);
            stmt.execute();
            temp_ = stmt.getInt(1);

        } catch (SQLException ex) {
            Logger.getLogger(PermissionSetMgt.class.getName()).log(Level.SEVERE, null, ex);
        }
        return temp_;
    }

This is the table

CREATE TABLE IF NOT EXISTS application_tab (
        nic                 VARCHAR(10)     NOT NULL,
        apply_degree        VARCHAR(45)     NOT NULL,
        user_email          VARCHAR(60)     NOT NULL,
        title               VARCHAR(10)     NOT NULL,
        initials            VARCHAR(15)     NULL,
        name_from_initials  VARCHAR(100)    NULL,
        first_name          VARCHAR(45)     NULL,
        middle_name         VARCHAR(45)     NULL,
        last_name           VARCHAR(45)     NULL,
        birth_day           DATE            NOT NULL,
        gender              VARCHAR(1)      NOT NULL,
        civil_status        VARCHAR(15)     NOT NULL,
        course_type         VARCHAR(25)     NOT NULL,
        com_lit_opt1        VARCHAR(2)      NOT NULL,
        com_lit_opt2        VARCHAR(2)      NOT NULL,
        com_lit_opt3        VARCHAR(2)      NOT NULL,
        com_lit_opt4        VARCHAR(2)      NOT NULL,
        designation         VARCHAR(50)         NULL,
        org_name            VARCHAR(50)         NULL,
        appointed_date      DATE                NULL,
        status              VARCHAR(15)     NOT NULL,
  PRIMARY KEY (nic, apply_degree));

This is the view from this table

CREATE OR REPLACE VIEW application_view AS
    SELECT  nic                 nic,
            apply_degree        apply_degree,
            user_email          user_email,
            title               title,
            initials            initials,
            name_from_initials  name_from_initials,
            first_name          first_name,
            middle_name         middle_name,
            last_name           last_name,
            birth_day           birth_day,
            gender              gender,
            civil_status        civil_status,
            course_type         course_type,
            com_lit_opt1        com_lit_opt1,
            com_lit_opt2        com_lit_opt2,
            com_lit_opt3        com_lit_opt3,
            com_lit_opt4        com_lit_opt4,
            designation         job,
            org_name            organization_name,
            appointed_date      appointed_date,
            status              status
    FROM application_tab;

Bro, why don't you return the value of the query from the function. If result is greater than zero return result, else, return zero? Just return the value of your query. — Sam Orozco
– Sam Orozco, Commented May 25, 2016 at 0:12

Shadow · Accepted Answer · 2016-05-25 00:16:59Z

6

This issue has nothing to do with java, your mysql function is incorrect.

First of all, the function is overcomplicated with the if. If the returned count is zero, then still you could return the count instead of a specific number.

Secondly, you do not have a temp variable in the function, so there is nothing to return.

I would simply declare an integer variable, fetch the count into it and return the variable's value:

...
declare temp int;
SELECT COUNT(*) into temp FROM application_view WHERE status = 'NEW';
return temp;
...

answered May 25, 2016 at 0:16

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

IzzEps · Accepted Answer · 2016-05-25 00:15:19Z

0

It seems that the issue is being caused by the fact that the temp column from SELECT COUNT(*) AS temp is not accessible in RETURN temp;.

Why not just ditch the IF condition altogether as it looks like you are just returning '0' if the count is not greater than 0 - in which case it would just be 0...?

answered May 25, 2016 at 0:15

IzzEps

5826 silver badges20 bronze badges

Collectives™ on Stack Overflow

How to return mysql function select count

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related