New Jaguar Calc add-in

Description

New JaguarSQL

New JaguarSQL is a JaguarSQL 1.0.2 update.
Within the extension file (.oxt) there is the original source code of JaguarSQL 1.0.2  which is the base of this update.

This extension adds  the SQLREQUEST function to Calc.    
SQLREQUEST allows you to query a database using a JDBC driver or an SDBC driver.
It is similar to the SQL.REQUEST function provided with old MS-Excel.
It is an array function. The result is an array containing the data returned from the data source. Calc’s online help includes the "Array Functions" section that explains how to use them.

The SQLREQUEST sintax is:

SQLREQUEST (driver, url_or_properties, sqlCommand, nRowsRequired, showColumnName, number2double, date2string, yearOffset)

To report bugs or give advice, lo.extensions@tiscali.it

Examples:

JDBC driver:
=SQLREQUEST("com.mysql.jdbc.Driver";"jdbc:mysql://server_ip_address/?user=name&password=abc";"SELECT * FROM table")

SDBC driver:
=SQLREQUEST("sdbc:odbc:mydatabase";"user=pippo;password=mypssword";"SELECT * FROM table")

Parameters

driver

It’s a string. If this string matches a full qualified java class name (for example: com.pippo.minni), SQLREQUEST attempts to use a JDBC driver with that name. Otherwise, it assumes that the driver string is the URL of a specific SDBC driver and tries to use it (see http://wiki.openoffice.org/wiki/Documentation/DevGuide/Database/Driver_Specifics#Driver_Specifics).

Example:

sun.jdbc.odbc.JdbcOdbcDriver : the bridge ODBC-JDBC until java 1.7
com.mysql.jdbc.Driver : JDBC driver for MySQL
jdbc:mysql://192.168.10.1:3306/MYDATABASE : SDBC specific driver. Driver: JDBC
sdbc:odbc:MyODBCSourceName: SDBC specific driver. Driver: ODBC 3.5

url_or_properties

It is a string or a string array. If it is a string array, SQLREQUEST concatenates each element of the array into a string.

JDBC driver
For JDBC driver, this parameter is a string with the connection URL. See the help of JDBC driver.

Example for sun.jdbc.odbc.JdbcOdbcDriver (until java 1.7):  jdbc:odbc:prova

Example for com.mysql.jdbc.Driver: jdbc:mysql://192.168.10.1:3306/MYDATABASE?user=pippo&password=paperone

SDBC driver
For SDBC driver, this parameter is a string with connection properties separates by a semicolumn.
For the allowed properties, see com::sun::star::sdbc::ConnectionProperties.
TypeInfoSettings and SystemProperties properties are not supported (com::sun::star::sdbc:: JDBCConnectionProperties).

Example for the JDBC specific driver: password=mypassword;user=pippo;JavaDriverClass= com.mysql.jdbc.Driver

sqlCommand

It is a string with the SQL command.
If it is a string array, SQLREQUEST concatenates each element of the array into a string.

nRowsRequired (Optional)

The result of SQLREQUEST is an array containing data returned from the data source.
This parameter sets the size of the array and then the maximum number of records returned.
If nRowsRequired <= 0, there is no limit.
The default value is zero.

showColumnName (Optional)

If true, the first row of the array contains the name of the columns.
The default value is false.

number2double (Optional)

If true, SQLREQUEST converts sql BIGINT, DECIMAL and NUMERIC types to DOUBLE.
The default value is false.

date2string (Optional)

If true, SQLREQUEST converts sql DATE type to STRING.
The default value is false.

yearOffset (Optional)

The offset of the year for sql DATE type.
This parameter solves my problem: the date field of ISAM file (Microfocus) of my cobol application is two centuries ahead (31.12.2016 in my ISAM file is 31.12.2216).
This parameter is used only if date2string is false.

Data type conversion issues.

Considering that Calc Add-In functions support only three types of data as return value, the following considerations have been taken:

Sql BIT and BOOLEAN types are converted to Java INT type (Uno LONG type): 0(false) - 1(true).
Sql DATE types are converted to floating point numbers compatible with Calc date/time values, if date2string is false (default value), otherwise to STRING.
Sql DECIMAL, NUMERIC and BIGINT types are converted to STRING, if number2double parameter is false (default value), otherwise to DOUBLE.
Sql TINYINT, INTEGER and SMALLINT types are converted to Java INT type (Uno LONG type).
For other SQL types, SQLREQUEST returns a string.

Contributors: FabioLeschanz