Wednesday 17 December 2008

Crossing Animals and Mysql user defined functions

Got up this morning about 7.45am. After breakfast I checked my email and went on Animal for a bit. I made a perfect snowman and there was a notice that Nook is having a ½ price sale tonight at 8pm, and on the 20th or 22nd (can't remember) he is having a double points day or sumat like that.

After that I checked my email again and did a bit more reading on user defined functions in mysql. I copied an example from the mysql website and got it working on my PC. Then I tested it on the webserver, there's not much point me trying to make my own function if it's not going to work on the server. So I tried it on the server, I could create the function but couldn't execute it, and couldn't add the execute privilege to any users. So I emailed evohosting to see what the problem was.

I also downloaded NetBeans PHP IDE and started downloading Ubuntu and OSX86.

Before lunch Clare gave me back my background stand, which she had used for a puppet show. I took the stands out and the middle section of the crossbar fell apart, the bit on the end that inserts into the end tube fell off. I put it back in and then searched around on top of the white shelves in my cupboard for the allen key that tightens the screws on it. I tightened the screw up, and that fixed the loose bit in place. But then I couldn't insert that end into the end pole. so I had to loosen the screw a bit, and then it fit okay.

After lunch I checked my email again, evohosting had replied and had enabled execute for the test user I was testing with. So I guess when I want to add execute to a user's privileges I just need to ticket them. I tested the script on the server, and the function now executed okay.

I vacuumed my room and also put all the camera bits on top of the white shelf in my cupboard in a box. I also took all the flashes and flash bits from the hanging shelves in my cupboards, and put them in a box. Then I put both the boxes on top of the white shelves. I also packed all my other camera gear in the Lowepro Slingshot 300AW bag and put one lens in the box that already had some lenses in it.

I tried modifying the function to get it to return more than one value, but couldn't get it to. I tried setting
  • RETURNS varchar(50), int
    RETURN CONCAT('Hello, ',s,'!'), 5;
  • RETURNS varchar(50), int;
    RETURN CONCAT('Hello, ',s,'!'), 5;
  • RETURNS varchar(50), int
    RETURN SELECT (CONCAT('Hello, ',s,'!'), 5);

But all of them just gave a syntax error.
I also tried
RETURNS char(50)
DECLARE result char(50);
SET result = (SELECT CONCAT('Hello, ',s,'!'), 5);
RETURN result;

And that didn't have a syntax error, but instead gave an error saying there should only be one column.

So it looks like for getting multiple values I will have to writing a procedure, which I don't think is as good as a function since you need to run two queries from php to get the result - once to call the procedure and secondly to get the the result.

The other problem with mysql user defined functions and procedures is that it seems to need to specify all the arguments/parameters expected. Since in my case I am dealing with a loop, this means I would need to call the procedure each time the loop runs. If I didn't need to specify arguments/parameters then I could just run the loop and aggregate the values needed to be passed to the procedure, and pass them in one go once the loop had finished, and then have mysql loop through them all.

Possibly I may be wrong about the above, once I've got a basic procedure working I will examine the param_list column of the mysql.proc table to see if this is anything to do with it.

After dinner I watched Lost with Mac and Ben, then I went on Animal for a bit. I did some fishing and caught a string fish! Then I went to Nook's and bought a blue cupboard or sumat similar and a spade in the half price sale. I let Ben go on Animal for a bit and he just bought loads of paper - probably more than 2 full inventories full. I had just finally got so bored I was going to go away when Ben said he'd finished, then I went on it a bit more. I still couldn't catch any Koi - they must be rarer than stringfish! Also 2 animals wanted me to get them a specific fossil, I don't normally get any fossils since Mac always digs them all up in the morning.

The weather today was nice all day, most blue sky but then at sunset there was a massive cloud, but it was above/behind the sun and lit up a really nice orange as the sun was setting.

Food
Breakfast: Bowl of fake coco pops; cup o' tea.
Lunch: Sumat (maybe Roma) style ham sandwich; clementine; bits of end of honey & muesli flapjack; cup o' tea.
Dinner: 2x big sausage rolls; potatoes; baked beans; a few peas. For pudding I had 2x butter pastry mince pies with a bit of cream that was left over (not enough cream really) - delee pies. Coffee; Sainsbury's caramel chocolate.

No comments: