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

1426
5
Jump to solution
03-31-2023 12:51 AM
Labels (1)
Bud
by
Notable Contributor

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:

Bud_0-1680248394378.png


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');

Bud_1-1680248579079.png

But I get an error:

not authorized


Anyone know how I can get around that error?

 

 

0 Kudos
1 Solution

Accepted Solutions
Bud
by
Notable Contributor

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.

Bud_1-1680574710731.png

 

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:

View solution in original post

5 Replies
MarlonAmaya
Esri Contributor

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

0 Kudos
Bud
by
Notable Contributor

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

Bud_0-1680322720041.png

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:

Bud_1-1680323282615.png

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?

0 Kudos
MarlonAmaya
Esri Contributor

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

0 Kudos
Bud
by
Notable Contributor

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.

Bud_1-1680574710731.png

 

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:

Bud
by
Notable Contributor

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.

 

0 Kudos