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.

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks