-1

Previously, I connected to SQL Server 2012 Views and Tables properly, but few days ago, I tried to select from a Stored Procedure, with the help of my good friends here, but no chance. So i forgot about Stored Procedure and tried a View. I want to select data from a View. Here is my code:

statement = connectionHelper1.getMyConnection().createStatement();
ResultSet resultSet = statement.executeQuery("select Latitude from View_4 where IMEI='xxxxx'");
while (resultSet.next()) 
{
    i++;
}
Toast.makeText(getApplicationContext(), " : " + i, Toast.LENGTH_SHORT).show();

This code is in button.setOnClickListener method. When I press the button it should print the I variable, but it does nothing. But when I print this Query in SQL Server 2012 query mode, it returns the values and Resultset works. (I mean this select query) I should say that this View(View_4) by itself select from a table that has about 28,000,000 row!! But when I select from another View named View_2 and select some other fields related to this View(View_2), it returns the I and ResultSet. Notice that View_2 is another View and contains different fields from View_4. Like this: 

statement = connectionHelper1.getMyConnection().createStatement();
ResultSet resultSet = statement.executeQuery("select Latitude from View_2 where name='xxxxxxx'");
while (resultSet.next()) {
    i++;
}
Toast.makeText(getApplicationContext(), " : " + i, Toast.LENGTH_SHORT).show();

The code below returns values and result from query,then it prints the I. But in first code that I have used View_4 it does not work. I notice again that the View_4 returns value from a table that has 28,000,000 rows. So I decided to select from that table directly, but still can not select from that table. Can the number of rows be the cause. If yes what can I do?

Alireza
  • 89
  • 2
  • 15
  • I can't see anything wrong with your code (hopping that you close the rs and statement in a final block). To be sure that you query same schema as in SQL Server 2012 include in your query the full schema name and Log the query, after the executeQuery (to check that the query is executed and and if you enter the loop. – Petter Friberg Nov 30 '15 at 15:41
  • Hi Petter again. Yeas,I always copy my select statement from android-studio and open a new query windows in SQL Server Management,then paste my statement and execute from studio. The schema is dbo. For example dbo.View_2 Even with schema it returns nothing. No error or something wrong in Logcat. The View_2 that i can select from that is in the same DB. – Alireza Nov 30 '15 at 15:57
  • The numbers of row's can effect the time that the query takes... but either its still executing or you get an error.... its not just skipping ; ) – Petter Friberg Nov 30 '15 at 16:01
  • i change the wile to if like this and edit select with top 10 ,Like below : if (resultSet.next()) { Toast.makeText(getApplicationContext(), "It Works !", Toast.LENGTH_SHORT).show(); } else { Toast.makeText(getApplicationContext(), "Get Out !", Toast.LENGTH_LONG).show(); } it does not Toast ! Like it is skipping ! – Alireza Nov 30 '15 at 16:12
  • And simple queries :ResultSet resultSet = statement.executeQuery("select top 10 Latitude from dbo.View_4 where IMEI=N'xxxxxx'"); I added N before IMEI ,and copy and run it in SQL Studio. In android-studio. it takes 3-4 seconds to execute. – Alireza Nov 30 '15 at 16:13
  • I would guess you have some kind of error that you are not seeing or your query is not correct, I have posted an answer to show you how to query and log query on database.. Trust me if database correct, schema correct and query works the only way to reduce the result is to set statmement.setMaxRows(1)... but this is not your case... – Petter Friberg Nov 30 '15 at 16:23
  • Hi Petter,Thx for your attention. I have found the problem ! But i solved it before,i dont know why it still exist ! I found it : java.sql.SQLException: The statement failed because column 'Latitude' (ID=0) uses collation Persian_100_CI_AI ! But i have changed the DB collation to Latin1_General_100_CI_AS When i go to properties of DB, it displays the Latin collation. – Alireza Dec 03 '15 at 08:43

1 Answers1

0

How to execute a query on existing connection.

String sql = "select Latitude from View_4 where IMEI='xxxxx'";
Statement std = null;
ResultSet rs = null;
logger.info("executeQuery - sql=" + sql);
try {
    std = connection.createStatement();
    rs = std.executeQuery(sql);
    logger.info("executeQuery - The query is executed");
    while (rs.next()) {
        String lat = rs.getString("Latitude");
        logger.info("executeQuery - Latitude=" + lat);
    }
    logger.info("executeQuery - Query End");
} catch (SQLException e) {
    logger.error("executeQuery", e); //log the full error
} finally {
    if (rs != null) {
        try {rs.close();} catch (SQLException sqle) {}
    }
    if (std != null) {
        try {std.close();} catch (SQLException sqle) {}
    }
}

In this code example you will see in the log after "executeQuery - sql = .."

Either

executeQuery - The query is executed

executeQuery - Latitude=" ...

executeQuery - Query End

or

Error executeQuery and its stacktrace

AND AFTER THIS YOU WILL UNDERSTAND WHATS GOING ON.., while you have no log means that it is executing the query... firewalls ecc can change this time...

NOTE: Adapt the logger to your (android LOG), don't you Toast try to use the standard LOG.

Community
  • 1
  • 1
Petter Friberg
  • 21,252
  • 9
  • 60
  • 109
  • With this code i found the problem. The collation of DB is my problem, Thx for your attention. I have found the problem ! But i solved it before,i dont know why it still exist ! I found it : java.sql.SQLException: The statement failed because column 'Latitude' (ID=0) uses collation Persian_100_CI_AI ! But i have changed the DB collation to Latin1_General_100_CI_AS When i go to properties of DB, it displays the Latin collation – Alireza Dec 03 '15 at 10:50