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 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 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.
