Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure
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
call 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
'ID = (select max(CONVERT(id_field, signed)) from my_table_t)' 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.
Is there a better way to generate incremented sequence IDs and then assign them to dynamic variables?
Can this be done in a stored function?
Any help would be greatly appreciated...