Data Types in REAL Server 2009

As an old hand with relational databases but new to SQL, the topic of data types has thrown me for a loop.

Supposedly we have data types in standard SQL. Unfortunately, they are not defined precisely as in how many bits for each type of numeric format. And there are some commonly used data types such as "Currency" (a fixed number of digits on each side of the decimal point) that are not in the official SQL standards.

REAL Server uses SQLite as its engine. Then we have SQLite which has only 4 actual data types:

• Integer
• Real
• Text
• Blob

SQLite accepts many of the usual data type names, but they all get mapped to one of those 4 actual types. By accepting these various other type names, you can write code and SQL scripts that are compatible with other databases. Beware that while SQLite allows you to use the names of stricter data types, SQLite does not enforce that strictness. For example, you can specify VARCHAR(10) but SQLite will let you insert a string longer than 10 characters.

In addition, SQLite is unusually liberal in accepting various kinds of data that strictly speaking violates the declared data type. For example, you can stuff a string into an Integer column. This feature is called "type affinity". So, if you want compatibility with other databases, be sure to tighten up your programming code to not store the wrong type of data to the database. While SQLite may accept it, other databases will refuse with an error.

SQLite has various limits you should study.

If you are using REAL Studio 2010 and REAL Server 2009, I have built a table listing your data type choices.

Data Types in REAL Server 2009

Category	REAL Server Type	Stored in SQLite as	REALbasic constant value	Description
Textual	TEXT	Text	5	UTF-8 character encoding. SQLite accepts other Unicode-compliant encodings, but they are moot as the REALbasic language uses only UTF-8. While the various documentation is vague, it seems the size limit on both REALbasic language and SQLite engine is about 2 billion octets. That may translate to fewer characters given that UTF-8 uses multiple octets per character for letters beyond the US-ASCII range (first 127 of Unicode).
	VARCHAR(n)	Text	5	UTF-8 character encoding. SQLite accepts other Unicode-compliant encodings, but they are moot as the REALbasic language uses only UTF-8.
Temporal	DATE	Text	8	YYYY-MM-DD
	TIME	Text	9	HH:MM:SS.nnnnnn No time zone that I know of.
	TIMESTAMP	Text	10	YYYY-MM-DD HH:MM:SS.nnnnnn No time zone that I know of.
Whole Numbers	INTEGER	Integer	3	Uses up to 64-bits, as needed.
	SMALLINT	Integer	2	REALbasic language variable type is 32-bit. SQLite stores as Integer, up to 64-bits as needed. 32-bits means numbers in the range of about ± 2 billion. 64-bits means a humongous range of about ± 9 quintillion.
Fractional Numbers	CURRENCY	Real	11	64-bits. nnn,nnn,nnn,nnn,nnn.nnnn (or nnn.nnn.nnn.nnn.nnn,nnnn) 15 digits for the whole portion (left of decimal point). 4 digits for the fraction (right of decimal point). Those 15 digits means numbers in the range of ± hundreds of trillions, up to about a quadrillion. Those 4 digits means as fine as a ten-thousandth of a fraction, or in the terms of US dollars, as fine as a hundredth of a penny. 'Currency' is a misnomer; while frequently used for money you can use it for any fractional number. This type is not actually savvy about monetary issues. The name 'Currency' is not standard SQL. While many databases offer a 'Currency' or 'Money', to be strictly standard in other databases you would define: DECIMAL(19,4)
	DOUBLE	Real	7	64-bit floating-point numbers. With floating-point numbers you trade off accuracy for a greater range of numbers. You get about 16 digits from which you can make either very large numbers or very small fractional numbers, but not both at same time. If you know you do not need small fractions, you may be better off using the Currency type.
	FLOAT	Real	6	32-bit floating-point numbers. You get about 7 digits. As with DOUBLE, you may be better off using the CURRENCY type.
Other	BOOLEAN	Integer	12	SQLite stores as 0 or 1. But that fact is moot, as the REALbasic feature DatabaseField.BooleanValue conveniently interprets it as a FALSE or TRUE.
	BLOB	Blob	15	The 'B' in blob stands for 'binary'. Binary means the data is treated simply as a bunch of bits without any kind of processing and without any interpretation of its meaning or how to handle it. The absolute size limit in SQLite for a blob is about 2 billion octets. But in most releases of SQLite the usual limit is set to 1 billion. I've not tested REAL Server's limit.
	BINARY	Blob	16	Same as BLOB according to REAL Software's Tech Support.

There is one additional type offered by the REAL Server Admin app's Structures feature: "Real". That type is not documented as having a constant value in REALbasic. And in many other database engines, 'Real' means 32-bits whereas in REAL Server it means 64-bits. So, I suggest using the other fractional number types such as Double or Currency. REAL Software Tech Support agrees.