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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
