SQL query not giving any result

[Gayan Herath]

Hi,

I'm trying to get some records by using this query in Java.

SELECT * FROM NetNanny WHERE (status='ACTIVE' AND 1306866600 < nextBilling AND nextBilling < 1306953000);

but this doesn't give any result. but when I executer the query in mysql, It gives some records. Waht could be the reason.

This the table sreucture for myDB

CREATE TABLE IF NOT EXISTS `NetNanny` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `fname` text, `lname` text, `email` varchar(100) NOT NULL DEFAULT '', `user_license` text NOT NULL, `pid` decimal(10,0) DEFAULT NULL, `sku` decimal(10,0) DEFAULT NULL, `datetime` int(11) NOT NULL, `seats` int(11) DEFAULT '0', `nextBilling` bigint(11) NOT NULL, `msisdn` varchar(11) NOT NULL, `status` varchar(10) NOT NULL, PRIMARY KEY (`id`,`email`,`msisdn`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=124 ;


Please give any solution.

Thanks.

[Avi Abrami]

Gayan,
Please post your java code for performing the query.
Do you use java.sql.Statement or java.sql.PreparedStatement ?

Good Luck,
Avi.

[Gayan Herath]

Avi Abrami wrote:Gayan,
Please post your java code for performing the query.
Do you use java.sql.Statement or java.sql.PreparedStatement ?

Good Luck,
Avi.

Hi Avi,

I'm using java.sql.Statement.
Here is my code for the query.

DBConnector conn = new DBConnector();
            Statement stmt = (Statement) conn.createStatement();
String query = "SELECT * FROM NetNanny WHERE  (status='ACTIVE' AND "+time1+" < nextBilling < "+time2+")";
        System.out.println(query);
        System.out.println("charging.............1 ");
            ResultSet rs = stmt.executeQuery(query);

String msisdn;
while(rs.next()){
                
                msisdn = rs.getString("msisdn");
System.out.println("charging............. "+msisdn);
}

And This my DBConnector class

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package servlet;



import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author Waruna Kothalawala
 */
public class DBConnector {
    Connection con = null;

    public DBConnector() {

       
        String driver = "";
        String url = "";

        String hostIp = "";
        String hostPort = "";
        String dbName = "";
        String dbUser = "";
        String dbPassword = "";

        
        try {
            
            hostIp = "localhost";
            hostPort = "3306";
            dbName = "my_portal";
            dbUser = "root";
            dbPassword = "root";

            try {

                driver = "com.mysql.jdbc.Driver";
                Class.forName(driver);

                url = "jdbc:mysql://" + hostIp + ":" + hostPort + "/" + dbName;
                
                con = DriverManager.getConnection(url, dbUser, dbPassword);
                

            } catch (Exception e) {
                e.printStackTrace();
            }

        } catch (Exception e) {
            System.out.println("ERROR-MISSING DB Properties");
        }
  
    }

    public Statement createStatement() throws SQLException{
        return con.createStatement();
    }


    protected void exeQuery(String sqlStatement)
          throws Exception {

                try {
                        Statement cs = con.createStatement();
                        ResultSet sqls = cs.executeQuery(sqlStatement);

                        while (sqls.next()) {
                                
                        }

                        sqls.close();

                } catch (SQLException e) {
                        System.out.println ("Error executing sql statement");
                        throw (e);
                }
    }
    
    protected ResultSet getQueryResult(String sqlStatement)
          throws Exception {
        
        ResultSet sqls = null;

                try {
                        Statement cs = con.createStatement();
                        sqls = cs.executeQuery(sqlStatement);

                        while (sqls.next()) {
                                //String id = (sqls.getObject("id").toString());
                                //String data = (sqls.getObject("data").toString());
                                //System.out.println(id + " " + data);
                        }

                        //sqls.close();

                } catch (SQLException e) {
                        System.out.println ("Error executing sql statement");
                        throw (e);
                }
                
                    return sqls;
                

    }



    protected void updateQuery(String sqlStatement)
          throws Exception {
                try {
                        Statement cs = con.createStatement();
                        cs.executeUpdate(sqlStatement);
                } catch (SQLException e) {
                        System.out.println ("Error executing sql statement");
                        throw (e);
                }
    }
}

Thanks for any solution.

[Avi Abrami]

Gayan,
JDBC drivers map database data types to java classes.
Hence from your code, the variables time1 and time2 must be instances of the java class that maps to the BIGINT database data type.
It is not clear to me from the code you posted whether this is the case.
I have no experience with mySQL and therefore do not know what java class maps to BIGINT.
I suggest trying the documentation for the JDBC driver you are using.

Good Luck,
Avi.

[Gayan Herath]

Avi Abrami wrote:Gayan,
JDBC drivers map database data types to java classes.
Hence from your code, the variables time1 and time2 must be instances of the java class that maps to the BIGINT database data type.
It is not clear to me from the code you posted whether this is the case.
I have no experience with mySQL and therefore do not know what java class maps to BIGINT.
I suggest trying the documentation for the JDBC driver you are using.

Good Luck,
Avi.

Hi Avi,

But this works for this SELECT * FROM NetNanny WHERE (status='ACTIVE' AND 1306866600 < nextBilling )

If this is a problem with type, This wouldn't work no?


Gayan.

[Mike Zal]

Gayan Herath wrote:
"SELECT * FROM NetNanny WHERE (status='ACTIVE' AND "+time1+" < nextBilling < "+time2+")";

Your SQL looks messed up. Try the following

"SELECT * FROM NetNanny WHERE  (status='ACTIVE' AND "+time1+" < nextBilling AND nextBilling < "+time2+")";

With that aside, you should be using a PreparedStatement instead of a regular Statement.

[Jan Cumps]

Mike Zal wrote:...

With that aside, you should be using a PreparedStatement instead of a regular Statement.

Yes, and I would use ... nextbilling BETWEEN x and y...