I am trying to insert HTML content into my MySQL table. When I execute this query using MySQL Workbench, it is inserting without any error, but when I try to insert it through JDBC code, it is throwing exception:
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 '' table { border-collapse: collapse; } table, td, th { border: 1px s' at line 2"
My insert statement is:
insert into event_template (HTML_ID,CONTENT,ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE)
values('Employee','<div><style> table { border-collapse: collapse; } table, td, th { border: 1px solid black; }
table.row-table > tbody > tr:not(:last-of-type) > td {
border-bottom: 3px solid black;
}</style>
<span><b><span>Dear User,</span></b></span>
<span><br></span>
</div>
<div>
<span><br></span>
</div>
<div>
<span>This html span.</span>
<span><br></span>
</div>
<div>
<span>The is span2 between {fromDate} to {toDate}. </span>
</div>
<br>
<span>This is timestamp {timestamp}.</span>
<br>
<br>
<table width="100%" class="row-table">
<thead>
<tr>
<td>emp #</td>
<td>emp Name</td>
<td>emp email</td>
<td>DOB</td>
<td>Age</td>
</tr>
</thead>
<tbody>
<tr>
<td>{emp}</td>
<td>{empName}</td>
<td>{empEmail}</td>
<td>{empDob}</td>
<td>{empAge}</td>
</tr>
</tbody>
</table>
<div>
<br>
</div>
<div>
Thank you,
</div>
<b><p>User Support</p></b>
<br><p></p>',true,'admin',now(),'admin',now());
Code to execute query through JDBC, this runScript() will call execCommand() :
private void runScript(Connection conn, Reader reader) throws IOException, SQLException {
StringBuffer command = null;
try {
LineNumberReader lineReader = new LineNumberReader(reader);
String line;
while ((line = lineReader.readLine()) != null) {
if (command == null) {
command = new StringBuffer();
}
String trimmedLine = line.trim();
final Matcher delimMatch = delimP.matcher(trimmedLine);
if (trimmedLine.length() < 1 || trimmedLine.startsWith("//")) {
// Do nothing
} else if (delimMatch.matches()) {
setDelimiter(delimMatch.group(2), false);
} else if (trimmedLine.startsWith("--")) {
println(trimmedLine);
} else if (trimmedLine.length() < 1 || trimmedLine.startsWith("--")) {
// Do nothing
} else if (!fullLineDelimiter && trimmedLine.endsWith(getDelimiter())
|| fullLineDelimiter && trimmedLine.equals(getDelimiter())) {
command.append(line.substring(0, line.lastIndexOf(getDelimiter())));
command.append(" ");
this.execCommand(conn, command, lineReader);
command = null;
} else {
command.append(line);
command.append("\n");
}
}
if (command != null) {
this.execCommand(conn, command, lineReader);
}
if (!autoCommit) {
conn.commit();
}
} catch (Exception e) {
throw new IOException(String.format("Error executing '%s': %s", command, e.getMessage()), e);
} finally {
conn.rollback();
flush();
}
}
private void execCommand(Connection conn, StringBuffer command, LineNumberReader lineReader) throws SQLException {
Statement statement = conn.createStatement();
println(command);
boolean hasResults = false;
try {
hasResults = statement.execute(command.toString());
} catch (SQLException e) {
final String errText = String.format("Error executing '%s' (line %d): %s", command,
lineReader.getLineNumber(), e.getMessage());
if (stopOnError) {
throw new SQLException(errText, e);
} else {
println(errText);
}
}
if (autoCommit && !conn.getAutoCommit()) {
conn.commit();
}
ResultSet rs = statement.getResultSet();
if (hasResults && rs != null) {
ResultSetMetaData md = rs.getMetaData();
int cols = md.getColumnCount();
for (int i = 1; i <= cols; i++) {
String name = md.getColumnLabel(i);
print(name + "\t");
}
println("");
while (rs.next()) {
for (int i = 1; i <= cols; i++) {
String value = rs.getString(i);
print(value + "\t");
}
println("");
}
}
try {
statement.close();
} catch (Exception e) {
// Ignore to workaround a bug in Jakarta DBCP
}
}
Please guide me to fix it.