Supported SQL Server datatypes
The SQL Native Client database interface supports the datatypes
listed in Table 9-1.
|
Binary |
Real |
|
Bit |
SmallDateTime |
|
Character (fewer |
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 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 |
declare cur cursor for select id, image_col from mytable;<br>open cur;<br>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 |
Insert into mytable (id, blob_col) values(1, :blob_var); |
|
1 |
Update mytable set blob_col = :blob_var where id = 1; |