List:General Discussion« Previous MessageNext Message »
From:James Dekker Date:January 5 2011 3:28am
Subject:Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
View as plain text  
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
Thread
Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.James Dekker5 Jan
  • Re: Trying to Generate Dynamic Sequence IDs as Stored Function orStored Procedure.Peter Brawley5 Jan
    • Re: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.James Dekker5 Jan
      • Re: Trying to Generate Dynamic Sequence IDs as Stored Function orStored Procedure.MySQL)5 Jan
        • Re: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.James Dekker5 Jan
          • Re: Trying to Generate Dynamic Sequence IDs as Stored Function orStored Procedure.MySQL)5 Jan