You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Glenn Ambrose <gl...@hotmail.com> on 2010/01/12 07:19:51 UTC
Importing
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
_________________________________________________________________
View photos of singles in your area! Browse profiles for FREE
http://clk.atdmt.com/NMN/go/150855801/direct/01/
RE: Importing
Posted by Glenn Ambrose <gl...@hotmail.com>.
Thank you Knut and Rick very much it now works a treat.
Cheers
Glenn
> Date: Tue, 12 Jan 2010 10:20:33 +0100
> From: Knut.Hatlen@Sun.COM
> Subject: Re: Importing
> To: derby-user@db.apache.org
>
> Glenn Ambrose <gl...@hotmail.com> writes:
>
> > 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.
>
> Try 'ELEMENTS' instead of 'elements'. Derby converts unquoted
> identifiers to upper case internally, and the arguments to
> SYSCS_IMPORT_TABLE must exactly match the canonical form of the
> identifier.
>
> --
> Knut Anders
_________________________________________________________________
Time for a new car? Sell your old one fast!
http://clk.atdmt.com/NMN/go/157637060/direct/01/
Re: Importing
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Glenn Ambrose <gl...@hotmail.com> writes:
> 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.
Try 'ELEMENTS' instead of 'elements'. Derby converts unquoted
identifiers to upper case internally, and the arguments to
SYSCS_IMPORT_TABLE must exactly match the canonical form of the
identifier.
--
Knut Anders
RE: Importing
Posted by Glenn Ambrose <gl...@hotmail.com>.
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: glennambrose@hotmail.com
To: derby-user@db.apache.org
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