> 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
