Data Type Conversion

Table of Contents | Previous | Next | Index

Chapter 18
Data Type Conversion

This chapter describes how the JavaScript runtime engine on the server converts between the more complex data types used in relational databases and the simpler ones defined for JavaScript.

This chapter contains the following sections:

Databases have a rich set of data types. The JavaScript runtime engine on the server converts these data types to JavaScript values, primarily either strings or numbers. A JavaScript number is stored as a double-precision floating-point value. In general, the runtime engine converts character data types to strings, numeric data types to numbers, and dates to JavaScript Date objects. It converts null values to JavaScript null.

NOTE: Because JavaScript does not support fixed or packed decimal notation, some precision may be lost when reading and writing packed decimal data types. Be sure to check results before inserting values back into a database, and use appropriate mathematical functions to correct for any loss of precision.

Working with Dates and Databases

Date values retrieved from databases are converted to JavaScript Date objects. To insert a date value in a database, use a JavaScript Date object, as follows:

cursorName.dateColumn = dateObj

Here, cursorName is a cursor, dateColumn is a column corresponding to a date, and dateObj is a JavaScript Date object. You create a Date object using the new operator and the Date constructor, as follows:

dateObj = new Date(dateString)

where dateString is a string representing a date. If dateString is the empty string, it creates a Date object for the current date. For example:

custs.orderDate = new Date("Jan 27, 1997")

NOTE: DB2 databases have time and timestamp data types. These data types both convert to the Date type in JavaScript.

WARNING: The LiveWire Database Service cannot handle dates after February 5, 2037.

For more information on working with dates in JavaScript, see Date Object.

Data-Type Conversion by Database

The following table shows the conversions made by the JavaScript runtime engine for DB2 databases.

Table 18.1 DB2 data-type conversions

DB2 Data Type JavaScript Data Type
char(n), varchar(n), long varchar, clob(n)
string

integer, smallint
integer

decimal, double
double

date, time, timestamp
Date

blob
Blob

DB2 Data Type	JavaScript Data Type
`char(n)`, `varchar(n)`, `long varchar, clob(n)`	`string`
`integer, smallint`	`integer`
`decimal, double`	`double`
`date, time, timestamp`	`Date`
`blob`	`Blob`

The following table shows the conversions made by the JavaScript runtime engine for Informix databases.

Table 18.2 Informix data-type conversions

Informix Data Type JavaScript Data Type
char, nchar, text, varchar, nvarchar
string

decimal(p,s), double precision, float, integer, money(p,s), serial, smallfloat, smallint
number

date, datetime¹
Date

byte
Blob

interval
Not supported

¹ The Informix datetime data type has variable precision defined by the user. Server-side JavaScript displays datetime data with the format of YEAR to SECOND. If a datetime variable has been defined with another precision, such as MONTH to DAY, it may display incorrectly. In this situation, the data is not corrupted by the incorrect display.

Informix Data Type	JavaScript Data Type
`char`, `nchar`, `text`, `varchar`, `nvarchar`	`string`
`decimal(p,s)`, `double precision`, `float`, `integer`, `money(p,s)`, `serial`, `smallfloat`, `smallint`	`number`
`date`, `datetime¹`	`Date`
`byte`	`Blob`
`interval`	Not supported

ODBC translates a vendor's data types to ODBC data types. For example, the Microsoft SQL Server varchar data type is converted to the ODBC SQL_VARCHAR data type. For more information, see the ODBC SDK documentation. The following table shows the conversions made by the JavaScript runtime engine for ODBC databases.

Table 18.3 ODBC data-type conversions

ODBC Data Type JavaScript Data Type
SQL_LONGVARCHAR, SQL_VARCHAR, SQL_CHAR
string

SQL_SMALLINT, SQL_INTEGER, SQL_DOUBLE, SQL_FLOAT, SQL_REAL, SQL_BIGINT, SQL_NUMERIC, SQL_DECIMAL
number

SQL_DATE, SQL_TIME, SQL_TIMESTAMP
Date

SQL_BINARY, SQL_VARBINARY, SQL_LONGBINARY
Blob

ODBC Data Type	JavaScript Data Type
`SQL_LONGVARCHAR`, `SQL_VARCHAR`, `SQL_CHAR`	`string`
`SQL_SMALLINT`, `SQL_INTEGER`, `SQL_DOUBLE`, `SQL_FLOAT`, `SQL_REAL`, `SQL_BIGINT`, `SQL_NUMERIC`, `SQL_DECIMAL`	`number`
`SQL_DATE`, `SQL_TIME`, `SQL_TIMESTAMP`	`Date`
`SQL_BINARY`, `SQL_VARBINARY`, `SQL_LONGBINARY`	`Blob`

The following table shows the conversions made by the JavaScript runtime engine for Oracle databases.

Table 18.4 Oracle data-type conversions

Oracle Data Type JavaScript Data Type
long, char(n), varchar2(n), rowid
string

number(p,s), number(p,0), float(p)
number

date
Date

raw(n), long raw
Blob

Oracle Data Type	JavaScript Data Type
`long`, `char(n),` `varchar2(n)`, `rowid`	`string`
`number(p,s)`, `number(p,0)`, `float(p)`	`number`
`date`	`Date`
`raw(n)`, `long raw`	`Blob`

The following table shows the conversions made by the JavaScript runtime engine for Sybase databases.

Table 18.5 Sybase data-type conversions

Sybase Data Type JavaScript Data Type
char(n), varchar(n), nchar(n), nvarchar(n), text
string

bit, tinyint, smallint, int, float(p), double precision, real, decimal(p,s), numeric(p,s), money, smallmoney
number¹

datetime, smalldatetime
Date

binary(n), varbinary(n), image
Blob

¹ The Sybase client restricts numeric data types to 33 digits. If you insert a JavaScript number with more digits into a Sybase database, you'll get an error.

Sybase Data Type	JavaScript Data Type
`char(n)`, `varchar(n)`, `nchar(n)`, `nvarchar(n)`, `text`	`string`
`bit`, `tinyint`, `smallint`, `int`, `float(p)`, `double precision`, `real`, `decimal(p,s)`, `numeric(p,s)`, `money`, `smallmoney`	`number¹`
`datetime`, `smalldatetime`	`Date`
`binary(n)`, `varbinary(n)`, `image`	`Blob`

Table of Contents | Previous | Next | Index

Last Updated: 11/12/98 15:29:45

Chapter 18 Data Type Conversion

Working with Dates and Databases

Data-Type Conversion by Database

Chapter 18
Data Type Conversion