Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 63 Insurance
- 536.4K On-Premises Infrastructure
- 138.3K Analytics Software
- 38.6K Application Development Software
- 5.8K Cloud Platform
- 109.5K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.3K Integration
- 41.6K Security Software
Jython : SQL query output to file

Dears,
Could you provide me guidance on how to extract FDMEE table data to a file? I would like be to trigger the select query from Jython script then write the output to a file on a specific directory on FDMEE server (problem is that we have 3 servers...).
Any sample, link or guidance appreciated. Many thanks.
I prepared this select already :
import java.sql as sql
sourceConn = sql.DriverManager.getConnection("jdbcUrl", "user", "password");
selectStmt = "SELECT DISTINCT
EXTRACT (YEAR FROM TDATASEG.PERIODKEY) YEAR,
CONCAT('M', EXTRACT (MONTH FROM TDATASEG.PERIODKEY)) PERIOD_QUALIAC,
CONCAT (
'M',
CASE
WHEN LENGTH (TO_CHAR (EXTRACT (MONTH FROM TDATASEG.PERIODKEY))) =
1
THEN
CONCAT ('0',
TO_CHAR (EXTRACT (MONTH FROM TDATASEG.PERIODKEY)))
ELSE
TO_CHAR (EXTRACT (MONTH FROM TDATASEG.PERIODKEY))
END)
CO_CVG_PERIOD,
CONCAT ('N', SUBSTR (TDATASEG.ACCOUNT, -9, 9)) CODE_NATURE_QUALIAC,
TDATASEG.ACCOUNTX CO_CVG_ACCOUNT,
CASE
WHEN TDATASEG.UD4X = '[None]' THEN 'None'
ELSE CONCAT ('F', TDATASEG.UD4)
END
CODE_FONCTION_QUALIAC,
TDATASEG.UD4X CO_CVG_FUNCTION,
REPLACE (TDATASEG.entity, 'L', 'S') CODE_ENTITY_QUALIAC,
TDATASEG.ENTITYX CO_CVG_ENTITY,
TDATASEG.ud3x CO_CVG_ACTIVITY,
CONCAT ('T', TDATASEG.UD7) CODE_TERRITOIRE_QUALIAC,
TDATASEG.UD7X CO_CVG_TERRITORY,
TDATASEG.ud5 CODE_PRODUIT_QUALIAC,
TDATASEG.ud5x CO_CVG_PRODUCT,
TDATASEG.ud2x CO_CVG_BU
FROM FDMDBO.TDATASEG
WHERE (LENGTH (SUBSTR (TDATASEG.ACCOUNT, -9, 9)) = 9)
AND (TDATASEG.ACCOUNTX IS NOT NULL)
AND (TDATASEG.UD4 <> 'None')
AND (TDATASEG.partitionkey = (SELECT partitionkey
FROM FDMDBO.tpovpartition
WHERE partname = 'GL_FRA_ANA'))
"
stmt = sourceConn.prepareStatement(selectStmt)
stmtRS = stmt.executeQuery()
while(stmtRS.next()):
params = [ ]
fdmAPI.executeDML(insertStmt, params, False)
fdmAPI.commitTransaction()
stmtRS.close()
stmt.close()
sourceConn.close()
Answers
-
within your while(stmtRS.next()): you will want to be taking the columns from the resultset stmtRS and writing the to a text file.
use the following to get your column value 1 row at a time
sColumn1 = stmtRS.getString("COLUMN1")
to write out to a text file review the following
Take a look at File I/O - Chapter 5: Input and Output — Jython Book v1.0 documentation
HTH