insert into temporary table not working in mysql stored procedure
up vote
0
down vote
favorite
i have the following stored procedure:
create temporary table logData (table_name varchar(50), column_name varchar(50), changed_date date, changed_by char(10), old_value varchar(50), new_value varchar(50))
insert into logData SELECT table_name, column_name, changed_date, changed_by, old_value, new_value FROM log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value FROM reservation_flight_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value FROM ticket_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value FROM passenger_record_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
select * from logData order by changed_date;
In the above sql select statement empId
, startDate
and endDate
are the input parameters for this stored procedure. This is showing error please help? Thank You!
mysql
add a comment |
up vote
0
down vote
favorite
i have the following stored procedure:
create temporary table logData (table_name varchar(50), column_name varchar(50), changed_date date, changed_by char(10), old_value varchar(50), new_value varchar(50))
insert into logData SELECT table_name, column_name, changed_date, changed_by, old_value, new_value FROM log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value FROM reservation_flight_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value FROM ticket_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value FROM passenger_record_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
select * from logData order by changed_date;
In the above sql select statement empId
, startDate
and endDate
are the input parameters for this stored procedure. This is showing error please help? Thank You!
mysql
formatMySQL code
, instead of posting plain text
– Dipak
Nov 5 at 3:18
What error is it showing?
– Nick
Nov 5 at 3:34
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
i have the following stored procedure:
create temporary table logData (table_name varchar(50), column_name varchar(50), changed_date date, changed_by char(10), old_value varchar(50), new_value varchar(50))
insert into logData SELECT table_name, column_name, changed_date, changed_by, old_value, new_value FROM log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value FROM reservation_flight_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value FROM ticket_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value FROM passenger_record_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
select * from logData order by changed_date;
In the above sql select statement empId
, startDate
and endDate
are the input parameters for this stored procedure. This is showing error please help? Thank You!
mysql
i have the following stored procedure:
create temporary table logData (table_name varchar(50), column_name varchar(50), changed_date date, changed_by char(10), old_value varchar(50), new_value varchar(50))
insert into logData SELECT table_name, column_name, changed_date, changed_by, old_value, new_value FROM log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value FROM reservation_flight_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value FROM ticket_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value FROM passenger_record_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
select * from logData order by changed_date;
In the above sql select statement empId
, startDate
and endDate
are the input parameters for this stored procedure. This is showing error please help? Thank You!
mysql
mysql
edited Nov 5 at 3:32
Nick
17.9k41333
17.9k41333
asked Nov 5 at 2:54
Sagar Binod
52
52
formatMySQL code
, instead of posting plain text
– Dipak
Nov 5 at 3:18
What error is it showing?
– Nick
Nov 5 at 3:34
add a comment |
formatMySQL code
, instead of posting plain text
– Dipak
Nov 5 at 3:18
What error is it showing?
– Nick
Nov 5 at 3:34
format
MySQL code
, instead of posting plain text– Dipak
Nov 5 at 3:18
format
MySQL code
, instead of posting plain text– Dipak
Nov 5 at 3:18
What error is it showing?
– Nick
Nov 5 at 3:34
What error is it showing?
– Nick
Nov 5 at 3:34
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
I believe you are missing two semicolons: one at the end of the first statement and another at the end of the before-last line. You may rewrite it like the following:
CREATE temporary TABLE logData (table_name varchar(50),
column_name varchar(50),
changed_date date,
changed_by char(10),
old_value varchar(50),
new_value varchar(50));
INSERT INTO logData
SELECT table_name, column_name, changed_date, changed_by, old_value, new_value
FROM log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value
FROM reservation_flight_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value
FROM ticket_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value
FROM passenger_record_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate;
SELECT * FROM logData ORDER BY changed_date;
I have used semicoloms also but it is not working but when i run without insert into statement it works but i need to store data in temporary table to sort them datewise.. please help me?
– Sagar Binod
Nov 5 at 11:31
What's the error message are you getting?
– TeeKea
Nov 5 at 14:46
i forget to use begin ... end statement inside the procedure and it was showing error. Now it worked. Next problem is when i execute the above procedure in phpmyadmin it displays result but when i execute from java it returns nothing.. Please help ..?
– Sagar Binod
Nov 5 at 14:58
Can you update your question please?
– TeeKea
Nov 5 at 15:01
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
I believe you are missing two semicolons: one at the end of the first statement and another at the end of the before-last line. You may rewrite it like the following:
CREATE temporary TABLE logData (table_name varchar(50),
column_name varchar(50),
changed_date date,
changed_by char(10),
old_value varchar(50),
new_value varchar(50));
INSERT INTO logData
SELECT table_name, column_name, changed_date, changed_by, old_value, new_value
FROM log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value
FROM reservation_flight_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value
FROM ticket_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value
FROM passenger_record_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate;
SELECT * FROM logData ORDER BY changed_date;
I have used semicoloms also but it is not working but when i run without insert into statement it works but i need to store data in temporary table to sort them datewise.. please help me?
– Sagar Binod
Nov 5 at 11:31
What's the error message are you getting?
– TeeKea
Nov 5 at 14:46
i forget to use begin ... end statement inside the procedure and it was showing error. Now it worked. Next problem is when i execute the above procedure in phpmyadmin it displays result but when i execute from java it returns nothing.. Please help ..?
– Sagar Binod
Nov 5 at 14:58
Can you update your question please?
– TeeKea
Nov 5 at 15:01
add a comment |
up vote
0
down vote
accepted
I believe you are missing two semicolons: one at the end of the first statement and another at the end of the before-last line. You may rewrite it like the following:
CREATE temporary TABLE logData (table_name varchar(50),
column_name varchar(50),
changed_date date,
changed_by char(10),
old_value varchar(50),
new_value varchar(50));
INSERT INTO logData
SELECT table_name, column_name, changed_date, changed_by, old_value, new_value
FROM log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value
FROM reservation_flight_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value
FROM ticket_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value
FROM passenger_record_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate;
SELECT * FROM logData ORDER BY changed_date;
I have used semicoloms also but it is not working but when i run without insert into statement it works but i need to store data in temporary table to sort them datewise.. please help me?
– Sagar Binod
Nov 5 at 11:31
What's the error message are you getting?
– TeeKea
Nov 5 at 14:46
i forget to use begin ... end statement inside the procedure and it was showing error. Now it worked. Next problem is when i execute the above procedure in phpmyadmin it displays result but when i execute from java it returns nothing.. Please help ..?
– Sagar Binod
Nov 5 at 14:58
Can you update your question please?
– TeeKea
Nov 5 at 15:01
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
I believe you are missing two semicolons: one at the end of the first statement and another at the end of the before-last line. You may rewrite it like the following:
CREATE temporary TABLE logData (table_name varchar(50),
column_name varchar(50),
changed_date date,
changed_by char(10),
old_value varchar(50),
new_value varchar(50));
INSERT INTO logData
SELECT table_name, column_name, changed_date, changed_by, old_value, new_value
FROM log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value
FROM reservation_flight_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value
FROM ticket_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value
FROM passenger_record_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate;
SELECT * FROM logData ORDER BY changed_date;
I believe you are missing two semicolons: one at the end of the first statement and another at the end of the before-last line. You may rewrite it like the following:
CREATE temporary TABLE logData (table_name varchar(50),
column_name varchar(50),
changed_date date,
changed_by char(10),
old_value varchar(50),
new_value varchar(50));
INSERT INTO logData
SELECT table_name, column_name, changed_date, changed_by, old_value, new_value
FROM log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value
FROM reservation_flight_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value
FROM ticket_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value
FROM passenger_record_log
WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate;
SELECT * FROM logData ORDER BY changed_date;
answered Nov 5 at 3:50
TeeKea
1
1
I have used semicoloms also but it is not working but when i run without insert into statement it works but i need to store data in temporary table to sort them datewise.. please help me?
– Sagar Binod
Nov 5 at 11:31
What's the error message are you getting?
– TeeKea
Nov 5 at 14:46
i forget to use begin ... end statement inside the procedure and it was showing error. Now it worked. Next problem is when i execute the above procedure in phpmyadmin it displays result but when i execute from java it returns nothing.. Please help ..?
– Sagar Binod
Nov 5 at 14:58
Can you update your question please?
– TeeKea
Nov 5 at 15:01
add a comment |
I have used semicoloms also but it is not working but when i run without insert into statement it works but i need to store data in temporary table to sort them datewise.. please help me?
– Sagar Binod
Nov 5 at 11:31
What's the error message are you getting?
– TeeKea
Nov 5 at 14:46
i forget to use begin ... end statement inside the procedure and it was showing error. Now it worked. Next problem is when i execute the above procedure in phpmyadmin it displays result but when i execute from java it returns nothing.. Please help ..?
– Sagar Binod
Nov 5 at 14:58
Can you update your question please?
– TeeKea
Nov 5 at 15:01
I have used semicoloms also but it is not working but when i run without insert into statement it works but i need to store data in temporary table to sort them datewise.. please help me?
– Sagar Binod
Nov 5 at 11:31
I have used semicoloms also but it is not working but when i run without insert into statement it works but i need to store data in temporary table to sort them datewise.. please help me?
– Sagar Binod
Nov 5 at 11:31
What's the error message are you getting?
– TeeKea
Nov 5 at 14:46
What's the error message are you getting?
– TeeKea
Nov 5 at 14:46
i forget to use begin ... end statement inside the procedure and it was showing error. Now it worked. Next problem is when i execute the above procedure in phpmyadmin it displays result but when i execute from java it returns nothing.. Please help ..?
– Sagar Binod
Nov 5 at 14:58
i forget to use begin ... end statement inside the procedure and it was showing error. Now it worked. Next problem is when i execute the above procedure in phpmyadmin it displays result but when i execute from java it returns nothing.. Please help ..?
– Sagar Binod
Nov 5 at 14:58
Can you update your question please?
– TeeKea
Nov 5 at 15:01
Can you update your question please?
– TeeKea
Nov 5 at 15:01
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53147723%2finsert-into-temporary-table-not-working-in-mysql-stored-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
format
MySQL code
, instead of posting plain text– Dipak
Nov 5 at 3:18
What error is it showing?
– Nick
Nov 5 at 3:34