March 2010 - users-list

[MonetDB-users] Load a file.txt in bats
by Miguel Ángel Águila Lorente 24 Mar '10

24 Mar '10

Hello, I'm creating a database with MAL. I would need help for creating bats with the information of a file. May be It could be posible to do with tablet.load but I still not know how it works. The format of the file is quite simple: int;int;int; int;int;int; I expect you can help me. Thank you. Mike

2 3

[MonetDB-users] Java executeBatch() behavior
by sateesh 23 Mar '10

23 Mar '10

Hi All, When we try to execute a Set of SQL Statements using executeBatch() and if one of the SQL Query fails then MonetDB seems to be ignoring the rest. Is there a way we can configure MonetDB to execute the remaining SQL Queries in the Batch ?? Thanks Sateesh -- View this message in context: http://old.nabble.com/Java-executeBatch%28%29-behavior-tp27783879p27783879.… Sent from the monetdb-users mailing list archive at Nabble.com.

2 6

[MonetDB-users] Transaction Management
by אייל 22 Mar '10

22 Mar '10

hi, this is from the documentation of Transaction Management: "If at commit time it can be assured that the data prepared for update affects tables not changed in the mean time, the results are merged." dose it mean that two users cannot change DIFFERENT records of the same table at the same time? what isolation levels dose MonetDB supports? thanks.

1 0

Re: [MonetDB-users] multiple result sets
by אייל 22 Mar '10

22 Mar '10

Martin, thanks again for your response. lastly i made experiments with mysql (innoDB and innoDB-plugin), postgreSQL and MonetDB. MonetDB won even those queries that weren't the reason MonetDB invented for. yes it's an amazing platform. doing what you suggested with JDBC is possible, but not convenient. for me, without getting into the details of the project that already works against mssql, i would just say it's too much of an effort. i think this feature can help MonetDB become more like a real world database, than an amazing platform for academic research. thanks.

1 0

Re: [MonetDB-users] multiple result sets
by אייל 21 Mar '10

21 Mar '10

sending it again due to a mistake in the subject. sorry. Martin, thanks for your response. my actual application is now served by a mssql server, which has many stored-procedures, some of them are 45kb of TSQL-code, that may return about ten result-sets at once. these procedures are normally executed within about 50msec. having this in mind... for the sake of our discussion only, lets define : "optimization" : automatically turning queries into query templates. "compilation" : parsing the bulk of statements + deciding for each of the queries which "optimization" should be used (or built - if no suitable "optimization" found). "bandwidth": sending a large bulk of statements from the client to the server. now i would like ask: dose MonetDB stores "compilation" for each stored-procedure for reuse? if the answer is "no", then i understand: "optimization" i pay only once :) "compilation" i pay many times :( "bandwidth" i pay only once :) if the answer is "yes": "optimization" i pay only once :) "compilation" i pay only once :) "bandwidth" i pay only once :) the situation today is i cannot use stored-procedure (because they don't return result sets), so it actually dose not meter whether the answer is "yes" or "no": "optimization" i pay only once :) "compilation" i pay many times :( "bandwidth" i pay many times :( since MonetDB actually support returning multiple result-sets (without stored procedure), and it even support returning a single result set (using stored-function), it looks like adding this feature to stored-procedures as well, is not so much of a work (i'm thinking of contributing this work myself, having your kindly help and general directions). but if MonetDB dose NOT even store a "compilation" for each stored procedure, i guess we are too much far away... note: every time i said "stored-procedure", i actually meant "stored-function" as well. thanks again.

2 1

Re: [MonetDB-users] Virtual Memory Requirement
by אייל 21 Mar '10

21 Mar '10

