Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 73879 invoked from network); 12 Jan 2010 06:57:20 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 12 Jan 2010 06:57:20 -0000 Received: (qmail 53848 invoked by uid 500); 12 Jan 2010 06:57:20 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 53781 invoked by uid 500); 12 Jan 2010 06:57:20 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 53773 invoked by uid 99); 12 Jan 2010 06:57:20 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 12 Jan 2010 06:57:20 +0000 X-ASF-Spam-Status: No, hits=2.2 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of glennambrose@hotmail.com designates 65.55.90.238 as permitted sender) Received: from [65.55.90.238] (HELO snt0-omc4-s35.snt0.hotmail.com) (65.55.90.238) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 12 Jan 2010 06:57:07 +0000 Received: from SNT107-W50 ([65.55.90.200]) by snt0-omc4-s35.snt0.hotmail.com with Microsoft SMTPSVC(6.0.3790.3959); Mon, 11 Jan 2010 22:56:46 -0800 Message-ID: Content-Type: multipart/alternative; boundary="_2021030d-771b-4c9b-bde7-331e152ec843_" X-Originating-IP: [115.64.119.4] From: Glenn Ambrose To: Derby Java DB Subject: RE: Importing Date: Tue, 12 Jan 2010 06:56:45 +0000 Importance: Normal In-Reply-To: References: MIME-Version: 1.0 X-OriginalArrivalTime: 12 Jan 2010 06:56:46.0325 (UTC) FILETIME=[67E7A250:01CA9354] X-Virus-Checked: Checked by ClamAV on apache.org --_2021030d-771b-4c9b-bde7-331e152ec843_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable This is what happens when you don't subscribe first. Replying to Rick (thanks for the response) Hi Glenn=2C It doesn't look to me like you are putting single quotes around the file=20 name when you construct the statement which calls SYSCS_IMPORT_TABLE.=20 The file name is a string argument to the procedure and so needs to be=20 single-quoted. Hope this helps=2C -Rick I added single quotes to the file name carrier.execute( "CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null=2C'elements'=2C" + "'" + dataSource + "'" + "=2Cnull=2Cnull=2Cnull=2C0)" )=3B Now I get a different error message Exception in thread "main" java.sql.SQLException: Table 'ROOT.elements' doe= s not exist. =20 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' d= oes not exist. =20 at org.apache.derby.client.am.Statement.completeExecute(Unknown Sou= rce) at org.apache.derby.client.net.NetStatementReply.parseEXCSQLSTTrepl= y(Unknown Source) at org.apache.derby.client.net.NetStatementReply.readExecuteCall(Un= known Source) at org.apache.derby.client.net.StatementReply.readExecuteCall(Unkno= wn Source) at org.apache.derby.client.net.NetStatement.readExecuteCall_(Unknow= n Source) at org.apache.derby.client.am.Statement.readExecuteCall(Unknown Sou= rce) 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=2C'hydrogen'=2C'h'=2C1=2C1= =2C'Nonmetal'=2C1.00794)")=3B carrier.execute("delete from elements where atomicnumber=3D1")=3B Dazed and confused Glenn From: glennambrose@hotmail.com To: derby-user@db.apache.org Subject: Importing Date: Tue=2C 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=2C'elements'=2C"+dataSource+"=2Cnull=2Cnull=2Cnull=2C0). public class Database { public void go() throws ClassNotFoundException=2C SQLException{ //Get connection to database Class.forName("org.apache.derby.jdbc.ClientDriver")=3B String url =3D "jdbc:derby://localhost:1527/DerbyElements"=3B Connection connect =3D DriverManager.getConnection(url=2C "root"=2C= "enter")=3B //Create an object to hold a SQL statement Statement carrier =3D connect.createStatement()=3B //Select file with the JFileChooser //Create an object 'fileChooser' that represents the data source JFileChooser fileChooser =3D new JFileChooser()=3B //Create variable 'dataSource' to hold the file url String dataSource =3D ""=3B int openFile =3D fileChooser.showOpenDialog(fileChooser)=3B //Check if a file has been choosen if(openFile =3D=3D JFileChooser.APPROVE_OPTION){ File file =3D fileChooser.getSelectedFile()=3B //Make dataSource the path to the file dataSource =3D file.getAbsolutePath()=3B //Load data from the file carrier.execute( "CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null=2C'elements'=2C"+data= Source+"=2Cnull=2Cnull=2Cnull=2C0)" )=3B } // carrier.execute("delete from elements where atomicnumber=3D2")=3B } } At the end=2C commented out I tried just issuing SQL commands=2C carrier.execute("delete from elements where atomicnumber=3D2")=3B and these work fine. The csv file contains: 1=2C"Hydrogen"=2C"H"=2C1=2C1=2C"Nonmetal"=2C1.00794 2=2C"Helium"=2C"He"=2C1=2C18=2C"Noble gas"=2C4.0026 3=2C"Lithium"=2C"Li"=2C2=2C1=2C"Alkali metal"=2C6.941 4=2C"Beryllium"=2C"Be"=2C2=2C2=2C"Alkaline earth metal"=2C9.01218 5=2C"Boron"=2C"B"=2C2=2C13=2C"Metalloid"=2C10.811 6=2C"Carbon"=2C"C"=2C2=2C14=2C"Nonmetal"=2C12.0107 7=2C"Nitrogen"=2C"N"=2C2=2C15=2C"Nonmetal"=2C14.0067 8=2C"Oxygen"=2C"O"=2C2=2C16=2C"Nonmetal"=2C15.9994 9=2C"Fluorine"=2C"F"=2C2=2C17=2C"Halogen"=2C18.9984 10=2C"Neon"=2C"Ne"=2C2=2C18=2C"Noble gas"=2C20.1797 The error I get is: Exception in thread "main" java.sql.SQLSyntaxErrorException: Syntax error: = Encountered "/" at line 1=2C 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: Encounter= ed "/" at line 1=2C column 53. at org.apache.derby.client.am.Statement.completeSqlca(Unknown Sourc= e) at org.apache.derby.client.net.NetStatementReply.parsePrepareError(= Unknown Source) at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTrepl= y(Unknown Source) at org.apache.derby.client.net.NetStatementReply.readPrepare(Unknow= n Source) at org.apache.derby.client.net.StatementReply.readPrepare(Unknown S= ource) at org.apache.derby.client.net.NetStatement.readPrepare_(Unknown So= urce) 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=20 Cheers Glenn =20 Browse profiles for FREE View photos of singles in your area! =20 _________________________________________________________________ Video chat with Windows Live Messenger Learn how http://windowslive.ninemsn.com.au/messenger/article/870686/video-chat-with-= messenger= --_2021030d-771b-4c9b-bde7-331e152ec843_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable This is what happens when you don't subscribe first.

