Re: RPG - MsSQL Connection

[Dieter Bender <dieter.bender@xxxxxxxxxxxx>]

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

Am Donnerstag 09 September 2010 19:09 schrieb DeLong, Eric:
> Dieter,
>
> Just wondering how to handle the need to join a remote table to a local
> table?  As an example, perhaps I need to retrieve rows from a MS-SQL
> Server, then update a local table to match the remote.  If using
> ARDgate, would I need to connect to the remote, then make a copy into a
> local temp table, then connect to the local DB2 to process the update
> statement?  I understand that DB2 Multisystem could handle concurrent
> access (federated DB), but otherwise is not supported on IBM i.
>
> -Eric DeLong
>
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Dieter Bender
> Sent: Thursday, September 09, 2010 8:37 AM
> To: RPG programming on the IBM i / System i
> Subject: Re: RPG - MsSQL Connection
>
> Hi,
>
> looks like a problem with the MS SQL Server Configuration, did you
> activate
> the TCP IP listener? check with netstat -a at the windows box with MS
> SQL
> Server, if there is a listener at 1433
>
> BTW: why don't you use ArdGate, its more easy to use embedded SQL over
> making
> calls to java methods from RPG - and you don't have multiple JVMs on
> your
> AS/400.
>
> D*B
>
> Am Donnerstag 09 September 2010 14:09 schrieb Chamara Withanachchi:
> > Dear All,
> >
> > I'm tryiong to connect a MS SQL database from RPG buy I'm getting
>
> following
>
> > error when it tries to make the connection
> >
> > com.microsoft.sqlserver.jdbc.SQLServerException: : Unrecoverable error
> >
> >         at java.lang.Throwable.<init>(Throwable.java:195)
> >
> >         at java.sql.SQLException.<init>(SQLException.java:40)
> >
> >         at
>
> com.microsoft.sqlserver.jdbc.SQLServerException.<init>(Unknown
>
> > Source)
> >
> >         at
>
> com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unkn
> own
>
> > Source)
> >         at
> > com.microsoft.sqlserver.jdbc.SQLServerConnection.build70Logon(Unknown
> > Source)
> >         at
>
> com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(Unknown
>
> > Source)
> >
> >         at
>
> com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown
>
> > Source)
> >
> >         at
>
> com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown
>
> > Source)
> >
> > bellow is the my code extracted from Scott Klement article
> >
> >          prop = JDBC_Properties();
> >          JDBC_setProp(prop: 'userName'     : 'sa');
> >          JDBC_setProp(prop: 'password'     : 'sdb#123');
> >          JDBC_setProp(prop: 'databaseName' : 'testDB');
> >
> >          conn =
> > JDBC_ConnProp('com.microsoft.sqlserver.jdbc.SQLServerDriver'
> >
> >                              :'jdbc:sqlserver://10.100.19.36:1433'
> >                              : prop );
> >
> >          JDBC_freeProp(prop);
> >
> > bellow is my CLASSPATH
> >
> > ADDENVVAR ENVVAR(CLASSPATH) VALUE('/mobbnk/java/sqljdbc.jar')
> >
> > Im on V5R4, JRE 1.4 and using MS SQL Server JDBC Driver 1.0
> >
> >
> > Can some one guide me on this
> >
> >
> > --
> > Regards,
> >
> > Chamara Withanachchi
> > IBM Certified Power System Expert
> > RPG Programmer
> > (owner of www.rpgiv.info)
> >
> > WWW.RPGIV.INFO
> > Mob: +94 77 1678646
> > chamaraw@xxxxxxxxxx
> >
> > i want to be future ready. i want control. i want an i.
>
> --
> mfG
>
> Dieter Bender
>
>
> DV-Beratung Dieter Bender
> Wetzlarerstr. 25
> 35435 Wettenberg
> Tel. +49 641 9805855
> Fax +49 641 9805856
> www.bender-dv.de
> eMail dBender@xxxxxxxxxxxx
> --
> This is the RPG programming on the IBM i / System i (RPG400-L) mailing
> list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.