RE: How I connected my ISeries to a SQL/Server

That's really slick! I'll definitely need to explore that option. Some
issues I need to overcome on the SQL server 1) needs IIS, 2) may have
database security. A great alternative, though - thanks for sharing the
process!

Loyd

-----Original Message-----
From: Art Tostaine, Jr. [mailto:art@link400.com]
Sent: Friday, June 08, 2001 8:31 AM
To: Goodbar, Loyd (AFS-Water Valley)
Cc: midrange-l@midrange.com
Subject: How I connected my ISeries to a SQL/Server


Loyd:

I thought my statement, 'we've got the connectivity issues all worked out'
might cause some questions, mostly because it's been talked about recently.

What we did is not conventional, but it was easy and it works.

The customer is developing a web site using IIS and SQL server.  The ISeries
has to talk to SQL server.  We couldn't do it with DRDA.  Someone on the web
development team asked if I could send a URL to the web server and receive
the page back.  I said I could, thanks to Brad Stone's GETURI program.

They wrote an ASP (active server page?), in about 3 hours, that takes a SQL
Statement embedded in a URL, and returns the results.

So, to get column ID from table dpobjects, using the value in &PRO & &BLDR,
I do these commands:

CHGVAR     VAR(&URL) +
             VALUE('http://192.1.1.105/docudb/as400sqlex+
             ec.asp?sql=select+id+from+dpobjects+where+t+
             itle+=+' *CAT &APOST *CAT &PRO *TCAT +
             &APOST *CAT '+and+field1+=+' *CAT &APOST +
             *CAT &BLDR *CAT &APOST)

GETURI     URI(&URL) OUTPUT(*FILE) FILE(QTEMP/WEB)

The GETURI puts the result of the URL in QTEMP/WEB:

HTTP/1.1 200 OKServer: Microsoft-IIS/4.0Date: Thu, 07 Jun 2001 03:05:32
GMT
id¦4728145¦14761¦4734178¦

It's a strange way to do things, and it adds an extra layer of failure, but
we couldn't find anyone that could understand how to get the DRDA working on
the SQL server.  The last guy we asked we thought was very knowledgable with
SQL server and he never heard of DRDA.  We have been talking for 6 months
about how we would do this, but could never get it done.  It's done now, and
it only took total about 8 hours of programming. (4 for them, 4 for me to
extract the data from the URL returned)

I've been considering asking the guys who wrote the ASP to give it to me to
put on a freeware site, but I'm not sure it would be useful for many AS/400
shops, since IIS is required.  I figure that it wasn't that difficult or be
that valuable to them since it only took 4 hours.

HTH

Art Tostaine, Jr.
CCA, Inc.
Jackson, NJ 08527

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---