CSVREAD can't work with CallableStatement ?

[cheris...@gmail.com]

Hi..

I use stored procedure "CSVREAD " with CallableStatement ,but it
can't work,here is the error information:

error on the execute the stored procedure
org.h2.jdbc.JdbcSQLException: Parameter number fileName is not set;
SQL statement: select * from CSVREAD( ? ) [90012-46]
org.h2.jdbc.JdbcSQLException: Parameter number fileName is not set;
SQL statement: select * from CSVREAD( ? ) [90012-46]
at org.h2.message.Message.addSQL(Message.java:348)
at org.h2.command.Parser.parse(Parser.java:227)
at org.h2.command.Parser.prepareCommand(Parser.java:192)
at org.h2.engine.Session.prepareLocal(Session.java:173)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:159)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:86)
at java.lang.Thread.run(Thread.java:595)

and program is:

Session session =
HibernateUtil.getSessionFactory().getCurrentSession();
Transaction tx = session.beginTransaction();
Connection conn = session.connection();
String CVSDirectory = "d:\\DBBackup\\";
try {
CallableStatement cs = conn.prepareCall("select * from
CSVREAD( ? )");
cs.setString(1,"d:/DBBackup/test.csv");
ResultSet rs=cs.executeQuery();

ResultSetMetaData meta = rs.getMetaData();
while (rs.next()) {
for (int i = 0; i < meta.getColumnCount(); i++) {
System.out.println(meta.getColumnLabel(i + 1) + ": "
+ rs.getString(i + 1));
}
System.out.println();
}
rs.close();
cs.close();
tx.commit();
} catch (SQLException e) {
System.out.println("error on the execute the stored procedure"+e);
return;
} finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error on the shutdown the connnection"+e);
return;
}
}
}

thanks in advance!!

[Thomas Mueller]

Hi,

The problem is that without knowing the column names, the statement
can not be 'compiled'. Currently the statement is always compiled when
you call prepareStatement. Example:

SELECT ID, NAME FROM CSVREAD(?) A, CSVREAD(?) B

In this case it is not possible to know if the columns ID and NAME are
from table A or B. So, at the moment, you can not use a parameter for
the file name. I will change it so you can optionally provide the
column names for this case:

CSVREAD(?, 'ID,NAME')

This doesn't work at the moment as well, however it should work. Just
now, only this works: CSVREAD('test.csv').

I hope this helps,
Thomas

[cheris...@gmail.com]

Hi, Thomas

Thanks for reply,and also thank you for delivering a Csv util
class,now I use this util class perform the import and export from cvs
file.But there is a small problem----when import and export the data
alone,it works fine,but when perform export just after import ,there
are a duplicate row of column name in the export file ,just as below:

PERSON_ID,NAME,SEX,PINYIN,MARRY,NATIONAL_NAME,IDNUMBER,BIRTH,JOB,HOMETEL,MOBILETEL,HOMEADDR,WORKUNIT
"PERSON_ID","NAME","SEX","PINYIN","MARRY","NATIONAL_NAME","IDNUMBER","BIRTH","JOB","HOMETEL","MOBILETEL","HOMEADDR","WORKUNIT"
....data.....
.....data...

Thanks again!!

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import org.h2.tools.Csv;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.work.exception.DataException;
import com.work.hibernate.HibernateUtil;

/*
* now can import and export the data alone,but can't work together(in
the export file,there are a duplicate row of column name),the reason
need to be found....
*/
public class CSVUtil {
private static Csv csv = Csv.getInstance();

public int exportToCsvFile(String fileName, String sql, String
charset)
throws DataException {

Session session =
HibernateUtil.getSessionFactory().getCurrentSession();
Transaction tx = session.beginTransaction();
Connection conn = session.connection();

try {
int count = csv.write(conn, fileName, sql, charset);
tx.commit();
return count;
} catch (SQLException e) {
throw new DataException("error on export data into csv file", e);

} finally {
try {
conn.close();
} catch (SQLException e) {

new DataException("error on the shutdown the connnection", e);
}
}
}

private String getSql(String tableName, int size) {
StringBuffer sql = new StringBuffer("insert into ");
sql.append(tableName).append(" values(");
for (int i = 0; i < size; i++) {
sql.append("?");
sql.append(",");
}
String sqlStr = sql.toString().substring(0, sql.length() - 1) + ")";
return sqlStr;
}

public void importFromCsvFile(String fileName, String charset)
throws DataException {
ResultSet rs;

Session session =
HibernateUtil.getSessionFactory().getCurrentSession();
Transaction tx = session.beginTransaction();
Connection conn = session.connection();

try {
rs = csv.read(fileName, null, null);
ResultSetMetaData meta = rs.getMetaData();
int size = meta.getColumnCount();
String name[] = new String[size];
String value[] = new String[size];

String sqlStr = getSql("person1", size);

while (rs.next()) {
for (int i = 0; i < size; i++) {
name[i] = meta.getColumnName(i + 1);
value[i] = rs.getString(i + 1);
}
PreparedStatement cs = conn.prepareStatement(sqlStr);
for (int i = 0; i < size; i++) {
if (name[i].equals("BIRTH")) {
cs.setDate(i + 1, Date.valueOf(value[i]));
} else {
cs.setString(i + 1, value[i]);
}
}
cs.execute();
cs.close();
}
rs.close();
tx.commit();
} catch (SQLException e) {
throw new DataException("error on importing data from cvs file",
e);

} finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error on the shutdown the connnection" + e);
return;
}
}

}

public static void main(String args[]) {
CSVUtil util = new CSVUtil();
try {
util.importFromCsvFile("D:/DBBackup/test.csv", null);
util.exportToCsvFile("d:/dbbackup/test2.csv","select * from
person1", null);

} catch (DataException e) {
e.printStackTrace();
}
}

}

[Thomas Mueller]

Hi,
Sorry for the delay... I think I know the reason for the duplicate
column names. It is very likely a bug in Csv utility. You have called
Csv.getInstance only once, and re-use the instance to import and
export data. Try using a new instance for each operation, it should
work then. I have another simplification: why don't you just use

INSERT INTO tableName SELECT * FROM CSVREAD(...)

Instead of inserting each row separately?
Thomas

[cheris...@gmail.com]

Hi
thanks for your good idea! It may have a better performance!

On 5月15日, 下午5时18分, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
> Sorry for the delay... I think I know the reason for the duplicate
> column names. It is very likely a bug in Csv utility. You have called
> Csv.getInstance only once, and re-use the instance to import and
> export data. Try using a new instance for each operation, it should
> work then. I have another simplification: why don't you just use
>
> INSERT INTO tableName SELECT * FROM CSVREAD(...)
>
> Instead of inserting each row separately?
> Thomas
>