New Jaguar Calc add-in

26 10

This extension adds the SQLREQUEST function to Calc. SQLREQUEST allows you to query a database using a JDBC driver or an SDBC driver.

Project 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)

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.

 

Category/Categories

Calc Extension

Current Release

New Jaguar Calc add-in - 2.0.0

Released Aug 30, 2017 — tested with:

LibreOffice 5.3, LibreOffice 5.4,

Tested on Windows 10 and Window 7.
More about this release…

JaguarSQL-2.0.0.oxt
All platforms
File size: 37 kb

All Releases

Version

Released

Platform(s)

Compatibility

Description

License(s)

Status

2.0.0

Aug 30, 2017
  • All platforms
  • LibreOffice 5.3
  • LibreOffice 5.4
Tested on Windows 10 and Window 7.
  • GNU-GPL-v2 (GNU General Public License Version 2)
final

Legal Disclaimer and Limitations for Downloads

The Document Foundation has not reviewed, and cannot review, all of the material, including computer software, available on or by means of The Document Foundation's
websites, and cannot therefore be responsible for that material's content, use or effects. By operating its websites, The Document Foundation does not represent or imply that it endorses the material there available, or that it believes such material to be accurate, useful or nonharmful. You are responsible for taking precautions as necessary to protect yourself and your computer systems from viruses, worms, Trojan horses and other harmful or destructive content. The Document Foundation's websites may contain content that is offensive, indecent or otherwise objectionable, as well as content containing technical inaccuracies, typographical mistakes and other errors. The Document Foundation's websites may also contain material that violates the privacy or publicity rights, or infringes the proprietary rights, of third parties, or the downloading, copying or use of which is subject to additional terms and conditions, stated or unstated. The Document Foundation disclaims any responsibility for any harm resulting from the use by The Document Foundation's visitors of The Document Foundation's websites, or from any downloading by those visitors of content available on or by means of The Document Foundation's websites.

Changes

Content contained on The Document Foundation's websites, including these Legal Disclaimers and Limitations, may be changed at the sole discretion of The Document Foundation and without notice. You are bound by any such updates or changes, and so should periodically review these Legal Disclaimers and Limitations.

If you believe any file is present on the site contrary to any TDF rule or applicable law, please supply full details to info@documentfoundation.org for impartial consideration.