Queries inside UDF

[sguazt sguazt]

Hi folks!
(I hope this is the right list ... if not please tell me where I can submit 
this post)

I would like to create a MySQL UDF (i.e. User Defined Function) that embeds 
a query; for instance, suppose the UDF is named foobar:

mysql> SELECT foobar();
When foobar function receives the control from the MySQL, it attempts to 
create a query. To do so it has to connect to DB (since it seems there's no 
way to access to current DB connection from a UDF function -- at least I 
did'nt find it any way). So the flow of execution is:
SELECT foobar()
1 --> Call foobar
2 -----> init MySQL
3 -----> connect to MySQL
4 -----> create/execute query
5 -----> get query result
6 -----> close MySQL connection
7 -----> return result

After doing step 3 I get the error: "Can't connect to MySQL server on 
'localhost' (111)"

One may argue that error is due to the host permission ...
However, executing the same code used inside the foobar function definition 
outside the UDF function (i.e. as a separate executable) all is OK!
So anyone know if is it possible do a query inside a UDF? ... And if it is, 
how?!

For everyone who would make a try I've written a C-file very similar to my 
...
After compiling the file remeber to do (from the MySQL console):

CREATE FUNCTION foobar RETURNS INTEGER SONAME 'libfoobar.so';
where 'libfoobar.so' is the shared file created from the C-file.
Thanks in advance to everyone!!!!
-- Marco
--- BEGIN foobar.c ---
#ifdef __WIN__
typedef unsigned __int64 ulonglong; /* Microsofts 64 bit types */
typedef __int64 longlong;
#else
typedef unsigned long long ulonglong;
typedef long long longlong;
#endif /*__WIN__*/
#include <mysql.h>
#include <stdio.h>
#include <string.h>
#ifdef __cpluscplus
extern "C" {
#endif
my_bool foobar_init( UDF_INIT* initid, UDF_ARGS* args, char* message );
void foobar_deinit( UDF_INIT* initid );
longlong foobar( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* 
error );

my_bool foobar_init( UDF_INIT* initid, UDF_ARGS* args, char* message )
{
 /* empty */
}
void foobar_deinit( UDF_INIT* initid )
{
 /* empty */
}
longlong foobar( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* 
error )
{
 MYSQL* mysql = NULL;
 MYSQL_RES* qryResult = NULL;
 const char* query = "SELECT COUNT(*) FROM tblfoobar";
 longlong retval = 0;

 mysql = mysql_init( mysql );
 if ( !mysql ) {
   fprintf( stderr, "Error on Init: %s\n", mysql_error( mysql );
   *error = 1;
   return retval;
 }
 if ( !mysql_real_connect( mysql, "host" "user" "password", "database", 0, 
NULL, 0 ) ) {
   fprintf( stderr, "Error on Connect: %s\n", mysql_error( mysql );
   *error = 1;
   return retval;
 }

 if ( mysql_real_query( mysql, query, strlen( query ) ) ) {
   fprintf( stderr, "Error on Query: %s\n", mysql_error( mysql );
   *error = 1;
   return retval;
 }
 if ( ( qryResult = mysql_store_result( mysql ) ) ) {
   MYSQL_ROW row;
   unsigned int num_fields;
   unsigned int i;
   num_fields = mysql_num_fields( qryResult );
   while ( ( row = mysql_fetch_row( qryResult ) ) ) {
     unsigned long *lengths = NULL;
     lengths = mysql_fetch_lengths( qryResult );
     for( i = 0; i < num_fields; i++ ) {
       fprintf( stderr, "[%.*s] ", (int) lengths[i], row[i] ? row[i] : 
"NULL");
       retval = strtoll( row[i] );
     }
     fprintf( stderr, "\n" );
   }

   mysql_free_result( qryResult );
 }
 else {
   if ( !mysql_field_count( mysql ) ) {
     fprintf( stderr, "Error on Storing Query Result: %s\n", mysql_error( 
mysql );
     *error = 1;
     return retval;
   }
 }

 mysql_close( mysql );
 return retval;
}
#ifdef __cpluscplus
}
#endif
--- END foobar.c ---
_________________________________________________________________
Blocca le pop-up pubblicitarie con MSN Toolbar! http://toolbar.msn.it/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]