What SQL client do you recommend for querying mobile geodatabases?

Bud — Mon, 05 Dec 2022 04:51:02 GMT

What SQL client do you recommend for querying mobile geodatabases? (SQLite)

For example, Toad, SQuirreL, or something like that.

Re: What SQL client do you recommend for querying mobile geodatabases?

jcarlson — Mon, 05 Dec 2022 15:00:23 GMT

I've heard good things about Beekeeper Studio, though admittedly I've not used it much. I like that it's got a portable option

Personally, I'm already in VSCode so much that I just use the SQLite extension there. Nothing incredible, but it works, and is easy to integrate into whatever project I'm working on.

Re: What SQL client do you recommend for querying mobile geodatabases?

Bud — Mon, 17 Apr 2023 15:34:34 GMT

For anyone who's interested:

I've been using DBeaver (free community edition) instead of Beekeeper Studio (paid) for a couple of reasons:

1. I had trouble installing the ST_GEOMETRY .dll in a mobile geodatabase using Beekeeper Studio:
Load ST_GEOMETRY .dll extension into SQLite/mobile geodatabase — Error: "not authorized". I didn't have that problem in DBeaver.

2. I like the SQL formatting in DBeaver a bit better.

Original/unformatted SQL from ArcGIS Pro Diagnostic Monitor:

SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.ACTIVE_TRANSPORTATION.ATN_ID,main.ACTIVE_TRANSPORTATION.TYPE,main.ACTIVE_TRANSPORTATION.CREATED_USER,main.ACTIVE_TRANSPORTATION.CREATED_DATE,main.ACTIVE_TRANSPORTATION.LAST_EDITED_USER,main.ACTIVE_TRANSPORTATION.LAST_EDITED_DATE,main.active_transportation_nt_flag_vw.OBJECTID,main.active_transportation_nt_flag_vw.flag FROM main.ACTIVE_TRANSPORTATION LEFT OUTER JOIN main.active_transportation_nt_flag_vw ON main.ACTIVE_TRANSPORTATION.OBJECTID = main.active_transportation_nt_flag_vw.OBJECTID WHERE main.ACTIVE_TRANSPORTATION.OBJECTID IN ( 14335, 14748, 14788, 14998, 15280, 15360, 15426, 15515, 15536, 15542, 15607, 15736, 16059, 16113, 16197, 16206, 16302, 16391, 16470, 16471, 16502, 16801, 16810, 16966, 17194, 17292, 17762, 17769, 17901, 17936, 18000, 18033, 18100, 18142, 18170, 18350, 18378, 18414, 18416, 18476, 18482, 18508, 18659, 18730, 18767, 18768, 18772, 18797, 18815, 18878, 18954, 19200, 19221, 19266, 19338, 19354, 19372, 19389, 19408, 19416, 19532, 19533, 19549, 19680, 19696, 19732, 19769, 20002, 20015, 20509, 20574, 20635, 20833, 20844, 20924, 21137, 21201, 21247, 21323, 21338, 21383, 21432, 21538, 21552, 21575, 21646, 21758, 21871, 21910, 21975, 21983, 22184, 22296, 22319, 22325, 22360, 22516, 22620, 22623, 22677, 22678, 22692, 22841, 22893, 22917, 22969, 23030, 23076, 23413, 23496, 23561, 23623, 23661, 23673, 23743, 23816, 23824, 23880, 23943, 24093, 24122, 24172, 24251, 24263, 24383, 24506, 24517, 24519, 24543, 24594, 24633, 24713, 24771, 25043, 25069, 25078, 25112, 25210, 25217, 25395, 25419, 25640, 25681, 25686, 25689, 25826, 25895, 25972, 26085, 26086, 26163, 26328, 26406, 26409, 26416, 26528, 26538, 26595, 26624, 26637, 26748, 26843, 26886, 26926, 26934, 26968, 26996, 27040, 27078, 27097, 27186, 31366, 34593, 38412, 40018, 41607, 41947, 41958, 41964, 41978, 41979, 42261, 42264, 42562, 42563, 42565, 42567, 44827, 44828, 44829, 44830, 44831, 44832, 44833, 44834, 44835, 44836, 44837, 44838, 50561 )

Formatted by Beekeeper Studio:

SELECT
  main.ACTIVE_TRANSPORTATION.OBJECTID,
  main.ACTIVE_TRANSPORTATION.ATN_ID,
  main.ACTIVE_TRANSPORTATION.TYPE,
  main.ACTIVE_TRANSPORTATION.CREATED_USER,
  main.ACTIVE_TRANSPORTATION.CREATED_DATE,
  main.ACTIVE_TRANSPORTATION.LAST_EDITED_USER,
  main.ACTIVE_TRANSPORTATION.LAST_EDITED_DATE,
  main.active_transportation_nt_flag_vw.OBJECTID,
  main.active_transportation_nt_flag_vw.flag
FROM
  main.ACTIVE_TRANSPORTATION
  LEFT OUTER JOIN main.active_transportation_nt_flag_vw ON main.ACTIVE_TRANSPORTATION.OBJECTID = main.active_transportation_nt_flag_vw.OBJECTID
WHERE
  main.ACTIVE_TRANSPORTATION.OBJECTID IN (    14335,
    14748,
    14788,
    14998,
    ... [200 values]

Notice that the values in the IN clause have line breaks between each value.

Whereas DBeaver puts all the IN values in a single line.

SELECT
    main.ACTIVE_TRANSPORTATION.OBJECTID,
    main.ACTIVE_TRANSPORTATION.ATN_ID,
    main.ACTIVE_TRANSPORTATION.TYPE,
    main.ACTIVE_TRANSPORTATION.CREATED_USER,
    main.ACTIVE_TRANSPORTATION.CREATED_DATE,
    main.ACTIVE_TRANSPORTATION.LAST_EDITED_USER,
    main.ACTIVE_TRANSPORTATION.LAST_EDITED_DATE,
    main.active_transportation_nt_flag_vw.OBJECTID,
    main.active_transportation_nt_flag_vw.flag
FROM
    main.ACTIVE_TRANSPORTATION
LEFT OUTER JOIN main.active_transportation_nt_flag_vw ON
    main.ACTIVE_TRANSPORTATION.OBJECTID = main.active_transportation_nt_flag_vw.OBJECTID
WHERE
    main.ACTIVE_TRANSPORTATION.OBJECTID IN ( 14335, 14748, 14788, 14998, ... [200 values]

I find that formatting more useful. It takes up less space.

Beekeeper Studio - SQL formatting enhancement requests

What SQLite tool does @JoshuaBixby use for querying mobile geodatabases?

topic Re: What SQL client do you recommend for querying mobile geodatabases? in Geodatabase Questions

What SQL client do you recommend for querying mobile geodatabases?

Re: What SQL client do you recommend for querying mobile geodatabases?

Re: What SQL client do you recommend for querying mobile geodatabases?