CSQL Data Types
<< CSQL Installation - TOC - CSQL Transaction Control >>
CSQL supports a number of data types in several categories: numeric types, string (character) types, binary types, and date/time types. This chapter first gives an overview of these data types, and then provides a more detailed description of the properties of the types in each category, and a summary of the data type storage requirements.
Numeric types
Numeric type includes all integer types and floating-point data types. These consist of one-, two-, four-, and eight-byte integers, four- and eight-byte floating-point numbers, and selectable-precision decimals.
Integer Types
The types tinyint, smallint, int, integer ,long and bigint store whole numbers, i.e numbers without fractional components, of various ranges. Attempts to store values outside of the allowed range will result in an error.
TINYINT type is generally only used if disk space is at a more premium. The range of this is -128 to 127.
SMALLINT type is generally only used if disk space is at a premium. The range of smallint is -32768 to 32767.
INTEGER (or int) (or long) is the usual choice, as it offers the best balance between range, storage size, and performance. The range if integer type is -2147483648 to 2147483647.
BIGINT type should only be used if the integer range is not sufficient, because the latter is definitely faster. The range of bigint type is -9223372036854775808 to 9223372036854775807.
Floating point types
These types are used for working with fractional numbers. There are 3 types of floating point types are supported in CSQL.
FLOAT: A single-precision floating-point number takes four bytes.
REAL : Same as float.
DOUBLE: A double-precision floating-point number takes 8 bytes.
Character types
A field of characters can consist of any combination of alphanumeric characters the first letter being alphabetical. If a column is to hold a fixed number of characters, such as the bookshelf numbers of a library, use the char data type for such a column. In such a case, all fields under that column would use the same number of character. The desired number of characters must be typed in the parentheses of the char data type. The syntax used is:
char (Number)
In this case, the Number factor represents the number of characters that each field would use. The Number factor must be a positive number between 0 and 8000.
CSQL supports variable character data type, varchar, to store fields of characters of different length.
varchar (Number)
Binary Data Types
CSQL supports fixed-length binary data with a maximum length of 65536 bytes. it is only used to keep info made up of Hexadecimal characters such as (0,1,2,...,9 and A,B,C,D,E,F,a,b,c,d,e,f). Two input characters take 1 byte.
Syntax
<field name> BINARY( length )
Example :1
CSQL>CREATE TABLE BINTABLE (f1 BINARY (5));
Statement Executed
CSQL>INSERT INTO BINTABLE VALUES ('ABCDEF123');
Statement Executed: Rows Affected = 1
CSQL>SELECT * FROM BINTABLE;
---------------------------------------------------------
BINTABLE.f1
---------------------------------------------------------
ABCDEF123
Date/Time Types
Date and time types are a convenient way to store date and time related data in a uniform SQL data structure, without having to worry about the conventions involved with storage (e.g., if the user were to try to store such information in a character data type). CSQL supports three different data types for date and time. They are Date, Time, and Timestamp.
Example: 1
CSQL>CREATE TABLE T1 (f1 DATE, f2 TIME, f3 TIMESTAMP);
Statement Executed
CSQL>insert into T1 values ('2001-1-1', '01:01:01', '2001-11-30 01:01:01');
Statement Executed: Rows Affected = 1
CSQL>select * from T1;
---------------------------------------------------------
T1.f1 T1.f2 T1.f3
---------------------------------------------------------
2001/1/1 1:1:1.0 2001/11/30 1:1:1.0
Date
This describes a date using the fields YEAR, MONTH and DAY in the format YYYY-MM-DD.
Syntax: <field name> DATE Formats: 'YYYY-MM-DD' , 'yyyy/MM/DD' , 'DD-MON-YYYY'
Time
This describes a time in an unspecified day, with second’s precision s, using the fields HOUR, MINUTE and SECOND in the format HH:MM:SS [.sF] where F is the fractional part of the SECOND value.
Syntax: <field name> TIME Format: 'HH:MM:SS’
Time Stamp
This describes both a date and time, with seconds precisions, using the fields YEAR, MONTH, DAY, HOUR, MINUTE and SECOND in the format YYYY-MM-DD HH:MM:SS [.sF] where F is the fractional part of the SECOND value.
Syntax: <field name> TIMESTAMP Format: 'YYYY-MM-DD HH:MM:SS’ , 'yyyy/MM/DD HH:MM:SS’ , 'DD-MON-YYYY HH:MM:SS’
Default a field to the current date/time
To get the current date and time whenever a record is added to a table, Just define your field as a TIMESTAMP field, and combine that with the DEFAULT keyword and the NOW keyword.
The syntax for creating a TIMESTAMP field that defaults to the current date and time is also same for DATE (for current date value) and TIME (for current time value) field and it looks like this:
Syntax:
TIMESTAMP: <Field_Name> TIMESTAMP DEFAULT “NOW” TIME: <Field_Name> TIME DEFAULT “NOW” DATE: <Field_Name> DATE DEFAULT “NOW”
Example: To put this in a little more perspective, here is the complete DDL for a CSQL database table whose definition includes a timestamp field that defaults to the current date and time.
CREATE TABLE FILE_EVENTS ( id int auto_increment, file_Id int, event_Time timestamp default “now” );
The ‘event_time’ field in that table definition shows how to use NOW keyword. It is always be in double quoted.
Insert record with current date/time
Records could be inserted with current date and time value using NOW keyword for a TIMESTAMP field. If it is a DATE or TIME field then the respective value will be inserted.
Example:
create table t1(f1 int, f2 timestamp); insert into values(10,’now’);
Update records with current date/time.
Using the NOW keyword the records could be updated to current date and time for a TIMESTAMP field. If NOW is used for DATE or TIME field then the respective value will be updated i.e., current date value for DATE field and current time value for TIME field.
update t1 set f2=’now’ where f1=20;
<< CSQL Installation - TOC - CSQL Transaction Control >>