intro_database
How to create a database table using SQL
========================================
(Note: UPPER CASE represents a syntax word, please use lower case when
using the commands.)
1. First time only: Contact Denise Finstrom (x4688) or John DeVoy
(x4729) to get a login account on the ADBS SQL Server so you can
use the ISQL utility to execute your table creation script
(the .sql file you create in steps 5-8 below).
2. Decide which database (appdb or apvdb) in which to create tables.
The appdb database keeps logs of each change to each row in the
table and these changes are saved each night. The appdb database
is used for most tables. The apvdb database is used for tables
that are more volatile (the changes may be bigger than the whole
table when one row is changed many times in one day). A "snapshot"
of the apvdb database is copied to tape each night.
Use of an image or text field (see below) often indicates a table
should be created in apvdb, NOT appdb... Please consult an expert
if the choice is not clear to you.
3. Organize and decide what data needs to be stored, is it stored
once per program, once per program activation, or once per
"subpage"?
4. Decide what data fields are needed. Remember to add the fields
needed to make each row in the table unique.
5. To document the new table, create a script file with the extension
".sql" containing the commands needed to setup the table and any
stored procedures which access the table. When you MECCA your
program, have this file in the same directory as your source code,
and MECCA will copy it along with the source.
6. The SQL syntax for creating a table is :
CREATE TABLE table_name (
field_name1 field_type not null,
field_name2 field_type not null,
...
field_nameN field_type not null
)
GO
The table name should be unique and represent the table.
FIELD_TYPE 'C' TYPE size range
tinyint unsigned char 1 byte 0 - 255
smallint signed short 2 byte -32768 - 32768
int signed int 4 byte -2147483648 - 2147483647
real float 4 byte n/a
float double 8 byte n/a
binary(n) char[n] n<256 n byte 0x00 - 0xFF each byte
varbinary(n) char[n] n<256 n byte* 0x00 - 0xFF each byte
char(n) char[n] n<256 n byte valid ascii each byte
varchar(n) char[n] n<256 n byte* valid ascii each byte
image char[32768] varies+ 0x00 - 0xFF each byte
text char[32768] varies+ valid ascii each byte
smalldatetime n/a 4 byte 1/1/1900 - 06/06/2079~
datetime n/a 8 byte 1/1/1753 - 12/31/9999~
* varbinary and varchar are stored as their actual length, but
the API zero fills to the declared maximum length.
+ image and text data is readable ONLY using the 'db_nextrow' API
function. The data is stored and returned as actual size
(no zero fill is done). So, any fields after the text/image
field will be shifted left. If less data is available than
specified in the bytes_per_row argument, a positive (success)
ACNET status of SQL_SMALLROW is returned.
Use of an image or text field often indicates a table should be
created in apvdb, NOT appdb. Please, consult an expert if the
choice is not clear to you.
~ smalldatetime has a resolution of one minute.
datetime has a resolution of 1/300th of a second.
Please see the later section on date and time in the Sybase database.
To select a date field as text use: (count as 24 byte column)
select convert(char(24),mydate,109)
OR select a date field as text use: (count as 10 byte column)
select convert(char(10),mydate,101)
(see date conversion styles table for other options)
7. Create index(s) on the table. Note that an index is not needed
if the number of rows is small (less than 100). If you create
any index, the "most important" one should be defined as
clustered (meaning that the table data will be built using this
index). The syntax is:
CREATE [UNIQUE] [CLUSTERED] index idxN on table_name
(field_name,...)
GO
where N starts at 1 and is incremented for each index on the table.
To remove an index the syntax is:
DROP INDEX table_name.index_name
GO
8. To grant read access to a table the syntax is:
GRANT SELECT ON table_name TO {user[,user]}
GO
To grant read/write access to a table the syntax is:
GRANT SELECT,INSERT,UPDATE,DELETE ON table_name TO {user[,user]}
GO
Where user=PAxxxx for an individual PA,
user=appdb_isql for all ISQL users,
user=appdb_console for all console applications,
user=appdb_central for all central services, and/or
user=appdb_readonly for unknown, INCLUDING WWW OFFSITE USERS.
Please don't use user=public anymore. Thanks.
9. Execute your script using ISQL, either using cut and paste or
$ isql -S ADBS -U user -i script.sql
10. The simplest approach to making changes in tables is to delete and
recreate a table. If the table is populated, it's best to create a
temporary table and move the data to it.
For moving data from one table to another the syntax is:
INSERT table_name_new
SELECT field_nameX,field_nameY,fill_data FROM table_name
GO
(NOTE: DO NOT USE _1 or _2, etc. names ending like this are
special to sybase in some cases. Thanks.)
The key here is that the select list must have exactly one
entry for each row in the new table. Please check a SQL
reference or an expert if this is not clear.
To delete all of the data in a table the syntax is:
TRUNCATE TABLE table_name
GO
To delete the table structure and all indexes, the syntax is:
DROP TABLE table_name
GO
Then if you want you can rename back to the old table name:
SP_RENAME table_name_new,table_name
GO
11. Date and time in the Sybase database
1. Sybase's text/string date formats don't include an explict
timezone, e.g., CST, CDT, or UTC.
2. Current Sybase best practice is to run the DB on a machine
that has its locale configured for UTC not CST/CDT or
whatever else might appear to be appropriate given the DB's
location. For historical reasons the controls database
is not configured in this best practices way.
3. The only exception which did not originally exist is that
Sybase now has both getdate() and getutcdate() functions. If
one follows the best practice above getdate() and
getutcdate() are equivalent. Otherwise getdate() will be a
local time per the underlying operating system's locale
configuration and getutcdate() will be a UTC time.
4. A date field initialized by getdate() or getutcdate() does
not and can not record which routine was used to initialize
it. THEREFORE, A SYBASE DATE FIELD WHETHER INITIAILIZED BY A
TEXT/STRING OR getdate() OR getutcdate() DOES NOT KNOW ITS
TIMEZONE. FOR EACH DATE FIELD A TIMEZONE MUST BE CHOSEN AND
USED CONSISTENTLY. For our situation where getdate() and
getutcdate() are not equivalent one has two potential options:
1. Use local time consistently:
a. Only use getdate() never getutcdate() to get the current time.
b. All time strings must implicitly be in local time. Suppose
an alarm occured at "Jun 10 2009 1:46PM CDT" and that we want
to store that value in a DB field where we have chosen to use
local time consistently. In such a case one has
alarm_occured_local = "Jun 10 2009 1:46PM".
2. Use UTC consistently:
a. Only use getutcdate() never getdate() to get the current time.
b. All time strings must implicitly be in UTC. Suppose
an alarm occured at "Jun 10 2009 1:46PM CDT" and that we want
to store that value in a DB field where we have chosen to use
UTC consistently. In such a case one has
alarm_occured_utc = "Jun 10 2009 6:46PM".
In the 1.b. and 2.b. the time of the alarm is the same but the
string used to initialize the date field is differnet because
the timezone convention is different. There is no way to tell
just from the field's type which convention is in use, so it is
advisable to document the choice in some way. The option chosen
in the example is a "_utc" or "_local" suffix. BECAUSE LOCAL
TIMES SUFFER PATHOLOGIES - REPEATED AND DUPLICATED TIMES - CAUSED
BY DAYLIGHT SAVINGS TIME IT IS RECOMMENDED THAT ALL NEW DATE FIELDS
USE THE UTC CONVENTION AND DOCUMENET THIS CHOICE WITH A "_utc"
SUFFIX. SYBASE's datediff, etc. DO NOT HANDLE LOCAL TIMES PROPERLY
EITHER BECAUSE OF THE DAYLIGHT SAVINGS TIME ISSUES.
5. ALL MOST ALL OF THE EXISTING DB TABLES USE LOCAL TIMES AND DON'T
HAVE A "_local" SUFFIX OR ANY OTHER SUCH MECHANISM TO IDENTIFY
THEIR USE OF THE LOCAL TIME CONVENTION. This is because Sybase
did not orginally support getutcdate() and all most all of the
controls software has its origin on VAX/VMS where local time was
used pervasively and not just at the user interface level.
6. SO TO USE UTC CONSISTENTLY FROM WITHIN C/C++ AND JAVA ONE MUST
KNOW HOW TO GENERATE A TIME STRING IN UTC GIVEN A TIME VALUE IN
SOME OTHER SYSTEM, E.G., SECONDS SINCE THE EPOCH, A.K.A., UTC
CTIME, OR CLINKS (SEE clinks_now). HERE IS HOW:
1. C/C++
a. clinks
Use clinks_to_date with the DATE_OPT_UTC_CTIME option.
b. seconds since the Epoch, a.k.a., UTC ctime
Use utc_ctime_to_date with its output_utc argument as true.
2. Java
a. Be careful because java.sql.Timestamp's toString() method
generates a local time.
b. John DeVoy has prepared documentation, which you may find
in the Javadoc for DbServer.
7. IF ONE USES UTC TIMES THEN SYBASE'S datediff CAN BE USED TO GET
TIMES OUT OF THE DATABASE IN EITHER SECONDS SINCE THE EPOCH OR CLINKS:
a. seconds since the Epoch, a.k.a., UTC ctime
UTC_ctime = datediff(second,'1/1/1970',mydate)
b. clinks
clinks = datediff(second,'1/1/1972',mydate)
- datediff(second,'Jan 1 1972 0:00AM','Jan 1 1972 6:00AM')
The line immediately above is because clinks use midnight CST not
midnight UTC as the time part of their t=0 moment.
8. References on Sybase and daylight savings time:
Daylight Savings Time and ASE
Daylight Saving Time and Sybase Server Products
In your application
===================
(Note: All SQL commands must be sent via the CLIB routines 'db_select'
and 'db_modify'.)
1. To select rows from a table, the SQL syntax is:
SELECT field_name1, field_name2 FROM table_name WHERE condition
e.g.: select di,name from device where name = "M:OUTTMP"
2. To insert a new row to a table, the SQL syntax is:
INSERT table_name values (value1, "value2", valueN)
e.g.: insert device values (27235, "M:OUTTMP")
3. To delete a row from a table, the SQL syntax is:
DELETE table_name WHERE condition
e.g.: delete device where name="M:OUTTMP"
Be careful, the database will delete ALL rows matching the
condition! With no conditions, ALL the data rows from a table
will be deleted!!!
4. To UPDATE a row in a table, the SQL syntax is:
UPDATE table_name SET fld_1 = val_1, fld_2 = "val_2" WHERE condition
e.g.: update device set di=12345 where name="M:OUTTMP"
Be careful, the database will update ALL rows matching the
condition! (you will usually want to specify conditions defining
exactly ONE row.) If no condition is provided, ALL the rows in
the table will be updated!!!
Additional notes:
See online help at http://www-ad.fnal.gov/help/ul_clib/db_open.html
Check with an SQL reference book to see what complex queries are
possible!
Multiple commands can be sent together in one 'db_select' call.
In this case, the total number of rows returned by all of the
select statements will be returned in the number of rows argument.
It is best if all of the returned row sizes are the same. If they
differ, a status of SQL_SMALLROW will be returned since some rows
will be smaller than the requested length.
All strings must be delimited by either single ' or double " quotes.
(Be careful if your strings contain quotes!)
To embed both quote types you must double the internal quotes:
"I'll bet he said ""huh?""." OR 'I''ll bet he said "huh?".'
Conditions are of the form:
(field_name1 = 5 and field_name2 = 2) or field_name3 = 7
The correct index(s) can make a very large difference in access
times for large tables. Ask an expert for advice if this may
apply to you.
Related functions:
db_select, db_modify, db_open, db_close, db_error,
db_nextrow, db_users, db_result_column_info, db_send_c