[Fsf-friends] Writing stored procedures for MySQL

[Biju G C]

While searching web I came across these projects and 
thought it may be interesting.


MySQL+LUA=MyLUA
Is a patch to MySQL that allow users to write procedures in a scripting 
language called LUA Allow to write a MySQL procedure in a scripting language, 
without rebuild the server.
http://www.fastflow.it/mylua/

PHP functions 4 MySQL
Is a user defined function for MySQL that allow users to write functions 
in PHP. It allow users to write a MySQL function in a scripting language, 
without rebuild the server.
http://www.fastflow.it/myphp/


DBTCP
DBTCP is a proxy server for ODBC connection.
Allow to make a query from a linux machine to a database in a Windows machine 
trough ODBC. Allow to connect all ODBC compliant databases from a Linux server
from shell scripts, perl via DBD and PHP.
http://www.fastflow.it/dbtcp/

Why a proxy for odbc connections ?
The main reason for using dbtcp is the lack of support for a few odbc 
databases under Linux and the fact that you can use multiple dbtcp clients 
on a pay-per-odbc-connection database management system.



Example: COUNT.LUA - Append a column to the result with the row number

linux: $ mysql mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.32-gamma-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer

mysql> select user from user procedure lua("/usr/src/mysql/sql/count.lua");
+-----------+---------+
| user.User | Counter |
+-----------+---------+
|           | 1       |
| root      | 2       |
|           | 3       |
| root      | 4       |
+-----------+---------+
4 rows in set (0.01 sec)


Source

-- Called when the procedure is initialized
function init ()
   ROWS.init=1
end

-- Modifica un campo del risultato
-- Change a field definition of the result set
function set_field ( num, name, type, length )

   if ( FIELDS[num] == nil ) then
      FIELDS[num]={}
   end

   FIELDS[num].name=name
   FIELDS[num].type=type
   FIELDS[num].length=length
end

-- Richiamata per cambiare le definizioni delle colonne
-- Called to change the columns definition for the result set
function change_columns ()
   set_field ( FIELDS.num, "Counter", "STRING", 10 )
   FIELDS.num=FIELDS.num+1
end

-- Richiamata appena prima di inviare il set di risultati al client
-- Called just before sending the result set to the client
function end_of_records ()
local idx, new_tab, idx2
local i,v

   new_tab={}
   -- Crea la struttura vuota per i valori
   idx=0
   while ( idx&ltROWS.num ) do

      ROWS[idx][FIELDS.num-1]=idx+1;

      idx=idx+1

   end

end




__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com