JDBCX

Overview

This library and WebApp enhance the capabilities of the standard Jdbc service for faster and more efficient query processing.

Description

JdbcX is a practical solution that combines a library and a WebApp to expand the functionality of the standard Google Apps Script Jdbc service. It accelerates query processing and supports asynchronous execution. The library application functionality has been tested with MySQL databases, with experimental support for Microsoft SQL Server.

Library's project key

1AMYNAA96kZTenVVNqjDXtyZx7h6PcQHIbFvnt1TxqbHIuqBbLbmMQk7l

How to Install

To use the asynchronous functions, you need to deploy the library application in your environment. Skip this step if you do not need asynchronous and multi-threaded processing support.

Deployment

Create a new Apps Script project and copy the following JSON into the appsscript.json file:

Enable the display of appsscript.json in the project settings to view the file.

{
  "timeZone": "Europe/Moscow",
  "dependencies": {
    "enabledAdvancedServices": [
      {
        "userSymbol": "Drive",
        "serviceId": "drive",
        "version": "v3"
      }
    ],
    "libraries": [
      {
        "userSymbol": "ScriptSync",
        "version": "6",
        "libraryId": "1nUiajCHQReVwWPq7rNAvsIcWvPptmMUSzeytnzVHDpdoxUIvuX0e_reL",
        "developmentMode": false
      }
    ]
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

<br/>

Create a new file named deploy in it, then copy the following code:

function deployJDBCXLibrary() {

  // Library & WebApp JdbcX script as a template
  const template_script_id = "1AMYNAA96kZTenVVNqjDXtyZx7h6PcQHIbFvnt1TxqbHIuqBbLbmMQk7l";

  // Initialize the template script
  const updater = ScriptSync.assignTemplate(template_script_id);

  // Copy new files
  const filesToCopy = ScriptSync.getScriptFiles(template_script_id);

  filesToCopy.forEach(function(item) {
    updater.AddNewFile(item.file);
  });

  // Delete this file
  updater.deleteFile('deploy');

  // Apply changes
  const status = updater.commit();

  if (!status) {
    console.error('Something went wrong.');
  } else {
    console.log(
      'Please rename the project to \"JdbcX\". ' +
      'Reload the current tab, change the timezone ' +
      'in the project settings, and follow step 4: ' +
      'https://github.com/githnow/JdbcX#step4.');
  }
}

Save the project and run deployJDBCXLibrary().

After a successful run, refresh the project tab and set the project timezone in the project settings.
Deploy the project simultaneously as both a Library and a WebApp.

<br/>

You can use the deployment IDs obtained in your working project in the following steps.

<br/>

IMPORTANT: Do not modify or alter the source code of the library files unless you are sure of what you are doing.

This can affect the speed of query submission and processing.

Installation

In order to use this library, please install this library or use your own library identifier.

Install library.
- Library's project key is 1AMYNAA96kZTenVVNqjDXtyZx7h6PcQHIbFvnt1TxqbHIuqBbLbmMQk7l.
Copy the following files to your project:
- To enable autocompletion support - types file.
- To set up properties of the library script in your own deployment (library), use the - how_to_use file or the code below.

Setting Up Library Properties

  const password = "new_password";
  const jx_app_url = "webapp_url";
  JdbcX.setJXAppPassword(null, password);
  JdbcX.setJXAppURL(jx_app_url, password);

About scopes

About the install of scopes using the library, this library requires installing scopes into the project that installed the library:

https://www.googleapis.com/auth/spreadsheets
https://www.googleapis.com/auth/sqlservice
https://www.googleapis.com/auth/script.external_request
https://www.googleapis.com/auth/script.scriptapp

IMPORTANT: Above 4 scopes are installed in this library. If you want to use other scopes, please install them accordingly using Manifests to the project installed this library.

Usage Overview

The main function for initializing the connection:

JdbcX.getConnection(config)

where config is a configuration object with the following options:

/** @type {JX_Config} */
const config = {
    prefix: "jdbc:mysql://",
    server: "127.0.0.1",
    port: 3306,
    db: "default_db",
    userName: "user",
    password: "password"
};

Example Usage

// Initialize connection with the configuration object
const connection = JdbcX.getConnection(config);

// Use the connection for your database operations
// Example: Running a query
const result = connection.execute('SELECT * FROM example_table');

About Config

Methods

Library

| Method | Description | | :------------------------------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------ | | getConnection | Initializes the class and establishes a new database connection using the provided configuration settings. | | Properties | | setJXAppURL | Sets the WebApps URL for accessing this instance. | | setJXAppPassword | Sets the password for the JdbcX application. | | Tools | | generateQuery | Generates a SQL query tailored for different database types (MySQL, PostgreSQL, SQL Server). Uses nested functions to construct an SQL query. | | dateTimeFormat | Converts any date format to a specified format, including Unix timestamp, with optional time truncation or extension. |

JdbcX

Install / Use

README

JDBCX

Overview

Description

Library's project key

How to Install

Deployment

Installation

Setting Up Library Properties

About scopes

Usage Overview

Example Usage

About Config

Methods

Library

Library Class