fetchAsString support for object types

[eliellis]

1. What versions are you using?

5.2.0

2. Describe the problem

I've been using the library for quite some time now (loving it btw!) and have just run into something I was curious about. I'm dealing with some date values coming back from a stored proc that are TIMESTAMP WITH TIME ZONE typed, and these values are being returned by the procedure nested within a database object type, therefore this portion of the documentation applies:

The options fetchAsBuffer and fetchAsString do not affect values in objects queried from the database.

This presents the unique challenge of not being able to interpret these dates appropriately in JS the way things are now. I of course understand I could change the procedure, or wrap it and do casting to strings in PL/SQL before consuming the values in JS. However this gets a little funny–changing the proc would impact a handful of other (ancient) consumers also dependent on the same proc, and while writing a PL/SQL wrapper would definitely work, it seems like an ugly approach if the driver itself could handle it as it does elsewhere.

I was wondering if there is a hard limitation with how these object types are handled at the ODPI level or some other serious hinderance that keeps fetchAsString from being supported in the above scenario, and if not, would like to ask kindly if it could be "added to the list." Thanks so much for your hard work on this library! 🙂

[anthony-tuininga]

The problem with using fetchAsString has to do with the fact that the value being fetched is a composite object. What format should the composite object be returned as a string? What formats should dates be converted to string? Numbers? In what order should attributes be returned? Should quotes be placed around the attribute names? If so, which style of quotes? How should values be escaped if needed? The database itself does not support converting composite objects to string. This means that the conversion would have to take place on the client.

You can perform the conversion to string yourself with a custom function that traverses the object (iterates over collections or the attributes in the object and recurses as needed). You can then decide what you have to do with the dates and what format you want them converted to string.

Note this attribute oracledb.dbObjectAsPojo which allows conversion to plain old Javsacript objects -- making them easier to traverse and manipulate.

If I am missing something, please provide a concrete example of what you were hoping to see!

[eliellis]

Hi @anthony-tuininga, sorry for the delay in getting back. Took a while to get some free time to gather a minimal example. Below is a similar situation to the one I am in, granted this is just a trivial case and the types I'm dealing with are more complex, but I feel like this will capture the gist of it. And, just to clarify right out of the gate, I'm not looking to serialize the whole composite object, only certain types of properties on the object that may be misinterpreted if not handled in the application code as a string, such as the below.

Let's suppose the below is our DDL:

CREATE OR REPLACE TYPE tstamp_test AS OBJECT (
   tstamp TIMESTAMP WITH TIME ZONE
);

CREATE OR REPLACE TYPE tstamp_table IS
   TABLE OF tstamp_test;

Now, let's suppose the below is our application code where we try to access some return values, either from OUT binds or using the implicit return feature:

const oracledb = require("oracledb");

oracledb.fetchAsString = [oracledb.DATE]; // as docs say, will not apply to objects

(async () => {
  const connection = await oracledb.getConnection({
    user: "username",
    password: "password",
    connectionString:
      "some_connection_string",
  });

const resImpl = await connection.execute(
    `
    DECLARE
      v_table tstamp_table;
      c1 SYS_REFCURSOR;
    BEGIN
      open c1 for select TSTAMP_TEST(CAST(sysdate as timestamp)) bulk collect into v_table from dual;
      dbms_sql.return_result(c1);
    END;
  `
  );

  console.log(resImpl.implicitResults[0][0][0].TSTAMP instanceof Date); // true

  const resProc = await connection.execute(
    `
    DECLARE
      PROCEDURE tstamp_proc (
          v_table OUT tstamp_table
      ) AS
      BEGIN
          SELECT tstamp_test(
                            CAST(sysdate AS TIMESTAMP WITH TIME ZONE)
                        )
                      BULK COLLECT
                      INTO v_table
                      FROM
                        dual;
      END;
  BEGIN
    tstamp_proc(:v_out);
  END;
  `,
    {
      v_out: {
        dir: oracledb.BIND_OUT,
        type: "USERNAME.TSTAMP_TABLE",
      },
    }
  )

  console.log(resProc.outBinds.v_out[0].TSTAMP instanceof Date); // true
})();

As I mentioned, reading through the documentation, I see:

The options fetchAsBuffer and fetchAsString do not affect values in objects queried from the database.

This amounts to the logs yielding true not being a surprise.

However, I'd love if there were some way to fetch this TIMESTAMP attribute as a string rather than a Date when it's being used in this way without having to jump through any proverbial hoops, such as CASTing attribute values into other types in a PL/SQL block wrapper from the application code like I mentioned in the previous comment. Just being able to use fetchAsString in this case would be exactly what I'm looking for.

Thanks so much for taking the time. Hopefully I was able to clear things up. Let me know if you need any more from me. 🙂

[cjbj]

@eliellis thanks for the example.

Fundamentally, we (as a community) have to decide how to do this conversion. The existing fetchAs functionality asks the DB to do a conversion. For objects that isn't possible. You can help by prototyping such a conversion in JavaScript. What types need to be handled (e.g. lobs)? Can we overload the fetchAs* attributes or would that break existing apps, meaning we need yet-another-setting? Anthony asked other questions earlier.

[eliellis]

@cjbj

Thanks for the link! Digging deeper into those issues, mine seems similar and the context was useful for getting the gears turning on anything new that might come of this.

I just wanted to clarify–by prototype in this case, are you meaning give a couple brief psuedo-code examples of what this could look like in JS, and the scenarios that would need to be covered, along with any immediate caveats or considerations that might need to be taken into account? If so I can certainly take a crack at it and sketch-out what I think would be a decent and somewhat consistent API.

[cjbj]

@eliellis your clarification is correct. The more detail, the better. Particularly around the actual conversion. Thanks!

[cjbj]

The node-oracledb 6.0 output type handler converter functionality can be used to map object attributes.

Using the data inserted in selectobject.js, the following converter:

function fth(metaData) {
  if (metaData.name == 'FARM' && metaData.dbType === oracledb.DB_TYPE_OBJECT) {
    return {converter: formatFarmer};
  }
}

function formatFarmer(val) {
  const v = {
    'FARMERNAME':val.FARMERNAME,
    'SEASON':val.SEASON.toLocaleString('de-DE'),
    'HARVEST': val.HARVEST,
  };
  return v;
}

and the query

    result = await connection.execute(
      `SELECT id, farm FROM no_farmtab WHERE id = 1 `,
      [],
      { outFormat: oracledb.OUT_FORMAT_OBJECT, fetchTypeHandler: fth }
    );

    for (const row of result.rows) {
      const farm = row.FARM;
      console.log('\nFarm is:', farm);
    }

prints the mapped date:

Farm is: {
  FARMERNAME: 'MacDonald',
  SEASON: '25.5.2023, 11:09:51',
  HARVEST: [CJ.DBHARVESTTYPE] [ 'Apples', 'Pears', 'Peaches' ]
}

[eliellis]

@cjbj I know I never got back around to this, but thank you! This is exactly the type of feature I was looking for. I appreciate all the hard work you and the other maintainers are putting into this library.