MySQL Forums
Forum List  »  Stored Procedures

Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure
Posted by: James Dekker
Date: January 04, 2011 11:45PM

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...

Options: ReplyQuote


Subject
Views
Written By
Posted
Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure
3969
January 04, 2011 11:45PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.