Re: [Spock Proxy Devel] Some spockproxy questions.

[Frank F. <fr...@co...>]

On Jul 29, 2008, at 12:26 PM, Zach Garner wrote:

> I've got a question. I've been working on a configuration using  
> spockproxy (which I'm really enjoying btw). So far I've got  
> everything working pretty well, which is exciting.
>
> My question is, is there a way to return the incremented column  
> value after performing an insert? Or to retrieve the value just  
> before performing the insert? I'm working with an application that  
> has been using an incrementing value in combination with the  
> last_insert_id() function to track the data and continue making some  
> changes to it.

As Michael said the Spockproxy does not support the last_insert_id()  
but there is a function get_next_id which will give you one or several  
id for a particular table before you do the insert.  Then you can use  
these id(s) and the proxy will not change them.

Because it's called ahead of time nether the proxy nor the MySQL  
server could possibly know which table you're about to insert into so  
you have to tell it.  You can also ask for several id's at once; if  
you know you have 50 rows to insert just ask for 50 id's and they will  
be put aside for you so you can just use them and not have to keep  
asking for new ones.

The format is:

select get_next_id( < table name >, < number of id's you want >);

and it will return the first id; if you asked for 50 then the next 50  
are yours, if you asked for 1 that one is yours.  Be careful because  
there is no error checking if you go over the id's you were alloted.

There is a bug it the current function, it is not thread safe and if  
you hit it massively from several clients you can get the same id  
back.  This is fixed in the next release which we should be posting  
soon.  If your still experimenting the current function is fine; if  
you're ramping up of stress testing you'll want the new one.

>
> Anyway, I'll keep playing around. Chances are I've missed something  
> fairly obvious.
>
> It's been a real pleasure using such a nicely thought out  
> implementation like this.
>
> Regards!
>
> Zach

Thanks,

Frank
http://www.spock.com/frank