March 2015 - users-list

SQL string from PreparedStatement ??
by Vijay Krishna 30 Mar '15

30 Mar '15

Hi, Is there any way to get the SQL query as a string from the PreparedStatement of MonetDB?? (Like in some DBs, toString will give the SQL string) Any help much appreciated. Thanks in advance. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

2 1

Count function with Over clause()
by Amey M 30 Mar '15

30 Mar '15

The Aggregate count function doesn't work with the over clause in MonetDB. I am a new user on MonetDB, and have trouble using queries running on other databases like Postgres on MonetDB. When using the Count function with Over clause I get the following error: sql>select count(id) over() from sample_table ;SELECT: function 'count' not found Regards, Ameya

1 0

MonetDB C functions
by Jiří Nádvorník 29 Mar '15

29 Mar '15

Hi all, I'm trying to understand the example of user defined functions in MonetDB (https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFunct ion). I understand the concept for just reversing a string. But what about working with tabular data? I'd like to pass to this C function a whole table (list of rows) and then return another table(s)/write result data to other tables. What is the correct way of doing this? Can I pass some kind of arrays to the C function? Can I return these arrays from a function? Or is it possible to query the DB directly from the C script? Thank you very much for some answers. Best regards, Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic <mailto:nadvornik.ji@gmail.com> nadvornik.ji(a)gmail.com

2 2

Better understand those MAL functions
by Leon-Constantin Fopa 29 Mar '15

29 Mar '15

hello dear all, I'm trying to understand an execution plan generated in MAL by monetDB. I would like to how exactlly the following functions works : - sql.mvc - algebra.subselect - sql.bind - sql.subdelta - algebra.leftfetchjoin Best regards,

1 0

Arrays in MonetDB
by Jiří Nádvorník 27 Mar '15

27 Mar '15

Hi, Do I understand correctly that MonetDB does not support array types? What's the best possibility to use instead of them? I have a table of pixels and table of points. For each pixel.ID I have two different arrays of points to store in the DB. Thank you for clarification. Best regards, Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic <mailto:nadvornik.ji@gmail.com> nadvornik.ji(a)gmail.com

1 0

MonetDB user defined function grouping
by Jiří Nádvorník 26 Mar '15

26 Mar '15

Hi all, I am trying to do a user defined function written in C++ which takes the whole table as a parameter, loads it into a vector structure, and then divides the data in some groups (duplicities will occur). How can I return the results of this grouping from the C function? Probably I'd like to store the group IDs in a separate table and create and then create an M:N table between the group IDs and the original records. Any help appreciated. Best regards, Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic <mailto:nadvornik.ji@gmail.com> nadvornik.ji(a)gmail.com

1 0

HEALPix with monetDB
by Jiří Nádvorník 25 Mar '15

25 Mar '15

Hi all, I'm planning to index my spherical data in MonetDB byHEALPix. I will need it for: 1. Data partitioning - distribute the data uniformly on the DB nodes, but keep them sorted (keep points close on the sphere on the same node). 2. Neighbor lookup - can I actually index my spherical coordinates stored in MonetDB by some kind of a BTree of HEALPix pixels? I understand how computing a hash for the coordinates and indexing on it helps in relational DB, but how do the indices function in an array DB like MonetDB? I'd like to benefit from the array storage of the data, because I will often look for nearest neighbors and compute distances between these neighbors. I will also divide the data in the beginning for parallel tasks by some calculation of their pixels, but that will be done only once in the beginning, the main computation will be around clustering these objects, which means lots and lots of neighbor searches and distance computation. Has somebody already tried to use HEALPix, or some other spherical index in MonetDB? How can I control the distribution of data across the nodes (e.g. sort them by this index)? How can I use and benefit from this kind of indexing in MonetDB? Thank you very much for even some questions answered. Best regards, Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic nadvornik.ji(a)gmail.com

1 0

Case insensitive LIKE ??
by Vijay Krishna 23 Mar '15

23 Mar '15

Hi, By default the LIKE criteria of MonetDB returns results by matching the exact case of the search word (MonetDB Jan 2014 SP1 Mac). How to make the LIKE criteria to do a case insensitive search by default?? (Something like the BINARY keyword that does case sensitive search in MySQL??) I don't wish to add a workaround like "LOWER(column) like LOWER(searchword)" here.. Any help much appreciated. Thanks in advance. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

2 2

How to tell MonetDB that data are sorted in the table?
by Leon-Constantin Fopa 19 Mar '15

19 Mar '15

