How to execute a query string in MonetDB
Hi, I'm attempting to declare a query string to execute in MonetDB. The documentation is not clear if this is possible, but other engines allow this functionality (such as MySQL and MS SQL Sever). Trying the following execute 'select * from tables'; fails with the message Error: syntax error, unexpected STRING, expecting IDENT or sqlINT The reason behind this is that I need to declare a variable *schema name*, such that I can execute a query in the following way: declare s varchar(32); set s = 'the_schema'; execute 'select * from ' || the_schema || '.the_table'; Please let me know if this is even possible in MonetDB or if you have some tips about that. I tried with a *prepared statement*, as given in MonetDB's documentation -- but the following code cannot execute prepare 'select * from ' || the_schema || '.the_table'; because prepare expects an actual query, not a string. I'd like to achieve this with only SQL stored functions. The client I have has to execute the SQL functions directly, and there's no intermediate Java/PHP/etc scripts to build the SQL on the fly before they're being sent to MonetDB server. Thus, I should create a function like so: create function getData( dataSchema varchar(32) ) returns bigint begin declare query varchar(128); set query = 'select count(*) from ' || dataSchema || '.the_table'; return( execute query ); end; My function is more complicated than this, with other tables and even functions. The issue is that functions belong to one schema, and data tables belong to another schema, which is not known in 'compile' time. Thanks for the help! Luis Ángel The information transmitted, including any attachments, is intended only for the individual or entity to which it is addressed, and may contain confidential and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by individuals or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you have received this communication in error, please delete the information from any computer and notify the sender.
Hi Luis! I'm afraid there is currently no support for executing a string as SQL in MonetDB. Without the specifics of your use case it is hard to come up with a suggestion for an alternative approach. If it involves for example several user specific schemas which all share some data and functionality from one common schema, you might be able to play games with the default schema for a user: refer to the common items with an explicit schema qualifier and to the user specific items without. But it's hard to tell in general. Joeri On Wed, Mar 15, 2017 at 11:22:42PM +0000, Luis Larios wrote:
Hi,
I'm attempting to declare a query string to execute in MonetDB. The documentation is not clear if this is possible, but other engines allow this functionality (such as MySQL and MS SQL Sever). Trying the following
execute 'select * from tables';
fails with the message
Error: syntax error, unexpected STRING, expecting IDENT or sqlINT
The reason behind this is that I need to declare a variable *schema name*, such that I can execute a query in the following way:
declare s varchar(32); set s = 'the_schema'; execute 'select * from ' || the_schema || '.the_table';
Please let me know if this is even possible in MonetDB or if you have some tips about that. I tried with a *prepared statement*, as given in MonetDB's documentation -- but the following code cannot execute
prepare 'select * from ' || the_schema || '.the_table'; because prepare expects an actual query, not a string.
I'd like to achieve this with only SQL stored functions. The client I have has to execute the SQL functions directly, and there's no intermediate Java/PHP/etc scripts to build the SQL on the fly before they're being sent to MonetDB server. Thus, I should create a function like so:
create function getData( dataSchema varchar(32) ) returns bigint begin declare query varchar(128); set query = 'select count(*) from ' || dataSchema || '.the_table'; return( execute query ); end;
My function is more complicated than this, with other tables and even functions. The issue is that functions belong to one schema, and data tables belong to another schema, which is not known in 'compile' time.
Thanks for the help!
Luis Ángel
The information transmitted, including any attachments, is intended only for the individual or entity to which it is addressed, and may contain confidential and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by individuals or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you have received this communication in error, please delete the information from any computer and notify the sender.
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
hi luis,
maybe a workaround would be to use a scripting language, such as bash,
to do all the string manipulation and pass the string query to monetdb
through the mclient -e option.
#> mclient -e 'select * from ....'
On Thu, Mar 16, 2017 at 3:12 PM, Joeri van Ruth
Hi Luis!
I'm afraid there is currently no support for executing a string as SQL in MonetDB.
Without the specifics of your use case it is hard to come up with a suggestion for an alternative approach. If it involves for example several user specific schemas which all share some data and functionality from one common schema, you might be able to play games with the default schema for a user: refer to the common items with an explicit schema qualifier and to the user specific items without. But it's hard to tell in general.
Joeri
On Wed, Mar 15, 2017 at 11:22:42PM +0000, Luis Larios wrote:
Hi,
I'm attempting to declare a query string to execute in MonetDB. The documentation is not clear if this is possible, but other engines allow this functionality (such as MySQL and MS SQL Sever). Trying the following
execute 'select * from tables';
fails with the message
Error: syntax error, unexpected STRING, expecting IDENT or sqlINT
The reason behind this is that I need to declare a variable *schema name*, such that I can execute a query in the following way:
declare s varchar(32); set s = 'the_schema'; execute 'select * from ' || the_schema || '.the_table';
Please let me know if this is even possible in MonetDB or if you have some tips about that. I tried with a *prepared statement*, as given in MonetDB's documentation -- but the following code cannot execute
prepare 'select * from ' || the_schema || '.the_table'; because prepare expects an actual query, not a string.
I'd like to achieve this with only SQL stored functions. The client I have has to execute the SQL functions directly, and there's no intermediate Java/PHP/etc scripts to build the SQL on the fly before they're being sent to MonetDB server. Thus, I should create a function like so:
create function getData( dataSchema varchar(32) ) returns bigint begin declare query varchar(128); set query = 'select count(*) from ' || dataSchema || '.the_table'; return( execute query ); end;
My function is more complicated than this, with other tables and even functions. The issue is that functions belong to one schema, and data tables belong to another schema, which is not known in 'compile' time.
Thanks for the help!
Luis Ángel
The information transmitted, including any attachments, is intended only for the individual or entity to which it is addressed, and may contain confidential and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by individuals or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you have received this communication in error, please delete the information from any computer and notify the sender.
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hi, Joeri and Lefteris,
Thanks for the help. Here's a more specific scenario of what I want to achieve. Consider the following function definition statement:
set schema user_data_schema;
create function functions_schema.csTest()
returns table( yearAndWeek bigint )
begin
return table(
select functions_schema.getYearAndWeek( et )
from stats_table
);
end;
Notice that I have explicitly indicated the schema for the functions that are shared among all users: *functions_schema*. The data tables, however, vary per user, and *stats_table* belongs to each of these different schemas.
I was expecting *stats_table* to be pulled, automatically, from the default schema that I'm using in the current session where I execute the function definition statement (i.e. *user_data_schema*). However, I get the following error:
[Code: 0, SQL State: 42S02] SELECT: no such table 'stats_table'
It seems that any non-explicit-schema tables that appear in the body of a function are understood, by default, as belonging to the same function schema -- and therefore *stats_table* is not found in the *functions-schema*. And that's true, it's not there, and I'd like it to not be there.
It appears the limitation here is that if I have a function that uses data tables, both the function and the data tables must belong to the same schema. Please confirm if this is true.
If you know of another work around this issue, please let me know.
Luis Ángel
-----Original Message-----
From: users-list [mailto:users-list-bounces+luis.larios=sysorex.com@monetdb.org] On Behalf Of Joeri van Ruth
Sent: Thursday, March 16, 2017 7:12 AM
To: Communication channel for MonetDB users
Hi,
I'm attempting to declare a query string to execute in MonetDB. The documentation is not clear if this is possible, but other engines allow this functionality (such as MySQL and MS SQL Sever). Trying the following
execute 'select * from tables';
fails with the message
Error: syntax error, unexpected STRING, expecting IDENT or sqlINT
The reason behind this is that I need to declare a variable *schema name*, such that I can execute a query in the following way:
declare s varchar(32); set s = 'the_schema'; execute 'select * from ' || the_schema || '.the_table';
Please let me know if this is even possible in MonetDB or if you have some tips about that. I tried with a *prepared statement*, as given in MonetDB's documentation -- but the following code cannot execute
prepare 'select * from ' || the_schema || '.the_table'; because prepare expects an actual query, not a string.
I'd like to achieve this with only SQL stored functions. The client I have has to execute the SQL functions directly, and there's no intermediate Java/PHP/etc scripts to build the SQL on the fly before they're being sent to MonetDB server. Thus, I should create a function like so:
create function getData( dataSchema varchar(32) ) returns bigint begin declare query varchar(128); set query = 'select count(*) from ' || dataSchema || '.the_table'; return( execute query ); end;
My function is more complicated than this, with other tables and even functions. The issue is that functions belong to one schema, and data tables belong to another schema, which is not known in 'compile' time.
Thanks for the help!
Luis Ángel
The information transmitted, including any attachments, is intended only for the individual or entity to which it is addressed, and may contain confidential and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by individuals or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you have received this communication in error, please delete the information from any computer and notify the sender.
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
On Thu, Mar 16, 2017 at 04:38:22PM +0000, Luis Larios wrote:
It seems that any non-explicit-schema tables that appear in the body of a function are understood, by default, as belonging to the same function schema -- and therefore *stats_table* is not found in the *functions-schema*. And that's true, it's not there, and I'd like it to not be there.
It appears the limitation here is that if I have a function that uses data tables, both the function and the data tables must belong to the same schema. Please confirm if this is true.
If you know of another work around this issue, please let me know.
Hi Luis! I did a quick experiment which confirms your observation. The following MonetDB script returns 'schema b', indicating that the schema is taken to be the function's schema. -- preparation DROP SCHEMA IF EXISTS a CASCADE; DROP SCHEMA IF EXISTS b CASCADE; DROP SCHEMA IF EXISTS c CASCADE; CREATE SCHEMA a; CREATE SCHEMA b; CREATE SCHEMA c; CREATE TABLE a.t (schema_name VARCHAR(10)); INSERT INTO a.t VALUES ('schema a'); CREATE TABLE b.t (schema_name VARCHAR(10)); INSERT INTO b.t VALUES ('schema b'); CREATE TABLE c.t (schema_name VARCHAR(10)); INSERT INTO c.t VALUES ('schema c'); -- create the function in b while default schema is a SET SCHEMA a; CREATE FUNCTION b.foo() RETURNS VARCHAR(10) RETURN (SELECT * FROM t); -- execute it while default schema is c SET SCHEMA c; SELECT b.foo(); The following similar Postgres script returns 'schema c': -- preparation DROP SCHEMA IF EXISTS a CASCADE; DROP SCHEMA IF EXISTS b CASCADE; DROP SCHEMA IF EXISTS c CASCADE; CREATE SCHEMA a; CREATE SCHEMA b; CREATE SCHEMA c; CREATE TABLE a.t (schema_name VARCHAR(10)); INSERT INTO a.t VALUES ('schema a'); CREATE TABLE b.t (schema_name VARCHAR(10)); INSERT INTO b.t VALUES ('schema b'); CREATE TABLE c.t (schema_name VARCHAR(10)); INSERT INTO c.t VALUES ('schema c'); -- create the function in b while default schema is a SET SCHEMA 'a'; CREATE FUNCTION b.foo() RETURNS VARCHAR(10) AS 'SELECT * FROM t' LANGUAGE SQL; -- execute it while default schema is c SET SCHEMA 'c'; SELECT b.foo(); I think in general MonetDB's behaviour is preferrable, especially from a security perspective because it avoids any number unpredictable behaviours related to injecting unexpected objects into functions defined by some other role in another schema. It does however unfortunately block your clever approach to function sharing. I'm not aware of an alternative approach, maybe someone else can chime in. A very crude solution would be to forego the shared schema altogether and simply define all functions over and over in every 'data schema'. This might be a little painful but workable if the shared functions do not change very frequently. Joeri
Thanks for all the help, Joeri,
We might end up using your suggestion (y)
Luis Ángel Larios Cárdenas | Web Developer
luis.larios@inpixon.com
o: +1 (818) 654.6787 | m: +1 (323) 702.2746
Encino Office: 6345 Balboa Blvd. | Suite # 247 | Encino | CA 91316 US
HQ: 2479 E. Bayshore Rd | Suite 195 | Palo Alto | CA 94303 US
-----Original Message-----
From: users-list [mailto:users-list-bounces+luis.larios=sysorex.com@monetdb.org] On Behalf Of Joeri van Ruth
Sent: Friday, March 17, 2017 9:07 AM
To: Communication channel for MonetDB users
It seems that any non-explicit-schema tables that appear in the body of a function are understood, by default, as belonging to the same function schema -- and therefore *stats_table* is not found in the *functions-schema*. And that's true, it's not there, and I'd like it to not be there.
It appears the limitation here is that if I have a function that uses data tables, both the function and the data tables must belong to the same schema. Please confirm if this is true.
If you know of another work around this issue, please let me know.
Hi Luis! I did a quick experiment which confirms your observation. The following MonetDB script returns 'schema b', indicating that the schema is taken to be the function's schema. -- preparation DROP SCHEMA IF EXISTS a CASCADE; DROP SCHEMA IF EXISTS b CASCADE; DROP SCHEMA IF EXISTS c CASCADE; CREATE SCHEMA a; CREATE SCHEMA b; CREATE SCHEMA c; CREATE TABLE a.t (schema_name VARCHAR(10)); INSERT INTO a.t VALUES ('schema a'); CREATE TABLE b.t (schema_name VARCHAR(10)); INSERT INTO b.t VALUES ('schema b'); CREATE TABLE c.t (schema_name VARCHAR(10)); INSERT INTO c.t VALUES ('schema c'); -- create the function in b while default schema is a SET SCHEMA a; CREATE FUNCTION b.foo() RETURNS VARCHAR(10) RETURN (SELECT * FROM t); -- execute it while default schema is c SET SCHEMA c; SELECT b.foo(); The following similar Postgres script returns 'schema c': -- preparation DROP SCHEMA IF EXISTS a CASCADE; DROP SCHEMA IF EXISTS b CASCADE; DROP SCHEMA IF EXISTS c CASCADE; CREATE SCHEMA a; CREATE SCHEMA b; CREATE SCHEMA c; CREATE TABLE a.t (schema_name VARCHAR(10)); INSERT INTO a.t VALUES ('schema a'); CREATE TABLE b.t (schema_name VARCHAR(10)); INSERT INTO b.t VALUES ('schema b'); CREATE TABLE c.t (schema_name VARCHAR(10)); INSERT INTO c.t VALUES ('schema c'); -- create the function in b while default schema is a SET SCHEMA 'a'; CREATE FUNCTION b.foo() RETURNS VARCHAR(10) AS 'SELECT * FROM t' LANGUAGE SQL; -- execute it while default schema is c SET SCHEMA 'c'; SELECT b.foo(); I think in general MonetDB's behaviour is preferrable, especially from a security perspective because it avoids any number unpredictable behaviours related to injecting unexpected objects into functions defined by some other role in another schema. It does however unfortunately block your clever approach to function sharing. I'm not aware of an alternative approach, maybe someone else can chime in. A very crude solution would be to forego the shared schema altogether and simply define all functions over and over in every 'data schema'. This might be a little painful but workable if the shared functions do not change very frequently. Joeri _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list The information transmitted, including any attachments, is intended only for the individual or entity to which it is addressed, and may contain confidential and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by individuals or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you have received this communication in error, please delete the information from any computer and notify the sender.
participants (3)
-
Joeri van Ruth -
Lefteris -
Luis Larios