I am trying to add two columns in an already existing table Contact_Info using MySQL.When I am doing this using MySQL command line client, its wrking perfectly. But if I try to do this in a JDBC program, writing the following code
String alter_query = "alter table Contact_Info"
+ "add column Phone varchar(30) not null after Last_Name,"
+ "add column Email varchar(50) not null after Phone";
its giving me error saying you have syntax error in your SQL.
I tried this query string to execute with a ';' appended and without it;but of no use
String alter_query = "alter table Contact_Info"
+ "add column Phone varchar(30) not null after Last_Name,"
+ "add column Email varchar(50) not null after Phone";
Can anyone tell me where I am wrong? What is mistake in my syntax?
It seems like your code syntax is almost correct but I found a little issue in your code.
You didn't add a space at the end of each line and that is the reason for the syntax error, because all lines will be merged without any space, like this alter table Contact_Infoadd column. As you can see Contact_Info and add is connected without space and that is a syntax error.
To avoid the issue, please add a SPACE character as shown here:
String alter_query = "ALTER TABLE Contact_Info " // ⬅️ Notice SPACE character at the end.
+ "ADD COLUMN Phone VARCHAR(30) NOT NULL AFTER Last_Name, "
+ "ADD COLUMN Email VARCHAR(50) NOT NULL AFTER Phone";
Alternatively, use the text blocks feature.
In a text block, each line of text remains intact. The line-terminator is preserved. No need for a SPACE at the end. Indeed, your IDE may even suggest removing any whitespace characters from the ends.
String alter_query = """
ALTER TABLE Contact_Info
ADD COLUMN Phone VARCHAR(30) NOT NULL AFTER Last_Name,
ADD COLUMN Email VARCHAR(50) NOT NULL AFTER Phone
""";
For SQL, such multiline text is acceptable. Other languages being embedded may not accept multiline text, so you would instead use concatenation.