access Oracle, mySQl, MS SQL Server by embedded SQL in RPG

[Guest.Visitor]

Hi,
3 steps to acccess any database over jdbc from STRSQL, QMQRY and embedded SQL

- install ArdGate its open Source and available at:
http://sourceforge.net/projects/appserver4rpg/
you could choose between runnable jar or running on Tomcat or other J2EE
compliant WebServer

- add the JDBC database to the remote database directory with ADDRDBDIRE

- install the JDBC driver Archive to ArdGate and provide url and driver class
name

Access the database with normal SQL as any other database in the remote
database directory.

Its very scalable with high performance, it uses one prestarted JVM to serve
multiple RPG or other native AS/400 clients using my other Java Freeware
AppServer4RPG (which is extendable for other applications to provide java
access to RPG)

Dieter Bender
www.bender-dv.de

[opendave]

Hi Dieter,

This looks pretty interesting and I wanted to do some speed comparisons between your approach and the Scott Klement JDBC API we have used in many of our apps. I especially am interested in a single JVM part.

I have downloaded the files, restored JVAGATE, installed the app, and edited the configuration. It is not working, and have hit a point where I need to ask a few questions...

We are on 7.1, so we have the IBM-supplied Apache and the version 8.1 of the integrated App Server. I installed to the integrated App Server, which seemed to go ok and I could see the release notes when it was running, but I do see an error in the log file that says:
SEVERE ALR0262E: Bundle not found ::class.method=com.ibm.lwi.deployment.services.Bun dleServicesImpl.showstatus
:loggername=com.ibm.lwi java.net.ConnectException: A remote host did not respond within the timeout period.

I put the JDBC driver in the WEB-INF/lib folder and edited the config file. One question on the config though: do I need to specify the actual port or full url of the application anywhere? The example just had an IP address and I did the same.

When I type in 'connect to MSSQL' (which is the RDB name and the name I entered in the config) it just sits there with no error...

If you can point me in the right direction, that would be great.
Thanks!

[opendave]

Just a quick update.

I am no longer seeing error messages, but it still simply locks up when I try to CONNECT in STRSQL. Looking at the job log for the STRSQL job, I can see it is creating the DTAQ in JVAGATE so I think the RDB setup side looks like it is working. The job is waiting for a response on the DTAQ that never seems to come.

Leads me to believe the app server side is not reading the DTAQ. I do not see any sort of indication that the app server is doing anything, but it is running.

[Guest.Visitor]

Hi,

@initial error: before editing the configuration, AppServer4RPG won't find the dataQ
@port: if you mean as400.system you could use the ip adress, or a dns name of your as/400, if appserver and local database are sitting on the local machine, localhost should do it as well. without port the toolbox would use the default port (maybe you should try to replace the packaged jt400Small, with the actual Version of the toolbox anyway)
If you meant the port of the MSSQL, itz depends on configuration from your MS SQL Server and your Firewall (remapping).
@your current problem: did you have a look to the log of AppServer, it should be in the directory log in the document root, there should be further information (please give me feedback, in cause of error it should not hang, it should come back with an error to sql and I would fix this constellation).

Dieter

[opendave]

Thanks for the response. I will clarify for a second.

I am using Microsoft's SQLJDBC.JAR to connect to SQL Server 2005 running on another machine. This same driver config and url work with the JDBCR4 apis we currently use.

When I asked about port, I meant the port that the App Server was running on. In retrospect, I guess that does not matter if communication is handled through DTAQs instead of direct SQL to AppServer communication. The abstraction layer added by using a DTAQ would eliminate the need for the SQL config to know the AppServer port.

As for logs, I checked the logs in both the app root and the logs folder under lwi. Nothing of interest in either. In the app root the only thing in the error_log are messages about stopping and starting Apache. plugin.log has no errors either, just messages stating that the plugins were loaded.

I created a fresh new app server instance for AppServer4Rpg, so I do not think that should be a problem.

Any other ideas on how I could test to see if the AppServer4Rpg is really doing anything? Maybe there is a logging function/debug flag I can to enable to get more detailed logs?

Thanks again for the response,
David

[Guest.Visitor]

Hi David

it uses log4j and this is configured in log4j.properties (should be in WEB-INF/classes as global.properties)
add there a line with:
log4j.logger.de.bender_dv=DEBUG
and you will get all diagnostics built in to the App
The output should go to a log File in WEB-INF/classes/log (as you have a recent build) otherwise you should post your log4j.properties.

you could check existence of the DtaQs JVAQ and one Rxxxxxx (xxxxxx = JobNr. of the Job with the SQL using ArdGate) Both Qs must be unique otherwise there is a misconfiguration.

Your driver has to be in WEB-INF/lib (the jar Archive)

and you need two entries in the config with your url and the Driverlass name, but misconfiguration here, the attempt to connect would not hang and come back with an error.

Dieter

[opendave]

Just to be thorough, I will post details of how I got to where I am - which is working - although
I am not 100% sure why it started working exactly. I am still not getting any logging however.

========================
Job 963700 has this as the last message in the joblog:
Object R963700 type *DTAQ created in library JVAGATE.

...and the job is sitting here in the call stack:
JDBCGATE JVAGATE _QRNP_PEP_JDBCGATE
JDBCGATE JVAGATE 166 JDBCGATE
JDBCGATE JVAGATE 183 WORK
JDBCGATE JVAGATE 346 JVAGATE_fireEventP
JDBCGATE JVAGATE 360 GETRESPONSE
QRCVDTAQ QSYS /00F3