Replying to Ric= k (thanks for the response)

Hi Glenn=2C

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 i=
s a string argument to the procedure and so needs to be 
single-quoted.<=
br>
Hope this helps=2C
-Rick


I added single quotes t= o the file name

carrier.execute(
 =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B "CALL SYSCS_UTIL.S= YSCS_IMPORT_TABLE (null=2C'elements'=2C" +
 =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B "'" + dataSource + "'" + "= =2Cnull=2Cnull=2Cnull=2C0)"
 =3B =3B =3B =3B =3B&nbs= p=3B =3B =3B =3B =3B =3B )=3B

Now I get a differ= ent error message

Exception in thread "main" java.sql.SQLException: = Table 'ROOT.elements' does not exist. =3B
 =3B =3B =3B&= nbsp=3B =3B =3B =3B at org.apache.derby.client.am.SQLExceptionF= actory40.getSQLException(Unknown Source)
 =3B =3B =3B = =3B =3B =3B =3B at org.apache.derby.client.am.SqlException.getS= QLException(Unknown Source)
 =3B =3B =3B =3B =3B&nbs= p=3B =3B at org.apache.derby.client.am.Statement.execute(Unknown Source= )
 =3B =3B =3B =3B =3B =3B =3B at csv2derby.= Database.go(Database.java:36)
 =3B =3B =3B =3B =3B&n= bsp=3B =3B at csv2derby.Main.main(Main.java:21)
