Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
MySQL stored procedures (Read 13631 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
MySQL stored procedures
28.10.08 at 12:53:54
 
MySQL stored procedures
 
It is possible to use stored procedures in MySQL. Stored procedures are implemented from MySQL version 5.0.
 
To create a stored procedure/function you can use the CREATE PROCEDURE and CREATE FUNCTION commands.  
Example:
CREATE PROCEDURE mysqlproc() SELECT 'Hello World';
 
A stored procedures is executed using the CALL statement. Example:
CALL mysqlproc()
 
A stored function can just be used as any other function in a SQL statement.
 
To delete a function or procedure use the DROP PROCEDURE or DROP FUNCTION statements.
 
See also:
MySQL stored procedures
http://www.databasejournal.com/features/mysql/article.php/3525581
Stored procedures Reference Manual
http://dev.mysql.com/doc/refman/5.0/en/stored-routines.html
Back to top
 
« Last Edit: 28.10.08 at 12:58:52 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Downloading and installing MySQL 5.1
Reply #1 - 02.01.09 at 22:05:08
 
Downloading and installing MySQL 5.1
 
MySQL can be downloaded from www.mysql.com.
 
In the developer zone, a link to community server can be followed. In the community server version 5.1 a link to a windows installer can be found.  
 
I chose the Windows ZIP/Setup.EXE (x86) 5.1.30 103.5M.
 
It installed without problems on Windows XP.
 
After installation I ran the following tests to see whether the installation was successful.
 
From the mysql bin directory you can connect to the database using the command:
mysql -u username -p
 
I connected as root: mysql -u root -p
 
First I setup a new user: create user myuser identified by 'mypassword';
 
I gave the user all privileges using: grant all on *.* to myuser;
 
Connecting to the mysql database server as myuser: mysql -u myuser -p
 
Then I connected to the test database that was also installed: connect test;
 
In created a new table: create table mytable (a numeric);
 
All seems to work fine!
Back to top
 
« Last Edit: 02.01.09 at 22:08:32 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Driver for MySQL 5.1
Reply #2 - 02.01.09 at 22:20:20
 
Driver for MySQL 5.1
 
A driver for MySQL 5.1 can be downloaded from www.mysql.com as well.
 
In the Developer zone select Connectors. In the list Connector/ODBC you find the 5.1 ODBC driver.
 
I downloaded the msi installer and did a complete install of the driver.
Back to top
 
« Last Edit: 02.01.09 at 22:23:09 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
SQL*XL connections to MySQL 5.1
Reply #3 - 04.01.09 at 11:17:51
 
SQL*XL connections to MySQL 5.1
 
Support for MySQL 5.1 was built into the SQL*XL connection wizard. See the following topic for more details:
 
Connect Excel to MySQL 5.1  
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1231064012/0#0
Why not give SQL*XL a try today.
 
SQL*XL: SQL Excel software

Download SQL*XL now!


Back to top
 
« Last Edit: 08.01.09 at 10:59:31 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Create a MySQL stored procedure
Reply #4 - 04.01.09 at 13:06:09
 
Create a MySQL stored procedure
 
To create a MySQL stored procedure the CREATE PROCEDURE command can be used.
 
Example:
create procedure mysqlproc() select 'hello world';
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Execute a MySQL stored procedure in Excel
Reply #5 - 04.01.09 at 13:07:17
 
Execute a MySQL stored procedure
 
To call a MySQL stored procedure in Excel you can use SQL*XL's call statement.
 
Example:
call mysqlproc();
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Delete a MySQL stored procedure
Reply #6 - 04.01.09 at 13:08:15
 
Delete a MySQL stored procedure
 
To remove a MySQL stored procedure you can use the SQL*XL DROP PROCEDURE command.
 
Example:
drop procedure mysqlproc;
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
ronaldyoung
YaBB Newbies
*


I Love SQL*XL

Posts: 0
Re: MySQL stored procedures
Reply #7 - 06.01.09 at 02:53:28
 
     
I would like to know of such create procedure with several sentences inside.
 
The example have only one sentence.
 
Please i hope your answer.
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: MySQL stored procedures
Reply #8 - 08.01.09 at 13:07:37
 
Of course you can create procedures with more than one line. It is hardly useful to have one just with a select statement. Actually, it is my opinion that you should not stored procedures to return records. But that is a different discussion.
 
You can use the following example procedure if you want to create a procedure with multiple lines and statements:
 

create procedure myproc()
begin
  update mytable
  set col_a = 'hi'
  ,     col_b = 4;
  ins
ert into other_table
  (a, b, c, d)
  values
  (1, 2, 3, 4);
end;

 
You can run this statement through SQL*XL's SQL dialog without any problems. Please do note that you cannot use a create or replace procedure statement as you can do in Oracle. Once you have created the procedure you cannot modify it. You need to run a drop procedure statement before you can run the create procedure statement again.
 
In SQL*XL you do not need to do anything special. It will detect the end of the procedure without problems. So you can just end each statement in the procedure with a semi colon.  
 
If you create the procedure in MySQL itself you may need to set the delimiter to another character than a semi colon. Otherwise MySQL thinks the end of a statement is also the end of your procedure. Again, no such problems with SQL*XL.
Back to top
 
« Last Edit: 08.01.09 at 13:10:55 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1