Android Question Trouble with SQL Query on Android 4.4.2

junglejet

Active Member
Licensed User
Longtime User
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

B4X: 
'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
 
Sort by date Sort by votes

junglejet

Active Member
Licensed User
Longtime User
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.
 
Upvote 0

junglejet

Active Member
Licensed User
Longtime User
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

B4X: 
    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
 
Last edited:
Upvote 0

junglejet

Active Member
Licensed User
Longtime User
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.

B4X: 
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.
    Click to expand...
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

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

Again, works ok on other devices
 
Last edited:
Upvote 0

junglejet

Active Member
Licensed User
Longtime User
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
 
Upvote 0

junglejet

Active Member
Licensed User
Longtime User
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
 
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…