Caused by: org.apach= e.derby.client.am.SqlException: Table 'ROOT.elements' does not exist. = =3B
 =3B =3B =3B =3B =3B =3B =3B at org.apa= che.derby.client.am.Statement.completeExecute(Unknown Source)
 =3B&n= bsp=3B =3B =3B =3B =3B =3B at org.apache.derby.client.n= et.NetStatementReply.parseEXCSQLSTTreply(Unknown Source)
 =3B = =3B =3B =3B =3B =3B =3B at org.apache.derby.client.net.= NetStatementReply.readExecuteCall(Unknown Source)
 =3B =3B = =3B =3B =3B =3B =3B at org.apache.derby.client.net.Statemen= tReply.readExecuteCall(Unknown Source)
 =3B =3B =3B =3B&= nbsp=3B =3B =3B at org.apache.derby.client.net.NetStatement.readExe= cuteCall_(Unknown Source)
 =3B =3B =3B =3B =3B = =3B =3B at org.apache.derby.client.am.Statement.readExecuteCall(Unknown= Source)
 =3B =3B =3B =3B =3B =3B =3B at org= .apache.derby.client.am.Statement.flowExecute(Unknown Source)
 =3B&n= bsp=3B =3B =3B =3B =3B =3B at org.apache.derby.client.a= m.Statement.executeX(Unknown Source)
 =3B =3B =3B =3B&nb= sp=3B =3B =3B ... 3 more
Java Result: 1

I don't understan= d why it can't find the table when the following work fine:

carrier.= execute("insert into elements values(1=2C'hydrogen'=2C'h'=2C1=2C1=2C'Nonmet= al'=2C1.00794)")=3B
carrier.execute("delete from elements where atomicnu= mber=3D1")=3B

Dazed and confused
Glenn

From: glennambrose@hotmail.com
To: derby-user@db.apache.org
Subj= ect: Importing
Date: Tue=2C 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=2C'elements'=2C"+dataSource+"=2Cnull=2Cnull=2Cnull=2C0).

publi= c class Database {
 =3B =3B =3B public void go() throws Clas= sNotFoundException=2C SQLException{
 =3B =3B =3B =3B&nbs= p=3B =3B =3B //Get connection to database
 =3B =3B = =3B =3B =3B =3B =3B Class.forName("org.apache.derby.jdbc.Cl= ientDriver")=3B
 =3B =3B =3B =3B =3B =3B =3B= String url =3D "jdbc:derby://localhost:1527/DerbyElements"=3B
 =3B&= nbsp=3B =3B =3B =3B =3B =3B Connection connect =3D Driv= erManager.getConnection(url=2C "root"=2C "enter")=3B
 =3B =3B&nb= sp=3B =3B =3B =3B =3B //Create an object to hold a SQL stat= ement
 =3B =3B =3B =3B =3B =3B =3B Statement= carrier =3D connect.createStatement()=3B
 =3B =3B =3B = =3B =3B =3B =3B //Select file with the JFileChooser
 =3B=  =3B =3B =3B =3B =3B =3B //Create an object 'fileCh= ooser' that represents the data source
 =3B =3B =3B =3B&= nbsp=3B =3B =3B JFileChooser fileChooser =3D new JFileChooser()=3B<= br> =3B =3B =3B =3B =3B =3B =3B //Create variab= le 'dataSource' to hold the file url
 =3B =3B =3B =3B&nb= sp=3B =3B =3B String dataSource =3D ""=3B
 =3B =3B = =3B =3B =3B =3B =3B int openFile =3D fileChooser.showOpenDi= alog(fileChooser)=3B
 =3B =3B =3B =3B =3B =3B&nb= sp=3B //Check if a file has been choosen
 =3B =3B =3B = =3B =3B =3B =3B if(openFile =3D=3D JFileChooser.APPROVE_OPTION)= {
 =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B File file =3D fileChooser.getSelectedFile()=3B
 = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B //Make dataSource the path to the file
 =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B dataSource= =3D file.getAbsolutePath()=3B
 =3B =3B =3B =3B =3B&= nbsp=3B =3B =3B =3B =3B =3B //Load data from the file =3B =3B =3B =3B =3B =3B =3B =3B =3B&= nbsp=3B =3B carrier.execute(
 =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B "CALL SYSCS_UTIL.SYSCS_= IMPORT_TABLE (null=2C'elements'=2C"+dataSource+"=2Cnull=2Cnull=2Cnull=2C0)"=
 =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B )=3B
 =3B =3B =3B =3B =3B = =3B =3B }
 =3B =3B =3B =3B =3B // =3B carrie= r.execute("delete from elements where atomicnumber=3D2")=3B
 =3B&nbs= p=3B =3B }
}

