- users-list - monetdb.org

[MonetDB-users] pcre not found when configure monetdb5
by xldelg＠sohu.com 18 Nov '11

18 Nov '11

hi, I tried to compile the latest monetdb(11.5.3) on centos6 x64, but the pcre library is not found when configure. I'm sure that the pcre library has been installed via compiling the source, the libpcre.la, libpcre.a, libpcre.so has been installed in /usr/local/lib. I also configure monetdb like: pcre_LIBS=/usr/local/lib pcre_CFLAGS=/usr/local/include ./configure, but it doesn't work. How to fix this problem? Any reply will be apprecitated.    

2 2

[MonetDB-users] "TypeException: algebra.join undefined" when evaluating EXISTS (SELECT ...) predicate
by Viktor Rosenfeld 16 Nov '11

16 Nov '11

Hi, I get an error message when executing the following query: SELECT count(*) FROM _rank AS _rank1, _rank AS _rank2 WHERE _rank1.pre = 23 AND _rank2.pre = 33 AND EXISTS (SELECT 1 FROM _rank AS ancestor WHERE ancestor.pre < _rank1.pre AND _rank1.pre < ancestor.post AND ancestor.pre < _rank2.pre AND _rank2.pre < ancestor.post); The error message is: TypeException:user.s0_1[99]:'algebra.join' undefined in: _129:any := algebra.join(_128:bat[:oid,:oid], _108:bat[:int,:oid]) SQLException:SQLengine:Program contains errors A minimal example of test data to run this query follows: CREATE TABLE _rank (pre INTEGER, post INTEGER); INSERT INTO _rank VALUES (22, 37); INSERT INTO _rank VALUES (23, 24); INSERT INTO _rank VALUES (33, 34); Cheers, Viktor

2 2

[MonetDB-users] General Questions on Columnar DB Design and Querying
by Sean McNamara 13 Nov '11

13 Nov '11

Hi all, I've managed to get MonetDB running and loaded with a chunk of data (thanks to some great help from the list) and am now trying to figure out the "right" way to make use of it. I don't have a whole lot of exposure to columnar stores, and am therefore not sure if I'm doing things in an idiomatically correct way. I have a couple general questions about columnar DBs, and hope its appropriate to ask them here even thought they aren't specific to MonetDB. If these questions aren't considered appropriate for this list I apologize in advance and will happily move them to a different forum (but would appreciate pointers to where such a forum might be.) Two questions are general, and one is fairly specific. First the general: 1. Does working with a columnar store fundamentally change the way one would structure the tables? I'm somewhat familiar with star-schema designs where a central fact table holds the entire attribute set in as rows of indexes with supporting lookup tables for each column type, but it seems like columnar stores achieve the same thing without actually requiring the developer to specifically build those structures. Tables are constructed in a de-normalized RDBMS manner and the underlying DB takes care of the vectorization. Is this a valid description, or am I fundamentally misunderstanding the basics? 2. If there are significant differences in the way columnar stores should be designed, can folks point me to any resources (web, book, article, etc.) to help educate me on the topic? Now the specific: I have several sets of time-series data modeled as follows: create table sensordata ( sensor varchar(32), epoch_ts bigint, reading double, CONSTRAINT SENSORDATA_PKEY PRIMARY KEY (sensor, epoch_ts) ); I've populated that data, and now want to query data for a subset of sensors and generate a table that shows the value of the sensors at each point in time. I'm not sure what the right way to do this is. My desired out:ut would be: epoch_ts sensor1 sensor2 delta -------------- ------------ ------------ ------ <time1> 23.5 24.1 0.6 <time2> 19.3 25.2 5.9 ... <timeN> 25.1 19.3 -5.8 I tried to build that query as follows but end up with NULL results (note: I'm in no way a SQL pro so it might be really ugly.) select a.epoch_ts as epoch, a.reading as sensor1_reading, b.reading as sensor2_reading, b.reading - a.reading as delta from (select epoch_ts, reading from sensordata where sensor='sensor1') a, (select epoch_ts, reading from sensordata where sensor='sensor2') b where b.epoch_ts = a.epoch_ts order by epoch asc; Is this a sane way to approach the problem, or am I completely off track here. Each subselect returns results, but they obviously aren't joining correctly and it feels like it might simply be the wrong way to try to build the result set. I appreciate any direction you might provide. Thanks!

2 1

[MonetDB-users] MonetDB Permission/COPY INTO Questions
by Sean McNamara 13 Nov '11

13 Nov '11

Hi All, I'm just staring to play with MonetDB, but am running into some trouble with a few basic tasks. I've managed to create a new DB and have added a table. I'd now like to bulk-load the table with a CSV file full of data. When I try to do so, I get the following: WS3:~ tank$ mclient -u testuser testdb Welcome to mclient, the MonetDB/SQL interactive terminal (Aug2011) Database: MonetDB v11.5.1, 'testdb' Type \q to quit, \? for a list of available commands auto commit mode: on sql>COPY 1800 records into testdata from 'testdata.csv'; COPY INTO: insufficient privileges: COPY INTO from file(s) requires administrator rights, use 'COPY INTO "testdata" FROM STDIN' instead I then try to copy into via STDIN: WS3:~ tank$ mclient -u testuser -s "COPY 1800 records into testdata from STDIN using delimiters ',';" testdb < testdata.csv WS3:~ tank$ mclient -u testuser testdb Welcome to mclient, the MonetDB/SQL interactive terminal (Aug2011) Database: MonetDB v11.5.1, 'testdb' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select * testdb; +-----+-----+------+ | foo | bar | baz | +===+==+===+ +-----+-----+------+ 0 tuples (2.307ms) sql> It seems clear I have some sort of permission/privilege issue, but I'm not clear how to resolve it. I've looked at all the tables and see the auths, users, user_role, and other tables, but it's not clear what actually needs to be tweaked there or if I should be using other DB commands to set things up. I've tried signing on as the monetdb user to do the import, but the monetdb user doesn't appear to be able to see the table: WS3:~ tank$ mclient -u monetdb testdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Aug2011) Database: MonetDB v11.5.1, 'testdb' Type \q to quit, \? for a list of available commands auto commit mode: on sql>COPY 1800 records into testdata from 'testdata.csv'; COPY INTO: no such table 'testdata' I realize this should all be quite simple, but am not finding my way forward. I've been searching the archives and hunting on google but am coming up empty. Can anyone point the way? Thanks!

6 11

[MonetDB-users] General questions on columnar DB design and querying
by Sean McNamara 13 Nov '11

13 Nov '11

