Can not create arrays of TIME WITH TIME ZONE, TIMESTAMP WITH TIME ZONE data types in hssql

[lahirusamith]

Description:

Even though we can create tables having columns of array types TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE in hssql. we can not insert data to those tables using ballerina java.jdbc database connector.

eg:

bellow sql script can be run in hssql directly.

CREATE TABLE IF NOT EXISTS TimeWithTimezoneArrayTypes (
  row_id        INTEGER NOT NULL,
  time_tz_array  TIME WITH TIME ZONE ARRAY,
  timestamp_tz_array TIMESTAMP WITH TIME ZONE ARRAY,
  PRIMARY KEY (row_id)
);

INSERT INTO TimeWithTimezoneArrayTypes (row_id, time_tz_array, timestamp_tz_array)
  VALUES (1, ARRAY[null, '16:33:55+6:30', '16:33:55+4:30'], ARRAY[null, '2017-01-25 16:33:55-8:00', '2017-01-25 16:33:55-5:00']);

but this ballerina code snippet gives an error.

time:Civil timestampWithTzRecord = {utcOffset: {hours: -8, minutes: 0}, timeAbbrev: "-08:00", year:2017,
                                            month:1, day:25, hour: 16, minute: 33, second:55};
DateTimeArrayValue paraDatetimeWithTZ = new ([timestampWithTzRecord]);
int rowId = 2;
ParameterizedQuery sqlQuery =
        `INSERT INTO TimeWithTimezoneArrayTypes (row_id, timestamp_tz_array) VALUES(${rowId},
                ${paraDatetimeWithTZ})`;
jdbc:Client dbClient = check new (url = executeParamsDb, user = user, password = password);
ExecutionResult result = check dbClient->execute(sqlQuery);

Error

error DatabaseError ("Error while executing SQL query: INSERT INTO TimeWithTimezoneArrayTypes (row_id, timestamp_tz_array) VALUES( ? ,
                 ? ). Invalid argument in JDBC call: TIMESTAMP_WITH_TIMEZONE.",errorCode=-423,sqlState="S1000")

Steps to reproduce:

Affected Versions:

OS, DB, other environment details and versions: ballerina version: swan lake beta 2 java.jdbc version : 0.6.0-beta.2

Related Issues (optional):

Suggested Labels (optional): module/sql module/java.jdbc

Suggested Assignees (optional): Lahiru Malepathirana Danesh Kuruppu Niveathika Rajendran

[niveathika]

@lahirusamith Wasnt this due to the HSSQL driver not supporting the said data types?

[lahirusamith]

@niveathinka, Yes this is due to hsqldb driver.

[niveathika]

Closing it as this is not a bug in ballerina connector with the above comment.