I would suggest you start building your script from scratch using the admin guide sample.
1. Build a BefoImport script that just connects to your source database
you can use fdmAPI log functions to write success messages or errors captured by exceptions
2. add more functionality to that script like querying your view
at the end of each iteration you should get your script more and more complete.
That's the best way of learning.
Write > Test > Fail > Troubleshooting > Fix > Test > Success :-)
You're correct and that was the best way to start writing the script! Following that approach, it looks like the script is failing when it gets to the SELECT statement. I have noted it below:
sourceConn = sql.DriverManager.getConnection("jdbc:oracle:thin:@server","fdmee","password");
selectStmt = "SELECT POSTED_TOTAL_AMT,
I'm guessing it's failing because I'm connecting to the SQL view in a different schema? However, the "fdmee" schema that I'm connecting to has access to the "fdmuser" user. Is there a different syntax for this type of connection?
which is the error?
you don't need to use ; at the end of the line.
If you want to have select statment in multiple lines you will have to use triple double quoted
select = """ SELECT
Please if you find any answer as helpful then mark it so others can see its feedback
Yes, that was the issue that I didn't have the SELECT statement in triple double quotes (since it was on multiple lines). I will mark this answer as helpful!
I'd like to ask one last question related to the same SELECT statement. I have included the full SELECT statement below which includes a WHERE clause. I've tried the syntax a couple of different ways, but still no luck. Would you be able to provide guidance as to what part of this syntax is incorrect? Unfortunately, the Admin guide didn't have such an example for reference.
selectStmt = selectStmt + "WHERE FISCAL_YEAR = 2015 AND BUDGET_PERIOD = 2015 AND ACCOUNTING_PERIOD = 10 AND LEDGER IN ('STAGE_EX','REVEST_RC')"
Thanks so much for your help with those of us not as skilled in this area!!!
Jython use parametrized select:
select = """ SELECT ...
WHERE x = ? and y = ?
then you run it for example: fdmAPI.executeQuery(select, [param1value, param2value])
There are some examples in the admin guide.