Solved

create table procedure

Posted on 2010-09-21
14
384 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
  • 10
  • 4
14 Comments
 
LVL 59

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 59

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
 
LVL 59

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 59

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 59

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 59

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 59

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 59

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 59

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 59

Accepted Solution

by:
Kevin Cross earned 500 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now