SEO

Im using MySQL 5.1 to create a stored procedure, but without luck.

Query:
DELIMITER /
CREATE PROCEDURE _test()
BEGIN
DECLARE x INT;
SET x = 5;
SELECT * FROM tbl LIMIT x;
END

I get this error:
#1064 - 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 'x;
END' at line 5

...and I have no idea why. Anyone knows why ?

Kim schreef:
> Im using MySQL 5.1 to create a stored procedure, but without luck.
>
> Query:
> DELIMITER /
> CREATE PROCEDURE _test()
> BEGIN
> DECLARE x INT;
> SET x = 5;
> SELECT * FROM tbl LIMIT x;
> END
>
> I get this error:
> #1064 - 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 'x;
> END' at line 5
>
> ..and I have no idea why. Anyone knows why ?

try this:
SET @a=1;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
EXECUTE STMT USING @a;
(source: http://dev.mysql.com/doc/refman/5.0/en/select.html)


--
Luuk

Kim <kimslot@gmail.com> wrote in
<5c7b07e9-65fe-4973-b042-e88bff5d42c8@2g2000hsn.googlegroups.com>:
> Im using MySQL 5.1 to create a stored procedure, but
> without luck.
>
> Query:
> DELIMITER /
> CREATE PROCEDURE _test()
> BEGIN
> DECLARE x INT;
> SET x = 5;
> SELECT * FROM tbl LIMIT x;
> END
>
> I get this error:
> #1064 - 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 'x;
> END' at line 5
>
> ..and I have no idea why. Anyone knows why ?

Have you checked the manual, as the error message itself
suggests?

MySQL Reference Manual, 12.2.7, section on LIMIT clause,
describes the limitation you've run into, as well as a
suggested workaround.

--
In Soviet Russia, XML documents transform *you*.

On Mar 12, 3:05 pm, Luuk <L...@invalid.lan> wrote:
> Kim schreef:
>
>
>
> > Im using MySQL 5.1 to create a stored procedure, but without luck.
>
> > Query:
> > DELIMITER /
> > CREATE PROCEDURE _test()
> > BEGIN
> > DECLARE x INT;
> > SET x = 5;
> > SELECT * FROM tbl LIMIT x;
> > END
>
> > I get this error:
> > #1064 - 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 'x;
> > END' at line 5
>
> > ..and I have no idea why. Anyone knows why ?
>
> try this:
> SET @a=1;
> PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
> EXECUTE STMT USING @a;
> (source:http://dev.mysql.com/doc/refman/5.0/en/select.html)
>
> --
> Luuk

Shortly after I posted this I noticed another similar topic, which did
in fact have an answer: http://groups.google.com/group/comp....19d336d3850e1/

Thanks Luuk, it helped me continue, but I have a new problem not
related to the limit problem. Maybe you can help ?

This is my SP code so far:
DELIMITER /
CREATE PROCEDURE _sp()
BEGIN
DROP TABLE IF EXISTS `tmp_tbl`;
CREATE TABLE `tmp_tbl`(`colAA` bigint(20) unsigned NOT NULL);

SET @tip_a = 0;
SET @tip_b = 0;
SET @tip_next = 0;
SET @tip_last = 0;
SET @tip_value = 0;
SET @tip_found = NULL;

SELECT count(*) INTO @tip_last FROM `tbl`;
PREPARE stat1 FROM 'SELECT colA,colB INTO @tip_a,@tip_b FROM `tbl`
LIMIT ?,1';
PREPARE stat2 FROM 'SELECT * INTO @tip_found FROM `tmp_tbl` WHERE
colAA=?';

WHILE @tip_next < @tip_last DO
EXECUTE stat1 USING @tip_next;
SET @tip_value = @tip_a;
WHILE @tip_value <= @tip_b DO
EXECUTE stat2 USING @tip_value;
IF (@tip_found IS NULL) THEN
INSERT INTO `tmp_tbl` VALUES(@tip_value);
END IF
SET @tip_value = @tip_value + 1; <------
END WHILE
SET @tip_next = @tip_next + 1;
END WHILE

DEALLOCATE stat2;
DEALLOCATE stat1;

SELECT count(*) FROM `tmp_tbl`;
END /
DELIMITER ;

Notice that there is two tables.
I get an error at the line, which I have marked with an arrow (<----).
Error 1064 (42000) simply states its a syntax error, yet I see nothing
wrong. Any idea ?
Removing the marked line makes the next line (END WHILE) the error, so
the error must be above the marked line.

Kim <kimslot@gmail.com> wrote in
<d2e7d459-4e5d-466f-aa6c-08cd9879fc3d@s12g2000prg.googlegroups.com>:
> On Mar 12, 3:05 pm, Luuk <L...@invalid.lan> wrote:
>> Kim schreef:
>> > Im using MySQL 5.1 to create a stored procedure, but
>> > without luck.
>>
>> try this:
>> SET @a=1;
>> PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
>> EXECUTE STMT USING @a;
>
> DELIMITER /
> CREATE PROCEDURE _sp()
> BEGIN
> DROP TABLE IF EXISTS `tmp_tbl`;
> CREATE TABLE `tmp_tbl`(`colAA` bigint(20) unsigned NOT
> NULL);
>
> SET @tip_a = 0;
> SET @tip_b = 0;
> SET @tip_next = 0;
> SET @tip_last = 0;
> SET @tip_value = 0;
> SET @tip_found = NULL;
>
> SELECT count(*) INTO @tip_last FROM `tbl`;
> PREPARE stat1 FROM 'SELECT colA,colB INTO @tip_a,@tip_b
> FROM `tbl`
> LIMIT ?,1';
> PREPARE stat2 FROM 'SELECT * INTO @tip_found FROM
> `tmp_tbl` WHERE
> colAA=?';
>
> WHILE @tip_next < @tip_last DO
> EXECUTE stat1 USING @tip_next;
> SET @tip_value = @tip_a;
> WHILE @tip_value <= @tip_b DO
> EXECUTE stat2 USING @tip_value;
> IF (@tip_found IS NULL) THEN
> INSERT INTO `tmp_tbl` VALUES(@tip_value);
> END IF
> SET @tip_value = @tip_value + 1; <------
> END WHILE
> SET @tip_next = @tip_next + 1;
> END WHILE
>
> DEALLOCATE stat2;
> DEALLOCATE stat1;
>
> SELECT count(*) FROM `tmp_tbl`;
> END /
> DELIMITER ;
>
> Notice that there is two tables.
> I get an error at the line, which I have marked with an
> arrow (<----). Error 1064 (42000) simply states its a
> syntax error, yet I see nothing wrong.

What about statement delimiters after your END IFs and END
WHILEs?

--
In Soviet Russia, XML documents transform *you*.

On Mar 13, 3:38 pm, Pavel Lepin <p.le...@ctncorp.com> wrote:
> Kim <kims...@gmail.com> wrote in
> <d2e7d459-4e5d-466f-aa6c-08cd9879f...@s12g2000prg.googlegroups.com>:
>
>
>
> > On Mar 12, 3:05 pm, Luuk <L...@invalid.lan> wrote:
> >> Kim schreef:
> >> > Im using MySQL 5.1 to create a stored procedure, but
> >> > without luck.
>
> >> try this:
> >> SET @a=1;
> >> PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
> >> EXECUTE STMT USING @a;
>
> > DELIMITER /
> > CREATE PROCEDURE _sp()
> > BEGIN
> > DROP TABLE IF EXISTS `tmp_tbl`;
> > CREATE TABLE `tmp_tbl`(`colAA` bigint(20) unsigned NOT
> > NULL);
>
> > SET @tip_a = 0;
> > SET @tip_b = 0;
> > SET @tip_next = 0;
> > SET @tip_last = 0;
> > SET @tip_value = 0;
> > SET @tip_found = NULL;
>
> > SELECT count(*) INTO @tip_last FROM `tbl`;
> > PREPARE stat1 FROM 'SELECT colA,colB INTO @tip_a,@tip_b
> > FROM `tbl`
> > LIMIT ?,1';
> > PREPARE stat2 FROM 'SELECT * INTO @tip_found FROM
> > `tmp_tbl` WHERE
> > colAA=?';
>
> > WHILE @tip_next < @tip_last DO
> > EXECUTE stat1 USING @tip_next;
> > SET @tip_value = @tip_a;
> > WHILE @tip_value <= @tip_b DO
> > EXECUTE stat2 USING @tip_value;
> > IF (@tip_found IS NULL) THEN
> > INSERT INTO `tmp_tbl` VALUES(@tip_value);
> > END IF
> > SET @tip_value = @tip_value + 1; <------
> > END WHILE
> > SET @tip_next = @tip_next + 1;
> > END WHILE
>
> > DEALLOCATE stat2;
> > DEALLOCATE stat1;
>
> > SELECT count(*) FROM `tmp_tbl`;
> > END /
> > DELIMITER ;
>
> > Notice that there is two tables.
> > I get an error at the line, which I have marked with an
> > arrow (<----). Error 1064 (42000) simply states its a
> > syntax error, yet I see nothing wrong.
>
> What about statement delimiters after your END IFs and END
> WHILEs?
>
> --
> In Soviet Russia, XML documents transform *you*.

> What about statement delimiters after your END IFs and END
> WHILEs?
Thanks, that did the trick.

Work that it works, performance seems to be an issue. As the stored
procedure can only process about 10k inserts per minute which gives
about a maximum time-to-complete of 300 days!!

Kim schreef:
> On Mar 13, 3:38 pm, Pavel Lepin <p.le...@ctncorp.com> wrote:
>> Kim <kims...@gmail.com> wrote in
>> <d2e7d459-4e5d-466f-aa6c-08cd9879f...@s12g2000prg.googlegroups.com>:
>>
>>
>>
>>> On Mar 12, 3:05 pm, Luuk <L...@invalid.lan> wrote:
>>>> Kim schreef:
>>>>> Im using MySQL 5.1 to create a stored procedure, but
>>>>> without luck.
>>>> try this:
>>>> SET @a=1;
>>>> PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
>>>> EXECUTE STMT USING @a;
>>> DELIMITER /
>>> CREATE PROCEDURE _sp()
>>> BEGIN
>>> DROP TABLE IF EXISTS `tmp_tbl`;
>>> CREATE TABLE `tmp_tbl`(`colAA` bigint(20) unsigned NOT
>>> NULL);
>>> SET @tip_a = 0;
>>> SET @tip_b = 0;
>>> SET @tip_next = 0;
>>> SET @tip_last = 0;
>>> SET @tip_value = 0;
>>> SET @tip_found = NULL;
>>> SELECT count(*) INTO @tip_last FROM `tbl`;
>>> PREPARE stat1 FROM 'SELECT colA,colB INTO @tip_a,@tip_b
>>> FROM `tbl`
>>> LIMIT ?,1';
>>> PREPARE stat2 FROM 'SELECT * INTO @tip_found FROM
>>> `tmp_tbl` WHERE
>>> colAA=?';
>>> WHILE @tip_next < @tip_last DO
>>> EXECUTE stat1 USING @tip_next;
>>> SET @tip_value = @tip_a;
>>> WHILE @tip_value <= @tip_b DO
>>> EXECUTE stat2 USING @tip_value;
>>> IF (@tip_found IS NULL) THEN
>>> INSERT INTO `tmp_tbl` VALUES(@tip_value);
>>> END IF
>>> SET @tip_value = @tip_value + 1; <------
>>> END WHILE
>>> SET @tip_next = @tip_next + 1;
>>> END WHILE
>>> DEALLOCATE stat2;
>>> DEALLOCATE stat1;
>>> SELECT count(*) FROM `tmp_tbl`;
>>> END /
>>> DELIMITER ;
>>> Notice that there is two tables.
>>> I get an error at the line, which I have marked with an
>>> arrow (<----). Error 1064 (42000) simply states its a
>>> syntax error, yet I see nothing wrong.
>> What about statement delimiters after your END IFs and END
>> WHILEs?
>>
>> --
>> In Soviet Russia, XML documents transform *you*.
>
>> What about statement delimiters after your END IFs and END
>> WHILEs?
> Thanks, that did the trick.
>
> Work that it works, performance seems to be an issue. As the stored
> procedure can only process about 10k inserts per minute which gives
> about a maximum time-to-complete of 300 days!!

this piece:
EXECUTE stat2 USING @tip_value;
IF (@tip_found IS NULL) THEN
INSERT INTO `tmp_tbl` VALUES(@tip_value);
END IF

could be done like this:
INSERT IGNORE INTO `tmp_tbl` VALUES(@tip_value);

which might make things quicker....
but i doubt if it will be quick enough... ;-)

so, maybe you should start all over again, and thin of a complete other
way to solve your problem?


--
Luuk

Kim wrote:
> On Mar 13, 3:38 pm, Pavel Lepin <p.le...@ctncorp.com> wrote:
>> Kim <kims...@gmail.com> wrote in
>> <d2e7d459-4e5d-466f-aa6c-08cd9879f...@s12g2000prg.googlegroups.com>:
>>
>>
>>
>>> On Mar 12, 3:05 pm, Luuk <L...@invalid.lan> wrote:
>>>> Kim schreef:
>>>>> Im using MySQL 5.1 to create a stored procedure, but
>>>>> without luck.
>>>> try this:
>>>> SET @a=1;
>>>> PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
>>>> EXECUTE STMT USING @a;
>>> DELIMITER /
>>> CREATE PROCEDURE _sp()
>>> BEGIN
>>> DROP TABLE IF EXISTS `tmp_tbl`;
>>> CREATE TABLE `tmp_tbl`(`colAA` bigint(20) unsigned NOT
>>> NULL);
>>> SET @tip_a = 0;
>>> SET @tip_b = 0;
>>> SET @tip_next = 0;
>>> SET @tip_last = 0;
>>> SET @tip_value = 0;
>>> SET @tip_found = NULL;
>>> SELECT count(*) INTO @tip_last FROM `tbl`;
>>> PREPARE stat1 FROM 'SELECT colA,colB INTO @tip_a,@tip_b
>>> FROM `tbl`
>>> LIMIT ?,1';
>>> PREPARE stat2 FROM 'SELECT * INTO @tip_found FROM
>>> `tmp_tbl` WHERE
>>> colAA=?';
>>> WHILE @tip_next < @tip_last DO
>>> EXECUTE stat1 USING @tip_next;
>>> SET @tip_value = @tip_a;
>>> WHILE @tip_value <= @tip_b DO
>>> EXECUTE stat2 USING @tip_value;
>>> IF (@tip_found IS NULL) THEN
>>> INSERT INTO `tmp_tbl` VALUES(@tip_value);
>>> END IF
>>> SET @tip_value = @tip_value + 1; <------
>>> END WHILE
>>> SET @tip_next = @tip_next + 1;
>>> END WHILE
>>> DEALLOCATE stat2;
>>> DEALLOCATE stat1;
>>> SELECT count(*) FROM `tmp_tbl`;
>>> END /
>>> DELIMITER ;
>>> Notice that there is two tables.
>>> I get an error at the line, which I have marked with an
>>> arrow (<----). Error 1064 (42000) simply states its a
>>> syntax error, yet I see nothing wrong.
>> What about statement delimiters after your END IFs and END
>> WHILEs?
>>
>> --
>> In Soviet Russia, XML documents transform *you*.
>
>> What about statement delimiters after your END IFs and END
>> WHILEs?
> Thanks, that did the trick.
>
> Work that it works, performance seems to be an issue. As the stored
> procedure can only process about 10k inserts per minute which gives
> about a maximum time-to-complete of 300 days!!
>

Why are you trying to insert > 4B rows? And I agree with Luuk - if this
is the case, you need to find some other way to do it.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================