The command LNCDBGET allows to retrieve data from database : Oracle, MySQL, PostgreSQL, Sybase, DB2 for iSeries or Microsoft SQL Server.
The "JDBC" license extension is mandatory.
Paramètres
The command LNCDBGET requires the following parameters :
|
|
|
|
|
Database port number |
NbPort |
|
|
Database type |
Type |
|
|
Database server address |
SvrAddr |
|
|
Database user name |
User |
|
|
Database user password |
Password |
|
|
Database name |
DBName |
|
|
SQL query |
Query |
|
|
Complete path of CSV result file |
CSVFile |
|
|
|
|
|
Prerequisites
Installation of the JDBC JARs on PC/Server hosting Launcher server:
In order to access the
Oracle, MySQL, PostgreSQL , Sybase, DB2 for iSeries or Microsoft SQL Server
database, it is imperative that the corresponding JDBC JAR is present in the
installation directory of the Launcher server.
For instance:
C:\Program Files (x86)\LAUNCHER400\LAUNCHER_JDBC\LNCClientWrapperJDBC_lib.
List of official JDBC JARs mandatory (no guarantee of operation if other JDBC JAR is used):
•
For Oracle databases, you must
use the Thin driver provided by Oracle.
For instance, for Oracle Database 10g Release 2, the JDBC Thin driver is named ojdbc14.jar,
and can be downloaded using the following link:
http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-10201-088211.html
•
For MySQL databases, the JDBC driver mysql-connector-java-5.1.21-bin.jar
has to be used :
http://www.mysql.com/downloads/connector/j/
•
For PostgreSQL databases (≥
version 7.2), the JDBC driver postgresql-9.2-1000.jdbc4.jar
can be downloaded from :
http://jdbc.postgresql.org/download.html
•
For Sybase databases (all Sybase products
using JConnect-7_0: Adaptive Server Enterprise, SQL Anywhere, Sybase IQ, and
Replication Server), the JDBC driver jconn4.jar has to be used :
http://www.sybase.fr/products/allproductsa-z/softwaredeveloperkit/jconnect
•
For Microsoft SQL Server (SQL Server2012, SQL
Server 2008 R2, SQL Server 2008, SQL Server 2005, and SQL Azure), the JDBC
driver sqljdbc4.jar has to be used :
http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774
•
For databases IBM DB2 for iSeries, you must
use the driver jt400.jar supplied with IBM System i Access for Windows.
After installing IBM Access on your PC, you can find the JDBC driver, for
example here:
C:\Program Files (x86)\IBM\Client Access\jt400\lib\jt400.jar
After having installed the JDBC JARs, it is mandatory to relaunch Launcher server.
Example
In this example, the database has the following characteristics :
• Type DB : PostgreSQL
• Database server address : 192.168.1.7
• Port number of the database server : 6078
• Database user : postgres
• Database user password : aura
• Database name : newold
• SQL query : select * from marci where title < 'b'
The CSV file containing data from database is still located here :
%TEMP%\CSV_result.csv
The LNCTOXLS
command is used to generate the following Excel document :
c:\temp\result.xls
PGM
LNCOPEN
LNCCMD CMD(LNCDBGET) +
PARM1('Type=postgresql;SvrAddr=192.168.1.7+
0;NbPort=:6078;User=postgres;Password=aura+
;DBName=newold;Query=select * from +
marci where title < +
''b'')+
CSVFile=C:\Users\crobini\AppData\Local\Temp\+
CSV_result.csv')
LNCCMD CMD(LNCTOXLS) +
PARM1('ToXls=*new;ToSheet=*NONE;ToName=*NON+
E;ColPref=*NONE;DataSource=C:\Users\crobini+
\AppData\Local\Temp\CSV_result.csv;RecCnt=32;A+
utoFmt=*NONE;AutoFit=true;FmtCells=false;Ad+
dColH=false;ShowDoc=true;SavDoc=C:\temp\res+
ult.xls;SavFmt=*XLS')
LNCCLOSE
ENDPGM