Subscribe to this thread
Home - General / All posts - table name as variable in a query component using multiple SQL queries
vincent

1,931 post(s)
#29-Jul-20 15:08

Hi,

I have around 15 queries to run on a single table. I know that I can pack all queries in one query component and trigger them all at the same time. But I don't know how to declare the name of the table as a query parameter. That was not possible in M8 if I remember well.

Is it possible in 9 ? How ? Thank you !

Example query :

--SQL 

Update [20060300] SET [date_time]

CAST ( [date_concat] as datetime)

vincent

1,931 post(s)
#29-Jul-20 19:15

I tried this, but it does not work :

--SQL 9

VALUE @theTable2Process nvarchar = '[20060801]';

--#1

ALTER TABLE @theTable2Process 

(

Rename [LATITUDE N/S] [LATITUDE] ,

Rename [LONGITUDE E/W] [LONGITUDE]

);

--#2

ALTER TABLE @theTable2Process (

ADD [mfd_id] INT64,

ADD INDEX [mfd_id_x] BTREE ([mfd_id])

);

Any way of doing this ?

Should I script the query text like in M8 ?

LeRepère
139 post(s)
#29-Jul-20 19:29

Vincent

Try Value @t TABLE = [Table]

vincent

1,931 post(s)
#29-Jul-20 19:33

Already tested I think. I'll test again.

vincent

1,931 post(s)
#29-Jul-20 19:36

No luck with these :

--Sql 9

> VALUE @t TABLE = [20060801];

ALTER TABLE @t 

(

Rename [LATITUDE N/S] [LATITUDE] ,

Rename [LONGITUDE E/W] [LONGITUDE]

);

Cannot parse query.

--

--

> VALUE @t TABLE = '[20060801]';

ALTER TABLE @t 

(

Rename [LATITUDE N/S] [LATITUDE] ,

Rename [LONGITUDE E/W] [LONGITUDE]

);

Cannot parse query.

tjhb

9,476 post(s)
#29-Jul-20 20:39

AFAIK, stored values or parameters can't be used to stand for tables in queries that create tables or change table schemas--that is, CREATE, ALTER, DROP--but only in SELECT, INSERT, or UPDATE.

The principle is that the query engine must be able to compile the whole query (or command sequence) before it begins execution.

It can't do that in cases where tables either do not exist at the start of the code, or will change within it.

Actually from memory, it is more subtle. A CREATE statement must fail, for the reason just given. In practice, DROP <component> also answers itself.

The subtlety is that each time a table is ALTERed (explicitly, without using a stored value or parameter), any previous references persist but continue to use the previous schema. This affects SELECT, INSERT and UPDATE queries as well--so care should be taken to recreate references to tables after they are changed.

I need to check these comments, please bear that in mind.

adamw


9,447 post(s)
#02-Sep-20 16:33

This is correct.

The @... values for tables can be used in statements like SELECT / INSERT / DELETE (DML), but cannot be used in statements like CREATE / ALTER / DROP (DDL). There are several reasons, but the most important one is that we are plain not sure writing VALUE @t TABLE = [real_table]; and then DROP TABLE @t; should actually drop [real_table], that seems dangerous. Similarly, we are not sure ALTER TABLE @t ... should alter [real_table]. We agree being able to ALTER TABLE @t could be useful, but mostly for temporary tables composed by other statements. Letting ALTER TABLE @t reach into a physical table, if @t is set to one, seems as dangerous as with DROP. Finally, CREATE TABLE @t cannot even begin to work, because if a table does not yet exist, @t cannot be set to it.

One of the options here is to let @t in CREATE / ALTER / DROP be a string instead of a table. This is actually doable now after some of the changes we did in the query engine. We might do it. If we don't do it, we'll probably allow doing just ALTER TABLE @t on a table and will either fail if @t is a real table, or cache it as part of ALTER.

In the meantime, the best way to run a big query with multiple statements (that include a lot of CREATE / ALTER / DROP) on multiple tables without copy and paste is to generate the query using a script. If there is only one table to run the queries on, the best way is to use the name of the table verbatim.

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