Android Question Trouble with SQL Query on Android 4.4.2

[junglejet]

I have this query code running well on Android 2.3 and 4.1. It fails retrieving locations on a Nexus 7 tablet with Android 4.4.2 only. Exactly the same code, no difference. Database is present and initialized. Lat1 and Long1 have valid data. I am running out of ideas. Any hints are appreciated.
Thank you
Andy

'Find locations within 50 km
Sub find_nearestlocs(lat1 As Double,Long1 As Double)
    Dim Cursor1 As Cursor
    Dim lat11,long11,lat21,long21 As Double
  
    If m_sql.sql1.IsInitialized=False Then
        ToastMessageShow("Cannot access locations database",True)
        Return  
    End If
   '>>>>All devices get here
    lat11=lat1-0.5
    lat21=lat1+0.5
    Dim r=0.5/Cos(Long1*cPI/180)
    long11=Long1+r
    long21=Long1-r
  
    Cursor1=m_sql.sql1.ExecQuery2("SELECT * FROM locs1 WHERE lat>? AND lat<? AND long>? AND long<?",Array As String(lat11,lat21,long21,long11))
    If Cursor1.RowCount=0 Then
        ToastMessageShow("No location found within 50km/35mi - "&Round2(lat1,3)&" "&Round2(Long1,3),True) '>>>>Nexus7/Android 4.4.2 ends here
        Return
    End If
    ToastMessageShow("Location(s) found from "&Round2(lat1,3)&" "&Round2(Long1,3),True)
   '>>>> Other devices end here

 

[CidTek]

Have you tested your query on a desktop SQLite browser?

 

[junglejet]

Yes, I tried with SQLliteadmin and the result is ok.
As I said, it works with three other handheld devices on Andorid 2.3 and 4.1.2.
It only does not work on the Nexus 7.

One thing I suspect is locale settings, as our decimal separator is a ",".
The lat, long... values are of the double tyoe in the database.
Could it be that
- Nexus uses "," internally, the others use "."
- the SQLExecQuery2 statement delivers strings with "," for the placeholder double values, that cannot be processed by the SQL library (I am not sure where the SQL processing routines sit)?

On the PC locale is ",", too, and it works, but maybe SQLLiteadmin makes a smarter conversion.

 

[Erel]

Can you upload the database file?

 

[junglejet]

Hi Erel,

thank you for stepping in. I tried to prevent a locale error with the code below, but it did not make a difference. Works on all devices except Nexus 7.

I will send you a link to the database by pm.

Thank you for your support
Andy

    lat11=lat1-0.5
    lat21=lat1+0.5
    Dim r=0.5/Cos(Long1*cPI/180)
    long11=Long1+r
    long21=Long1-r
    s1=lat11
    s1=s1.Replace(",",".")
    s2=lat21
    s2=s2.Replace(",",".")
    s3=long11
    s3=s3.Replace(",",".")
    s4=long21
    s4=s4.Replace(",",".")

    Cursor1=m_sql.sql1.ExecQuery2("SELECT * FROM locs1 WHERE lat>? AND lat<? AND long>? AND long<?",Array _
        As String(s1,s2,s4,s3))
    If Cursor1.RowCount=0 Then
        ToastMessageShow("No location found within 50km/35mi - "&Round2(lat1,3)&" "&Round2(Long1,3),True)
        Return
    End If

 

[Erel]

What is your device locale settings?

Does it work if you change them?

 

[junglejet]

I changed language settings to English, but it did not make any difference.

I am now trying whether it is related to the double datatype definition. The database uses float. I changed that in B4A to float, too, but it does not make a difference either.

CREATE TABLE locs1
(name TEXT(50),
area TEXT(30),
country TEXT(30),
iata TEXT(3),
icao TEXT(4),
lat FLOAT,
long FLOAT,
alt INTEGER,
tz INTEGER,
dst TEXT(3));

From what I read float, double, real are all the same in SQLlite.

• REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

I could not find anything about precision of B4A datatypes, though.

I am stuck

Added:
Precision is not relevant because SQL processes numbers as strings.

I tried this simple query, same thing

Cursor1=m_sql.sql1.ExecQuery("SELECT * FROM locs1 WHERE lat>53.3 AND lat<53.6 ")

Again, works ok on other devices

 

[eps]

What is the error reported?

 

[junglejet]

Resolved:

B4A + Android 4.4 + SQLlite Execs cannot work with SQL datatype float, but only with double (did not try real) in WHERE clause.

Re-generating the database with double fields did the trick. Interestingly the database kept the same size, whether it is double or float.

Closed.

Andy

 

[Erel]

There is no float data type in SQLite: http://www.sqlite.org/datatype3.html

This however doesn't explain why it worked with previous versions. It seems like a bug in Android 4.4.

 

[junglejet]

Erel,

I fully agree.

The problem is hidden somewhere between Android 4.4 and RazorSQL

I converted a CSV file to SQLlite with RazorSQL and its native SQLite driver. This offers REAL, DOUBLE and FLOAT as datatype options. I did not pay any attention to it. But it seems the output is different. I assume Android 4.4 has stepped up to true double processing due to the rounding errors reported, while previous versions did some single precision processing in a double fake format.

Anyhow, thanks again for no tletting your customers alone

-- Andy