Importing Data using ODBC connection

kloren1

Forums

Hi,
I have the Mastering IDEAScript manual (by John Paul Mueller) and I can't seem to find any examples of importing data using an ODBC connection to Oracle within a macro. It is quite simple using the import assistant but my users have no SQL knowledge. Essentially what I would like to achieve is to create a dialog box which allows the user to enter an ID number and a fiscal year. Using those values as parameters I would like to import the data into IDEA.
SQL Example:
Select * from table(eg. transcation_detail) where id = :IDNumberEnteredByUser and fiscal_year = :FiscalYearEnteredByUser;
Is this possible and if so how would I do it? Do you need more information?
I am currently using Client Version 8.0.5.243
Thanks for your help.
Kenny
 
 

paarth Tue, 02/07/2017 - 11:05

thanks Mr. brian
now i will need ur help when actually i have to iimport data
ur guidance that time will help a lot
 

jarcea01 Tue, 10/01/2019 - 17:40

Hi Brian
with any permission you must have an ORACLE user to access the tables in IDEA through ODBC.
Connecting to the ORACLE database through ODBC does not display the tables.
 
 

JHDCC Mon, 11/11/2019 - 11:59

Hi all.

I have managed to amend the Oracle ODBC script to meet my needs, however, I am now seeking to improve it and remove the need to make manual amendments. As can be seen in the attachment, I have had to include a payment date in the SQL parameters for the import.

Is it possible to add variables that are not included on the dialogue box? I would like to pull from eg the user entering 19/20, a "FROM" date of '01-APR-2019' and a TO date of '31-MAR-2020' which can be used in the SQL.

Currently, if a user was to enter 17/18 in the Financial Year, I would expect it would bring back no results, due to my fixed SQL parameters.

I did have a thought that I could include a file or database with a list of dates, financial years and start and end dates to look up to, however, that may be overcomplicating things at this stage!

jppayan16 Tue, 02/11/2020 - 14:49

Hi, all
I have too many tables in Oracle, I need to import a lot of and specifics fields, how can i do? help me please!!!
 
 

Fernando Tue, 04/27/2021 - 04:39

Hi,
I have a question in case that I don´t want to fill in the endless list of field in the import. Can I instead add a function removing those unwanted fields like:
Remove Field
Function RemoveField
                Set db = Client.OpenDatabase("###.IMD")
                Set task = db.TableManagement
                task.RemoveField "IGCDAT_TIME"
                task.PerformTask
                Set task = Nothing
                Set db = Nothing
End Function