List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:January 5 2011 3:45am
Subject:Re: Trying to Generate Dynamic Sequence IDs as Stored Function or
Stored Procedure.
View as plain text  
>  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
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