OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[solved] Can I export queries?
Goto page Previous  1, 2
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
liherb
General User
General User


Joined: 19 Aug 2011
Posts: 38

PostPosted: Sun Jan 01, 2012 2:53 pm    Post subject: Reply with quote

I guess it is a good time to mark this thread as "solved". Thank you dacm! You are very helpful Very Happy
Back to top
View user's profile Send private message
liherb
General User
General User


Joined: 19 Aug 2011
Posts: 38

PostPosted: Sun Feb 19, 2012 12:28 am    Post subject: Reply with quote

dacm wrote:


I would just pick an RDBMS and stick with it:
Base+SQLite^ >> Dabo+Python+SQLite^
Base+JDBC^* >> Dabo+Jython+JDBC^* or Dabo+Python+PyJdbc+JDBC^*
Base+Firebird >> Dabo+Python+Firebird
Base+MySQL >> Dabo+Python+MySQL
Base+PostgreSQL >> Dabo+Python+PostgreSQL

^ indicates in-process (embedded) mode available for seamless, single-user access.
* JDBC = HSQLDB 1.8, HSQLDB 2.x, H2 or Apache Derby.


Excuse me, does JDBC here refer to HyperSQL JDBC driver(that .jar file under /lib folder)?
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sun Feb 19, 2012 5:47 am    Post subject: Reply with quote

liherb wrote:
dacm wrote:
* JDBC = HSQLDB 1.8, HSQLDB 2.x, H2 or Apache Derby.


Excuse me, does JDBC here refer to HyperSQL JDBC driver(that .jar file under /lib folder)?

Yes. The JDBC driver is built-into the .jar file (hsqldb.jar in this case).
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
liherb
General User
General User


Joined: 19 Aug 2011
Posts: 38

PostPosted: Sun Feb 19, 2012 11:09 am    Post subject: Reply with quote

Hi DACM, by the way could you list some pros and cons regarding H2 vs HSQLDB for the case of single desktop user? I've checked H2's website and their latest version seems a bit newer than HSQLDB 2.2.8's release date. I wonder if H2 project is more active? Thanks.
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sun Feb 19, 2012 7:42 pm    Post subject: Reply with quote

liherb wrote:
...could you list some pros and cons regarding H2 vs HSQLDB for the case of single desktop user? I've checked H2's website and their latest version seems a bit newer than HSQLDB 2.2.8's release date. I wonder if H2 project is more active? Thanks.

I've compiled a pro/con list here:
Database engines

But I don't really consider H2 to be more "active." It's true that H2 enjoys monthly updates while HSQLDB 2.x is updated slightly less frequently (at least quarterly).

I could easily be persuaded to use either one of these for desktop use within the parameters I mentioned: (1) up to a few dozen active users (2) up to a few million records (3) without a significant internet presence. I really can't give a recommendation except to say that HSQLDB is much more widely tested with Base. HSQLDB 2.x also offers SQL stored procedures. But H2 has a full-text search engine built-in. So it would depend on the project. I've done a project with H2 using Base and it went well. But Villeroy and others uncovered a SubForm issue when using multiple link-fields in a single SubForm connection...however it is my understanding that the issue was soon eliminated through H2 updates and/or Base releases. And for that matter, Villeroy uncovered an anomaly with HSQLDB 2.x with Base that only surfaces in 'server mode' but not in 'file mode' (nor in H2 with Base).
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
liherb
General User
General User


Joined: 19 Aug 2011
Posts: 38

PostPosted: Sun Feb 19, 2012 9:51 pm    Post subject: Reply with quote

I've read through your comparison, and found the following two points make me feel very ambivalent about H2:

- not yet fully-threaded, or at least not when combined with MVCC support
- supports external Java routines through triggers; but no SQL stored procedures


What is the significance for "not yet fully-threaded"? Under what circumstances will this become a problem?

For the second point, if I don't program in Java but in Python or Javascript instead, would this shortcoming be a big deal?

I was also wondering how useful the "full-text search" feature in H2 could be, while one could always use like '%[search key]%' in a query?

Could you please give me some brief hint here? Many thanks.
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Tue Feb 21, 2012 10:22 am    Post subject: Reply with quote

liherb wrote:
What is the significance for "not yet fully-threaded"? Under what circumstances will this become a problem?...for the case of single desktop user?


Threading has little or no significance to a single-user database unless you run it in server-mode AND run long commands from separate instances of Base, or from separate tabs in a threaded database manager.


