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?

Accepted Answer

Friday, June 28 2019, 03:00 PM - #Permalink
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.
The reply is currently minimized Show
Responses (0)
  • There are no replies here yet.
Your Reply

Recent Tweets