Highlighted
PM Thompson Outstanding Contributor.
Outstanding Contributor.
449 views

SBM (11.5) ModScript ReadDynaSQL field type constants?

Jump to solution
For the ReadDynaSQL function, what are the (2nd param) DBTypeConstants constants for the following field types:

    VarChar(255) (schema says DATA_TYPE=-9; nvarchar; LENGTH=1020)
    VarChar(max) (schema says DATA_TYPE=-10; ntext; LENGTH=2147483646)

The "schema" info came from SQL Server, "exec sp_columns ts_file_objs".

For daily double bonus points: Where do the predefined DBTypeConstants values come from and how do they relate to the values in the SQL Server schema DATA_TYPE?
Tags (2)
0 Likes
1 Solution

Accepted Solutions
dinghram Absent Member.
Absent Member.

Re: SBM (11.5) ModScript ReadDynaSQL field type constants?

Jump to solution
For SQL Server:

  • For a nvarchar(512) or smaller column, use DBTypeConstants.VARCHAR.
  • For text columns wider than nvarchar(512), use DBTypeConstants.LONGVARCHAR. I *think* that LONGVARCHAR will work with nvarchar(1000). I know it will work with nvarchar(max).
  • For varchar (non-n text columns), ModScript will read them using "n" rules, meaning MSSQL will convert the data to Unicode using the collation of the text column for conversion. As such, DBTypeConstants.VARCHAR can be used (but ensure that the varchar column data can fit in a UTF-16 buffer of 512 or less after conversion). For wider columns, use DBTypeConstants.LONGVARCHAR.
  • As an aside, ModScript will not read text columns larger than the SBM maximum for Memo fields, which (in general) is 65535 Unicode code points in length.
  • For integer columns, DBTypeConstants.BIGINT will work for any column, but INTEGER, SMALLINT, and TINYINT can be used for corresponding (smaller) columns in the database.
  • For float columns, DBTypeConstants.DOUBLE will work for columns up to a 8 byte floating point value, but FLOAT can be used for corresponding (lower precision) columns in the database.
  • For database datetime fields, use DBTypeConstants.DATETIME.


For Oracle and Postgres:
The rules are similar to MSSQL, but SBM data is stored in varchar (UTF-8) rather than nvarchar. As such, DBTypeConstants.VARCHAR will support up to a UTF-16 buffer that is 512 in size. The safest rule is to only read up to VARCHAR(128) with a DBTypeConstants.VARCHAR.

View solution in original post

0 Likes
1 Reply
dinghram Absent Member.
Absent Member.

Re: SBM (11.5) ModScript ReadDynaSQL field type constants?

Jump to solution
For SQL Server:

  • For a nvarchar(512) or smaller column, use DBTypeConstants.VARCHAR.
  • For text columns wider than nvarchar(512), use DBTypeConstants.LONGVARCHAR. I *think* that LONGVARCHAR will work with nvarchar(1000). I know it will work with nvarchar(max).
  • For varchar (non-n text columns), ModScript will read them using "n" rules, meaning MSSQL will convert the data to Unicode using the collation of the text column for conversion. As such, DBTypeConstants.VARCHAR can be used (but ensure that the varchar column data can fit in a UTF-16 buffer of 512 or less after conversion). For wider columns, use DBTypeConstants.LONGVARCHAR.
  • As an aside, ModScript will not read text columns larger than the SBM maximum for Memo fields, which (in general) is 65535 Unicode code points in length.
  • For integer columns, DBTypeConstants.BIGINT will work for any column, but INTEGER, SMALLINT, and TINYINT can be used for corresponding (smaller) columns in the database.
  • For float columns, DBTypeConstants.DOUBLE will work for columns up to a 8 byte floating point value, but FLOAT can be used for corresponding (lower precision) columns in the database.
  • For database datetime fields, use DBTypeConstants.DATETIME.


For Oracle and Postgres:
The rules are similar to MSSQL, but SBM data is stored in varchar (UTF-8) rather than nvarchar. As such, DBTypeConstants.VARCHAR will support up to a UTF-16 buffer that is 512 in size. The safest rule is to only read up to VARCHAR(128) with a DBTypeConstants.VARCHAR.

View solution in original post

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.