Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.

Hello there,

I am using MySQL 5 on OS X - Snow Leopard...

Have working code in place which obtains the highest sequence number ID =
from a sequence table and then increments and assigns it to its =
corresponding table:

The original code's purpose is to dynamically increments a specific =
table's last sequence id and set its corresponding table's id to that =
new value.

Notes:

------------------------------------------------------------ -

(1) Original Code Snippet (which is working):

-- Get last sequence number.
replace into my_sequence_id_s set id =3D (select =
max(CONVERT(sequence_id, signed)) from my_table_t);

-- Increments the number.
insert into my_sequence_id_s set id =3D null;

-- Saves the number as a variable
set @dynamicId =3D last_insert_id();

-- Print
select @dynamicId;

------------------------------------------------------------ -

(2) Refactoring:

DROP PROCEDURE IF EXISTS generate_dynamic_id#
CREATE PROCEDURE generate_dynamic_id(IN _sequence_table varchar(40),IN =
_actual_table varchar(40), IN _id_field VARCHAR(40), OUT dynamic_id =
varchar(40))
BEGIN
-- Get Last Sequence Number
set @getLastSequenceNumberSQL =3D concat('REPLACE INTO ', =
_sequence_table, 'SET ID =3D (select max(CONVERT(',_id_field,', signed)) =
from ', _actual_table, ');');
prepare lastRecordStmt from @getLastSequenceNumberSQL;
execute lastRecordStmt;
deallocate prepare lastRecordStmt;
=20
-- Increments the number.
set @createNewSequenceNumberSQL =3D concat('insert into ', =
_sequence_table ,' set id =3D null;');
prepare newSequenceNumberStmt from @createNewSequenceNumberSQL;
execute newSequenceNumberStmt;
deallocate prepare newSequenceNumberStmt;

-- Set the number as a dynamic variable.
set @dynamic_id =3D last_insert_id();
END;
#

------------------------------------------------------------ -

(3) Here's the calling function (which fails):

-- Get dynamically incremented id
generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id', =
@dynamicId);

Error:

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error =
in your SQL syntax; check the manual that corresponds to your MySQL =
server version
for the right syntax to use near =
'generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id', @dyn' =
at line 1.

------------------------------------------------------------ -

For some odd reason, dynamic function calls are not allowed in Stored =
Functions or Triggers, so that's why a Stored Procedure was used.

As you can see, I am setting up varchars at the parameters and then =
trying to concatenate them as strings and run them inside prepared =
statements.

Any help would be greatly appreciated...

-James=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Trying to Generate Dynamic Sequence IDs as Stored Function orStored Procedure.

> generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id', @dynamicId);

Should be:

CALL generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id', @dynamicId);

PB

----

