Forum Stats

  • 3,757,480 Users
  • 2,251,236 Discussions
  • 7,869,840 Comments

Discussions

FDMEE link server use in BefImport Event

745408
745408 Member Posts: 133
edited Jun 2, 2015 7:50AM in Financial Data Management

Hi Experts , please suggest !

I am trying to use link server in BefImport event so i dont have to pass the connection details, my selct statement works fine from FDMEE DB side but not from BefImport in Jyhton.

I have done many changes but its getting failed again and again. ODI operaoter log says:  AttributeError: 'str' object has no attribute 'executeQuery' in following script.

Also , i tried to insert values directly wihtout param and by removing all FDM functios (stmt, selectStmt, stmtRS etc) and that is showing import as green check but warning on validation and process log says "no generic data row imported"

here is the script :

if fdmContext["LOCNAME"] == "ACT":

import java.sql as sql

batchName = "Batch_" + str(fdmContext["LOCNAME"])

insertStmt = """

INSERT INTO AIF_OPEN_INTERFACE ( BATCH_NAME

,COL01

,COL02

,COL03

,COL04

,COL05

,YEAR

,PERIOD

,PERIOD_NUM

,CURRENCY

,AMOUNT

) VALUES (

?

,?

,?

,?

,?

,?

,?

,?

,?

,?

,?

)

"""

stmt = "select * from [hyperion-financial-dev].[DB_Link].[dbo].[ALL_USD_EXTRACT] where Fyear = 2014 and LMonth = Jan"

stmtRS = stmt.executeQuery()

while(stmtRS.next()):

  params = [batchName, stmtRS.getString("account"), stmtRS.getString("entity"), stmtRS.getString("XXX"), stmtRS.getString("XXX"), stmtRS.getString("XXX"), stmtRS.getString("Fyear"), stmtRS.getString("Month"), stmtRS.getString("period_num"), stmtRS.getString("Currency"),stmtRS.getBigDecimal("Data")]

  fdmAPI.executeDML(insertStmt, params, False)

fdmAPI.commitTransaction()

stmtRS.close()

stmt.close()

Many thanks !

Tagged:

Best Answer

  • SH_INT
    SH_INT Member Posts: 3,187 Bronze Crown
    edited Jun 1, 2015 3:11PM Accepted Answer

    The problem with your statement stmRS = fdmAPI.executeQuery(stmt, params) that is failing is that params is undefined. It is expecting an array containing parameters, you obviosly haven't declared / defined  one. Try this code:

    import java.sql as sql

    batchName = "Batch_" + str(fdmContext["LOCNAME"])

    insertStmt = """INSERT INTO AIF_OPEN_INTERFACE

    ( BATCH_NAME

    ,COL01

    ,COL02

    ,COL03

    ,COL04

    ,COL05

    ,YEAR

    ,PERIOD

    ,PERIOD_NUM

    ,CURRENCY

    ,AMOUNT

    )

    SELECT ?, account, entity, XXX, XXX, XXX, Fyear, Month, period_num, Currency,Data) from [hyperion-financial-dev].[DB_Link].[dbo].[ALL_USD_EXTRACT] where Fyear = ? and LMonth = ?"""

    params = [batchName, "2014","Jan"]

    fdmAPI.executeDML(insertStmt, params, False)

    fdmAPI.commitTransaction()

    Obviously assumes a DBLink has been set up to the source database

