Table doesn"t exist from stored function

Hi,

When I try to run this function, I receive ERROR 1146 (42S02): Table
'gi2.meta' doesn't exist

CREATE FUNCTION get_version()
RETURNS INT UNSIGNED
BEGIN
DECLARE exist_ TINYINT;
SELECT COUNT(*) INTO exist_ FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'GI2' AND TABLE_NAME = 'Meta' LIMIT 1;
IF (0 < exist_) THEN BEGIN
DECLARE ver_ INT UNSIGNED;
SELECT SUBSTRING(Version, 14, LENGTH(Version) - 15) INTO ver_
FROM GI2.Meta LIMIT 1;
IF (ver_ <= 65) THEN RETURN 5;
ELSE RETURN 0;
END IF;
END;
END IF;
RETURN 1;
END;

I can't see why I am receiving this error, as I am checking if the table
exists before the select. If this is a limitation in MySQL, any ideas
how to circumvent it?

Thanks

ImRe


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: Table doesn"t exist from stored function

Hello Imre,

Imre Palik wrote:
> Hi,
>
> When I try to run this function, I receive ERROR 1146 (42S02): Table
> 'gi2.meta' doesn't exist
>
> CREATE FUNCTION get_version()
> RETURNS INT UNSIGNED
> BEGIN
> DECLARE exist_ TINYINT;
> SELECT COUNT(*) INTO exist_ FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_SCHEMA = 'GI2' AND TABLE_NAME = 'Meta' LIMIT 1;
> IF (0 < exist_) THEN BEGIN
> DECLARE ver_ INT UNSIGNED;
> SELECT SUBSTRING(Version, 14, LENGTH(Version) - 15) INTO ver_
> FROM GI2.Meta LIMIT 1;
> IF (ver_ <= 65) THEN RETURN 5;
> ELSE RETURN 0;
> END IF;
> END;
> END IF;
> RETURN 1;
> END;
>
> I can't see why I am receiving this error, as I am checking if the
> table exists before the select. If this is a limitation in MySQL, any
> ideas how to circumvent it?
>
> Thanks
>
> ImRe
>
>

Is the table `Meta` or `meta` ? Case sensitivity is very important to
most Operating Systems. Try it with all lower case and see what happens.

--
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN

Join us at the 2007 MySQL Conference and Exposition
April 23 to 26 - Santa Clara, California, USA
http://www.mysqlconf.com/




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: Table doesn"t exist from stored function

Imre,

You may have found a stored function bug--the server tries to evaluate
the SELECT statement within the IF block when IF condition is false.

PB

-----

Shawn Green wrote:
> Hello Imre,
>
> Imre Palik wrote:
>> Hi,
>>
>> When I try to run this function, I receive ERROR 1146 (42S02): Table
>> 'gi2.meta' doesn't exist
>>
>> CREATE FUNCTION get_version()
>> RETURNS INT UNSIGNED
>> BEGIN
>> DECLARE exist_ TINYINT;
>> SELECT COUNT(*) INTO exist_ FROM INFORMATION_SCHEMA.TABLES
>> WHERE TABLE_SCHEMA = 'GI2' AND TABLE_NAME = 'Meta' LIMIT 1;
>> IF (0 < exist_) THEN BEGIN
>> DECLARE ver_ INT UNSIGNED;
>> SELECT SUBSTRING(Version, 14, LENGTH(Version) - 15) INTO ver_
>> FROM GI2.Meta LIMIT 1;
>> IF (ver_ <= 65) THEN RETURN 5;
>> ELSE RETURN 0;
>> END IF;
>> END;
>> END IF;
>> RETURN 1;
>> END;
>>
>> I can't see why I am receiving this error, as I am checking if the
>> table exists before the select. If this is a limitation in MySQL,
>> any ideas how to circumvent it?
>>
>> Thanks
>>
>> ImRe
>>
>>
>
> Is the table `Meta` or `meta` ? Case sensitivity is very important to
> most Operating Systems. Try it with all lower case and see what happens.
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org