PostgreSQL not working 100% on android application

Question

i'm here because i have a problem with postgreSQL on android .... I coded earlier on a java class, a code which connects to my external database ( postgreSQL) and can send and receive queries (It works 100% , i tested the same query with the application pgAdmin3 on MAC and both does have the same results). When i saw that this was working really good, i decided to export that code to my android application ... The problem is: that query

public static final String queryLojasCidadaoOrderByName = "SELECT * FROM public.lojas_cidadao ORDER BY nome_loja_cidadao";

isn't working on my android application ... when on java application its working good ! BUT that query

public static final String queryLojasCidadao = "SELECT * FROM public.lojas_cidadao ";

its working on my on android application !

Is there a problem with ORDER BY ?

Any help will be welcomed ! :) Thank you !

Log of my running application:

05-22 11:13:06.625: D/AbsListView(21421): unregisterIRListener() is called 
05-22 11:13:06.835: D/AbsListView(21421): onVisibilityChanged() is called, visibility : 4
05-22 11:13:06.845: D/AbsListView(21421): unregisterIRListener() is called 
05-22 11:13:16.014: D/skia(21882): ---- fAsset->read(2074) returned 0
05-22 11:13:16.064: D/dalvikvm(21882): GC_FOR_ALLOC freed 78K, 6% free 17421K/18408K, paused 20ms, total 20ms
05-22 11:13:16.094: I/dalvikvm-heap(21882): Grow heap (frag case) to 25.807MB for 8294416-byte allocation
05-22 11:13:16.114: D/dalvikvm(21882): GC_FOR_ALLOC freed 1K, 4% free 25519K/26512K, paused 18ms, total 18ms
05-22 11:13:16.124: D/dalvikvm(21882): GC_CONCURRENT freed <1K, 4% free 25519K/26512K, paused 2ms+2ms, total 16ms
05-22 11:13:16.144: D/dalvikvm(21882): GC_FOR_ALLOC freed <1K, 4% free 25519K/26512K, paused 15ms, total 16ms
05-22 11:13:16.194: I/dalvikvm-heap(21882): Grow heap (frag case) to 43.603MB for 18662416-byte allocation
05-22 11:13:16.214: D/dalvikvm(21882): GC_FOR_ALLOC freed <1K, 3% free 43744K/44740K, paused 23ms, total 23ms
05-22 11:13:16.234: D/dalvikvm(21882): GC_CONCURRENT freed 0K, 3% free 43744K/44740K, paused 3ms+3ms, total 21ms
05-22 11:13:19.157: W/System.err(21882): java.lang.NumberFormatException: Invalid double: "a"
05-22 11:13:19.157: W/System.err(21882):    at java.lang.StringToReal.invalidReal(StringToReal.java:63)
05-22 11:13:19.167: W/System.err(21882):    at java.lang.StringToReal.parseDouble(StringToReal.java:269)
05-22 11:13:19.167: W/System.err(21882):    at java.lang.Double.parseDouble(Double.java:295)
05-22 11:13:19.167: W/System.err(21882):    at com.example.basicmaponline.Intro$loadDatabase.doInBackground(Intro.java:102)
05-22 11:13:19.167: W/System.err(21882):    at com.example.basicmaponline.Intro$loadDatabase.doInBackground(Intro.java:1)
05-22 11:13:19.167: W/System.err(21882):    at android.os.AsyncTask$2.call(AsyncTask.java:287)
05-22 11:13:19.167: W/System.err(21882):    at java.util.concurrent.FutureTask.run(FutureTask.java:234)
05-22 11:13:19.167: W/System.err(21882):    at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:230)
05-22 11:13:19.167: W/System.err(21882):    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1080)
05-22 11:13:19.167: W/System.err(21882):    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:573)
05-22 11:13:19.167: W/System.err(21882):    at java.lang.Thread.run(Thread.java:856)
05-22 11:13:19.177: D/ASYNC(21882): entrei no if da listaLojas
05-22 11:13:19.227: D/libEGL(21882): loaded /system/lib/egl/libEGL_adreno200.so
05-22 11:13:19.237: D/libEGL(21882): loaded /system/lib/egl/libGLESv1_CM_adreno200.so
05-22 11:13:19.237: D/libEGL(21882): loaded /system/lib/egl/libGLESv2_adreno200.so
05-22 11:13:19.247: I/Adreno200-EGL(21882): <qeglDrvAPI_eglInitialize:265>: EGL 1.4 QUALCOMM build:  (CL3544079)
05-22 11:13:19.247: I/Adreno200-EGL(21882): Build Date: 03/28/13 Thu
05-22 11:13:19.247: I/Adreno200-EGL(21882): Local Branch: adreno_20130328
05-22 11:13:19.247: I/Adreno200-EGL(21882): Remote Branch: 
05-22 11:13:19.247: I/Adreno200-EGL(21882): Local Patches: 
05-22 11:13:19.247: I/Adreno200-EGL(21882): Reconstruct Branch: 
05-22 11:13:19.297: D/OpenGLRenderer(21882): Enabling debug mode 0
05-22 11:13:22.320: D/AbsListView(21882): Get MotionRecognitionManager
05-22 11:13:22.330: D/AbsListView(21882): onVisibilityChanged() is called, visibility : 4
05-22 11:13:22.330: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:22.330: D/AbsListView(21882): onVisibilityChanged() is called, visibility : 0
05-22 11:13:22.330: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:22.340: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:22.340: I/endeffect(21882): AbsListView.onMeasure(), getWidth()=0, getHeight()=0, this=android.widget.GridView{43b8b2f8 VFED.VC. ......I. 0,0-0,0 #7f070003 app:id/grid}
05-22 11:13:22.380: D/dalvikvm(21882): GC_FOR_ALLOC freed 8263K, 20% free 36922K/46100K, paused 21ms, total 21ms
05-22 11:13:22.420: I/dalvikvm-heap(21882): Grow heap (frag case) to 45.942MB for 9437200-byte allocation
05-22 11:13:22.470: D/dalvikvm(21882): GC_FOR_ALLOC freed 1K, 17% free 46137K/55320K, paused 49ms, total 49ms
05-22 11:13:22.500: D/dalvikvm(21882): GC_CONCURRENT freed 27K, 17% free 46110K/55320K, paused 13ms+3ms, total 34ms
05-22 11:13:22.520: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:22.520: I/endeffect(21882): AbsListView.onLayout(), getWidth()=1080, getHeight()=1701, this=android.widget.GridView{43b8b2f8 VFED.VC. ......ID 0,0-1080,1701 #7f070003 app:id/grid}
05-22 11:13:22.520: I/endeffect(21882): AbsListView.onMeasure(), getWidth()=1080, getHeight()=1701, this=android.widget.GridView{43b8b2f8 VFED.VC. .F....ID 0,0-1080,1701 #7f070003 app:id/grid}
05-22 11:13:22.520: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:22.520: I/endeffect(21882): AbsListView.onLayout(), getWidth()=1080, getHeight()=1701, this=android.widget.GridView{43b8b2f8 VFED.VC. .F....ID 0,0-1080,1701 #7f070003 app:id/grid}
05-22 11:13:22.660: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:22.670: I/endeffect(21882): AbsListView.onMeasure(), getWidth()=1080, getHeight()=1701, this=android.widget.GridView{43b8b2f8 VFED.VC. .F....I. 0,0-1080,1701 #7f070003 app:id/grid}
05-22 11:13:22.670: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:22.670: I/endeffect(21882): AbsListView.onLayout(), getWidth()=1080, getHeight()=1701, this=android.widget.GridView{43b8b2f8 VFED.VC. .F....ID 0,0-1080,1701 #7f070003 app:id/grid}
05-22 11:13:22.740: E/SpannableStringBuilder(21882): SPAN_EXCLUSIVE_EXCLUSIVE spans cannot have a zero length
05-22 11:13:22.740: E/SpannableStringBuilder(21882): SPAN_EXCLUSIVE_EXCLUSIVE spans cannot have a zero length
05-22 11:13:27.435: D/LISTA(21882): ENTREI AQUI CARALHOOO NA LISTA
05-22 11:13:27.435: D/AbsListView(21882): Get MotionRecognitionManager
05-22 11:13:27.435: D/AbsListView(21882): onVisibilityChanged() is called, visibility : 4
05-22 11:13:27.435: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:27.445: D/AbsListView(21882): onVisibilityChanged() is called, visibility : 0
05-22 11:13:27.445: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:27.445: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:27.455: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:27.455: I/endeffect(21882): AbsListView.onMeasure(), getWidth()=0, getHeight()=0, this=android.widget.ListView{43b6a938 V.ED.VC. ......I. 0,0-0,0 #102000a android:id/list}
05-22 11:13:27.475: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:27.475: I/endeffect(21882): AbsListView.onLayout(), getWidth()=1080, getHeight()=1701, this=android.widget.ListView{43b6a938 V.ED.VC. ......ID 0,0-1080,1701 #102000a android:id/list}
05-22 11:13:27.585: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:27.585: I/endeffect(21882): AbsListView.onMeasure(), getWidth()=1080, getHeight()=1701, this=android.widget.ListView{43b6a938 V.ED.VC. ......I. 0,0-1080,1701 #102000a android:id/list}
05-22 11:13:27.585: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:27.595: I/endeffect(21882): AbsListView.onLayout(), getWidth()=1080, getHeight()=1701, this=android.widget.ListView{43b6a938 V.ED.VC. ......ID 0,0-1080,1701 #102000a android:id/list}
05-22 11:13:27.645: E/SpannableStringBuilder(21882): SPAN_EXCLUSIVE_EXCLUSIVE spans cannot have a zero length
05-22 11:13:27.645: E/SpannableStringBuilder(21882): SPAN_EXCLUSIVE_EXCLUSIVE spans cannot have a zero length
05-22 11:13:27.655: D/AbsListView(21882): onVisibilityChanged() is called, visibility : 4
05-22 11:13:27.655: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:30.428: D/AbsListView(21882): Get MotionRecognitionManager
05-22 11:13:30.428: D/AbsListView(21882): onVisibilityChanged() is called, visibility : 4
05-22 11:13:30.428: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:30.428: D/AbsListView(21882): onVisibilityChanged() is called, visibility : 0
05-22 11:13:30.428: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:30.438: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:30.438: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:30.438: I/endeffect(21882): AbsListView.onMeasure(), getWidth()=0, getHeight()=0, this=android.widget.GridView{42202b48 VFED.VC. ......I. 0,0-0,0 #7f070003 app:id/grid}
05-22 11:13:30.458: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:30.458: I/endeffect(21882): AbsListView.onLayout(), getWidth()=1080, getHeight()=1701, this=android.widget.GridView{42202b48 VFED.VC. ......ID 0,0-1080,1701 #7f070003 app:id/grid}
05-22 11:13:30.468: I/endeffect(21882): AbsListView.onMeasure(), getWidth()=1080, getHeight()=1701, this=android.widget.GridView{42202b48 VFED.VC. .F....ID 0,0-1080,1701 #7f070003 app:id/grid}
05-22 11:13:30.468: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:30.468: I/endeffect(21882): AbsListView.onLayout(), getWidth()=1080, getHeight()=1701, this=android.widget.GridView{42202b48 VFED.VC. .F....ID 0,0-1080,1701 #7f070003 app:id/grid}
05-22 11:13:30.508: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:30.518: I/endeffect(21882): AbsListView.onMeasure(), getWidth()=1080, getHeight()=1701, this=android.widget.GridView{42202b48 VFED.VC. .F....I. 0,0-1080,1701 #7f070003 app:id/grid}
05-22 11:13:30.518: D/AbsListView(21882): unregisterIRListener() is called 
05-22 11:13:30.518: I/endeffect(21882): AbsListView.onLayout(), getWidth()=1080, getHeight()=1701, this=android.widget.GridView{42202b48 VFED.VC. .F....ID 0,0-1080,1701 #7f070003 app:id/grid}
05-22 11:13:30.568: E/SpannableStringBuilder(21882): SPAN_EXCLUSIVE_EXCLUSIVE spans cannot have a zero length
05-22 11:13:30.568: E/SpannableStringBuilder(21882): SPAN_EXCLUSIVE_EXCLUSIVE spans cannot have a zero length
05-22 11:13:30.568: D/AbsListView(21882): onVisibilityChanged() is called, visibility : 4
05-22 11:13:30.568: D/AbsListView(21882): unregisterIRListener() is called

Intro class of my android application:

package com.example.basicmaponline;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.concurrent.ExecutionException;

import android.app.Activity;
import android.content.Intent;
import android.os.AsyncTask;
import android.os.Bundle;
import android.util.Log;

import com.mlab.android.basicoverlays.PostgreSQL;
import com.mlab.android.basicoverlays.SQLloja;

public class Intro extends Activity{

//SQLlistLoja listaLoja;
//HashMap<String, SQLloja> listaLoja;
ArrayList<SQLloja> listaLoja;

@Override
protected void onCreate(Bundle savedInstanceState) {
    // TODO Auto-generated method stub
    super.onCreate(savedInstanceState);
    setContentView(R.layout.intro);
    //Intent openMainActivity = new Intent("com.example.basicmaponline.MAINACTIVITY"); //isto vem do ficheiro AndroidManifest.xml o "com.example....."
    //startActivity(openMainActivity);

    try {
        listaLoja = new loadDatabase().execute().get();
    } catch (InterruptedException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    } catch (ExecutionException e) {        
        // TODO Auto-generated catch block
        e.printStackTrace();
    } 

    if(listaLoja !=null){
        for(SQLloja loja : listaLoja){
            Log.d("ASYNC25", loja.getNome() );
        }
    }
    else{
        Log.d("ASYNC25", "NAO GUARDOU!" );
    }

    Thread timer = new Thread(){
        public void run(){
            try{
                sleep(3000);
            }catch (InterruptedException e) {
                // TODO: handle exception
                e.printStackTrace();
            }finally{
                Intent openMenu = new Intent("com.example.basicmaponline.MENU"); //isto vem do ficheiro AndroidManifest.xml o "com.example....."
                openMenu.putExtra("listaLoja",listaLoja);
                startActivity(openMenu);
            }
        }
    };
    timer.start();
}

@Override
protected void onPause() {
    // TODO Auto-generated method stub
    super.onPause();
    //ourSong.release(); //release the music and we are done with you
    finish();
}

//@SuppressWarnings("rawtypes")
public class loadDatabase extends AsyncTask<Void, Void, ArrayList<SQLloja>>{

    @Override
    //protected HashMap<String,SQLloja> doInBackground(Void... params) {
    protected ArrayList<SQLloja> doInBackground(Void... params) {   
        // TODO Auto-generated method stub

        //HashMap<String,SQLloja>listaLojas = new HashMap<String, SQLloja>();
        ArrayList<SQLloja> listaLojas = new ArrayList<SQLloja>();
    try{    
        PostgreSQL pSQL = new PostgreSQL();
        //String sql = pSQL.getLojasCidadao();
        String sql = PostgreSQL.queryLojasCidadaoOrderByName; // public static final String queryLojasCidadaoOrderByName = "SELECT * FROM public.lojas_cidadao ORDER BY nome_loja_cidadao";
        Statement st = pSQL.getConnection().createStatement();
        ResultSet rs = st.executeQuery(sql);

        //Log.d("ASYNC2","Entrei na thread ASYNCTASK");

        while(rs.next()){
            int lcId = Integer.parseInt(rs.getString(1));
            String lcNome=rs.getString(2);
            String lcCP = rs.getString(3);
            int lcDistrito = Integer.parseInt(rs.getString(4));
            int lcConselho = Integer.parseInt(rs.getString(5));
            double lcAltitude = Double.parseDouble(rs.getString(6));
            double lcLongitude = Double.parseDouble(rs.getString(7));
            String lcTelefone = rs.getString(8);
            boolean lcEstado = Boolean.parseBoolean(rs.getString(9));
            String lcRua = rs.getString(10);

            SQLloja loja = new SQLloja(lcId,lcNome,lcCP,lcDistrito,lcConselho,lcAltitude,lcLongitude,lcTelefone,lcEstado,lcRua);

            //listaLojas.put(loja.getNome(),loja.clone());
            listaLojas.add(loja.clone());

            String informacoesLoja = "Rua : "+lcRua+"\nC.P. : "+lcCP+"\nTel. : "+lcTelefone;

            Log.d("ASYNC",lcNome+" Altitude = "+lcAltitude+" Longitude = "+lcLongitude+" Rua = "+lcRua);
        }
        //listaLoja = new SQLlistLoja(listaLojas);
        rs.close();
        st.close();

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (Exception e) {
    e.printStackTrace();
    }

    if(listaLojas!=null){
        Log.d("ASYNC","entrei no if da listaLojas");
        for(SQLloja loja : listaLojas){
            Log.d("ASYNC", loja.getNome() );
        }
    }
    else Log.d("ASYNC","listaLoja nula ! PQP !");


        return listaLojas;

        /*HashMap<String ,SQLloja> listaLojas = new HashMap<String, SQLloja>();

        try {
            PostgreSQL pSQL = new PostgreSQL();
            listaLojas = pSQL.getLojasCidadao();

            if(listaLojas !=null){
                for(SQLloja loja : listaLoja.values()){
                    Log.d("DOIN", loja.getNome() );
                }
            }
            else Log.d("DOIN","NOT WOR");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        if(listaLojas !=null){
            for(SQLloja loja : listaLoja.values()){
                Log.d("DOIN2", loja.getNome() );
            }
        }
        else Log.d("DOIN2","NOT WOR");


        return listaLojas;*/
    }

    @Override
    protected void onPostExecute(ArrayList<SQLloja> listaLojas){
        listaLoja = listaLojas;
        for(SQLloja loja : listaLoja){
            Log.d("ASYNC24", loja.getNome() );
        }

    }

}

}

*How* is it not working? Does it produce an error message, does it fail to return the expected results? Are you using the same version of the driver? — Bruno, May 22 '13 at 09:59
Yes i am using the same version of postgreSQL in both ( jar : postgresql-9.2-1002.jdbc4.jar), i will post the log of my running application and see if there is a problem . — Damiii, May 22 '13 at 10:14
Are you connecting to the exact same database, with the same data, with your Java and Android applications? — Bruno, May 22 '13 at 11:16

mihail · Answer 1 · 2013-05-22T10:30:58.027

0

maybe it doesn't find the column name since you call the table as public.lojas_cidadao. Try this :

public static final String queryLojasCidadaoOrderByName = "SELECT * FROM public.lojas_cidadao A ORDER BY A.nome_loja_cidadao";

you get the error in one of those lines:

double lcAltitude = Double.parseDouble(rs.getString(6));
double lcLongitude = Double.parseDouble(rs.getString(7));

probably when you put the ORDER BY the returned data have reordered columns.

In other words, examine the ResultSet with and without ORDER BY clause and check where is the difference. Good luck.

edited May 22 '13 at 10:30

answered May 22 '13 at 09:55

mihail

2,173
19
31

I tried what you told me and it is doing the same thing as mine ( basically not returning anything at least not showing the data ) – Damiii May 22 '13 at 10:08
show some source code of how you are running the query and maybe some logcat if there is an error – mihail May 22 '13 at 10:16
i updated my post which it shows my logcat of my running application ! :) – Damiii May 22 '13 at 10:19
post your code also - you see a `java.lang.NumberFormatException: Invalid double: "a"` in `com.example.basicmaponline.Intro$loadDatabase.doInBackground(Intro.java:102)` . I (and nobody) can't predict what you have inside this AsyncTask – mihail May 22 '13 at 10:22
Oh, so you are saying that the result of my query from my pgAdmin application may not be the same as the result from android application even if it is used with the same and exactly query?? – Damiii May 22 '13 at 11:10
just debug it and check the result – mihail May 22 '13 at 11:23

score 0 · Accepted Answer · edited May 23 '17 at 12:11

What you're getting is a java.lang.NumberFormatException, due to trying to parse a string that doesn't represent a Double as a Double. There's no reason why this should have anything to do with PostgreSQL.

There are a number of issues you can address in your application:

Firstly, you seem to be expecting certain text/char/varchar columns to be consistently populated with strings representing doubles or integers. If you're sure that these column numbers will always have values that have these types, this sounds like a mistake in the database design: PostgreSQL (like most other RDMBS) supports typed columns, in particular, INTEGER and REAL.

If for independent reasons you do need to keep those columns as text/char/varchar, you should really catch these NumberFormatException, since they may occur again depending on what's inserted later on in your table.
Secondly, using SELECT * is rarely a good idea (see this question). It leads to bugs in general. The reason for this is that it hides which columns you where intending to retrieve. The structure of the database or table may change, independently of the application using it. Assuming the column order forces the programmer to know the current state of the column order in the database. There are a couple of solutions (you can actually use both at the same time):
- Use the column names explicitly in your SELECT statement (i.e. SELECT id, name,...): you'll find your code more readable in general.
- Use the ResultSet metadata to find the name of the column at that position (see this question), or use the accessor that uses the columnLabel instead of the columnIndex.

That's what i did, i didn't used the "*" and now it's working like a charm ! :) Thank you ! — Damiii, May 22 '13 at 13:22

PostgreSQL not working 100% on android application

2 Answers2