On 1/4/2011 9:28 PM, James Dekker wrote:
> Hello there,
>
> I am using MySQL 5 on OS X - Snow Leopard...
>
> Have working code in place which obtains the highest sequence number ID from a sequence table and then increments and assigns it to its corresponding table:
>
> The original code's purpose is to dynamically increments a specific table's last sequence id and set its corresponding table's id to that new value.
>
> Notes:
>
> ------------------------------------------------------------ -
>
> (1) Original Code Snippet (which is working):
>
> -- Get last sequence number.
> replace into my_sequence_id_s set id = (select max(CONVERT(sequence_id, signed)) from my_table_t);
>
> -- Increments the number.
> insert into my_sequence_id_s set id = null;
>
> -- Saves the number as a variable
> set @dynamicId = last_insert_id();
>
> -- Print
> select @dynamicId;
>
> ------------------------------------------------------------ -
>
> (2) Refactoring:
>
> DROP PROCEDURE IF EXISTS generate_dynamic_id#
> CREATE PROCEDURE generate_dynamic_id(IN _sequence_table varchar(40),IN _actual_table varchar(40), IN _id_field VARCHAR(40), OUT dynamic_id varchar(40))
> BEGIN
> -- Get Last Sequence Number
> set @getLastSequenceNumberSQL = concat('REPLACE INTO ', _sequence_table, 'SET ID = (select max(CONVERT(',_id_field,', signed)) from ', _actual_table, ');');
> prepare lastRecordStmt from @getLastSequenceNumberSQL;
> execute lastRecordStmt;
> deallocate prepare lastRecordStmt;
>
> -- Increments the number.
> set @createNewSequenceNumberSQL = concat('insert into ', _sequence_table ,' set id = null;');
> prepare newSequenceNumberStmt from @createNewSequenceNumberSQL;
> execute newSequenceNumberStmt;
> deallocate prepare newSequenceNumberStmt;
>
> -- Set the number as a dynamic variable.
> set @dynamic_id = last_insert_id();
> END;
> #
>
> ------------------------------------------------------------ -
>
> (3) Here's the calling function (which fails):
>
> -- Get dynamically incremented id
> generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id', @dynamicId);
>
> Error:
>
> com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
> for the right syntax to use near 'generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id', @dyn' at line 1.
>
> ------------------------------------------------------------ -
>
> For some odd reason, dynamic function calls are not allowed in Stored Functions or Triggers, so that's why a Stored Procedure was used.
>
> As you can see, I am setting up varchars at the parameters and then trying to concatenate them as strings and run them inside prepared statements.
>
> Any help would be greatly appreciated...
>
> -James

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.

Peter,

Thanks for the response!

Unfortunately, that worked but a new error arose:

check the manual that corresponds to your MySQL server version for the =
right syntax to use near 'ID =3D (select max(CONVERT(id_field, signed)) =
from my_table_t)' at line 1

Is there a better way to generate incremented sequence IDs?=20

Can this be done in a stored function?

Happy programming,

James

On Jan 4, 2011, at 7:45 PM, Peter Brawley wrote:

>> generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id', =
@dynamicId);
>=20
> Should be:
>=20
> CALL generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id', =
@dynamicId);
>=20
> PB
>=20
> ----
>=20
> On 1/4/2011 9:28 PM, James Dekker wrote:
>> Hello there,
>>=20
>> I am using MySQL 5 on OS X - Snow Leopard...
>>=20
>> Have working code in place which obtains the highest sequence number =
ID from a sequence table and then increments and assigns it to its =
corresponding table:
>>=20
>> The original code's purpose is to dynamically increments a specific =
table's last sequence id and set its corresponding table's id to that =
new value.
>>=20
>> Notes:
>>=20
>> ------------------------------------------------------------ -
>>=20
>> (1) Original Code Snippet (which is working):
>>=20
>> -- Get last sequence number.
>> replace into my_sequence_id_s set id =3D (select =
max(CONVERT(sequence_id, signed)) from my_table_t);
>>=20
>> -- Increments the number.
>> insert into my_sequence_id_s set id =3D null;
>>=20
>> -- Saves the number as a variable
>> set @dynamicId =3D last_insert_id();
>>=20
>> -- Print
>> select @dynamicId;
>>=20
>> ------------------------------------------------------------ -
>>=20
>> (2) Refactoring:
>>=20
>> DROP PROCEDURE IF EXISTS generate_dynamic_id#
>> CREATE PROCEDURE generate_dynamic_id(IN _sequence_table =
varchar(40),IN _actual_table varchar(40), IN _id_field VARCHAR(40), OUT =
dynamic_id varchar(40))
>> BEGIN
>> -- Get Last Sequence Number
>> set @getLastSequenceNumberSQL =3D concat('REPLACE INTO ', =
_sequence_table, 'SET ID =3D (select max(CONVERT(',_id_field,', signed)) =
from ', _actual_table, ');');
>> prepare lastRecordStmt from @getLastSequenceNumberSQL;
>> execute lastRecordStmt;
>> deallocate prepare lastRecordStmt;
>>=20
>> -- Increments the number.
>> set @createNewSequenceNumberSQL =3D concat('insert into ', =
_sequence_table ,' set id =3D null;');
>> prepare newSequenceNumberStmt from @createNewSequenceNumberSQL;
>> execute newSequenceNumberStmt;
>> deallocate prepare newSequenceNumberStmt;
>>=20
>> -- Set the number as a dynamic variable.
>> set @dynamic_id =3D last_insert_id();
>> END;
>> #
>>=20
>> ------------------------------------------------------------ -
>>=20
>> (3) Here's the calling function (which fails):
>>=20
>> -- Get dynamically incremented id
>> generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id', =
@dynamicId);
>>=20
>> Error:
>>=20
>> com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an =
error in your SQL syntax; check the manual that corresponds to your =
MySQL server version
>> for the right syntax to use near =
'generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id', @dyn' =
at line 1.
>>=20
>> ------------------------------------------------------------ -
>>=20
>> For some odd reason, dynamic function calls are not allowed in Stored =
Functions or Triggers, so that's why a Stored Procedure was used.
>>=20
>> As you can see, I am setting up varchars at the parameters and then =
trying to concatenate them as strings and run them inside prepared =
statements.
>>=20
>> Any help would be greatly appreciated...
>>=20
>> -James
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Djames.dekker@gmail.com
>=20


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Trying to Generate Dynamic Sequence IDs as Stored Function orStored Procedure.

