Data Type Conversion

Chapter 11
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.

Sections in this chapter:

Working with Dates and Databases

Data-Type Conversion by Database

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 the JavaScript Guide.

Data-Type Conversion by Database

Table 11.1 shows the conversions made by the JavaScript runtime engine for DB2 databases.

Table 11.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`

Table 11.2 shows the conversions made by the JavaScript runtime engine for Informix databases.

Table 11.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. Table 11.3 shows the conversions made by the JavaScript runtime engine for ODBC databases.

Table 11.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`

Table 11.4 shows the conversions made by the JavaScript runtime engine for Oracle databases.

Table 11.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`

Table 11.5 shows the conversions made by the JavaScript runtime engine for Sybase databases.

Table 11.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`

[Contents] [Previous] [Next] [Index]

Last Updated: 10/30/97 12:19:41

Chapter 11 Data Type Conversion

Working with Dates and Databases

Data-Type Conversion by Database

Chapter 11
Data Type Conversion