Hi all, I've managed to get MonetDB running and loaded with a chunk of data (thanks to some great help from the list) and am now trying to figure out the "right" way to make use of it. I don't have a whole lot of exposure to columnar stores, and am therefore not sure if I'm doing things in an idiomatically correct way. I have a couple general questions about columnar DBs, and hope its appropriate to ask them here even thought they aren't specific to MonetDB. If these questions aren't considered appropriate for this list I apologize in advance and will happily move them to a different forum (but would appreciate pointers to where such a forum might be.) Two questions are general, and one is fairly specific. First the general: 1. Does working with a columnar store fundamentally change the way one would structure the tables? I'm somewhat familiar with star-schema designs where a central fact table holds the entire attribute set in as rows of indexes with supporting lookup tables for each column type, but it seems like columnar stores achieve the same thing without actually requiring the developer to specifically build those structures. Tables are constructed in a de-normalized RDBMS manner and the underlying DB takes care of the vectorization. Is this a valid description, or am I fundamentally misunderstanding the basics? 2. If there are significant differences in the way columnar stores should be designed, can folks point me to any resources (web, book, article, etc.) to help educate me on the topic? Now the specific: I have several sets of time-series data modeled as follows: create table sensordata ( sensor varchar(32), epoch_ts bigint, reading double, CONSTRAINT SENSORDATA_PKEY PRIMARY KEY (sensor, epoch_ts) ); I've populated that data, and now want to query data for a subset of sensors and generate a table that shows the value of the sensors at each point in time. I'm not sure what the right way to do this is. My desired out:ut would be: epoch_ts sensor1 sensor2 delta -------------- ------------ ------------ ------ <time1> 23.5 24.1 0.6 <time2> 19.3 25.2 5.9 ... <timeN> 25.1 19.3 -5.8 I tried to build that query as follows but end up with NULL results (note: I'm in no way a SQL pro so it might be really ugly.) select a.epoch_ts as epoch, a.reading as sensor1_reading, b.reading as sensor2_reading, b.reading - a.reading as delta from (select epoch_ts, reading from sensordata where sensor='sensor1') a, (select epoch_ts, reading from sensordata where sensor='sensor2') b where b.epoch_ts = a.epoch_ts order by epoch asc; Is this a sane way to approach the problem, or am I completely off track here. Each subselect returns results, but they obviously aren't joining correctly and it feels like it might simply be the wrong way to try to build the result set. I appreciate any direction you might provide. Thanks!

1 0

Re: [MonetDB-users] MonetDB Permission/COPY INTO Questions
by Allen Zhang 12 Nov '11

12 Nov '11

Sean, Maybe here is why you did not spot the duplicate: cat /tmp/testdata2.csv | cut -d, -f1,5 | sort | uniq -d Regards Allen On 11/12/11 12:39 PM, "Sean McNamara" <tank(a)thursday.org> wrote: >Looks like I had too much faith in the "uniq" command (or >misunderstand its constraints.) There are definitely duplicates in my >import set. :-( > >Back to data scrubbing... > >On Sat, Nov 12, 2011 at 12:08 PM, Allen Zhang <azhang(a)pmci-usa.com> wrote: >> Sean, >> >> Use sql select to find out duplicates. >> >> Regards >> Allen >> >> On 11/12/11 12:01 PM, "Sean McNamara" <tank(a)thursday.org> wrote: >> >>>I tried that (as both the normal dbuser and as admin). Both fail the >>>same way but don't give an indicator of where it feels the constraint >>>is violated: >>> >>>$ mclient -u monetdb -s "alter table mduser.testdata add constraint >>>testdata_pkey primary key (foo,epoch);" testdb >>>password: >>>SQLException:assert:UPDATE: PRIMARY KEY constraint >>>'testdata.testdata_pkey' violated >>> >>> >>>On Sat, Nov 12, 2011 at 11:42 AM, Allen Zhang <azhang(a)pmci-usa.com> >>>wrote: >>>> How about add constraint after you import? I think it might tell you >>>>which >>>> one had problem. >>>> >>>> Regards >>>> Allen >>>> >>>> On 11/12/11 11:19 AM, "Sean McNamara" <tank(a)thursday.org> wrote: >>>> >>>>>Brilliant suggestion, and I think it exposed the problem. >>>>> >>>>>$ mclient -u monetdb -s "copy into mduser.testdata from >>>>>'/tmp/testdata2.csv' using delimiters ',','\n','\'';" testdb >>>>>password: >>>>>SQLException:importTable:value 'ONE_SECOND' from line 12734 field 3 >>>>>not inserted, expecting type varchar(8) >>>>>failed to import table >>>>> >>>>>It looks like the field I created is too small to handle some of the >>>>>values in the file. After expanding all the varchar fields to 32 and >>>>>re-running, I get a different error: >>>>> >>>>>$ mclient -u monetdb -s "copy into mduser.testdata from >>>>>'/tmp/testdata2.csv' using delimiters ',','\n','\'';" testdb >>>>>password: >>>>>SQLException:assert:INSERT INTO: PRIMARY KEY constraint >>>>>'testdata.testdata_pkey' violated >>>>> >>>>>That makes me think I have duplicate data. The PKEY is comprised of >>>>>fields #1 and #5, so I check for that but it looks fine: >>>>> >>>>>$ cat /tmp/testdata2.csv | cut -d, -f1,5 |uniq -d >>>>>$ >>>>> >>>>>However if I drop and re-add the table without the constraint, it >>>>>works: >>>>> >>>>>sql>create table testdata ( >>>>>more> foo varchar(32), >>>>>more> bar varchar(32), >>>>>more> baz varchar(32), >>>>>more> woz varchar(32), >>>>>more> epoch bigint >>>>>more>); >>>>>operation successful (1.477ms) >>>>>sql>\q >>>>>$ mclient -u monetdb -s "copy into mduser.testdata from >>>>>'/tmp/testdata2.csv' using delimiters ',','\n','\'';" testdb >>>>>password: >>>>>2519931 affected rows >>>>> >>>>> >>>>>Thanks Allen! This gets me able to import. Now I'm wondering what >>>>>might be wrong with the constraint. Any insight there? >>>>> >>>>> >>>>> >>>>>On Sat, Nov 12, 2011 at 10:58 AM, Allen Zhang <azhang(a)pmci-usa.com> >>>>>wrote: >>>>>> What about just issue the following: >>>>>> >>>>>> mclient -s "copy into testdata from '/tmp/testdata2.csv' using >>>>>>delimiters >>>>>> ',','\n','\'';" >>>>>> >>>>>> >>>>>> Regards >>>>>> Allen >>>>>> >>>>>> >>>>>> On 11/12/11 10:48 AM, "Sean McNamara" <tank(a)thursday.org> wrote: >>>>>> >>>>>>>Thanks Allen. Red-herring there. My first file had "SPY" in the >>>>>>>first column, and the second had "testdata1", so the difference >>>>>>>there >>>>>>>is just because of the contents. If I convert the original SPY file >>>>>>>and re-run, I get the same behavior. >>>>>>> >>>>>>>$ mclient -s "copy into testdata from STDIN using delimiters >>>>>>>',','\n','\'';" testdb - < /tmp/testdata2.csv >>>>>>>syntax error, unexpected STRING in: "'SPY'" >>>>>>> >>>>>>> >>>>>>>On Sat, Nov 12, 2011 at 10:43 AM, Allen Zhang <azhang(a)pmci-usa.com> >>>>>>>wrote: >>>>>>>> Hi Sean, >>>>>>>> >>>>>>>> You should inspect your data quality, it seems in the first 3 >>>>>>>>imports, >>>>>>>>it >>>>>>>> stopped at "spy", I suspect it might be some \t in that supposed >>>>>>>>to >>>>>>>>be >>>>>>>> string, and last import you put single-quote around string, then >>>>>>>>it >>>>>>>> stopped at "testvalue1", search your import file, look for that >>>>>>>> "testvalue1", the problem should around there, maybe an extra >>>>>>>>field >>>>>>>> because wrong replace by sed. >>>>>>>> >>>>>>>> Best Regards >>>>>>>> Allen >>>>>>>> >>>>>>>> On 11/12/11 10:26 AM, "Sean McNamara" <tank(a)thursday.org> wrote: >>>>>>>> >>>>>>>>>Thanks Viktor, but no dice. I also tried converting from CSV to >>>>>>>>>TAB-delim to match what you were doing, but it's still not >>>>>>>>>working... >>>>>>>>> >>>>>>>>>$ mclient -s "copy into testdata from STDIN using delimiters >>>>>>>>>',','\n','';" testdb - < /tmp/testdata.csv >>>>>>>>>syntax error, unexpected IDENT in: "spy" >>>>>>>>> >>>>>>>>>$ sed 's/,/\'$'\t''/g' /tmp/testdata.csv > /tmp/testdata.dat >>>>>>>>> >>>>>>>>>$ mclient -s "copy into testdata from STDIN;" testdb - < >>>>>>>>>/tmp/testdata.dat >>>>>>>>>syntax error, unexpected IDENT in: "spy" >>>>>>>>> >>>>>>>>>$ mclient -s "copy into testdata from STDIN using delimiters >>>>>>>>>'\t','\n','';" testdb - < /tmp/testdata.dat >>>>>>>>>syntax error, unexpected IDENT in: "spy" >>>>>>>>> >>>>>>>>>I'm understanding the format of the documentation quite a bit >>>>>>>>>better >>>>>>>>>now after spending a few days with it, but I'm still not clear on >>>>>>>>>why >>>>>>>>>this insert is failing. >>>>>>>>>I went one step further and converted the input file to >>>>>>>>>single-quote >>>>>>>>>the string fields, but this fails as well: >>>>>>>>> >>>>>>>>>$ mclient -s "copy into testdata from STDIN using delimiters >>>>>>>>>',','\n','\'';" testdb - < /tmp/testdata2.csv >>>>>>>>>syntax error, unexpected STRING in: "'testvalue1'" >>>>>>>>> >>>>>>>>> >>>>>>>>>The problem has to be something trivial, but I have no idea what >>>>>>>>>it >>>>>>>>>is... >>>>>>>>> >>>>>>>>>On Sat, Nov 12, 2011 at 5:30 AM, Viktor Rosenfeld >>>>>>>>><listuser36(a)googlemail.com> wrote: >>>>>>>>>> Hi Sean, >>>>>>>>>> >>>>>>>>>> Sean McNamara wrote: >>>>>>>>>> >>>>>>>>>>> $ mclient -s "copy 2519931 records into testdata from STDIN >>>>>>>>>>>using >>>>>>>>>>> delimiters ',';" testdb - < /tmp/testdata.csv >>>>>>>>>>> syntax error, unexpected IDENT in: "testvalue1" >>>>>>>>>>> >>>>>>>>>>> where testvalue1 is one of the actual values in the FOO column >>>>>>>>>>> (actually the only value in this particular file.) >>>>>>>>>>> >>>>>>>>>>> I'm wondering if this is telling me it's expecting the field to >>>>>>>>>>>be >>>>>>>>>>> quoted, but that seems unlikely as it would make working with >>>>>>>>>>>CSVs >>>>>>>>>>> quite a bit painful. >>>>>>>>>>> >>>>>>>>>>> Can you guys kindly help me take another step forward. I >>>>>>>>>>>really >>>>>>>>>>> appreciate the guidance. >>>>>>>>>> >>>>>>>>>> I'm using >>>>>>>>>> >>>>>>>>>> COPY ... USING DELIMITERS '\t','\n',''; >>>>>>>>>> >>>>>>>>>> to bulk-load tab-delimited unquoted data. The '\t' is the >>>>>>>>>>default >>>>>>>>>>field >>>>>>>>>> separator, the '\n' is the default record separator. The final >>>>>>>>>>'' >>>>>>>>>>is >>>>>>>>>>the >>>>>>>>>> quote string -- empty in my case. >>>>>>>>>> >>>>>>>>>> See: >>>>>>>>>>http://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInt >>>>>>>>>>o >>>>>>>>>> >>>>>>>>>> Cheers, >>>>>>>>>> Viktor >>>>>>>>>> >>>>>>>>> >>>>>>>>>------------------------------------------------------------------ >>>>>>>>>-- >>>>>>>>>-- >>>>>>>>>-- >>>>>>>>>-- >>>>>>>>>---- >>>>>>>>>RSA(R) Conference 2012 >>>>>>>>>Save $700 by Nov 18 >>>>>>>>>Register now >>>>>>>>>http://p.sf.net/sfu/rsa-sfdev2dev1 >>>>>>>>>_______________________________________________ >>>>>>>>>MonetDB-users mailing list >>>>>>>>>MonetDB-users(a)lists.sourceforge.net >>>>>>>>>https://lists.sourceforge.net/lists/listinfo/monetdb-users >>>>>>>> >>>>>>>> >>>>>>>> >>>>>> >>>>>> >>>>>> >>>> >>>> >>>> >> >> >>

1 0

Re: [MonetDB-users] MonetDB Permission/COPY INTO Questions
by Allen Zhang 12 Nov '11

12 Nov '11

Sean, Use sql select to find out duplicates. Regards Allen On 11/12/11 12:01 PM, "Sean McNamara" <tank(a)thursday.org> wrote: >I tried that (as both the normal dbuser and as admin). Both fail the >same way but don't give an indicator of where it feels the constraint >is violated: > >$ mclient -u monetdb -s "alter table mduser.testdata add constraint >testdata_pkey primary key (foo,epoch);" testdb >password: >SQLException:assert:UPDATE: PRIMARY KEY constraint >'testdata.testdata_pkey' violated > > >On Sat, Nov 12, 2011 at 11:42 AM, Allen Zhang <azhang(a)pmci-usa.com> wrote: >> How about add constraint after you import? I think it might tell you >>which >> one had problem. >> >> Regards >> Allen >> >> On 11/12/11 11:19 AM, "Sean McNamara" <tank(a)thursday.org> wrote: >> >>>Brilliant suggestion, and I think it exposed the problem. >>> >>>$ mclient -u monetdb -s "copy into mduser.testdata from >>>'/tmp/testdata2.csv' using delimiters ',','\n','\'';" testdb >>>password: >>>SQLException:importTable:value 'ONE_SECOND' from line 12734 field 3 >>>not inserted, expecting type varchar(8) >>>failed to import table >>> >>>It looks like the field I created is too small to handle some of the >>>values in the file. After expanding all the varchar fields to 32 and >>>re-running, I get a different error: >>> >>>$ mclient -u monetdb -s "copy into mduser.testdata from >>>'/tmp/testdata2.csv' using delimiters ',','\n','\'';" testdb >>>password: >>>SQLException:assert:INSERT INTO: PRIMARY KEY constraint >>>'testdata.testdata_pkey' violated >>> >>>That makes me think I have duplicate data. The PKEY is comprised of >>>fields #1 and #5, so I check for that but it looks fine: >>> >>>$ cat /tmp/testdata2.csv | cut -d, -f1,5 |uniq -d >>>$ >>> >>>However if I drop and re-add the table without the constraint, it works: >>> >>>sql>create table testdata ( >>>more> foo varchar(32), >>>more> bar varchar(32), >>>more> baz varchar(32), >>>more> woz varchar(32), >>>more> epoch bigint >>>more>); >>>operation successful (1.477ms) >>>sql>\q >>>$ mclient -u monetdb -s "copy into mduser.testdata from >>>'/tmp/testdata2.csv' using delimiters ',','\n','\'';" testdb >>>password: >>>2519931 affected rows >>> >>> >>>Thanks Allen! This gets me able to import. Now I'm wondering what >>>might be wrong with the constraint. Any insight there? >>> >>> >>> >>>On Sat, Nov 12, 2011 at 10:58 AM, Allen Zhang <azhang(a)pmci-usa.com> >>>wrote: >>>> What about just issue the following: >>>> >>>> mclient -s "copy into testdata from '/tmp/testdata2.csv' using >>>>delimiters >>>> ',','\n','\'';" >>>> >>>> >>>> Regards >>>> Allen >>>> >>>> >>>> On 11/12/11 10:48 AM, "Sean McNamara" <tank(a)thursday.org> wrote: >>>> >>>>>Thanks Allen. Red-herring there. My first file had "SPY" in the >>>>>first column, and the second had "testdata1", so the difference there >>>>>is just because of the contents. If I convert the original SPY file >>>>>and re-run, I get the same behavior. >>>>> >>>>>$ mclient -s "copy into testdata from STDIN using delimiters >>>>>',','\n','\'';" testdb - < /tmp/testdata2.csv >>>>>syntax error, unexpected STRING in: "'SPY'" >>>>> >>>>> >>>>>On Sat, Nov 12, 2011 at 10:43 AM, Allen Zhang <azhang(a)pmci-usa.com> >>>>>wrote: >>>>>> Hi Sean, >>>>>> >>>>>> You should inspect your data quality, it seems in the first 3 >>>>>>imports, >>>>>>it >>>>>> stopped at "spy", I suspect it might be some \t in that supposed to >>>>>>be >>>>>> string, and last import you put single-quote around string, then it >>>>>> stopped at "testvalue1", search your import file, look for that >>>>>> "testvalue1", the problem should around there, maybe an extra field >>>>>> because wrong replace by sed. >>>>>> >>>>>> Best Regards >>>>>> Allen >>>>>> >>>>>> On 11/12/11 10:26 AM, "Sean McNamara" <tank(a)thursday.org> wrote: >>>>>> >>>>>>>Thanks Viktor, but no dice. I also tried converting from CSV to >>>>>>>TAB-delim to match what you were doing, but it's still not >>>>>>>working... >>>>>>> >>>>>>>$ mclient -s "copy into testdata from STDIN using delimiters >>>>>>>',','\n','';" testdb - < /tmp/testdata.csv >>>>>>>syntax error, unexpected IDENT in: "spy" >>>>>>> >>>>>>>$ sed 's/,/\'$'\t''/g' /tmp/testdata.csv > /tmp/testdata.dat >>>>>>> >>>>>>>$ mclient -s "copy into testdata from STDIN;" testdb - < >>>>>>>/tmp/testdata.dat >>>>>>>syntax error, unexpected IDENT in: "spy" >>>>>>> >>>>>>>$ mclient -s "copy into testdata from STDIN using delimiters >>>>>>>'\t','\n','';" testdb - < /tmp/testdata.dat >>>>>>>syntax error, unexpected IDENT in: "spy" >>>>>>> >>>>>>>I'm understanding the format of the documentation quite a bit better >>>>>>>now after spending a few days with it, but I'm still not clear on >>>>>>>why >>>>>>>this insert is failing. >>>>>>>I went one step further and converted the input file to single-quote >>>>>>>the string fields, but this fails as well: >>>>>>> >>>>>>>$ mclient -s "copy into testdata from STDIN using delimiters >>>>>>>',','\n','\'';" testdb - < /tmp/testdata2.csv >>>>>>>syntax error, unexpected STRING in: "'testvalue1'" >>>>>>> >>>>>>> >>>>>>>The problem has to be something trivial, but I have no idea what it >>>>>>>is... >>>>>>> >>>>>>>On Sat, Nov 12, 2011 at 5:30 AM, Viktor Rosenfeld >>>>>>><listuser36(a)googlemail.com> wrote: >>>>>>>> Hi Sean, >>>>>>>> >>>>>>>> Sean McNamara wrote: >>>>>>>> >>>>>>>>> $ mclient -s "copy 2519931 records into testdata from STDIN using >>>>>>>>> delimiters ',';" testdb - < /tmp/testdata.csv >>>>>>>>> syntax error, unexpected IDENT in: "testvalue1" >>>>>>>>> >>>>>>>>> where testvalue1 is one of the actual values in the FOO column >>>>>>>>> (actually the only value in this particular file.) >>>>>>>>> >>>>>>>>> I'm wondering if this is telling me it's expecting the field to >>>>>>>>>be >>>>>>>>> quoted, but that seems unlikely as it would make working with >>>>>>>>>CSVs >>>>>>>>> quite a bit painful. >>>>>>>>> >>>>>>>>> Can you guys kindly help me take another step forward. I really >>>>>>>>> appreciate the guidance. >>>>>>>> >>>>>>>> I'm using >>>>>>>> >>>>>>>> COPY ... USING DELIMITERS '\t','\n',''; >>>>>>>> >>>>>>>> to bulk-load tab-delimited unquoted data. The '\t' is the default >>>>>>>>field >>>>>>>> separator, the '\n' is the default record separator. The final '' >>>>>>>>is >>>>>>>>the >>>>>>>> quote string -- empty in my case. >>>>>>>> >>>>>>>> See: >>>>>>>>http://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto >>>>>>>> >>>>>>>> Cheers, >>>>>>>> Viktor >>>>>>>> >>>>>>> >>>>>>>-------------------------------------------------------------------- >>>>>>>-- >>>>>>>-- >>>>>>>-- >>>>>>>---- >>>>>>>RSA(R) Conference 2012 >>>>>>>Save $700 by Nov 18 >>>>>>>Register now >>>>>>>http://p.sf.net/sfu/rsa-sfdev2dev1 >>>>>>>_______________________________________________ >>>>>>>MonetDB-users mailing list >>>>>>>MonetDB-users(a)lists.sourceforge.net >>>>>>>https://lists.sourceforge.net/lists/listinfo/monetdb-users >>>>>> >>>>>> >>>>>> >>>> >>>> >>>> >> >> >>

1 0

Re: [MonetDB-users] MonetDB Permission/COPY INTO Questions
by Allen Zhang 12 Nov '11

12 Nov '11

What about just issue the following: mclient -s "copy into testdata from '/tmp/testdata2.csv' using delimiters ',','\n','\'';" Regards Allen On 11/12/11 10:48 AM, "Sean McNamara" <tank(a)thursday.org> wrote: >Thanks Allen. Red-herring there. My first file had "SPY" in the >first column, and the second had "testdata1", so the difference there >is just because of the contents. If I convert the original SPY file >and re-run, I get the same behavior. > >$ mclient -s "copy into testdata from STDIN using delimiters >',','\n','\'';" testdb - < /tmp/testdata2.csv >syntax error, unexpected STRING in: "'SPY'" > > >On Sat, Nov 12, 2011 at 10:43 AM, Allen Zhang <azhang(a)pmci-usa.com> wrote: >> Hi Sean, >> >> You should inspect your data quality, it seems in the first 3 imports, >>it >> stopped at "spy", I suspect it might be some \t in that supposed to be >> string, and last import you put single-quote around string, then it >> stopped at "testvalue1", search your import file, look for that >> "testvalue1", the problem should around there, maybe an extra field >> because wrong replace by sed. >> >> Best Regards >> Allen >> >> On 11/12/11 10:26 AM, "Sean McNamara" <tank(a)thursday.org> wrote: >> >>>Thanks Viktor, but no dice. I also tried converting from CSV to >>>TAB-delim to match what you were doing, but it's still not working... >>> >>>$ mclient -s "copy into testdata from STDIN using delimiters >>>',','\n','';" testdb - < /tmp/testdata.csv >>>syntax error, unexpected IDENT in: "spy" >>> >>>$ sed 's/,/\'$'\t''/g' /tmp/testdata.csv > /tmp/testdata.dat >>> >>>$ mclient -s "copy into testdata from STDIN;" testdb - < >>>/tmp/testdata.dat >>>syntax error, unexpected IDENT in: "spy" >>> >>>$ mclient -s "copy into testdata from STDIN using delimiters >>>'\t','\n','';" testdb - < /tmp/testdata.dat >>>syntax error, unexpected IDENT in: "spy" >>> >>>I'm understanding the format of the documentation quite a bit better >>>now after spending a few days with it, but I'm still not clear on why >>>this insert is failing. >>>I went one step further and converted the input file to single-quote >>>the string fields, but this fails as well: >>> >>>$ mclient -s "copy into testdata from STDIN using delimiters >>>',','\n','\'';" testdb - < /tmp/testdata2.csv >>>syntax error, unexpected STRING in: "'testvalue1'" >>> >>> >>>The problem has to be something trivial, but I have no idea what it >>>is... >>> >>>On Sat, Nov 12, 2011 at 5:30 AM, Viktor Rosenfeld >>><listuser36(a)googlemail.com> wrote: >>>> Hi Sean, >>>> >>>> Sean McNamara wrote: >>>> >>>>> $ mclient -s "copy 2519931 records into testdata from STDIN using >>>>> delimiters ',';" testdb - < /tmp/testdata.csv >>>>> syntax error, unexpected IDENT in: "testvalue1" >>>>> >>>>> where testvalue1 is one of the actual values in the FOO column >>>>> (actually the only value in this particular file.) >>>>> >>>>> I'm wondering if this is telling me it's expecting the field to be >>>>> quoted, but that seems unlikely as it would make working with CSVs >>>>> quite a bit painful. >>>>> >>>>> Can you guys kindly help me take another step forward. I really >>>>> appreciate the guidance. >>>> >>>> I'm using >>>> >>>> COPY ... USING DELIMITERS '\t','\n',''; >>>> >>>> to bulk-load tab-delimited unquoted data. The '\t' is the default >>>>field >>>> separator, the '\n' is the default record separator. The final '' is >>>>the >>>> quote string -- empty in my case. >>>> >>>> See: >>>>http://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto >>>> >>>> Cheers, >>>> Viktor >>>> >>> >>>------------------------------------------------------------------------ >>>-- >>>---- >>>RSA(R) Conference 2012 >>>Save $700 by Nov 18 >>>Register now >>>http://p.sf.net/sfu/rsa-sfdev2dev1 >>>_______________________________________________ >>>MonetDB-users mailing list >>>MonetDB-users(a)lists.sourceforge.net >>>https://lists.sourceforge.net/lists/listinfo/monetdb-users >> >> >>

1 0

Re: [MonetDB-users] Extended Fetch ODBC Error
by Jeremy O'Sullivan 10 Nov '11

10 Nov '11

Hi Sjoerd, Would it be possible to test your thesis ? The OBI guys reckon that for odbc 2 connections, they use the function SQLExtendedFetch. This has bee replaced by SQLFetchScroll in odbc 3.5. Not sure if that helps, I know little or nothing about odbc, other than many BI tools only use this interface and not jdbc. Regards, Jeremy It is unclear from this message what exactly fails. My guess (since I saw something like that recently) is that Oracle's OBI EE tries to set the statement option (or attribute) SQL_ATTR_CONCURRENCY to a value that is not supported. We only support setting the value to SQL_CONCUR_READ_ONLY. Attempting to set the attribute to any other value will return the above error (not implemented). Perhaps what we should do instead here is accept the value and return a message (in the ODBC way) that instead of the value that the application asked for we set it to SQL_CONCUR_READ_ONLY. Other possible statement attributes that could return the feature not implemented error are SQL_ATTR_ASYNC_ENABLE, SQL_ATTR_CURSOR_SENSITIVITY, SQL_ATTR_ENABLE_AUTO_IPD, SQL_ATTR_FETCH_BOOKMARK_PTR, SQL_ATTR_KEYSET_SIZE, SQL_ATTR_MAX_LENGTH, SQL_ATTR_MAX_ROWS, SQL_ATTR_QUERY_TIMEOUT, SQL_ATTR_ROW_NUMBER, SQL_ATTR_SIMULATE_CURSOR, and SQL_ATTR_USE_BOOKMARKS. On 9 Nov 2011, at 12:26, Jeremy O'Sullivan wrote: > Hello, > > When trying to connect to MonetDB using Oracle's OBI EE, I get the error below. Has anyone seen this before ? > Regards, > Jeremy > > [MonetDB][ODBC Driver 11.5.7]Optional feature not implemented. > > [nQSError: 16006] Setting the statement option for extended fetch failed. > >

2 1

[MonetDB-users] Extended Fetch ODBC Error
by Jeremy O'Sullivan 09 Nov '11

09 Nov '11

Hello, When trying to connect to MonetDB using Oracle's OBI EE, I get the error below. Has anyone seen this before ? Regards, Jeremy [MonetDB][ODBC Driver 11.5.7]Optional feature not implemented. [nQSError: 16006] Setting the statement option for extended fetch failed.

2 1

[MonetDB-users] Tableau Alias error
by Jeremy O'Sullivan 09 Nov '11

09 Nov '11

Hello, When connecting from Tableau, the following syntax generates and error on the command line, is this correct ? (The :w triggers the error.) Regards, Jeremy select count(context_key) as "context_key:w" from context_dim;

2 1

[MonetDB-users] How to check wether a record exists or not in stored procedure
by dengxk 08 Nov '11

08 Nov '11

I am writing a stored procedure to insert an record into my table. If the record exists, just update it. it is easier to acheive in microsoft sql server with the sql: if exists( select column from table where condition ...). As monetdb doesn't support select statment and cursor in stored procedure, is there other walkround?

1 0

Re: [MonetDB-users] confirm e5d538cae9aefced13c1ef02f7f06476bbdd59e0
by charles 07 Nov '11

07 Nov '11

Hello Everyone, I have been starting to work with MonetDB for use with our large production database. It is a 'data warehouse' of sorts. Few comments: a) the php client was not included with built/binary distro b) user and password was not specified in http://www.monetdb.org/Documentation/monetdbd at "Next thing, making a connection to the database using mclient requires the -d argument to specify which database to connect to:" c) 'last crash' field is scary to sys admins/users :) I am going to investigate more, just wanted to pop my head in and say hello. As you can guess, our main dev platform is php, so I will need to be using the php extensions. Cheers, Charles

