CraueGeoBundle & sqlite3: SQLSTATE[HY000]: General error: 1 no such function: ASIN

[azine]

Hi,

thank you very much for the cool bundle. Works like a charm with my MySQL backend.

...unfortunately it does not with my installation of sqlite3 (on Ubuntu 15.04) that I use to run unit-tests on.

I always get the following error "SQLSTATE[HY000]: General error: 1 no such function: ASIN".

Googling around I found out the cause could be, that I need to install an extension for sqlite (=> http://www.sqlite.org/contrib/download/extension-functions.c?get=25).

As I didn't manage to install it on my Ubuntu machine, I wanted to ask two things:

1) am I on the right track an must install that extension or is it something else? 2) do you know of a place/url with (working) instructions to get the extension installed?

Thanx a lot for your help. Dominik

[craue]

I'm afraid I cannot help you with this. SQLite is currently not supported and I guess that won't change due to these missing math functions.

[azine]

Hi Christian,

thanx for the feedback. I was afraid that would be your answer. :-/

If I manage to work around the problem (e.g. install the extensions), I'll post a short "how-to" here.

Cheers, Dominik

2016-04-22 5:40 GMT+02:00 Christian Raue notifications@github.com:

I'm afraid I cannot help you with this. SQLite is currently not supported and I guess that won't change due to these missing math functions.

— You are receiving this because you authored the thread. Reply to this email directly or view it on GitHub https://github.com/craue/CraueGeoBundle/issues/12#issuecomment-213232181

[robertfausk]

@azine I think you're on the right way. Maybe this will help you: https://www.sqlite.org/loadext.html

[robertfausk]

@azine I investigated little bit further. It seems that you first have to install the extension and then load the extension via load_extension() Additionally you have to allow/enable extension loading for sqlite.

Following library uses extension loading: https://github.com/brick/geo#using-sqlite3-with-spatialite I think for our use case we simply have to use the math extension instead of spatialite extension.

[azine]

Hi @robertfausk,

I already managed to get the math functions from "extension-functions.c" loaded into sqlite.

Instructions are inside the extension-functions.c file... I used gcc -fPIC -shared extension-functions.c -o libsqlitefunctions.so -lm to compile the code (note the -lm is at the end of the line).

I can now call the new math functions by executing a "native query" through Doctrine.

But there is still one step missing, because Doctrine does not yet know that sqlite is able to handle those math functions. The functions are not part of the doctrine-sqlite-mapping.

So I'll have to look into "teaching" Doctrine that sqlite is now able to calculate asin and acos etc.

When I managed to get all the pieces to work, I'll post some instructions here.

Cheers,

[azine]

Hi,

I managed to get the asin/acos functions working in sqlite and also in doctrine by compiling the extension as described above, placing it in bin/sqlite-extension/libsqlitefunctions.so and adding the following code to the app/AppKernel.php.

    protected function initializeContainer() {

        parent::initializeContainer();

        if ('test' !== $this->getEnvironment()) {
            return;
        }

        // load the math function for sqlite
        $manager = $this->getContainer()->get('doctrine.orm.entity_manager');
        if($manager->getConnection()->getDatabasePlatform() instanceof \Doctrine\DBAL\Platforms\SqlitePlatform){
            $extensionFile = realpath(__DIR__."/../bin/sqlite-extension/libsqlitefunctions.so");
            $statement = $manager->getConnection()->prepare("SELECT load_extension('$extensionFile');");
            if(!$statement->execute()){
                throw new \Exception("unable to load libsqlitefunctions.so from $extensionFile");
            }
            $statement = $manager->getConnection()->prepare("SELECT ASIN(1) as a;");
            $statement->execute();
            $result = $statement->fetchAll();
            if($result[0]['a'] !== "1.5707963267949"){
                throw new \Exception("unable to execute the asin function, loading libsqlitefunctions.so from $extensionFile probably failed.");
            }
        }
    }

It's probably not the nicest way, but for now it works.

Cheers,

[Headd2k]

For Windows users: My PHP on my Windows machine also complained about the missing "ASIN" function when using SQLite.

I was able to fix this by replacing the "libsqlite3.dll" file found in my PHP directory with the sqlite3.dll (rename to libsqlite3.dll) found in the .zip file on the official SQLite download page (https://www.sqlite.org/download.html) under the section "Precompiled Binaries for Windows".