Hi Given a table `data` (as described below) and given the insurance that that values imported into this table are sorted. CREATE TABLE data (INT value) CREATE INDEX index_value ON data (value) Since MonetDB can neglect the `CREATE INDEX` statement (see details here : https://www.monetdb.org/Documentation/Manuals/SQLreference/Indices) How do I tell MonetDB that data are sorted in order to let him improves lookup operations ? Best regards, Fopa Léon Constantin

2 3

Datacell issue on Jan2014-SP3 build
by Tsai Li Ming 18 Mar '15

18 Mar '15

Hi, We are trying data cell on the Jan2014-SP3 build. There is a “Query datacell.pass failed:SQLException:mvc:SQL module not initialised” error. Please see below: Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014-SP3) Database: MonetDB v11.17.21 (Jan2014-SP3), 'mapi:monetdb://localhost.localdomain:50000/foo' Type \q to quit, \? for a list of available commands auto commit mode: on sql>set optimizer='datacell_pipe'; operation successful (1.465ms) sql> sql>create table datacell.bsktin( more> id integer, more> tag integer, more> payload integer more>); operation successful (325.852ms) sql>create table datacell.bsktout (like datacell.bsktin); operation successful (106.356ms) sql> sql>call datacell.receptor('datacell.bsktin','localhost',50500); sql> sql>call datacell.emitter('datacell.bsktout','localhost',50600); sql> sql>call datacell.query('datacell.pass', 'insert into datacell.bsktout select * from datacell.bsktin;'); sql> sql>select * from datacell.receptors(); select * from datacell.emitters(); select * from datacell.queries(); select * from datacell.baskets(); +-----------------+-----------+-------+----------+---------+--------+----------------------------+--------+----------+---------+ | nme | host | port | protocol | mode | status | lastseen | cycles | received | pending | +=================+===========+=======+==========+=========+========+============================+========+==========+=========+ | datacell.bsktin | localhost | 50500 | TCP | passive | paused | null | 0 | 0 | 0 | +-----------------+-----------+-------+----------+---------+--------+----------------------------+--------+----------+---------+ 1 tuple (10.546ms) +------------------+-----------+-------+----------+--------+--------+----------------------------+--------+------+---------+ | nme | host | port | protocol | mode | status | lastsent | cycles | sent | pending | +==================+===========+=======+==========+========+========+============================+========+======+=========+ | datacell.bsktout | localhost | 50600 | UDP | active | paused | null | 0 | 0 | 0 | +------------------+-----------+-------+----------+--------+--------+----------------------------+--------+------+---------+ 1 tuple (10.780ms) +---------------+--------+----------------------------+--------+--------+------+-------+-------------------------------------------------------------+ | nme | status | lastrun | cycles | events | time | error | def | +===============+========+============================+========+========+======+=======+=============================================================+ | datacell.pass | paused | null | 0 | 0 | 0 | | insert into datacell.bsktout select * from datacell.bsktin; | +---------------+--------+----------------------------+--------+--------+------+-------+-------------------------------------------------------------+ 1 tuple (15.348ms) +------------------+-----------+---------+-----------+-----------+------------+------+----------------------------+--------+ | nme | threshold | winsize | winstride | timeslice | timestride | beat | seen | events | +==================+===========+=========+===========+===========+============+======+============================+========+ | datacell.bsktin | 0 | 0 | 0 | 0 | 0 | 0 | null | 0 | | datacell.bsktout | 0 | 0 | 0 | 0 | 0 | 0 | null | 0 | +------------------+-----------+---------+-----------+-----------+------------+------+----------------------------+--------+ 2 tuples (15.704ms) sql> sql>call datacell.resume(); sql>call datacell.dump(); invalid/unknown response from server, ignoring output ? #baskets[ 1] datacell.bsktin columns 3 threshold 0 window=[0,0] time window=[0,0] beat 0 milliseconds events 0 ? #baskets[ 2] datacell.bsktout columns 3 threshold 0 window=[0,0] time window=[0,0] beat 0 milliseconds events 0 ? #receptor datacell.bsktin at localhost:50500 protocol=TCP mode=passive status=running delay=1000 ? #emitter datacell.bsktout at localhost:50600 protocol=UDP mode=active status=running delay=1000 ? #scheduler status running (remaining output omitted, use \fraw to examine in detail) We then ran nc and sensor program but get nothing on the nc output: sql>select * from datacell.receptors(); select * from datacell.emitters(); select * from datacell.queries(); select * from datacell.baskets(); +-----------------+-----------+-------+----------+---------+---------+----------------------------+--------+----------+---------+ | nme | host | port | protocol | mode | status | lastseen | cycles | received | pending | +=================+===========+=======+==========+=========+=========+============================+========+==========+=========+ | datacell.bsktin | localhost | 50500 | TCP | passive | running | 2015-03-12 09:58:36.000000 | 1 | 1000 | 1000 | +-----------------+-----------+-------+----------+---------+---------+----------------------------+--------+----------+---------+ 1 tuple (5.729ms) +------------------+-----------+-------+----------+--------+---------+----------------------------+--------+------+---------+ | nme | host | port | protocol | mode | status | lastsent | cycles | sent | pending | +==================+===========+=======+==========+========+=========+============================+========+======+=========+ | datacell.bsktout | localhost | 50600 | UDP | active | running | null | 0 | 0 | 0 | +------------------+-----------+-------+----------+--------+---------+----------------------------+--------+------+---------+ 1 tuple (6.183ms) +---------------+---------+----------------------------+--------+------+----------+------------------------------------------------+------------------------------------------------+ | nme | status | lastrun | cycles | even | time | error | def | : : : : : ts : : : : +===============+=========+============================+========+======+==========+================================================+================================================+ | datacell.pass | running | null | 105797 | 0 | 12205937 | Query datacell.pass failed:SQLException:mvc:SQ | insert into datacell.bsktout select * from dat | : : : : : : : L module not initialized : acell.bsktin; : +---------------+---------+----------------------------+--------+------+----------+------------------------------------------------+------------------------------------------------+ 1 tuple (6.299ms) +------------------+-----------+---------+-----------+-----------+------------+------+----------------------------+--------+ | nme | threshold | winsize | winstride | timeslice | timestride | beat | seen | events | +==================+===========+=========+===========+===========+============+======+============================+========+ | datacell.bsktin | 0 | 0 | 0 | 0 | 0 | 0 | 2015-03-12 10:04:22.000000 | 1000 | | datacell.bsktout | 0 | 0 | 0 | 0 | 0 | 0 | null | 0 | +------------------+-----------+---------+-----------+-----------+------------+------+----------------------------+--------+ 2 tuples (6.487ms) Can someone help? Our MonetDB build is faulty? Regards, Liming

4 5