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: 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]
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]
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]
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]
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]
