MySQL Forums
Forum List  »  Stored Procedures

Re: Can't reopen temporary table in successive SELECTs in a FUNCTION
Posted by: Ondra Zizka
Date: May 10, 2008 11:11PM

Of course.

First I prepare the temp table with input data.

--------
CREATE TEMPORARY TABLE mhd_nn_GetStationTracesInfo (
pos SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
id_trace INT UNSIGNED NOT NULL UNIQUE KEY,
onward_stops SMALLINT UNSIGNED NOT NULL
) SELECT ...;

ALTER TABLE mhd_nn_GetStationTracesInfo RENAME TO mhd_nn_FormatDesiredOutput_traces; -- Input table.
--------

Then I call the function using

SET @saDesired = mhd_nn_FormatDesiredOutput( @iTrasaPos, @iStaniOffset );

The error appears upon calling the function, i.e., a logging CALL LoggP() is not performed (at least, there is no record in the log table).

If I comment out the SELECT, it works.
If I separate the CALL into SET and CALL and do an alias for the table, it also works:

SELECT MAX(onward_stops) INTO @iMaxOnward FROM mhd_nn_FormatDesiredOutput_traces AS traces;
CALL lib_GenerateSequence( 1, @iMaxOnward, 1 );


Thanks


--- function code ---

DELIMITER $$
CREATE FUNCTION `mhd_nn_FormatDesiredOutput`(
iSelectedTrace INT UNSIGNED,
iSelectedOnwardStopOffset INT UNSIGNED
) RETURNS varchar(512) BEGIN

CALL LoggP('mhd_nn_FormatDesiredOutput', F2('mhd_nn_FormatDesiredOutput({1}, {2})', iSelectedTrace, iSelectedOnwardStopOffset));

CALL lib_GenerateSequence( 1, (SELECT MAX(iSelectedOnwardStopOffset) FROM mhd_nn_FormatDesiredOutput_traces), 1 );

SELECT GROUP_CONCAT( IF(traces.id_trace = iSelectedTrace, 1.0, 0.0) ORDER BY traces.pos SEPARATOR ',' ) INTO @saValTraces
FROM mhd_nn_FormatDesiredOutput_traces AS traces;

-- SNIP --

DROP TEMPORARY TABLE IF EXISTS lib_GenerateSequence; /**/
RETURN CONCAT(@saValTraces, ',', @saValOnwardStops);

END $$
DELIMITER ;



Edited 2 time(s). Last edit at 05/10/2008 11:13PM by Ondra Zizka.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Can't reopen temporary table in successive SELECTs in a FUNCTION
6189
May 10, 2008 11:11PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.