Menu
▾
▴
hsqldb-user — User discussions about HSQLdb
You can subscribe to this list here.
| 2001 |
Jan
|
Feb
|
Mar
|
Apr
(7) |
May
(10) |
Jun
(8) |
Jul
(5) |
Aug
(13) |
Sep
(7) |
Oct
(13) |
Nov
(1) |
Dec
(10) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2002 |
Jan
(5) |
Feb
(3) |
Mar
(23) |
Apr
(23) |
May
(15) |
Jun
(16) |
Jul
(22) |
Aug
(29) |
Sep
(98) |
Oct
(60) |
Nov
(47) |
Dec
(26) |
| 2003 |
Jan
(30) |
Feb
(36) |
Mar
(54) |
Apr
(96) |
May
(108) |
Jun
(58) |
Jul
(150) |
Aug
(61) |
Sep
(75) |
Oct
(100) |
Nov
(52) |
Dec
(57) |
| 2004 |
Jan
(93) |
Feb
(82) |
Mar
(183) |
Apr
(73) |
May
(154) |
Jun
(67) |
Jul
(178) |
Aug
(155) |
Sep
(60) |
Oct
(74) |
Nov
(79) |
Dec
(55) |
| 2005 |
Jan
(81) |
Feb
(103) |
Mar
(74) |
Apr
(102) |
May
(87) |
Jun
(76) |
Jul
(41) |
Aug
(33) |
Sep
(74) |
Oct
(42) |
Nov
(75) |
Dec
(68) |
| 2006 |
Jan
(36) |
Feb
(21) |
Mar
(67) |
Apr
(71) |
May
(47) |
Jun
(7) |
Jul
(32) |
Aug
(30) |
Sep
(21) |
Oct
(49) |
Nov
(22) |
Dec
(29) |
| 2007 |
Jan
(48) |
Feb
(17) |
Mar
(23) |
Apr
(31) |
May
(35) |
Jun
(31) |
Jul
(19) |
Aug
(12) |
Sep
(35) |
Oct
(8) |
Nov
(44) |
Dec
(38) |
| 2008 |
Jan
(74) |
Feb
(77) |
Mar
(17) |
Apr
(21) |
May
(29) |
Jun
(10) |
Jul
(23) |
Aug
(21) |
Sep
(11) |
Oct
(67) |
Nov
(20) |
Dec
(17) |
| 2009 |
Jan
(17) |
Feb
(30) |
Mar
(56) |
Apr
(61) |
May
(20) |
Jun
(57) |
Jul
(58) |
Aug
(41) |
Sep
(44) |
Oct
(82) |
Nov
(52) |
Dec
(36) |
| 2010 |
Jan
(33) |
Feb
(43) |
Mar
(37) |
Apr
(57) |
May
(29) |
Jun
(58) |
Jul
(71) |
Aug
(69) |
Sep
(21) |
Oct
(50) |
Nov
(49) |
Dec
(43) |
| 2011 |
Jan
(64) |
Feb
(36) |
Mar
(54) |
Apr
(50) |
May
(52) |
Jun
(38) |
Jul
(49) |
Aug
(52) |
Sep
(25) |
Oct
(50) |
Nov
(30) |
Dec
(24) |
| 2012 |
Jan
(22) |
Feb
(56) |
Mar
(28) |
Apr
(40) |
May
(19) |
Jun
(8) |
Jul
(61) |
Aug
(24) |
Sep
(61) |
Oct
(41) |
Nov
(28) |
Dec
(24) |
| 2013 |
Jan
(17) |
Feb
(24) |
Mar
(22) |
Apr
(7) |
May
(23) |
Jun
(18) |
Jul
(12) |
Aug
(9) |
Sep
(16) |
Oct
(8) |
Nov
(5) |
Dec
(10) |
| 2014 |
Jan
(10) |
Feb
(3) |
Mar
(13) |
Apr
(1) |
May
(4) |
Jun
(1) |
Jul
(3) |
Aug
(11) |
Sep
|
Oct
(3) |
Nov
(3) |
Dec
|
| 2015 |
Jan
(8) |
Feb
|
Mar
(2) |
Apr
(5) |
May
(3) |
Jun
(8) |
Jul
(5) |
Aug
|
Sep
|
Oct
(10) |
Nov
(3) |
Dec
(8) |
| 2016 |
Jan
|
Feb
(10) |
Mar
(12) |
Apr
(2) |
May
(4) |
Jun
(1) |
Jul
|
Aug
|
Sep
(12) |
Oct
|
Nov
|
Dec
(10) |
| 2017 |
Jan
(8) |
Feb
(2) |
Mar
(10) |
Apr
(4) |
May
(2) |
Jun
|
Jul
|
Aug
(6) |
Sep
(5) |
Oct
|
Nov
|
Dec
(2) |
| 2018 |
Jan
(8) |
Feb
|
Mar
(1) |
Apr
(2) |
May
(5) |
Jun
|
Jul
|
Aug
|
Sep
|
Oct
|
Nov
|
Dec
|
| 2019 |
Jan
|
Feb
|
Mar
(2) |
Apr
|
May
(11) |
Jun
(3) |
Jul
|
Aug
(14) |
Sep
(1) |
Oct
(6) |
Nov
(9) |
Dec
(1) |
| 2020 |
Jan
|
Feb
|
Mar
(1) |
Apr
(1) |
May
(4) |
Jun
(9) |
Jul
(4) |
Aug
|
Sep
|
Oct
|
Nov
(15) |
Dec
(7) |
| 2021 |
Jan
(4) |
Feb
|
Mar
(2) |
Apr
(13) |
May
|
Jun
|
Jul
(4) |
Aug
(2) |
Sep
|
Oct
(7) |
Nov
(10) |
Dec
|
| 2022 |
Jan
|
Feb
|
Mar
|
Apr
(4) |
May
(3) |
Jun
(1) |
Jul
(3) |
Aug
(4) |
Sep
|
Oct
(3) |
Nov
(8) |
Dec
|
| 2023 |
Jan
(4) |
Feb
(1) |
Mar
(13) |
Apr
(1) |
May
(5) |
Jun
(4) |
Jul
(4) |
Aug
|
Sep
|
Oct
|
Nov
|
Dec
|
| 2024 |
Jan
(1) |
Feb
|
Mar
(3) |
Apr
|
May
(3) |
Jun
(2) |
Jul
(10) |
Aug
|
Sep
(2) |
Oct
|
Nov
|
Dec
|
|
From: <da...@da...> - 2024-09-17 17:04:13
|
Hello, The release candidate 2.7.4 at a cursory level has no issue with my database access GUI tool. A simple test was conducted on editing an existing database table with all data types. A review of JDBC meta-data indicates no changes from 2.7.3. Dana M. Proctor Ajqvue Project Manager |
|
From: Fred T. <fr...@us...> - 2024-09-15 18:51:16
|
The jar is available from SourceForge: https://sourceforge.net/projects/hsqldb/files/release_candidate/ Please test and report any issue in the Help Forum. Fred Toussi HSQL Development Group |
|
From: Fred T. <fr...@us...> - 2024-07-11 20:17:30
|
1) The database must be running when you export. No need to shut down. 2) You can sync both ways. 3) It's better to get the EXPORT and IMPORT working in the first place. You can do the automation later. For your setup, you need to automate periodic export and import, so the replica is not far behind the primary. After recovery from crash the I don't know about 'all null on the receiving side'. What did you look at. The commands you included are for export and import of the full data. You will be using versioning variants of the commands after the initial setup. Fred On Thu, Jul 11, 2024, at 16:01, Ken Lemoine wrote: > Thanks Fred. I've been trying the export/import as you mentioned and when it imports, it's all 'null' on the receiving side. I'll keep fiddling with it, I think I'm close. > Below is my currently failing attempts at exporting the entire DB (both schema and data), I've also tried bringing only the data over with the same result. Should this work to just get a replica database on a second machine? > > PERFORM EXPORT SCRIPT FOR DATABASE WITH COLUMN NAMES TO 'export_script_all.sql'; > PERFORM IMPORT SCRIPT DATA FROM 'export_script_all.sql' CONTINUE ON ERROR; > > Three questions I have now are - > > 1) Does it matter if the database is running when I export, or ideally would I shut it down first? > 2) When you mentioned "*sync only the changes in the primary database with the secondary*", that seems to imply we can sync both ways? That would be ideal. Is that supported also? > > Brief description of what our use case is - We have a two machine setup, a primary and secondary. The secondary is idle, it will never receive any traffic unless the primary if offline. So we need a way to get the data to the secondary machine, a great first step, but ideally, we would like to be able to fail back to primary if it comes back online. If this is two completely seperate things in HSQLDB, I'd rather build the latter, but if we can get just the replica going now and roll out versioned tables and two-way sync, that would be fantabulous. So with that: > 3) can I build the replica now and roll out a two-way sync later? > > Thanks again, > > Ken > > > *From:* Fred Toussi via Hsqldb-user <hsq...@li...> > *Sent:* Wednesday, July 10, 2024 12:36 PM > *To:* HSQLDB user list <hsq...@li...> > *Cc:* Fred Toussi <fr...@us...> > *Subject:* Re: [Hsqldb-user] Duplicate HSQLDB - Export/Import from one instance to a new instance. > > There is no need for the Transfer Tool. > > You can create a dump of the database using the PERFORM EXPORT statement. You can then use PERFORM IMPORT statement to load the dump into the current database. > > If you want to sync only the changes in the primary database with the secondary, enable system versioning for the tables. See the guide at http://hsqldb.org/doc/2.0/guide/management-chapt.html#mtc_system_versioned_tables and the addtional description of the syntax in the Guide. > > Fred Toussi > > On Wed, Jul 10, 2024, at 15:37, Ken Lemoine wrote: >> Thanks, I'm still unable to get a simple export to work using this transfer tool. Can anyone give me the command syntax? If the usage is only --dump, how can I pass it the connection details and authentication (sa) ? Is there a transfer tool documentation that explains all this in more detail? >> >> Here's roughly how I'm trying it now >> >> java -cp hsqldbutil.jar org.hsqldb.util.Transfer jdbc:hsqldb:hsql:/path/to/prod/db/hsql;user=sa;password= --dump >> >> *-bash: --dump: command not found* >> ** >> Same error. I'm stuck, any help would be appreciated. Thanks! >> >> Sincerely, >> Ken >> >> >> >> *From:* Andrew Richardson <sp...@gm...> >> *Sent:* Tuesday, July 9, 2024 7:14 AM >> *To:* User discussions about HyperSQL Database Engine. <hsq...@li...> >> *Subject:* Re: [Hsqldb-user] Duplicate HSQLDB - Export/Import from one instance to a new instance. >> >> Per the help text, the tool only supports the switches specified. Thus the " --database1 jdbc:hsqldb:hsql:/path/to/prod/db/hsql;user=sa;password= " text is a problem. >> Suspect command line use is not supported presently...you might need to add that support yourself, the source code is there. >> I was able to get the dump file generated using the GUI, but did not try the restore function. >> I do not have any further input for you. >> Good luck with your project! >> >> On Mon, Jul 8, 2024 at 8:59 PM Ken Lemoine <ken...@cy...> wrote: >>> Thanks for the timely response, Andrew. >>> >>> I've got a shiny new transfer tool (hsqldbutil.jar) compiled and tried it every which way to no avail. >>> >>> Here's my last attempt, with the response - I'm sure there's something I'm missing still. The hsqldbutil.jar is in my local directory. >>> >>>> java -cp hsqldbutil.jar org.hsqldb.util.Transfer --database1 jdbc:hsqldb:hsql:/path/to/prod/db/hsql;user=sa;password= --dump >>> >>> Wondering if I need to change the jdbc connection to use 'file' vice 'hsql' since we're using a .script file, but I can't seem to get past this message, as if it doesn't know what the --dump parameter is: >>> >>> *Exception in thread "main" java.lang.IllegalArgumentException: Try: java org.hsqldb.util.Transfer --help* >>> * at org.hsqldb.util.Transfer.main(Unknown Source)* >>> *-bash: --dump: command not found* >>> >>> >>> All the --help gives me is the usage (-help,-dump,-restore). It also seems to sometimes complain of the lack of X11 since it can also be run graphically, but I do not want that, the goal here is to have a script that can export data frequently, xfer it to another machine and have another script to import it. >>> >>> Appreciate any help you can provide, and happy Monday! >>> >>> Regards, >>> >>> >>> *From:* Andrew Richardson <sp...@gm...> >>> *Sent:* Saturday, July 6, 2024 1:57 PM >>> *To:* User discussions about HyperSQL Database Engine. <hsq...@li...> >>> *Subject:* Re: [Hsqldb-user] Duplicate HSQLDB - Export/Import from one instance to a new instance. >>> >>> Hello again <sorry for the spam> >>> Out of curiosity, I looked into how to generate the Transfer Tool. Here is what worked for me on Windows. >>> >>> cd hsqldb-2.7.3\hsqldb\build >>> gradlew hsqldbutil >>> java -cp ..\lib\hsqldbutil.jar org.hsqldb.util.Transfer --dump >>> >>> Maybe this saves you a little time. >>> Cheers! >>> >>> On Sat, Jul 6, 2024 at 12:59 PM Andrew Richardson <sp...@gm...> wrote: >>>> Hello Ken >>>> Not sure if this will cover all your requirements, but have a look at the "Transfer Tool" utility. Might be a place to start. >>>> https://hsqldb.org/doc/2.0/util-guide/transfer-tool-chapt.html >>>> >>>> Cheers, >>>> Andrew Richardson >>>> >>>> On Fri, Jul 5, 2024 at 4:18 PM Ken Lemoine <ken...@cy...> wrote: >>>>> Hello, >>>>> >>>>> Apologies in advance if this has been asked before, I'm just not finding much information in the archives. >>>>> >>>>> We have a hsqldb instance that needs to be replicated to a secondary. I've tried and failed 3 different methods, mostly using the sqltool.jar and once by simply copying over the hsql.script file and crossing my fingers. The final attempt I just completed does seemingly create a valid 'exporteddata.sql' file, I can see it in there. Then when I import it on the second machine, it also seemingly imports it - it outputs to stdout so I know it did SOMEthing. But, the data is not showing up in the application. >>>>> >>>>> This is the latest hsqldb, running on latest FreeBSD 14. >>>>> >>>>> I'll also be needing a way of fully automating this, to keep a second machine up to date, and have struggled also getting a script to do this, but at this point, I really just want to see if I can get this working manually. >>>>> >>>>> Any tips or hints, or a point in the right direction would be appreciated. >>>>> >>>>> Thanks! >>>>> >>>>> Ken >>>>> *Ken Lemoine* | Deployment Manager >>>>> >>>>> (614) 344-6500 >>>>> >>>>> www.cyberzek.com >>>>> >>>>> >>>>> _______________________________________________ >>>>> Hsqldb-user mailing list >>>>> Hsq...@li... >>>>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user >>> *Ken Lemoine* | Deployment Manager >>> >>> (614) 344-6500 >>> >>> www.cyberzek.com >>> >>> >>> _______________________________________________ >>> Hsqldb-user mailing list >>> Hsq...@li... >>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user >> *Ken Lemoine* | Deployment Manager >> >> (614) 344-6500 >> >> www.cyberzek.com >> >> >> >> _______________________________________________ >> Hsqldb-user mailing list >> Hsq...@li... >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user >> > > *Ken Lemoine* | Deployment Manager > > (614) 344-6500 > > www.cyberzek.com > > > > _______________________________________________ > Hsqldb-user mailing list > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > |
|
From: Ken L. <ken...@cy...> - 2024-07-11 15:18:01
|
Thanks Fred. I've been trying the export/import as you mentioned and when it imports, it's all 'null' on the receiving side. I'll keep fiddling with it, I think I'm close.
Below is my currently failing attempts at exporting the entire DB (both schema and data), I've also tried bringing only the data over with the same result. Should this work to just get a replica database on a second machine?
PERFORM EXPORT SCRIPT FOR DATABASE WITH COLUMN NAMES TO 'export_script_all.sql';
PERFORM IMPORT SCRIPT DATA FROM 'export_script_all.sql' CONTINUE ON ERROR;
Three questions I have now are -
1) Does it matter if the database is running when I export, or ideally would I shut it down first?
2) When you mentioned "sync only the changes in the primary database with the secondary", that seems to imply we can sync both ways? That would be ideal. Is that supported also?
Brief description of what our use case is - We have a two machine setup, a primary and secondary. The secondary is idle, it will never receive any traffic unless the primary if offline. So we need a way to get the data to the secondary machine, a great first step, but ideally, we would like to be able to fail back to primary if it comes back online. If this is two completely seperate things in HSQLDB, I'd rather build the latter, but if we can get just the replica going now and roll out versioned tables and two-way sync, that would be fantabulous. So with that:
3) can I build the replica now and roll out a two-way sync later?
Thanks again,
Ken
________________________________
From: Fred Toussi via Hsqldb-user <hsq...@li...>
Sent: Wednesday, July 10, 2024 12:36 PM
To: HSQLDB user list <hsq...@li...>
Cc: Fred Toussi <fr...@us...>
Subject: Re: [Hsqldb-user] Duplicate HSQLDB - Export/Import from one instance to a new instance.
There is no need for the Transfer Tool.
You can create a dump of the database using the PERFORM EXPORT statement. You can then use PERFORM IMPORT statement to load the dump into the current database.
If you want to sync only the changes in the primary database with the secondary, enable system versioning for the tables. See the guide at http://hsqldb.org/doc/2.0/guide/management-chapt.html#mtc_system_versioned_tables and the addtional description of the syntax in the Guide.
Fred Toussi
On Wed, Jul 10, 2024, at 15:37, Ken Lemoine wrote:
Thanks, I'm still unable to get a simple export to work using this transfer tool. Can anyone give me the command syntax? If the usage is only --dump, how can I pass it the connection details and authentication (sa) ? Is there a transfer tool documentation that explains all this in more detail?
Here's roughly how I'm trying it now
java -cp hsqldbutil.jar org.hsqldb.util.Transfer jdbc:hsqldb:hsql:/path/to/prod/db/hsql;user=sa;password= --dump
-bash: --dump: command not found
Same error. I'm stuck, any help would be appreciated. Thanks!
Sincerely,
Ken
________________________________
From: Andrew Richardson <sp...@gm...>
Sent: Tuesday, July 9, 2024 7:14 AM
To: User discussions about HyperSQL Database Engine. <hsq...@li...>
Subject: Re: [Hsqldb-user] Duplicate HSQLDB - Export/Import from one instance to a new instance.
Per the help text, the tool only supports the switches specified. Thus the " --database1 jdbc:hsqldb:hsql:/path/to/prod/db/hsql;user=sa;password= " text is a problem.
Suspect command line use is not supported presently...you might need to add that support yourself, the source code is there.
I was able to get the dump file generated using the GUI, but did not try the restore function.
I do not have any further input for you.
Good luck with your project!
On Mon, Jul 8, 2024 at 8:59 PM Ken Lemoine <ken...@cy...<mailto:ken...@cy...>> wrote:
Thanks for the timely response, Andrew.
I've got a shiny new transfer tool (hsqldbutil.jar) compiled and tried it every which way to no avail.
Here's my last attempt, with the response - I'm sure there's something I'm missing still. The hsqldbutil.jar is in my local directory.
java -cp hsqldbutil.jar org.hsqldb.util.Transfer --database1 jdbc:hsqldb:hsql:/path/to/prod/db/hsql;user=sa;password= --dump
Wondering if I need to change the jdbc connection to use 'file' vice 'hsql' since we're using a .script file, but I can't seem to get past this message, as if it doesn't know what the --dump parameter is:
Exception in thread "main" java.lang.IllegalArgumentException: Try: java org.hsqldb.util.Transfer --help
at org.hsqldb.util.Transfer.main(Unknown Source)
-bash: --dump: command not found
All the --help gives me is the usage (-help,-dump,-restore). It also seems to sometimes complain of the lack of X11 since it can also be run graphically, but I do not want that, the goal here is to have a script that can export data frequently, xfer it to another machine and have another script to import it.
Appreciate any help you can provide, and happy Monday!
Regards,
________________________________
From: Andrew Richardson <sp...@gm...<mailto:sp...@gm...>>
Sent: Saturday, July 6, 2024 1:57 PM
To: User discussions about HyperSQL Database Engine. <hsq...@li...<mailto:hsq...@li...>>
Subject: Re: [Hsqldb-user] Duplicate HSQLDB - Export/Import from one instance to a new instance.
Hello again <sorry for the spam>
Out of curiosity, I looked into how to generate the Transfer Tool. Here is what worked for me on Windows.
cd hsqldb-2.7.3\hsqldb\build
gradlew hsqldbutil
java -cp ..\lib\hsqldbutil.jar org.hsqldb.util.Transfer --dump
Maybe this saves you a little time.
Cheers!
On Sat, Jul 6, 2024 at 12:59 PM Andrew Richardson <sp...@gm...<mailto:sp...@gm...>> wrote:
Hello Ken
Not sure if this will cover all your requirements, but have a look at the "Transfer Tool" utility. Might be a place to start.
https://hsqldb.org/doc/2.0/util-guide/transfer-tool-chapt.html
Cheers,
Andrew Richardson
On Fri, Jul 5, 2024 at 4:18 PM Ken Lemoine <ken...@cy...<mailto:ken...@cy...>> wrote:
Hello,
Apologies in advance if this has been asked before, I'm just not finding much information in the archives.
We have a hsqldb instance that needs to be replicated to a secondary. I've tried and failed 3 different methods, mostly using the sqltool.jar and once by simply copying over the hsql.script file and crossing my fingers. The final attempt I just completed does seemingly create a valid 'exporteddata.sql' file, I can see it in there. Then when I import it on the second machine, it also seemingly imports it - it outputs to stdout so I know it did SOMEthing. But, the data is not showing up in the application.
This is the latest hsqldb, running on latest FreeBSD 14.
I'll also be needing a way of fully automating this, to keep a second machine up to date, and have struggled also getting a script to do this, but at this point, I really just want to see if I can get this working manually.
Any tips or hints, or a point in the right direction would be appreciated.
Thanks!
Ken
Ken Lemoine | Deployment Manager
(614) 344-6500
www.cyberzek.com<http://www.cyberzek.com>
[https://static.wixstatic.com/media/7044be_7929e43cb3664f7f87bd4b5d091e73f5~mv2.png/v1/fill/w_282,h_68,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/CYBERZEK%20Logo%20-%20Color%20-%20Horizontal.png]
_______________________________________________
Hsqldb-user mailing list
Hsq...@li...<mailto:Hsq...@li...>
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Ken Lemoine | Deployment Manager
(614) 344-6500
www.cyberzek.com<http://www.cyberzek.com>
[https://static.wixstatic.com/media/7044be_7929e43cb3664f7f87bd4b5d091e73f5~mv2.png/v1/fill/w_282,h_68,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/CYBERZEK%20Logo%20-%20Color%20-%20Horizontal.png]
_______________________________________________
Hsqldb-user mailing list
Hsq...@li...<mailto:Hsq...@li...>
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Ken Lemoine | Deployment Manager
(614) 344-6500
www.cyberzek.com
[https://static.wixstatic.com/media/7044be_7929e43cb3664f7f87bd4b5d091e73f5~mv2.png/v1/fill/w_282,h_68,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/CYBERZEK%20Logo%20-%20Color%20-%20Horizontal.png]
_______________________________________________
Hsqldb-user mailing list
Hsq...@li...<mailto:Hsq...@li...>
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Ken Lemoine | Deployment Manager
(614) 344-6500
www.cyberzek.com
[https://static.wixstatic.com/media/7044be_7929e43cb3664f7f87bd4b5d091e73f5~mv2.png/v1/fill/w_282,h_68,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/CYBERZEK%20Logo%20-%20Color%20-%20Horizontal.png]
|
|
From: Fred T. <fr...@us...> - 2024-07-10 16:52:10
|
There is no need for the Transfer Tool. You can create a dump of the database using the PERFORM EXPORT statement. You can then use PERFORM IMPORT statement to load the dump into the current database. If you want to sync only the changes in the primary database with the secondary, enable system versioning for the tables. See the guide at http://hsqldb.org/doc/2.0/guide/management-chapt.html#mtc_system_versioned_tables and the addtional description of the syntax in the Guide. Fred Toussi On Wed, Jul 10, 2024, at 15:37, Ken Lemoine wrote: > Thanks, I'm still unable to get a simple export to work using this transfer tool. Can anyone give me the command syntax? If the usage is only --dump, how can I pass it the connection details and authentication (sa) ? Is there a transfer tool documentation that explains all this in more detail? > > Here's roughly how I'm trying it now > > java -cp hsqldbutil.jar org.hsqldb.util.Transfer jdbc:hsqldb:hsql:/path/to/prod/db/hsql;user=sa;password= --dump > > *-bash: --dump: command not found* > ** > Same error. I'm stuck, any help would be appreciated. Thanks! > > Sincerely, > Ken > > > > *From:* Andrew Richardson <sp...@gm...> > *Sent:* Tuesday, July 9, 2024 7:14 AM > *To:* User discussions about HyperSQL Database Engine. <hsq...@li...> > *Subject:* Re: [Hsqldb-user] Duplicate HSQLDB - Export/Import from one instance to a new instance. > > Per the help text, the tool only supports the switches specified. Thus the " --database1 jdbc:hsqldb:hsql:/path/to/prod/db/hsql;user=sa;password= " text is a problem. > Suspect command line use is not supported presently...you might need to add that support yourself, the source code is there. > I was able to get the dump file generated using the GUI, but did not try the restore function. > I do not have any further input for you. > Good luck with your project! > > On Mon, Jul 8, 2024 at 8:59 PM Ken Lemoine <ken...@cy...> wrote: >> Thanks for the timely response, Andrew. >> >> I've got a shiny new transfer tool (hsqldbutil.jar) compiled and tried it every which way to no avail. >> >> Here's my last attempt, with the response - I'm sure there's something I'm missing still. The hsqldbutil.jar is in my local directory. >> >>> java -cp hsqldbutil.jar org.hsqldb.util.Transfer --database1 jdbc:hsqldb:hsql:/path/to/prod/db/hsql;user=sa;password= --dump >> >> Wondering if I need to change the jdbc connection to use 'file' vice 'hsql' since we're using a .script file, but I can't seem to get past this message, as if it doesn't know what the --dump parameter is: >> >> *Exception in thread "main" java.lang.IllegalArgumentException: Try: java org.hsqldb.util.Transfer --help* >> * at org.hsqldb.util.Transfer.main(Unknown Source)* >> *-bash: --dump: command not found* >> >> >> All the --help gives me is the usage (-help,-dump,-restore). It also seems to sometimes complain of the lack of X11 since it can also be run graphically, but I do not want that, the goal here is to have a script that can export data frequently, xfer it to another machine and have another script to import it. >> >> Appreciate any help you can provide, and happy Monday! >> >> Regards, >> >> >> *From:* Andrew Richardson <sp...@gm...> >> *Sent:* Saturday, July 6, 2024 1:57 PM >> *To:* User discussions about HyperSQL Database Engine. <hsq...@li...> >> *Subject:* Re: [Hsqldb-user] Duplicate HSQLDB - Export/Import from one instance to a new instance. >> >> Hello again <sorry for the spam> >> Out of curiosity, I looked into how to generate the Transfer Tool. Here is what worked for me on Windows. >> >> cd hsqldb-2.7.3\hsqldb\build >> gradlew hsqldbutil >> java -cp ..\lib\hsqldbutil.jar org.hsqldb.util.Transfer --dump >> >> Maybe this saves you a little time. >> Cheers! >> >> On Sat, Jul 6, 2024 at 12:59 PM Andrew Richardson <sp...@gm...> wrote: >>> Hello Ken >>> Not sure if this will cover all your requirements, but have a look at the "Transfer Tool" utility. Might be a place to start. >>> https://hsqldb.org/doc/2.0/util-guide/transfer-tool-chapt.html >>> >>> Cheers, >>> Andrew Richardson >>> >>> On Fri, Jul 5, 2024 at 4:18 PM Ken Lemoine <ken...@cy...> wrote: >>>> Hello, >>>> >>>> Apologies in advance if this has been asked before, I'm just not finding much information in the archives. >>>> >>>> We have a hsqldb instance that needs to be replicated to a secondary. I've tried and failed 3 different methods, mostly using the sqltool.jar and once by simply copying over the hsql.script file and crossing my fingers. The final attempt I just completed does seemingly create a valid 'exporteddata.sql' file, I can see it in there. Then when I import it on the second machine, it also seemingly imports it - it outputs to stdout so I know it did SOMEthing. But, the data is not showing up in the application. >>>> >>>> This is the latest hsqldb, running on latest FreeBSD 14. >>>> >>>> I'll also be needing a way of fully automating this, to keep a second machine up to date, and have struggled also getting a script to do this, but at this point, I really just want to see if I can get this working manually. >>>> >>>> Any tips or hints, or a point in the right direction would be appreciated. >>>> >>>> Thanks! >>>> >>>> Ken >>>> *Ken Lemoine* | Deployment Manager >>>> >>>> (614) 344-6500 >>>> >>>> www.cyberzek.com >>>> >>>> >>>> _______________________________________________ >>>> Hsqldb-user mailing list >>>> Hsq...@li... >>>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user >> *Ken Lemoine* | Deployment Manager >> >> (614) 344-6500 >> >> www.cyberzek.com >> >> >> _______________________________________________ >> Hsqldb-user mailing list >> Hsq...@li... >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user > *Ken Lemoine* | Deployment Manager > > (614) 344-6500 > > www.cyberzek.com > > > > _______________________________________________ > Hsqldb-user mailing list > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > |
|
From: Ken L. <ken...@cy...> - 2024-07-10 16:12:57
|
Thanks, I'm still unable to get a simple export to work using this transfer tool. Can anyone give me the command syntax? If the usage is only --dump, how can I pass it the connection details and authentication (sa) ? Is there a transfer tool documentation that explains all this in more detail? Here's roughly how I'm trying it now java -cp hsqldbutil.jar org.hsqldb.util.Transfer jdbc:hsqldb:hsql:/path/to/prod/db/hsql;user=sa;password= --dump -bash: --dump: command not found Same error. I'm stuck, any help would be appreciated. Thanks! Sincerely, Ken ________________________________ From: Andrew Richardson <sp...@gm...> Sent: Tuesday, July 9, 2024 7:14 AM To: User discussions about HyperSQL Database Engine. <hsq...@li...> Subject: Re: [Hsqldb-user] Duplicate HSQLDB - Export/Import from one instance to a new instance. Per the help text, the tool only supports the switches specified. Thus the " --database1 jdbc:hsqldb:hsql:/path/to/prod/db/hsql;user=sa;password= " text is a problem. Suspect command line use is not supported presently...you might need to add that support yourself, the source code is there. I was able to get the dump file generated using the GUI, but did not try the restore function. I do not have any further input for you. Good luck with your project! On Mon, Jul 8, 2024 at 8:59 PM Ken Lemoine <ken...@cy...<mailto:ken...@cy...>> wrote: Thanks for the timely response, Andrew. I've got a shiny new transfer tool (hsqldbutil.jar) compiled and tried it every which way to no avail. Here's my last attempt, with the response - I'm sure there's something I'm missing still. The hsqldbutil.jar is in my local directory. java -cp hsqldbutil.jar org.hsqldb.util.Transfer --database1 jdbc:hsqldb:hsql:/path/to/prod/db/hsql;user=sa;password= --dump Wondering if I need to change the jdbc connection to use 'file' vice 'hsql' since we're using a .script file, but I can't seem to get past this message, as if it doesn't know what the --dump parameter is: Exception in thread "main" java.lang.IllegalArgumentException: Try: java org.hsqldb.util.Transfer --help at org.hsqldb.util.Transfer.main(Unknown Source) -bash: --dump: command not found All the --help gives me is the usage (-help,-dump,-restore). It also seems to sometimes complain of the lack of X11 since it can also be run graphically, but I do not want that, the goal here is to have a script that can export data frequently, xfer it to another machine and have another script to import it. Appreciate any help you can provide, and happy Monday! Regards, ________________________________ From: Andrew Richardson <sp...@gm...<mailto:sp...@gm...>> Sent: Saturday, July 6, 2024 1:57 PM To: User discussions about HyperSQL Database Engine. <hsq...@li...<mailto:hsq...@li...>> Subject: Re: [Hsqldb-user] Duplicate HSQLDB - Export/Import from one instance to a new instance. Hello again <sorry for the spam> Out of curiosity, I looked into how to generate the Transfer Tool. Here is what worked for me on Windows. cd hsqldb-2.7.3\hsqldb\build gradlew hsqldbutil java -cp ..\lib\hsqldbutil.jar org.hsqldb.util.Transfer --dump Maybe this saves you a little time. Cheers! On Sat, Jul 6, 2024 at 12:59 PM Andrew Richardson <sp...@gm...<mailto:sp...@gm...>> wrote: Hello Ken Not sure if this will cover all your requirements, but have a look at the "Transfer Tool" utility. Might be a place to start. https://hsqldb.org/doc/2.0/util-guide/transfer-tool-chapt.html Cheers, Andrew Richardson On Fri, Jul 5, 2024 at 4:18 PM Ken Lemoine <ken...@cy...<mailto:ken...@cy...>> wrote: Hello, Apologies in advance if this has been asked before, I'm just not finding much information in the archives. We have a hsqldb instance that needs to be replicated to a secondary. I've tried and failed 3 different methods, mostly using the sqltool.jar and once by simply copying over the hsql.script file and crossing my fingers. The final attempt I just completed does seemingly create a valid 'exporteddata.sql' file, I can see it in there. Then when I import it on the second machine, it also seemingly imports it - it outputs to stdout so I know it did SOMEthing. But, the data is not showing up in the application. This is the latest hsqldb, running on latest FreeBSD 14. I'll also be needing a way of fully automating this, to keep a second machine up to date, and have struggled also getting a script to do this, but at this point, I really just want to see if I can get this working manually. Any tips or hints, or a point in the right direction would be appreciated. Thanks! Ken Ken Lemoine | Deployment Manager (614) 344-6500 www.cyberzek.com<http://www.cyberzek.com> [https://static.wixstatic.com/media/7044be_7929e43cb3664f7f87bd4b5d091e73f5~mv2.png/v1/fill/w_282,h_68,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/CYBERZEK%20Logo%20-%20Color%20-%20Horizontal.png] _______________________________________________ Hsqldb-user mailing list Hsq...@li...<mailto:Hsq...@li...> https://lists.sourceforge.net/lists/listinfo/hsqldb-user Ken Lemoine | Deployment Manager (614) 344-6500 www.cyberzek.com<http://www.cyberzek.com> [https://static.wixstatic.com/media/7044be_7929e43cb3664f7f87bd4b5d091e73f5~mv2.png/v1/fill/w_282,h_68,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/CYBERZEK%20Logo%20-%20Color%20-%20Horizontal.png] _______________________________________________ Hsqldb-user mailing list Hsq...@li...<mailto:Hsq...@li...> https://lists.sourceforge.net/lists/listinfo/hsqldb-user Ken Lemoine | Deployment Manager (614) 344-6500 www.cyberzek.com [https://static.wixstatic.com/media/7044be_7929e43cb3664f7f87bd4b5d091e73f5~mv2.png/v1/fill/w_282,h_68,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/CYBERZEK%20Logo%20-%20Color%20-%20Horizontal.png] |
|
From: Andrew R. <sp...@gm...> - 2024-07-09 11:14:50
|
Per the help text, the tool only supports the switches specified. Thus the " --database1 jdbc:hsqldb:hsql:/path/to/prod/db/hsql;user=sa;password= " text is a problem. Suspect command line use is not supported presently...you might need to add that support yourself, the source code is there. I was able to get the dump file generated using the GUI, but did not try the restore function. I do not have any further input for you. Good luck with your project! On Mon, Jul 8, 2024 at 8:59 PM Ken Lemoine <ken...@cy...> wrote: > Thanks for the timely response, Andrew. > > I've got a shiny new transfer tool (hsqldbutil.jar) compiled and tried it > every which way to no avail. > > Here's my last attempt, with the response - I'm sure there's something I'm > missing still. The hsqldbutil.jar is in my local directory. > > java -cp hsqldbutil.jar org.hsqldb.util.Transfer --database1 > jdbc:hsqldb:hsql:/path/to/prod/db/hsql;user=sa;password= --dump > > > Wondering if I need to change the jdbc connection to use 'file' vice > 'hsql' since we're using a .script file, but I can't seem to get past this > message, as if it doesn't know what the --dump parameter is: > > *Exception in thread "main" java.lang.IllegalArgumentException: Try: java > org.hsqldb.util.Transfer --help* > * at org.hsqldb.util.Transfer.main(Unknown Source)* > *-bash: --dump: command not found* > > All the --help gives me is the usage (-help,-dump,-restore). It also > seems to sometimes complain of the lack of X11 since it can also be run > graphically, but I do not want that, the goal here is to have a script that > can export data frequently, xfer it to another machine and have another > script to import it. > > Appreciate any help you can provide, and happy Monday! > > Regards, > > ------------------------------ > *From:* Andrew Richardson <sp...@gm...> > *Sent:* Saturday, July 6, 2024 1:57 PM > *To:* User discussions about HyperSQL Database Engine. < > hsq...@li...> > *Subject:* Re: [Hsqldb-user] Duplicate HSQLDB - Export/Import from one > instance to a new instance. > > Hello again <sorry for the spam> > Out of curiosity, I looked into how to generate the Transfer Tool. Here > is what worked for me on Windows. > > cd hsqldb-2.7.3\hsqldb\build > gradlew hsqldbutil > java -cp ..\lib\hsqldbutil.jar org.hsqldb.util.Transfer --dump > > Maybe this saves you a little time. > Cheers! > > On Sat, Jul 6, 2024 at 12:59 PM Andrew Richardson <sp...@gm...> > wrote: > > Hello Ken > Not sure if this will cover all your requirements, but have a look at the > "Transfer Tool" utility. Might be a place to start. > https://hsqldb.org/doc/2.0/util-guide/transfer-tool-chapt.html > > Cheers, > Andrew Richardson > > On Fri, Jul 5, 2024 at 4:18 PM Ken Lemoine <ken...@cy...> > wrote: > > Hello, > > Apologies in advance if this has been asked before, I'm just not finding > much information in the archives. > > We have a hsqldb instance that needs to be replicated to a secondary. > I've tried and failed 3 different methods, mostly using the sqltool.jar and > once by simply copying over the hsql.script file and crossing my fingers. > The final attempt I just completed does seemingly create a valid > 'exporteddata.sql' file, I can see it in there. Then when I import it on > the second machine, it also seemingly imports it - it outputs to stdout so > I know it did SOMEthing. But, the data is not showing up in the > application. > > This is the latest hsqldb, running on latest FreeBSD 14. > > I'll also be needing a way of fully automating this, to keep a second > machine up to date, and have struggled also getting a script to do this, > but at this point, I really just want to see if I can get this working > manually. > > Any tips or hints, or a point in the right direction would > be appreciated. > > Thanks! > > Ken > > *Ken Lemoine* | Deployment Manager > > (614) 344-6500 > > www.cyberzek.com > _______________________________________________ > Hsqldb-user mailing list > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > > *Ken Lemoine* | Deployment Manager > > (614) 344-6500 > > www.cyberzek.com > _______________________________________________ > Hsqldb-user mailing list > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > |
|
From: Ken L. <ken...@cy...> - 2024-07-09 00:59:16
|
Thanks for the timely response, Andrew. I've got a shiny new transfer tool (hsqldbutil.jar) compiled and tried it every which way to no avail. Here's my last attempt, with the response - I'm sure there's something I'm missing still. The hsqldbutil.jar is in my local directory. java -cp hsqldbutil.jar org.hsqldb.util.Transfer --database1 jdbc:hsqldb:hsql:/path/to/prod/db/hsql;user=sa;password= --dump Wondering if I need to change the jdbc connection to use 'file' vice 'hsql' since we're using a .script file, but I can't seem to get past this message, as if it doesn't know what the --dump parameter is: Exception in thread "main" java.lang.IllegalArgumentException: Try: java org.hsqldb.util.Transfer --help at org.hsqldb.util.Transfer.main(Unknown Source) -bash: --dump: command not found All the --help gives me is the usage (-help,-dump,-restore). It also seems to sometimes complain of the lack of X11 since it can also be run graphically, but I do not want that, the goal here is to have a script that can export data frequently, xfer it to another machine and have another script to import it. Appreciate any help you can provide, and happy Monday! Regards, ________________________________ From: Andrew Richardson <sp...@gm...> Sent: Saturday, July 6, 2024 1:57 PM To: User discussions about HyperSQL Database Engine. <hsq...@li...> Subject: Re: [Hsqldb-user] Duplicate HSQLDB - Export/Import from one instance to a new instance. Hello again <sorry for the spam> Out of curiosity, I looked into how to generate the Transfer Tool. Here is what worked for me on Windows. cd hsqldb-2.7.3\hsqldb\build gradlew hsqldbutil java -cp ..\lib\hsqldbutil.jar org.hsqldb.util.Transfer --dump Maybe this saves you a little time. Cheers! On Sat, Jul 6, 2024 at 12:59 PM Andrew Richardson <sp...@gm...<mailto:sp...@gm...>> wrote: Hello Ken Not sure if this will cover all your requirements, but have a look at the "Transfer Tool" utility. Might be a place to start. https://hsqldb.org/doc/2.0/util-guide/transfer-tool-chapt.html Cheers, Andrew Richardson On Fri, Jul 5, 2024 at 4:18 PM Ken Lemoine <ken...@cy...<mailto:ken...@cy...>> wrote: Hello, Apologies in advance if this has been asked before, I'm just not finding much information in the archives. We have a hsqldb instance that needs to be replicated to a secondary. I've tried and failed 3 different methods, mostly using the sqltool.jar and once by simply copying over the hsql.script file and crossing my fingers. The final attempt I just completed does seemingly create a valid 'exporteddata.sql' file, I can see it in there. Then when I import it on the second machine, it also seemingly imports it - it outputs to stdout so I know it did SOMEthing. But, the data is not showing up in the application. This is the latest hsqldb, running on latest FreeBSD 14. I'll also be needing a way of fully automating this, to keep a second machine up to date, and have struggled also getting a script to do this, but at this point, I really just want to see if I can get this working manually. Any tips or hints, or a point in the right direction would be appreciated. Thanks! Ken Ken Lemoine | Deployment Manager (614) 344-6500 www.cyberzek.com<http://www.cyberzek.com> [https://static.wixstatic.com/media/7044be_7929e43cb3664f7f87bd4b5d091e73f5~mv2.png/v1/fill/w_282,h_68,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/CYBERZEK%20Logo%20-%20Color%20-%20Horizontal.png] _______________________________________________ Hsqldb-user mailing list Hsq...@li...<mailto:Hsq...@li...> https://lists.sourceforge.net/lists/listinfo/hsqldb-user Ken Lemoine | Deployment Manager (614) 344-6500 www.cyberzek.com [https://static.wixstatic.com/media/7044be_7929e43cb3664f7f87bd4b5d091e73f5~mv2.png/v1/fill/w_282,h_68,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/CYBERZEK%20Logo%20-%20Color%20-%20Horizontal.png] |
|
From: David A. D. <an...@de...> - 2024-07-08 22:20:53
|
I've been trying to do something similar, generate a standalone db for
offline access.
So I want to look into this conversation. If I get anywhere ("take a
seat") I'll post any results.
THANKS for the post & responses
David
On 5/07/2024 11:45, Ken Lemoine wrote:
> Hello,
>
> Apologies in advance if this has been asked before, I'm just not
> finding much information in the archives.
>
> We have a hsqldb instance that needs to be replicated to a secondary.
> I've tried and failed 3 different methods, mostly using the
> sqltool.jar and once by simply copying over the hsql.script file and
> crossing my fingers. The final attempt I just completed does
> seemingly create a valid 'exporteddata.sql' file, I can see it in
> there. Then when I import it on the second machine, it also seemingly
> imports it - it outputs to stdout so I know it did SOMEthing. But,
> the data is not showing up in the application.
>
> This is the latest hsqldb, running on latest FreeBSD 14.
>
> I'll also be needing a way of fully automating this, to keep a second
> machine up to date, and have struggled also getting a script to do
> this, but at this point, I really just want to see if I can get this
> working manually.
>
> Any tips or hints, or a point in the right direction would
> be appreciated.
>
> Thanks!
>
> Ken
>
> *Ken Lemoine* | Deployment Manager
>
> (614) 344-6500
>
> www.cyberzek.com
>
>
>
> _______________________________________________
> Hsqldb-user mailing list
> Hsq...@li...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
|
From: Andrew R. <sp...@gm...> - 2024-07-06 17:57:27
|
Hello again <sorry for the spam> Out of curiosity, I looked into how to generate the Transfer Tool. Here is what worked for me on Windows. cd hsqldb-2.7.3\hsqldb\build gradlew hsqldbutil java -cp ..\lib\hsqldbutil.jar org.hsqldb.util.Transfer --dump Maybe this saves you a little time. Cheers! On Sat, Jul 6, 2024 at 12:59 PM Andrew Richardson <sp...@gm...> wrote: > Hello Ken > Not sure if this will cover all your requirements, but have a look at the > "Transfer Tool" utility. Might be a place to start. > https://hsqldb.org/doc/2.0/util-guide/transfer-tool-chapt.html > > Cheers, > Andrew Richardson > > On Fri, Jul 5, 2024 at 4:18 PM Ken Lemoine <ken...@cy...> > wrote: > >> Hello, >> >> Apologies in advance if this has been asked before, I'm just not finding >> much information in the archives. >> >> We have a hsqldb instance that needs to be replicated to a secondary. >> I've tried and failed 3 different methods, mostly using the sqltool.jar and >> once by simply copying over the hsql.script file and crossing my fingers. >> The final attempt I just completed does seemingly create a valid >> 'exporteddata.sql' file, I can see it in there. Then when I import it on >> the second machine, it also seemingly imports it - it outputs to stdout so >> I know it did SOMEthing. But, the data is not showing up in the >> application. >> >> This is the latest hsqldb, running on latest FreeBSD 14. >> >> I'll also be needing a way of fully automating this, to keep a second >> machine up to date, and have struggled also getting a script to do this, >> but at this point, I really just want to see if I can get this working >> manually. >> >> Any tips or hints, or a point in the right direction would >> be appreciated. >> >> Thanks! >> >> Ken >> >> *Ken Lemoine* | Deployment Manager >> >> (614) 344-6500 >> >> www.cyberzek.com >> _______________________________________________ >> Hsqldb-user mailing list >> Hsq...@li... >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user >> > |
|
From: Andrew R. <sp...@gm...> - 2024-07-06 16:59:53
|
Hello Ken Not sure if this will cover all your requirements, but have a look at the "Transfer Tool" utility. Might be a place to start. https://hsqldb.org/doc/2.0/util-guide/transfer-tool-chapt.html Cheers, Andrew Richardson On Fri, Jul 5, 2024 at 4:18 PM Ken Lemoine <ken...@cy...> wrote: > Hello, > > Apologies in advance if this has been asked before, I'm just not finding > much information in the archives. > > We have a hsqldb instance that needs to be replicated to a secondary. > I've tried and failed 3 different methods, mostly using the sqltool.jar and > once by simply copying over the hsql.script file and crossing my fingers. > The final attempt I just completed does seemingly create a valid > 'exporteddata.sql' file, I can see it in there. Then when I import it on > the second machine, it also seemingly imports it - it outputs to stdout so > I know it did SOMEthing. But, the data is not showing up in the > application. > > This is the latest hsqldb, running on latest FreeBSD 14. > > I'll also be needing a way of fully automating this, to keep a second > machine up to date, and have struggled also getting a script to do this, > but at this point, I really just want to see if I can get this working > manually. > > Any tips or hints, or a point in the right direction would > be appreciated. > > Thanks! > > Ken > > *Ken Lemoine* | Deployment Manager > > (614) 344-6500 > > www.cyberzek.com > _______________________________________________ > Hsqldb-user mailing list > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > |
|
From: Ken L. <ken...@cy...> - 2024-07-05 20:17:46
|
Hello, Apologies in advance if this has been asked before, I'm just not finding much information in the archives. We have a hsqldb instance that needs to be replicated to a secondary. I've tried and failed 3 different methods, mostly using the sqltool.jar and once by simply copying over the hsql.script file and crossing my fingers. The final attempt I just completed does seemingly create a valid 'exporteddata.sql' file, I can see it in there. Then when I import it on the second machine, it also seemingly imports it - it outputs to stdout so I know it did SOMEthing. But, the data is not showing up in the application. This is the latest hsqldb, running on latest FreeBSD 14. I'll also be needing a way of fully automating this, to keep a second machine up to date, and have struggled also getting a script to do this, but at this point, I really just want to see if I can get this working manually. Any tips or hints, or a point in the right direction would be appreciated. Thanks! Ken Ken Lemoine | Deployment Manager (614) 344-6500 www.cyberzek.com [https://static.wixstatic.com/media/7044be_7929e43cb3664f7f87bd4b5d091e73f5~mv2.png/v1/fill/w_282,h_68,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/CYBERZEK%20Logo%20-%20Color%20-%20Horizontal.png] |
|
From: Michael D. <mic...@ic...> - 2024-06-14 15:15:00
|
This is very nice work. How did you pull and display the Metadata? On May 23, 2024, at 12:58 PM, danap via Hsqldb-user <hsq...@li...> wrote: Hello, This is a bit much, but I have one primary test for databases via their datatypes and JDBC meta-data returned. Testing is for adding, editing, deleting and import/export of SQL DDL and DML. This does not test all functionality of course. Issues: None -- Host: localhost -- Generated On: 2024.05.23 AD at 10:12:50 MDT -- SQL version: HSQL Database Engine 2.7.3 -- Database: hsql_datatypes -- -- ------------------------------------------ -- -- Table structure for table "PUBLIC"."HSQLTYPES" -- DROP TABLE IF EXISTS "PUBLIC"."HSQLTYPES"; CREATE TABLE "PUBLIC"."HSQLTYPES" ( "DATA_TYPE_ID" INTEGER IDENTITY NOT NULL, "CHAR_TYPE" CHAR(30) DEFAULT NULL, "VARCHAR_TYPE" VARCHAR(30) DEFAULT NULL, "LONGVARCHAR_TYPE" LONGVARCHAR DEFAULT NULL, "CLOB_TYPE" CLOB DEFAULT NULL, "BINARY_TYPE" BINARY(1) DEFAULT NULL, "VARBINARY_TYPE" VARBINARY(200000) DEFAULT NULL, "LONGVARBINARY_TYPE" LONGVARBINARY DEFAULT NULL, "BLOB_TYPE" BLOB DEFAULT NULL, "TINYINT_TYPE" TINYINT DEFAULT NULL, "SMALLINT_TYPE" SMALLINT DEFAULT NULL, "INT_TYPE" INTEGER DEFAULT NULL, "BIGINT_TYPE" BIGINT DEFAULT NULL, "FLOAT_TYPE" DOUBLE DEFAULT NULL, "DOUBLE_TYPE" DOUBLE DEFAULT NULL, "REAL_TYPE" DOUBLE DEFAULT NULL, "DECIMAL_TYPE" DECIMAL(16,2) DEFAULT NULL, "NUMERIC_TYPE" NUMERIC(128,32) DEFAULT NULL, "UUID_TYPE" UUID DEFAULT NULL, "BOOLEAN_TYPE" BOOLEAN DEFAULT NULL, "BIT_TYPE" BIT(1) DEFAULT NULL, "BIT2_TYPE" BIT(2) DEFAULT NULL, "BITVARYING_TYPE" BIT VARYING(5) DEFAULT NULL, "DATE_TYPE" DATE DEFAULT NULL, "TIME_TYPE" TIME(3) DEFAULT NULL, "TIMETMZ_TYPE" TIME(3) WITH TIME ZONE DEFAULT NULL, "DATETIME_TYPE" TIMESTAMP DEFAULT NULL, "TIMESTAMP_TYPE" TIMESTAMP(3) DEFAULT NULL, "TIMESTAMPTMZ_TYPE" TIMESTAMP(3) WITH TIME ZONE DEFAULT NULL, "INTERVAL_TYPE" INTERVAL YEAR(3) DEFAULT NULL ); HyperSQL 2.7.3 (JDBC Meta Data) Changes: None Additions: Added UUID Type. index Name Class Type Type Name Scale Precision Size 1 DATA_TYPE_ID java.lang.Integer 4 INTEGER 0 32 11 2 CHAR_TYPE java.lang.String 1 CHARACTER 0 30 30 3 VARCHAR_TYPE java.lang.String 12 VARCHAR 0 30 30 4 LONGVARCHAR_TYPE java.lang.String 12 VARCHAR 0 16777216 16777216 5 CLOB_TYPE java.sql.Clob 2005 CLOB 0 1073741824 1073741824 6 BINARY_TYPE [B -2 BINARY 0 0 0 7 VARBINARY_TYPE [B -3 VARBINARY 0 200000 200000 8 LONGVARBINARY_TYPE [B -3 VARBINARY 0 16777216 16777216 9 BLOB_TYPE java.sql.Blob 2004 BLOB 0 1073741824 1073741824 10 TINYINT_TYPE java.lang.Integer -6 TINYINT 0 8 4 11 SMALLINT_TYPE java.lang.Integer 5 SMALLINT 0 16 6 12 INT_TYPE java.lang.Integer 4 INTEGER 0 32 11 13 BIGINT_TYPE java.lang.Long -5 BIGINT 0 64 20 14 FLOAT_TYPE java.lang.Double 8 DOUBLE 0 64 23 15 DOUBLE_TYPE java.lang.Double 8 DOUBLE 0 64 23 16 REAL_TYPE java.lang.Double 8 DOUBLE 0 64 23 17 DECIMAL_TYPE java.math.BigDecimal 3 DECIMAL 2 16 18 18 NUMERIC_TYPE java.math.BigDecimal 2 NUMERIC 32 128 130 19 UUID_TYPE [B -2 UUID 0 16 36 20 BOOLEAN_TYPE java.lang.Boolean 16 BOOLEAN 0 0 5 21 BIT_TYPE [B -7 BIT 0 1 1 22 BIT2_TYPE [B -7 BIT 0 2 2 23 BITVARYING_TYPE [B -7 BIT VARYING 0 5 5 24 DATE_TYPE java.sql.Date 91 DATE 0 10 10 25 TIME_TYPE java.sql.Time 92 TIME 3 12 12 26 TIMETMZ_TYPE java.time.OffsetTime 2013 TIME WITH TIME ZONE 3 18 18 27 DATETIME_TYPE java.sql.Timestamp 93 TIMESTAMP 6 26 26 28 TIMESTAMP_TYPE java.sql.Timestamp 93 TIMESTAMP 3 23 23 29 TIMESTAMPTMZ_TYPE java.time.OffsetDateTime 2014 TIMESTAMP WITH TIME ZONE 3 29 29 30 INTERVAL_TYPE java.lang.String 12 INTERVAL YEAR 0 4 4 Dana M. Proctor Ajqvue Project Manager. _______________________________________________ Hsqldb-user mailing list Hsq...@li... https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
|
From: <hsq...@du...> - 2024-06-04 23:02:52
|
Hello, anybody out there? Just nod if you can hear me... --- Ken Lemoine [Duke of Good](https://dukeofgood.com)[Substack](https://dukeofgood.substack.com/) On Friday, May 31st, 2024 at 5:03 AM, hsq...@li... [via Relay] <hsq...@du...> wrote: > [relay icon] > > Forwarded from [hsq...@du...](https://relay.firefox.com/accounts/profile/#hsqlmaillist%40dukeofgood.mozmail.com) by [Firefox Relay Premium](https://relay.firefox.com/accounts/profile/) > > 0 email trackers removed > > [Manage this mask](https://relay.firefox.com/accounts/profile/#hsqlmaillist%40dukeofgood.mozmail.com) > > Version 2.7.3 download package is now available at SourceForge via [http://hsqldb.org](http://hsqldb.org/download/) The default jars require Java 11 or later and can be used as Java modules. Java 8 versions of the jars are also included in the package. Jars are also available on Maven [https://repo1.maven.org/maven2/org/hsqldb/.](https://repo1.maven.org/maven2/org/hsqldb/) > > This version has some new features and enhancements. The documentation on the web site covers the latest release. > > Fred Toussi > HSQL Development Group > [relay logo](https://relay.firefox.com) [Your dashboard](https://relay.firefox.com/accounts/profile) |
|
From: Fred T. <fr...@us...> - 2024-05-31 09:02:55
|
Version 2.7.3 download package is now available at SourceForge via http://hsqldb.org <http://hsqldb.org/download/> The default jars require Java 11 or later and can be used as Java modules. Java 8 versions of the jars are also included in the package. Jars are also available on Maven https://repo1.maven.org/maven2/org/hsqldb/. This version has some new features and enhancements. The documentation on the web site covers the latest release. Fred Toussi HSQL Development Group |
|
From: <da...@da...> - 2024-05-23 17:28:23
|
Hello,
This is a bit much, but I have one primary test for databases
via their datatypes and JDBC meta-data returned. Testing
is for adding, editing, deleting and import/export of SQL
DDL and DML. This does not test all functionality of course.
Issues: None
-- Host: localhost
-- Generated On: 2024.05.23 AD at 10:12:50 MDT
-- SQL version: HSQL Database Engine 2.7.3
-- Database: hsql_datatypes
--
-- ------------------------------------------
--
-- Table structure for table "PUBLIC"."HSQLTYPES"
--
DROP TABLE IF EXISTS "PUBLIC"."HSQLTYPES";
CREATE TABLE "PUBLIC"."HSQLTYPES" (
"DATA_TYPE_ID" INTEGER IDENTITY NOT NULL,
"CHAR_TYPE" CHAR(30) DEFAULT NULL,
"VARCHAR_TYPE" VARCHAR(30) DEFAULT NULL,
"LONGVARCHAR_TYPE" LONGVARCHAR DEFAULT NULL,
"CLOB_TYPE" CLOB DEFAULT NULL,
"BINARY_TYPE" BINARY(1) DEFAULT NULL,
"VARBINARY_TYPE" VARBINARY(200000) DEFAULT NULL,
"LONGVARBINARY_TYPE" LONGVARBINARY DEFAULT NULL,
"BLOB_TYPE" BLOB DEFAULT NULL,
"TINYINT_TYPE" TINYINT DEFAULT NULL,
"SMALLINT_TYPE" SMALLINT DEFAULT NULL,
"INT_TYPE" INTEGER DEFAULT NULL,
"BIGINT_TYPE" BIGINT DEFAULT NULL,
"FLOAT_TYPE" DOUBLE DEFAULT NULL,
"DOUBLE_TYPE" DOUBLE DEFAULT NULL,
"REAL_TYPE" DOUBLE DEFAULT NULL,
"DECIMAL_TYPE" DECIMAL(16,2) DEFAULT NULL,
"NUMERIC_TYPE" NUMERIC(128,32) DEFAULT NULL,
"UUID_TYPE" UUID DEFAULT NULL,
"BOOLEAN_TYPE" BOOLEAN DEFAULT NULL,
"BIT_TYPE" BIT(1) DEFAULT NULL,
"BIT2_TYPE" BIT(2) DEFAULT NULL,
"BITVARYING_TYPE" BIT VARYING(5) DEFAULT NULL,
"DATE_TYPE" DATE DEFAULT NULL,
"TIME_TYPE" TIME(3) DEFAULT NULL,
"TIMETMZ_TYPE" TIME(3) WITH TIME ZONE DEFAULT NULL,
"DATETIME_TYPE" TIMESTAMP DEFAULT NULL,
"TIMESTAMP_TYPE" TIMESTAMP(3) DEFAULT NULL,
"TIMESTAMPTMZ_TYPE" TIMESTAMP(3) WITH TIME ZONE DEFAULT NULL,
"INTERVAL_TYPE" INTERVAL YEAR(3) DEFAULT NULL
);
HyperSQL 2.7.3 (JDBC Meta Data)
Changes: None
Additions: Added UUID Type.
index Name Class Type Type Name Scale
Precision Size
1 DATA_TYPE_ID java.lang.Integer 4 INTEGER 0
32 11
2 CHAR_TYPE java.lang.String 1 CHARACTER 0
30 30
3 VARCHAR_TYPE java.lang.String 12 VARCHAR 0
30 30
4 LONGVARCHAR_TYPE java.lang.String 12 VARCHAR 0
16777216 16777216
5 CLOB_TYPE java.sql.Clob 2005 CLOB 0
1073741824 1073741824
6 BINARY_TYPE [B -2 BINARY 0
0 0
7 VARBINARY_TYPE [B -3 VARBINARY 0
200000 200000
8 LONGVARBINARY_TYPE [B -3 VARBINARY 0
16777216 16777216
9 BLOB_TYPE java.sql.Blob 2004 BLOB 0
1073741824 1073741824
10 TINYINT_TYPE java.lang.Integer -6 TINYINT 0
8 4
11 SMALLINT_TYPE java.lang.Integer 5 SMALLINT 0
16 6
12 INT_TYPE java.lang.Integer 4 INTEGER 0
32 11
13 BIGINT_TYPE java.lang.Long -5 BIGINT 0
64 20
14 FLOAT_TYPE java.lang.Double 8 DOUBLE 0
64 23
15 DOUBLE_TYPE java.lang.Double 8 DOUBLE 0
64 23
16 REAL_TYPE java.lang.Double 8 DOUBLE 0
64 23
17 DECIMAL_TYPE java.math.BigDecimal 3 DECIMAL 2
16 18
18 NUMERIC_TYPE java.math.BigDecimal 2 NUMERIC 32
128 130
19 UUID_TYPE [B -2 UUID 0
16 36
20 BOOLEAN_TYPE java.lang.Boolean 16 BOOLEAN 0
0 5
21 BIT_TYPE [B -7 BIT 0
1 1
22 BIT2_TYPE [B -7 BIT 0
2 2
23 BITVARYING_TYPE [B -7 BIT VARYING 0
5 5
24 DATE_TYPE java.sql.Date 91 DATE 0
10 10
25 TIME_TYPE java.sql.Time 92 TIME 3
12 12
26 TIMETMZ_TYPE java.time.OffsetTime 2013 TIME WITH TIME ZONE
3 18 18
27 DATETIME_TYPE java.sql.Timestamp 93 TIMESTAMP 6
26 26
28 TIMESTAMP_TYPE java.sql.Timestamp 93 TIMESTAMP 3
23 23
29 TIMESTAMPTMZ_TYPE java.time.OffsetDateTime 2014 TIMESTAMP WITH TIME
ZONE 3 29 29
30 INTERVAL_TYPE java.lang.String 12 INTERVAL YEAR 0
4 4
Dana M. Proctor
Ajqvue Project Manager.
|
|
From: Fred T. <fr...@us...> - 2024-05-22 13:49:11
|
Please download the jar and test. The general release is due within 10 days. The Release Candidate jar is available here: https://sourceforge.net/projects/hsqldb/files/release_candidate/ See the change list and updated documentation at https://hsqldb.org/web/hsqlDocsFrame.html |
|
From: <da...@da...> - 2024-03-24 21:26:19
|
Hello, Most development Environments/IDEs will allow you to select resources for export for your build. See Properties Export. Manually as an example my project. danap@gx520c:~/development/Java/Ajqvue$ cat ajqvue.mf Manifest-Version: 3.1 Created-By: dp Specification-Title: Ajqvue Specification-Version: 3.4 Specification-Vender: Dandy Made Productions Specification-URL: http://dandymadeproductions.com Main-Class: com.dandymadeproductions.ajqvue.Ajqvue Class-Path: lib/commons-net-ftp-3.6.jar lib/itextpdf-5.5.13.1.jar lib/hsqldb.jar danap@gx520c:~/development/Java/Ajqvue$ danap@gx520c:~/development/Java/Ajqvue$ cat build.sh jar cvfm Ajqvue.jar ajqvue.mf -C bin/ com /lib/hslqdb.jar docs/Manual docs/Release/*.html With this the jar will include my source, bin, and the library hsqldb.jar, the Manual folder and Release html files. danap |
|
From: Fred T. <fr...@us...> - 2024-03-24 21:06:33
|
You can use: connection.createArrayOf(String typeName, Object[] elements) You can use JDBCArrayBasic in HSQLDB routines written in Java. Fred On Sun, Mar 24, 2024, at 10:42, pieter wrote: > Hi, > > I am a long time user of hsqldb. > Trying to port my application to use java's module system I am unable > to get access to org.hsqldb.types.Type as org.hsqldb.types is not > exported. > > org.hsqldb.types.Type is used in JDBCArrayBasic so I am not sure why it > not exported. > > If its a bug please export it, if not, am I not suppose to be using > JDBCArrayBasic? > > Thanks > Pieter > > > _______________________________________________ > Hsqldb-user mailing list > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
|
From: pieter <pie...@ri...> - 2024-03-24 10:42:45
|
Hi, I am a long time user of hsqldb. Trying to port my application to use java's module system I am unable to get access to org.hsqldb.types.Type as org.hsqldb.types is not exported. org.hsqldb.types.Type is used in JDBCArrayBasic so I am not sure why it not exported. If its a bug please export it, if not, am I not suppose to be using JDBCArrayBasic? Thanks Pieter |
|
From: Jorge G. de A. <jor...@gm...> - 2024-01-24 16:15:43
|
Hello, Transpose function would be very useful. Jorge "JR" |
|
From: Fred T. <fr...@us...> - 2023-07-29 21:27:33
|
I don't know what JPA is doing and how this relies on the order of function creation.
I would suggest using different function names.
On Sat, Jul 29, 2023, at 20:45, Chris Rankin wrote:
> Hi, thanks for replying.
>
> I was hoping that JPA's `setParameter()` API could somehow inform the
> SQL parser about the value's _actual_ type at runtime :-(.
>
> Does this mean that swapping the order of the CREATE FUNCTION
> statement so that the variant with the INT parameter is created first
> is not _supposed_ to make any difference?
>
> Thanks again,
> Chris
>
> On Sat, 29 Jul 2023 at 19:26, Fred Toussi via Hsqldb-user
> <hsq...@li...> wrote:
>>
>> HSQLDB can use the correct signature of the function if enough information is available in the SELECT statement.
>>
>> SELECT ... WHERE JsonFieldAsText(a, :index) = :value
>>
>> In the above statement, the type of the :index variable is not known to the SQL parser and it defaults to a VARCHAR type. When writing an SQL statement, JsonFieldAsText(a, CAST(;index AS INT)) can be used to force the INT type.
>>
>> Fred
>>
>> On Sat, Jul 29, 2023, at 17:49, Chris Rankin wrote:
>> > Hi,
>> >
>> > I am using HSQLDB 2.7.2, and I am writing some Java functions that act
>> > like the Postgres "->>" operator, e.g.
>> >
>> > a ->> b = field 'b' from JSON object
>> > a ->> 1 = element [1] from JSON array
>> >
>> > So I've mapped two Java functions into HSQLDB, using SQL like this:
>> >
>> > CREATE FUNCTION JsonFieldAsText(IN json VARCHAR(32768), IN fieldIndex INT)
>> > RETURNS VARCHAR(32768)
>> > LANGUAGE JAVA DETERMINISTIC NO SQL
>> > EXTERNAL NAME 'CLASSPATH:MyClass.fieldByIndex
>> >
>> > CREATE FUNCTION JsonFieldAsText(IN json VARCHAR(32768), IN fieldIName
>> > VARCHAR(50))
>> > RETURNS VARCHAR(32768)
>> > LANGUAGE JAVA DETERMINISTIC NO SQL
>> > EXTERNAL NAME 'CLASSPATH:MyClass.fieldByName
>> >
>> > The idea is that;
>> >
>> > "a ->> b" becomes "JsonFieldAsText(a, 'b')", and
>> > "a ->> 1" becomes "JsonFieldAsText(a, 1)"
>> >
>> > This all seems OK, except for when I tried to execute a JPA Query:
>> >
>> > "SELECT ... WHERE JsonFieldAsText(a, :index) = :value"
>> >
>> > having set the index parameter using "setParameter("index", 2)".
>> >
>> > My understanding was that HSQLDB would choose the JsonFieldAsText()
>> > function whose signature provided the best match to the parameters,
>> > i.e. the one that maps to MyClass.fieldAsIndex(). However, I was
>> > alarmed to see HSQLDB choose to execute the MyClass.fieldAsName()
>> > variant instead.
>> >
>> > I have currently "resolved" this issue by swapping the order of my
>> > CREATE FUNCTION statements so that the one mapping to
>> > MyClass.fieldAsIndex() is executed first, but I could be relying on
>> > undocumented behaviour here, for all I know.
>> >
>> > Can anyone advise me as to whether there's a better way to do this please?
>> >
>> > Thanks,
>> > Chris
>> >
>> >
>> > _______________________________________________
>> > Hsqldb-user mailing list
>> > Hsq...@li...
>> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>
>>
>> _______________________________________________
>> Hsqldb-user mailing list
>> Hsq...@li...
>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
|
|
From: Chris R. <ran...@gm...> - 2023-07-29 19:45:33
|
Hi, thanks for replying.
I was hoping that JPA's `setParameter()` API could somehow inform the
SQL parser about the value's _actual_ type at runtime :-(.
Does this mean that swapping the order of the CREATE FUNCTION
statement so that the variant with the INT parameter is created first
is not _supposed_ to make any difference?
Thanks again,
Chris
On Sat, 29 Jul 2023 at 19:26, Fred Toussi via Hsqldb-user
<hsq...@li...> wrote:
>
> HSQLDB can use the correct signature of the function if enough information is available in the SELECT statement.
>
> SELECT ... WHERE JsonFieldAsText(a, :index) = :value
>
> In the above statement, the type of the :index variable is not known to the SQL parser and it defaults to a VARCHAR type. When writing an SQL statement, JsonFieldAsText(a, CAST(;index AS INT)) can be used to force the INT type.
>
> Fred
>
> On Sat, Jul 29, 2023, at 17:49, Chris Rankin wrote:
> > Hi,
> >
> > I am using HSQLDB 2.7.2, and I am writing some Java functions that act
> > like the Postgres "->>" operator, e.g.
> >
> > a ->> b = field 'b' from JSON object
> > a ->> 1 = element [1] from JSON array
> >
> > So I've mapped two Java functions into HSQLDB, using SQL like this:
> >
> > CREATE FUNCTION JsonFieldAsText(IN json VARCHAR(32768), IN fieldIndex INT)
> > RETURNS VARCHAR(32768)
> > LANGUAGE JAVA DETERMINISTIC NO SQL
> > EXTERNAL NAME 'CLASSPATH:MyClass.fieldByIndex
> >
> > CREATE FUNCTION JsonFieldAsText(IN json VARCHAR(32768), IN fieldIName
> > VARCHAR(50))
> > RETURNS VARCHAR(32768)
> > LANGUAGE JAVA DETERMINISTIC NO SQL
> > EXTERNAL NAME 'CLASSPATH:MyClass.fieldByName
> >
> > The idea is that;
> >
> > "a ->> b" becomes "JsonFieldAsText(a, 'b')", and
> > "a ->> 1" becomes "JsonFieldAsText(a, 1)"
> >
> > This all seems OK, except for when I tried to execute a JPA Query:
> >
> > "SELECT ... WHERE JsonFieldAsText(a, :index) = :value"
> >
> > having set the index parameter using "setParameter("index", 2)".
> >
> > My understanding was that HSQLDB would choose the JsonFieldAsText()
> > function whose signature provided the best match to the parameters,
> > i.e. the one that maps to MyClass.fieldAsIndex(). However, I was
> > alarmed to see HSQLDB choose to execute the MyClass.fieldAsName()
> > variant instead.
> >
> > I have currently "resolved" this issue by swapping the order of my
> > CREATE FUNCTION statements so that the one mapping to
> > MyClass.fieldAsIndex() is executed first, but I could be relying on
> > undocumented behaviour here, for all I know.
> >
> > Can anyone advise me as to whether there's a better way to do this please?
> >
> > Thanks,
> > Chris
> >
> >
> > _______________________________________________
> > Hsqldb-user mailing list
> > Hsq...@li...
> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
>
> _______________________________________________
> Hsqldb-user mailing list
> Hsq...@li...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
|
|
From: Fred T. <fr...@us...> - 2023-07-29 18:25:57
|
HSQLDB can use the correct signature of the function if enough information is available in the SELECT statement.
SELECT ... WHERE JsonFieldAsText(a, :index) = :value
In the above statement, the type of the :index variable is not known to the SQL parser and it defaults to a VARCHAR type. When writing an SQL statement, JsonFieldAsText(a, CAST(;index AS INT)) can be used to force the INT type.
Fred
On Sat, Jul 29, 2023, at 17:49, Chris Rankin wrote:
> Hi,
>
> I am using HSQLDB 2.7.2, and I am writing some Java functions that act
> like the Postgres "->>" operator, e.g.
>
> a ->> b = field 'b' from JSON object
> a ->> 1 = element [1] from JSON array
>
> So I've mapped two Java functions into HSQLDB, using SQL like this:
>
> CREATE FUNCTION JsonFieldAsText(IN json VARCHAR(32768), IN fieldIndex INT)
> RETURNS VARCHAR(32768)
> LANGUAGE JAVA DETERMINISTIC NO SQL
> EXTERNAL NAME 'CLASSPATH:MyClass.fieldByIndex
>
> CREATE FUNCTION JsonFieldAsText(IN json VARCHAR(32768), IN fieldIName
> VARCHAR(50))
> RETURNS VARCHAR(32768)
> LANGUAGE JAVA DETERMINISTIC NO SQL
> EXTERNAL NAME 'CLASSPATH:MyClass.fieldByName
>
> The idea is that;
>
> "a ->> b" becomes "JsonFieldAsText(a, 'b')", and
> "a ->> 1" becomes "JsonFieldAsText(a, 1)"
>
> This all seems OK, except for when I tried to execute a JPA Query:
>
> "SELECT ... WHERE JsonFieldAsText(a, :index) = :value"
>
> having set the index parameter using "setParameter("index", 2)".
>
> My understanding was that HSQLDB would choose the JsonFieldAsText()
> function whose signature provided the best match to the parameters,
> i.e. the one that maps to MyClass.fieldAsIndex(). However, I was
> alarmed to see HSQLDB choose to execute the MyClass.fieldAsName()
> variant instead.
>
> I have currently "resolved" this issue by swapping the order of my
> CREATE FUNCTION statements so that the one mapping to
> MyClass.fieldAsIndex() is executed first, but I could be relying on
> undocumented behaviour here, for all I know.
>
> Can anyone advise me as to whether there's a better way to do this please?
>
> Thanks,
> Chris
>
>
> _______________________________________________
> Hsqldb-user mailing list
> Hsq...@li...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
|
|
From: Chris R. <ran...@gm...> - 2023-07-29 16:49:33
|
Hi,
I am using HSQLDB 2.7.2, and I am writing some Java functions that act
like the Postgres "->>" operator, e.g.
a ->> b = field 'b' from JSON object
a ->> 1 = element [1] from JSON array
So I've mapped two Java functions into HSQLDB, using SQL like this:
CREATE FUNCTION JsonFieldAsText(IN json VARCHAR(32768), IN fieldIndex INT)
RETURNS VARCHAR(32768)
LANGUAGE JAVA DETERMINISTIC NO SQL
EXTERNAL NAME 'CLASSPATH:MyClass.fieldByIndex
CREATE FUNCTION JsonFieldAsText(IN json VARCHAR(32768), IN fieldIName
VARCHAR(50))
RETURNS VARCHAR(32768)
LANGUAGE JAVA DETERMINISTIC NO SQL
EXTERNAL NAME 'CLASSPATH:MyClass.fieldByName
The idea is that;
"a ->> b" becomes "JsonFieldAsText(a, 'b')", and
"a ->> 1" becomes "JsonFieldAsText(a, 1)"
This all seems OK, except for when I tried to execute a JPA Query:
"SELECT ... WHERE JsonFieldAsText(a, :index) = :value"
having set the index parameter using "setParameter("index", 2)".
My understanding was that HSQLDB would choose the JsonFieldAsText()
function whose signature provided the best match to the parameters,
i.e. the one that maps to MyClass.fieldAsIndex(). However, I was
alarmed to see HSQLDB choose to execute the MyClass.fieldAsName()
variant instead.
I have currently "resolved" this issue by swapping the order of my
CREATE FUNCTION statements so that the one mapping to
MyClass.fieldAsIndex() is executed first, but I could be relying on
undocumented behaviour here, for all I know.
Can anyone advise me as to whether there's a better way to do this please?
Thanks,
Chris
|
68 messages has been excluded from this view by a project administrator.