JET Database/Object names and constants

From Wikibooks, open books for an open world
< JET Database
Jump to: navigation, search
JET Database

Introduction · Creating and connecting · Data types · Object names and constants · Data integrity · Security · Data definition language · Select · Insert · Update · Delete · Functions · Views and stored procedures · Manipulation tools · Integration and migration · Design tools

Naming objects in the database[edit]

As with most other SQL databases, care is recommended when naming database objects such as tables, columns, indexes, views and stored procedures. Using names outside the normal naming conventions can cause problems when writing SQL code to handle those objects, requiring such names to be qualified in SQL code. The conventions in JET are:

  • names must begin with an alphabetic character (a-z or A-Z)
  • names must contain only alpha-numeric characters or the underscore
  • names must not be reserved words

Qualifying special names[edit]

There are occasions when objects are given names that cause problems in SQL statements, either by including special characters like spaces, or by using reserved words. Sometimes this is legitimate, other times it isn't, but either way the situation needs to be handled.

In such circumstances, the object names can be qualified in one of the following ways:

  • wrapping the name in (square) brackets, [...]
  • wrapping the name in left single quotes (grave accents), `...`

Here are some examples:

CREATE TABLE `Name With Spaces` (
 `Complex Column Name a/b %` single, 
 [Text] text(20))
GO
 
INSERT INTO `Name With Spaces` (`Complex Column Name a/b %`, [Text]) 
 VALUES (1.45, 'First attempt')
GO
 
SELECT `Complex Column Name a/b %`, [Text] AS [Output Complex Text Name]
 FROM `Name With Spaces`
GO
Complex Column Name a/b % Output Complex Text Name
------------------------- ------------------------
1.45                      First attempt

Constants[edit]

Text constants[edit]

Text constants can either be written with single quote (apostrophe) delimiters or double quote delimiters. Any instances of the delimiters used within the text constant need to be doubled.

SELECT 'Eat the "food" at O''Malley''s' AS T1, 
 "Eat the ""food"" at O'Malley's" AS T2
GO
T1                             T2
------------------------------ ------------------------------
Eat the "food" at O'Malley's   Eat the "food" at O'Malley's

Numeric constants[edit]

Numeric constants can take the form of:

  • plain integers – 123
  • decimal numbers – 123.45
  • decimal numbers in scientific notation – 123.45E5 (equates to 12,345,000)

Numeric constants cannot have commas, dollar signs or other non-numeric characters other than the decimal point or the E in scientific notation.

Date/time constants[edit]

Date and time constants are best written as hash-delimited strings, either in long textual format, US date format (mm/dd/yyyy) or in ODBC date format (yyyy-mm-dd).

Select #26 October 2007 6:43pm# As D_Long_Format, 
 #10/26/2007 18:43:00# As D_US_Format, 
 #2007-10-26 18:43:00# As D_ODBC_Format
go

D_Long_Format           D_US_Format             D_ODBC_Format
----------------------- ----------------------- -----------------------
26/10/2007 18:43:00     26/10/2007 18:43:00     26/10/2007 18:43:00

NB: JET does not unambiguously accept dates in other formats! Specifying a date in dd/mm/yyyy, for example, will appear to work correctly when there is no possibility of interpreting that date in US date format, otherwise it will be interpreted incorrectly, no matter what regional settings are in use. To remove ambiguity, it is best to specify dates in ODBC format.

Reserved words[edit]

This is a list of the reserved words in JET. Not all of these words will cause problems when used as object names, as at JET 4.0, but may in a future version of JET.

absolute action add admindb all allocate
alphanumeric alter and any are as
asc assertion at authorization autoincrement avg
band begin between binary bit bit_length
bnot bor both bxor by byte
cascade cascaded case cast catalog char
character char_length character_length check close coalesce
collate collation column commit comp compression
connect connection constraint constraints container continue
convert corresponding count counter create createdb
cross currency current current_date current_time current_timestamp
current_user cursor database date datetime day
deallocate dec decimal declare default deferrable
deferred delete desc describe descriptor diagnostics
disallow disconnect distinct domain double drop
else end end_exec escape except exception
exclusiveconnect exec execute exists external extract
false fetch first float float4 float8
for foreign found from full general
get global go goto grant group
guid having hour identity ieeedouble ieeesingle
ignore image immediate in index indicator
inheritable initially inner input insensitive insert
int integer integer1 integer2 integer4 intersect
interval into is isolation join key
language last leading left level like
local logical logical1 long longbinary longchar
longtext lower match max memo min
minute module money month names national
natural nchar next no not note
null nullif number numeric object octet_length
of oleobject on only open option
or order outer output overlaps owneraccess
pad parameters partial password percent pivot
position precision prepare preserve primary prior
privileges proc procedure public read real
references relative restrict revoke right rollback
rows schema scroll second section select
selectschema selectsecurity session session_user set short
single size smallint some space sql
sqlcode sqlerror sqlstate string substring sum
system_user table tableid temporary text then
time timestamp timezone_hour timezone_minute to top
trailing transaction transform translate translation trim
true union unique uniqueidentifier unknown update
updateidentity updateowner updatesecurity upper usage user
using value values varbinary varchar varying
view when whenever where with work
write year yesno zone