News & Information from your source for Simplified Data Access. Volume 15 Winter CONNX 8.2 expands database support

(1)

News & Information from your source for Simplified Data Access. Volume 15 • Winter 1999-2000

INSIDE

The Front

End

CONNX Goes

to College

Tech Talk

continued on page 7

CONNX 8.2 expands database support

O

n March 7, 2000, SolutionsIQ’s Software Products Division (SPD) announced the release of CONNX 8.2, marking a major milestone in the company’s quest for “true” universal data access. This latest release of CONNX included support for IBM’s DB2 database as well as an OLE DB adapter module for SQL-based ODBC and OLE DB data sources.

Supported platforms for DB2

include OS/400, OS/390, OS/2 Warp, MVS, Linux, and Win-dows NT. Connectivity to DB2 will be available through TCP/ IP or APPC/SNA LU 6.2. The DB2 CONNX module brings exciting new enhancements such as dynamic SQL learning capability, static SQL support, OS/400 remote commands, remote and distributed

It’s a “Thin” Java World

R

ecognizing that Java is a key strategic platform for many applications, SolutionsIQ has added a Type 3 JDBC driver to access the CONNX engine. A Type 3 driver is distin-guished by two main components: • A thin JDBC client that

communi-cates with a server component. The JDBC calls are translated to an RPC (Remote Procedure Call) that communicates via TCP/IP to the server component.

• A Server component that handles the Java requests and makes calls to the database engine (CONNX). The JDBC client is referred to as “thin” because the Java driver is mainly a dispatcher. The JDBC server hosted on a Windows

ma-occurs, as it is responsible for communicating with the CONNX engine. Because Java can run on any platform, CONNX’s JDBC client is portable across many different operating systems, including Linux, Macintosh, Unix, VMS, and Win-dows. It is compatible with Web servers such as IIS and Apache. The only required component is a JVM (Java Virtual Machine). JVM’s are freely available at

http://www.javasoft.com.

CONNX JDBC has many advan-tages including:

• Universal application develop-ment. This means that a Java program can be developed to run on any machine.

• Thin clients mean that database-specific libraries/.dlls do not have

• Internet Applications. Both applets and servlets can use CONNX JDBC.

(2)

by Doug Wright

Director’s Dialogue

SolutionsIQ

Software Products Division 1800-112th_{Ave. NE, Suite 150}

Bellevue, WA 98004 or

email: [email protected]

Phone: 425-519-6600 or

Toll Free: 888-88CONNX

FAX: 425-453-8871

CONNX

Customer Service Ext. 256 Sales

Reseller Ext. 267

Northeast Ext. 1809

Southeast Ext. 2023

Central US Ext. 269

West Coast Ext. 270

International Ext. 271

Sales Administration Ext. 272

CONNX Files Ext. 161

The

Doug’s view on industry directions and impact.

Business-to-Business, EDI,

e-Commerce: what’s in a term?

I

remember reading my first article about XML and how (implied overnight) it was going to instantly transform the industry. Also, when I first read about Java, the same thing. In that case I was on an airplane and the article led me to believe that by the time I landed, the IT world would be different. How could something I’d never heard of make such rapid and sweeping changes.

Well, they can’t. Otherwise, I suspect, IBM’s BAL would not still be used, nor for that matter COBOL, VSAM, RMS, etc. How is a person then supposed to understand the ramifications of these terms that show up in articles and apply them to their own organizations in order to make sure their companies data processing can keep up with all the new technology.

The smart companies have de-cided to build underlying open architecture blocks between the pieces that change rapidly and those brick and mortar pieces. What are the pieces? Visual or UI pieces (screens), business rules (how does my company run its business?), data access, retrieval (data stored on disks), output (reports, queries, forms, files). To avoid the big bang approach it’s better to attack these pieces independently and map out the path a little at a time. Some-times it’s impossible to approach

moving in this direction when source code, software tools, and help are not available. Even after getting your software Y2K-compli-ant by implementing a new soft-ware application package, if it then does not support open standards, you’ll very soon be right back behind the technology eight ball in this fast-paced industry.

Why? Although terms such as “e-commerce,” when applied to business-to-business purposes, reference 20-year-old terms such as EDI, the actual results of such tech-nology may have a widely different effect in today’s world of PCs and Internet connections. If you believe the reality of what the new termi-nology represents will not impact your company, either positively or negatively, because of how you adopt the new technology, I would suggest that you start to study such terms and their implications.

If your software cannot quickly support new technology, you should examine the architecture, standards, and software tools that will allow your company to remain competi-tive throughout the next decade. Terms such as these have been around for a while, but it is time to put them to good use. We don’t have years to figure out the best way for them to work for us, but we must act now in order to gain their full benefit.

