Jaguar Calc add-in

(0 votes)

A library of Calc functions.
Functions
This version adds only the SQLREQUEST function to Calc.
SQLREQUEST allows to query a JDBC data source. It is similar to the SQL.REQUEST function provided with MS-Excel.
Being an array function, SQLREQUEST, results in an array containing the data returned by the data source. The online help of Calc includes the "Array Functions" section explaining how to use them.
Examples.
The following is an example of a typical call using the MySQL J/Connector driver:
=SQLREQUEST("com.mysql.jdbc.Driver";"jdbc:mysql://server_ip_address/?user=name&password=abc";"SELECT * FROM table")
Data type conversion issues.
Considering that Calc Add-In functions, by design, only support three data types as return value, the following considerations have been taken:

SQL-Bit and SQL-Boolean are converted to integers (long). This way it is possible to consider zeros as false values, and true otherwise. In other words, the logic is up to you.
SQL-Date values are converted to floating point numbers compatible with the way Calc handles date/time values.
SQL-Decimal values are converted to strings. The advantage of this approach is that the loss of precision that could result by converting the value to a double is avoided. However, the string must be converted to a number before being able to perform numerical operations. This might be accomplished by using, for example, =VALUE(cellReference)
JDBC advanced data types have not been tested.

Consult the com.sun.star.sheet.AddIn service for further details.
Requirements.

OpenOffice 2.3.0 and later (not tested under earlier versions)
Java Runtime Environment 1.6.0 or later enabled (not tested under earlier versions)
JDBC driver correctly installed

This extension has been tested using the following:

OpenOffice version 2.3.0 (English US and Spanish)
Windows XP SP2
MySQL J/Connector version 5.0.4 (com.mysql.jdbc.Driver class)

Future releases
Version 1.1 might add a fourth parameter to allow specifying the desired return type of each column.
This would leave the user the choice of specifying:

If an SQL-Decimal column should be returned as a string (the current behaviour) or to a double
If an SQL-Date column should be returned as a number (the current behaviour) or as a string string.

I appreciate your feedback on this issue to decide the future direction of SQLREQUEST.
This add-in was first released on 02-dec-2007