fill calendar table - mysql syntax

why isn't this procedure working?

i'm trying to fill a calendar with a bunch of dates so i can reference it from another table to see what dates are missing.

if you could, include the syntax to create a new table in case i messed up that as well.
i used this last time: CREATE Table Calendar(dt date not null);
ysql> create procedure fillcalendar()
    -> begin
    -> SET @x = '2000-01-01'
    -> repeat
    -> INSERT INTO Calendar(dt) VALUES (@x)
    -> SET @x = @x + 1
    -> UNTIL @x > '2000-12-31'
    -> end repeat
    -> end
    -> ;
ERROR 1064 (42000): 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 'repeat
INSERT INTO Calendar(dt) VALUES (@x)
SET @x = @x + 1
UNTIL @x > '2000-12-' at line 4
mysql>

Open in new window

LVL 1
bschwartingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hujiCommented:
Look here:

set @x = '2000-01-01';
....
set @x = @x + 1;

as far as I know, there is no way to add 1 to a string.
0
bschwartingAuthor Commented:
so what is the best way to create a calendar table?
0
hujiCommented:
So you actually may need something more like this:

ADDDATE('1998-01-02', INTERVAL 31 DAY);

create procedure fillcalendar()
begin
SET @x = '2000-01-01';
 repeat
  INSERT INTO Calendar(dt) VALUES (@x)
  @x = ADDDATE(@x, INTERVAL 1 DAY);
  UNTIL @x > '2000-12-31'
 end repeat
end
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Beverley PortlockCommented:
Try using ADDDATE to increment the date rather than adding one to it

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

SET @x = ADDDATE(@x INTERVAL 1 DAY)
0
bschwartingAuthor Commented:
not sure what i'm doing wrong, so bare with me, it's my 1st procedure

at they mysql prompt, i type the 1st line, create procedure fillcalendar(), then hit enter
it gives me the next line like so,  -> , and i type begin, hit enter and continue

i guess this is the correct way to do this?

if it is, when i get to the set line, #3, with the ";" at the end, and hit enter, it tries to execute right then.

what am i doing wrong?  if i do all the procedure without ";" at the end until the last line, i get the same error as above.
0
bschwartingAuthor Commented:
looks like i needed to change this 1st, now i can setup the procedure, but it still errors out

mysql> delimiter //
mysql> create procedure fillcalendar()
    -> begin
    -> SET @x = '2000-01-01';
    -> repeat
    -> INSERT INTO Calendar(dt) VALUES (@x)
    -> set @x = ADDDATE(@x, INTERVAL 1 DAY);
    -> UNTIL @x > '2000-12-31'
    -> end repeat
    -> end;
    -> //
ERROR 1064 (42000): 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 'set @x = ADDDATE(@x, INTERVAL 1 DAY);
UNTIL @x > '2000-12-31'
end repeat
end' at line 6

Open in new window

0
bschwartingAuthor Commented:
idea's as to what's wrong now?  can't figure out the syntax on this.
0
hujiCommented:
Dont do this:

set @x = ADDDATE(@x, INTERVAL 1 DAY);


Instead, try this:

@x = ADDDATE(@x, INTERVAL 1 DAY);

Huji
0
bschwartingAuthor Commented:
i tried both, neither worked.
0
bschwartingAuthor Commented:
anyone have a mysql server to test this on and assist?
0
hujiCommented:
I didn't test the code I provided (it was only based on minor corrections on your code); I tested that yesterday and noticed that it doesn't work, as you mentioned. I'm still looking for the most simple way to accomplish what you want here, other than using a huge select which is generated elsewhere.
0
bschwartingAuthor Commented:
great, thanks.  

i would think this would be simple (silly me), and similar to what we have, but maybe a new route is necessary.  not sure.
0
hujiCommented:
I'm still working on it, but I think we need a totally different approach, not something similar to what we have.
0
bschwartingAuthor Commented:
ok, thanks, let me know where you are going with this so I can do some research to assist.
0
hujiCommented:
Well, I'm really trying to find a solution to do it in one go. However, if we failed, is this okay for you to them in two steps? (Step one generates a big SQL command for you, with lots of INSERTs, and step two will be your copying that command pasting it in MySQL client and executing it). If this is something which happens rarely and by hand, a two-step solution would be fine for you, probably. However, if this is a regular automated process, I'd really avoid it. Tell me what you think.
0
bschwartingAuthor Commented:
no, this is a one time deal.

i'll generate this table out 10 years, then by then hopefully mysql 8.0 will be out and have an easy fix for us :)
0
hujiCommented:
Okay, the procedure you see in the code snippet below, generates a SQL statement itself, which successfully inserts all those dates to the table. So what you actually need to do is to:

   CALL sp_test(@query);
   SELECT @query;

