Re: Stored procedures (2)
Claudio Cicali wrote: I've got a tour on the web, searching for info and ideas, regarding the subject to implement some sort of stored procedure in mysql (remember Lets approach stored procedure thread ?) This was what I discovered - The Perl stored procedures (myperl project) in not a good idea at all, but, even if called poor man's stored procedure, I think that this project don't add, actually, stored procedure in the precise meamning of term. It add only a binding with Perl via UDF. It's a nice (and quite simple) hack. Dana Power, I've not take a look to _your_ hack, but I think this is the same approach. I agree, even if this is a nice hack, it is not the solution we are looking for. - I had searched everywhere the ANSI SQL3 directive... not luck. I only found a 1994 document where this directives were proposed It is quite incomprensile, though (like all standard doc I have read :) Check our book recommendations at http://www.mysql.com/portal/books/html/index.html - I'm evalueting the IBM DB2 approach. Since the ol' COBOL days (rattles...) I remember the EXEC SQL ... END-EXEC commands embedded inside the COBOL as the host language. At compile time there was a cross-compile step the produced some code to get recompiled and finally linked and binded. This is also the (old?) informix approach, where SQL EXECs were embedded inside the C source, and then a cross-compile (or precompiler) make the dirty work, creating another C source that could finally be compiled and linked. I believe a solution where you could invoke code/procedures external to the server is needed and that this mechanism should be fairly independent of how and in which language this code is written. There is no reason why you cannot use whatever means this language might have to access MySQL. The fact that it was called from within the data base server does not change anything. I believe that this mechanism and the embedded stored procedure capability fulfills two quite different needs. But I don't know to have some (low skilled) SQL coders to be well skilled in some other language to write down a stored procedure. So here came my idea: use a SIMPLE language to write the logic of the stored procedure (no complex structures, definition and use of local variables, some test statement, error trapping, and of source free SQL coding), than use a precompiler to convert that source into the source of an UDF. I think we might end up with something along those lines. At last, we had stored procedure written in a simple language, but compiled and used as UDFs. MySQL itself could then be used to store the packaging approach (as Oracle does), or other information regarding validation of the sp, last modification time, user... etc Just some thoughts Claudio Cicali mysql, database, tables Your input is greatly appreciated. /Arnulf - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Stored procedures (2)
I've got a tour on the web, searching for info and ideas, regarding the subject to implement some sort of stored procedure in mysql (remember Lets approach stored procedure thread ?) This was what I discovered - The Perl stored procedures (myperl project) in not a good idea at all, but, even if called poor man's stored procedure, I think that this project don't add, actually, stored procedure in the precise meamning of term. It add only a binding with Perl via UDF. It's a nice (and quite simple) hack. Dana Power, I've not take a look to _your_ hack, but I think this is the same approach. - I had searched everywhere the ANSI SQL3 directive... not luck. I only found a 1994 document where this directives were proposed It is quite incomprensile, though (like all standard doc I have read :) - I'm evalueting the IBM DB2 approach. Since the ol' COBOL days (rattles...) I remember the EXEC SQL ... END-EXEC commands embedded inside the COBOL as the host language. At compile time there was a cross-compile step the produced some code to get recompiled and finally linked and binded. This is also the (old?) informix approach, where SQL EXECs were embedded inside the C source, and then a cross-compile (or precompiler) make the dirty work, creating another C source that could finally be compiled and linked. But I don't know to have some (low skilled) SQL coders to be well skilled in some other language to write down a stored procedure. So here came my idea: use a SIMPLE language to write the logic of the stored procedure (no complex structures, definition and use of local variables, some test statement, error trapping, and of source free SQL coding), than use a precompiler to convert that source into the source of an UDF. At last, we had stored procedure written in a simple language, but compiled and used as UDFs. MySQL itself could then be used to store the packaging approach (as Oracle does), or other information regarding validation of the sp, last modification time, user... etc Just some thoughts Claudio Cicali mysql, database, tables - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Stored procedures (2)
- The Perl stored procedures (myperl project) in not a good idea at all, but, even if called poor man's stored procedure, I think that this project don't add, actually, stored procedure in the precise meamning of term. It add only a binding with Perl via UDF. It's a nice (and quite simple) hack. Dana Power, I've not take a look to _your_ hack, but I think this is the same approach. Actually, its not. Its more along the lines of apache's CGI vs. mod_perl. And its not just perl, its an architecture for pluggable languages ( python, javascript, lisp, or your new pl/sql clone ), which is exactly what you're looking for, if im not mistaken. The problem you're going to have is that MySQL does not support callbacks into the engine - i.e. youre in the middle of an sql call when your function gets called ( whether its udf or something else doesnt matter ), halting everything - effectively pushing it onto 'the stack' - and making separate sql calls, is not supported. You'll find that the hardest part is overcoming the current 'atomic' architecture, which has been used to optimize pretty much every part of the engine ( atomic meaning that 1 sql call is 1 simple actions. ). Of course CREATE ... SELECT and INSERT ... SELECT are simple exceptions, but the same reason that Sub-Selects are non-trivial, is the same reason that stored procedures are non-trivial. dpk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