3 7

[MonetDB-users] alias error
by Jeremy O'Sullivan 07 Nov '11

07 Nov '11

Hello, The following syntax generates and error on the command line, is this correct ? The :w triggers the error. Regards, Jeremy select count(context_key) as "context_key:w" from context_dim;

1 0

[MonetDB-users] ODBC-Extended Fetch
by Jeremy O'Sullivan 07 Nov '11

07 Nov '11

Hello, When trying to connect to MonetDB using Oracle's OBI EE, I get the error below. Has anyone seen this before ? Regards, Jeremy [nQSError: 16001] ODBC error state: HYC00 code: 0 message: [MonetDB][ODBC Driver 11.5.7]Optional feature not implemented. [nQSError: 16006] Setting the statement option for extended fetch failed.

1 0

[MonetDB-users] Errors after executing "set history=true" for saving the query history
by Roberto Fernandez 05 Nov '11

05 Nov '11

Hi, I was trying to save my history based on this documentation http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/History http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/History And this is what is happening: $ mclient -dtest-db -umonetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Aug2011-SP2) Database: MonetDB v11.5.7 (Aug2011-SP2), 'test-db' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (0.623ms) sql>select count(*) from narvq.narvq where zip5 = '81657'; +------+ | L5 | +======+ | 2785 | +------+ 1 tuple (1.6s) sql>set history=true; sql>select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (0.232ms) sql>select count(*) from narvq.narvq where zip5 = '81657'; TypeException:user.s0_1[1]:'user.keepquery' undefined in: _23:any := user.keepquery(_24:wrd, _25:str, _26:lng, _27:lng) TypeException:user.s0_1[32]:'user.keepcall' undefined in: _73:any := user.keepcall(_24:wrd, ctime:timestamp, args:str, xtime:lng, rtime:lng, foot:lng, memory:lng, tuples:wrd, inblock:lng, oublock:lng) SQLException:SQLengine:Program contains errors sql>select * from queryHistory; TypeException:user.s5_1[1]:'user.keepquery' undefined in: _43:any := user.keepquery(_44:wrd, _45:str, _46:lng, _47:lng) TypeException:user.s5_1[48]:'user.keepcall' undefined in: _111:any := user.keepcall(_44:wrd, ctime:timestamp, args:str, xtime:lng, rtime:lng, foot:lng, memory:lng, tuples:wrd, inblock:lng, oublock:lng) SQLException:SQLengine:Program contains errors sql>select 1; TypeException:user.s0_1[1]:'user.keepquery' undefined in: _12:any := user.keepquery(_13:wrd, _14:str, _15:lng, _16:lng) TypeException:user.s0_1[24]:'user.keepcall' undefined in: _55:any := user.keepcall(_13:wrd, ctime:timestamp, args:str, xtime:lng, rtime:lng, foot:lng, memory:lng, tuples:wrd, inblock:lng, oublock:lng) SQLException:SQLengine:Program contains errors sql>set history=false; TypeException:user.s0_1[1]:'user.keepquery' undefined in: _2:any := user.keepquery(_3:wrd, _4:str, _5:lng, _6:lng) TypeException:user.s0_1[19]:'user.keepcall' undefined in: _41:any := user.keepcall(_3:wrd, ctime:timestamp, args:str, xtime:lng, rtime:lng, foot:lng, memory:lng, tuples:wrd, inblock:lng, oublock:lng) SQLException:SQLengine:Program contains errors sql> SERVER INFORMATION: It is a Amazon Web Services Linux distribution (based on CentOS) $ mserver5 --version MonetDB 5 server v11.5.7 "Aug2011-SP2" (64-bit, 64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2011 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 7.3GiB available memory, 2 available cpu cores Libraries: libpcre: 7.8 2008-09-05 (compiled with 7.8) openssl: OpenSSL 1.0.0e 6 Sep 2011 (compiled with OpenSSL 1.0.0e-fips 6 Sep 2011) libxml2: 2.7.6 (compiled with 2.7.6) Compiled by: ec2-user@domU-12-31-39-0E-8D-F2 (x86_64-unknown-linux-gnu) Compilation: gcc -g -O2 Linking : /usr/bin/ld -m elf_x86_64 I will really appreciate any help provided. Thanks, Roberto F -- View this message in context: http://old.nabble.com/Errors-after-executing-%22set-history%3Dtrue%22-for-s… Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] Errors saving query history
by Roberto Fernandez 05 Nov '11

05 Nov '11

Hi, I was trying to save my history based on this documentation http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/History http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/History And this is what is happening: $ mclient -dtest-db -umonetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Aug2011-SP2) Database: MonetDB v11.5.7 (Aug2011-SP2), 'test-db' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (0.623ms) sql>select count(*) from narvq.narvq where zip5 = '81657'; +------+ | L5 | +======+ | 2785 | +------+ 1 tuple (1.6s) sql>set history=true; sql>select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (0.232ms) sql>select count(*) from narvq.narvq where zip5 = '81657'; TypeException:user.s0_1[1]:'user.keepquery' undefined in: _23:any := user.keepquery(_24:wrd, _25:str, _26:lng, _27:lng) TypeException:user.s0_1[32]:'user.keepcall' undefined in: _73:any := user.keepcall(_24:wrd, ctime:timestamp, args:str, xtime:lng, rtime:lng, foot:lng, memory:lng, tuples:wrd, inblock:lng, oublock:lng) SQLException:SQLengine:Program contains errors sql>select * from queryHistory; TypeException:user.s5_1[1]:'user.keepquery' undefined in: _43:any := user.keepquery(_44:wrd, _45:str, _46:lng, _47:lng) TypeException:user.s5_1[48]:'user.keepcall' undefined in: _111:any := user.keepcall(_44:wrd, ctime:timestamp, args:str, xtime:lng, rtime:lng, foot:lng, memory:lng, tuples:wrd, inblock:lng, oublock:lng) SQLException:SQLengine:Program contains errors sql>select 1; TypeException:user.s0_1[1]:'user.keepquery' undefined in: _12:any := user.keepquery(_13:wrd, _14:str, _15:lng, _16:lng) TypeException:user.s0_1[24]:'user.keepcall' undefined in: _55:any := user.keepcall(_13:wrd, ctime:timestamp, args:str, xtime:lng, rtime:lng, foot:lng, memory:lng, tuples:wrd, inblock:lng, oublock:lng) SQLException:SQLengine:Program contains errors sql>set history=false; TypeException:user.s0_1[1]:'user.keepquery' undefined in: _2:any := user.keepquery(_3:wrd, _4:str, _5:lng, _6:lng) TypeException:user.s0_1[19]:'user.keepcall' undefined in: _41:any := user.keepcall(_3:wrd, ctime:timestamp, args:str, xtime:lng, rtime:lng, foot:lng, memory:lng, tuples:wrd, inblock:lng, oublock:lng) SQLException:SQLengine:Program contains errors sql> SERVER INFORMATION: It is a Amazon Web Services Linux distribution (based on CentOS) $ mserver5 --version MonetDB 5 server v11.5.7 "Aug2011-SP2" (64-bit, 64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2011 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 7.3GiB available memory, 2 available cpu cores Libraries: libpcre: 7.8 2008-09-05 (compiled with 7.8) openssl: OpenSSL 1.0.0e 6 Sep 2011 (compiled with OpenSSL 1.0.0e-fips 6 Sep 2011) libxml2: 2.7.6 (compiled with 2.7.6) Compiled by: ec2-user@domU-12-31-39-0E-8D-F2 (x86_64-unknown-linux-gnu) Compilation: gcc -g -O2 Linking : /usr/bin/ld -m elf_x86_64 I will really appreciate any help provided. Thanks, Roberto F -- View this message in context: http://old.nabble.com/Errors-saving-query-history-tp32786364p32786364.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] Errors saving query history
by Roberto Fernandez 05 Nov '11

05 Nov '11

Hi, I was trying to save my history based on this documentation http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/History http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/History And this is what is happening: $ mclient -dtest-db -umonetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Aug2011-SP2) Database: MonetDB v11.5.7 (Aug2011-SP2), 'test-db' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (0.623ms) sql>select count(*) from narvq.narvq where zip5 = '81657'; +------+ | L5 | +======+ | 2785 | +------+ 1 tuple (1.6s) sql>set history=true; sql>select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (0.232ms) sql>select count(*) from narvq.narvq where zip5 = '81657'; TypeException:user.s0_1[1]:'user.keepquery' undefined in: _23:any := user.keepquery(_24:wrd, _25:str, _26:lng, _27:lng) TypeException:user.s0_1[32]:'user.keepcall' undefined in: _73:any := user.keepcall(_24:wrd, ctime:timestamp, args:str, xtime:lng, rtime:lng, foot:lng, memory:lng, tuples:wrd, inblock:lng, oublock:lng) SQLException:SQLengine:Program contains errors sql>select * from queryHistory; TypeException:user.s5_1[1]:'user.keepquery' undefined in: _43:any := user.keepquery(_44:wrd, _45:str, _46:lng, _47:lng) TypeException:user.s5_1[48]:'user.keepcall' undefined in: _111:any := user.keepcall(_44:wrd, ctime:timestamp, args:str, xtime:lng, rtime:lng, foot:lng, memory:lng, tuples:wrd, inblock:lng, oublock:lng) SQLException:SQLengine:Program contains errors sql>select 1; TypeException:user.s0_1[1]:'user.keepquery' undefined in: _12:any := user.keepquery(_13:wrd, _14:str, _15:lng, _16:lng) TypeException:user.s0_1[24]:'user.keepcall' undefined in: _55:any := user.keepcall(_13:wrd, ctime:timestamp, args:str, xtime:lng, rtime:lng, foot:lng, memory:lng, tuples:wrd, inblock:lng, oublock:lng) SQLException:SQLengine:Program contains errors sql>set history=false; TypeException:user.s0_1[1]:'user.keepquery' undefined in: _2:any := user.keepquery(_3:wrd, _4:str, _5:lng, _6:lng) TypeException:user.s0_1[19]:'user.keepcall' undefined in: _41:any := user.keepcall(_3:wrd, ctime:timestamp, args:str, xtime:lng, rtime:lng, foot:lng, memory:lng, tuples:wrd, inblock:lng, oublock:lng) SQLException:SQLengine:Program contains errors sql> SERVER INFORMATION: It is a Amazon Web Services Linux distribution (based on CentOS) $ mserver5 --version MonetDB 5 server v11.5.7 "Aug2011-SP2" (64-bit, 64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2011 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 7.3GiB available memory, 2 available cpu cores Libraries: libpcre: 7.8 2008-09-05 (compiled with 7.8) openssl: OpenSSL 1.0.0e 6 Sep 2011 (compiled with OpenSSL 1.0.0e-fips 6 Sep 2011) libxml2: 2.7.6 (compiled with 2.7.6) Compiled by: ec2-user@domU-12-31-39-0E-8D-F2 (x86_64-unknown-linux-gnu) Compilation: gcc -g -O2 Linking : /usr/bin/ld -m elf_x86_64 I will really appreciate any help provided. Thanks, Roberto F -- View this message in context: http://old.nabble.com/Errors-saving-query-history-tp32786363p32786363.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] Errors saving query history
by Roberto Fernandez 05 Nov '11

05 Nov '11

Hi, I was trying to save my history based on this documentation http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/History http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/History And this is what is happening: $ mclient -dtest-db -umonetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Aug2011-SP2) Database: MonetDB v11.5.7 (Aug2011-SP2), 'test-db' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (0.623ms) sql>select count(*) from narvq.narvq where zip5 = '81657'; +------+ | L5 | +======+ | 2785 | +------+ 1 tuple (1.6s) sql>set history=true; sql>select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (0.232ms) sql>select count(*) from narvq.narvq where zip5 = '81657'; TypeException:user.s0_1[1]:'user.keepquery' undefined in: _23:any := user.keepquery(_24:wrd, _25:str, _26:lng, _27:lng) TypeException:user.s0_1[32]:'user.keepcall' undefined in: _73:any := user.keepcall(_24:wrd, ctime:timestamp, args:str, xtime:lng, rtime:lng, foot:lng, memory:lng, tuples:wrd, inblock:lng, oublock:lng) SQLException:SQLengine:Program contains errors sql>select * from queryHistory; TypeException:user.s5_1[1]:'user.keepquery' undefined in: _43:any := user.keepquery(_44:wrd, _45:str, _46:lng, _47:lng) TypeException:user.s5_1[48]:'user.keepcall' undefined in: _111:any := user.keepcall(_44:wrd, ctime:timestamp, args:str, xtime:lng, rtime:lng, foot:lng, memory:lng, tuples:wrd, inblock:lng, oublock:lng) SQLException:SQLengine:Program contains errors sql>select 1; TypeException:user.s0_1[1]:'user.keepquery' undefined in: _12:any := user.keepquery(_13:wrd, _14:str, _15:lng, _16:lng) TypeException:user.s0_1[24]:'user.keepcall' undefined in: _55:any := user.keepcall(_13:wrd, ctime:timestamp, args:str, xtime:lng, rtime:lng, foot:lng, memory:lng, tuples:wrd, inblock:lng, oublock:lng) SQLException:SQLengine:Program contains errors sql>set history=false; TypeException:user.s0_1[1]:'user.keepquery' undefined in: _2:any := user.keepquery(_3:wrd, _4:str, _5:lng, _6:lng) TypeException:user.s0_1[19]:'user.keepcall' undefined in: _41:any := user.keepcall(_3:wrd, ctime:timestamp, args:str, xtime:lng, rtime:lng, foot:lng, memory:lng, tuples:wrd, inblock:lng, oublock:lng) SQLException:SQLengine:Program contains errors sql> SERVER INFORMATION: It is a Amazon Web Services Linux distribution (based on CentOS) $ mserver5 --version MonetDB 5 server v11.5.7 "Aug2011-SP2" (64-bit, 64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2011 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 7.3GiB available memory, 2 available cpu cores Libraries: libpcre: 7.8 2008-09-05 (compiled with 7.8) openssl: OpenSSL 1.0.0e 6 Sep 2011 (compiled with OpenSSL 1.0.0e-fips 6 Sep 2011) libxml2: 2.7.6 (compiled with 2.7.6) Compiled by: ec2-user@domU-12-31-39-0E-8D-F2 (x86_64-unknown-linux-gnu) Compilation: gcc -g -O2 Linking : /usr/bin/ld -m elf_x86_64 I will really appreciate any help provided. Thanks, Roberto F -- View this message in context: http://old.nabble.com/Errors-saving-query-history-tp32786355p32786355.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] compile error when build MonetDB 11.5.7 on CentOS 5.5
by Allen Zhang 04 Nov '11

04 Nov '11

Hi, When compile on CentOS 5.5, following error pop up, can any one tell me which version of libcom_err.so is needed for this MonetDB version? Thanks Allen make[5]: Entering directory `/usr/src/MonetDB-11.5.7/common/stream' /bin/sh ../../libtool --tag=CC --mode=link gcc -DLIBSTREAM -g -O2 -D_REENTRANT -version-info 2:2:0 -o libstream.la -rpath /usr/local/MonetDBAug2011-SP2/lib libstream_la-stream.lo -lbz2 -L/usr/kerberos/lib64 -lssl -lcrypto -ldl -lz -L/usr/kerberos/lib64 -lcurl -ldl -lgssapi_krb5 -lkrb5 -lk5crypto -lcom_err -lidn -lssl -lcrypto -lz -pthread libtool: link: gcc -shared -fPIC -DPIC .libs/libstream_la-stream.o -lbz2 -L/usr/kerberos/lib64 -lcurl -ldl -lgssapi_krb5 -lkrb5 -lk5crypto -lcom_err -lidn -lssl -lcrypto -lz -O2 -pthread -pthread -Wl,-soname -Wl,libstream.so.2 -o .libs/libstream.so.2.0.2 /usr/bin/ld: skipping incompatible /usr/lib/libcom_err.so when searching for -lcom_err /usr/bin/ld: skipping incompatible /usr/lib/libcom_err.a when searching for -lcom_err /usr/bin/ld: cannot find -lcom_err collect2: ld returned 1 exit status

2 2

Re: [MonetDB-users] compile error when build MonetDB 11.5.7 on CentOS 5.5
by Stefan Manegold 04 Nov '11

04 Nov '11

Hi Allen, from your log, I guess your CentS is 64 bit, right? Is the installed libcom_err also 64 bit or 32 bit? The linker says the sitcom_err it finds is "incompatible" ... Stefan ----- Reply message ----- From: "Allen Zhang" <azhang(a)pmci-usa.com> To: <monetdb-users(a)lists.sourceforge.net> Subject: [MonetDB-users] compile error when build MonetDB 11.5.7 on CentOS 5.5 Date: Fri, Nov 4, 2011 01:07 Hi, When compile on CentOS 5.5, following error pop up, can any one tell me which version of libcom_err.so is needed for this MonetDB version? Thanks Allen make[5]: Entering directory `/usr/src/MonetDB-11.5.7/common/stream' /bin/sh ../../libtool --tag=CC --mode=link gcc -DLIBSTREAM -g -O2 -D_REENTRANT -version-info 2:2:0 -o libstream.la -rpath /usr/local/MonetDBAug2011-SP2/lib libstream_la-stream.lo -lbz2 -L/usr/kerberos/lib64 -lssl -lcrypto -ldl -lz -L/usr/kerberos/lib64 -lcurl -ldl -lgssapi_krb5 -lkrb5 -lk5crypto -lcom_err -lidn -lssl -lcrypto -lz -pthread libtool: link: gcc -shared -fPIC -DPIC .libs/libstream_la-stream.o -lbz2 -L/usr/kerberos/lib64 -lcurl -ldl -lgssapi_krb5 -lkrb5 -lk5crypto -lcom_err -lidn -lssl -lcrypto -lz -O2 -pthread -pthread -Wl,-soname -Wl,libstream.so.2 -o .libs/libstream.so.2.0.2 /usr/bin/ld: skipping incompatible /usr/lib/libcom_err.so when searching for -lcom_err /usr/bin/ld: skipping incompatible /usr/lib/libcom_err.a when searching for -lcom_err /usr/bin/ld: cannot find -lcom_err collect2: ld returned 1 exit status

1 0

[MonetDB-users] I want to join this group:)
by Tony Bai 02 Nov '11

