I know the Escape Logic works; that is not the issue. When I use the Escape Logic in increases the string length when I update the table. I'm trying to figure out if there is a way so that Escape logic doesn't get counted as part of the string length

Hello Robert - The use of TO_DATE and TO_TIMESTAMP is to not have to know what the specific date format is on the Source Database. If TO_DATE & TO_TIMESTAMP cannot be used, is there another method I could use that does the same thing? I have no issues getting the date; just how to format it and send it so it is not a specific date format that could change. 

I could, but I'd rather have the date format work for any environment and not hardcoded. I understand TO_DATE & TO_TIMESTAMP is an SQL Function but thought there might be something else to perform this functionality here.

Why SCALE=4? Wouldn't that fix 4 decimal placement? I'm am looking for a SCALE that will allow any number with any decimal placement.

I have a link generator written that is reading Source (Oracle) tables and that uses the %Library.ResultSet class. for each it gets column name, type, maxsize, precision, lais, readonly, required & data type name and builds Cache Linked Table.

Are you referring to this logic where I need to apply scale? I do not see any SCALE references in logic.

 if (jdbc) {
  s rs= ##class(%Library.ResultSet).%New("%GTWCatalog:SQLFieldsJ")
 } else {
  s rs= ##class(%Library.ResultSet).%New("%GTWCatalog:SQLFields")
 }
 s sc = rs.Execute(gateway, table, schema)
 if ($$$ISERR(sc)) {
  d $system.OBJ.DisplayError(sc)
  quit sc
 }
 s tname = schema _ "." _ table
 s cols = ""
 while rs.Next() {
  s externalcolumnname=rs."COLUMN_NAME",
    columnname = externalcolumnname,
    columntype=rs.DATATYPE,
    columnmaxsize=rs."COLUMN_SIZE",
    columnprecision=rs."DECIMAL_DIGITS",
    columnalias="" /* defaults to externalcolumnname */,
    readonly=0 /* hardcoded */,
    required='rs.NULLABLE,
    notused="",
   datatypename=rs."DATATYPE_NAME"
   if prikey="",(externalcolumnname["_cd")!(externalcolumnname["_id") SET prikey=externalcolumnname
  s col=$LTS($LB( externalcolumnname,
     columnname,
    columntype,
    columnmaxsize,
    columnprecision,
    columnalias,
    readonly,
    'required,
    notused,
    datatypename ),"^")
  s cols = cols_$LB(col)
 }
 s cols=$LTS(cols,"@")
 d rs.Close()
 

Hi Robert,

When I review the existing DDL Mapping for NUMBER, the datatype states: %Library.Numeric(SCALE=0)

This does not make any sense my generated SQL Table code(.cls & .int) is validating with EXTERNALSQLTYPE=2 and $isvalidnum(%val,2,,)

I thought SCALE=0 allowed any decimal value but it appears somewhere else there is logic that is defaulting this SCALE to 2.

Thanks Robert - I'm not using IRIS but Cache with JDBC. Also, with the %Library.Numeric parameter Scale, how and where do I implement it? Can a system wide setting be set for this control?

Those are exactly what I needed and worked! Thanks!

Just using Cache, not IRIS so no Interoperability.

The Object Script I'm using for ODBC is both in SQL Queries and Cache Object Classes. I think using JDBC will be faster having multiple JDBC SQL Gateways. Just trying to find an example of someone having a external Database with JDBC using Cache Object Script. 

Hello Kevin Chan,

I know how to setup jdbc in SQL Gateway; that is not the Question. The Question is can I code in Cache Object Script to an external Oracle database using jdbc SQL Gateway in place? And if I can, do you or anyone else have examples of doing this. I have Cache Object Script working with an External Oracle database using ODBC but wanted to see how to do this (or replace this) with jdbc?