tag:blogger.com,1999:blog-15319370.post113270497600586834..comments2024-03-05T11:16:00.846+01:00Comments on Roland Bouman's blog: Using an UDF to Raise Errors from inside MySQL Procedures and/or Triggersrpboumanhttp://www.blogger.com/profile/13365137747952711328noreply@blogger.comBlogger16125tag:blogger.com,1999:blog-15319370.post-61945071236989296262015-05-16T09:40:11.744+02:002015-05-16T09:40:11.744+02:00I read few articles on this site and I think that ...I read few articles on this site and I think that your web blog is real interesting and Power to the People of excellent information.<br /> Keep up the excellent work <br />kutil kelaminhttp://www.pusatpengobatanherbal.com/2015/01/cara-menghilangkan-kutil-di-kemaluan.htmlnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-87115391455731070112009-08-13T15:09:59.088+02:002009-08-13T15:09:59.088+02:00I'm litle iddle !
can you post the binary DLL ...I'm litle iddle !<br />can you post the binary DLL ?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-57601298917531954142009-02-21T00:00:00.000+01:002009-02-21T00:00:00.000+01:00Hi hasimahmet@hotmail.com, Please goto http://www....Hi hasimahmet@hotmail.com, <BR/><BR/>Please goto <BR/><BR/>http://www.mysqludf.org/lib_mysqludf_udf/index.php<BR/><BR/>and download <BR/><BR/>http://www.mysqludf.org/lib_mysqludf_udf/lib_mysqludf_udf_0.0.3.tar.gz<BR/><BR/>Read instructions here to compile on windows:<BR/><BR/>http://rpbouman.blogspot.com/2007/09/creating-mysql-udfs-with-microsoft.html<BR/><BR/>After compiling, read and use http://www.mysqludf.org/lib_mysqludf_udf/index.php#udf_init_error<BR/><BR/>Good luck!rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-53979818572397202302009-02-20T23:54:00.000+01:002009-02-20T23:54:00.000+01:00hi Roland, can i have copyed this code to vis2005 ...hi Roland, can i have copyed this code to vis2005 and i havent compile this code, i have get alot of error. can i get a complied file for linux from you.thanx alot. my email hasimahmet@hotmail.comAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-6523801533730639792008-06-23T09:15:00.000+02:002008-06-23T09:15:00.000+02:00Hi Edson, I'm so glad it is working now ;) Yeah, I...Hi Edson, <BR/><BR/>I'm so glad it is working now ;) <BR/><BR/>Yeah, I think I noticed a change in the message too, maybe in 5.1.23 already. I don't think you can change that easily. Maybe it is possible to hack the message files although that is kind of dodgy.<BR/><BR/>re. returning 0 - no, that won't work. You signal an error by returning something else than zero - that's just how the interface works. <BR/><BR/>To tell you the truth, I think MySQLs current behaviour is actually sane - the error message makes a lot of sense if you really do have a problem with a failed UDF. The UDF to raise an error is basically a hack, just like any of the other methods used to force a runtime error.<BR/><BR/>Anyway, I do think there is something you can do. In your application or better, db access layer, you can check for the code, message *and function name*. As long as you reserve "raise_error" for this trick, you can distinguish between a real UDF failure and a raised error at the application level. Yes, I know - ugly but it will work, and it will be way less cumbersome than for example causing a duplicate key or not null problem. An important thing to note is that with the UDF you at least have a fighting chance to work around it - the other methods to raise a runtime error don't have any method to let you discover an raised error occurred.<BR/><BR/>I hope this helps, <BR/><BR/>Rolandrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-50920640755803468562008-06-23T02:02:00.000+02:002008-06-23T02:02:00.000+02:00Thanks, Roland!You did the trick.Interesting to no...Thanks, Roland!<BR/>You did the trick.<BR/>Interesting to note, MySQL 5.1.25 produces a little different error message as result:<BR/><BR/>mysql> select raise_error('Whoops!');<BR/>ERROR 1123 (HY000): Can't initialize function 'raise_error'; Whoops!<BR/><BR/>So, it's working, besides the error appears with the "Can't initialize function 'raise_error'; in the message.<BR/><BR/>There is any chance to implement this same procedure but using the raise_error (returning the message, and then "return 0" in body of main function)?<BR/><BR/>Actually, this is just a brainstorm...<BR/><BR/>Thanks for your support so far!<BR/><BR/>Edson RichterUnknownhttps://www.blogger.com/profile/07576463899050062035noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-76764902337795210672008-06-21T22:36:00.000+02:002008-06-21T22:36:00.000+02:00My initial though too was that it might be the lon...My initial though too was that it might be the long long, and if you really want to you can use my_longlong or my_ulonglong instead. <BR/><BR/>However it doesn't really add up since the error message says<BR/><BR/>"ERROR 1123 (HY000): Can't initialize function 'raise_error';"<BR/><BR/>Well, since you return 1 from the init function *this is exactly the expected result*.<BR/><BR/>What is unexpected is that you do not see the argument as message.<BR/><BR/>Now, I do see a problem in your init function. This line:<BR/><BR/>argStringLength = strlen(args->args[0]) <BR/><BR/>is not good. You cannot (should not) attempt to determine the argument length using strlen. You see, both UDF args as well as return value (for string UDFs) can convey a binary string. So, these strings are not \0 terminated.<BR/><BR/>Use args->lengths[index] to determine the length instead. (see http://dev.mysql.com/doc/refman/5.1/en/udf-arguments.html)<BR/><BR/>So, something like:<BR/><BR/>my_bool raise_error_init(<BR/> UDF_INIT *initid<BR/>, UDF_ARGS *args<BR/>, char *message<BR/>){ <BR/> if(args->arg_count==1<BR/> && args->arg_type[0]==STRING_RESULT){<BR/> int length = (args->lengths[0] > (MYSQL_ERRMSG_SIZE - 1)) ? MYSQL_ERRMSG_SIZE - 1: args->lengths[0];<BR/> memcpy(<BR/> message<BR/> , args->args[0]<BR/> , length<BR/> );<BR/> message[length] = '\0';<BR/> } else {<BR/> memcpy( <BR/> message<BR/> , "Unspecified error raised"<BR/> , 25<BR/> );<BR/> }<BR/> return 1;<BR/>}<BR/><BR/>should work.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-59290673944224835652008-06-21T19:08:00.000+02:002008-06-21T19:08:00.000+02:00I've changed the code little bit during my tests. ...I've changed the code little bit during my tests. This is the last version:<BR/><BR/>#include <mysql.h><BR/><BR/>#include <string.h><BR/><BR/><BR/>my_bool raise_error_init(UDF_INIT *initid, UDF_ARGS *args, char *message);<BR/><BR/>long long raise_error(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args,<BR/> char *is_null __attribute__((unused)),<BR/> char *error __attribute__((unused)));<BR/><BR/><BR/>my_bool raise_error_init(<BR/> UDF_INIT *initid<BR/><BR/>, UDF_ARGS *args<BR/><BR/>, char *message<BR/>){ <BR/> unsigned int argStringLength;<BR/> if(args->arg_count==1<BR/> && args->arg_type[0]==STRING_RESULT)<BR/> {<BR/> argStringLength = strlen(args->args[0]) <BR/> + 1<BR/> ;<BR/> memcpy(<BR/> message<BR/> , args->args[0]<BR/> , argStringLength>MYSQL_ERRMSG_SIZE<BR/> ? MYSQL_ERRMSG_SIZE<BR/> : argStringLength<BR/> );<BR/> } else {<BR/> memcpy(<BR/> message<BR/> , "Unspecified error raised"<BR/> , 25<BR/> );<BR/> } <BR/> return 1;<BR/>}<BR/><BR/>long long raise_error(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args,<BR/> char *is_null __attribute__((unused)),<BR/> char *error __attribute__((unused))) {<BR/> return 0;<BR/>}<BR/><BR/>int main(int argc, char **argv)<BR/>{<BR/> return 0;<BR/>}<BR/><BR/><BR/>compiled with:<BR/><BR/>gcc -shared -R/usr/sfw/lib/64 -m64 libmysqlraiseerror.c -o libmysqlraiseerror.so -I /opt/mysql/mysql/include -fPIC<BR/><BR/>I suspect the problem is "long long", because the machine already is 64bit... But I'm not sure - I just leaved C programming 15 years ago...<BR/><BR/>Thanks for your kind support.Unknownhttps://www.blogger.com/profile/07576463899050062035noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-58768547480362944422008-06-21T09:27:00.000+02:002008-06-21T09:27:00.000+02:00Oh Edson, sorry my comment does not make sense I g...Oh Edson, sorry my comment does not make sense I guess. I am wondering did you use exactly the code I have for raise_error_init()?<BR/><BR/>I don't have Solari64 bit running so I can't quickly check it out myself. But if you have the time, please send me the code, and I'll have a look. Maybe I see something you accidentally overlooked.<BR/><BR/>Thanks! <BR/><BR/>Rolandrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-91570354084248784822008-06-21T09:23:00.000+02:002008-06-21T09:23:00.000+02:00Hi Edson, Do you have the raise_error_init functio...Hi Edson, <BR/><BR/><BR/>Do you have the raise_error_init function defined?rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-76732017295320389542008-06-21T06:04:00.000+02:002008-06-21T06:04:00.000+02:00I'm trying to compile in OpenSolaris 2008.05, but ...I'm trying to compile in OpenSolaris 2008.05, but I always receive the error "ERROR 1123 (HY000): Can't initialize function 'raise_error';".<BR/>Do you have any tips/tricks?<BR/>I've compiled ir with no problems, and also the function loading is working ok.<BR/>I'm using OpenSolaris 64 bit.Unknownhttps://www.blogger.com/profile/07576463899050062035noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-56540598093769584822008-04-10T12:41:00.000+02:002008-04-10T12:41:00.000+02:00Thank you for your kind comments! Glad you could g...Thank you for your kind comments! Glad you could get it to work too :)rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-12717201835626028952008-04-10T09:54:00.000+02:002008-04-10T09:54:00.000+02:00Very good article!I use ubuntu and this is what I ...Very good article!<BR/>I use ubuntu and this is what I had to do to make it work. Install libmysql++-dev.<BR/><BR/>Replace winsock.h with string.h in the include statement. <BR/><BR/>As root, run the command:<BR/>gcc -shared libmysqlraiseerror.c -o libmysqlraiseerror.so -I /usr/include/mysql<BR/><BR/>Where libmysqlraiseerror.c is the C source file and libmysqlraiseerror.so is the output file name.<BR/><BR/>copy libmysqlraiseerror.so into the /usr/libAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-1132843015432992682005-11-24T15:36:00.000+01:002005-11-24T15:36:00.000+01:00Yes that's true, you can setup a context using use...Yes that's true, you can setup a context using user variables such as i did with @sqlerrm and @sqlcode, mimicking the Oracle stuff. <BR/><BR/>But what I meant by returning the error code from an UDF is this. Suppose I have BEGIN..END block, in which all kinds of stuff could go wrong: constraint violations, NO DATA FOUND, tables does not exist...you name it. Suppose these are all genuine error situations that i cannot compensate. Well, what I would want to do is something like this:<BR/><BR/>BEGIN<BR/> DECLARE EXIT HANDLER <BR/> FOR SQLEXCEPTION<BR/> INSERT <BR/> INTO ERROR_LOG(<BR/> proc<BR/> , block<BR/> , error_number<BR/> , error_message<BR/> , sql_state<BR/> ) VALUES (<BR/> current_proc()<BR/> , current_block()<BR/> , mysql_error_number()<BR/> , mysql_error_message()<BR/> , sql_state()<BR/> );<BR/> /*<BR/> here comes the stuff <BR/> that could go wrong<BR/> */<BR/>END;<BR/><BR/>Now, I can live without current_proc() and current_block(), but it would be nice to have at least one function that returns the errornumber or the sqlstate. And, from as far as I can see, you can't do that, not even with an UDF.<BR/><BR/>While it's true I can emulate something like this if the only errors would be raised by the UDF raise_error, I still need to be able to do the same for 'normal' errors.<BR/><BR/>Another thing we can't do is define a CONDITION for an arbitrary expression:<BR/><BR/>BEGIN<BR/> DECLARE v_error bool default false;<BR/> DECLARE myCondition <BR/> CONDITION FOR <BR/> v_error = TRUE;<BR/> DECLARE CONTINUE HANDLER <BR/> FOR myCondition <BR/> BEGIN<BR/> call compensate();<BR/> set v_error := FALSE;<BR/> END;<BR/> /* do stuff */<BR/>END;rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-1132792506190075772005-11-24T01:35:00.000+01:002005-11-24T01:35:00.000+01:00Hi anonymous!I don't know what you mean exactly, b...Hi anonymous!<BR/><BR/>I don't know what you mean exactly, but I'll make a guess.<BR/><BR/>According to <A HREF="http://rpbouman.blogspot.com/2005/11/sql-2003-drafts-for-download.html" REL="nofollow">SQL 2003</A> PSM (part 4), SIGNAL syntax is like this:<BR/><BR/><signal statement> ::=<BR/>SIGNAL <signal value> [ <set signal information> ]<BR/><BR/>So at least we will have to accept that we must use enclosing parenthesis. <BR/><BR/>The spec also sais this:<BR/><BR/><signal value> ::=<BR/><condition name><BR/>| <sqlstate value><BR/><BR/>well, we can pass whatever condition name or sqlstate to whatever UDF we can think of....there will never be a HANDLER clever enough to recognize that is should react specifically to our UDF raising an error.<BR/><BR/>To exen come close, you must be able to set the internal mysql error number from within the UDF; of course, for the same session that runs the UDF.<BR/><BR/>I looked into that, and as far as i can see this is not possible. I would like to be able too of course, also for the other way around: reporting the exact error in case of handelr for SQLExceptionrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-1132781754096199622005-11-23T22:35:00.000+01:002005-11-23T22:35:00.000+01:00Thank you.But why this can't be used to emulate SI...Thank you.<BR/><BR/>But why this can't be used to emulate SIGNAL?Anonymousnoreply@blogger.com