(3)

TECH TALK

By Larry McGhaw

Larry explains new SQL functionality.

New extended SQL function

added to CONNX

®

_{for SCT Sites}

T

he new version of CONNX®

8.2 has a new SQL function called CNXPreference, which can be used to select addresses for students. The AAFILE contains up to eight different addresses for a given student. Each address is flagged with special address type codes.

For example, the code ¡P means that this is a permanent address. The code L means that this is a local address, etc. When creating mailing lists for SCT sites, it is desirable to use one particular address type if a student has multiple addresses. Let’s walk through an example:

A given student has 3 addresses 1 Local Address

1 Permanent Address 1 Billing Address

Another student has 2 addresses 1 Billing address

1 Local Address

A 3rd_{student has 1 address}

1 Billing Address

For this particular mailing, we want to use a student’s permanent address. However, if the student does not have a permanent

ad-dress, we want to use their local address. Additionally, if the student does not have a local address, we want to use their billing address.

This type of query can be accom-plished with the new

CNXPreference function. The CNXPreference function accepts the following parameters

1) The number of different items in the search criteria.

In our example, this is 3. (Perma-nent, local and billing)

2) Specify each search criteria using the LIKE clause wildcard syntax.

In our example, permanent = “%P%”, Local = “%L%”, and Billing = “%B%”

3) List pairs of comparison and result fields.

In our example, the comparison field is the AD_TYPES_#####, which contains the address type. This field is compared to the search criteria specified in step #2 using a SQL like comparison. The result field is the data element that will be returned if the comparison is successful (AD_ADDRESS_#####)

The address information can be selected by using the following

SQL statement: SELECT ADFILE.AD_REC_KEY, ADFILE.AD_SID, CNXPreference (3, “%P%”, “%L%”, “%B%”, AD_TYPES_00001,AD_ADDRESS_00001, AD_TYPES_00002,AD_ADDRESS_00002, AD_TYPES_00003,AD_ADDRESS_00003, AD_TYPES_00004,AD_ADDRESS_00004, AD_TYPES_00005,AD_ADDRESS_00005, AD_TYPES_00006,AD_ADDRESS_00006, AD_TYPES_00007,AD_ADDRESS_00007, AD_TYPES_00008,AD_ADDRESS_00008 ) as ADDRESS FROM ADFILE

Using our example data, this query would return

1) The permanent address of student #1

2) The local address of student #2

3) And the billing address of student #3

The CNXPREFERENCE function gives you the power to write complex queries, with minimal work. This function is not only useful for SCT sites, but for anyone who has a similar need of selecting an item from an array, based on a series of comparisons.

(4)

.

The Front End

By Mary Charvat

The Front End

By Mary Charvat

Using CONNX

®

_{with Microsoft}

®

Internet Information Server

(IIS) to Web-Enable Your Data

W

anting to improve communications with vendors, customers, prospects, and employees? Dream-ing of quick-start solutions that allow students to confirm schedules before the semester starts? Hoping to be able to let employees input hours and check and change benefits in real time — in Paris?

If you answered “yes” to any of these questions, it’s clear you need to start putting CONNX®_{to work –}

online. Simply use CONNX®_with

Active Server Pages and then post them to the Web with IIS.

First you need to create a System DSN that allows you to access your previously prepared CONNX CDD tables, views, or stored procedures. Then you need to make sure that the valid license files for the data-bases you use (Oracle, DB2,

DataFlex, OLE DB- or ODBC-compli-ant providers, etc.) are on the machine on which IIS is installed. (This step is not necessary for VMS databases.)

Decide on a method for displaying your data, and then create the necessary code. You can work with .asp files in Microsoft Access or

Visual InterDev, .html files in Excel, or use Visual Basic script.

Open the files in IIS, save them in wwwroot (it saves the files to the local host automatically), then run with them in your browser of choice or attach them to the corporate intranet or Internet web site.

Important note: You must beware of security issues, as you always would with any “web-lification.” Security for all your web pages must be set up in IIS before you post your pages since CONNX-based data does not have the same login screen when viewed in IIS. It is vital you remember to use IIS security to restrict access, otherwise your internal affairs are fair game!

The following is a sample CONNX Active Server Page to open and test in IIS:

<!— #INCLUDE FILE=”ADOVBS.INC” —>

<title>CONNX ASP Test Page</title> </head>

<body bgcolor=#FFFFFF topmargin=10 leftmargin=15

marginwidth=15 marginheight=10> <%

‘ This file is a sample program to demon-strate the usage of CONNX in an asp

