Peter,
Thanks for the response!
Unfortunately, that worked but a new error arose:
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
Is there a better way to generate incremented sequence IDs?
Can this be done in a stored function?
Happy programming,
James
On Jan 4, 2011, at 7:45 PM, Peter Brawley wrote:
>> 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=james.dek...@gmail.com
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
