Unable to load csv on classpath/jar file

2009-09-01
2014-01-19
  • abgalphabet
    abgalphabet
    2009-09-01

    Can anybody can show me how to load text table source csv file on classpath/jar for a standalone db?
    I've tried any means to load from classpath according to the hsqldb doc with no luck. And I need to put those csv file on file system. It definitely not a good option on production env.

     
    • Blaine Simpson
      Blaine Simpson
      2009-09-01

      As you are talking about distributing the CSV file in a jar file, we are talking about totally static data.  Why don't you load the data at dev-time and distribute the seed database in your jar, thereby eliminating a significant chunk of runtime work and risk along with your CVS-on-CLASSPATH issue?

       
    • Blaine Simpson
      Blaine Simpson
      2009-09-01

      ... FYI, I realize there are several good reasons why this may not be an appropriate solution for you.  Just checking one possibility...

       
    • abgalphabet
      abgalphabet
      2009-09-01

      that sounds an possible alternatives. the csv file may change sometimes for tuning the business parameters.

      but the fact is it's a project i just got handover from a vendor and the project is already near the end of UAT. I don't want to make such change at this moment and just want to ease the deployment since it's error-prone in forgetting to copy the csv file along with deploying an .ear file.

      Any help is welcome.

       
      • Blaine Simpson
        Blaine Simpson
        2009-09-01

        Understanding your sitation a little better, would it be acceptable, security-wise, if the CSV file momentarily appeared in ${java.io.tmpdir} (which physical directory varies widely depending on J2EE container)?

         
        • Fred Toussi
          Fred Toussi
          2009-09-01

          This could be done in HSQLDB 1.8.0.7 (which you can download and use) but does not work in 1.8.0.10

          An example text table definition would look like this:

          CREATE TEXT TABLE tblValidProperties(PropertyNumber INTEGER,Property VARCHAR)
          SET TABLE tblValidProperties READONLY true
          SET TABLE tblValidProperties SOURCE "/hsqldb/tblValidProperties.data"

          The source path is absolute with respect of the jar.

           
          • abgalphabet
            abgalphabet
            2009-09-02

            oh. i am using 1.8.0.10 currently. maybe i should try 1.8.0.7 (hope) that works loading text table source from .csv file on classpath using jdbc:hsqldb:res: method

             
            • abgalphabet
              abgalphabet
              2009-09-02

              try 1.8.0.7 also with no luck. I am using 1.5.0.6 jre. :(

               
    • abgalphabet
      abgalphabet
      2009-09-02

      oh sorry. more accurate scenario description should be:

      1. hsqldb is created from the db.script which is on the classpath now
      2. the csv data file don't get loaded which is put in the same directory as db.script on the classpath

       
      • Fred Toussi
        Fred Toussi
        2009-09-02

        1. According to a developer, who sent me a sample, 1.8.0.7 does work, but 1.8.0.10 doesn't..
        2. The path in the text source command is the location of the csv file. In the example I gave, the name of the csv file ends with .data, but it can have any prefix.

         
    • Fred Toussi
      Fred Toussi
      2009-09-03

      I have fixed the issue in 1.8.0.10. You can download a snapshot of 1.8.0.1 from  the http://hsqldb.org/support/ page.

      As I mentioned in the example I gave, although you put the database files in the same directory, your SET TABLE .. SOURCE statement needs the absolute path of this directory within the jar. In the example, the database files are in the /hsqldb directory in the root of the jar.

       
      • abgalphabet
        abgalphabet
        2009-09-04

        Hi fredt,

        thanks for your help! I've downloaded and tried the the hsqldb_1_8_1_rc.jar. The same result as before and then I traced it in a debug mode, that's what i found:

        My setup:
        1. set the jdbc url as "jdbc:hsqldb:res:/path/to/the/db/folder/testdb" or "jdbc:hsqldb:res:path/to/the/db/folder/testdb" where testdb is the db name
        2. set the testdb.script to load data from csv by "SET TABLE TEST_TBL SOURCE "/path/to/the/db/folder/TEST_TBL.csv;ignore_first=true" or "SET TABLE TEST_TBL SOURCE "TEST_TBL.csv;ignore_first=true"
        3. set the table as readonly by "SET TABLE TEST_TBL READONLY true"
        4. The db directory & files are:
            /path/to/the/db/folder/
              |-testdb.script
              |-TEST_TBL.csv

        What's the result:
        1. When table source set to "SET TABLE TEST_TBL SOURCE "/path/to/the/db/folder/TEST_TBL.csv;ignore_first=true"

        Exception:
        error in script file line: 19 bad TEXT table source file - line number: 0 File input/output erroropenning file: D:\path\to\the\db\folder\/path/to/the/db/folder/TEST_TBL.csv error: java.io.FileNotFoundException: D:\path\to\the\db\folder\/path/to/the/db/folder/TEST_TBL.csv (系統找不到指定的路徑。) in statement [SET TABLE TEST_DB READONLY true]

        2. When table source set to "SET TABLE TEST_TBL SOURCE "TEST_TBL.csv;ignore_first=true"

        Exception:
        error in script file line: 19 bad TEXT table source file - line number: 0 File input/output erroropenning file: D:\path\to\the\db\folder\TEST_TBL.csv error: java.io.FileNotFoundException: D:\path\to\the\db\folder\TEST_TBL.csv (系統找不到指定的路徑。) in statement [SET TABLE TEST_DB READONLY true]

        3. When removing SET TABLE TEST_TBL READONLY true

        No Execption. db can be created but no csv data!!!

         
        • Fred Toussi
          Fred Toussi
          2009-09-04

          This issue with appending the path to D:\\etc was someting that I fixed, toghether with a couple others. Your test paths seem fine (the paths should start with / )

          Regarding the layout:
          If you have a classpath pointing to a directory, say C://mydir/classes/ then the root is considered this directory and your files will be in

          C://mydir/classes/path/to/the/db/folder/

          but you specify it in the URL and text source as you did.

          Please also check the 1.9.0 jar and let me know if that one works, so that I can narrow my seach.

           
    • abgalphabet
      abgalphabet
      2009-09-07

      I have tried the 1.9.0 RC also. The same result got.
      Since the db and related files are packaged as jar and put it in classpath, it's not a fixed absolute path like "C://mydir/classes" and would rather like "/mydir/db/folder" sth like that.

       
  • Fred Toussi
    Fred Toussi
    2009-09-13

    I spent some time on 1.9.0 and fix the issues there. I have tested it with a small database in a jar. I will open a bug on the Bugs Tracker and attach the jar and the URL to connect.

    Please create a similar jar with 1.8.1 and test it, as I don't have time.

    Also note, in 1.9 (don't know about 1.8.1) you can freely create ordinary memory tables and modify the data after openning a res: database.