The MSOLEDBSQL SQL Server and SNC SQL Native Client database interfaces support the datatypes listed in the following table.
|
Binary |
Real |
|
Bit |
SmallDateTime |
|
Character (fewer than 255 characters) |
SmallInt |
|
DateTime |
SmallMoney |
|
Decimal |
Text |
|
Float |
Timestamp |
|
Identity |
TinyInt |
|
Image |
VarBinary(max) |
|
Int |
VarBinary(n) |
|
Money |
VarChar(max) |
|
Numeric |
VarChar(n) |
|
NVarChar(max) |
XML |
|
NVarChar(n) |
|
The XML datatype is a built-in datatype in SQL Server 2005 that enables you to store XML documents and fragments in a SQL Server database. The XML datatype maps to the PowerScript String datatype. You can use this datatype as a column type when you create a table, as a variable, parameter, or function return type, and with CAST and CONVERT functions.
Additional datatypes are supported for SQL Server 2008. For more information, see Support for new datatypes in SQL Server 2008.
Datatype conversion
When you retrieve or update columns, PowerBuilder converts data appropriately between the Microsoft SQL Server datatype and the PowerScript datatype. Keep in mind, however, that similarly or identically named SQL Server and PowerScript datatypes do not necessarily have the same definitions.
For information about the definitions of PowerScript datatypes, see the section called “Datatypes” in PowerScript Reference.
In SQL Server 2005, the VarChar(max), NVarChar(max), and VarBinary(max) datatypes store very large values (up to 2^31 bytes). The VarChar(max) and NVarChar(max) datatypes map to the PowerScript String datatype and the VarBinary(max) datatype maps to the PowerScript Blob datatype. You can use these datatypes to obtain metadata, define new columns, and query data from the columns. You can also use them to pipeline data.
Working with large data values
For large data values of datatypes Text, NText, Image, Varchar(max), NVarchar(max), VarBinary(max), and XML, the MSOLEDBSQL and SNC interfaces support reading data directly from the database using an embedded SQL statement.
Example 1:
select image_col into :blob_var from mytable where key_col = 1;
Example 2:
declare cur cursor for select id, image_col from mytable; open cur; fetch cur into :id_var, :blob_var;
If the result set contains a large datatype of type Text or Varchar(max), using ANSI encoding, you must set a maximum size for each large value using the PBMaxBlobSize database parameter. For other large datatypes, there is no limitation on the size of the data. The MSOLEDBSQL and SNC interfaces retrieve all the data from the database if there is sufficient memory.
The MSOLEDBSQL and SNC interfaces support inserting and updating values of large datatypes using embedded SQL INSERT and UPDATE statements. You must set the DisableBind database parameter to 0 to enable the MSOLEDBSQL or SNC interface to bind large data values. For example:
Insert into mytable (id, blob_col) values(1, :blob_var); Update mytable set blob_col = :blob_var where id = 1;
