Re: RPG - MsSQL Connection -- RPG400-L

Hi Vern,

there is one thing you have to consider the special flavour of every database
anyway. DB2/400 uses SQLCODE for its decisions how to go on with the dialogue
between client and the database, and thats the vendor specific code. My first
attempt was to translate the SQLSTATE to a derived SQLCODE, but diffrent
databases bring very diffrent SQLSTATES back, sometimes even illegal ones, or
none.
In this case I have externalized the conversion rules to a property file, so
that an advanced user could customize this by his own for his special
database. If I would come to a point, where I would need database specific
code (I don't think so) this would be seperated in database specific classes,
plugged in by an interface, provided by ardgate. Its Open Source and
everybody would be free to provide components for a special database he
needs.
The diffrent flavours of the database could be hidden from embedded SQL by
using dynamic SQL and most of verification is done at package creation time
and ArdGate doesn't need packages and the upcoming compiletime component will
agree with anything, for the risc of runtime errors. At some point it could
end up in not supporting all Features of a database (Timestamp handling in MS
SQL Server for instance) or in a compromiss (translating oracles Integer to
numeric 31 instead of numeric 38).
The oracle guys wrote it native and I don't think they've used C++ and in my
opinion it seems to be a very hard stuff to write such things in a non OO
Language.
One note to DRDA: DRDA (normaly) runs on the server, I've used the ARDPGM
Interface, thats something diffrent, running on the Client (in this case
AS/400). But the main concept is transferable to DRDA, my first conceptual
approach was an universal DRDA Proxy, running on the AS/400 too, but the DRDA
specs seemed a littlle bit unprecise to me.

Dieter

Am Donnerstag 09 September 2010 21:20 schrieb Vern Hamberg:

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