Re: Queries inside UDF
From: Philippe Poelvoorde [EMAIL PROTECTED] Reply-To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com Subject: Re: Queries inside UDF Date: Mon, 21 Mar 2005 11:05:39 + sguazt sguazt wrote: Hi! From: Philippe Poelvoorde [EMAIL PROTECTED] Reply-To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com To: mysql@lists.mysql.com Subject: Re: Queries inside UDF Date: Thu, 17 Mar 2005 08:22:46 + .. You can actually access a DB within a UDF, but you should do the same than when you're using the C api of mysql. So you need a way to get database name, user/passwd and port without user-input, and do mysql_init, mysql_connect, mysql_query, and so on, like when you are accessing that database using a 'external' C program. Also bear in mind that it should be thread-safe ;) a way to extend udf, is ... stored procedure ? Yes I did it ... look my first post (http://lists.mysql.com/mysql/181361) There you can find and example of a C-code I used to write my UDF ... but when using the UDF inside MySQL I've got Can't connect to MySQL server on 'localhost' (111) you should check the permission. Instead compiling the C-code as stand-alone program (removing the xxx_init/xxx_deinit functions and adding a main) all is OK, the query is executed and the program normally exits. did you execute it on your DB server ? (not on your WS) yes I did. The strange fact is just the stand alone program works and the library doesn't even if the connection parameters (host, port, user, password) are the same. The DBMS is on my machine, so to connect to it I use: host: localhost (I've also tried 127.0.0.1, NULL, ...) port: 3306 (I've also tried 0) user: root password: NULL (I've also tried ) -- Marco _ Ricerche online più semplici e veloci 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]
Re: Queries inside UDF
Hi! From: Philippe Poelvoorde [EMAIL PROTECTED] Reply-To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com To: mysql@lists.mysql.com Subject: Re: Queries inside UDF Date: Thu, 17 Mar 2005 08:22:46 + ... You can actually access a DB within a UDF, but you should do the same than when you're using the C api of mysql. So you need a way to get database name, user/passwd and port without user-input, and do mysql_init, mysql_connect, mysql_query, and so on, like when you are accessing that database using a 'external' C program. Also bear in mind that it should be thread-safe ;) a way to extend udf, is ... stored procedure ? Yes I did it ... look my first post (http://lists.mysql.com/mysql/181361) There you can find and example of a C-code I used to write my UDF ... but when using the UDF inside MySQL I've got Can't connect to MySQL server on 'localhost' (111) Instead compiling the C-code as stand-alone program (removing the xxx_init/xxx_deinit functions and adding a main) all is OK, the query is executed and the program normally exits. I can't use stored procedure because the target DB is the 4.0.21 Currently I'm working on MySQL 4.1.10a on my devel station ... but the production station is 4.0.21 (all installed as RPM x86). So please look at the C-code and tell me if it's wrong ... as alternative you can post me a working example of UDF containing MySQL query execution. Thanks! -- Marco -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Personalizza MSN Messenger con sfondi e fotografie! http://www.ilovemessenger.msn.it/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Queries inside UDF
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]
RE: Queries inside UDF
Hi!
From: Tom Crimmins [EMAIL PROTECTED]
To: sguazt sguazt [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: RE: Queries inside UDF
Date: Wed, 16 Mar 2005 10:09:16 -0600
...
Can you explain exactly what you are using this for? What benefit does this
provide over just executing the query?
You can either execute SELECT foobar() or SELECT COUNT(*) FROM tblfoobar,
and
you don't have to do anything to make the latter work. Creating the UDF
just
seems like a lot of extra work. Maybe this is my ignorance, but I don't see
much use for executing a query within a UDF esspecially if you are using
4.1
with subqueries.
ok sorry if I submitted a stupid exampe :P
but I wanted to keep the stuff simple.
My real case is a little more complicated ...
First of all, unfornately I've to use MySQL 4.0.21.
The purpose of creating a UDF, in my case, is doing something that in my
opinion cannot be done in a query.
For example, if you have to implement in SQL the following pseudo-code:
start = a unix timestamp;
duration = 100;
stop = null;
increment = 0;
while ( true ) {
start2 = 0;
stop2 = 0;
stop = start + ( duration - increment );
rs = doQuery( SELECT start,stop FROM t1 INNER JOIN t2 ON t1.c1=t2.c1
WHERE t1.start= + start + AND t1.stop= + stop );
if ( rs == null ) {
delta = 0;
start2 = rs[0]['start'];
stop2 = rs[0]['stop'];
if ( start start2 ) {
delta += start2 - start;
}
increment += delta;
start = stop2
}
else {
break;
}
}
return stop;
what do you write?
The main problem is that you can establish a-priori when you stop the main
while ... :(
I thought to use a temporary table but the problem remains ...
Furthermore that code produces an info that is to be used by another (more
complex) query.
So the only solutions are:
1. use a high-level language (C,perl,Java,PHP) and implement the entire
logic (not only the code above) in that language (but this seems to be too
slow ... I did it before exploring the UDF world ;) )
2. try to write a UDF that implement the code above; so you create a UDF,
e.g. named foobar, you can use inside other query: SELECT ...,foobar( start,
duration ) AS stop FROM a very long JOIN ...
Any idea?
bye!!!
-- Marco
--
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
_
Ricerche online più semplici e veloci 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]
RE: Queries inside UDF
From: [EMAIL PROTECTED]
To: sguazt sguazt [EMAIL PROTECTED]
CC: mysql@lists.mysql.com,[EMAIL PROTECTED]
Subject: RE: Queries inside UDF
Date: Wed, 16 Mar 2005 12:37:59 -0500
I can almost follow the logic of your pseudocode. Can you explain what
it is you are trying to compute? It seems as though you are looking for
(or computing) the ending value to some sort of time span but I just can't
quite understand how you are getting there. I can see that you are using
some kind of varying-width (you change your increment value), sliding time
window (you move your start and stop times) but I just don't understand
why you stop when you DO get a result (the else clause of (rs==null)) when
you needed results for the previous loops (I think you meant to stop when
you didn't get a result)... And the significance of the data you finally
found escapes me...
yes you got it ... I wrote an erroneous stopping condition:
The real stopping condition is (as you told me):
if ( ts != null ) {
}
else {
break; // STOP CONDITION
}
Well I'm trying to explain what I want to do ...
I have a starting date of an event (see variable start) and it's virtual
duration (see variable duration); the word virtual will be clearer later.
For instance you have a table of research projects with the starting date
and their related duration.
In the simple vanilla world, the due date (see variable stop) would be:
start + duration (consider start as a UNIX timestamp and duration expressed
in seconds).
But in real world you've to take care about a calendar. Each project has a
project manager associated.
Each project manager is assigned a calendar because can follow different
holydays (in fact project manager for project1 could be American; project
manager for project2 could be European; and so on ...)
So for each project you've to calculate the real due date according to the
calendar associated to the project manager.
The calendar I have is a event-based calendar (that is, the calendar has
associated a series of calendar event representing the holydays).
So the pseudo-code:
int calculateDueDate( projectManager, wTsStart, duration )
{
wTsStop = 0; // Working Day stop timestamp
increment = 0; // increment of duration
// calculate the Working Day stop timestamp according to the
patching duration
while ( true ) do
nwTsStart = 0; // Non-Working Day start timestamp
nwTsStop = 0; // Non-Working Day stop timestamp
wTsStop = wTsStart + (duration increment);
// retrieve the intersecting interval for the interval
[wTsStart, wTsStop]
rs = doQuery(
SELECT CE.tsstart,CE.tsstop
+ FROM calendar C INNER JOIN calendarevent CE ON
C.id=CE.calendar
+ WHERE C.owner= + projectManager + AND (( +
wTsStart + =CE.tsstart AND + wTsStop + CE.tsstart) OR ( + wTsStart +
CE.tsstart AND + wTsStart + CE.tsstop))
+ ORDER BY CE.tsstart, CE.tsstop
);
if ( rs != null ) then
delta = 0; // offset between the start of intervals
nwTsStart = rs[0]['tsstart'];
nwTsStop = rs[0]['tsstop'];
if ( wTsStart nwTsStart ) then
delta += nwTsStart - wTsStart;
endif
increment += delta;
wTsStart = nwTsStop;
else
break;
endif
endwhile
return wTsStop;
}
the returned value is just the real due date.
The above code have to be repeated for each project manager:
SELECT , calculateDueDate( P.manager, P.startDate, P.duration ) FROM
project P INNER JOIN ...;
bye!!
-- Marco
Maybe if I understood the nature of your data better I wouldn't be so
confused. And, if you would be so kind, can you explain why you are
scrolling through the data with a shifting time window to reach some kind
of non-record (I assume)? Is there no other way to reach the same
information? Can you not just use the last record and work from there?
It's hard to get help for a complex data issue like yours unless you give
us enough information so that we understand your both your data and your
needs. Any one of us may see a path to your solution that you haven't
thought of but we can't do that unless we understand both where you are
starting from (your base data structures) and where you need to be (the
data you require). It's going to be very hard to confuse all of us. The
more information you provide, the more complete the help will be.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
_
Scopri il nuovo MSN Htomail - 10MB di allegati
http://www.msn.it/hotmail/minisite_10
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
RE: Queries inside UDF
Thanks for answering! Your solution would be right if I can modify the database (and I have no control on software that populate tables). Unfortunately I can do only queries on that db; so the due date has to be recalculated every time I want to perform the main report (that use the ending date). An alternative way is to created a snapshot of main db with the added fields, but the report may contains not so updated information (e.g. if the snapshot is synch one time at day in theory the report may display info older up to a day). This violates the requirements of my boss who wants a very updated data ... :'-| So the only possible ways are: * do that via high-level language, like C, Perl, Java; I've done this but is very slow ... especially the client running the code is remote (with respect to DB server) ... so in addition to the load generated by the number of queries there's the load of network communication: (Send Queries + Receive Results) * N (where N 1 in general); * do that via SQL+UDF; the advantage of this solution is the load of computation is on DB server ... the load due to the network communication a very minimal: Send Query One Time + Receive Result When I tried UDF I'm said WOW this could be the solution to my problems Unfortunaly, I found out there's no way (at least for me) to access to current DBMS thread or connection; i.e.: SELECT foobar( ... ) FROM ...; I believed from foobar FUNCTION there would have been a way (through parameters passed by MySQL, like UDF_INIT* initid) to access to current DBMS connection object. I this is true ... what a pity! I hope MySQL staff will think to extend UDF to include this feature unless there're hidden trickly I don't see. -- Marco From: [EMAIL PROTECTED] To: sguazt sguazt [EMAIL PROTECTED] CC: mysql@lists.mysql.com,[EMAIL PROTECTED] Subject: RE: Queries inside UDF Date: Wed, 16 Mar 2005 15:20:09 -0500 Marco, I think I understand why you might want the end date of your projects to be dynamically calculated (assuming that's why you want this calculation to be a UDF?). You would be able to change a starting date, the project's duration, or add or delete a holiday and your ending dates would automatically adjust. However, as you have found out, it is not a simple calculation to perform. Based on the starting date and some initial duration, any number of non-working days (which includes both holidays and weekends) may occur within that time span. So you adjust your ending date which may now also include more non-working days, so you repeat the process until you achieve the duration you wanted and have accurately accounted for all non-working days since the startdate of the project. I think your pseudo code fits that model of calculating the finishdate rather well. I agree with your assessment to do the finish date calculation in code, not SQL. However I disagree with the design choice to make the finish date a dynamic value (if that's what you did, sorry if you didn't). You should only need to compute the finish date's value under the following conditions: a) initial record creation b) if you change the start date c) if you change the duration d) if a non-workday was added to or removed from the calendar and that day fell within the computed time span of the project. It makes better sense to me to detect one of those 4 conditions and UPDATE a field that holds the ending date by using your algorithm while performing one of those actions. That way a query like SELECT name, startdate, enddate FROM projects WHERE startdate = '2005-07-01' AND enddate '2005-07-01'; doesn't need to compute the value for enddate twice (once for the result and once for the WHERE test) for every project just to detect any projects that will be active on July 1, 2005. In fact, that query may even be able to use an index if you stored the value in a field while if you kept it only as a calculated value, that would not be possible. So... have we answered your original question or did we just go WAAY off track? Shawn Green Database Administrator Unimin Corporation - Spruce Pine sguazt sguazt [EMAIL PROTECTED] wrote on 03/16/2005 01:25:09 PM: From: [EMAIL PROTECTED] To: sguazt sguazt [EMAIL PROTECTED] CC: mysql@lists.mysql.com,[EMAIL PROTECTED] Subject: RE: Queries inside UDF Date: Wed, 16 Mar 2005 12:37:59 -0500 I can almost follow the logic of your pseudocode. Can you explain what it is you are trying to compute? It seems as though you are looking for (or computing) the ending value to some sort of time span but I just can't quite understand how you are getting there. I can see that you are using some kind of varying-width (you change your increment value), sliding time window (you move your start and stop times) but I just don't understand why you stop when you DO get a result (the else clause of (rs==null)) when you needed results for the previous loops (I think you meant to stop when you didn't get a result