‘ Following is a query to be run against the CUSTOMERS sample table. The table name may have to be modified to match your sample

sqlQuery= “SELECT * FROM CUSTOM-ERS WHERE customerid LIKE ‘a%’” Response.Write “ Opening

connec-tion ... “ & Time()

‘ create the connection object for ADO Set

Conn=Server.CreateObject(“ADODB.Connection”) ‘ Open the connection.

‘ you will have to fill in the needed information

Conn.Open “Your DNS Name”,”Your CDD UserName”,”Your CDD Password” Response.Write “ Done.” & Time() Response.Write “ Geting recordset

...” & Time() ‘ Run the query

Set RS=Conn.Execute(sqlQuery) Response.Write “ Done. “ & Time() ‘ Display the query and the number of

records returned RecCount=RS.RecordCount

Response.Write “ SQL Query =” & sqlQuery

Response.Write “ Recordset RecordCount =” & RecCount ‘ display the information %>

Mary discusses using CONNX with IIS.

(5)

Quick Tips

By Josh Martin

Josh Martin discusses DSNs.

CONNX Frequently Asked

Questions

I

n this edition of Quick Tips, I’d like to go over some frequently asked technical support questions.

Question One: Why do I have a Data Source Name (DSN) called CONNX32? Isn’t that the name of the ODBC driver I am supposed to use? What happens if I use the CONNX32 DSN to connect to my CDD?

The CONNX ODBC driver was developed to the ODBC version 2.X specifications, which needed to support the File DSN feature. We created a work-around that routed the File DSN through the CONNX32 DSN. With the new ODBC 3.X specifications, this routing became unnecessary.

Display the records found. <table bgcolor=#000000 border=0

cellspacing=1 cellpadding=2> <tr bgcolor=#BBBBBB>

<% For i=0 to RS.Fields.Count-1 %> <td><%=RS(i).Name %></

td> <% Next %> </tr>

<% Do While Not RS.EOF %>

