Re: Stored function problems (Was: UDF failure)
Nic, At the end of the func, your sproc delimiter // needs to come before restoration of the semi-colon as delimiter, thus: DELIMITER // CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) RETURN CONCAT('Hello, ',s,'!'); // DELIMITER ; PB - Nic Stevens wrote: Hi, Still having difficulty with stored functions. I downloaded and installed MySQL 5.0.7 and I *still* cannot get stored functions to work. I used the example for hello from section 19.2.1 of the online documentation for MySQL and the example code doesnt work for me either. Below are the versions from, respectively, the MySQL cli, the MySQL server and my Linux and following that is a transcript from the mysql cli trying to create the hello function. mysql Ver 14.11 Distrib 5.0.7-beta, for pc-linux-gnu (i686) using readline 4.3 mysqld Ver 5.0.7-beta-standard for pc-linux-gnu on i686 (MySQL Community Edition - Standard (GPL) Linux colinux 2.6.10-co-0.6.2 #5 Sat Feb 5 10:19:16 IST 2005 i686 i686 i386 GNU/Linux Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30 to server version: 5.0.7-beta-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> DELIMITER // mysql> mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) -> RETURN CONCAT('Hello, ',s,'!'); -> -> DELIMITER ; -> // ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RETURN CONCAT('Hello, ',s,'!'); DELIMITER' at line 2 No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.10/43 - Release Date: 7/6/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.10/43 - Release Date: 7/6/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored function problems (Was: UDF failure)
At 11:41 -0700 7/7/05, Nic Stevens wrote: Hi, Still having difficulty with stored functions. I downloaded and installed MySQL 5.0.7 and I *still* cannot get stored functions to work. I used the example for hello from section 19.2.1 of the online documentation for MySQL and the example code doesnt work for me either. Below are the versions from, respectively, the MySQL cli, the MySQL server and my Linux and following that is a transcript from the mysql cli trying to create the hello function. mysql Ver 14.11 Distrib 5.0.7-beta, for pc-linux-gnu (i686) using readline 4.3 mysqld Ver 5.0.7-beta-standard for pc-linux-gnu on i686 (MySQL Community Edition - Standard (GPL) Linux colinux 2.6.10-co-0.6.2 #5 Sat Feb 5 10:19:16 IST 2005 i686 i686 i386 GNU/Linux Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30 to server version: 5.0.7-beta-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> DELIMITER // mysql> mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) -> RETURN CONCAT('Hello, ',s,'!'); -> -> DELIMITER ; -> // The example in the manual has the "delimiter ;" line following the // line, not preceding it. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RETURN CONCAT('Hello, ',s,'!'); DELIMITER' at line 2 -- Nic Stevens - [EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored function problems (Was: UDF failure)
Hi, Still having difficulty with stored functions. I downloaded and installed MySQL 5.0.7 and I *still* cannot get stored functions to work. I used the example for hello from section 19.2.1 of the online documentation for MySQL and the example code doesnt work for me either. Below are the versions from, respectively, the MySQL cli, the MySQL server and my Linux and following that is a transcript from the mysql cli trying to create the hello function. mysql Ver 14.11 Distrib 5.0.7-beta, for pc-linux-gnu (i686) using readline 4.3 mysqld Ver 5.0.7-beta-standard for pc-linux-gnu on i686 (MySQL Community Edition - Standard (GPL) Linux colinux 2.6.10-co-0.6.2 #5 Sat Feb 5 10:19:16 IST 2005 i686 i686 i386 GNU/Linux Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30 to server version: 5.0.7-beta-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> DELIMITER // mysql> mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) -> RETURN CONCAT('Hello, ',s,'!'); -> -> DELIMITER ; -> // ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RETURN CONCAT('Hello, ',s,'!'); DELIMITER' at line 2 -- Nic Stevens - [EMAIL PROTECTED]
Re: UDF failure
Looks like you're confusing Prepared Statements with Functions/Stored Procedures. You don't compile a function, and you're using 4.1, you need Mysql 5. (specifically 5.0.3 I think, you'd have to look that up, but you would want 5.0.7 anyway. Maybe I have that confused with triggers, cant remember.) Prepared Statements are C code using the C API. You have SQL Language code, that looks correct, but you're implementing it incorrectly. (with the wrong version) Functions are not compiled, like you are doing with gcc. Prepared Statements: http://dev.mysql.com/doc/mysql/en/c-api-prepared-statements.html Functions: http://dev.mysql.com/doc/mysql/en/stored-procedures.html Hope that helps. Greg On 7/6/05, Gleb Paharenko <[EMAIL PROTECTED]> wrote: > Hello. > > > > >When attempting to load a new function with the mysql cli client the > > >client > > >complains that the server has gone away and reconnects with connection > > > > Check MySQL error log, server could die while loading the UDF. > > > > Stored procedures appeared only in 5 version. This works fine for me on 5.0.7. > > > > mysql> create function hello (s char(20)) returns char(50) RETURN > > CONCAT('Hello, ',s,'!');// > > Query OK, 0 rows affected (0.00 sec) > > > > > > Nic Stevens <[EMAIL PROTECTED]> wrote: > > >Hello, > > > > > >I'm trying to build a UDF for MySQL 4.1.12. I'm running on Linux > > >(FedoraFC1). My UDF function seemed to blow up the server so I went to > > >the > > >source and tried to build udf_example.cc. > > > > > >Using the precompiled binaries for Linux and using the source > > >distribution > > >for the same version (4.1.12) I was unable to build udf_example.cc as a > > >shared object (I followed the instructions in the source -- they didnt > > >work > > >so I used what seemed to make the most sense for building my object). > > >When > > >building the shared object the compiler spits out a warning from > > >asm/atomic.h (#warning Using kernel header in userland program. BAD!) > > > > > >I'm using gcc version 3.3.2 20031022 (Red Hat Linux 3.3.2-1) on a > > >Fedore > > >Core 1 machine. I built the shared object with gcc -I/usr/include/mysql > > >-shared -o udf_example.so udf_example.cc > > > > > >When attempting to load a new function with the mysql cli client the > > >client > > >complains that the server has gone away and reconnects with connection > > >id of > > >1. Nothing is listed in mysql.funcs either. > > > > > >There needs to be better documentation of the process for building > > >UDF's. > > > > > >The code I need to run can be run nicely by MySQL but I can't declare a > > >stored function as documented. The stored function is fairly simple: > > > > > >DELIMITER // > > > > > >CREATE FUNCTION distance(z1 VARCHAR(10), z2 VARCHAR(10)) RETURNS REAL > > >BEGIN > > >DECLARE x REAL; > > >DECLARE y REAL; > > >DECALRE miles REAL; > > >SET x = 69.1*(lat - lat2); > > >SET y = 69.1*(lon1 - lon2) * COS(lon1*57.3); > > >SET miles = SQRT(x*x+y*y); > > >RETURN miles; > > >END > > >// > > > > > >The mysql client spits back : > > >ERROR 1064 (42000): You have an error in your SQL syntax; check the > > >manual > > >that corresponds to your MySQL server version for the right syntax to > > >use > > >near '(z1 VARCHAR(10), z2 VARCHAR(10)) RETURNS REAL > > > > > >Using the example given in the documentation I get: > > > > > >mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) > > >-> RETURN CONCAT('Hello, ',s,'!'); > > >ERROR 1064 (42000): You have an error in your SQL syntax; check the > > >manual > > >that corresponds to your MySQL server version for the right syntax to > > >use > > >near '(s CHAR(20)) RETURNS CHAR(50) > > >RETURN CONCAT('Hello, ',s,'!')' at line 1 > > > > > > > > >DELIMITER ; > > >// > > > > > >Can someone elucidate the mystery of either stored functions or UDF's? > > > > > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.NET http://www.ensita.net/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Gleb Paharenko > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ><___/ www.mysql.com > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Greg Fischer 1st Byte Solutions http://www.1stbyte.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UDF failure
Hello. >When attempting to load a new function with the mysql cli client the >client >complains that the server has gone away and reconnects with connection Check MySQL error log, server could die while loading the UDF. Stored procedures appeared only in 5 version. This works fine for me on 5.0.7. mysql> create function hello (s char(20)) returns char(50) RETURN CONCAT('Hello, ',s,'!');// Query OK, 0 rows affected (0.00 sec) Nic Stevens <[EMAIL PROTECTED]> wrote: >Hello, > >I'm trying to build a UDF for MySQL 4.1.12. I'm running on Linux >(FedoraFC1). My UDF function seemed to blow up the server so I went to >the >source and tried to build udf_example.cc. > >Using the precompiled binaries for Linux and using the source >distribution >for the same version (4.1.12) I was unable to build udf_example.cc as a >shared object (I followed the instructions in the source -- they didnt >work >so I used what seemed to make the most sense for building my object). >When >building the shared object the compiler spits out a warning from >asm/atomic.h (#warning Using kernel header in userland program. BAD!) > >I'm using gcc version 3.3.2 20031022 (Red Hat Linux 3.3.2-1) on a >Fedore >Core 1 machine. I built the shared object with gcc -I/usr/include/mysql >-shared -o udf_example.so udf_example.cc > >When attempting to load a new function with the mysql cli client the >client >complains that the server has gone away and reconnects with connection >id of >1. Nothing is listed in mysql.funcs either. > >There needs to be better documentation of the process for building >UDF's. > >The code I need to run can be run nicely by MySQL but I can't declare a >stored function as documented. The stored function is fairly simple: > >DELIMITER // > >CREATE FUNCTION distance(z1 VARCHAR(10), z2 VARCHAR(10)) RETURNS REAL >BEGIN >DECLARE x REAL; >DECLARE y REAL; >DECALRE miles REAL; >SET x = 69.1*(lat - lat2); >SET y = 69.1*(lon1 - lon2) * COS(lon1*57.3); >SET miles = SQRT(x*x+y*y); >RETURN miles; >END >// > >The mysql client spits back : >ERROR 1064 (42000): You have an error in your SQL syntax; check the >manual >that corresponds to your MySQL server version for the right syntax to >use >near '(z1 VARCHAR(10), z2 VARCHAR(10)) RETURNS REAL > >Using the example given in the documentation I get: > >mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) >-> RETURN CONCAT('Hello, ',s,'!'); >ERROR 1064 (42000): You have an error in your SQL syntax; check the >manual >that corresponds to your MySQL server version for the right syntax to >use >near '(s CHAR(20)) RETURNS CHAR(50) >RETURN CONCAT('Hello, ',s,'!')' at line 1 > > >DELIMITER ; >// > >Can someone elucidate the mystery of either stored functions or UDF's? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UDF failure
Hello, I'm trying to build a UDF for MySQL 4.1.12. I'm running on Linux (FedoraFC1). My UDF function seemed to blow up the server so I went to the source and tried to build udf_example.cc. Using the precompiled binaries for Linux and using the source distribution for the same version (4.1.12) I was unable to build udf_example.cc as a shared object (I followed the instructions in the source -- they didnt work so I used what seemed to make the most sense for building my object). When building the shared object the compiler spits out a warning from asm/atomic.h (#warning Using kernel header in userland program. BAD!) I'm using gcc version 3.3.2 20031022 (Red Hat Linux 3.3.2-1) on a Fedore Core 1 machine. I built the shared object with gcc -I/usr/include/mysql -shared -o udf_example.so udf_example.cc When attempting to load a new function with the mysql cli client the client complains that the server has gone away and reconnects with connection id of 1. Nothing is listed in mysql.funcs either. There needs to be better documentation of the process for building UDF's. The code I need to run can be run nicely by MySQL but I can't declare a stored function as documented. The stored function is fairly simple: DELIMITER // CREATE FUNCTION distance(z1 VARCHAR(10), z2 VARCHAR(10)) RETURNS REAL BEGIN DECLARE x REAL; DECLARE y REAL; DECALRE miles REAL; SET x = 69.1*(lat - lat2); SET y = 69.1*(lon1 - lon2) * COS(lon1*57.3); SET miles = SQRT(x*x+y*y); RETURN miles; END // The mysql client spits back : ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(z1 VARCHAR(10), z2 VARCHAR(10)) RETURNS REAL Using the example given in the documentation I get: mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) -> RETURN CONCAT('Hello, ',s,'!'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(s CHAR(20)) RETURNS CHAR(50) RETURN CONCAT('Hello, ',s,'!')' at line 1 DELIMITER ; // Can someone elucidate the mystery of either stored functions or UDF's? Thanks, in advance, Nic -- Nic Stevens - [EMAIL PROTECTED]