{"id":710,"date":"2017-10-26T10:54:23","date_gmt":"2017-10-26T10:54:23","guid":{"rendered":"http:\/\/milecastle27.co.uk\/rob\/?p=710"},"modified":"2017-10-26T10:55:44","modified_gmt":"2017-10-26T10:55:44","slug":"jdbc-connections-for-openoffice-hsqldb","status":"publish","type":"post","link":"https:\/\/milecastle27.co.uk\/rob\/2017\/10\/jdbc-connections-for-openoffice-hsqldb\/","title":{"rendered":"JDBC connections for OpenOffice HSQLDB"},"content":{"rendered":"<h3>How to connect to and query an OpenOffice database with JDBC<\/h3>\n<p>The internet completely failed to provide a clear answer to this question &#8211; so having worked it out and on the off chance that someone else needs to know the answer; here&#8217;s how to run queries from <a href=\"http:\/\/squirrel-sql.sourceforge.net\/\">SQuirrel SQL<\/a> against your ODB data:<\/p>\n<ol>\n<li>Create your new database in Open Office Base (or equivalent):\n<ul>\n<li>Call it anything you like but in this post I call mine &#8216;<em>ManuscriptLibrary<\/em>&#8216;<\/li>\n<li>Add a table to the database, this is the table you want to query &#8211; mine is called &#8216;<em>Manuscript<\/em>&#8216;<\/li>\n<li>Save and Close the Open Office Base file<\/li>\n<\/ul>\n<\/li>\n<li>Extract the database files from the ODB file:\n<ul>\n<li>Open the file &#8216;ManuscriptLibrary.odb&#8217; in your favourite zip tool (7-Zip, WinZip .. whatever).<\/li>\n<li>Select the database folder and copy the four files (properties, backup, data, script) out to a local directory\u00a0 such as <em>C:\\Path\\To\\Extracted\\Files\\<\/em><\/li>\n<\/ul>\n<\/li>\n<li>Rename the files using your database name (backup is not needed):\n<ul>\n<li>properties = <em>ManuscriptLibrary.properties<\/em><\/li>\n<li>data = <em>ManuscriptLibrary.data<\/em><\/li>\n<li>script = <em>ManuscriptLibrary.script<\/em><\/li>\n<\/ul>\n<\/li>\n<li>Open Squirrel SQL:\n<ul>\n<li>Add a new Alias with the HSQLSB Standalone driver (you will need to add the <a href=\"http:\/\/hsqldb.org\/\">hsqldb<\/a> jar to enable this driver)<\/li>\n<li>Set the URL to: <em>jdbc:hsqldb:file:\/\/\/C:\/Path\/To\/Extracted\/Files\/ManuscriptLibrary <\/em>(ManuscriptLibrary is the name of the DB and the name you used in step 3 above)<\/li>\n<li>Set the User Name: <em>sa<\/em> (Do not set the Password &#8211; it&#8217;s empty)<\/li>\n<li>Hit OK\u00a0 (note the driver will connect even if you get the filenames wrong &#8211; so Test is spectacularly unhelpful)<\/li>\n<\/ul>\n<\/li>\n<li>Connect to the DB:\n<ul>\n<li>Double click on the saved Alias or hit Connect<\/li>\n<li>In the Objects tab, expand PUBLIC &gt; PUBLIC &gt; TABLE &#8211; at this point you should see the table you created in step 1 (&#8216;<em>Manuscript<\/em>&#8216;), you may need to hit refresh at this point<\/li>\n<li>Select SQL tab and run the query: <em>select * from &#8220;Manuscript&#8221;;<\/em> (note the table name is in double quotes). You should get the contents of the table. If you get error codes 42501 or -5501 this means that either &#8211; the ODB files weren&#8217;t found or the table name is wrong.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<p>And now no doubt you want to manipulate the data and put it back into an ODB file that can be edited in Open Office &#8211; that&#8217;s a topic for another post.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>How to connect to and query an OpenOffice database with JDBC The internet completely failed to provide a clear answer to this question &#8211; so having worked it out and on the off chance that someone else needs to know the answer; here&#8217;s how to run queries from SQuirrel SQL against your ODB data: Create<a class=\"more-link\" href=\"https:\/\/milecastle27.co.uk\/rob\/2017\/10\/jdbc-connections-for-openoffice-hsqldb\/\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[4],"tags":[],"_links":{"self":[{"href":"https:\/\/milecastle27.co.uk\/rob\/wp-json\/wp\/v2\/posts\/710"}],"collection":[{"href":"https:\/\/milecastle27.co.uk\/rob\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/milecastle27.co.uk\/rob\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/milecastle27.co.uk\/rob\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/milecastle27.co.uk\/rob\/wp-json\/wp\/v2\/comments?post=710"}],"version-history":[{"count":2,"href":"https:\/\/milecastle27.co.uk\/rob\/wp-json\/wp\/v2\/posts\/710\/revisions"}],"predecessor-version":[{"id":712,"href":"https:\/\/milecastle27.co.uk\/rob\/wp-json\/wp\/v2\/posts\/710\/revisions\/712"}],"wp:attachment":[{"href":"https:\/\/milecastle27.co.uk\/rob\/wp-json\/wp\/v2\/media?parent=710"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/milecastle27.co.uk\/rob\/wp-json\/wp\/v2\/categories?post=710"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/milecastle27.co.uk\/rob\/wp-json\/wp\/v2\/tags?post=710"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}