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

-----Original Message-----
From: Goodbar, Loyd (AFS-Water Valley) [mailto:LGoodbar@afs.bwauto.com]
Sent: Thursday, June 07, 2001 3:39 PM
To: 'art@link400.com'
Subject: RE: How to insert/update records using SQL?


Just a personal question, how are you connecting from the AS/400 to SQL
server? I would like to do the same thing (read/update data on SQL server
from the AS/400).

I assume you have the SQL dev kit for embedded SQL in RPG?
If you have set up a cursor and are fetching one row at a time, you can do a

UPDATE table SET column = value, column = value WHERE CURRENT OF cursor_name

Inserting is

INSERT INTO table (column, column, column) = value, value, value

HTH,
Loyd

-----Original Message-----
From: Art Tostaine, Jr. [mailto:art@link400.com]
Sent: Thursday, June 07, 2001 8:55 AM
To: midrange-l@midrange.com
Subject: How to insert/update records using SQL?


I have to update a table on SQL/Server from the Iseries.  I have the
connectivity issues resolved.

Using a sql statement, how do INSERT or UPDATE the records that already
exist.

In RPG, I would CHAIN, and either WRITE or UPDATE if %FOUND.  Does a similar
function exist in SQL?

What is the standard or preferred method of doing a mass update?

My SQL table is basically,

CustId
OpenAr
Open30
Open60
Open90
Open120

Thanks.

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
+---