Load ST_GEOMETRY .dll extension into SQLite/mobile geodatabase — Error: "not authorized"

[Bud]

I want to use ST_GEOMETRY functions in SQL queries in a ArcGIS Pro 3.1 mobile geodatabase (Windows).

The Load ST_Geometry to a mobile geodatabase for SQL access docs say:

Load the ST_Geometry library.

This example loads the ST_Geometry library to an SQLite database on a Microsoft Windows computer:

SELECT load_extension('stgeometry_sqlite.dll','SDE_SQL_funcs_init');

Here's what I've tried:

I downloaded the .DLL from MyEsri:

Using Beekeeper Studio SQL client, I connected to the mobile geodatabase and tried to run this SQL statement:

SELECT load_extension('stgeometry_sqlite.dll','SDE_SQL_funcs_init');

But I get an error:

not authorized

Anyone know how I can get around that error?

[MarlonAmaya]

Hi @Bud 

First, try specifying the full path of where the file is.

(i.e -' C:\desktop\stgeometry_sqlite.dll' )

Is this a mobile geodatabase created in Pro 3.1? If not, are you getting the same behavior is you create a new mobile geodatabase using Pro 3.1?

Marlon

[Bud]

@MarlonAmaya Thanks for the suggestions.

I tried using the full path, but unfortunately I got the same error.

Yes, the mobile geodatabase was created in Pro 3.1.0. 

I also tried restarting my computer and creating a new mobile geodatabase. I get the same error, whether I use the full .dll path or just the .dll name.

I actually did a different test where I replaced the proper text arguments with 'fake text', just to see if I'd get a different error or not:

I get the same error.

So I guess that means the error is caused by the mobile GDB SQLite database, possibly indicating that extensions are disabled in the SQLite database? So the problem isn't caused by the .dll or ST_GEOMETRY.

Does that sound right?

[MarlonAmaya]

Hi@Bud ,

I agree with you, it does not sound like there is an issue with the file. There could be something going on with the connection the the mobile database. Maybe a driver issue?  I went ahead and tested on a 3.1 mobile geodatabase and it worked with full path. I am using DBeaver and making a SQLite connection prior to running the query.

Marlon

[Bud · Accepted solution]

It worked with DBeaver! Thanks.

For what it's worth, DBeaver popped up this message, "SQLite driver files are missing." 
So I downloaded the drivers using DBeaver.

Like you said, I used the full path to the location where I downloaded the .DLL (64-bit): 

SELECT load_extension('C:\SQLite\Windows64\stgeometry_sqlite.dll','SDE_SQL_funcs_init');

Then, as the docs suggest: "Call the CreateOGCTables function to add ST_Geometry tables to the database."

SELECT CreateOGCTables();

Now, I'm able to run SQL queries that use ST_GEOMETRY functions on the mobile geodatabase:

   SELECT objectid
    FROM (SELECT ply.objectid,
                 row_number() over(partition by ply.objectid order by null) rn
            FROM ply
      CROSS JOIN pnt
           WHERE st_intersects(ply.shape, pnt.shape) = 1
         )
   WHERE rn = 1

Query source: Spatial Query Benchmarking

Ideas:

• Clarify the docs: Load ST_Geometry extension to mobile GDB — Use full path of .DLL
• Enable ST_GEOMETRY in mobile geodatabase using GP Tool

[Bud]

It's a shame that Beekeeper Studio wasn't able to load the SQLite extension, especially since I paid money for Beekeeper Studio to use it for this very purpose.

Edit:

I reported the issue to BeekeeperStudio Support. They said:

Beekeeper Studio has to bundle the SQLite libraries, so I'm not sure of the process of loading an extension (it does come with a few extensions pre-loaded).

I'm sure it's possible, but need to investigate.