db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Glenn Ambrose <glennambr...@hotmail.com>
Subject RE: Importing
Date Tue, 12 Jan 2010 06:56:45 GMT

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
Mime
View raw message