Okay, so I've been trying this since a long time and it wasn't working ... but I got some help and now it's working, thanks to Roland Bouman.

The end goal is as such:

Consider the tables inbox, ads, outbox. Everytime text is inserted into the inbox table a function should be called which analyses the inserted text and performs one of two operations:
1] Either the text should be inserted into the ads table, with correct specifications about what kind of ad it is.
2] The text should still be analysed and on the basis of that analysis a set of ads should be extracted from the ads table and this set should be pushed into the outbox table, to be sent out by an SMS gateway.

Now unfortunately for me, being the amateur that I am, I painted myself into a corner by selecting FLEX to generate the lexical analyser or text analyser. Never select technology without understanding the true meaning of flexibility. I would never have spent so much time against a wall, had I been able to re-write the (couple of thousand) lines of code the analyser is in, into MySQL code.

In technology terms I had to somehow ensure that a MySQL trigger would call the analyser code which would call further MySQL code.
1] MySQL Trigger - MySQL.
2] Analyser - C.
3] Further db processing - MySQL/C?

So the question arose how to go about accomplishing said goals.

These User-Defined-Functions allow you to install C functions as MySQL functions. It's really quite cool, kudos to the guys at MySQL for this beautiful extension mechanism.

Now the problem further arose, how could I pass on the results of the text analysis to MySQL code? The answer is obvious, write the MySQL db processing in the analyser itself! The UDF's allow you to return only pointers to strings, integers, doubles. No pointers to structures, unless I used some string typecasting...

Problem that will "kill-you-till-you-die"
Now the problem that arose is that, everytime the MySQL C API would try to spawn a new connection it would return a "Cannot connect to server(111)" error.

I pegged it as "the client could not spawn a new connection from within the connection it was in".

According to the manual, the seventh parameter to the mysql_real_connect() unix_socket is not NULL, the string specifies the socket or named pipe that should be used. Ergo, it piggybacks the socket your already connected client uses instead of trying and failing to create a new one.

In my case, it was /tmp/mysql.socket

And now, problem solved.

A trigger on the inbox calls the function which analyses the text inserted into the inbox table and on the basis of that either inserts it into the ads table or retrieves a set of ads from the ads table and pushes an outgoing message into the outbox table.


"There's fighting on the left,
and marching on the right"
ACDC - The Razor's Edge