Written by coregps on Wednesday, January 18th, 2006 in MySQL.
If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!
When configuring apache 2.0.55 with php 5.0.4 on Windows Server 2003, I got the error message: ?PHP Startup: Unable to load dynamic library ?D:\Apache2\php\ext\php_mysql.dll? - The specified module could not be found.? To make it work, just copy the libmysql.dll come with the PHP distribution into c:\windows\system32.
Written by coregps on Thursday, October 27th, 2005 in Java, MySQL.
When I recently upgraded to MySQL 4.1.10 and Connector/J 3.1.8, I got the following Exception:
In most cases, the exception is raised in the following two situtations:
1. The auto increment field is incremented using ‘’.
2. The date column is updated using ‘’.
In order to recreate the problem, I created two tables, named “product” and “orders”. The former is used to test the auto increment field, the latter is used to test the date filed.
CREATE TABLE `product` (
`productid` int(10) NOT NULL auto_increment,
`productname` varchar(50) NOT NULL default ‘’,
`price` decimal(10,2) NOT NULL default ‘0.00′,
PRIMARY KEY (`productid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `orders` (
`orderno` int(10) NOT NULL default ‘0′,
`orderdate` date default NULL,
PRIMARY KEY (`orderno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The following is the result of my test run from the mysql command line interface.
mysql> insert into product values('’, ‘test’, 100.00);
Query OK, 1 row affected, 1 warning (0.36 sec)
mysql> show warnings;
+———+——+————————————————+
| Level | Code | Message |
+———+——+————————————————+
| Warning | 1265 | Data truncated for column ‘productid’ at row 1 |
+———+——+————————————————+
1 row in set (0.00 sec)
mysql> insert into product values(null, ‘test again’, 20.00);
Query OK, 1 row affected (0.05 sec)
mysql> insert into orders values(1, ‘’);
Query OK, 1 row affected, 1 warning (0.05 sec)
mysql> show warnings;
+———+——+————————————————+
| Level | Code | Message |
+———+——+————————————————+
| Warning | 1265 | Data truncated for column ‘orderdate’ at row 1 |
+———+——+————————————————+
1 row in set (0.00 sec)
mysql> insert into orders values(2, null);
Query OK, 1 row affected (0.05 sec)
As you can see, when I changed the insert statement from insert('’, … ) to insert(null, …), it didn’t generate a warning. Finally, I found that the problem was already mentioned in the Connector/J documentation:
Prior to MySQL-4.1, the server itself couldn’t report truncation of values. Starting with Connector/J 3.1.0, the JDBC driver will issue warnings or throw DataTruncation exceptions as is required by the JDBC specification unless the connection was configured not to do so by using the property “jdbcCompliantTruncation” and setting it to “false”.
jdbcCompliantTruncation — Should the driver throw java.sql.DataTruncation exceptions when data is truncated as is required by the JDBC specification when connected to a server that supports warnings(MySQL 4.1.0 and newer)?
When I set jdbcCompliantTruncation=false in my connection url something like this:
It works well.
In addition, when updating a date field use ‘’, the date column is overwritten with 0000-00-00, Which will cause the following exception when we retrieve the data using rs.getDate(”orderdate”):
This problem is also mentioned in the Connector/J documentation:
Datetimes with all-zero components (’0000-00-00 …’) - These values can not be represented reliably in Java. Connector/J 3.0.x always converted them to NULL when being read from a ResultSet.
Connector/J 3.1 throws an exception by default when these values are encountered as this is the most correct behavior according to the JDBC and SQL standards. This behavior can be modified using the ‘ zeroDateTimeBehavior ‘ configuration property. The allowable values are: ‘exception’ (the default), which throws a SQLException with a SQLState of ‘S1009′, ‘convertToNull’, which returns NULL instead of the date, and ’round’, which rounds the date to the nearest closest value which is ‘0001-01-01′.
I solved the problem by setting the ‘zeroDateTimeBehavior’ property to ‘convertToNull’ as follows:
Written by coregps on Saturday, August 6th, 2005 in Java, MySQL.
In my recent post I wrote something about import excel data into mysql database. In this entry, I want to talk about the steps I take to export the data of a mysql database into an excel file.
1. Create excel templates and put them into WEB-INF/classes directory.
2. Create a writable workbook using ServletOutputStream and the excel template.
3. Write data into the workbook just created.
4. Write out the workbook.
The following is the Java Bean I’ve created used to export data from MySQL to excel:
package com.esurfer.dbutil;
import java.io.File;
import java.io.OutputStream;
import java.net.URL;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class DataExport {
/** excel template file */
private String templatefile = null;
/** data to export */
private ArrayList datasource = null;
/**
* column names, excel columns should be in
* the same order (and number) of table fields.
*/
private String[] columnnames = null;
/** response to write to */
private HttpServletResponse response = null;
/** output file name */
private String outputfile;
/** row number begin to write data */
private int rowno;
public void setColumnnames(String[] columnnames) {
this.columnnames = columnnames;
}
public void setOutputfile(String outputfile) {
this.outputfile = outputfile;
}
public void setDatasource(ArrayList datasource) {
this.datasource = datasource;
}
public void setTemplatefile(String templatefile) {
this.templatefile = templatefile;
}
public void setResponse(HttpServletResponse response) {
this.response = response;
}
public void setRowno(int rowno) {
this.rowno = rowno;
}
/**
* get file saved in WEB-INF/classes directory.
*
* @param fileName file name to be retrieved
* @return File or null if the file is not exist
*/
public File getResource(String fileName) {
URL url = this.getClass().getResource(”/” + fileName);
return url != null ? new File(url.getFile()): null;
}
/**
* Export data to excel file
*/
public void export() {
Workbook workbook;
WritableWorkbook w;
Label label;
int currentRow = rowno;
try {
OutputStream out = null;
response.reset();
response.setContentType(”application/vnd.ms-excel”);
response.setHeader(”Content-Disposition”, “attachment; filename=” + outputfile);
// creates a readable spreadsheet using the excel template
workbook = Workbook.getWorkbook(getResource(templatefile));
// obtain a writable version of this spreadsheet
out = response.getOutputStream();
w = Workbook.createWorkbook(out, workbook);
WritableSheet sheet = w.getSheet(0);
WritableFont font = new WritableFont(WritableFont.createFont(”Arial”), 10);
WritableCellFormat format = new WritableCellFormat(font);
format.setAlignment(Alignment.RIGHT);
format.setVerticalAlignment(VerticalAlignment.JUSTIFY);
format.setBorder(Border.ALL, BorderLineStyle.THIN);
// write data to excel
Iterator iterator = datasource.iterator();
Map map = null;
int cols = columnnames.length;
while (iterator.hasNext()) {
sheet.insertRow(currentRow);
map = (Map)iterator.next();
for (int i = 0; i < cols; i++) {
label = new Label(i, currentRow, “” + map.get(columnnames[i]).toString());
label.setCellFormat(format);
sheet.addCell(label);
}
currentRow++;
}
w.write();
w.close();
if (out != null) out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
Using the Java Bean in the jsp page:
<%@ page pageEncoding=”UTF-8″ contentType=”text/html; charset=UTF-8″
%><%@ page import=”com.esurfer.product.*”
%><jsp:useBean id=”exp” class=”com.esurfer.dbutil.DataExport”/><%
String sExport = request.getParameter(”export”);
if (sExport != null) {
exp.setTemplatefile(”producttpl.xls”);
exp.setOutputfile(”ProductRep.xls”);
exp.setColumnnames(new String[]{”pid”, “pname”, “amount”, “price”});
exp.setRowno(2);
exp.setDatasource(Product.getProductList());
exp.setResponse(response);
exp.export();
return;
}
%>
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01//EN” “http://www.w3.org/TR/html4/strict.dtd“>
<html>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=UTF-8″/>
<title>Export data from MySQL to excel</title>
</head>
<body>
<form name=”expform” method=”post”>
<input type=”hidden” name=”export” value=”true” />
<input type=”submit” value=”Export” />
</form>
</body>
</html>
Make sure there are no breaklines at the end of the lines highlighted in blue. Otherwise, you will get the following error message:
Written by coregps on Friday, August 5th, 2005 in Java, MySQL.
Many times we need to import data from excel to MySQL or other databases. This can be done using Java Excel API - A Java API to read, write, and modify Excel spreadsheets, which can be downloaded from http://sourceforge.net/project/showfiles.php?group_id=79926. Below is the steps I performed:
1. Using a multipart HTML form to upload the Excel file to import.
2. Creating a spreadsheet from the ServletInputStream.
3. Read data from the Excel spreadsheet and insert into MySQL database.
For ease of reuse, I write a Java Bean to handle the reading of the Excel file and import the data into MySQL database.
package com.esurfer.dbutil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import javax.servlet.ServletInputStream;
import javax.servlet.http.HttpServletRequest;
import com.esurfer.db.DBConn;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
public class DataImport {
private HttpServletRequest request = null;
// column names of the MySQL table
// which should be in the same order as the excel columns
private String[] columnNames = null;
// table name to import to
private String tableName = null;
static Connection conn = null;
static PreparedStatement pst = null;
public void setColumnNames(String[] columnNames) {
this.columnNames = columnNames;
}
public void setRequest(HttpServletRequest request) {
this.request = request;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
// import data from excel to mysql
public boolean importdata() {
boolean result = false;
try {
ServletInputStream is = request.getInputStream();
byte[] junk = new byte[1024];
int bytesRead = 0;
// strip off the HTTP information from input stream
// the first four lines are request junk
bytesRead = is.readLine(junk, 0, junk.length);
bytesRead = is.readLine(junk, 0, junk.length);
bytesRead = is.readLine(junk, 0, junk.length);
bytesRead = is.readLine(junk, 0, junk.length);
// create the workbook object from the ServletInputStream
Workbook workbook = Workbook.getWorkbook(is);
Sheet sheet = workbook.getSheet(0);
Cell cell = null;
// prepare the insert sql statement
StringBuffer sql = new StringBuffer(”INSERT INTO “).append(tableName).append(”(”);
StringBuffer params = new StringBuffer(”VALUES(”);
int cols = columnNames.length;
for (int i = 0; i < cols; i++) {
sql.append(columnNames[i]).append(”,”);
params.append(”?,”);
}
sql = sql.deleteCharAt(sql.length() - 1).append(”)”).append(params.deleteCharAt(params.length() - 1)).append(”)”);
// get database connection
conn = DBConn.getConnection();
conn.setAutoCommit(false);
pst = conn.prepareStatement(sql.toString());
SimpleDateFormat df = new SimpleDateFormat(”yyyy-MM-dd”);
// read data from the excel spreadsheet
// the code here assumes that the data begin in row 2 [A2]
int rows = sheet.getRows();
for (int i = 1; i < rows; i++) {
for (int j = 0; j < cols - 1; j++) {
cell = sheet.getCell(j, i);
if (cell.getType() == CellType.DATE) {
DateCell datecell = (DateCell)cell;
pst.setString(j + 1, df.format(datecell.getDate()));
} else {
pst.setString(j + 1, cell.getContents());
}
}
pst.executeUpdate();
}
conn.commit();
conn.setAutoCommit(true);
result = true;
// close the workbook and free up memory
workbook.close();
} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException ex) {
}
result = false;
} finally {
closeDB();
}
return result;
}
private static void closeDB() {
try {
if (pst != null) {
pst.close();
pst = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (Exception e) {}
}
}
Using the Java Bean in the jsp page:
<%@ page contentType=”text/html; charset=utf-8″ language=”java” errorPage=”" %>
<jsp:useBean id=”imp” class=”com.esurfer.dbutil.DataImport” />
<%
String operate = request.getParameter(”imp”);
if (”true”.equals(operate)) {
imp.setRequest(request);
imp.setTableName(”products”);
imp.setColumnNames(new String[]{”pid”, “pname”, “amount”, “price”});
imp.importdata();
}
%>
<html>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″>
<title>Import data from excel to MySQL</title>
</head>
<body>
<form name=”impform” method=”post” action=”?imp=true” enctype=”multipart/form-data”>
Select the Excel file to import?<br />
<input name=”filename” type=”file” size=”80″ maxlength=”40″>
<impur type=”submit” value=”Import” />
</form>
</body>
</html>
The excel file is something like this:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | pid | pname | amount | price |
| 2 | 1 | Computer | 100 | 4000 |
| 3 | 2 | Software | 200 | 3000 |
How do you achieve this? Any good ideas will be appreciated.
Written by coregps on Tuesday, July 19th, 2005 in Java, MySQL.
It took me a whole day to make my web application work with UTF-8. I feel that several key things should be noted. Perhaps it’ll help someone.
1. Set the default file encoding to UTF-8
2. Setup MySQL to use UTF-8 as the default encoding(edit the MySQL ini file my.ini)
[client]
port=3306
default-character-set=utf8
[mysqld]
default-character-set=utf8
3. Create the database and table(s) using UTF8 encoding
CREATE DATABASE demodb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE `test` (
`id` tinyint(1) NOT NULL default ‘0′,
`title` varchar(20) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
4. Add the following page directive to all jsp pages
5. Set the request encoding in the JSP to UTF-8
6. Always use post method in forms to submit data by adding method=”post” to <form> tag