And then copy the resulted text and run it separately.

That needs the resulted statement to be run by hand. I'm still looking for the best way to make it automatic.

By the way, if you want, you can extend the procedure, so it accepts the begining and ending date (so you don't need to change the procedure every time you do a different set of dates). If you can't, I can do it for you.

Wish I can help
Huji
CREATE PROCEDURE sp_test(OUT ut TEXT)
  BEGIN
    SET @begin = 'INSERT INTO Calendar(dt) VALUES ';
    SET @date = '2000-01-01';
    SET @temp = '';
    REPEAT
      SET @temp = concat(@temp, '(''', @date, '''), ');
      SET @date = ADDDATE(@date, INTERVAL 1 DAY);
      UNTIL @date > '2000-12-30' // THIS SHOULD BE ONE DAY BEFORE THE ENDING DAY
    END REPEAT;
    SET @temp = concat(@temp, '(''', @date, ''')');
    SET ut = concat(@begin, @temp);
  END

Open in new window

0
hujiCommented:
Okay, I felt I seem to be lazy not to add that last feature, so I did it! Now it works like this:

   call sp_test('2000-01-01', '2000-01-04', @z);

   select @z;

and then run the output by hand!
CREATE PROCEDURE sp_test(IN start_date DATE, IN end_date DATE, OUT result_text TEXT)
  BEGIN
    SET @begin = 'INSERT INTO Calendar(dt) VALUES ';
    SET @date = '2000-01-01';
    SET @max = SUBDATE(end_date, INTERVAL 1 DAY);
    SET @temp = '';
    REPEAT
      SET @temp = concat(@temp, '(''', @date, '''), ');
      SET @date = ADDDATE(@date, INTERVAL 1 DAY);
      UNTIL @date > @max
    END REPEAT;
    SET @temp = concat(@temp, '(''', @date, ''')');
    SET result_text = concat(@begin, @temp);
  END

Open in new window

0
hujiCommented:
Okay, here is the final solution:

Use the last procedure code I posted. Then, for automation of the procude do as such:

CALL sp_test('2000-01-01', '2000-01-04', @z);

SELECT @z;

PREPARE stmt_1 FROM @z;

EXECUTE stmt_1;

DEALLOCATE PREPARE stmt_1;

That's all!

huji
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bschwartingAuthor Commented:
ok, i get down to the prepare part, and i get this error:

mysql> PREPARE stmt_1 FROM @z;
ERROR 1146 (42S02): Table 'db219058388.Calendar' doesn't exist

i know calendar exists (see below), why am i getting this?

mysql> show columns from calendar;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| dt    | date | NO   |     |         |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

Open in new window

0
bschwartingAuthor Commented:
nevermind, it worked!!!

change Calendar to calendar.  i guess table names are case sensitive.

thanks so much!  you da man!
0
bschwartingAuthor Commented:
wow, did this with 10 years of data in 0.01 seconds!

great work!

again, thanks!
0
hujiCommented:
You're welcome. As you may know, you can update the procedure code so it will do the PREPARE and EXECUTE itself too. :)
0
bschwartingAuthor Commented:
oh yeah, didn't think about that.  good idea!

what's the easiest way to edit a procedure?  just copy/paste it all back in or is their a text editor in mysql?
0
hujiCommented:
It depends. If you have direct access to the MySQL server (for example, it is installed on a machine you can log into directly, or you can access it with SSH tunneling) then you can use MySQL's own "Client" software (I'm certain you know where to download MySQL Client!) or any other similar software. (I personally use Aqua Data Studio. It is freeware, and supports many databases like Microsft SQL server, MySQL, Oracle, etc. I use it because I work with several databases.)

But if you don't have direct access (for example, it is a MySQL server running on a shared web server, and you access it using CPanel or similar web-based interfaces) then you are limited to the web-based solutions such as PHPMyAdmin.

In order to "update" a procedure which already exists, there are two solutions:
  a) DROP the procedure and CREATE it again, with new defintion
  b) use ALTER PROCEDURE and update the procedure
The first is easier, but if some other parts of the program you are working on (like some web pages) rely on the procedure, they will stop working between the time you drop it and you create it again. The second doesn't have this limitation, but needs some SQL skills. You can always ask a new question in EE to get further help.

Cheers,
Huji
0
bschwartingAuthor Commented:
great, thanks, i think drop and create will work.
0
iameyeCommented:
Very useful!

I want to point out that the line:

SET @date = '2000-01-01';

should read

SET @date = start_date;
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.