TIP: SBM: What are the API options available with SBM, and how much do they cost? See KB doc… https://t.co/2SI6ASmWAE
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?
- 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.