Re: Open Office Base

John Meyer wrote:
> On 12/1/2009 5:36 AM, Gerard G wrote:
>> WOuld you send me ( or hint ) documenation concerning SQL standard in 
>> Open Office with functions which might be used in SQL statement.
>>   Which version of SQL is implemented ?
>>    
>
> http://hsqldb.org/web/hsqlDocsFrame.html
>

Well, that is one answer - HSQLdb is available, by default, on any 
OpenOffice.org desktop which includes the Base modules.

Thing is however, that Base can work with data from a variety of 
databases, this includes a group of file types for which Base implements 
a native, and primitive, set of data manipulation functions.

For instance:

Suppose you export contact information from some email application, 
Google's gmail or Microsoft's LiveMail. Let's say the file you create is 
a comma separated text file named mycontacts.csv.

Using Base you have two, at least, easy ways of attaching to that file.

In the first you would create a new Base file, using the wizard to 
connect to an existing database - in this case a CSV file and you would 
setup the Base database with file path information, and file format options.

In the second you would create a new Base file, using the wizard to 
create an embedded HSQLdb database - in this case you would then need to 
create a TEXT table definition in the new Base database and using HSQLdb 
specific commands ATTACH this TEXT table to mycontacts.csv.

In the first Base file, from above, you will find the number of 
available functions in the query designer quite limited.

In the second the full set, as referenced in John's remark, will be 
available. ( There is an added bonus, in the second you can update the 
csv file, not so in the first. )

Flat files and Base native data functions there is the issue of the wide 
support for Relational Database Managers in general. Base does not limit 
the query parser to understanding only the HSQLdb dialect - [ I believe 
that is unequivocally true...lol...big word of the day... If not perhaps 
Ocke or Frank will correct here? ]

That said, there is the SQL standard - *plural* - the HSQLdb set of 
functions will be found on pretty much all of the major database 
engines, along with some slight variance in how some of the particular 
procedures behave from one to another.

For instance:

A Base file using an embedded HSQLdb database and one utilizing the 
MySQL Native Connector will allow differences in procedures, even humble 
as the simple concat function.

So - my rule of thumb. If you are connecting to a database with JDBC 
then the HSQLdb set of functions will most likely be available, ODBC a 
bit less ( I'm thinking here of ODBC <->SQLite3 ??? ) but still a safe 
bet. Get into any of the file based connectors, including Dbf files, and 
don't expect much.

hmm - not sure that was any better advice then John's simple one line. ;>)

Drew