Re: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
On 1/4/2011 23:23, James Dekker wrote: 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? Is there a particular reason why you cannot use an auto_increment column to atomically create your sequence number? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
Because some sequence tables contain one to many cardinality and MySQL tables can only have one auto_increment column... Is there a way to do what I am trying to do (obtain max sequence id, set it to its corresponding table, and then increment by one) in a stored function? Happy programming, James On Jan 5, 2011, at 10:01 AM, Shawn Green (MySQL) wrote: On 1/4/2011 23:23, James Dekker wrote: 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? Is there a particular reason why you cannot use an auto_increment column to atomically create your sequence number? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
On 1/5/2011 13:31, James Dekker wrote: Because some sequence tables contain one to many cardinality and MySQL tables can only have one auto_increment column... Is there a way to do what I am trying to do (obtain max sequence id, set it to its corresponding table, and then increment by one) in a stored function? Maybe some variation of this will help? http://stackoverflow.com/questions/805808/emulating-a-transaction-safe-sequence-in-mysql I don't understand the need for a SEQUENCE. In my history, if there is some kind of object identifier you want to use, then an auto_increment field on the row that defines the object itself is sufficient. Then all child elements of that object can include the autogenerated ID value from their parent object as you create them alongside of any unique identifiers they may require. http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id Have you also explored the use of auto_increment columns as part of a multiple-column index on MyISAM tables as described here? http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
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=arch...@jab.org
Re: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
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=arch...@jab.org
Re: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
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
