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.
This chapter contains the following sections:
About Data Type Conversion
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.
|
|
Caution
|
The LiveWire Database Service cannot handle dates after February 5, 2037.
|
Data-Type Conversion by Database
The following table 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
|
The following table 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, datetime1
|
Date
|
|
byte
|
Blob
|
|
interval
|
Not supported
|
| 1
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.
|
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 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
|
The following table 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
|
The following table 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
|
number1
|
|
datetime, smalldatetime
|
Date
|
|
binary(n), varbinary(n), image
|
Blob
|
| 1
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.
|