2

I found OpenJump a real nice tool with its ability to render results of ad hoc SQL queries like PostGIS ones (among other things).

However, I also found that not everything that runs in (say) pgAdmin can be executed in OpenJump (File > Run Datastore Query). For example:

If I run a query that does not return result,

CREATE TABLE t AS
SELECT * FROM tbl;

I get an error (as of Version 1.10 release rev.5336) about No results were returned by the query. Invalid query .... (Yet if I run the above again, it says Error: relation t already exists. Seems that the query is executed anyways.) Similarly problem happens with CREATE FUNCTION etc. The error happens even if there is a second query that does return a relation.

If I run the same query twice,

SELECT * FROM tbl;
SELECT * FROM tbl;

I get an Error Multiple ResultSets were returned by the query. Invalid query....

From trial and error, it seems that the only things found to work so far is a single SELECT statement or equivalent (CTE etc.).

I am wondering if there is any documentation on this. In particular,

What kind of queries are valid?

How many queries can be performed in one run of data store query?

(Wouldn't it be nice if OpenJump just returns the result of the last query?)

3

Ad-hoc query tool in OpenJump is for viewing spatial queries and each query is assumed to be a dataset.

That said, I wouldn't expect it to work on any query that returns more than one resultset or doesn't return a resultset at all. Note also as a spatial query, I don't think it works unless your query has at least one spatial column.

What you discovered is just a side-effect of how it executes. Aside from being able to return multiple resultsets, I feel action queries are out of the scope of it's purpose.

That is not to say you can't do it like using a writeable CTE that returns a final result. Then again that would be dangerous since then the refresh query option would repeat the query.

1
  • I think that you are perfectly right but I added some more details as another answer.
    – user30184
    Apr 20 '17 at 8:14
2

There are two ways to run ad-hoc queries with OpenJUMP. You have already used the standard "Run datastore query tool" but OpenJUMP Plus comes with DB Query plugin that also works with PostGIS out-of-the-box. There are some differences in these tools. What is common is that they are very simple, stupid, and therefore extremely useful.

Both ad-hoc query tools run any SQL statement. That reminds using PSQL terminal from the command line. So all these do work:

1. CREATE table my_table (fid integer);
2. DROP TABLE my_table;
3. SELECT fid from my_table;

However, those tools may not show any message for you because despite they are simple and stupid they are still somehow planned to be used with spatial data.

If query does not return any result set like with cases 1. and 2. above the Run Datastore Query returns an error like:

java.lang.Error: No results were returned by the query.
Invalid query: CREATE table my_table (fid integer)

DB Query tool just shows a message:

No results were returned by the query.

If the query does return data but result set does not contain geometry (example 3. above) then Run Datastore Query does not create a layer. The error message is:

java.lang.Exception: Result Set Must Have a Geometry Column

The DB Query tool does create a layer and shows a message:

Some query features have null geometries

Because all the layers in OpenJUMP must contain geometry this is handled by a workaround that generates on empty geometry for each missing geometry as WKT GEOMETRYCOLLECTION EMPTY.

Q: What kind of queries are valid?

A: All queries are valid in that sense that they will be executed by the database. Some queries may not show you any reasonable feedback.

Q: How many queries can be performed in one run of data store query?

A: With DB Query plugin only the first one will be ever executed so the answer is "One". With Run Datastore Query I am not sure. You can create two tables with one Run Datastore Query execution

create table foo (fid integer);
create table foo2 (fid integer);

However, it is possible that some other SQL statements will fail before they have been executed. That works only if at maximum one query returns a result set because OpenJUMP is initializing only one layer to receive data from the query. In case of many result sets you will see

java.lang.Error: Multiple ResultSets were returned by the query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.