Subscribe to this thread
Home - General / All posts - running a query from another query
artlembo

3,043 post(s)
#11-May-18 02:56

I'm trying to run a query from another query using the EXECUTE statement. I keep getting the non-descript error message:Invalid object reference.

The query that I am trying to run creates contours from a surface. The contouring query runs fine all on its own, but when I attempt to EXECUTE it, I get the error.

Same for a query that uses GeomOverlayTopologyIntersectPar. The query runs fine on its own, but when I attempt to EXECUTE it, I get the invalid object error.

Are there some things that EXECUTE will not allow?

tjhb
8,005 post(s)
online
#11-May-18 03:56

(Exact syntax is needed though Art. The actual text.)

tjhb
8,005 post(s)
online
#11-May-18 04:13

Just to state the obvious, EXECUTE will parse everything within [[ ]]. And only that.

Like

EXECUTE

[[

SELECT * FROM [mfd_meta]

]]

;

tjhb
8,005 post(s)
online
#11-May-18 06:11

Such a dumb post, I apologise.

Art did not say that the subject query was literal (text).

It might well be a static query component.

artlembo

3,043 post(s)
#11-May-18 13:37

no, you were correct - I should include a small project. Unfortunately, I'm not allowed to share the data I am working with. So, let me go back and try to mock up some other data.

adamw


7,903 post(s)
#11-May-18 17:23

EXECUTE may fail on queries that create components and use them.

For example, if the query is: CREATE TABLE t (...); INSERT INTO t ...;, then EXECUTE will try to compile that and compiling INSERT INTO t ... will fail, because t does not yet exist, it gets created when the query is run.

This is a bit of a problem, yes, in that we need to have a way for a query to run any other query, and currently we don't have such a way. We are considering extending the syntax for EXECUTE to ignore the resulting table, that way it will be able to run an arbitrary query.

In the meantime, rewrite the query that you are EXECUTing to use existing components.

artlembo

3,043 post(s)
#11-May-18 19:30

Adam,

That is the issue I believe. I am running the basic contour query, so the query has a:

CREATE TABLE contour

CREATE DRAWING,

and then

INSERT INTO (...CALL TileContourAreasPar)

It's not too terrible. I can always but everything in a query, and maybe set all my parts up as FUNCTIONS or something.

tjhb
8,005 post(s)
online
#11-May-18 19:43

How about SELECT INTO followed by CREATE DRAWING?

artlembo

3,043 post(s)
#11-May-18 19:57

I think the issue is the creation of the drawing. I created the query as a SELECT INTO, but the problem is, we use that new table later on:

-- $manifold$

 SELECT

  [Geom][ValueMin][ValueMax]

INTO contours

FROM CALL TileContourAreasPar([Pgasurf], CALL ValueSequence(0, 100, 0.2), true, ThreadConfig(SystemCpuCount()));

ALTER TABLE [contours] (

 ADD [mfd_id] INT64,

 ADD INDEX [mfd_id_x] BTREE ([mfd_id]),

 ADD INDEX [Geom_x] RTREE ([Geom]),

  ADD PROPERTY 'FieldCoordSystem.Geom' '<?xml version="1.0" encoding="UTF-8"?>

<data>

 <coordinateSystem>

 <name>State Plane - California 5*(NAD 83, feet)</name>

 <datum>North American 1983 (mean for CONUS)</datum>

 <system>Lambert Conformal Conic</system>

 <unit>US Survey Foot</unit>

 <majorAxis>6.378137000000000000000000e+006</majorAxis>

 <eccentricity>8.181919104281579200000000e-002</eccentricity>

 <centerX>0.000000000000000000000000e+000</centerX>

 <centerY>0.000000000000000000000000e+000</centerY>

 <centerZ>0.000000000000000000000000e+000</centerZ>

 <rotationX>0.000000000000000000000000e+000</rotationX>

 <rotationY>0.000000000000000000000000e+000</rotationY>

 <rotationZ>0.000000000000000000000000e+000</rotationZ>

 <scaleAdjustment>0.000000000000000000000000e+000</scaleAdjustment>

 <scaleX>1.000000000000000000000000e+000</scaleX>

 <localScaleX>3.048006095999999700000000e+002</localScaleX>

 <scaleY>1.000000000000000000000000e+000</scaleY>

 <localScaleY>3.048006095999999700000000e+002</localScaleY>

 <falseEasting>1.999999999991999700000000e+006</falseEasting>

 <localOffsetX>1.934132633145604500000000e+006</localOffsetX>

 <falseNorthing>4.999999999980001000000000e+005</falseNorthing>

 <localOffsetY>5.166423451821215600000000e+005</localOffsetY>

 <centerLat>3.350000000000000000000000e+001</centerLat>

 <centerLon>-1.180000000000000000000000e+002</centerLon>

 <firstStdLat>3.403333333333333100000000e+001</firstStdLat>

 <secondStdLat>3.546666666666666900000000e+001</secondStdLat>

 </coordinateSystem>

 <surface>

 <channels>1</channels>

 <cx>192</cx>

 <cy>274</cy>

 <type>Float32</type>

 <void>1.17549e-038</void>

 </surface>

</data>'

);

CREATE DRAWING [contours drawing] (

  PROPERTY 'Table' '[contours]',

  PROPERTY 'FieldGeom' 'Geom'

);

tjhb
8,005 post(s)
online
#11-May-18 20:21

Yes, but I think that can compile as one query-expression, within [[ ]]?

Because the creation of the drawing only refers to the table in a property, as just text. It is not part of SQL syntax.

In the same way, misspelling the table name in the property is not an SQL error. It will happily compile (and proceed), despite the spelling error.

It is different for INSERT INTO t because it refers to the (yet-to-be-created) table directly as SQL syntax.

tjhb
8,005 post(s)
online
#12-May-18 00:05

I am wrong. Yes CREATE DRAWING is fine, but ALTER TABLE can’t be compiled before its table exists.

On future developments (responding to Adam), how about a pragma or command to force query statements to be compiled and run separately and sequentially? Divided at each ‘;’—almost as if they were contained in separate components, except that state would be retained and inherited (functions, other pragma values, fullfetch state). So: exactly as if the user selected each block in order, pressing Alt-Enter each time.

This could be useful more broadly, e.g. for diagnosis, maybe other things later.

I’m imagining it working as a stateful toggle. If not a pragma setting then e.g.

--!lazy

Lazy compilation could be turned off for one series of statements, on for a second series, then off for another.

My feeling is that allowing delayed or staged compilation would be better than allowing unresolved references just for EXECUTE. Not a syntax exception, easier to debug, easier to reuse code.

artlembo

3,043 post(s)
#12-May-18 01:06

Of course the immediate solution for this is to throw the query into the other query as a single component. Or, string them together as a bunch of functions.

tjhb
8,005 post(s)
online
#12-May-18 01:33

Quite right. It’s hardly a top priority, is it. Undue focus on my part, typical.

adamw


7,903 post(s)
#14-May-18 13:43

Running a multi-statement query already runs statements one by one, they don't have to first compile as a whole. It's when statements do have to first compile as a whole that there is a problem. This is more than just EXECUTE. For example, SELECT * FROM [q] also has to compile [q] as a whole, if it is a query. All this happens because we allow mixing DDL statements with DML ones, other databases do not allow this (and if queries that you SELECT from only run DDL, you don't have any issues in 9 either).

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.