02 Nov '11

Could you add me as a member of Monetdb please? Thanks, Tony

1 0

[MonetDB-users] Temporal coalescing query error
by Alexander S 29 Oct '11

29 Oct '11

Hello, I'm attempting to use monetdb for some temporal queries. Some queries require the output to be coalesced. For example, with schema emp(empno int, ts date, te date) the tuples (00001, '1/1/2001', '2/1/2001'), (00001, '2/1/2001', '3/1/2001') are output as (00001, '1/1/2001', '3/1/2001'). My coalescing query works in both MS SQL Server and DB2 but MonetDB gives me the error "SELECT: no such column 'm.ts'". 1) A leaner alternative query form using window clauses (ROWS UNBOUNDED PRECEDING) doesn't appear to be supported in MonetDB. So I'm stuck. Any help would be appreciated. Query: WITH Emps(empno, ts, te) AS (SELECT empno, ts, te FROM emp) SELECT DISTINCT F.empno, F.ts, L.te FROM Emps AS F, Emps AS L WHERE F.ts < L.te AND F.empno = L.empno AND NOT EXISTS (SELECT * FROM Emps AS M WHERE M.empno = F.empno AND F.ts < M.ts AND M.ts < L.te AND NOT EXISTS (SELECT * FROM Emps AS T1 WHERE T1.empno = F.empno AND T1.ts < M.ts AND M.ts <= T1.te) ) AND NOT EXISTS (SELECT * FROM Emps AS T2 WHERE T2.empno = F.empno AND ((T2.ts < F.ts AND F.ts <= T2.te) OR (T2.ts <= L.te AND L.te < T2.te)) ); Thanks, Alex -- View this message in context: http://old.nabble.com/Temporal-coalescing-query-error-tp32742108p32742108.h… Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] eEnd of Stream
by nat lu 28 Oct '11

