[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

fill calendar table - mysql syntax

Posted on 2008-02-02
27
Medium Priority
?
2,504 Views
Last Modified: 2013-12-12
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

0
Comment
Question by:bschwarting
  • 14
  • 11
  • +1
27 Comments
 
LVL 14

Expert Comment

by:huji
ID: 20806156
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
 
LVL 1

Author Comment

by:bschwarting
ID: 20806163
so what is the best way to create a calendar table?
0
 
LVL 14

Expert Comment

by:huji
ID: 20806164
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 20806165
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
 
LVL 1

Author Comment

by:bschwarting
ID: 20806216
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
 
LVL 1

Author Comment

by:bschwarting
ID: 20806470
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
 
LVL 1

Author Comment

by:bschwarting
ID: 20807097
idea's as to what's wrong now?  can't figure out the syntax on this.
0
 
LVL 14

Expert Comment

by:huji
ID: 20808168
Dont do this:

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


Instead, try this:

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

Huji
0
 
LVL 1

Author Comment

by:bschwarting
ID: 20809804
i tried both, neither worked.
0
 
LVL 1

Author Comment

by:bschwarting
ID: 20814483
anyone have a mysql server to test this on and assist?
0
 
LVL 14

Expert Comment

by:huji
ID: 20816341
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
 
LVL 1

Author Comment

by:bschwarting
ID: 20816355
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
 
LVL 14

Expert Comment

by:huji
ID: 20816816
I'm still working on it, but I think we need a totally different approach, not something similar to what we have.
0
 
LVL 1

Author Comment

by:bschwarting
ID: 20817260
ok, thanks, let me know where you are going with this so I can do some research to assist.
0
 
LVL 14

Expert Comment

by:huji
ID: 20818522
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
 
LVL 1

Author Comment

by:bschwarting
ID: 20818645
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
 
LVL 14

Expert Comment

by:huji
ID: 20818910
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
 
LVL 14

Expert Comment

by:huji
ID: 20818982
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
 
LVL 14

Accepted Solution

by:
huji earned 2000 total points
ID: 20819046
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
 
LVL 1

Author Comment

by:bschwarting
ID: 20820422
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
 
LVL 1

Author Comment

by:bschwarting
ID: 20820530
nevermind, it worked!!!

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

thanks so much!  you da man!
0
 
LVL 1

Author Comment

by:bschwarting
ID: 20823068
wow, did this with 10 years of data in 0.01 seconds!

great work!

again, thanks!
0
 
LVL 14

Expert Comment

by:huji
ID: 20830829
You're welcome. As you may know, you can update the procedure code so it will do the PREPARE and EXECUTE itself too. :)
0
 
LVL 1

Author Comment

by:bschwarting
ID: 20832000
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
 
LVL 14

Expert Comment

by:huji
ID: 20833253
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
 
LVL 1

Author Comment

by:bschwarting
ID: 20833288
great, thanks, i think drop and create will work.
0
 

Expert Comment

by:iameye
ID: 22602193
Very useful!

I want to point out that the line:

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

should read

SET @date = start_date;
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question