The following code seems to work; so it's possible to use a URL, but not
the documented classpath: protocol. I haven't tried reconfiguring this to
run from a jar as yet, which is the object of the exercise.
String url = getClass().getClassLoader().getResource(
"timeStamps.csv").getPath(); // Gets the path of the class or jar.
if (url.startsWith("/")) url= url.substring(1, url.length());
System.out.println("URL of timeStamps.csv is:"+url);
String queryString="INSERT INTO timestamps (timeStampID, projectFK,
Description) SELECT TIMESTAMPID, PROJECTFK, DESCRIPTION FROM CSVREAD('"+url+
"');";
System.out.println("Query string is:"+queryString);
executeQuery(queryString,s);
On Wednesday, December 6, 2017 at 3:53:42 PM UTC-6, Alan Freeman wrote:
>
> Hi
> I'm tryng to populate a small database using H2's CSVREAD function, using
> the syntax described in http://www.h2database.com/html/functions.html.
> I would like to reference my data files by placing them in the classpath,
> so that I do not need to refer to an absolute file location and can produce
> a runnable jar.
>
> Up until now I am working with eclipse link. I first set a variable
> @FILELOCATION with the following code:
>
> and execute the following three statements without problems:
>
> String setFilesLocationQuery = "SET @FILELOCATION ='" +
> userFilesLocation+"'";
> try {
> Statement fl;
> fl = conn.createStatement();
> fl.executeUpdate(setFilesLocationQuery);
> } catch (SQLException e) {
> logger.info("Could not create the file locator statement
> because/" + e.getMessage() + "/n");
> return;
> }...
>
>
> Iterator<String> it = batchQuery.iterator();
> while (it.hasNext()) {
> String nextQuery = it.next();
>
> logger.info("Initialisation: processing the following query\n
> " + nextQuery + "\n");
> try {
> s.executeUpdate(nextQuery);
> } catch (Exception f) {
> logger.debug("Query failed because:\n" + f.getMessage());
> f.printStackTrace();
> }
> }
>
> Batch query is an ArrayList<String> in which each element is a query.
> userFileLocation is set to the directory of the Eclipse project in which
> the .csv files reside viz:C:\Users\{ME}\eclipse-workspace\{MYPROJECT}\daata
>
> the following statements execute fine:
>
> CREATE TABLE timeStamps (`timeStampID` int Default 1 NOT NULL, `projectFK`
> INT default 1 NOT NULL, `Description` VARCHAR(30) default NULL, PRIMARY
> KEY (`timeStampID`,projectFK));
> INSERT INTO timestamps (timeStampID, projectFK, Description) SELECT
> TIMESTAMPID, PROJECTFK, DESCRIPTION FROM CSVREAD(CONCAT(@FILELOCATION,
> 'timeStamps.csv'));
>
> However, I don't think I can use this technique to produce a runnable
> jar, so I tried using the classpath: syntax. However, I get errors of one
> kind or another for all the combinations I have tried.
>
> Substitutiing CSVREAD('classpath:timeStamps.csv') gives me a Syntax Error.
> the Stacktrace is
>
> Syntax error in SQL statement " INSERT INTO TIMESTAMPS (TIMESTAMPID,
> PROJECTFK, DESCRIPTION) SELECT TIMESTAMPID, PROJECTFK, DESCRIPTION FROM
> CSVREAD('classpath:timeStamps.csv'))[*]; "; SQL statement:
> INSERT INTO timestamps (timeStampID, projectFK, Description) SELECT
> TIMESTAMPID, PROJECTFK, DESCRIPTION FROM CSVREAD(
> 'classpath:timeStamps.csv')); [42000-195]
> org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement " INSERT INTO
> TIMESTAMPS (TIMESTAMPID, PROJECTFK, DESCRIPTION) SELECT TIMESTAMPID,
> PROJECTFK, DESCRIPTION FROM CSVREAD('classpath:timeStamps.csv'))[*]; ";
> SQL statement:
> INSERT INTO timestamps (timeStampID, projectFK, Description) SELECT
> TIMESTAMPID, PROJECTFK, DESCRIPTION FROM CSVREAD(
> 'classpath:timeStamps.csv')); [42000-195][CAPITALISM] DBHandler -
> Initialisation: processing the following query
> insert into projects (ProjectID, Description) select ProjectID,
> Description from CSVREAD(CONCAT(@FILELOCATION,'\\projects.csv'));
>
>
> at org.h2.message.DbException.getJdbcSQLException(DbException.java:345
> )
> at org.h2.message.DbException.get(DbException.java:179)
> at org.h2.message.DbException.get(DbException.java:155)
> at org.h2.message.DbException.getSyntaxError(DbException.java:191)
> at org.h2.command.Parser.getSyntaxError(Parser.java:533)
> at org.h2.command.Parser.prepareCommand(Parser.java:260)
> at org.h2.engine.Session.prepareLocal(Session.java:573)
> at org.h2.engine.Session.prepareCommand(Session.java:514)
> at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1204)
> at org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:
> 133)
> at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:122)
> at rd.dev.simulation.utils.DBHandler.initialiseDatabase(DBHandler.java
> :201)
> at rd.dev.simulation.utils.DBHandler.initialiseDataBaseAndStart(
> DBHandler.java:241)
> at rd.dev.simulation.Capitalism.start(Capitalism.java:116)
> at com.sun.javafx.application.LauncherImpl.lambda
> $launchApplication1$162(LauncherImpl.java:863)
> at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$175(
> PlatformImpl.java:326)
> at com.sun.javafx.application.PlatformImpl.lambda$null$173(
> PlatformImpl.java:295)
> at java.security.AccessController.doPrivileged(Native Method)
> at com.sun.javafx.application.PlatformImpl.lambda$runLater$174(
> PlatformImpl.java:294)
> at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(
> InvokeLaterDispatcher.java:95)
> at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
> at com.sun.glass.ui.win.WinApplication.lambda$null$148(WinApplication.
> java:191)
> at java.lang.Thread.run(Thread.java:748)
>
>
>
> Substitutiing CSVREAD('classpath:/timeStamps.csv') gives me the same
> result as does 'classpath:\\timeStamps.csv'
>
> Substitutiing CSVREAD('classpath:/data/timeStamps.csv) gives me an IO
> exception;the stack trace is
>
> IO Exception: "IOException reading classpath:/data/timeStamps.csv"; SQL
> statement:
>
> IO Exception: "IOException reading classpath:/data/timeStamps.csv"; SQL
> statement:
> INSERT INTO timestamps (timeStampID, projectFK, Description) SELECT
> TIMESTAMPID, PROJECTFK, DESCRIPTION FROM CSVREAD(
> 'classpath:/data/timeStamps.csv')); [90028-195]
> org.h2.jdbc.JdbcSQLException: IO Exception: "IOException reading
> classpath:/data/timeStamps.csv"; SQL statement:
> INSERT INTO timestamps (timeStampID, projectFK, Description) SELECT
> TIMESTAMPID, PROJECTFK, DESCRIPTION FROM CSVREAD(
> 'classpath:/data/timeStamps.csv')); [90028-195]
> DBHandler - Initialisation: processing the following query
> insert into projects (ProjectID, Description) select ProjectID,
> Description from CSVREAD(CONCAT(@FILELOCATION,'\\projects.csv'));
>
> at org.h2.message.DbException.getJdbcSQLException(DbException.java:345
> )
> at org.h2.message.DbException.get(DbException.java:168)
> at org.h2.tools.Csv.convertException(Csv.java:597)
> at org.h2.tools.Csv.read(Csv.java:196)
> at org.h2.expression.Function.getValueForColumnList(Function.java:2711
> )
> at org.h2.table.FunctionTable.<init>(FunctionTable.java:65)
> at org.h2.command.Parser.readTableFilter(Parser.java:1252)
> at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1940)
> at org.h2.command.Parser.parseSelectSimple(Parser.java:2089)
> at org.h2.command.Parser.parseSelectSub(Parser.java:1934)
> at org.h2.command.Parser.parseSelectUnion(Parser.java:1749)
> at org.h2.command.Parser.parseSelect(Parser.java:1737)
> at org.h2.command.Parser.parseInsert(Parser.java:1114)
> at org.h2.command.Parser.parsePrepared(Parser.java:416)
> at org.h2.command.Parser.parse(Parser.java:320)
> at org.h2.command.Parser.parse(Parser.java:292)
> at org.h2.command.Parser.prepareCommand(Parser.java:257)
> at org.h2.engine.Session.prepareLocal(Session.java:573)
> at org.h2.engine.Session.prepareCommand(Session.java:514)
> at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1204)
> at org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:
> 133)
> at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:122)
> at rd.dev.simulation.utils.DBHandler.initialiseDatabase(DBHandler.java
> :201)
> at rd.dev.simulation.utils.DBHandler.initialiseDataBaseAndStart(
> DBHandler.java:241)
> at rd.dev.simulation.Capitalism.start(Capitalism.java:116)
> at com.sun.javafx.application.LauncherImpl.lambda
> $launchApplication1$162(LauncherImpl.java:863)
> at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$175(
> PlatformImpl.java:326)
> at com.sun.javafx.application.PlatformImpl.lambda$null$173(
> PlatformImpl.java:295)
> at java.security.AccessController.doPrivileged(Native Method)
> at com.sun.javafx.application.PlatformImpl.lambda$runLater$174(
> PlatformImpl.java:294)
> at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(
> InvokeLaterDispatcher.java:95)
> at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
> at com.sun.glass.ui.win.WinApplication.lambda$null$148(WinApplication.
> java:191)
> at java.lang.Thread.run(Thread.java:748)
> Caused by: java.io.FileNotFoundException: resource /data/timeStamps.csv
> at org.h2.store.fs.FilePathDisk.newInputStream(FilePathDisk.java:312)
> at org.h2.store.fs.FileUtils.newInputStream(FileUtils.java:218)
> at org.h2.tools.Csv.initRead(Csv.java:315)
> at org.h2.tools.Csv.readResultSet(Csv.java:218)
> at org.h2.tools.Csv.read(Csv.java:194)
> ... 30 more
> Enter code here...
>
> I printed out the runtime classpath and it gives me:
> /C:/Users/afree/eclipse-workspace/capitalism-7.2/data/
> /C:/Users/afree/eclipse-workspace/capitalism-7.2/bin/
> /C:/Users/afree/.p2/pool/plugins/org.eclipse.fx.ide.css.jfx8_3.0.0.
> 201705220750.jar
> /C:/Users/afree/eclipse-workspace/libraries/EclipseLink%202.5.2/
> eclipselink/jlib/eclipselink.jar
> /C:/Users/afree/eclipse-workspace/libraries/EclipseLink%202.5.2/
> eclipselink/jlib/jpa/javax.persistence_2.1.0.v201304241213.jar
> /C:/Users/afree/eclipse-workspace/libraries/EclipseLink%202.5.2/
> eclipselink/jlib/jpa/org.eclipse.persistence.jpa.modelgen_2.5.2.v20140319-
> 9ad6abd.jar
> /C:/Users/afree/eclipse-workspace/libraries/EclipseLink%202.5.2/
> eclipselink/jlib/jpa/org.eclipse.persistence.jpars_2.5.2.v20140319-9ad6abd
> .jar
> /C:/Users/afree/eclipse-workspace/libraries/EclipseLink%202.5.2/
> eclipselink/jlib/moxy/com.sun.tools.xjc_2.2.0.jar
> /C:/Users/afree/eclipse-workspace/libraries/EclipseLink%202.5.2/
> eclipselink/jlib/moxy/com.sun.xml.bind_2.2.0.v201004141950.jar
> /C:/Users/afree/eclipse-workspace/libraries/EclipseLink%202.5.2/
> eclipselink/jlib/moxy/javax.activation_1.1.0.v201108011116.jar
> /C:/Users/afree/eclipse-workspace/libraries/EclipseLink%202.5.2/
> eclipselink/jlib/moxy/javax.mail_1.4.0.v201005080615.jar
> /C:/Users/afree/eclipse-workspace/libraries/EclipseLink%202.5.2/
> eclipselink/jlib/moxy/javax.xml.bind_2.2.0.v201105210648.jar
> /C:/Users/afree/eclipse-workspace/libraries/EclipseLink%202.5.2/
> eclipselink/jlib/moxy/javax.xml.stream_1.0.1.v201004272200.jar
> /C:/Users/afree/eclipse-workspace/capitalism-7.2/lib/
> /C:/Users/afree/eclipse-workspace/capitalism-7.2/lib/log4j-api-2.9.1.jar
> /C:/Users/afree/eclipse-workspace/capitalism-7.2/lib/log4j-core-2.9.1.jar
> /C:/Users/afree/eclipse-workspace/capitalism-7.2/lib/log4j-api-2.9.1-
> sources.jar
> /C:/Users/afree/eclipse-workspace/capitalism-7.2/lib/commons-math3-3.6.
> 1.jar
> /C:/Users/afree/eclipse-workspace/capitalism-7.2/lib/h2-1.4.195.jar
>
> Has anyone else encountered these problems and have I done something
> wrong, or is there a workaround?
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
