No error when string is too long, working with Oracle

[mschoelzel]

Hello,

we are still using applications, written with CTD 2.1, which accesses Oracle databases via the Oracle router sqlora32.dll. Against Oracle 10, using oracle Clients 10.2.0 or 11.2.0 I experienced, that I get no error when trying to write a string value in a varchar2- database column and the length of the sring value is too long for the database column. The row simply will be not inserted without an error. In Oracle SQLDeveloper or SQLPlus I get the right error. In an example I had a string value with al length of 21 characters without any special characters like german umlauts. The database column in Oracle is defined as varchar(20 bytes). Unicode is not used. The value of NLS_CHARACTERSET- Parameter of the Oracle instance is WE8MSWIN1252. The value of NLS_NCHAR_CHARACTERSET is AL16UTF16, but this parameter shout be significant only for NVARCHAR database columns.

Is it possible, to change this behaviour anyway?

Byside- The same result I get with the Router from TD 4.2. With TD 5.2 and 6.0 it works fine, but it is nor possible to convert the applications to TD 6.0 in a short time.

Thank you for recommendations

Matthias

[Jeff Luther]

In Oracle SQLDeveloper or SQLPlus I get the right error.

That error-handling is coming from the frontend tool, sounds like. Sounds too like Oracle isn't handling this as an error (and not returning an error to your TD app). Thus, to get the "right error" your TD code would want to query the DB system table info. for the columns, like datatype, length, if nulls allowed and so on, and provide the error handling in SAL.

The router is pass-through and assumes the DB will manages/reports any errors. Apparently, what you describe isn't considered an 'error' in Oracle. In fact, that's what the TD function SqlGetModifiedRows() is used for: to find out if a row or rows were modified by last SQL DML statement. (Note, I have not tested this function with Oracle, so if you use it be sure you test that.) See TD help for details on this function.

As an example, here's the Q SQL query for Oracle to get the DB table/columns info., as I suggested above, based on having owner and table_name values:

Code: Select all

"select column_id, column_name, data_type, data_length, data_scale, nullable" || sCR ||
      "into   :nColno, :sColName, :sColType, :nLength, :nScale, :sNulls" || sCR ||
      "from   all_tab_columns" || sCR ||
      "where owner      = '" || SalStrUpperX( iv_sTBCreatorOwner ) || "' and" || sCR ||
      "   table_name = '" || SalStrUpperX( iv_sTBName ) || "'" || sCR ||
      "order by column_id"

From there, your code can see if, given a SalStrLength() value for your string, if it is > nLength.