<tr> <% For i = 0 to RS.Fields.Count-1 Response.Write (“<td bgcolor=#DDDDDD>”) Response.Write RS(i) Next %> </tr> <% RS.MoveNext Loop %> </table> <%

‘ Close the connection and destroy the object RS.Close Set RS=nothing Conn.Close %> </body> </html>

continued from page 4

Front End

The second part

of the answer is ‘Yes, CONNX32 is the name of the driver that you are supposed to use when creating your DSN.’ With a DSN named CONNX32 and the driver named CONNX32, it is easy to see how this leads to problems. We are working on a way to remove this for our 8.2 release.

The last part of the answer is the most important. If you use the CONNX32 DSN in Microsoft Ac-cess, it will prompt you for your CDD, since there is none specified. Then, it will ask you for your logon information. Just when you think all is well, you get an error message (see screen shot).

This can all be avoided by simply

not using the CONNX32 DSN. If you are not planning on using File DSNs, you can delete the

CONNX32 DSN from the User DSN menu.

Question Two: How can I avoid getting the ‘Remote Query

Timeout’ error in Microsoft Access?

When you are in the query design mode, click on the ‘Proper-ties’ button (the one with the pointing hand on the Query Toolbar). Change the ‘ODBC Timeout’ property from 60 (the default) to 0. This will enable your query to wait indefinitely for data to be returned from the server, instead of only 60 seconds.

(6)

Appalachian State University Case Study:

Technology Improvements Earn High Marks

University enhances Web and

reporting capabilities with CONNX

®

from SolutionsIQ

• Web-enabled applications using CONNX®_give

students, faculty, administrators, and alumni at Appalachian State University (ASU) easier access to information.

• CONNX’s ease of use empowers staff and frees IT programmers to develop new applications. • ASU’s administrative departments use CONNX for

timely ad hoc reporting without IT intervention.

A

t Appalachian State Univer-sity (ASU), many Web and PC application development and reporting tasks will be made easier by CONNX, a data access engine developed by SolutionsIQ. CONNX provides easy access to RMS files residing on the school’s Alpha cluster through popular PC front ends such as MS Access, MS Word, and Brio. SolutionsIQ modi-fied CONNX to handle the data format requirements of a new version of Systems and Computer Technology (SCT), an application widely used by colleges and univer-sities, including ASU.

For the Web applications, CONNX provides a real-time Web connec-tion to RMS data. “We built an e-mail directory application for the Alumni office using Active Server Pages,” says Steve Hopper, Associ-ate Director of Administrative Applications. “We use CONNX to validate in our legacy system that the person submitting the

informa-tion for the directory is a graduate.” A new browser-based application for the Human Resource depart-ment gives employees the informa-tion from their employee record and frees the department from fielding numerous questions daily. Also, members of the university commu-nity will be able to use the Web to identify and validate parking lot options, then pay their fees elec-tronically using a credit card.

CONNX has also improved the school’s ability to produce ad hoc reports. Individual administrative departments have gained autonomy for their reporting activities thanks to CONNX’s ease of use. And employees can get their reports much faster because they can create the reports themselves. Although users never have to learn the technical features of CONNX, Amy Winebarger, ASU’s Applications Programmer/Analyst, and Hopper agree that a key techni-cal benefit is the way the solution

breaks very large files into a (logical) flat-file or relational presentation, which simplifies legacy data access and use. Programmers at the school also appreciate CONNX’s ease of use for their programming pur-poses.

An on-site visit by two SolutionsIQ staff members helped ASU enhance performance of its back-end system and pave the way for easily getting updated versions of the product out to users. Winebarger feels that, “Their time here reinforced that SolutionsIQ is very customer-oriented and wants to provide a high level of support.”

Appalachian State University, founded in 1899 as Watauga Academy, has 12,000 students taught by over 500 faculty members and is the sixth largest university in the University of North Carolina system. To view the case study in its entirety, check our Web site at www.SolutionsIQ.com/products/ casestudies/html.

(7)

Channel Surfing

continued from page 1

transactions, ODBC Level II catalog functions and sticky and scrollable cursors. These features, among others, promise to provide unpar-alleled performance when access-ing DB2 data sources.

According to Research and Development Manager, Larry McGhaw, “The new OLE DB Adapter module technology will allow CONNX users to integrate those individual data sources for which a CONNX module does not exist, but for which they already have a driver. By integrating these additional data sources into the CONNX Data Dictionary, users will

finally be able to treat all of their data objects, regardless of origin, as if they existed in one enterprise-spanning relational database.”

“CONNX 8.2 launch and roll-out will include product ads in key publications, press releases, direct mail, trade show participation, eblasts and web site updates,” explained Product Manager Jon-Jacques Umphrey. “Our sales team will also be able to demonstrate the product without leaving their offices, live from their utilizing Net-Meeting and with a newly created screen-cam demo that allows users to view a pre-selected demo

through our corporate Web site. “Hot on the heels of CONNX 8.2 are CONNX modules for Sybase, SQL Server, Informix, CISAM and VSAM data sources. CONNX will also be supporting JDBC access to CONNX data sources in an upcom-ing release. This will allow users to access CONNX-enabled data sources from any platform that supports Java,” continued Umphrey. “We are no longer addressing a VMS niche market alone but the entire IT market, expanding our potential customer base and significantly increasing our revenue potential.”

CONNX

®

_{Version 8.2 allows}

partners to support any

datasource

W

ith the release of CONNX Version 8.2 scheduled for March 7, 2000, CONNX will

be able to support all data sources within an enter-prise. For our partners, this means they are able to deal with one vendor, rather than multiple vendors, when it comes to supplying data access to the end-user software products they sell

to companies. Our SCT partnership is a prime example of how this will help our two companies work

closer and better to-gether. SCT has multiple software packages that use Digital RMS, Oracle, IBM VSAM, and IBM DB2. CONNX, with its release 8.2, supports all of these data sources, and

mon integration software applica-tions amongst all of their

different database plat-forms.

Also, software manu-facturing partners (like SCT) who have com-mon software applica-tions but use different databases in order to sell into those market places, can now take full advantage of CONNX’s real power. They will now be able to write their next-generation software using CONNX as their database layer, thus allowing CONNX to access any and all physical database layers. This way they will only have to deal with one, instead of many, interface platforms.

SolutionsIQ will be focusing on building our Partner Channel pro-gram more this coming year. SolutionsIQ supports our channel partners by enabling them to pro-vide a more streamlined, functional product to their customers through CONNX.

CONNX 8.2

Doug Wright (left) and Jon-Jacques Umphrey (right) focus on building the CONNX Core Partners program.

(8)

SEATTLE, W A

PERMIT NO. 1578

The

OLE DB/ODBC compliant

T

hat’s right, CONNX 8.2, the newest release of

SolutionsIQ’s simplified data access software includes support for DB2 and OLE DB compliant data sources. See inside for additional information on this, as well as other exciting new features in CONNX 8.2.

If you have DB2 or OLE DB

data in your organization and want to be able to perform seamless joins between

it and your other CONNX data sources, give your CONNX sales representative

a call right away. For a very limited time, current CONNX

customers can order these new CONNX modules, including support for Oracle, for the discount rate reserved normally for multi-platform licenses. Call today for details.