Martin, thanks for your response. my actual application is now served by a mssql server, which has many stored-procedures, some of them are 45kb of TSQL-code, that may return about ten result-sets at once. these procedures are normally executed within about 50msec. having this in mind... for the sake of our discussion only, lets define : "optimization" : automatically turning queries into query templates. "compilation" : parsing the bulk of statements + deciding for each of the queries which "optimization" should be used (or built - if no suitable "optimization" found). "bandwidth": sending a large bulk of statements from the client to the server. now i would like ask: dose MonetDB stores "compilation" for each stored-procedure for reuse? if the answer is "no", then i understand: "optimization" i pay only once :) "compilation" i pay many times :( "bandwidth" i pay only once :) if the answer is "yes": "optimization" i pay only once :) "compilation" i pay only once :) "bandwidth" i pay only once :) the situation today is i cannot use stored-procedure (because they don't return result sets), so it actually dose not meter whether the answer is "yes" or "no": "optimization" i pay only once :) "compilation" i pay many times :( "bandwidth" i pay many times :( since MonetDB actually support returning multiple result-sets (without stored procedure), and it even support returning a single result set (using stored-function), it looks like adding this feature to stored-procedures as well, is not so much of a work (i'm thinking of contributing this work myself, having your kindly help and general directions). but if MonetDB dose NOT even store a "compilation" for each stored procedure, i guess we are too much far away... note: every time i said "stored-procedure", i actually meant "stored-function" as well. thanks again.

1 0

Re: [MonetDB-users] multiple result sets
by אייל 18 Mar '10

18 Mar '10

Fabian, thanks for your response. i did try it. as far as i can see, stored procedures cannot return result set at all... i hoped to get an official verification for my findings. and maybe any work around. about the network traffic: the result would be the same with or without stored procedure, but the request would be much smaller. sending a request like: "call sp1(x,y,z);" is much smaller than a very large bulk of statements. of course, the more critical point is to avoid unnecessary re-compilation of a large and complex bulk of statements, that should be able to execute many times during a single second. in mssql, and mysql, procedures do able to return multiple result sets. i don't know what is the standard sql2003 about it. do you think it worth a bug\feature report? thanks, again.

3 2

[MonetDB-users] multiple result sets
by אייל 18 Mar '10

18 Mar '10

hi, dose monetdb support returning multiple result sets by a stored procedure or a stored function? something equevalent to this (mssql code): create procedure p1 as begin select 'a' as f1; select 'b' as f1, 'c' as f2 end; any workaround?? i realized that if i do (in mclient terminal): sql>select 'a' as f1; select 'b' as f1 ,'c' as f2; i get: f1 -- a f1|f2 ----- b |c but this is not good enough for me. the actual procedures are long and complex, and i want to gain the benefit of avoiding the re-compilation and reduced network traffic (in mssql it makes big difference for me). i also realized that i can do: create function f1() returns table... but this is good for a single result only. BTW: dose the monetdb-jdbc-driver support multiple result sets (java.sql.Statement.getMoreResult() etc.)? thanks!

2 1

[MonetDB-users] newbie question about monetdb5 server installation
by Min Zhou 16 Mar '10

16 Mar '10

hi all, I failed in install moentdb beacause the configure script can't find the correct openssl path. here is my instruction ./monetdb-install.sh --prefix=$HOME/monetdb --enable-sql the failure hapened when compiling monetdb5 server with informations like below: configure: error: MonetDB 5 requires OpenSSL (you need a more recent version of OpenSSL) configuring MonetDB5-server failed Please refer to the full log at /var/tmp/MonetDB-1268650686/log If you believe this is an error in the script or the software, file a bug and attach the logfile. Terminated here is my openssl information: $which openssl /usr/local/ssl/bin/openssl $openssl version OpenSSL 0.9.8m 25 Feb 2010 How can i fix it? thanks, Min -- My research interests are distributed systems, parallel computing and bytecode based virtual machine. My profile: http://www.linkedin.com/in/coderplay My blog: http://coderplay.javaeye.com

4 8

[MonetDB-users] Cursor Alternative
by Nafees Ur Rehman 16 Mar '10

16 Mar '10

Hi, Its mentioned on your website (http://monetdb.cwi.nl/SQL/Documentation/SQL-Features.html#SQL-Features) that monetDB/SQL does not support cursors. My question is whether it has any alternative for cursors? I am working on integrating Data Mining techniques with Database systems. So I need to translate the DM algorithms and write them in monetDB. Regards Nafees Ur Rehman University of Konstanz.

2 1