liherb wrote:
...if I don't program in Java but in Python or Javascript instead, would this shortcoming still be a big deal?

No big deal at all.

If you have no intention of using the database engine to 'trigger' custom commands natively, then you can use an external language processor (such as a Python installation or perhaps OS-provided script processor like Javascript) to dispatch native commands to the database engine. The only difference between this approach and the native Java approach is that database events won't 'trigger' your Python code...well at least not directly. You could probably find some Java code that 'calls' your Python code, which would then allow you to use database 'triggers' to run the Python code indirectly through some boilerplate Java code. Otherwise, you can always use front-end events such as those provided with Forms to run any supported macro code, including Python with Base.

So, off the top of my head, I don't think there are any limitations to using the language of your choice with a desktop database. With a server-based database, the only limitation would be with off-site, hosted databases that don't offer both support for both Java (Tomcat, GlassFish, Jetty, etc.) and Python (in your case) installed.

liherb wrote:
I was also wondering how useful the "full-text search" feature in H2 could be, while one could always use like '%[search key]%' in a query?

See:
http://www.h2database.com/html/tutorial.html#fulltext
http://stackoverflow.com/questions/6641737/how-to-use-full-text-search-in-h2-database
http://stackoverflow.com/questions/6611764/how-to-use-full-text-search-in-h2
http://stackoverflow.com/questions/6635948/full-text-search-in-h2-problem-with-like-clause
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'


Last edited by dacm on Tue Feb 21, 2012 10:13 pm; edited 1 time in total
Back to top
View user's profile Send private message
liherb
General User
General User


Joined: 19 Aug 2011
Posts: 38

PostPosted: Tue Feb 21, 2012 3:35 pm    Post subject: Reply with quote

Since H2 does not support stored procedure, and it seems to be more open to Java, is it correct to make such an assumption that H2 is more suitable for Java programmers who happen to have to deal with database every now and then, while HSQLDB is more suitable for ones whose main job is database related and programming language neutral?
Anyway, it's probably just my opinion. Smile
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Wed Feb 22, 2012 12:27 am    Post subject: Reply with quote

You may be confusing the merits of macros in Base with HSQLDB's stored procedure options.

    Base supports several macro languages including Python. Is that what you mean by "language neutral?"

    H2 and HSQLDB both support Java stored procedures (SQL/JRT specification), as does Oracle, DB2, and perhaps others. I assume you can call Python from Java, so you can always run Python from a tiny Java stored procedure. And it appears that you can run Python code within a Java stored procedure by using either Jython, Jepp or JPype. So evidently, combining Java with Python is a very common practice -- and probably your best bet. But this flexibility applies to both engines equally, so it doesn't help you choose between H2 and HSQLDB. Confused

    HSQLDB 2.x also supports SQL stored procedures in conformance to the SQL/PSM (Persistent Stored Modules) specification. This involves learning procedural SQL, which could be considered "language neutral" after you learn it. Wink

_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
liherb
General User
General User


Joined: 19 Aug 2011
Posts: 38

PostPosted: Wed Feb 22, 2012 12:40 am    Post subject: Reply with quote

Very informative, thanks DACM!
I only wish this forum could introduce a +1 system, so I could give DACM many +1s. Laughing
Back to top
View user's profile Send private message
datakeyword
Newbie
Newbie


Joined: 29 Feb 2012
Posts: 1

PostPosted: Mon Mar 05, 2012 9:43 pm    Post subject: try esproc Reply with quote

Hi
if you need a query migration that is independent of specifical database, even with multiple database, you may try esProc.
To turn "multiple datasources" into "single independent datasource", the esProc utility, built with Java and offered for free, can solve this problem well. It is just same as a "middle/virtual database"
To put it concretely:
1 It can retrieve data from "multiple datasources/multiple SQL" and ultimately output these data as a JDBC interface.
2 You can access to esProc via a single JDBC/JNDI, just like operating on a single database.
3 esProc will output different results for various parameter inputs. For example, in esProc, running java code "getresult(MSSQL)" will get returns from SQL server, and running getresult(ORACLE) will get returns from Oracle.
4 Of course, the value/number of parameters is under your control. That is, you can input such values as "customer id", "start time", etc.
Check URL for details about above:

http://www.esproc.com/library/product/invoking-by-report-tool-via-jdbc.html
http://www.esproc.com/library/practice/esproc-act-as-data-source-to-jasperreport.html
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page Previous  1, 2
Page 2 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group