Jython : SQL query output to file

user1659263

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()

WVanSluys1

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