Answers

  • ThinkFDM
    ThinkFDM Member Posts: 224 Silver Badge
    edited May 27, 2015 1:58PM

    You're Jython code and queries look good.

    You may have to go a different route in connecting to an external database using Jython.  I've done this before using Jython and a JDBC connection.

    This article should get you close to what you're looking for - https://www.safaribooksonline.com/library/view/python-cookbook/0596001673/ch08s13.html

    Good luck.

  • Francisco Amores
    Francisco Amores Member Posts: 1,693 Bronze Crown
    edited May 27, 2015 8:23PM

    Hi,

    your code is not good.

    stmt = "select * from [hyperion-financial-dev].[DB_Link].[dbo].[ALL_USD_EXTRACT] where Fyear = 2014 and LMonth = Jan"

    stmtRS = stmt.executeQuery()

    while(stmtRS.next()):

      params = [batchName, stmtRS.getString("account"), stmtRS.getString("entity"), stmtRS.getString("XXX"), stmtRS.getString("XXX"), stmtRS.getString("XXX"), stmtRS.getString("Fyear"), stmtRS.getString("Month"), stmtRS.getString("period_num"), stmtRS.getString("Currency"),stmtRS.getBigDecimal("Data")]

      fdmAPI.executeDML(insertStmt, params, False)

    fdmAPI.commitTransaction()

    stmtRS.close()

    stmt.close()

    Many thanks !

    the error you get is that stmt which you have declared as a string has not the executeQuery method.

    You are mixing different approaches.

    Try changing your code:

    stmtRS = stmt.executeQuery()  > stmRS = fdmAPI.executeQuery(stmt)

    stmtRS.close() > fdmAPI.closeResultSet(stmRS)

    stmt.close() > you don't need it

    Then when you make it working I would start thinking about doing with other approach. What about INSERT INTO...SELECT ...

    in that way you would not have to loop each record returned by the select.

    745408
  • 745408
    745408 Member Posts: 133
    edited May 28, 2015 4:29AM

    Thanks Francisco for your quick response as always !

    I tried the changes suggeted but geting another error as:

    stmtRS = fdmAPI.executeQuery(stmt)

    TypeError: executeQuery(): expected 2-3 args; got 1

    seperately, i tried INSERT INTO ..SELECT method for direct insertion..that semms connection is establish and all steps are green in ODI operator but FDME log says, no generic data rows imported.. and here is not data in AIF while Period mapping and everythig is correct.

    Thanks !

  • Francisco Amores
    Francisco Amores Member Posts: 1,693 Bronze Crown
    edited May 28, 2015 8:43AM

    Sorry,

    stmtRS = stmt.executeQuery()  > stmRS = fdmAPI.executeQuery(stmt, params)

    params is a list of parameters. If you don't have any ? in you SELECT then you can pass empty list [] so:

    stmRS = fdmAPI.executeQuery(stmt, [])

    for your second point, I encourage you to troubleshooting... log the SQL query using fdmAPI.logDebug, then execute the sql statement in any sql tool...

    Please mark any question as helpful or answered so others can see its feedback.

    745408
  • 745408
    745408 Member Posts: 133
    edited Jun 1, 2015 10:57AM

    Hello Francisco ! hope you are doing good !

    I am still getting following error while changing the syntax. I have also tried by placing stmRS up and down on different places. but geting same issue.

    stmRS = fdmAPI.executeQuery(stmt, params)

    NameError: name 'params' is not defined.

    Also, no luck with following direct connection code: it shows all green check for Import and warning on validation with messge in log "no generic data rows imported". i am using a extra column name for BATCH _NAME insertion to avoid the variable input for just testing.

    if fdmContext["LOCNAME"] == "ACT":

    import java.sql as sql

    #batchName = "Batch_" + str(fdmContext["LOCNAME"])

    """

    INSERT INTO AIF_OPEN_INTERFACE ( BATCH_NAME

    ,COL01

    ,COL02

    ,COL03

    ,COL04

    ,COL05

    ,YEAR

    ,PERIOD

    ,PERIOD_NUM

    ,CURRENCY

    ,AMOUNT

    )

    select * Location,entity,account,XXX,XXX,XXX,year,Month,Period_NUM,Currency,Data from [DB link name].[DBNAME].[dbo].[ALL_USD_EXTRACT] where year = 2014 and Month = Jan"""

    fdmAPI.commitTransaction()

    Any suggestion would be much appreciated.

    Thanks

  • SH_INT
    SH_INT Member Posts: 3,187 Bronze Crown
    edited Jun 1, 2015 3:11PM Accepted Answer

    The problem with your statement stmRS = fdmAPI.executeQuery(stmt, params) that is failing is that params is undefined. It is expecting an array containing parameters, you obviosly haven't declared / defined  one. Try this code:

    import java.sql as sql

    batchName = "Batch_" + str(fdmContext["LOCNAME"])

    insertStmt = """INSERT INTO AIF_OPEN_INTERFACE

    ( BATCH_NAME

    ,COL01

    ,COL02

    ,COL03

    ,COL04

    ,COL05

    ,YEAR

    ,PERIOD

    ,PERIOD_NUM

    ,CURRENCY

    ,AMOUNT

    )

    SELECT ?, account, entity, XXX, XXX, XXX, Fyear, Month, period_num, Currency,Data) from [hyperion-financial-dev].[DB_Link].[dbo].[ALL_USD_EXTRACT] where Fyear = ? and LMonth = ?"""

    params = [batchName, "2014","Jan"]

    fdmAPI.executeDML(insertStmt, params, False)

    fdmAPI.commitTransaction()

    Obviously assumes a DBLink has been set up to the source database

  • 745408
    745408 Member Posts: 133
    edited Jun 2, 2015 7:50AM

    Thanks you very much SH. This worked ! your and obviously francisco's help is much appreciated !

    Thanks Again

    Vivek J

This discussion has been closed.