User Reference Guide
Version 7.1
This documentation and the software described in this document are provided under license,
and usage is restricted by the terms of such license. No responsibility is assumed for third
party products that m
ay be used in conjunction with the software described in this document.
© SolutionsIQ Corporation 1998
All Rights Reserved
About CONNX and SolutionsIQ
Established in 1979, SolutionsIQ provides consulting and software development services to
business est
ablishments, including some of the world's largest software firms. Areas of
specialization are wholesale distribution and manufacturing utilizing IBM AS/400, DEC VAX, or
microcomputer platforms.
SolutionsIQ has expertise with a wide range of programming l
anguages, software
development tools, databases, networks, and systems integration. SolutionsIQ's Graphical
User Interface and Development group has extensive experience with Visual C++, Visual
Basic, TCP/IP, and Pathworks.
SolutionsIQ's consulting and so
ftware development services are available to CONNX users.
Ask your CONNX sales representative about policies and rates.
CONNX
1800 112th Avenue NE
Suite 150
Bellevue, WA, 98004, USA
Telephone: (425) 516
-
6600
Toll Free: (888)
-
88
-
CONNX
Facsimile: (425)
-
453
-
8871
Information:
info@solutionsiq.com
Internet:
www.SolutionsIQ.com
Technical Support: (888) 930
-
2727
Email:
suppo
rt.products@solutionsiq.com
The following are trademarks of SolutionsIQ
in
the USA and other countries:
CONNX
CONNX/16
CONNXCDD
CONNX/32
The following are trademarks of their
respective companies or organizations:
Crystal Reports/Cognos, Inc.
Dharm
a/SQL, Dharma/ODBC, ODBC
Integrator/
Dharma Systems, Inc.
Macintosh/Apple Computer, Inc.
Oracle, Oracle RDB, and Oracle CODASYL
DBMS/
Oracle Corporation.
OS/2/IBM, Inc.
Paradox/Ansa Software, a Borland
company.
Postscript/Adobe Systems, Inc.
Sun/Sun Micros
ystems, Inc.
UNIX/AT&T Bell Laboratories
The following are trademarks of Microsoft
Corporation:
Microsoft
MS
MS
-
DOS
Microsoft Access FoxPro
Visual Basic
PowerPoint
Visual C++
Litho in USA, October 1998 P/N CX981008A
-
QV
iii
Table of Contents
1
-
Preface
................................
................................
.................
1
-
1
About SolutionsIQ
................................
................................
.................
1
-
1
Technical Support
................................
................................
.................
1
-
2
2
-
Overview
................................
................................
..............
2
-
2
The CONNX Server
................................
................................
..............
2
-
3
The CONNX
Data Dictionary
................................
................................
2
-
3
The CONNX ODBC Driver
................................
................................
....
2
-
4
CONNX Requirements for RMS, RDB & DBMS
................................
...
2
-
5
CONNX Requirements for DataFlex
................................
.....................
2
-
7
With What
Is CONNX Compatible?
................................
......................
2
-
8
Components
................................
................................
.........................
2
-
9
3
-
CONNX Data Dictionary
................................
......................
3
-
1
Creating your first data dictionary
................................
.........................
3
-
1
Opening an existing Dictio
nary
................................
.............................
3
-
3
Manually creating a data dictionary entry
................................
.............
3
-
4
Creating an RMS entry manually
................................
...................
3
-
5
Creating a DataFlex entry manually
................................
...............
3
-
9
Creatin
g an Oracle RDB entry manually
................................
......
3
-
10
Creating an Oracle DBMS entry manually
................................
....
3
-
10
Documenting existing dictionary entries
................................
.............
3
-
11
Saving a data dictionary entry
................................
............................
3
-
12
Adding multiple manual table entries to a data dictionary
..................
3
-
12
Importing data dictionary entries
................................
.........................
3
-
12
Importing from a VMS CDD Repository (RMS only)
.....................
3
-
13
Importing from Powerhouse PDL Files (RMS Only)
.....................
3
-
13
Importing from COBOL FD Files (RMS Only)
...............................
3
-
14
Importing using the RMS text file import specification
..................
3
-
14
Importing from DataFlex Directory (DataFlex Only)
......................
3
-
23
Importing from an Oracle RDB Database (RDB Only)
.................
3
-
24
iv
Importing from an Oracle DBMS Database (DBMS Only)
............
3
-
25
CONNX Security Overview
................................
................................
.
3
-
26
Adding Security to a data dictionary entry
................................
..........
3
-
27
Adding Users and Groups
................................
............................
3
-
27
Applying Security to Tables and Columns
................................
....
3
-
29
Database Account Management
................................
..................
3
-
30
Application Management
................................
..............................
3
-
31
Maximum Security
................................
................................
........
3
-
32
4
-
CONNX ODBC Driver
................................
...........................
4
-
1
ODBC Arc
hitecture
................................
................................
...............
4
-
2
CONNX ODBC Conformance
................................
...............................
4
-
2
Configuring the Data Source
................................
................................
4
-
3
CONNX INI File Settings
................................
................................
......
4
-
8
CONNX Performance
................................
................................
.....
4
-
8
CONNX Debugging
................................
................................
........
4
-
8
CONNX TCPIP Settings
................................
................................
.
4
-
9
CONNX DataFlex Settings
................................
.............................
4
-
9
CONNX Year 2000 Settings
................................
.........................
4
-
10
ODBC SQL Grammar
................................
................................
.........
4
-
12
Primary SQL Commands
................................
.............................
4
-
12
SQL Time & Date Literals
................................
............................
4
-
15
SQL String Functions
................................
................................
...
4
-
16
SQL Date Functions
................................
................................
.....
4
-
17
SQL Conversion Functions
................................
................................
.
4
-
18
SQL Aggregate functions
................................
................................
....
4
-
19
SQL Join Syntax
................................
................................
.................
4
-
20
CONNX SQL extended functions
................................
.................
4
-
21
Transaction Supp
ort
................................
................................
...........
4
-
22
Special Features of CONNX for DBMS
................................
..............
4
-
23
Troubleshooting connection problems
................................
................
4
-
25
ODBC Programming Considerations
................................
..................
4
-
26
5
-
Advan
ced Features of CONNX
................................
...........
5
-
1
Views
................................
................................
................................
....
5
-
1
Create Table/Drop Table
................................
................................
......
5
-
2
Create Index/Drop Index
................................
................................
.......
5
-
3
v
Rotated Arrays (RMS Only)
................................
................................
..
5
-
4
Using the Rotated Array Assistant (RMS)
................................
.............
5
-
7
Clone Table Assistant (RMS)
................................
.............................
5
-
11
ODBC SQL Performance Tips
................................
............................
5
-
14
Microsoft Access & Visual Basic Performanc
e Tips
...........................
5
-
16
Snapshots vs Dynasets
................................
................................
5
-
16
Microsoft Access
................................
................................
..........
5
-
18
Visual Basic
................................
................................
..................
5
-
20
6
-
CONNX OLE RPC Server
................................
.....................
6
-
1
CONNX.Connect
................................
................................
..................
6
-
1
Node
................................
................................
...............................
6
-
1
UserName
................................
................................
......................
6
-
1
Password
................................
................................
.......................
6
-
2
LastResultCode
................................
................................
..............
6
-
2
LastErrorMessage
................................
................................
..........
6
-
2
InputValue
................................
................................
......................
6
-
2
InputValueLength
................................
................................
...........
6
-
2
OutputValue
................................
................................
...................
6
-
2
OutputValueLength
................................
................................
........
6
-
2
Connect
................................
................................
..........................
6
-
3
D
isconnect
................................
................................
.....................
6
-
3
RPC ( RPCName as String)
................................
...........................
6
-
3
7
-
CONNX Remote Procedures
................................
...............
7
-
1
Building a Remote Procedure
................................
........................
7
-
2
Appendix A
-
CONNX Data Types
................................
............
A
-
1
Appendix B
-
CONNX DataFlex Data Type Chart
....................
B
-
1
Appendix C
-
Demonstrations/Applications
...........................
C
-
2
Sample Visual Basic ODBC Application
................................
..............
C
-
2
vi
Index
................................
................................
...........................
I
-
5
Preface
1
-
1
1
-
Preface
This manual describes the general features of the CONNX product. The
CONNX/32 32
-
bit Product and the CONNX/16 16
-
bit Product are licensed
together. Please consult your sales representat
ive for licensing policies
. The
term CONNX used throughout the manual applies to both products, unless
otherwise specified.
About SolutionsIQ
Established in 1979, SolutionsIQ provides consulting and sof
tware
development services to business establishments, including some of the
world's largest software firms. Areas of specialization are wholesale
distribution and manufacturing utilizing IBM AS/400, DEC VAX, or
microcomputer platforms.
SolutionsIQ has e
xpertise with a wide range of programming languages,
software development tools, databases, networks, and systems integration.
SolutionsIQ's Graphical User Interface and Development group has extensive
experience with Visual C++, Visual Basic, TCP/IP, and
Pathworks.
SolutionsIQ's consulting and software development services are available to
CONNX users. Ask your CONNX sales representative about policies and
rates. Call 1
-
425
-
519
-
6600 or 1
-
888
-
88CONNX (1
-
888
-
882
-
6669)
Preface
1
-
2
Technical Support
Should you encounter any problems with the installation or operation of
CONNX, if you purchased products through an authorized CONNX reseller,
please contact them.
If you purchased product directly from SolutionsIQ, contact Technical Support:
VOICE: (880) 930
-
2727
FAX: (425) 453
-
8871
E
-
MAIL: SUPPORT.PRODUCTS@SolutionsIQ.COM
SolutionsIQ Technical Support personnel are available Monday through Friday
between 6:30 a.m. and 4:30 p.m. Pacific Time.
Overview
2
-
2
2
-
Overview
CONNX/32 and CONNX/16 are unique clie
nt/server connectivity programming
toolsets that make it possible to use PC’s in real
-
time interactive operation
with many databases and mainframes. CONNX provides read/write access to
the data in these systems. CONNX includes the following components:
The CONNX ODBC Driver
The CONNX Data Dictionary
The CONNX Host Data Server.
CONNX supports databases on the following host database platforms:
Operating System
Database
VAX/VMS
RMS, RDB, DBMS
AXP Alpha/VMS
RMS, RDB, DBMS
Windows
DataFlex
Versions
of CONNX are available for the DEC Alpha platform. References to
VAX in this manual also apply to the DEC Alpha. Please refer to
SolutionsIQ's current price list for details regarding license fees for the above
components.
NOTE: If you have not yet in
stalled CONNX, please follow the instructions in
the Quick Start Installation Guide, and review the Installation checklist.
Overview
2
-
3
The CONNX Server
The CONNX Server is a full
-
featured data server that translates SQL requests
into the native d
atabase requests. The CONNX ODBC Driver makes this
server transparent to the end user. Features include:
Complete user
-
level and group level security.
Low memory and disk resource utilization.
Complete file and/or table security.
RPC Support (for VMS
only).
The CONNX Data Dictionary
The CONNX Data Dictionary is a repository of information about the data
tables and fields. The CONNX Data Dictionary:
Can reside on the client computer, a shared server disk, or in a
Pathworks ar
ea on a VMS system.
Describes the structures of the tables or files being accessed.
Multiple views of the same data are allowed, thus enhancing
security and ease
-
of
-
use.
Overview
2
-
4
The CONNX ODBC Driver
The CONNX ODBC Driver is a dynamic link l
ibrary that applications can call to
access data located in remote systems. The CONNX driver processes the
ODBC function calls, submits requests to the appropriate data source, and
then returns the results. The CONNX ODBC Driver:
Is tightly coupled with
the CONNX Data Dictionary.
Uses Structured Query Language (SQL) is the standard for
accessing information.
Provides the ability to use off
-
the
-
shelf ODBC compliant
applications without the need to program.
Overview
2
-
5
CONNX Requirements
for RMS, RDB & DBMS
CONNX requires a PC running Windows (any version), and a VMS server
running VMS version 5.4 or greater.
Network Requirements:
CONNX requires a TCP/IP or DECNet network connection
between the client PC
and the VMS Server.
Client PC Requirements:
Minimum:
Software
Windows 3.1 or Windows for Workgroups 3.11
for TCPIP: Any Winsock TCP/IP software
for DECNet: Pathworks 4.0 or greater
Hardware
386/16 with 4 megabytes of memory.
25 megabytes of free hard
drive space.
Recommended:
Software
Windows 95, Windows 98, or Windows NT 4 with
SP3 or greater
for TCPIP: Microsoft TCP/IP
for DECNet: Pathworks 7 or greater
Hardware
Pentium 90 with 32 megabytes of memory.
50 megabytes of free hard drive space.
VMS
Server Requirements:
Minimum:
Software
VMS 5.4
for TCP/IP: any UCX compatible TCP/IP software
for DECNet: DECNet services enabled under VMS
Hardware
DEC MicroVAX II or Alpha Station 200 4/100
Overview
2
-
6
Recommended:
Software
OpenVMS 6.0 or greater
for TCP/IP:
any UCX compatible TCP/IP
software
for DECNet: DECNet services enabled under
VMS
Hardware
Any DEC Alpha server
Additional VMS Requirements for CONNX for RDB:
Software
RDB version 6 or higher
Additional VMS Requirements for CONNX for DBMS:
Software
DBMS version 4.3 or higher
Overview
2
-
7
CONNX Requirements
for DataFlex
Network Requirements:
CONNX requires a network connection between the client PC and
the PC or Server that contains the DataFlex files t
o be accessed.
Client PC Requirements:
Minimum:
Software
Windows 3.1 or Windows for Workgroups 3.11
Hardware
386/16 with 4 megabytes of memory.
25 megabytes of free hard drive space.
Recommended:
Software
Windows 95, Windows 98, or Windows NT 4 wit
h
SP3 or greater
Hardware
Pentium 90 with 32 megabytes of memory.
50 megabytes of free hard drive space.
Overview
2
-
8
With What Is CONNX Compatible
?
In general, CONNX works with any ODBC Compliant application.
Specifically, we know that progr
ams have been written successfully utilizing
CONNX to access data using:
OLE
ODBC
Product
Microsoft Visual Basic
Microsoft Visual C++, Microsoft Visual Studio
PowerBuilder
Microsoft Access
Microsoft Excel (MSQuery) and Excel
Cognos Impromptu
Crystal Reports
Borland C++
Microsoft Visual Basic for Applications (VBA)
Paradox for Windows
Dharma ODBC Integrator
Visual FoxPro for Windows
JetForms
Any ODBC or OLEDB compliant application
Overview
2
-
9
Compone
nts
CONNX is a universal data connectivity product that provides real
-
time access
to multiple disparate databases. The CONNX product currently supports the
following databases:
Digital RMS
DataFlex
Oracle RDB
Oracle DBMS
With the CONNX product, dat
a from any of the above database types can be
retrieved and updated. The CONNX approach to data access is to treat all of
the different data sources as a single database. The CONNX architecture
consists of three main components:
The CONNX ODBC Driver, the CONNX
Data Dictionary, and the CONNX Server.
Client PC
VMS Mainframe
User Application
(Access, VB, Crystal Reports,
IIS, Powerbuilder, Impromptu,etc)
CONNX ODBC Driver
RDB
CNXDBMS
Server
RMS Files
DBMS
DataFlex
CNXRDB
Server
CNXRMS
Server
Secure
Data Dictionary
This is the CONNX enterprise data model. CONNX has a distributed SQL
engine, meaning that the work of processing queries is distributed between
the client and
the server. Most of the CPU intensive query processing such as
data conversion and sorting is performed on the PC. All of the data retrieval is
Overview
2
-
10
performed on the server. In the case of CONNX for DataFlex, all processing
is done on the PC, because there i
s no server component. The following
diagram shows the distribution of labor in the CONNX architecture:
Client PC
VMS Mainframe
Data Conversion
Meta-data Retrieval (CONNX CDD)
First Pass SQL Optimization
Partial Joins
Sorting
Grouping
Extended SQL Functions
CONNX Security
Indexed Retrieval
Non-Indexed Retrieval
Partial Joins
Data Compression (If requested)
RPCs
Database Security
This distributed architecture has several advantages:
1.
When performing joins, significantly less data is sent across the
network
because no duplicates are transmitted.
2.
The workload on the VMS mainframe is minimal, because CPU
bound tasks are moved to the client, resulting in a reduction of load
on the mainframe.
3.
When several users are issuing queries simultaneously, the CPU
power of
each client computer is utilized in addition to that of the
server, resulting in true parallel processing. This makes CONNX
highly scalable to a large enterprise.
CONNX Data Dictionary
3
-
1
3
-
CONNX Data Dictionary
The CONNX Data Dictionary is used to create,
store and maintain table
definitions. The dictionary is required in order to use the CONNX ODBC
Driver.
It is a REQUIREMENT that all of the table definitions that could
potentially be joined together reside in ONE Data Dictionary.
Once you create a data
dictionary, please re
-
use this data dictionary for
additional table definitions.
Creating your first data dictionary
1.
Double click on the CONNX Data Dictionary icon from the CONNX
Administrator folder.
2.
Enter the Logon Inf
ormation appropriate to your site (only required for
RMS).
(EXAMPLE)
CONNX Data Dictionary
3
-
2
3.
You will be presented with an “Open File” dialog. Since you do not
have an existing file to load, press Cancel.
4.
You will be presented with the data definition window similar t
o the one
described below.
CONNX Data Dictionary
3
-
3
Opening an existing Dictionary
1.
Double click on the CONNX Data Dictionary icon from the CONNX
Administrator folder.
2.
You will be presented with an “Open File” dialog. Enter the name of
your prev
iously saved data dictionary.
3.
You have now opened your previously saved data dictionary!
CONNX Data Dictionary
3
-
4
Manually creating a data dictionary entry
Press the “Add Table” button to add a new RMS or DataFlex table entry to the
da
ta dictionary. Manual entries cannot be created for RDB and DBMS tables.
For RDB and DBMS, use the Import option.
The Add Table button can also be used to create CONNX views.
After pressing the “Add Table” button, the following dialog will appear:
SQL Object Name
. The SQL Table Name will refer to the data table
specified below in SQL Statements. A valid table name cannot contain
spaces or begin with a number. The maximum length is
50 characters.
Object Type
. This option determines whether the object is a “view”, or a
“table” definition. A “view” is a SQL statement that defines the relationships
between one or more “tables”.
Manual data dictio
nary entries can only be made for RMS and DataFlex files,
and CONNX heterogeneous views. Entries for RDB and DBMS must be
created using the Import option.
Database
. This option specifies the type of database in which t
he table is
located. Valid database types for manual entry are RMS and DataFlex.
CONNX Data Dictionary
3
-
5
Creating an RMS entry manually
RMS Filename:
This is the physical VMS path for
the RMS file. The
maximum length of the filename is 255 characters. The RMS file name may
also include VAX logicals. If a logical is used in the filename, it is important
that the logical is still defined when logging onto the VMS system as a
“network” p
rocess. Please make sure that the user LOGON.COM or the
system logon does not contain a line that will pre
-
maturely exit if the process
is not interactive.
Example: IF F$MODE() .NES. “INTERACTIVE” THEN EXIT. Additionally,
CONNX does not support VMS n
ode name (NODE::) specifiers in the
filename.
CONNX Data Dictionary
3
-
6
SQL View clause:
The SQL view clause may be any valid SQL expression. It
may be used to limit the type of records that are returned from the table. This
is most useful whe
n a table contains records of different types. The maximum
length is 128 characters. Suppose you had a table with a column called
itemtype. You might create a view like this:
ITEMTYPE = “C”
Record Length:
This is the
maximum length of a record in the RMS file. For
variable length files, specify the longest record in the file. The length may not
exceed 32,767. If you do not know the record length of the file, use the DCL
command
ANALYZE / RMS <rms filename>
Show CN
XROWNUMBER :
This checkbox shows whether the records in the
table can be directly accessed by number. For RMS, the CNXROWNUMBER
that represents the RRN (Relative Record Number) for all relative files. For
DataFlex, thi
s number represents the DataFlex record number. For RMS, this
checkbox is automatically selected by the data dictionary based on the
organization of the RMS file, and cannot be set by the user.
Show CNXROWID:
This checkbox
determines whether CONNX will use the
RFA (Record File Address) of RMS files as a primary key. This option is
useful on RMS files that do not have a unique key. For RMS, the RFA will be
represented by the pseudo
-
field “CNXROWID”. For RDB the CNXROWID
rep
resents the DBKEY. For DBMS, the CNXROWID represents the set
currency. For DataFlex, the CNXROWID represents the record number.
Large Rotated Array Information / Rotated Array:
A rotated array converts
column
-
wise arr
ays, into row data. Please refer to the Rotated Arrays section
under “Advanced Features” in this manual for more information.
Priority Count:
This value is used internally by CONNX to compare the
relative size of tabl
es when performing join optimization. Do not be alarmed if
the record count does not match the actual number of records in the RMS file.
Sometimes, records are compressed by VMS. This value is only needed as
an estimate. Pressing the “Refresh Indexes” b
utton will refresh this value and
perform the equivalent to the SQL Command “Update Statistics”
CONNX Data Dictionary
3
-
7
Adding columns to an RMS file entry
:
SQL Column Name:
This
name will be used to refer to this field in SQL
Statements. Column names must be unique in a table. A valid column name
cannot contain spaces or begin with a number. The maximum length is 30
characters.
Native Type:
Thi
s field represents the data type of the data.
Please refer to the appropriate Data Type Chart Appendix for more
information.
SQL Type:
This field represents the SQL ODBC data type of the data. This
field is determined by th
e Native Type automatically.
Offset:
This field represents the physical offset into the record of this column.
All offsets are zero based. The offset field can be used to “redefine” a
column or group of columns by reusing t
he same physical offset. The
CONNX CDD application will automatically calculate the offsets for you by
pressing the “
Calculate Offset
” button.
CONNX Data Dictionary
3
-
8
Note: Do not use the “Calculate Offsets” button if your record contains
redefined fields.
Length:
This field represents the physical length in bytes of this column.
Decimal Places:
This field represents the number of implied decimal places
in this column.
Scale:
This field should rarely be used. This field represents the number of
places to move the decimal point in a numeric field. A scale of
-
2 will
convert the number 345.67 to 3.4567 and a scale of 2 will convert the
number 345.67 to 34567
Array Offs
et:
This field is used to determine the size of one element of an
array when used with the Rotated Array Option. Please refer to the Rotated
Arrays section under “Advanced Features” in this manual for more
information.
C
omment:
This field is used to provide up to 64 characters of descriptive text
for this data field.
Specifying RMS indexes
:
Index Columns:
The
CONNX data dictionary now automatically maintains
indexes. When you make a change to the record layout, just press “Refresh
Indexes” to view the new index relationships.
CONNX Data Dictionary
3
-
9
Creating a DataFlex entry manually
DataFlex Filename:
The DataFlex file name points to the actual DataFlex
data file with the “.DAT” file extension.
Record Length:
The record length field is calculat
ed automatically based on
the DataFlex file specified.
SQL View clause:
The SQL view clause may be any valid SQL expression. It
may be used to limit the type of records that are returned from the table. This
is most use
ful when a table contains records of different types. The maximum
length is 128 characters. Suppose you had a table with a column called
itemtype. You might create a view like this:
ITEMTYPE = “C”
Show CNXROWID:
This chec
kbox determines whether CONNX will use the
DataFlex record number as an additional key.
CONNX Data Dictionary
3
-
10
Priority Count:
This value is used internally by CONNX to compare the
relative size of tables when performing join optimization.
Do not be alarmed if
the record count does not match the actual number of records in the DataFlex
file. This value is only needed as an estimate. Pressing the “Refresh Indexes”
button will refresh this value and perform the equivalent to the SQL Command
“Update Statistics”
Adding columns to a DataFlex file entry
:
Column attributes of DataFlex files for the CONNX data dictionary are
automatically maintained. Only two attributes of a colum
n may be modified
for DataFlex:
SQL Column Name:
This name will be used to refer to this field in SQL
Statements. Column names must be unique in a table. The CONNX Column
name may be different than the DataFlex column n
ame. This allows for the
renaming or aliasing of column names. A valid column name cannot contain
spaces or begin with a number. The SQL column name maximum length is 30
characters.
Comment:
This field is used to provide up
to 64 characters of descriptive text
for this data field.
Specifying DataFlex indexes
:
Index Columns:
The CONNX data dictionary now automatically maintains
indexes. When you make a change to the record layout, just press “Refresh
Indexes” to view the new index relationships.
Creating an Oracle RDB entry manually
Table entries for RDB cannot be m
anually created. To add a table entry for
RDB, select the Import option.
Creating an Oracle DBMS entry manually
Table entries for DBMS cannot be manually created. To add a table entry for
DB
MS, select the Import option.
CONNX Data Dictionary
3
-
11
Documenting existing dictionary entries
CONNX provides a method to add comments to tables and columns of tables
that can be printed by the CONNX data dictionary.
SQL Column Name:
This name will be used to refer to this field in SQL
Statements. Column names must be unique in a table. The CONNX Column
name may be different than that of the original table. This allows for
the
renaming or aliasing of column names. A valid column name cannot contain
spaces or begin with a number. The SQL column name maximum length is 30
characters.
Table Comment:
This field is used to provide up to 64 ch
aracters of
descriptive text for this data field.
Column Comment:
This field is used to provide up to 64 characters of
descriptive text for this data field.
These comments and columns names will appear on Data Dictio
nary
printouts.
CONNX Data Dictionary
3
-
12
Saving a data dictionary entry
Once you have entered all required fields for a data definition, Choose “File,
Save”, or use the “Save” toolbar button.
Adding multiple manual table entries to a data
dictionary
For each new data dictionary entry, press
, and proceed with
entering the new entry.
Once you have entered all required fields for a data definition, Choose “File,
Save”, or use the “Save” toolbar
button.
Importing data dictionary entries
To import a data dictionary entry, perform the following steps:
1.
Press
.
2.
Choose the appropriate import type, and enter the entry name for
import.
If CONNX detects more than on
e record layout when
importing, multiple data dictionary entries will be created.
CONNX Data Dictionary
3
-
13
Importing from a VMS CDD Repository
(RMS only)
When importing from a VAX or Alpha CDD, specify the full C
DD record name.
Example: cdd$top.products.customer_record
CDD specifications do not contain the RMS file name.
Remember to specify
an RMS File on the Information Panel, as the default file name is
“UNKNOWN.RMS”. No additional logon information is requir
ed.
Importing from Powerhouse PDL Files
(RMS Only)
When importing from a Powerhouse PDL, specify the full RMS filename for
CONNX Data Dictionary
3
-
14
the PDL import file. All of the record layouts in the specified PDL
file will be
imported. No additional logon information is required.
Importing from COBOL FD Files
(RMS Only)
When importing from a
COBOL FD
, specify the full RMS filename for the
COBOL FD
impo
rt file. All of the record layouts in the specified
COBOL FD
file will be imported.
COBOL FD specifications do not contain the RMS file
name.
Remember to specify an RMS File on the Information Panel, as the
default file name is “UNKNOWN.RMS”. No addition
al logon information is
required.
Importing using the RMS text file import specification
This feature allows you to import the record layout from a CONNX RMS Text
import file.
The RMS file should be sequential. The format of the text file is as
follows:
CONNX Data Dictionary
3
-
15
The first line of each record should be as follows:
CONNXTABLE, <SQL TableName>, <RMS File Name>,<Record Length>
One import file may contain multiple record layouts, each
starting with the
header above.
Each subsequent line in the file represents a column in the record layout.
The format for the line is as follows:
<column name>, <column length>, <column offset>, <column type>, <column
scale>, <column base>, <column fract
ion>, <column comment >
<column name>:
Name of the column
<column length>:
Length of the column
<column offset>:
Offset of the column
<column type>:
Code for the data type of the column (see table A)
<column scale>:
Scale of the column (power of 10)
A scale of 2 would convert 4.3 to 430
A scale of
-
2 would convert 4.3 to .043
<column base>:
Reserved
-
must be 0
<column fraction>:
Fraction of the column (negative power of 10)
A fraction
of 2 would convert 4.3 to .043
A fraction of
-
2 would convert 4.3 to 430
<column comment>:
Comment field
Example RMS Import File:
CONNXTABLE, CompanyTable, COMPTABLE.DAT, 64
Company, 30, 0, 1000, 0, 0, 0, This is the Company Field
Title, 10, 30, 1000, 0, 0, 0, This is the Title Field
Name, 20, 40, 1000, 0, 0, 0, This is the Name Field
Age, 4, 60, 1023, 0 ,0 ,2, This is the Age Field
CONNX Data Dictionary
3
-
16
Table A
–
CONNX Import Data Type Codes.
Native Data Type
CONNX Import Code
Text (Space Padded)
1 (or 1000)
Text (Null Terminated)
2
Text (Expandable)
3
Zero Filled Text
4 (or 1039)
Single Precision
5 (or 1018)
Double Precision
6 (or 1017
)
Byte Integer
7 (or 1026)
Byte Decimal
8 (or 1021)
Byte Bit (1/0)
9 (or 1001)
Byte Bit Text (Yes/No)
10
Word Integer
11 (or 1027)
Word Decimal
12 (or 1022)
Longword Integer
13 (or 1028)
Longword Decimal
14 (or 1023)
Quadword Integer
15 (or 1029)
Quadword Decimal
16 (or 1024)
Integer Text
17 (or 1010 with fraction =
0)
Double Text
18 (or 1010 with fraction >
0)
Double Text (Formatted)
19
Dibol (1
-
19 bytes) Double
20 (or 1015 with fraction >
0)
Dibol (1
-
19 bytes) Integer
21 (or 1015 with fraction =
0)
PACKED Decimal D
ouble
22 (or 1014 with fraction >
0)
PACKED Decimal Integer
23 (or 1014 with fraction =
0)
CONNX Data Dictionary
3
-
17
Native Data Type
CONNX Import Code
Signed Overpunch Double
24 (or 1011 with fraction >
0)
Signed Overpunch Integer
25
(or 1011 with fraction =
0)
(TXT) VMS Date/Time
26
VMS Date
27 or 1002
VMS Time
28
VMS Date/Time
29
Reverse VMS Date
30 (or 1052)
Reverse VMS Time
31 (or 1053)
Reverse VMS Date/Time
32 (or 1054)
Cognos PHDate
33 (or 1040)
Cognos JDate
34 (or 1041)
Binary
35
Binary (Text)
36
Basic Plus Single
37
Basic Plus Double
38
Basic Plus Word Int
39
Basic Plus Long Int
40
Text Date (DDMMYY)
41 (or 1042)
Text Date (DDMMYYYY)
42 (or 1043)
Text Date (YYMMDD)
43 (or 1044)
Text Date (YYYYMMDD)
44 (or 1055)
Text Date (MMDDYY)
45 (or 1050)
CONNX Data Dictionary
3
-
18
Native Data Type
CONNX Import Code
Text Date (MMDDYYYY)
46 or (1051)
Long Date (DDMMYY)
47 (or 1035)
Long Date (DDMMYY
YY)
48 (or 1036)
Long Date (YYMMDD)
49 (or 1037)
Long Date (YYYYMMDD)
50 (or 1038)
Long Date (MMDDYY)
51 (or 1048)
Long Date (MMDDYYYY)
52 (or 1049)
Pack Date (DDMMYY)
53
Pack Date (DDMM
YYYY)
54
Pack Date (YYMMDD)
55
Pack Date (YYYYMMDD)
56
Pack Date (MMDDYY)
57
Pack Date (MMDDYYYY)
58
Text Date 2000(DDMMYY)
59
Text Date 2000(YYMMDD)
60
Text
Date 2000(MMDDYY)
61
Long Date 2000(DDMMYY)
62
Long Date 2000(YYMMDD)
63
CONNX Data Dictionary
3
-
19
Native Data Type
CONNX Import Code
Long Date 2000(MMDDYY)
64
Pack Date 2000(DDMMYY)
65
Pack Date 2000(YYMMDD)
66
Pack Date 2000(MMDDYY)
67
Maxcim Key Date
68
Text Currency
69 (or 1046)
Word Currency
70 (or 1031)
Longword Currency
71 (or 1032)
Quadword Currency
72 (or 1033)
PACKED Decimal Currency
73 (or 1047)
VMS 4 Byte Date
74
Reverse VMS 4 Byte Date
75
CODA Year
76
Reverse CODA Year
77
BMS Date
78
BMS Re
verse Date
79
Noah Date
80
Noah Inverse Date
81
Noah Time
82
Byte Bit#0 (2^0)
83
Byte Bit#1 (2^1)
84
Byte Bit#2 (2^2)
85
Byte Bit#3 (2^3)
86
Byte Bit#4 (2^4)
87
Byte B
it#5 (2^5)
88
Byte Bit#6 (2^6)
89
CONNX Data Dictionary
3
-
20
Native Data Type
CONNX Import Code
Byte Bit#7 (2^7)
90
Winery Word Date
91
Winery Plus Word Date
92
Winery Long Date
93
Winery Plus Long Date
94
Poise Double
95
VMS C Date
96
VMS C Reverse Date
97
Word Double
98
24bit Pascal Integer
99
PACKED (A) Decimal Double
100
PACKED (A) Deci
mal Integer
101
PACKED (E) Decimal Double
102
PACKED (E) Decimal Integer
103
PACKED (F) Decimal Double
104
PACKED (F) Decimal Integer
105
DAI History Date
106
DAI History Time
107
DAI History
Year
108
DAI History Period
109
Left Seperate Double
110
Left Seperate Integer
111
Right Seperate Double
112
Right Seperate Integer
113
VMS G Float
114 (or 1018)
VMS H Float
115 (or 1019)
Allport Julian Date (4 byte)
116
CONNX Data Dictionary
3
-
21
Native Data Type
CONNX Import Code
Allport Time
117
National Compressed Double
118
ADL Math
119
ADL MMIS Number
120
ADL Date
121
ADL GL Number
122
ADL Type Code
123
ADL Zip Code
124
ADL Old Math
125
DataFlex Text
126
DataFlex Double
127
DataFlex Longword Integer
128
DataFlex Date
129
DataFlex Binary
130
String Tinyint
131
String Smallint
132
String Integer
133
String Quadword
134
String Single
135
String Double
136
RDB Date VMS
137
RDB Date ANSI
138
RDB Time
139
RDB Timestamp
140
Interval Day
141
Interval Hour
142
Interval Minute
143
Interval Date Day/Second
144
Interval Date Hour
145
CONNX Data Dictionary
3
-
22
Native Data Type
C
ONNX Import Code
Interval Date Hour/Minute
146
Interval Date Hour/Second
147
Interval Date Minute
148
Interval Date Minute/Second
149
Interval Date Month
150
Interval Date Second
151
Int
erval Date Year
152
Interval Date Year/Month
153
Morse Date
154
Allport Julian Date (2 byte)
155
VMS X Float
156
Reverse VMS Date (6 byte)
157
VMS Date (6 byte)
15
8
VMS S Float
159
VMS T Float
160
KCS Compressed Integer
161
KCS Phone
162
KCS Zip Code
163
KCS Time (2 byte)
164
KCS Time (3 bytes)
165
Unsigned Word
166
KCS Date (3 bytes)
167
Variable Length Integer
168
Variable Length Decimal
169
CONNX Data Dictionary
3
-
23
Importing from DataFlex Directory
(DataFlex Only)
When import
ing from a DataFlex data file directory, specify the full path for the
directory. This may be a local or network path. All of the DataFlex files in the
specified directory will be imported. No additional logon information is
required.
CONNX Data Dictionary
3
-
24
Importing from an
Oracle RDB Database (RDB Only)
When importing tables from an RDB database, specify the full VMS path for
the database. This path may include VMS logicals. CONNX also requires
additional logon information to be provided.
Server
The name of the VMS
system that is running CONNX
for RDB.
UserName
VMS Username to be used when creating CONNX
process on VMS system.
Password
VMS Password to be used when creating CONNX
process on VMS system.
CONNX Data Dictionary
3
-
25
Importing from an Oracle DBMS Database
(DBMS Only)
When importing tables from a DBMS database, specify the full VMS path for
the database. Please include the “.ROO” file extension on the database
name. This path may include VMS logicals. CONNX also
requires additional
logon information to be provided.
Server
The name of the VMS system that is running CONNX
for DBMS.
UserName
VMS Username to be used when creating CONNX
process on VMS system.
Password
VMS Password to be used when creating CONNX
pr
ocess on VMS system.
CONNX Data Dictionary
3
-
26
CONNX Security Overview
The CONNX product has a comprehensive security model, to keep data safe
and sound. CONNX security has the following features:
Existing security established on the database is hono
red by CONNX.
If a user through normal means has read
-
only access to a table, the
user will still have read
-
only access to the table through CONNX.
CONNX does not bypass any security established on a database or
file.
CONNX security can be assigned to an
individual user, or to a group of
CONNX users.
CONNX supports four access levels: Full Access, Read Only, Write
Only, and No Access. These access levels can be applied to a table
object, a view object, or a column within a table or a view.
Integrated S
ecurity / Database Account Management
CONNX provides a mechanism to allow users to specify a single
CONNX userid and password to access multiple databases that may
each require a distinct userid and password. The database specific
userid and password info
rmation is stored in the CONNX data
dictionary.
Secure Data Dictionary
The CONNX Data Dictionary is encrypted to only allow access to
authorized users.
Maximum Security Option
This option allows only users defined in the CONNX data dictionary to
access d
ata, regardless of database permissions.
CONNX Data Diction
ary
3
-
27
Adding Security to a data dictionary entry
Adding Users and Groups
Defining CONNX Users…
Select “Add User” to add a new CONNX user to the
Data dictionary. To set or
change the CONNX password, press the “Change Password” button.
Defining CONNX Groups…
CONNX Data Dictionary
3
-
28
Define CONNX Group Users…
CONNX Data Dictionary
3
-
29
Applying Security to Tables and Columns
1.
Select
a SQL Object.
2.
Press the
Panel Button.
3.
To add a security restriction, press
button.
Full access is assumed by default.
Select the name of the user or group whom you would like to restrict.
Enter the column (or <All Columns>) you would like to re
strict.
Enter the restriction Type
No Access
Read Only
CONNX Data Dictionary
3
-
30
Write Only
Full Access (This is not normally required).
4.
To modify an existing security restriction, simply use the mouse or
keyboard to change the desired field.
5.
To delete an existing security re
striction, click on the line you wish to
delete, then press “Delete Restriction”.
6.
To set the default security for all users, use the special username
“EVERYONE”. This does not override any existing security.
Database Account Management
When using CONNX to access multiple databases, a separate logon is
required for each database. To make this multi
-
logon process simple,
CONNX has an integrated logon model, that only requires the user to know
one userid and pas
sword, and CONNX will store the logon information for all
of the databases being accessed. This database logon information can be
configured in the “Database Account Management” screen. This information
does not need to be entered manually. When a perso
n first uses CONNX, the
database logon information is requested, and then stored in the CONNX data
dictionary for future use. If a user’s password changes, upon logon, CONNX
will prompt for the new password, and store the updated information in the
CONNX
data dictionary. The CONNX Data dictionary is encrypted with a
strong 64bit key to ensure this vital logon information is protected.
CONNX Data Dictionary
3
-
31
Application Management
The CONNX application management feature is designed
for companies that
have several different database types being accessed through CONNX.
By defining a CONNX application name, a user can determine which
databases are required by the application, and CONNX will only connect to
the databases specified,
instead of connecting to all of the databases in the
CONNX data dictionary. The application is an arbitrary name that can be
specified during a CONNX logon.
CONNX Data Dictionary
3
-
32
Maximum Security
By enabling the maximum securit
y option, CONNX will deny all logon attempts
if the user is not defined in the CONNX Data Dictionary. Even if the user
specifies a valid database userid and password, CONNX will not proceed if
they do not have a user entry in the CDD. This option allows
database
administrators to determine which users are allowed to access the database
through the CONNX product.
CONNX ODBC Driver
4
-
1
4
-
CONNX ODBC Driver
The CONNX ODBC Driver is exciting technology which offers our CONNX
users a way of easily getting access
to data.
The CONNX ODBC Driver, coupled with the CONNX Data Dictionary,
provides a means of using many popular off
-
the
-
shelf query tools and
applications development tools. Because our driver works with most any
ODBC
-
compliant software, companies have th
e flexibility to choose their front
-
end tools. Microsoft Visual Basic and Microsoft Access being among the most
popular.
The CONNX software is designed to let you take advantage of the available
PC software while utilizing the information stored in other
formats.
The Open Database Connectivity interface allows applications to access data
in database management systems using Structured Query Language (SQL)
as a standard. (SQL is a widely accepted industry standard for defining data,
manipulating data, da
ta management, access protection and transaction
control. SQL uses tables, indexes, keys, rows, and columns to define storage
locations.)
Using ODBC, application developers can develop, compile, and ship an
application without targeting a specific DBMS.
The users then can add
modules called database drivers that link the application to their choice of
backend databases.
ODBC is a standard that has been espoused by a significant number of
software vendors, Microsoft being one of the major players.
CONNX ODBC Driver
4
-
2
ODBC
Architecture
ODBC architecture has four components:
Application:
Performs processing and calls ODBC
functions to submit SQL statements and retrieve results.
Driver Manager:
Loads drivers when requested by an
application.
Driver:
Software that processes the ODBC function
calls, submits the SQL requests to a specific data source, and
returns results to the application. If needed, the driver changes
an application’s request so that it conforms to the syntax
supported by
the associated database.
Data Source:
The data the user wants to access and its
associated operating system, DBMS, and network platform (if
any) used to access the DBMS.
CONNX ODBC Conformance
CONNX is an ODBC 2.5 d
river. The CONNX driver supports all core, level 1,
and level 2 functions, including
transaction
functions, such as commit and
rollback. See the section “ODBC SQL Grammer” for further details on ODBC
SQL Grammer support.
CONNX is als
o compatible with ODBC 3.x
applications, and OLEDB/ADO 2.x applications.
CONNX ODBC Driver
4
-
3
Configuring the Data Source
Before an application program can communicate with the data source, you are
required to provide configurati
on information. The configuration information
tells the ODBC driver which information you want to access.
The data source is comprised of the data you want to access, its associated
operating system, database management system (or file system), and netwo
rk
platform used to access the database management system. To configure your
ODBC Driver, use the ODBC icon located in Windows Control Panel. Follow
these steps:
1.
CONNX/16 Only
-
Double click on the ODBC icon. This icon
can be found in
Control Panel
.
CONNX/32 Only
-
Double click on the ODBC32 icon. This icon
can be found in
Control Panel.
2.
A list of the ODBC data sources is displayed. Press the “Add”
button to create a new data source.
CONNX ODBC Driver
4
-
4
16
-
bit
32
-
bi
t
Note: The 32
-
bit ODBC Administrator show several different categories of
data sources: User DSN’s, System DSN’s and File DSN’s. User DSN’s will
only be available for the user currently logged onto the computer. System
DSN’s will be available to all u
sers of this computer. File DSN’s can be
available to all users on a network.
3.
A list of installed drivers is displayed. The ODBC driver is listed
as CONNX or CONNX32.
CONNX ODBC Driver
4
-
5
4.
Click on CONNX or CONNX32 in the list of installed drivers.
The ODBC dialog box is
displayed.
5.
Click on Add New Name. The ODBC Driver Setup dialog box is
displayed. You can provide the other information requested in
the dialog box, or you can leave the fields blank and provide the
information when you run the application.
CONNX ODBC Driver
4
-
6
The Dat
a Source Name is the name used to identify the data source to
ODBC. This generally equates to a database.
The Description field lets you comment about the data source.
(Mandatory)
The Data Dictionary is the full pathname (location) of the data
diction
ary definition you saved through the CONNX Data
Dictionary. (Browse invokes the file search dialogue box if you
can't remember where the file is located). (Mandatory)
Application Name
–
if using CONNX to access multiple
databases, a CONNX application can
be defined to specify
which databases are needed for this data source.
After you have entered the data, click on OK or press Return. The driver
writes these values to the ODBC.INI file and these are now the defaults when
you connect to the data source.
Configuring the data source again can
change these defaults.
After exiting Control Panel, you may want to try a sample ODBC
application that was shipped with this software or use other query tools
to ensure the driver is functioning successfully
.
Note:
To configure an Existing Data Source, do the following:
1.
Double
-
click the ODBC icon in the Windows Control Panel. A
list of the data sources appears.
2.
Select the data source you wish to configure and click the
SETUP button. The Setup dialog box appears.
3.
Respond to the questions as described above in Configuring
the Data Source.
CONNX ODBC Driver
4
-
7
After the data source has been configured, CONNX is now ready for use by
your front end application (Access, Excel, Crystal Reports, etc). Typically, you
must link to this dat
a source using the application. This process is different for
every application. For example, if using Microsoft Access 97, you would:
1)
Create a new database.
2)
Select “Get External Data” from the file menu
3)
Select “Link Tables”
4)
Select “ODBC” from the “File
s of type” list at the bottom left of the dialog
5)
Select the CONNX data source you have created
6)
Log into CONNX
7)
Select the list of tables you wish to use in Microsoft Access
Please consult the user guide of your front end application to learn how to
access
ODBC data sources.
CONNX ODBC Driver
4
-
8
CONNX INI File Settings
The CONNX.INI file, which is located in the WINDOWS directory, can be
modified to contain special settings.
CONNX Performance
To turn off
connection sharing
,
set the following entry
[CONNX]
SHARECONNECTIONS=0
B
y default, connection sharing is enabled. This means that if more than one
connection is made on the same PC, using the same userid and password,
CONNX will make a single physical connection to the VMS server, instead of
creating multiple connections. By
disabling this option, each connection from
a single PC gets a dedicated socket to a VMS server, but more resources are
consumed on the VMS Server.
To configure the CONNX
join count,
which determines the number of keys to
use in a single pass when perfor
ming joins, set the following entry.
[CONNX]
JOINCOUNT=250
The default and maximum value is 250.
For users of RDB 6.x, we
recommend changing this setting to 8.
CONNX Debugging
To turn on
TCPIP Debug Messages
, set the following entry
[CONNX]
TCPIPDEBUG=1
To turn on
DECNet Debug Messages
, set the following entry
[CONNX]
DECNetDEBUG=1
We recommend that these settings remain off, unless debugging an issue
with a CONNX Technical Support Representative.
CONNX ODBC Driver
4
-
9
CONNX TCPIP Settings
To change the
TCPIP Connection p
ort
CONNX uses to communicate with
the CONNX Server, set the following entry
[CONNX]
CONNECTPORT=XXXX
Where XXXX = the TCPIP Port number that the CONNX Server will use the
communicate to the PC. The default value is 6500. We do not recommend
changing thi
s value.
To set the
connection timeout
, set the following entry in the CONNX.INI file
[CONNX]
CONNECTTIMEOUT=value
where “value” = the number of milliseconds to wait for a connection from the
VMS server. The default value is 90000 (90 seconds)
To set t
he
heartbeat timeout
value, which is the number of milliseconds that
CONNX will wait for a response from the VMS Server. The default value is
3600000 (1 hour).
[CONNX]
TIMEOUT=value
To turn on
CONNX Data Compression
, set the following entry in the
CONNX.
INI file
[CONNX]
COMPRESS=1
CONNX Data Compression uses a high speed compression algorithm to
remove repeating characters from the data stream. This option is
recommended when using CONNX over slow transmission lines.
CONNX DataFlex Settings
To select
2 digit DataFlex years
for DataFlex Dates, set the following entry in
the CONNX.INI file
[DATAFLEX]
CONNX ODBC Driver
4
-
10
2DigitYears=1
This option will configure CONNX to interpret DataFlex dates as having a 2
digit years, with the century implied as 1900.
To select
4
digit D
ataFlex years
for DataFlex Dates, set the following entry in
the CONNX.INI file
[DATAFLEX]
2DigitYears=0
This option will configure CONNX to interpret DataFlex dates as having a 4
digit years.
CONNX Year 2000 Settings
Configurable
Sliding Date Window
for
Year 2000 Dates
This is intended as a user configurable option to help convert non Y2K legacy
date data. It is not a fix for any deficiency in CONNX.
CONNX has several Y2K data types for date formats that only s
tore 2 digits of
the year. The problem with accessing dates that contain 2 digit years is
knowing what century the year is in.
Ex: 03/05/10
–
is this March 5
th
1910, or March 5
th
2010
CONNX has a class of Y2K date data types to handle this issue. CONN
X
Y2K dates use a “date window” technology that will place years greater than
40 in the current century.
Ex. 3/5/41 becomes 3/5/1944, 3/5/39 becomes 3/5/2039.
As a service to our users, this new option provides a method to configure the
“breakpoint year
”
and override the default value of 40.
This feature allows you to select a breakpoint year. Any year equal to or less
than the breakpoint year will be incremented to the next century. Any year
greater than the break point ye
ar input will be made this century.
Ex. Yearwindow
= 40
CONNX ODBC Driver
4
-
11
12/03/25 becomes 12/03/2025
03/01/40 becomes 03/01/2040
05/25/55 becomes 05/25/1955
The Configurable sliding date is set in the CONNX.ini file, on each individual
user PC. I
n the CONNX section, input your selected break point year. (See
example below.) The default setting for this parameter is set to 40. In order
to make the breakpoint year anything else, you must edit the ini file, and input
the yearwindow statement as be
low:
[CONNX]
YEARWINDOW=40
The following CONNX data types
will be affected.
CONNX Data
Type
SQL Data
Type
Length
Description
Text Date 2000
(YYMMDD)
DATE
6
Text Date in the specified format. This format
will handle years betw
een 1941 and 2040.
Text Date 2000
(MMDDYY)
DATE
6
Text Date in the specified format. This format
will handle years between 1941 and 2040.
Text Date 2000
(DDMMYY)
DATE
6
Text Date in the specified format. This format
will handle years between 1941 and 2
040.
Long Date
2000
(YYMMDD)
DATE
6
LongWord Date in the specified format. This
format will handle years between 1941 and
2040.
Long Date
2000
(MMDDYY)
DATE
6
LongWord Date in the specified format. This
format will handle years between 1941 and
2040.
Long Date
2000
(DDMMYY)
DATE
6
LongWord Date in the specified format. This
format will handle years between 1941 and
2040.
Packed Date
2000
(YYMMDD)
DATE
4
Packed Decimal Date in the specified format.
This format will handle years between 1941 and
2040
.
Packed Date
2000
(MMDDYY)
DATE
4
Packed Decimal Date in the specified format.
This format will handle years between 1941 and
2040.
Packed Date
2000
(DDMMYY)
DATE
4
Packed Decimal Date in the specified format.
This format will handle years between 194
1 and
2040.
CONNX ODBC Driver
4
-
12
ODBC SQL Grammar
Primary SQL Commands
SELECT
[ALL | DISTINCT] select
-
list, MIN, MAX, AVG, SUM, COUNT
FROM table
-
reference
-
list
[WHERE search
-
condition] [Outer Joins] [I
nner Joins]
[GROUP BY column
-
name [, column
-
name]...]
[HAVING search
-
condition]
[order
-
by
-
clause]
Examples:
Simple Select:
Select customername, customerid from customers_rms
Where customerid = “ALCAO”
Inner Join Example:
Select cust
omername, orderid
From customers_rms c, orders_rms o
Where c.customerid = o.customerid
Outer Join Example:
Select customername, orderid
From customers_rms c, orders_rms o
Where c.customerid *=o.customerid
Group by Example:
Select customername, sum
(o.productquantity * p.productprice)
From customers_rms c, orders_rms o, products_rms p
Where c.customerid = o.customerid
And o.productid = p.productid
Group by c.customername
CONNX ODBC Driver
4
-
13
DELETE
[*] FROM table
-
name
[WHERE search
-
con
dition]
Example: delete from customers_rms where customerid = “ALCAO”
INSERT
INTO table
-
name [( column
-
identifier [, column
-
identifier]...)]
VALUES (insert
-
value[, insert
-
value]... )
Example: insert into customers_rm
s (customername, customerid)
values (“New Customer”, “NEWID”)
UPDATE
table
-
name
SET column
-
identifier = {expression | NULL }
[, column
-
identifier = {expression | NULL}]...
[WHERE search
-
condi
tion]
Example: update customers_rms set customername = “After Merger”
Where customerid = “ALCAO”
CREATE TABLE
table
-
name
(column1 datatype[,column2 datatype]…)
Note:
not currently supported by RDB & DBMS
Example: cr
eate table test_rms(companyname char(20),
Companyid integer, companybudget double)
DROP TABLE
table
-
name
Note:
not currently supported by RDB & DBMS
Example: drop table test_rms
Note: Drop table does not physically de
lete the RMS or DataFlex file. Only
the CONNX CDD entry is deleted.
CONNX ODBC Driver
4
-
14
CREATE [UNIQUE] INDEX
index
-
name on table
-
name
(column1 [ASC][DESC][, column2[ASC][DESC]] …)
Note:
not supported by RDB & DBMS
Example: create index
test_index on test_rms(Companyname DESC,
companyid ASC)
DROP INDEX
[CONNX table
-
name]_INDEX_[CONNX index
-
number]
Note:
not supported by RDB & DBMS
Example: drop index customers_rms_INDEX_1
CONNX ODBC Driver
4
-
15
SQL Time & Date Literals
{t “HH:MM:SS”}
This is the proper format to specify a literal time value.
Example: Select {t “21:02:33”} from testtable
{ts “YYYY
-
MM
-
DD HH:MM:SS.FF”}
This is the proper format to specify a literal timestamp valu
e.
Example: Select {ts “1999
-
02
-
03 21:02:33.98”} from testtable
{d “YYYY
-
MM
-
DD”}
This is the proper format to specify a literal date value.
Example: Select {d “1999
-
02
-
03”} from testtable
CONNX ODBC Driver
4
-
16
SQL String Functions
CONCAT
(string_exp1, string_exp2)
Returns a character string that is the result of concatenating string_exp2 to
string_exp1.
SUBSTRING
(string_exp, start, length)
Returns a character string that is derived from string_exp beginnin
g at the
character position specified by start for length characters.
Example:
Select Substring(“ABCDE”, 2, 1)
Results:
“B”
Example:
Select Substring(“ABCDE”, 4, 2)
Results:
“DE”
CONNX ODBC Driver
4
-
17
SQL Date Functions
- Αρχική
- Περιήγηση
- Όλες οι Κατηγορίες
- Internet και Εφαρμογές Web
- Αποθήκευση
- Ασφάλεια
- Βιοτεχνολογία
- Δίκτυα και Επικοινωνίες
- Διακομιστές
- Διαχείριση
- Διαχείριση Δεδομένων
- Επιχειρησιακές Εφαρμογές
- Ηλεκτρονική - Συσκευές
- Ημιαγωγοί
- Κινητά – Ασύρματες Τεχνολογίες
- Λογισμικό & κατασκευή λογ/κού
- Μηχανική
- Περιβαλλοντική Τεχνολογία
- Πετρελαϊκά και Εξόρυξη
- Πολεοδομικά Έργα
- Τεχνίτη Νοημοσύνη και Ρομποτική
- Τηλεπικοινωνίες
- Ψυχαγωγία
- Κοινότητα
- Μεταφόρτωση
Σχόλια 0
Συνδεθείτε για να κοινοποιήσετε σχόλιο