Supported SQL Server datatypes
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:
|
1 |
select image_col into :blob_var from mytable where key_col = 1; |
Example 2:
|
1 2 3 |
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:
|
1 2 |
Insert into mytable (id, blob_col) values(1, :blob_var); Update mytable set blob_col = :blob_var where id = 1; |