SQLLite function

marcel

marcel

Active Member
Licensed User
Longtime User
Hi,

In my Iphone app I build a SQL Lite function. Is there a way to do this also with B4A?

Would be great!

Thanks
 
Sort by date Sort by votes
mc73

mc73

Well-Known Member
Licensed User
Longtime User
If you mean to let user create his very own query from inside your app, the answer is yes, you should simply dim a string and execute the query entered in a textBox.
 
Upvote 0
marcel

marcel

Active Member
Licensed User
Longtime User
klaus said:
What exactly do you want to do ?
You should have a look at the SQL library.
You could have a look at the SQLite Database chapter in the User's Guide.
Click to expand...

I have created this SQLite function that I use on the apple iOs. I need to do something similar using B4A.

B4X: 
#define DEG2RAD(degrees) (degrees * 0.01745327) // degrees * pi over 180

static void distanceFunc(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    // check that we have four arguments (lat1, lon1, lat2, lon2)
    assert(argc == 4);
    // check that all four arguments are non-null
    if (sqlite3_value_type(argv[0]) == SQLITE_NULL || sqlite3_value_type(argv[1]) == SQLITE_NULL || sqlite3_value_type(argv[2]) == SQLITE_NULL || sqlite3_value_type(argv[3]) == SQLITE_NULL) {
        //sqlite3_result_null(context);
        //return;
    }
    // get the four argument values
    double lat1 = sqlite3_value_double(argv[0]);
    double lon1 = sqlite3_value_double(argv[1]);
    double lat2 = sqlite3_value_double(argv[2]);
    double lon2 = sqlite3_value_double(argv[3]);
    // convert lat1 and lat2 into radians now, to avoid doing it twice below
    double lat1rad = DEG2RAD(lat1);
    double lat2rad = DEG2RAD(lat2);
    // apply the spherical law of cosines to our latitudes and longitudes, and set the result appropriately
    // 6378.1 is the approximate radius of the earth in kilometres
    
    double dist = acos(sin(lat1rad) * sin(lat2rad) + cos(lat1rad) * cos(lat2rad) * cos(DEG2RAD(lon2) - DEG2RAD(lon1))) * 6378.1 * 1000;
    
    sqlite3_result_double(context, acos(sin(lat1rad) * sin(lat2rad) + cos(lat1rad) * cos(lat2rad) * cos(DEG2RAD(lon2) - DEG2RAD(lon1))) * 6378.1 * 1000);
}
 
Last edited:
Upvote 0
klaus

klaus

Expert
Licensed User
Longtime User
I don't understand exactly how your routine does work with SQL.
But from the principle it should be possible.
To calculate the distance between two locations you could use the function DistanceTo with the Location object from the GPS library. The calculation is more accurate than the Grand Circle formula.
B4X: 
Dim Loc1, Loc2 As Location
    
Loc1.Initialize2(lat1, lon1)
Loc2.Initialize2(lat2, lon2)
Dist = Loc1.DistanceTo(Loc2)
Best regards.
 
Upvote 0
K

keirS

Well-Known Member
Licensed User
Longtime User
You can't do this with B4A. I think the SpatiaLite SQLite library will do what you are wanting to do. https://www.gaia-gis.it/fossil/libspatialite/index.

The problem you will have is that by default loading extensions is disabled in SQLite in Android. You would need get the instance handle for the SQLite DB and then work out how to call sqlite3_enable_load_extension from B4A and pass it the instance handle. Then it would be a simple case of loading spatialite.so using the "SELECT load_extension"

The only way I got this to work was on a rooted table with a custom ROM where Spatialite had already been built into the SQLite package. Which isn't really a satisfactory solution.
 
Upvote 0
marcel

marcel

Active Member
Licensed User
Longtime User
keirS said:
You can't do this with B4A. I think the SpatiaLite SQLite library will do what you are wanting to do. https://www.gaia-gis.it/fossil/libspatialite/index.

The problem you will have is that by default loading extensions is disabled in SQLite in Android. You would need get the instance handle for the SQLite DB and then work out how to call sqlite3_enable_load_extension from B4A and pass it the instance handle. Then it would be a simple case of loading spatialite.so using the "SELECT load_extension"

The only way I got this to work was on a rooted table with a custom ROM where Spatialite had already been built into the SQLite package. Which isn't really a satisfactory solution.
Click to expand...

I don't think this is needed.

Create Or Redefine SQL Functions

When you take a look here than SQlite has not the ability to have a function like MS SQL or MY SQL but maps this to C. So I expect this should also possible with java. This can also be done as well with PHP and a few other functions. (PHP: sqlite_create_function - Manual.

Would be a nice extention on the SQL library.
 
Upvote 0
marcel

marcel

Active Member
Licensed User
Longtime User
klaus said:
I don't understand exactly how your routine does work with SQL.
But from the principle it should be possible.
To calculate the distance between two locations you could use the function DistanceTo with the Location object from the GPS library. The calculation is more accurate than the Grand Circle formula.
B4X: 
Dim Loc1, Loc2 As Location
    
Loc1.Initialize2(lat1, lon1)
Loc2.Initialize2(lat2, lon2)
Dist = Loc1.DistanceTo(Loc2)
Best regards.
Click to expand...

Correct but I like to do thinks like :

SQL.ExecNonQuery2("SELECT * from table1 where distance(latitude,longitude,?,?)<?",Array As Object(curLongitudePos,curLatitudePos,range))
Click to expand...

cur... gets their positions form the GPS and the range value is set by the user in meters (f.e. 1000)

if I need to do this with the GPS library I need to first get a list from the database and finetune this with the functions you are mentioned.

I can probably reduce the list with some tricks but I prefer the user defined function. But is this is not possible I will look for an alternative..... :)
 
Upvote 0
K

keirS

Well-Known Member
Licensed User
Longtime User
marcel said:
I don't think this is needed.

Create Or Redefine SQL Functions

When you take a look here than SQlite has not the ability to have a function like MS SQL or MY SQL but maps this to C. So I expect this should also possible with java. This can also be done as well with PHP and a few other functions. (PHP: sqlite_create_function - Manual.

Would be a nice extention on the SQL library.
Click to expand...

The problem still remains the same. There is no way to call this natively from Java or B4A. The android.database.sqlite API doesn't support it You would need to use the Android NDK to get access to SQLite and write a routine to do what you want and then write a JNI library to interact with it. Pretty much exactly the same steps needed to write an extension library and get it to load.

I agree it would be great to have. Spacialite makes GIS apps very easy to build.
 
Upvote 0
You must log in or register to reply here.
Top