any of you ever wrote a get_next_id function?

[Phil Powell]

function getNewID($field Name, $tableName) { // INT "METHOD" FOR
NON-AUTO-INCREMENT FIELDS
global $dbConn;
$sql = "SELECT MAX($fieldName) FROM $tableName";
$query = mysql_query($sq l, $dbConn) or die('Could not perform query: ' .
mysql_error());
if (!($row = mysql_fetch_row ($query))) return 1;
return $row[0];
}

I'm trying to retrieve the max(ID) from a column that cannot be
auto_incremente d (since mySQL version on this remote site only allows for
tables to have ONE auto_increment field - how stupid), the basic premise is
this:

I either return
1) the max ID
2) '1'

How have you guys done it, or what have I missed?

Phil

[127.0.0.1]

Re: any of you ever wrote a get_next_id function?

Phil Powell wrote:
[color=blue]
> I either return
> 1) the max ID
> 2) '1'
>
> How have you guys done it, or what have I missed?[/color]

You have missed what happens when more than one request comes at the
same time.

Just a question - why do you need more than one auto-inc in a single
table ?

--
Spam:newsgroup( at)craznar.com@ verisign-sux-klj.com
EMail:<01100011 001011100110001 001110101011100 10011010110
110010101000000 011000110111001 001100001011110 10011011100
110000101110010 001011100110001 101101111011011 0100100000>

[Tom Lee]

Re: any of you ever wrote a get_next_id function?

Hi Phil,

Agreed that simultaneous requests will cause problems, but what
mechanisms for ensuring unique column identifiers are commonly used
outside of MySQL - e.g. SQL Server, Postgres - where auto_inc is not
available?

- TL

127.0.0.1 wrote:[color=blue]
> Phil Powell wrote:
>
>[color=green]
>>I either return
>>1) the max ID
>>2) '1'
>>
>>How have you guys done it, or what have I missed?[/color]
>
>
> You have missed what happens when more than one request comes at the
> same time.
>
> Just a question - why do you need more than one auto-inc in a single
> table ?
>[/color]

[Matthias Esken]

Re: any of you ever wrote a get_next_id function?

Tom Lee <tl_nntp@webcru mb.com> schrieb:
[color=blue]
> Agreed that simultaneous requests will cause problems, but what
> mechanisms for ensuring unique column identifiers are commonly used
> outside of MySQL - e.g. SQL Server, Postgres - where auto_inc is not
> available?[/color]

I use triggers on Oracle or MS-SQL Server.

Regards,
Matthias

[127.0.0.1]

Re: any of you ever wrote a get_next_id function?

Tom Lee wrote:
[color=blue]
> Agreed that simultaneous requests will cause problems, but what
> mechanisms for ensuring unique column identifiers are commonly used
> outside of MySQL - e.g. SQL Server, Postgres - where auto_inc is not
> available?[/color]

Create a table called T_AUTO which has

___________
Table Field NextId


Then when you need a new ID, create a r/w connection to the table - get
the value and update it in one go.

Problem with this is that in MySQL it will be very slow as there is no
such thing as a row lock.

Seriously I have used the filesystem before for unique ids - by
creating a directory full of number.avail (e.g. 1.avail etc) ... then
my process tries to rename one of them to 1.unavail - when it succeeds
it uses it and puts some info in the file as to what it now points to.
The cool thing about file renames is they are one of the few fast
indivisible operations in almost all OSs.


--
Spam:newsgroup( at)craznar.com@ verisign-sux-klj.com
EMail:<01100011 001011100110001 001110101011100 10011010110
110010101000000 011000110111001 001100001011110 10011011100
110000101110010 001011100110001 101101111011011 0100100000>

[Phil Powell]

Re: any of you ever wrote a get_next_id function?

Could you go into that a bit more, I am not sure what you mean, sounds
interesting.

I need more than one "auto-increment" due to the schema of the table; in the
Vignette world I'm used to a separate "next_id" table but that would not
apply well in this version of mySQL due to performance.

Phil

"127.0.0.1" <newsgroup(at)c raznar.com@veri sign-sux-ijlkl.com> wrote in
message news:s4ggb.1390 93$bo1.2485@new s-server.bigpond. net.au...[color=blue]
> Tom Lee wrote:
>[color=green]
> > Agreed that simultaneous requests will cause problems, but what
> > mechanisms for ensuring unique column identifiers are commonly used
> > outside of MySQL - e.g. SQL Server, Postgres - where auto_inc is not
> > available?[/color]
>
> Create a table called T_AUTO which has
>
> ___________
> Table Field NextId
>
>
> Then when you need a new ID, create a r/w connection to the table - get
> the value and update it in one go.
>
> Problem with this is that in MySQL it will be very slow as there is no
> such thing as a row lock.
>
> Seriously I have used the filesystem before for unique ids - by
> creating a directory full of number.avail (e.g. 1.avail etc) ... then
> my process tries to rename one of them to 1.unavail - when it succeeds
> it uses it and puts some info in the file as to what it now points to.
> The cool thing about file renames is they are one of the few fast
> indivisible operations in almost all OSs.
>
>
> --
> Spam:newsgroup( at)craznar.com@ verisign-sux-klj.com
> EMail:<01100011 001011100110001 001110101011100 10011010110
> 110010101000000 011000110111001 001100001011110 10011011100
> 110000101110010 001011100110001 101101111011011 0100100000>[/color]