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=arch...@jab.org
