SQLLite function

[marcel]

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

 

[klaus]

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.

Best regards.

 

[mc73]

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.

 

[marcel]

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.

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

#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);
}

 

[Erel]

Why is SQLite needed here at all? Why not execute the calculations in B4A?

 

[marcel]

Why is SQLite needed here at all? Why not execute the calculations in B4A?

This makes it much more easier and quicker to run my queries.

 

[klaus]

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.

Dim Loc1, Loc2 As Location
    
Loc1.Initialize2(lat1, lon1)
Loc2.Initialize2(lat2, lon2)
Dist = Loc1.DistanceTo(Loc2)

Best regards.

 

[keirS]

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.

 

[marcel]

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.

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.

 

[marcel]

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.

Dim Loc1, Loc2 As Location
    
Loc1.Initialize2(lat1, lon1)
Loc2.Initialize2(lat2, lon2)
Dist = Loc1.DistanceTo(Loc2)

Best regards.

Correct but I like to do thinks like :

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

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.....

 

[keirS]

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.

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.

 

[marcel]

I agree it would be great to have. Spacialite makes GIS apps very easy to build.

Yes, It would Ok. Then I would like to find another solution.....

 

[peterecollins63]

Spatialite Android

Has anyone worked out how to use the new SpatiaLite binaries from https://www.gaia-gis.it/fossil/libspatialite/wiki?name=splite-android in b4a.

Thanks,
Pete - Aus

 

[Erel]

Please start a new thread for this question.