This is what happens when you don't subscribe first.
Replying to Rick (thanks for the response)
Hi Glenn,
It doesn't look to me like you are putting single quotes around the file
name when you construct the statement which calls SYSCS_IMPORT_TABLE.
The file name is a string argument to the procedure and so needs to be
single-quoted.
Hope this helps,
-Rick
I added single quotes to the file name
carrier.execute(
"CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'elements'," +
"'" + dataSource + "'" + ",null,null,null,0)"
);
Now I get a different error message
Exception in thread "main" java.sql.SQLException: Table 'ROOT.elements' does
not exist.
at
org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown
Source)
at org.apache.derby.client.am.Statement.execute(Unknown Source)
at csv2derby.Database.go(Database.java:36)
at csv2derby.Main.main(Main.java:21)
Caused by: org.apache.derby.client.am.SqlException: Table 'ROOT.elements' does
not exist.
at org.apache.derby.client.am.Statement.completeExecute(Unknown Source)
at
org.apache.derby.client.net.NetStatementReply.parseEXCSQLSTTreply(Unknown
Source)
at
org.apache.derby.client.net.NetStatementReply.readExecuteCall(Unknown Source)
at org.apache.derby.client.net.StatementReply.readExecuteCall(Unknown
Source)
at org.apache.derby.client.net.NetStatement.readExecuteCall_(Unknown
Source)
at org.apache.derby.client.am.Statement.readExecuteCall(Unknown Source)
at org.apache.derby.client.am.Statement.flowExecute(Unknown Source)
at org.apache.derby.client.am.Statement.executeX(Unknown Source)
... 3 more
Java Result: 1
I don't understand why it can't find the table when the following work fine:
carrier.execute("insert into elements
values(1,'hydrogen','h',1,1,'Nonmetal',1.00794)");
carrier.execute("delete from elements where atomicnumber=1");
Dazed and confused
Glenn
From: [email protected]
To: [email protected]
Subject: Importing
Date: Tue, 12 Jan 2010 06:19:51 +0000
Hi
I have been having some trouble trying to import data from a csv file.
The
program opens a JFileChooser where a .csv file can be selected and
entered under the variable 'dataSource'. Importing is achieved (not
working) with CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE
(null,'elements',"+dataSource+",null,null,null,0).
public class Database {
public void go() throws ClassNotFoundException, SQLException{
//Get connection to database
Class.forName("org.apache.derby.jdbc.ClientDriver");
String url = "jdbc:derby://localhost:1527/DerbyElements";
Connection connect = DriverManager.getConnection(url, "root", "enter");
//Create an object to hold a SQL statement
Statement carrier = connect.createStatement();
//Select file with the JFileChooser
//Create an object 'fileChooser' that represents the data source
JFileChooser fileChooser = new JFileChooser();
//Create variable 'dataSource' to hold the file url
String dataSource = "";
int openFile = fileChooser.showOpenDialog(fileChooser);
//Check if a file has been choosen
if(openFile == JFileChooser.APPROVE_OPTION){
File file = fileChooser.getSelectedFile();
//Make dataSource the path to the file
dataSource = file.getAbsolutePath();
//Load data from the file
carrier.execute(
"CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE
(null,'elements',"+dataSource+",null,null,null,0)"
);
}
// carrier.execute("delete from elements where atomicnumber=2");
}
}
At
the end, commented out I tried just issuing SQL commands,
carrier.execute("delete from elements where atomicnumber=2"); and these
work fine.
The csv file contains:
1,"Hydrogen","H",1,1,"Nonmetal",1.00794
2,"Helium","He",1,18,"Noble gas",4.0026
3,"Lithium","Li",2,1,"Alkali metal",6.941
4,"Beryllium","Be",2,2,"Alkaline earth metal",9.01218
5,"Boron","B",2,13,"Metalloid",10.811
6,"Carbon","C",2,14,"Nonmetal",12.0107
7,"Nitrogen","N",2,15,"Nonmetal",14.0067
8,"Oxygen","O",2,16,"Nonmetal",15.9994
9,"Fluorine","F",2,17,"Halogen",18.9984
10,"Neon","Ne",2,18,"Noble gas",20.1797
The error I get is:
Exception in thread "main" java.sql.SQLSyntaxErrorException: Syntax error:
Encountered "/" at line 1, column 53.
at
org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown
Source)
at org.apache.derby.client.am.Statement.execute(Unknown Source)
at csv2derby.Database.go(Database.java:36)
at csv2derby.Main.main(Main.java:21)
Caused by: org.apache.derby.client.am.SqlException: Syntax error: Encountered
"/" at line 1, column 53.
at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
at
org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source)
at
org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown
Source)
at org.apache.derby.client.net.NetStatementReply.readPrepare(Unknown
Source)
at org.apache.derby.client.net.StatementReply.readPrepare(Unknown
Source)
at org.apache.derby.client.net.NetStatement.readPrepare_(Unknown Source)
at org.apache.derby.client.am.Statement.readPrepare(Unknown Source)
at org.apache.derby.client.am.Statement.flowExecute(Unknown Source)
at org.apache.derby.client.am.Statement.executeX(Unknown Source)
... 3 more
Java Result: 1
Any help would be greatly appreciated
Cheers
Glenn
Browse profiles for FREE View photos of singles in your area!
_________________________________________________________________
Video chat with Windows Live Messenger Learn how
http://windowslive.ninemsn.com.au/messenger/article/870686/video-chat-with-messenger