28 Oct '11

I'm using Monet 11.5 on Ubuntu 11.04. Ive created a database and am attempting to run a series of queries against it with a test script. This is in attempt to use Monet with Apache Jena. I had earlier mailed about a problem with temp tables, but have dodged that by using the monetdb account for now. The first few succeed, but then I get the following error: java.sql.SQLException: Read from localhost:50000: End of stream reached (mserver still alive?) Why does this happen, under what circumstances does it arise ? Is there anything I can do to avoid it ? This is the query that it fails on: SELECT R_1.lex AS V_1_lex, R_1.datatype AS V_1_datatype, R_1.lang AS V_1_lang, R_1.type AS V_1_type, R_2.lex AS V_2_lex, R_2.datatype AS V_2_datatype, R_2.lang AS V_2_lang, R_2.type AS V_2_type FROM ( SELECT T_1.s AS X_1 FROM Triples AS T_1 WHERE ( T_1.p = 2004134117598721274 AND T_1.o = 435905340492217258 ) ) AS T_1 LEFT OUTER JOIN Triples AS T_2 ON ( T_2.p = 3816485599920428794 AND T_1.X_1 = T_2.s ) LEFT OUTER JOIN Nodes AS R_1 ON ( T_1.X_1 = R_1.hash ) LEFT OUTER JOIN Nodes AS R_2 ON ( T_2.o = R_2.hash )

3 7

[MonetDB-users] IsNumeric() clause in MonetDB
by Osmin Castillo 26 Oct '11

26 Oct '11

Hi everyone: How can I test if a VARCHAR field has numeric data?, at SQLServer I use IsNumeric() clause, what can i use in MonetDB? thanks in advance Osmin

1 0