[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

create table procedure

Posted on 2010-09-21
14
Medium Priority
?
391 Views
Last Modified: 2012-05-10
i need a mysql procedure that creates a table with 25 rows first row is date other 24 is hours like 0 1 2 3 ... I will send the procedure only 1 value it is table name and it will also will check if table already exist and will return a spesific error code or succes info    thanks ...
0
Comment
Question by:3XLcom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 4
14 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33728769
3XLcom,

Do you really mean "rows" above OR are you looking to create a column called `date` and then columns `0`, `1`, `2`, ... `23`?  Rows would be creating a table with one column called `date` and then store 24 rows of :
'2010-09-21 00:00'
'2010-09-21 01:00'
...
'2010-09-21 23:00'

Just want to ensure we are on the same page.

If I am correct, you simply need to build a dynamic SQL string using the table name variable you pass and execute that dynamic SQL.

Kevin
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33728786
I use something similar in my article here, so you can read section "Dynamic" for an example:
http://www.experts-exchange.com/Database/MySQL/A_3638-A-MySQL-Tidbit-Dynamic-Export-To-XML-Stored-Procedure.html

Otherwise, check the manual page:
http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html
0
 

Author Comment

by:3XLcom
ID: 33729116
no there will be a matris like this
every they we record 24 value so i want to store one line for each day and we have 650 different recorder for each day so i want to create a new table for each recorder.

so it will be sth. like this one row for date 24 row for each hour and every line will be one day's information
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33730149
Okay, then you want:
CREATE  TABLE `recorder_name` (
  `date` DATE NOT NULL,
  `0` INT NULL ,
  ...,
  `23` INT NULL,

PRIMARY KEY (`date`) );

When building the string, you can additional tighten the code by using a loop to do the 0 - 23 column entries, but even if you type them in manually (using copy and paste), you can set the recorder_name value using the variable and then execute the SQL string per the references I gave you previously.  They also give you syntax for creating procedure -- give it a try and post back if you get an error -- think this will give you a better understanding of the code if you try it out yourself.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33730155
Another note, you will want to set the data type of `0` to `23` columns to what is appropriate -- showed with INT as an example.  For `date` the DATE data type should be good since you just want the date and don't need the time portion which you are breaking up into each hour.
0
 

Author Comment

by:3XLcom
ID: 33730162
but i want to do it with a loop and check if table exist also
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33730163
Yes, sorry I left that part out accidentally.

Change:
CREATE TABLE

to:
CREATE TABLE IF NOT EXISTS
0
 

Author Comment

by:3XLcom
ID: 33730254
yes but i want to know this if it is exist i mean procedure will give me a spesific answer

I have it
I do it

sth. like this

so i need an if statement for create table and need a loop for 24 hours' rows
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33730269
Not sure I understood what you last post is trying to say.  Based on your question and details you have given me, I have provided you with code you will need.  If you are having a specific error, please post the code you have and error you are getting !
0
 

Author Comment

by:3XLcom
ID: 33730280
Please check the question carrefully
i write on there at the end :
it will also will check if table already exist and will return a spesific error code or succes

so i need to check if table exist and return a value if it exist so instead of this :

CREATE TABLE IF NOT EXISTS

i need another if statement which will produce an answer to stored procedure

and also i ask for a loop not for 24 create row command
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33730365
http:#a33730149 << mentioned using a WHILE loop
http:#a33728786 << has code examples of IF, dynamic SQL (prepared statements)

I guess you are wanting someone to write all the code for you ?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33730383
Here is the WHILE loop manual >> http://dev.mysql.com/doc/refman/5.0/en/while-statement.html
Again, I will say, I did NOT intend on writing the initial code for you as you will (1) learn nothing that way and (2) have to modify it all anyway to fit your actual data types and business logic you want.  You need to take what I have told you and try this for yourself and then post back if you have an issue.  
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33730741
3XLcom,

Looking back, I realize you may be having difficulty even getting started, so here is a stub of what the procedure looks like.  Once you have had a chance to add-in your code -- then post back issues you are having.

You call it like this:
call create_recorder_tbl('recorder', @message);
select @message;

You may want to consider a function which returns a varchar so you don't have to select the output variable as a separate step.
DROP PROCEDURE IF EXISTS create_recorder_tbl;
DELIMITER $$
CREATE PROCEDURE create_recorder_tbl(
   IN tablename VARCHAR(255), 
   OUT message VARCHAR(255)
) BEGIN

-- check if table exists using information schema
IF EXISTS(
      SELECT null
      FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = tablename
      -- AND SCHEMA_NAME = 'your_database_name'
   ) THEN
   -- send error/info message that table exists.
   SET message = CONCAT(tablename, ' already exists!');
ELSE
   -- Build dynamic SQL string.
      -- Create base string.
      -- Append `0` - `23` columns via loop.
      -- Append end portion of create DDL.

   -- Execute dynamic SQL string.
   PREPARE dySQL FROM @sqlstring;
   EXECUTE dySQL;
   DEALLOCATE PREPARE dySQL;
   
   -- set success/info mesage.
   SET message = CONCAT('Successfully created ', tablename, '!');
END IF;

END$$
DELIMITER ;

Open in new window

0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 33766579
I see you are using the code I gave you here in some of your other questions, so please advise what else you are needing here.

Thanks!

Kevin
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

649 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