In JVAGATE there are indeed two DTAQ objects:
JAVAQ *DTAQ
R963700 *DTAQ

I am still not getting any logs, so here is my log4j.properties:
log4j.appender.stdout=org.apache.log4j.ConsoleAppe nder
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.Patt ernLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ ABSOLUTE} [%-10t] %-5p %c{1} - %m%n
log4j.appender.MeinDaRoFiAppender=org.apache.log4j .DailyRollingFileAppender
log4j.appender.MeinDaRoFiAppender.datePattern='.'y yyy-MM-dd
log4j.appender.MeinDaRoFiAppender.file=logs/ArdGateLog.log
log4j.appender.MeinDaRoFiAppender.layout=org.apach e.log4j.PatternLayout
log4j.appender.MeinDaRoFiAppender.layout.Conversio nPattern=%d{ISO8601} %-5p [%t] %c: %m%n
log4j.rootLogger=WARN, MeinDaRoFiAppender
log4j.logger.de.bender_dv.ardgate=DEBUG

Here is my server declaration in global.properties:
ard.url.CHUCK=jdbc:sqlserver://192.0.0.9
ard.driver.CHUCK=com.microsoft.sqlserver.jdbc.SQLS erverDriver

And the ADDRDBDIRE:
ADDRDBDIRE RDB(CHUCK) RMTLOCNAME(*ARDPGM) ARDPGM(JVAGATE/JDBCGATE)
================

And my current status is 'working', just no logs. And I am seeing this error on some
"Select * from" requests, which I assume is a certain field type not being supported
or liked by the driver:
SQ30000 - '220E'X -- Data descriptor did not match data.

As for why it started working, I think I know what the problem was. I noticed that when I
tried to restart the Application Server after making changes to the global properties, the
App Server would say 'Stopping' and the page would eventually time out. The associated HTTP
Server would stop, but if I went back into the App Server list the server was going back to
a 'Running' status on its own. I killed the job *immed in wrkact job to finally get it to
really stop. After restarting it started to work for me. I think somewhere along the line
I got the properties right, but the App Server job that was not ending was still working with
the old properties or maybe before I updated the CLASSPATH.

A few other notes:
- I did have to add the SQLJDBC.JAR to the App Server's CLASSPATH in HTTPAdmin.
- I have successfully used the DB2 JDBC driver to connect and pull data
- I have not been able to get JTDS to work. It throws an error:
Connection exception - SQLSTATE 08003, SQLCODE -30061
- Ideally, I would like to be able to specify full connection strings in the global.properties
so I could include database names, user, and password in that file instead of the calling app.
I tested this and it throws this error when I attempt to simply 'CONNECT TO CHUCK':
SQLSTATE class unknown - SQLSTATE S0001, SQLCODE -901

Anyhow, thanks for the help getting this far. I will do some testing to see how it performs and
try to identify the field types that are throwing the SQ30000.

David

[Guest.Visitor]

Hi David,

tried to reproduce your problem: if the Server is running, sitting on the right Q, wrong url, wrong Driverclass Entry, wrong user or worng password doesn't hang up the application.
You should try following:
add following lines to log4j.properties in WEB-INF/classes:

log4j.logger.de.bender_dv=INFO
log4j.logger.de.bender_dv.ardgate=DEBUG

restart the application

there must be a log File named ArdGate.log (don't know where your server its throwing to)
it must have entries like following:

2010-09-02 21:14:01,217 INFO [Thread-2] de.bender_dv.jvagate.communication.Receiver: GlobalProps loaded

2010-09-02 21:14:01,516 INFO [Thread-2] de.bender_dv.jvagate.communication.Receiver: connected to DTAQ DataQueue (system: AS400 (system name: 'PUB1.RZKH.DE' user ID: 'DSTERNB'):com.ibm.as400.access.AS400@65d0d124 path: /QSYS.LIB/DSTERNB1.LIB/JAVAQ.DTAQ):com.ibm.as400.access.DataQueue@2eb0a3f 5

then try connect again

2010-09-02 21:14:02,458 INFO [Thread-2] de.bender_dv.jvagate.communication.Receiver: getOrder

you should see lots of diagnostics with this log level (put it back to info or WARN afterwards)

Dieter

[Guest.Visitor]

the logs are named ArdGAte.log (don't know where the server throws it)
if you change the root entry to
log4j.rootLogger=WARN, stdout

the messages will go to the log of the container, but I would not recommend

Datatypes are supported CHAR, VARCHAR, DEC NUMERIC INT (up to precision) of 31, date, time, timestamp (depends on database) at the moment

For the jtds message its important to look to the originating message in the log of ArdGate (and in the Joblog too) full connection strings are no problem, they are just passed as found in global.properties, the only problem is: passing user and password is not tested and no feature (maybe you should make arequest at the project site at SourceForge) (S0001 is login failed)

BTW: with STRSQL DB2 tries to load the complete table, befor showing anything.

Embedded SQL is supported too, for select you would have to use prepared Cursor, you will find an example with the deployed Sources in the ZIP FILE)

Dieter

[Guest.Visitor]

Hi,
the output path is Container dependend, one quick an dirty solution for finding the logfiles would be to specify an absolute path in
log4j.properties:

log4j.appender.MeinDaRoFiAppender.file=/myAbsoluetPath/logs/ArdGateLog.log

finding the datatype that causes problems, you could use prompting the fields with F4, one candidate is timestamp, its treated very diffrent in DB2 and MS SQL Server

Dieter