User Reference Guide Version 7.1

laurelsandwichΛογισμικό & κατασκευή λογ/κού

25 Νοε 2013 (πριν από 3 χρόνια και 10 μήνες)

259 εμφανίσεις











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