On 1/4/2011 23:23, James Dekker wrote:
> Peter,
>
> Thanks for the response!
>
> Unfortunately, that worked but a new error arose:
>
> check the manual that corresponds to your MySQL server version for the right syntax to use near 'ID = (select max(CONVERT(id_field, signed)) from my_table_t)' at line 1
>
> Is there a better way to generate incremented sequence IDs?
>
> Can this be done in a stored function?
>

Is there a particular reason why you cannot use an auto_increment column
to atomically create your sequence number?

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.

Because some sequence tables contain one to many cardinality and MySQL =
tables can only have one auto_increment column...

Is there a way to do what I am trying to do (obtain max sequence id, set =
it to its corresponding table, and then increment by one) in a stored =
function?

Happy programming,

James

On Jan 5, 2011, at 10:01 AM, Shawn Green (MySQL) wrote:

> On 1/4/2011 23:23, James Dekker wrote:
>> Peter,
>>=20
>> Thanks for the response!
>>=20
>> Unfortunately, that worked but a new error arose:
>>=20
>> check the manual that corresponds to your MySQL server version for =
the right syntax to use near 'ID =3D (select max(CONVERT(id_field, =
signed)) from my_table_t)' at line 1
>>=20
>> Is there a better way to generate incremented sequence IDs?
>>=20
>> Can this be done in a stored function?
>>=20
>=20
> Is there a particular reason why you cannot use an auto_increment =
column to atomically create your sequence number?
>=20
> --=20
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc.
> Office: Blountville, TN


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Trying to Generate Dynamic Sequence IDs as Stored Function orStored Procedure.

On 1/5/2011 13:31, James Dekker wrote:
> Because some sequence tables contain one to many cardinality and MySQL tables can only have one auto_increment column...
>
> Is there a way to do what I am trying to do (obtain max sequence id, set it to its corresponding table, and then increment by one) in a stored function?
>

Maybe some variation of this will help?
http://stackoverflow.com/questions/805808/emulating-a-transa ction-safe-sequence-in-mysql

I don't understand the need for a SEQUENCE. In my history, if there is
some kind of object identifier you want to use, then an auto_increment
field on the row that defines the object itself is sufficient. Then all
child elements of that object can include the autogenerated ID value
from their parent object as you create them alongside of any unique
identifiers they may require.
http://dev.mysql.com/doc/refman/5.1/en/information-functions .html#function_last-insert-id

Have you also explored the use of auto_increment columns as part of a
multiple-column index on MyISAM tables as described here?

http://dev.mysql.com/doc/refman/5.1/en/example-auto-incremen t.html


--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org