At the end=2C commented out I tried just issuing SQL commands=2C carrier.execute("delete from elements where atomicnumber=3D2")=3B and these work fine.

The csv file contains:

1=2C"Hydrogen"=2C"H"=2C1=2C= 1=2C"Nonmetal"=2C1.00794
2=2C"Helium"=2C"He"=2C1=2C18=2C"Noble gas"=2C4.= 0026
3=2C"Lithium"=2C"Li"=2C2=2C1=2C"Alkali metal"=2C6.941
4=2C"Beryl= lium"=2C"Be"=2C2=2C2=2C"Alkaline earth metal"=2C9.01218
5=2C"Boron"=2C"B= "=2C2=2C13=2C"Metalloid"=2C10.811
6=2C"Carbon"=2C"C"=2C2=2C14=2C"Nonmeta= l"=2C12.0107
7=2C"Nitrogen"=2C"N"=2C2=2C15=2C"Nonmetal"=2C14.0067
8= =2C"Oxygen"=2C"O"=2C2=2C16=2C"Nonmetal"=2C15.9994
9=2C"Fluorine"=2C"F"= =2C2=2C17=2C"Halogen"=2C18.9984
10=2C"Neon"=2C"Ne"=2C2=2C18=2C"Noble gas= "=2C20.1797

The error I get is:

Exception in thread "main" ja= va.sql.SQLSyntaxErrorException: Syntax error: Encountered "/" at line 1=2C = column 53.
 =3B =3B =3B =3B =3B =3B =3B at o= rg.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Sou= rce)
 =3B =3B =3B =3B =3B =3B =3B at org.apa= che.derby.client.am.SqlException.getSQLException(Unknown Source)
 = =3B =3B =3B =3B =3B =3B =3B at org.apache.derby.cli= ent.am.Statement.execute(Unknown Source)
 =3B =3B =3B = =3B =3B =3B =3B at csv2derby.Database.go(Database.java:36)
&= nbsp=3B =3B =3B =3B =3B =3B =3B at csv2derby.Main.m= ain(Main.java:21)
Caused by: org.apache.derby.client.am.SqlException: Sy= ntax error: Encountered "/" at line 1=2C column 53.
 =3B =3B&nbs= p=3B =3B =3B =3B =3B at org.apache.derby.client.am.Statemen= t.completeSqlca(Unknown Source)
 =3B =3B =3B =3B =3B=  =3B =3B at org.apache.derby.client.net.NetStatementReply.parsePrep= areError(Unknown Source)
 =3B =3B =3B =3B =3B = =3B =3B at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTT= reply(Unknown Source)
 =3B =3B =3B =3B =3B =3B&n= bsp=3B at org.apache.derby.client.net.NetStatementReply.readPrepare(Unknown= Source)
 =3B =3B =3B =3B =3B =3B =3B at org= .apache.derby.client.net.StatementReply.readPrepare(Unknown Source)
&nbs= p=3B =3B =3B =3B =3B =3B =3B at org.apache.derby.cl= ient.net.NetStatement.readPrepare_(Unknown Source)
 =3B =3B = =3B =3B =3B =3B =3B at org.apache.derby.client.am.Statement= .readPrepare(Unknown Source)
 =3B =3B =3B =3B =3B&nb= sp=3B =3B at org.apache.derby.client.am.Statement.flowExecute(Unknown S= ource)
 =3B =3B =3B =3B =3B =3B =3B at org.a= pache.derby.client.am.Statement.executeX(Unknown Source)
 =3B = =3B =3B =3B =3B =3B =3B ... 3 more
Java Result: 1

Any help would be greatly appreciated

Cheers
Glenn =
Browse profiles for FREE View photos of singles in your area! =
Learn how Video chat w= ith Windows Live Messenger = --_2021030d-771b-4c9b-bde7-331e152ec843_--