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?

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. 

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?

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.

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()
 

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 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.

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 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