ISO 8601 Date Types

[bchr02]

Dates are serialized as strings in ISO 8601 format when being parsed from JSON. E.g when using express.json(), or when using JSON.parse(). To INSERT into DATE columns, one could use a reviver function as mentioned here or keep the IN binds as oracledb.STRING types and wrap the VALUES in TO_UTC_TIMESTAMP_TZ(:1) but it would be nice if within bindDefs there could be a special type for dates stored as strings in ISO 8601 format that are meant for DATETIME or DATE columns. I currently do not see anything documented that allows for this behavior (explicitly or implicitly), but I believe it would be extremely beneficial. Also, the examples/date.js would benefit by providing an example using the TO_UTC_TIMESTAMP_TZ function with the IN binds as oracledb.STRING.

[cjbj]

@bchr02 possibly @anthony-tuininga's favourite 'input type handler' concept?

I'm happy to take PRs on the examples and doc (or code!)

[bchr02]

@cjbj Interesting concept indeed. Is this possible in Node.js too? Is it documented anywhere?

I was thinking if a bind in of the type date is specified and the provided value is a string that this should trigger an implicit conversion, and or explicitly having a stringdate type built into node-oracledb.

If I have some spare time I will try to send over a PR.

[cjbj]

@bchr02 there is no equivalent to the Python input type handler in node-oracledb.

[sla100]

I tested such minor changes and it works: src/njsVariable.c

bool njsVariable_initForQuery(njsVariable *vars, uint32_t numVars,
        dpiStmt *handle, njsBaton *baton)
{
              case DPI_ORACLE_TYPE_TIMESTAMP_LTZ:
                if (vars[i].varTypeNum != DPI_ORACLE_TYPE_VARCHAR) {
                    vars[i].varTypeNum = DPI_ORACLE_TYPE_TIMESTAMP_LTZ;
                    // vars[i].nativeTypeNum = DPI_NATIVE_TYPE_DOUBLE;
                    vars[i].nativeTypeNum = DPI_NATIVE_TYPE_TIMESTAMP;
                }
                break;
}

bool njsVariable_getScalarValue(njsVariable *var, njsConnection *conn,
        njsVariableBuffer *buffer, uint32_t pos, njsBaton *baton, napi_env env,
        napi_value *value)
{
        case DPI_NATIVE_TYPE_TIMESTAMP: // (new case)
            char *dateString;

            if (var->dbTypeNum == DPI_ORACLE_TYPE_DATE)
            {
                dateString = malloc(19);
                sprintf(dateString, "%04hu-%02hu-%02huT%02hu:%02hu:%02hu",
                        data->value.asTimestamp.year,
                        data->value.asTimestamp.month,
                        data->value.asTimestamp.day,
                        data->value.asTimestamp.hour,
                        data->value.asTimestamp.minute,
                        data->value.asTimestamp.second);
            }
            else if (var->dbTypeNum == DPI_ORACLE_TYPE_TIMESTAMP)
            {
                dateString = malloc(29);
                sprintf(dateString, "%04hu-%02hu-%02huT%02hu:%02hu:%02hu.%u",
                                                            data->value.asTimestamp.year,
                                                            data->value.asTimestamp.month,
                                                            data->value.asTimestamp.day,
                                                            data->value.asTimestamp.hour,
                                                            data->value.asTimestamp.minute,
                                                            data->value.asTimestamp.second,
                                                            data->value.asTimestamp.fsecond);
            }else{
                dateString = malloc(100);
                sprintf(dateString, "%04hu-%02hu-%02huT%02hu:%02hu:%02hu.%u-%02hu:%02hu",
                        data->value.asTimestamp.year,
                        data->value.asTimestamp.month,
                        data->value.asTimestamp.day,
                        data->value.asTimestamp.hour,
                        data->value.asTimestamp.minute,
                        data->value.asTimestamp.second,
                        data->value.asTimestamp.fsecond,
                        data->value.asTimestamp.tzHourOffset,
                        data->value.asTimestamp.tzMinuteOffset);
            }
            NJS_CHECK_NAPI(env, napi_create_string_utf8(env, dateString, strlen(dateString), value));
            free(dateString);
            break;
}