Re: RPG - MsSQL Connection

Thanks, Dieter

That's cool that you'd done this as open source - I like the basic concept. A couple of us here have looked at writing an open source SQL Server ARD - the specs are all out there for DRDA, as well as SQL Server, as well as the ARD stuff. We haven't done it - mostly because it would conflict with our product too much, partly because of time working on other projects.

RPG2SQL uses a similar concept to this extent - allowing connections to any remote database that has a suitable driver or provider in Windows. It does use function calls instead of embedded SQL and allows some of the flexibility Scott describes as needs he had when writing the JDBC service program.

I see some potential limitations in an ARD approach as you have it, mostly related to SQL syntax supported on disparate platforms. Personally I would not want something like a general-purpose ARD to have to make special conversions of SQL statements for different RDBMS' like Oracle or SQL Server, which all have some uniqueness and have things that are valid on the i and NOT on the remote database. I'm sure you are aware of this issue.

Regards
Vern

On 9/9/2010 1:36 PM, Dieter Bender wrote:

Vern,

first of all: ArdGate is Open Source under GPL License, in other words: its
free and non commercial.
ArdGate uses another approach than RPG2SQL, comparable to Oracle Access
Manager for AS/400. The main diffrence in functionality is, that ArdGate
enables Connectivity to all Databases providing a JDBC driver. Its a pure
Java universal Database driver, a bridge between the *ARDGATE interface (also
known as "Client Integration Exit") and JDBC. The RPG Connector is running on
the AS400, the Java Process could run on AS/400 too, or on any other Java
Plattform. From the AS/400 perspective the remote database is handled as anyy
other remote DRDA capable database, in other words: you could use normal SQL
statements with all supported SQL interfaces on AS/400!
Using dynamic SQL, AS/400 will not see what you are doing with the remote
database and it will let pass any statement and bring back the SQLSTATE
(internal translated to a sqlcode) and the data the remote database delivers.
If the remote database has capabilities, as400 doesn't have, as far as the
returned resultset is compatible with DB2/400 (some limitations: no BLOB max
precision 31), it would be possible to get it delivered in Host Variables, or
presented in interactive SQL.

Dieter
Am Donnerstag 09 September 2010 20:16 schrieb Vern Hamberg:

Dieter

I'm not so sure that is correct. IBM has some federation support
(WebSphere Federation Server, formerly known as DB2 Data Integrator or
WebSphere Information Integrator) that lets you define "connections" on
a PC server to different RDBMS' and let you treat them all as DB2
resources on that server. I believe SQL Server has something similar
(linked servers?).

Now if you do not take advantage of this kind of support, then, yes, you
have to make separate connections. It is the same with our RPG2SQL
Integrator product, although you are able to have multiple live
connections at one time in that product. You are not able to use the
separate connections to join tables, of course.

By the way, is your software free, or are you selling it? It isn't clear
from your posts. Your DRDA support is quite intriguing, of course.

Vern

On 9/9/2010 12:37 PM, Dieter Bender wrote:

Hi Eric,

you can't use two connections in one SQL Statement, regardless which
method you would use.
It works like it works on AS/400, you make a connect to your MS_SQL_DB
(configured with ADDDRDBDIRE and 2 lines in the global.properties),
preparing and opening your cursor to fetch data from the MS_SQL_DB and
then fetching one record into your Host Variables (might be a
DataStructure) then switching the connection to your local database with
set connection, updating your local table (or whatever your want)
switching back to remote with set connection and so on...

You might have multiple Connections to diffrent locations (even Mix with
DRDA Connections) at the same time and switching from one to the other.

If You have further questions, it would be no problem to provide an
example doing this.

ArdGate is work in progress and there will come up manuals later on,
first priority at the moment is marketing, the more users it has the
faster it will grow in functionality, second priority is to complete
functionality for Milestone 4 (select into, positioned uopdate operations
and completing to support all SQL statements). Milestone 5 will provide
commitment controll.

regards

Dieter