Question
Evgeny Shvarov · May 19, 2020

Can I substitute table in FROM with a hosted variable?

Haven't found this in the documentation. I want:

&sql(SELECT name INTO :var FROM :table)

Is it possible?

#ObjectScript #SQL #InterSystems IRIS
00
2 0 7 172

Replies

Robert Cemper · May 20, 2020

NO it is not possible:
In IRIS for Windows (x86-64) 2020.1 (Build 215U) it is compiled.
But at run time you get an SQLCODE=1 and an error message for a bad SQL statement. 
Better compose an SQL string  "SELECT name FROM "_table and process it with some ResultSet class, 

The embedded code in the class we know from Cache is gone.
It all generates into  a %scqlc.* class
 

10
Accepted answer
John Murray · May 20, 2020

Better compose an SQL string  "SELECT name FROM "_table and process it with some ResultSet class

But take care you don't open your system up to an SQL Injection attack.

20
Vitaliy Serdtsev · May 20, 2020

Extending the reply of @Robert Cemper

  • The following query compiles without errors, even though Studio highlights the error 
    &sql(select :fieldname into :fieldvar from :tablename)

    Everything is fine in Caché: the error occurs at the compilation-time.

    In order for an error to occur in IRIS Studio too you need to use the flag /compileembedded=1

  • It is strange that the documentation mentions field name, but there is no mention of table name:
    A host variable cannot be used to pass a field name or keyword into an SQL statement. proof
10
Accepted answer
Benjamin De Boe · May 20, 2020

For more about where the source code for embedded SQL has gone in 2020.1, please see this article on the Universal Query Cache

Indeed funny the doc doesn't call out table names while it does talk about field names, probably because it silently assumes the one implies the other. I'll ask our doc writer to fix that. Thanks for the notification.

00
Evgeny Shvarov · May 24, 2020

Thanks, @Benjamin De Boe ! It doesn't explain though (at least for me) why hosted variables can't be used for table and column names.

00
Danny Wijnschenk · May 20, 2020

Use %SQL.Statement for dynamic sql, do not use %ResultSet anymore (it is slower).

10