Supported SQL Server datatypes
The SQL Native Client database interface supports 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 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 SNC interface supports 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 SNC
interface retrieves all the data from the database if there is sufficient
memory.
The SNC interface supports 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 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; |